Hi, this is Gary with MacMost.com. Let me show you how you can get
creative and make spreadsheets that are more than just a wall of 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 everybody is probably familiar with spreadsheets that are just big tables of
numbers. But, the Numbers App can do much more than that. For instance you can use Text and
Shapes and Images on sheets to design really nice pages that aren't blocks of numbers but
just have numbers individually placed throughout the page and performed calculations based on them.
For instance, let's start here with a blank template. But I'm immediately going to click
at the top left corner of this table and then press Delete and get rid of
that big table and just start with a blank area here. Now let's say, as an example,
we want to create a spreadsheet that helps us plan something. So let's say planning a party. So we're
going to start with a piece of text here and we'll call this Attendees to get the number of people
attending our party. Let's go ahead and make it more interesting with Format and then Text here
and then change it to some other Font, maybe make it Bold, maybe make it a little bigger and use
this as a nice font on our sheet here. Then let's have the number of attendees appear under here.
Since we want to perform calculations based on this number we want to put that number in
a Table. So I'm going to click here for Table and then choose the simplest table here with
no Header columns or rows. But I'm immediately going to shrink this down to 1 cell, like this,
and go to Format, Table and turn Off the title. Furthermore, I'm going to go down to Table Outline
here and turn it Off and set it to None. So now basically an invisible table but it is there
and it has room for one number. So let's put a sample number in here and say 50. I'm going to
select that and go to Format, Text and let's make it a little bit bigger and make it Bold
and let's center it in the cell like that. Then we'll move it up here under Attendees. Let's make
it even bigger than that. Like that. So now we've got one number we can enter in there.
Let's go and color this up a bit with Shapes. I'm going to do Shape and I'm going to go with
People and let's select a woman and then let's select a man and put them right there like that.
Maybe shrink them a bit and we can give it a color. Let's Color Code this whole thing. So
I'm going to select these two and go to Style and make them blue. Let's change the Attendees here
also to use that same blue color. Then even here we can change the text color to that if we want.
Now let's go and add a calculation here for the Budget for drinks. So I'm going to Copy
the text here by holding the Option Key down and dragging and then let's change this to Drinks
and let's create a small spreadsheet table again using this as a template here. I'm going
to select it and I can Option Drag from here to duplicate it. So let's have the Drinks budget
be here. Instead of there being that number 50 in it I'm going to go into that one cell,
press equals to enter a formula. I'm going to then select this cell here multiple let's say
by eight. $8.00 per person for an average drink budget. Now I get the result. $400. So if I now
go to Format Cell and change the data format to currency now I get something that looks like that.
Let's change the color here and make drinks, say, this red color. Like that. The same thing
here for this cell here. Let's go to Format Text and change that as well. So now we've got that.
Let's go and Add a Shape. Let's go and, say, under Food we'll probably find
some cups there. Let's go and set a little glass like that. Change the color of that
and use the same red, like that. Now we've got Drinks. Notice that if I
were to change this to say a different number this value changes automatically.
Now let's do the same thing but for Food. I'm going to select these two items here and Option
Drag to duplicate them and move them somewhere else. Let's change Drinks to Food. Let's change
the value here, instead of multiplying by 8, let's say we need to multiple by 12 for a Food Budget.
Let's set a new color for this as well. So Format Text and we'll
change this to green. Same thing for the text in that cell there.
Now when we change the number of Attendees, both of these numbers change. Let's go and
add a Shape. Something that works really well with Food here. Like we can choose a
plate like that. Let's use the same green for that style and then Fill like that.
Now let's go and Add another one for the amount of space that is needed. So this is different.
Instead of calculating a monetary amount we're going to calculate square footage. So let's go and
I'm going to Option Drag these two things here and then let's say Space and then the calculation here
will be similar except that what we're going to do is, let's say, we need 20 square feet per person.
We want to change the Format Cell instead of currency to a number. Actually let's go to
Create Custom Format. Leave it as a number there. But then put Space and then square feet like that.
So now we get the number of square feet needed. Let's go and change the color for this as well.
Make it some sort of other color, to a pink there. Pink for Space. For a Shape for this
there's nothing really great but we can go to like basic shapes and say let's take a
square there and resize it for space. I'll take the Style there, the color
and change that to that same color there. Let's go and do some arrows to signify space. Just
making up, basically, a shape here. Like that. Now we can change this to that color as well.
Drag with the Option Key held down to duplicate it and make one that is going vertically like that.
Now we have something that nicely signifies space there. All of these will change
according to what we need there.
Let's go and get more complex. Let's say that for square feet we don't need 20 per person, we need,
say, 40 square feet per person for the first ten people and then 20 square feet per person. So what
we're going to do for that is say let's take the minimum value of 10 and the number of Attendees
and multiply that by 40. So that will be from 1 to 10 will be multiplied by
40. Then let's add and we'll take the maximum value of zero and this number
minus 10 and multiply that by 20. So basically from 1 to 10 will be 40 square
feet are needed and anything over 10 will be 20 square feet needed. So that gives us
a little bit more complex calculations and shows how this can be more than just simple
multiplication or addition or whatever. You can have complex formulas in these things.
Now in addition let's go and Option Drag this here and let's switch it over to that same pink. Let's
calculate the cost and the cost is going to be the number of square feet times, say, $10.00. Maybe
that's probably way too much. Let's say times a $1.00 for the amount it is going to cost to rent
space for these parties. Now that probably doesn't make much sense because you're not
going to rent per square feet. You're going to rent based on the size of the space. So instead
of doing this let's create another sheet here. In this sheet we're going to have a
Table and the Table is going to have number of square feet needed and then it's going to
have (we're going to skip that column there) say the room name and then let's say a cost.
So, we'll say everything up to 1500 square feet can go in the conference room and the cost for
that is zero because that is free. That's part of your office. But anything bigger than that and
let's say up to 4000 square feet that's going to need a meeting room and that is actually part of
the building so that is going to cost you $500 to rent that. Anything bigger than that is going to
be at the event center and that's going to be a $1200 rental there. We can shrink this down
and let's go and put a formula here that's going to use XLOOKUP. If we look at XLOOKUP
we can see that it takes as parameters the search value. So what are we looking for. Well,
we're looking for the number of square feet here. Then what are we searching in the range. We're
searching this range here and then what are we going to get is, we're going to get that value
as well. If not found then zero and the match type is going to be exact or next smallest or we can do
exact or next largest. So let's try exact or next smallest. One more thing. We need to go in here
and set the Preserve Row for this so it doesn't try to change rows for it. So now we Copy and
Paste and we can see we're going to get 1500 in all of these. We can do a simple equals the cell
here to the left equals that. So now instead of 1500 we'll get True if it matches what's on the
left. So notice this is 1500 square feet True. We need actually 1600 square feet. What we want to
do here then is we want to have a 1 for exact or next largest instead. We'll Copy and Paste that.
So now it basically says if we need 1600 square feet we're going to need the meeting room. If we
were to go back here and reduce the number of attendees to 60 we will only need 1400 square
feet and we can see the True is now next to the conference room. So now what we can
do here is this value can instead of being a calculation can just be a link to the LOOKUP
and what are we going to LookUp. We're going to have True
in this column and we're going to get the value of this column. There we go. Zero dollars.
But if we go here to 70 attendees now it is going to cost us $500. In addition we
can duplicate this and do the same LookUp here but instead of returning the value in Column D
we'll return the value in Column C. So that gives us the name of the room. Let's reduce
the size of the text there and we can say put it underneath. $500 for the meeting room OR
zero dollars for the conference room OR $1200 for the event center. So it gives you an idea
of how you can use a LookUp Table. It's kind of hidden over here and get that information.
Let's add another thing to this. Let's add a Pie Chart. I'm going to select a 2D Pie Chart here and
it's going to say Add Chart Data. Well, what we're going to do here is we are going to calculate the
cost of everything. I'm going to click Add Chart Data. I'm going to select this cell, this cell,
and this cell. Done. Now it gives us the Costs broken up in a Pie Chart. Let's go to Wedges
here. I'll say I want Data Point Names. Notice they are all untitled. We'll leave the values as
percentages. Actually let's change them to currency there. We get the actual numbers.
Now let's go into each one of these and change what it looks like. I'm going to
go to this wedge here and this matches the drinks. So what I'm going to do with this is
I'm going to change the name. I'm going to change inside the quotes here. It has to be in quotes,
Drinks. That's done. I'm going to, with that still selected, go to Style and set the color to
match. I'm going to go to this wedge here, let's set the color to match there. That's for the $1200
for the event center. I'll change that and I'll go over there to wedge and I'll change this to Space
and then we'll select this one and let's change this to Food and to Style and we'll go and,
we're already using the right color there for that one. So that worked out well. So now we've
got this nice Pie Chart here. Let's go and change the label options. Get a little closer in there.
Let's change the wedges so they are separated a bit. Make it look really nice. We can even
go and add shadows and all sorts of things. Make it look good. I want to shrink this a bit
like that which is going to mean we're probably going to want to change the font size
like that. So now we've got this here.
Let's finish this up by adding some arrows. So I'm going to do an arrow here and have it point to
Drinks. From Attendees I'm going to Control Click on it and then make Editable and now I can make it
curved there. Now I'm going to click elsewhere and click back on it and
then do an Option Drag and another Option Drag and take each one of these, double
click on it to make it Editable and change where it goes and change that middle point
to make it more interesting. Then let's go and select all of these things here,
all of these different Tables, none of them should be Editable including the chart. Now we can go now
to Arrange Lock. Now they are all locked. Now you can't easily edit them unless you select them and
unlock them. But you can still change this one. So you can change this, change the party like that,
and based on that change you can see all the rest of this change. You could have done this
at as boring grid of numbers and such but instead now we have a nice colorful chart.
I'm sure with more time you can drag and drop things and setup shadows. Maybe use
pictures instead of shapes. Do all sorts of things to make this look even better.
So you can create more interesting ways to do your calculations not only to allow you
do them quickly but then you can make a pdf from this or print this out and
it's much easier for others to see exactly what you're doing and what the results are.
I hope this gives you some ideas of how to use Numbers creatively. Thanks for watching.