Excel’s Fill command lets you fill an adjacent group of cells with the same text or numbers, or with text or numbers that form a series. In its simplest form it’s like a faster version of copy and paste. For example, enter a name in cell A1, then highlight cells A1 through A5 and press Ctrl+D (the keyboard shortcut for Fill Down) and all the selected cells will be filled in with the name from A1. But it can do much more.
First let’s talk about the mechanics of using Fill, then we’ll get into what else it can do. There are several ways to access Fill:
- Keyboard Shortcuts – Ctrl+D is the shortcut for Fill Down. Ctrl+R is the shortcut for Fill Right. Both require you to first highlight a range of cells, starting will the cell containing the text or numbers you want copied to the other cells.
- Fill Button – With the Home tab selected on the ribbon you will find the Fill button in the Editing group at the right end of the ribbon. Clicking it will give you options to fill Down, Right, Up, or Left, which are self explanatory, as well as Across Worksheets, Series, Justify, and Flash Fill. To use the Across Worksheets option, select some cells on the current worksheet, then Shift+Click or Ctrl+Click on one or more tabs to select multiple worksheets. Then choose Fill – Across Worksheets to have the selected cells copied to the other worksheets. I’ll explain the Series option later in this tip and Justify will be covered another time.
- Fill Handle – The fill handle is the small black square at the bottom right corner of the bold outline around the currently selected cell(s). Dragging it is the same as using Fill or Fill Series, depending on the situation. Dragging it with the right mouse button will present you with a menu of options. Double-clicking it will automatically do a Fill Down as far as the contents of the adjacent column without you having to select the destination cells.
As I mentioned before, the basic fill command is like a streamlined version of copy and paste. You highlight one or more cells along with the cells you want to fill in, then select the appropriate Fill-Down/Right/Up/Left/Across Worksheet command. That’s nice but the Fill Series option let’s you do much more.
A series can be numbers, dates, days, months, etc. Excel just needs to be able to determine the pattern for the series. Some series are programed into Excel and are automatically recognized when using the fill handle or the Fill – Series button, such as:
- Months – Enter January and Excel with fill with February, March, etc. Enter Jan and you will get Feb, Mar, etc.
- Days of the Week – Again use the full name (Monday) or the abbreviation (Mon)
- Ordinals – 1st, 2nd, 3rd, etc
- Text followed by a number – The number will be incremented as in Year 1, Year 2, Year 3…
- Dates – dragging the fill handle will increment the day, but using the Fill – Series button will let you choose Day, Weekday, Month, or Year.
To fill a series of numbers you have to tell Excel how you want the numbers incremented. If you enter 1 in a cell and drag the fill handle, it will fill all the selected cells with 1. But if you enter 1 in a cell, then 2 in the cell below it, highlight both cells and drag the fill handle down, it will increment the number in each cell by one. If you did the same thing using 10 and 20 instead of 1 and 2, Excel would continue the series as 30, 40, etc.
If you use the Fill – Series button instead of the fill handle, you can enter just your starting number, then click Fill and choose Series. Here you can enter a Step Value (the number added to each cell in the fill range). You can also set a Stop Value if you want to stop filling once a certain number is reached. If you didn’t highlight a fill area before clicking the Fill button, you can also choose to have your series filled in a row (will fill to the right) or column (will fill down). If you set the series Type to Growth instead of Linear, each cell in the series will be multiplied by the step value. Set the Type to Date and the Date Unit section allows you to choose whether the step value applies to the Day, Weekday, Month, or Year.
Try out all three methods and you will quickly see how Fill works. Don’t forget to try right-dragging the fill handle as a quick way to get to the series options.
Next month I’ll explain how you can create your own custom series such as list of departments or sales reps.
