If you need to have a set of months automatically generated instead of typing them one by one in Microsoft Office Excel, this post will help you. You can repeat the same for anything such as number, weekdays, months, years, and so on. Let us figure out how to auto-generate months in Microsoft Office Excel.
Though it is popularly known, I discovered it a few years ago when preparing my accounts for this financial year and had to do calculations month by month. Now it was really tiring to type each month one by one. I wanted to generate all the months automatically, and I found how to do it.
How to Auto Generate Months in Microsoft Office Excel
- Select a cell in Excel, right-click, and select the type as Date.
- In Date, you will get various options like Date formats in MM.DD.YY and more. Select Just M, i.e., which means months.
- Now keeping the cell selected, you should be able to see a cross mark at the right corner.
- Grab it with your mouse and start dragging down.
- As you drag down, You will see Excel showing you next month. Look at the picture for a better understanding.
Note #1: Since I already typed in April, it started generating from April, and it rolls on January again when you cross December.
Note #2: This should be applicable to many things like auto-increment numbers 1,2,3 or incrementing dates. Give it a try.
How to Auto-Generate Complex Series in Excel
Excel offers alternate ways to do this using the Fill Method. It is available in the Home tab > Editing group.
To use this, you need to enter one or two months in a column and then select the range until which you want to fill. Then click on Fill > Series. It will open a small window where you can select:
- Series in Rows or Columns
- Type can be Linear or Growth or Date or AutoFill
- If you setup Date, you can select one of the options in the Date unit
- Lastly, if you don’t want to do the hard work, use AutoFill.
There is another option—Step value—So if you want to skip a month, you can increment by two instead of one, and you can also add a stop value to set where it ends. It is also called filling non-sequential months or numbers.
How to use Flash-Fill to autogenerate data in Excel
Flash Fill feature in Excel is smart sense. If Excel sees what data you’re filling in and compares that with what is already available in nearby columns, it can auto-fill that data. It need not be in the immediate column, but they need to around.
In the above example, I have a monthly column, salary column, and month paid column. Even though I had used a short form of the months, it was able to pick and suggest a flash fill.
Want to read more tips? Here is our guide on tips to increase your productivity with Excel
