Hi, this is Gary with MacMost.com. Let's look at using Autofill in Numbers.
MacMost is brought to you thanks to a great group of more than 1000 supporters. Go to
MacMost.com/patreon. There you can read more about
the Patreon Campaign. Join us and get exclusive content and course discounts.
Now if you need to fill a range of cells in Numbers with a single value or a pattern of
values you can do so easily using one of three Autofill methods. The first is simply to use
Copy and Paste. So, for instance, I'll put a value here in cell B2. Now if I want to
autofill a bunch of cells I can just Copy that one value. Of course I can Paste it
in any other cell. But I could also paste it in a range of cells. So I select this cell,
drag the little dot at the bottom of the right hand corner of the selection to select a range
of cells. Or I could click in the first cell and then hold the Shift key down and then click in
the last cell. Then if I Paste, using Command V, it will paste the same value with all those
cells. A quick way to do that for an entire column is to double-click on the Header,
here. So if I double-click on B, right there, you can see how it selects all of the cells
in that column but not the Header. The same thing with a row. So if I wanted to put that
value that I copied in column D I could just double-click there and Command V to paste.
Now this also works if you have multiple values. So, for instance, if I put a 2
here and a 3 here and then I select these three cells I can copy and paste it somewhere else,
like that. I could also select a range and paste it and you can see how out repeats
those values. It will do that for rows and columns. So this group that is 2 by 3 here
I can Copy and then Paste like this and you can see how it just repeats that pattern.
Now the second method is to use a special autofill feature in Numbers. To use that select a cell like
this one. Then move your pointer over it and hover towards the bottom center. Notice when
I do so there's that little yellow dot there. If I click and drag that yellow dot down you can see
how it autofills everything that I add to the selection. The same thing happens to the right.
You can get a dot on the right side and autofill like that. Now what if I want to have a pattern of
values. Instead of one I want to increment by one each time. All it needs for that is two cells to
show what the pattern is. So this is obviously increasing by one. I'm going to Shift Click to
select both of these cells, grab the yellow dot at the bottom and notice when I drag it gets that
this is a pattern where it starts with one and increases by one for every row. You can do the
same for columns across like this. The pattern can be something different. So I could put values like
this in the first two cells. Then select them both and use the yellow dot and drag down and
see I get it by fives. Or I can do something like this and you can see I get it by tenths.
It works the same way with date values. You can type a date like this and another date
and then you can use the yellow dot to autofill.
But with date values it's actually even easier. Because if you just enter one date it gets that
you probably don't want to repeat that same date over and over again. If you do you can
always use the Copy and Paste method to do so. But for autofill it just takes one value. It
will assume that you're increasing by one day for every row. But that doesn't mean you can't use two
values to show it a pattern. So I could show it something like this and then one week later
and then it will take that pattern and give me weeks instead. It works for months as well. So I
could do something like this and then something like this for January and February and then
drag and you see I get the first of every month. Now if I were to type January and then a year
I get what looks like just the month and the year. But if you look at the actual
value at the lower left you see that it is actually the first day of that month. It's
just going to automatically format this so the cell is formatted to be like that.
In that case it is real easy to drag and then get months. It works like that in the columns
too. So if I type this here in the first cell you can see the actual value is the first of
that month for this year and if I autofill you can see I get months across the top.
It even works with letters. If I type a letter like this and I autofill then you can see how
it automatically increases it by one. So it is real easy to create sample values like this.
Another case where it works is if you type a word or words and then a number like that. Then
you can drag and you could see how it increases the number there. So again real easy to make
sample values. Now what if I have some regular values across a row like this. Then I put a
formula here. I'm just going to do a simple formula to add B2 plus C2 plus D2 like that
and I get that. Now if I autofill, selecting all of those across, and drag the yellow dot down
you could see for these values it actually just copied them. In this one it copied it as well but
the formula automatically changes to stick with the row. So here is B2 + C2 + D2. This is B2,
C3, and D3 and row 4, row 5, etc. So a better way to do this maybe to actually just
autofill this one column here and then when you fill in the actual data here then these
formulas will work correctly because they'll be relative to the row they are in. But you
could also teach it an increment like I'll put a 2 there and I'll just repeat these like that,
copy this formula like that, and now if I autofill it works as expected. This went
increments by one. These two stayed the same since the two sample values I gave it were the same,
and then the formula works on its own row with relative values. You could see the result there.
Now there's a third way to do autofill and you may already be doing this without even thinking
of it as autofill. That is to not have any blank rows in your table. They don't really serve any
purpose so shrink your table to only include the rows that you have values for. Now when you go
to add a new row you can press Return and look at what happened. The columns that had values in them
are blank. They're waiting for new values. But the column that has a formula in it, that autofilled.
It assumed that you wanted the same formula there. So all you need to do is add these values but you
don't have to add the formula again. Go to add another row, I'll just press Return again, and
I can do the same. I never have to enter the formula again. It autofills when you add a new
row. That works even if you have Footer rows. So I've created a Footer row here
that just gives me the total, the sum of this column. Now if I go to the last actual data row
and I press Return notice how it pushes the Footer row down. It's separate from the data
and now I can enter new values in here and then press Return to go to a new row
and continue to add new values like that. It's always going to autofill the formula portion.
So I want to leave you with one important tip. This pertains to the second method,
the standard autofill function. Instead of having to select the cells and then drag the yellow dot
you can actually do this with the keyboard. So, I'm going to use the arrow keys to navigate to
the beginning of the cells that I want to use as the pattern. I'm going to use the Shift key and
the Down arrow key. You saw I select both. Now if I use Command and the backslash key,
that's the key over Return in U.S. keyboards, and you could see I get this yellow highlight.
Now with the yellow highlight on I can use the Down arrow key to expand the autofill area and
it will autofill like that. When I'm done I could just press Return and it exits that mode. You can
combine this with other keyboard shortcuts. For instance, Command Down Arrow will go to
the last cell in a column. So, if I select these two cells with Shift Down Arrow to select them
both and then I do Command backslash and then Command Down Arrow I've filled the entire column.
Hope you found this useful. Thanks for watching.