Sign in or start a free trial to avail of this feature.
12. Fill Commands
FILL commands are a nice alternative to COPY/PASTE formulas that can be more convenient in certain situations. Find out how to use various FILL commands in this lesson
Overview of Fill Commands (00:26)
FILL commands are used to copy and paste the format of a selected cell. They’re a useful alternative to using Paste Formulas. One simple fill command uses the mouse. By double clicking the bottom right corner of a cell, its formula will be pasted in every row in the data range. This can be useful in a large data set, as you can paste into a large number of rows with one double click.
Using Keyboard Shortcuts (01:04)
For smaller data sets, it can be easier to use keyboard shortcuts. There are two fill commands you can use this way: Fill Right and Fill Down. Fill Right fills a formula along a row, while Fill Down fills it along a column. In both cases, you pre-select the cell to be copied, and the destination cells, then use the keyboard shortcut: Ctrl + R for Fill Right, and Ctrl + D for Fill Down,
Fill Commands and Borders (01:51)
Fill Commands copy both formulas and formatting. If you put borders around your data set, then Fill Commands may remove them. In this case, you’re better off using Paste Formulas instead.
In the previous lesson, we learned how to use Excel's formula auditing tools to identify mistakes in our formulas. In this lesson, we'll learn how to copy and paste formulas using fill commands. You may have noticed in this course that I'm a big user of the paste formulas command. Out of sheer habit, I probably use it too much in fact. Instead of using paste formulas, you can also use the fill command. The fill command pastes both the formula and the format of the selected cell.
The first fill command we're going to use involves the mouse. In the order dollar amount column, I'd like to copy and paste this formula for the remaining rows in the data set. To do this, I can simply double click the mouse in the bottom right hand corner.
This automatically finds the last line of my array and pastes the formula down as far as this line. For data sets with a large number of rows, this is a really useful command.
If we're completing smaller copy paste jobs using the fill command, I tend to use keyboard shortcuts rather than the mouse. Let's take a look at this with fill right.
In the selected cell, I've used a SUMIF formula to calculate the total revenue for Bobby. If I'd like to calculate the total revenue for the remaining customers, I'll simply use control R after pre selecting the cells.
In addition to fill right, you'll also use a fill down command quite often in your data sets. And for fill down, we use a similar shortcut, control D. So again, I'll preselect the cells, control D, and again, this pastes the formula and the format for the remaining cells. If you have formatting that encloses data with borders, the fill commands don't work very well because they paste formats as well as formulas. In this case, you're better off using old ESF. Let's take a look at this in the order dollar amount column, where we can see in the last cell that we don't have our bottom border showing. And this is because when we paste from the first cell at the top, this cell has no border either.
So if you tend to add borders around the side of your data sets as I do, be aware that the fill commands will disrupt this border formatting when you complete a control D or control or command.