30 Quick Numbers Tips

Learn some useful tips for using Numbers on your Mac. Discover ways to autocomplete, highlight, style, select and calculate.

You can also watch this video at YouTube (but with ads).

Video Transcript: Hi, this is Gary with MacMost.com. Here are some tips for using Numbers on your Mac. 
The first tip you should know is about Edit. Paste Formula Results. One example is say you want to increase all the prices in this table. Add another column here, create a simple formula, I'll type equals here, and then I'll choose the column to the left x1.2. This will increase the price by 20%. I can Copy and then Paste this here and I get all new prices. Now to have these new prices replace these I can select here, I'll just do Command C for Copy. Go here and then use Edit, Paste Formula Results. So instead of pasting the actual formulas that are here it's actually going to take the numbers and paste them there. So that the actual number is there. Now I can delete this column and I've got my change. 
A similar function is copy snapshots. This works for a whole table. So here I've got some values and then a formula that calculates the results. I can select the Table, go to Edit, Copy Snapshot. Now when I Paste I get a new version of that table but notice here this is no longer a formula. It's an actual value. 
Now let's say you decide to change the Style of some text, like in this cell right here. I'll go to Format, Text, and I'll make it Bold and let's make it a color, like this. Now I want to apply that to all of these. I can select this cell and go to Format, Copy Style, or Option Command C, and then I can select the other cells and then go to Format, and Paste Style to apply that. 
So a better way to do that is to use actual Styles. Notice after I made this change to this one cell here, under Format Text it shows a Style name here. It gives a little asterisk with an Update Button. Instead of updating the style for all of the cells I'm going to click here, add a New Style and call it something. Then I'm going to apply it to this cell. I can then select other cells and apply the same style to those. The advantage to this is I can go to anyone of these later on and change its style, like that, and if I Update the style all of the cells using that style will now update throughout the entire document. 
By the way, if you find these videos valuable consider joining the more than 2000 others that support MacMost at Patreon. You'll get exclusive content, course discounts, and more. You can read about it at macmost.com/patreon. 
Now let's say you want to add multiple lines inside a cell. Like, for instance, I want more information to be here. If I were to simply hit Return it goes to the next cell. So the trick is to use Option Return and you get a new line and you can type something else, like that. 
If instead you had this all on one line, like this, you can see it simply wraps and expands the cell like that. Let's select the entire Table and go to Table, Wrap Text, turn that Off and you can see it no longer wraps. The text just disappears into the right side.  By the way if the cell next to it was empty then instead of disappearing it just continues into that cell. 
If you even need to you can also merge cells. I can select, for instance, these two Header Cells here, go to Table and then Merge Cells and now it is just one cell. So, this cell is actually the cell C1. There is no cell D1 in this table anymore. You can do it for whole blocks of cells if you wanted to. Like that. So now there is just a cell D4 and the rest aren't really there. If I ever want to Undo it you can go and Unmerge cells like that. 
Also, if you ever want to resize columns to fit all of the content exactly you can, of course, drag the line here in-between the Header, like this, to resize. But a double click will actually snap the column with to perfectly fit everything in there, even if that means expanding it like that. If you want to do it for all of them you can select All of the columns and then just double click any one of them.
When you enter a number like this you don't always get the formatting you want. You can select it and then go to Format, Cell and change the data format. But if you type it in the data format you want to begin with it will figure it out. So, I can put a dollar sign, for instance, and then something like this and instead of converting it to 4.5 it will keep this format here. This is also true using commas. So I can type something like this and it will keep the commas there. 
If I don't do that, or this is a result of a calculation, if I want to add commas I can go to Format, Cell and under Data Format whether it is automatic or number there's a thousand separator option here. You can also set the number of decimal places to Auto or a Specific Amount. Also how negative numbers are represented.
Another thing you can do is you can get it to show fractions. So I can type something, for instance, like this and of course it is going to give me a decimal there. But if I go to Format, Cell and change to Fraction it will show me a fraction there. I can pick an accuracy for numbers that don't perfectly fit a fraction. 
If you want some numbers to stand out you can use Conditional Highlighting for that. So you can select a bunch of cells, go to Format, Cell and then click Conditional Highlighting. Add a rule if the number is greater than or equal to 30 then we can have the text be red. It will update as you change the values. 
Notice when I have a table like this and it has got a Header Row at the top and Header column on the left. If I were to scroll notice how the Header Columns stay there and the same thing here for the Header Row. This is useful but if you don't want it you can go to Table and then turn On or Off Freeze Header Rows and Freeze Header Columns. 
Also notice when you look at a formula like this it is going to use the Column and Row Headers to represent the formulas. If you'd rather use letters and numbers, like the traditional way to do formulas and spreadsheets you can go into Numbers, Settings and turn Off use Header Names as labels here. So now if you look it gives you C2 x B2.
If you want to leave a little note so you can remember what a formula is doing you can either use Insert and then Comment, you can see there is a keyboard shortcut for it, or use Comment here in the Toolbar. Do that and you get to enter some information so you can include a lot of information about what is going on in this specific cell. Then you're always going to see this little mark here indicating that there is a comment and you'll get it whenever you just move your pointer over that. This is meant for back-and-forth editing but you can use it even if it is just you to implant more information into the table so you can remember what is going on later. 
But another way you can add simple information that is not in a table is to just add a textbox. Just add any plain textbox anywhere you want, type some info into it, multiple lines, and put that wherever you want. You can use any shape you want and in a shape you can add some text and you can make the shape a different color. You can make the text a different color, as much as you want. 
If you want to include a long list of sequential numbers you don't have to type them all out. You can just do two of them, like that, select both, and then move your pointer at the bottom here and you'll see this little yellow dot. Click and drag that and it will autofill everything below that. This also works with dates. So you can type a date like this and then select it and then drag down and you can see it will autofill all the dates. It even works if you have an interval. So I'll just do like a weekly thing here and then I drag down it figures it out.
Note that Numbers has a handy Autocomplete Function that works on the column that you're using. So in this column here if I go to Add something new and I start typing, like an O, you can see it gives me an autocomplete for a value found in that column in another cell. 
But Numbers also has, in Settings, its own set of Text Replacements. These are like the System text replacements but they only work in Numbers. So you can add something, like for instance I'll do .O and then have that replaced with oranges, like that. Make sure you have this turned on and then I can go here and type dot O and then return and it replaces it with oranges. 
But you can also create a Pop-up menu by simply going to Format, Cell. Change the data format to Pop-Up menu like this. Then you can add all these different items in here. Which sounds like a lot of work but here's what you do. Instead, select all of these cells, like this. Then go and switch them all to Pop-Up menu. Now you'll see it auto populates with all of these values.
Here's some useful selection tools. If you want to select an entire table it can sometimes be difficult to remember exactly how to do it. You have to click here. But if I select any cell in a table I can go to Edit and there is Select Parent. You can see the keyboard shortcut is Command Return. That will just select the table that the cell is in. 
If I want to select an entire column of cells I can click the letter at the top. So I can select all of column B, like that. If I just want to select the values, not the Header or Footer cells, just a double-click will do that and you can see how it selects all of the actual value cells, not the Header and not any Footer ones as well. 
Now let's say I wanted to get a quick summary. Like I wanted the total of all of these numbers here. I can select them all by holding Shift. Notice what happens at the bottom of the window. I get a Sum, Average, Min, Max, and a Count or all of those. I can also click here and add other summary values at the bottom as well. 
If I have a Footer row and I'll add one here by dragging down, and then I will convert it to a Footer Row, I can actually drag these into the Footer and it inserts the formula. It is the same as actually typing it in. It just a little easier to drag and drop if you're not used to typing formulas. 
You can bring up the Sidebar here using keyboard shortcuts. If I go to View and then Inspector you see that I can show Inspector with Option Command i. Then I can move through the tabs in Inspector with Control and then Back tic, or Control Shift Back tic to go backwards. So Option Command i brings it up and then Control and Back tic moves between these. You can also go here to switch between Format and Organize but there are no keyboard shortcuts. You could always set those up in System Settings, Keyboard, Keyboard Shortcuts if you want. 
It can also be a bit of a pain to insert new rows. You click here and choose Add Row Above, Add Row Below. But you can also use the Option Key. Option and then Down Arrow actually inserts rows. Option and right arrow will insert columns. 
Now often it is useful to use random values to test out your spreadsheets and formulas. I can have a random value just by using the RANbetween function and then I can set it up here. I can do, say, a random number between 3 and 9 and then I can multiply the whole thing by ten to get 30 to 90. I will copy it. Double click here to select all of the cells, and then Command V to paste and it will paste the same formula in all of these giving me some good random values. 
But let's say I wanted some random values that weren't numbers. I can use Choose for that. So the first thing is a random number so I'll do RANDbetween like this and say between 1 and 3. Now I need three values and get a formula that looks like that. So the result is one of those three random things. If I Copy it and Paste it throughout you can see it is just choosing random items. 
Note that if I want to sort this a lot of people go directly to here. Then choose Sort. But you can actually also use the Organize Sidebar and go to Sort and then Add the column to sort. You can have multiple columns as well to make it even more complex. I can click Sort Now and it will resort I don't have to give it the criteria again. 
Finally, if you even want to prevent yourself from actually changing some values put all those values in one table. Remember you can have multiple tables. So put all your constant values in one table, like this. Then you can go to Arrange and then Lock, or Command L. Now the table is locked. I can't get in here to change any of these values. I can easily Unlock it. So it is not really a security thing. It's more like a reminder to yourself to leave those values alone and work with the values in other tables instead. 
So I hope you found these tips useful. Thanks for watching.

Comments: 7 Responses to “30 Quick Numbers Tips”

Sheldon

1 month ago

Thanks bunches. I do enjoy when you go back to the basics....I am always stunned by how much I have forgotten

Randy Cloward

Great info as always. Is there a way to defeat the auto progression when auto filling cells? I'm constantly getting the wrong fill down when I want to keep the same date, account number, or even text that might have a number as the last character. I'm tired of reverting to copy/paste every time I have to fill cells having completed the change in one row.

Gary Rosenzweig

Randy: Depends on the exact situation. If you have a column with the same number in it, it should just autofill that same value. So I'm not sure of the situation here. Experiment and test things out in a sample document, perhaps?

Thanks Gary. My experimentation thus far yields varying result. If there is text in the field it depends on what it contains whether or not it duplicates or increments. For example when filling down "Joint-185" you get "Joint-186", etc. for the rest of the fill. If you have a Yes/No column with a "Y"as the text and you want the fill to all Ys you get "Z", then "A" etc. If it's a currency in doesn't increment. Dates always increment. So I'm relegated to Copy/Paste. Excel shortcuts fail.

Oh, and I've discovered that manually typing or pasting the same thing in the next cell down, select both cells and then fill down it will duplicate and not increment. That does help but I still wish for a key combination that just lets me fill down the cell contents or better yet provide an option in settings to just turn off the auto increment feature.

nick

3 weeks ago

Gary, I can't find a way to copy cells in a column and paste them in a row, or vice versa. In Excel it's easy, there's a Transpose option. Is there a way to do this in Numbers? thx

nick: There is a transpose function in the Table menu. It transposes the current table, so you'd need a different workflow than what you describe, but you can make it work.