MacMost Now 785: Filling Cells In Numbers

There are various ways you can automatically fill cells in Numbers. You can copy and paste to quickly fill cells one time. Or, you can construct your table so cells automatically fill in as you expand the table. You can also fill in cells with a series like months, dates, numbers or letters.
You can also watch this video at YouTube (but with ads).
Video Transcript: Hi this is Gary with MacMost Now. On today's episode I want to show you how to fill cells in Numbers.
So I'm often asked how do you fill cells in Numbers. For instance you may want to copy and then paste a number into multiple cells, you may want to have them automatically fill as you expand a spreadsheet. There is all sorts of different ways to fill cells in Numbers. So let's look at them all.
Here is a standard blank spreadsheet. The most common thing you may want to do is to simply fill a series of cells with the same number. So here I have put a number into a cell, I'm going to select it, and then select Edit and then Copy or Command C. Then I'm going to select a series of cells. I can do that by selecting one and then shift selecting another. You can also drag the little dot here at the bottom right hand corner and select your area. Then I'm going to Edit and Paste, or Command V. You can see it simply fills all those cells with that same number. I can select an area, paste it and it will do it like that.
Now this also works if you have a series of cells and even if there is formulas in them. So for instance this first cell here is just a number and then there is another number and this third cell here is actually a formula adding these two together. Now if I select all of them, Command Shift and select all three there, and I Command C (copy), then I select say the cell below that and Shift Select there to select that whole area, and I Paste, you can see that it pasted it all in. It is not pasting the number there in the last cell, it is actually pasting the formula, shifting it down as you would expect. So I can Undo that there and select say only that cell, copy, select a bunch of cells here, paste and you can see it is a bunch of zeros because this on here is trying to add these two together. I can put values in them and you can see it will adjust according to those values.
Now let's do this taking the entire table into consideration. I've got a large table here. I'm going to shrink it down to just the size I need. I'm going to insert an extra Footer Row here. It is very common to want to have totals. So for instance, let's shrink this all the way so that I just have one sample row here and put this as the -sum of everything in B, and then I'm actually going to copy that and paste that here, and sum of B and sum of C, and sum of D. Then I'm going to make this a formula where it takes this number and adds this number to it. So now I can put two values in here and you can see that it fills it in properly. Now what would be nice if it automatically filled in this formula here as I expanded and if I drag down and expand you can see that it is exactly what it does. It is smart enough to realize that I've got a formula here and it's going to add that to every row that I add. So as I increase the table size it will just add it in. So I can start adding in new numbers and it will automatically do that. 
I can shrink the table back down, say if I added too many and it will automatically do it if I select it properly. Oh, it actually looks like it is locked in place. So I can't actually remove these rows here, I can only add new ones to it. Which is probably the best way to do it. You can still select and I am sure delete these. You can see I am deleting the selected rows. So it is not going to let you easily remove data that you have added but you can still do it by deleting. 
So the automatic filling here, you can see how well it works as long as you set the table up correctly.
Now you also have some other options. For instance if I have a number here in the table I can select that number and a bunch of other cells. I can go to Insert/Fill and you can see Fill Down has been selected. I can copy that number into all of them. Look what happens if I put two different numbers here and I select a whole bunch of cells. Then I Insert/Fill and Fill Down and you can see that it's actually going to always take that first cell. Likewise if I did Fill and Fill Up it will take that bottom cell and fill it up. So you want to be careful when doing that. Likewise I can fill across, so I can Fill and Fill Right and it will put those numbers there as well. This works for formulas which of course where it really is useful. So if I put this formula here and now I select and I say Insert/Fill/Fill Down you can see its doing the same thing that the Copy and Paste would have done but just another way of doing it using the Menu/Insert/Fill.
Now here is something really cool that you can do. You can fill with patterns. It works pretty intuitively. For instance if I put the word January there and I simply grab the bottom right hand corner of that and drag down, look what happens. All the months appear. It will even repeat. The same thing will happen for the days of the week. Or if I were to do say a pattern like 1, 2, 3 and then select all of it like that and then drag, it knows the pattern and will repeat it. You can do this horizontally or vertically. You can even do it with text. For instance, A, B, I can select both of those and drag down and it will continue the alphabet. 
Now one more note about filling in formulas. Say I want a formula here that is this cell plus this cell and I also want a constant. I want to add something automatically like a sales charge to a list of sales. I want to put that number here. So you can see B2 plus C2 plus G2. Now if I were to copy this and paste it below you can see that it is B3 plus C3 plus G3. But I want this to be a constant. I want to put a number here like 1.5 and have that always added to each one. What I am going to do is go into this formula and click on the G2 in the formula and change it to make it an absolute row and column. So that means it is absolute. I will always link to G2 no matter where I copy and paste this. So I am going to copy and paste it below here. As a matter of fact I am going to do it a bunch of times. You can see it is always 1.5. It's going to do say 4 +5 and 6 plus, and just do a whole bunch of different ones to fill in. You can see it is always adding in, there B2 plus C2 plus G2. Then B3 plus C3 plus G2. So it is always going to use this number in every version of this formula. It is not going to go sequentially down as I copy and paste the formula. So that really comes in handy for a lot more advanced stuff.
So there is a lot of different ways to fill in cells in Numbers without actually having to enter in the value over and over again.
Hope you found this useful. This is Gary with MacMost Now.
MacMost Now 785: Filling Cells In Numbers

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
