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.
