20 Useful Tips and Tricks For Mac Numbers

Numbers is a huge app with tons of features. If you use it a lot, you can benefit from learning some tips, tricks and techniques. Learn how to autofill cells, re-apply sorts, get quick calculation results, automatically format cells, hidden keyboard shortcuts and much more.
You can also watch this video at YouTube (but with ads).
Video Transcript: Hi, this is Gary with MacMost.com. Here are 20 useful Mac Numbers' tips.
MacMost is brought to you thanks to a great group of supporters. Go to MacMost.com/patreon. There you can read more about it. Join us and get exclusive content.
Let's start off by talking about filling cells. Say you want to fill a column with sequential numbers. Start off by defining the series by having two numbers, like 1 and 2 here. Select both cells. Now if I move my cursor near the bottom center you'll see a yellow dot appear. Click and drag that dot and you can extend that series as much as you want. Now Numbers is smart about this series so if you, for instance, have 5 and 10 here and you drag down you see it completes the series in steps of five. But if you've got a series that just doesn't work with addition like this one then it if you extend you'll see it just repeats that series. You can do this extending to the right as well as down.
Now if you want to change the style or in some other way use all of the values in a column you could select the first item there, scroll all the way down to the bottom and select the last one. Or you could simply click on the Header here, where the letter is for the column, and then you could make a change. You could see the problem here. I also had this Header row selected. So if I were to change something, like for instance change the text to be a different color, you can see it effects the Header row as well as the values. If I want to select just the value cells, not the Header cells, all I need to do is double click here. So instead of clicking once to select Column B, I double click and now you can see it selects everything but the Header or Footer cells of that column.
Now if you have some repetitive data entry you can use a pop-up cell to make it a little bit easier. So, for instance, if I have these four items here that I want to be able to enter in any of these cells I can go to Cell and then change the data format to Pop-up Menu. Then I can define in this list here exactly which values should appear in that pop-up menu. But instead of that what you can do is first select these cells. So I select all of these cells here in this column including the ones that's already filled in. When I then change them all to be a pop-up menu it automatically fills in the values it finds for that pop-up menu. So when I go to this cell here to fill it in I have the right choices. 
Now you can also update this. So for instance if I want to add something else here I could just hit Delete and then type in a new name there. Now this won't appear here as something new there but all you need to do is select all of these cells and reapply the data format to pop-up menu. This keeps all the data the same. But now all of the items are included there in future pop-ups. So I can click here and choose the right one. Even the old ones will have the new items in the pop-up.
Now even if you don't use pop-ups you still have an Auto-Complete function in Numbers. So you start typing something, like letter A, and you can see suggestions from previous items in this column. All you need to do is use the down arrow to select it and Return to accept it. When there are multiple ones like with pear and peach here I can do p and I can select the right one with the down arrow and hit Return to enter it. It only works for the current column. So if I go over to the next one and I type p you can see it doesn't  come up with those suggestions. 
Now when you want to format a cell you can do so in the right sidebar. For instance I can type a value like this and then I can select it. Go to Cell and then change it. Say I want to go to Currency. Now it sets it to Currency with two decimal places. I also want to add a thousand separator to it. That's the format I want. But that was a lot of steps. Instead I can simply type it right the first time. So I can do dollar sign and then include the comma and include the number of decimal places. Now when I look here at the format you can see it actually guesses the formatting from what I typed. The only difference is it's still set to Automatic Data  Format. But I could actually switch it in this case to currency and it still keeps my options. The same thing works for Dates. Since I can type a date like this and you can see the formatting here is set to what I typed. But I could also type this and you can see the formatting here is set to what I typed there. So just by typing it right the first time you can avoid having to set the data format.
A handy feature in Numbers is Freezing Header columns and Header Rows. If I were to scroll over here you could see the first column stays there so I can see what's in it. Likewise if I were to scroll up you can see the first row stays there. You can change this by going to Table and then there's Freeze Header Rows, Freeze Header Columns. 
Now let's say you had a repeating value that you were using a lot in a table. Like, for instance, you wanted to calculate sales tax here. So you would do a formula here, select the price and multiple it by say 1.08 for eight percent sales tax. Then I can copy and paste that here. But what has happened is I baked in that number into this formula. A better way to do it would be to actually have a cell that represents that value. So in this case for sales tax let's do 0.08 and then make the formula 1+ and then this cell here. Let's also make sure we preserve the row and preserve the column. We get the same result but we can change this one value whenever we want and all of these values will change as well. A better way to do that instead of including this sitting by itself in the same table is to actually create a new table. So you go to Table and then create a blank table here. Shrink it down to just one cell and we'll call this Sales Tax. Let's put the value in here. Now we can move this wherever we want. We could put it here at the top, for instance. Then I could change this formula here to instead of pointing to this cell, point to this one. Also set it to preserve row, preserve column. Now I can Copy and I can Paste it in here. Get rid of this awkward cell here and shrink this table down if I want. Now I've got Sales Tax represented all by itself. I can change it to whatever I want. 
This is also great if you want to Lock this table. So you can go to Arrange Lock and lock this entire table. So now it can't be edited. But the sales tax can. So now you can play around with different sales tax amounts distributed and you know that it's a little harder for people to mess with these numbers. This is the one they're supposed to be playing with. 
Sometimes it's great to have random numbers to be able to test out an idea in a spreadsheet. So you can just type randomly and do that. But an easier way to do it to use the Randbetween function. So enter a formula. Use Randbetween. Choose two values like say 10 to 999. Then you can copy and let's say paste this in this entire column here. Then you have random data to work with. The odd thing is that these will change every time you make a change to your spreadsheet. So if I enter some data in here notice how these all change. The formula recalculates a gives you a new random number. So what you can do is select all these values. Then do Edit, Copy and then immediately with the same selection Edit and Paste formula results. That pastes the actual values in there. Now these are the real numbers. No longer the Randbetween function and they won't change as you work on your spreadsheet. 
So let's say you've changed the style of some cells and you want to apply that to other cells as well. Well, you can select a cell and you can copy just the styling. You can go to Format and Copy Style. You can select other cells and Format Paste Style. It won't change the value of what's in the cell. Even if it's a formula. It will only just paste the style. There's another way to do this that may work better depending upon what you're trying to do. There's Styles in Numbers just like there are in Pages. So I can select this cell, for instance, and it will tell me over here under Text that this is Table Style 2. It will put an asterisk next to it and allow me to update that style. If I look at this cell I can see that it's also Table Style 2 but it's been unaltered. If I select this cell and say Yes, update Table Style 2 to match what's in this cell then these styles or anything that's set for Table Style 2 will change. This will work across tables and on all different cells. You don't have to select anything in advance. It will just change all of those. So pay attention to Styles in Numbers just as you would in Pages.
Now sometimes you enter a lot of data into a cell and that forces that row to be larger than the others. Sometimes you really just want all of the rows to be even. You can turn wrapping off in a cell by going over to Format, and Text, and there's a checkbox here for Wrap text in cell. Select that and the text will then stay on the same line. In fact it will overwrite cells beyond it. These are still individual cells but since there is nothing in them this text continues over it. As soon as I actually put a value into one of these cells then it will just truncate text. All the text is still there. You just can only see a portion of it. The nice thing is all of my rows are nice and even.
Now whether or not you want text to wrap sometimes you do want to add another line of text in a cell. So I can type, say, line 1. But if I hit Return to go to line 2 it just goes to the next cell. You can add multiple lines of text in a cell by using Option and Return. That will then go to the next line just like you were hitting return by itself in a word processing app or Notes or something. Then you can add more lines of text and continue to use Option Return to add even more.
So often you're dealing with a lot of sidebars over here. There are four different variations of the Format sidebar and three for the Organize sidebar. There are keyboard shortcuts to allow you to select these. You don't have to use the cursor to click on them. If you go to View and then look in Inspector, you'll see Show Next Inspector Tab. It's Control and then the key above the tab key. Add Shift to go backward. So I can do Control and that key to move through these. However you still have to click to go between Format and Organize. But since these are actually menu items here you could go in System Preferences and set keyboard shortcuts for Format and Organize in Numbers and then have keyboard access to those as well. 
Text substitutions is another great thing that you can do in System Preferences. So you can setup a few key strokes  to enter a longer word. You can also do it just for Numbers. So if there are things that you just need to enter in Numbers and you don't want to set a system wide text replacement you can do it. For instance if every once in awhile I need to type the name of a company instead of having to type all those characters I could take that text and go into Numbers, Preferences and under Auto Correction you can see Replacement. There are custom replacements just for inside Numbers. So I can add one here. Do the shortcut here and then what it should type out there. Now I can use that and just type those characters, hit Return, and it puts that text there. That will only happen in Numbers. It won't happen in other apps if you do it that way. If I wanted it to happen throughout the system I could do the same thing in System Preferences. 
Now if I want to insert a column or insert a row the way I would do it is select the column or row and then I would go to Table there I have Add Column Before or Add Column After. There aren't any keyboard shortcuts for this which is a shame if you need to use them a lot. But there are actually hidden keyboard shortcuts for this that aren't, for some reason, listed in the menu. You can use the Option key and then any of the arrow keys to insert columns or rows. Option and the right arrow will insert a column after the current column. Option and the left arrow will insert a column before the current one. Likewise, Option and the up arrow will insert a row before and Option and down arrow will insert a row after. So you can easily enter in a whole bunch of new rows by using Option and the down arrow a bunch of times. 
Now if you want to do a quick sum or average of a bunch of values one way to do it is to select them like this and then look at the bottom. You'll see these little dragible things here. I can, for instance, drag some up here and insert it in. I don't like to use those because one of the problems with them is they use the exact range like B2 to B6. Usually I'm putting these in a Footer row so I want this to be sum of B. That's the proper way to do it. But I do like the fact that they appear at the bottom with some quick answers. So if I wanted to see a quick sum here but not actually have that in a cell all I have to do is select them, look at the bottom, and there's the sum. There's the average. There's the minimum and there's the maximum and there's the total number of items I have selected. But there's more than that. I can click here and there's a whole bunch of different things that I can see. For instance, I could do the Product and it will add it here. I can see the product of everything I have selected. So I can select two numbers and there's the product of it.
So to sort your spreadsheet you may be tempted to click here at the top of the column and then use one of these sorting functions. The thing with that is that if you change a number here, so if I change this, you can see it's not going to resort this column. You have to go and remember exactly how it is you resorted it before. Instead if you were to go to Organize and then Sort and then Add a Column like, for instance amount here and say Sort Ascending it will come up with the same thing that sorting like this would. The only difference is that it saves this sort here and you could always use the Sort Now button. So I could change this number here and then hit Sort Now and it will resort everything. If I have a complex sort involving several columns it will remember all that and all I need to do is hit Sort Now. You could also go to Organize and Apply Sort Rules instead.
So my last tip is that sometimes when you're looking at a big table it could be hundreds of thousands of rows and you need to actually look through it manually to find some data or to check for corrections that kind of thing. You can, of course, select an item here and then use the arrow keys to move down. But you can also select the entire row by clicking on the Header number for that row and then it selects the entire row. Once you've done that you can use the arrow keys to move down or move up and since it has highlighted the entire row it makes it really easy for you to look across the row and see what's there.
Comments: 6 Responses to “20 Useful Tips and Tricks For Mac Numbers”

Jan Wille
5 years ago
I’m sure that you know about this, but just a little addition to your Tip #1.: Autofill works also with e.g. Dates and Letters.
Rob N
I use pop-up menus quite frequently and could never figure out an easy way to edit them easily in a large spreadsheet. Thank you for showing me a simple way to accomplish this task. Much appreciated.
Gene
I was aware of some of these but you showed me ones that I will use a lot (insert rows and columns). Your deep insight into the functionality of Apple products is truly amazing. Thanks
Henry
Great video - I have learned a lot on how to use Numbers better!
Maggie
Great tips - but I still can not get Auto-complete to work. I found a previous question about it and went to preferences as suggested & ticked 'editing-showsuggestions' but it still does not work.
All good now :-) Had tried to use the pop-up menu and when I went back to Auto it worked.
Comments Closed.
20 Useful Tips and Tricks For Mac Numbers

Comments: 6 Responses to “20 Useful Tips and Tricks For Mac Numbers”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
