Designing Better Numbers Spreadsheets With Text And Shapes

Creating good spreadsheets isn't just about getting the formulas right. By adding some text and design elements you can make it clear to someone else, or yourself in the future, what to do to use the spreadsheet and understand how it works.

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 to add text and design elements to make your spreadsheets better. 
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 let's say you have a spreadsheet that does some calculations and you plan on handing this off to somebody else to fill out some data. Or perhaps you want to use it yourself but maybe far in the future and you may not remember what everything is for. Well, you can add design elements here to make it easier to use in the future. 
So in a simple mortgage calculator here I'm already indicating which elements can be changed by keeping those as white cells with plain text in there and then making everything else a darker background color making these bold. These are all calculations. They are going to use formulas. So only these cells are the ones that should be changed. 
Now to show what should go in one of these cells it might be useful to add some comments. You may be tempted to use the Comment button right here. So you can select something like this, add a comment, and then put something like or something more helpful than that. Now when you do that normally you don't see the comment. You just see that there is a comment with this little indicator here. If you move the pointer over it it appears. So it's not that useful. Also notice Reply and Delete here. This is really meant for marking up the document while editing it. So you're building the document. Maybe you pass it around to some people and they add some comments and you go back and forth. This is what this type of Comments is for. Not for using the spreadsheet later. In fact it's even difficult to do so. So here I've got the Stepper here for increasing or decreasing this value. But you can see the comment is actually going to cover it. So don't use these kinds of comments to actually make your spreadsheet easier to use.
One idea would be to simply use another column. I'm just going to grab this handle here and drag it to add one more column there. I'm going to then stretch it out so it's nice and long. It's going to auto-fill some stuff. Let's just quickly delete that. Now I can put some text right here. So I may enter text like this. So now I've got some useful text here that describes what should be entered there. I don't need to have long descriptions here in the Header column because this will fill in the details. Then whoever is using this will know what to put here based on the description to the right.
But you could do better in the formatting. For instance, I could select all of these cells here and let's get rid of the background behind these cells. I'm going to go to Format, Cell and then change the fill to No Fill so that looks a little bit better. Now let's select them all and do Format, Cell and change the border. Get rid of all the lines. So I'm going to select All Borders there. Then change the style to No Border. Then if I do that I'll see the left border is has been removed from there so I'm going to select all of these, or better yet select these, and then change the right border. Then change the border style to Default style. You can see now that it looks a lot better. These kind of hang out there outside of the table. They're part of the table but by visually separating them you can see clearly they are comments.
I can make this even better by selecting all of these and then going to Format, Text. Let's change the text color to something like this. Make it a little bit easier to see that these are separate. If I really wanted to I could add things like Arrows here. Let me go and bring up the Emoji Viewer with Control Command Space and then search for Left. I can add something like an arrow like this or this right here. You can see how that makes it really nice to see exactly what this comment is about. You can add those for each one of these. Notice how the title now stretches across all of this. So maybe I could left justify the title to make that a little easier to see. Or I could get rid of the Table title altogether.  Add one as a text box or perhaps insert a row at the top. I'm going to change that to a Header clicking there. Selecting one Header Row. I'm going to then select these two cells, Control click on them, and merge them like that. Now I've got a single cell here. Let me change the background color of this other Header Cell here to No Fill. I can center this. There. So now I've got a very nice looking table.
But that's not my only option. Let's go back to how it was looking before. So here's the original table. Now instead of adding more cells and columns and all of that I can just use Text and Shapes. Let's add a piece of Text right here and then just put the description in there. I can drag this wherever I want and it just hangs out there by itself.  I can change its style here. Format, Text Style and let's change the color to something like this. I can now add something like Arrows. Let's create a shape here. Add an arrow.  Have it point to something like that. The other end goes there. Let's go and make that a little bit bigger. Let's change the color. Now I've got something like that. Instead of just a text box you can also use a shape. So I'm going to select Shape here. Let's do a rounded rectangle. I'm going to paste the text in here. Let's set the text to actually be black and the style of the shape to have the fill of color like that. Now I can drag it out and make it the right shape I want. Of course I can style the text in there as well. This is useful because you can have multiple arrows. I'm going to Option Drag this arrow here and take it right there. Point to that part. Point to this part. Maybe it would even look better if we reversed the arrows. I'll select both of those. I'll change the end points to go in the opposite direction and then we can have it point like that. Let's reduce the size like that so it makes it kind of clear exactly what this calculation is doing. 
If you want you can select all of this and then you can go to Arrange & Block. That way it's not easy to change these. Of course it's easy to unblock them but going back in just to edit these values it's harder to accidentally drag or change what's here. Of course you can arrange this anyway you want. Use different kinds of shapes. Use different kinds of arrows, different types of lines. You could end up with something like this. 
So you can probably do a better job than me using the right colors, the right shapes, carefully placing the arrows. The idea is to end up with a spreadsheet that not only performs some useful functions but also explains what it is doing so whoever is using it can see exactly how it works. Hope you found this useful. Thanks for watching.

Comments: 2 Responses to “Designing Better Numbers Spreadsheets With Text And Shapes”

Sanjoy Das

3 years ago

SQLite integration into Numbers is extremely powerful
Any tips on setting up a DB, then updating from Numbers

Gary Rosenzweig

Sanjoy: Sorry, I don't know any way to integrate Numbers with an SQL database.

Comments Closed.

Designing Better Numbers Spreadsheets With Text And Shapes

Comments: 2 Responses to “Designing Better Numbers Spreadsheets With Text And Shapes”

Welcome to MacMost

Free Weekly Newsletter

MacMost Online Courses

Keyboard Shortcuts PDF

Connect with MacMost

MacMost Sections

Popular Tutorials

Information