12. Fill Commands

Subtitles Enabled

Overview

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

Lesson Notes

Fill commands

- Fill commands paste both the formula and the format of a cell
- In some cases, they can be faster than Copy / Paste
- However, they do tend to disrupt the formatting of cells that are enclosed by borders

Keyboard shortcuts

Double-click: Autofill
CTRL + R: Fill Right
CTRL + D: Fill Down

Transcript

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 order dollar amount column, I'd like to copy and paste this formula for the remaining rows and 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 Ctrl + R after preselecting 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: Ctrl + D.

So again, I'll preselect the cells, Ctrl + D, and again this pastes the formula and the format for the remaining cells.

If you have formatting that encloses data with boarders, the Fill commands don't work very well, because they paste formats as well as formulas.

In this case, you're better off using Alt + E S F.

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 boarder showing.

And this is because when we paste from the first cell at the top, this cell has no boarder either.

So if you tend to add boarders around the side of your data sets as I do, be aware that the Fill commands will disrupt this boarder formatting when you complete a Ctrl + D or Ctrl + R command.

3 mins

4 mins

2 mins

5 mins

5 mins

4 mins

5 mins

6 mins

2 mins

5 mins

5 mins

2 mins

3 mins

4 mins