Get Creative With Numbers Spreadsheets

You can use Numbers for more than just a grid of data. You can create colorful and easy-to-use sheets with single-cell tables, text, graphics, lookups, charts and more.
You can also watch this video at YouTube (but with ads).
Video Transcript: 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.
Get Creative With Numbers Spreadsheets

Comments: One Response to “Get Creative With Numbers Spreadsheets”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information
