My boss wants me to set up a basic spreadsheet where we can enter transactions by customer and tally the total amount of sales. I know nothing about spreadsheets. Can you give me a rudimentary primer for Apple Numbers on my iMac, please?
You came to the right person because it turns out that I love working with spreadsheets. I think it’s from getting an MBA and having to create dozens and dozens of them; ya just had to “master” the process. Adding formatting, colors, formulas, it all adds to the utility and readability fo spreadsheets too and if people can understand them, odds go up that they’ll use them correctly too!
Numbers is a good place to start too because Apple has not only created a quite competent spreadsheet program, but added lots of design features to make it easier and more understandable than Microsoft Excel or Google Spreadsheets. Even built-in help that defines and explains formulas right on the screen. To start, I always just create a default spreadsheet and enter some of the basic data. Then you can apply formatting to entire columns, rows, groups, etc.
Here’s my sample customer purchase spreadsheet:
By default the grey background row and column would get some simple formatting, but everything else is just an automatic format, so sequences of letters are left justified while digits are right justified.
I probably should have put the column headers on the very first row so they’d be against the gray background, but let’s just apply some formatting to the three fields specified instead: Date, Customer, Amount. As with everything in Numbers, you’ll make the selection, then apply the formatting or changes you want. In this case, I’m going to click and drag to select Date, Customer and Amount, then on the right size go to “Text” option on the formatting sidebar:
If you aren’t seeing any of these formatting options, then make sure you’ve clicked on the Format button on the very top right (adjacent to Sort & Filter) to display them.
Two common formatting choices for text that’s a column head are to center the contents and apply bold to everything. The former’s easy, choose the second Alignment option and everything should snap into the center immediately. Bold is a bit more confusing because it shows that they already are all in bold! Numbers is getting a bit confused because the first cell has bold applied, so just click on the “B” until all three show up in bold.
Now click and drag to choose all of the Amount field cells, then switch from “Text” to “Cell” in the format pane on the right. You’ll see this:
Notice you can add background fills and borders around the cells (which are much more visible when you print your spreadsheet!) but we’re going to stick with the numeric formatting. Turns out that Numbers knows about a lot of different cell formats, as you can see once you click on the “Automatic” menu option under Data Format:
As highlighted, since we’re working with currency, just choose “Currency” as the data format option and it’ll add the decimal points, the cents, even add commas if the amounts are in the thousands!
Here’s how those two changes improve things:
I’m sure you’re looking at the pop up “fx” box in the cell that’s supposed to have the summary of amounts. I clicked on that cell then typed in “=”. That’s it. Typing in the equals sign tells Numbers that you’re entering a formula and it pops up the formula entry box. More importantly, on the right side, it shows a long list of potential functions:
You’ll need an advanced degree to figure out some of them, but click on one and an explanation will appear immediately under it, which is terrific. For summation of values, however, it’s really easy to add: type in “SUM”. Then just click and drag the set of cells you want summarized! It will then looks like this:
Correct cells highlighted? Great. Just press Return or click on the green checkmark button and the formula will be locked into the cell and the value shown:
Looks good, actually, and $904 is the total amount due from our four customers. Change a value and everything updates automatically. Nice.
Now there’s a lot more that you can do with Numbers but this will get you started with the basics of spreadsheet formatting. Have fun!