If you’re working with a Numbers document that has several sheets within it (designated by the tabs near the toolbar)…
…then you can use a cell in one of those sheets to calculate values in the others. In the case of my spreadsheet above, for example, I can take a number from my “Expenses” tab and one from my “Income” tab and use those to populate a summary cell under the “Budget” one. That’s cool, and here’s how you’ll do it!
Step one is to start creating the formula that’ll add up (or average, or what have you) the cells in question. To do so, click on the cell where you want your formula to go, and then click the “Insert” button in the toolbar, which looks like a plus button with a box around it.
As you can see above, you can then pick which formula you want; I’m selecting “Sum,” which’ll put that equation right into my chosen cell.
Now, in this case my Mac didn’t get smart and try to auto-sum anything, but if yours does, Numbers might just populate that cell with the references around it:
If that’s the case for you, just click the cell again and delete out the offending data, leaving your cursor in the middle of the blank formula, like so:
Once you’re ready to fill in the correct information, make sure you’ve got the formula up and waiting with your cursor in it if necessary, then select your other sheets one at a time and click on the cells from those sheets you’d like to use.
If you’ve done it correctly, the formula will stay active while you’re clicking around, and the cells you’re adding will appear in the box as they are above for me. When you’re done, click the green checkmark next to the formula box or press Return, and when you go back to the original sheet, you’ll see your finished calculation.
If you want to type this in manually, note that the structure here is =SUM(Sheet 1 Name::Table Name::Cell Name,Sheet 2 Name::Table Name::Cell Name). I mean, that’s gonna take you longer than clicking around will, but if you prefer doing things that way, go you!
Now, since I’m doing income and expenses here, I should probably go in and edit that formula so it’s not adding those numbers, it’s subtracting expenses from income. Adding my expenses to my income is…well, that wouldn’t give me a great idea of where my budget is. Anyway, I’ll do that by just double-clicking the formula cell and dropping that minus sign in instead.
Two things that are important:
- No, that’s not actually how much money I make in a month. Maybe in my next life I’ll be that wildly lucky.
- Yes, I realize that income minus expenses isn’t technically a “budget,” per se, but to be fair, I’m writing this at 2 a.m. in a semi-feverish daze. So pardon my late-night spreadsheet foibles, friends.
Hello all,
I am not sure how active this blog but I would try to ask anyhow. I just started to use numbers and dealing with lots of tables. I need to use data in structured tables. This example helps a bit but not completely. Let’s say I have totals per month, I need to recall the sum of January. Instead of giving the cell name (like “B5” in your example), I need to give the month name.
I was using the excel formula such as “TableName[[#Totals],[March]]”, so “totals” give the line information for the cell name while “march” gives the column name.
So I don’t need to change the formula each case when I add new line to the data table.
I would appreciate if you could give some opinions on it.
Thank you
Hello ays_ces and sorry for the late response. From what we’ve found, every row and column in Numbers will function as a named range in Excel if you have established a Header Row and Header Column.
If you wish to have one cell as a named reference, like ‘January’, then you may build up a two-cell table with the first cell serving as a header row and your value in the body cell below it.
When entering a formula, all you have to do to refer to a body cell is type its name from the Header Row. But before this, make sure to go to Numbers > Preferences, and select ‘Use header names as labels’.
Morning,
I am wondering if you know if there is an easy way to do the below:
Sheet 1 = a new column for each set of data.
Sheet 2 on wards = using the data from one column only
ie sheet 2 is the data from column 2, sheet 3 from column 3 etc.
Do I have to manually enter the formulas for Numbers to pull the cell values or is there a way of cutting & pasting that will auto-populate the correct column # to the correct sheet?
ie when pasting formulas it can handle subsequent rows / columns, can it do the same for sheets?
Thank you!
Hi Melissa, think you could help me figure out how to sum the same cell across a range of sheets in a workbook? Excel has the simple formula in this form [=SUM(Alpha:Omega!C22)], Alpha being the name of the first relevant sheet and Omega being the name of the last sheet in the workbook. The function sums the values of the cell C22 in Alpha, Omega and all the sheets between them.
Now I’ve spent quite some time cracking my brain trying to figure out same on Numbers but I haven’t been successful. Any help will be duly appreciated. Thank you.
Hello chibdo, sorry for the late response, but it seems like the most straightforward way is to add the name of the sheet, followed by an exclamation point, and then the name of the cell you want to add: =SUM(Sheet1! A14,Sheet2! A14).