Excel has some really useful Auto Fill options when you need to create patterns in data.
Basic Auto Fill Options
When you are in an active cell in Excel, there’s a little green box at the bottom right-hand corner, which is called the fill handle.
When you move your cursor over the fill handle, the mouse pointer will change into a cross hair shape. You can then click your mouse and drag the mouse down to fill in the cells.
If you have one number in a cell and drag it down, Excel will simply repeat that number in all cells. When you do this, a little box will appear called the Auto Fill Options box. Clicking on this gives you a few options, that we will go through.
Fill Series
If we drag the number 1 down and then click in our Auto Fill Options box and select “Fill Series” Excel recognizes that we likely want to fill these cells with numbers in a series. In this case, it counts up for us. This option also copies the formatting from the original cell.
Fill Formatting Only
If we didn’t want to drag down the contents of the cell, but only the formatting of the cell, we can select the Fill Formatting Only option, and you can see that it just preserves the formatting, without any numbers.
Fill Without Formatting
The Fill Without Formatting copies the cell down, but does not copy the cell formatting. So in this case, it would just give us a 1 in all cells, without any special formatting.
As a side note, the Flash Fill option looks for patterns in adjacent cells. We won’t cover those today, but you can play around with that option as well.
Number Patterns
In the previous examples, we’ve only pulled down one cell’s worth of data, so Excel can only assume that we want our series to increase by 1. But we can change that assumption if we select multiple cells and drag them down.
For example, if we wanted Excel to give us a series of only even numbers, we could highlight cells with 2 and 4 in them and then drag that down. This allows Excel to recognize that we want it to simply add 2 to the previous number:
Dates and Times
Using Auto Fill with dates and time behaves a little differently. For example, when you select a date and drag it down, Excel automatically assumes you want to fill as a series.
It’s the same thing with a time. Time goes up by 1 hour. Excel also recognizes days of the week and months and can fill those out in sequential order.
Auto Fill Options with Dates
When we Auto Fill days, we get a few more options in the Auto Fill Options. Fill Days is just adding one day to the cell, so nothing special there.
Fill Weekdays
The Fill Weekdays option is especially useful in a work situation. Let’s say that you are filling out a schedule and we only want to show Monday through Friday. We’ll put in July 1, 2019, which is a Monday. If we drag it down and select Fill Weekdays, you can see that after July 5, it skips to the next Monday.
Fill Months
Fill Months will show the same day each month. When the day is the last day of the month, Excel Auto Fills for the last day of the month.
Fill Years
The Fill Years feature keeps the same month and day, but adds one year to the date.
Other Items
I should note that all the examples that we’ve done so far has been pulling cells down, but we can do the same thing pulling it across as well. ]
Another quick tip: When are Auto Filling numbers, you can hold down the control button and drag to automatically Auto Fill as a series.
Auto Fill Text
You can also use Auto Fill with text, but it’s not as robust. You can Auto Fill a pattern that you want to repeat.
If you have text that has numbers in it, Excel will Auto Fill the number as a series. Excel even has some features to make business reporting easier by repeating quarters.