Typing or formatting cells of repetitive data can be very boring, but Excel has an Auto Fill feature to make it easier by automatically filling in data for you. We thought we knew Auto Fill, but just found nine surprising and useful tricks.
Auto Fill goes beyond simple incrementing numbers or dates which is what most of us do. Auto Fill can be used for any type of data that fits a regular pattern. Dates are well supported with good options under a little-used menu as well as copying formatting choices.
Numbers
Just a quick catch up for Excel newbies. Enter two or more values, select them then drag down/across to fill more cells according to the repeat pattern in the selected cells.
The simplest example is a list of integers, just drag down to make a list as long as you need. As you drag the tooltip show the last value you’ll get if you complete the drag there.
At the end of the completed fill is a little icon, click on that to see a fill menu of options. We’ll explain that a little later.
Auto Fill doesn’t stop at simple number list. Excel looks at the difference between the two selected cells and repeats it. These examples jump by two, three and seven.
As you can see, the starting value can be anything you like.
That’s the basic Auto Fill, from there Excel has a lot of hidden tricks available.
Dates – day by day
Another simple example of Auto Fill is filling a column with dates, you can type in the first date in the format you want, click the bottom-right hand corner of the cell, and drag down for as many rows as you want to fill. Excel will automatically fill the cells with all the following dates.
Dates – week by week
You can auto fill with other patterns as well. Just start typing the pattern into the cells, and when you have established the pattern, select all the cells comprising the pattern, and again click and drag from the bottom right corner. For example, if you want a list of weekly dates, you would start by putting two dates a week apart into the first two cells, and ensuring you have both cells selected when you click and drag from the bottom right corner.
Dates – monthly or more
Auto Fill will detect and continue repeat patterns such a fortnightly or monthly.
Really any pattern is acceptable even an unusual one like this, jumping 9 days at a time.
This works for numbers, dates, days of the week, and anything else that has a numerical pattern to it.
It’s also possible to fill weekdays only or fill by month or year, see below.
Repeating Formatting
Auto-fill can also be used to repeat any pattern of data, including the formatting. While for numbers and dates it predicts the data that is coming up, if you want to just repeat an existing pattern, enter the pattern once, select all cells, and click and drag. In the example below, we want each week to be represented by a different color, so we have entered the six colors we want and filled in the cells with the appropriate color. We want this pattern to repeat for the rest of the dates listed, so again we highlight them all, and click and drag from the bottom right corner.
Auto-Fill Options
You can also choose different options for auto-fill by clicking the Auto Fill Options menu that appears after you drag the cursor. In the example above, if we click the Auto Fill Options menu, we see a number of options for what data should be used to fill the cells.
Copy Cells will copy both the format and the content of the cells. This is the default.
Fill Formatting Only will copy the sequence of colors, but without the content.
Fill Without Formatting will copy the content of the cells but leave the cells with no color fill.
Flash Fill is not relevant in this case but we’ve talked about Flash Fill here.
More Date Auto Fill options
With a series of dates, you have a few extra options:
Copy Cells will again copy exactly whatever cells you have selected. (So if you have only selected one date, the same date will be repeated in every cell. If you have selected a whole sequence, that sequence will be repeated.)
Fill Series and Fill Days will fill the cells exactly as described in our first example – incrementing by one if you have only selected one cell, and repeating the sequence if you have selected a whole sequence.
Fill Weekdays will skip dates that fall on Saturday and Sunday.
Fill Months will fill the cells with the same date of each month.
Fill Years will fill the cells with the same date each year.
Last day of the month
If you choose ‘Fill Months’ starting from the last day of a month, Excel is smart enough to show the last day of following months. For example, starting from 31 December will fill to the end of February, April and June.
That works properly even if the starting value is the end of a month with 29 or 30 days.
Using Auto Fill for Calculations
Auto-fill can also be used to copy equations to a number of cells. It’s something that’s taught very early in Excel classes.
Say we are selling something that has a processing fee of 10% of the sale price. In that case we just need to put the appropriate equation into the first row, then drag down the column to apply the same equation on each row. Note if you drag past the rows that already contain data, the formula will be there waiting to fill in the cell automatically with the correct calculation when data is added to that row.
If you don’t want the cell reference changed use the $ to ‘fix’ the reference. $A$2 stays fixed on that cell no matter where you move or copy that cell. $A2 fixes the column reference but the row ref will change. A$2 lets the column reference move while the row number remains the same.
These are just a few examples of how the Auto Fill feature can be used. There are endless applications of this feature with various patterns of data, so have a play with it until you are comfortable using it.
