Cell References in iCloud Numbers vs Microsoft Excel

I recently ran into a problem setting up a new expenses spreadsheet in Apple iCloud Numbers (the web version). I’m more familiar with Microsoft Excel, and that led me into error. So I wanted to quickly explain the problem and document the solution, as finding it should have been easier.

The Situation

My workbook has three sheets/tabs: Expenses, Income, and Net. You can see where this is going. My Expenses sheet has a cell (C2) which sums a column of individual expenses (B). My Income sheet has a cell (B2) which contains all the income. In my Net sheet, I wanted to pull both over and subtract expenses from income. That’s easy enough. In Microsoft Excel, the cell reference to pull C2 on the Expenses sheet into wherever I wanted it on the Net sheet would be this:

=Expenses!C2

Similarly, the reference for pulling B2 from the Income sheet would look like this:

=Income!B2

This syntax doesn’t work in Numbers. First, Numbers doesn’t use the exclamation point (!) to separate sheets from cells. Instead, it uses two colons, back-to-back (::). So, I tried in vain to do this, which fails:

/* Negative example: this will fail! */
=Income::B2

It won’t work because Numbers has the concept of tables. Each spreadsheet can have multiple tables. And you can create references from one table to another (on the same sheet or across sheets). These table names are displayed by default. But I usually turn them via the control in the Formatting pane, which is also where you can rename them:

Even if you don’t bother with multiple tables on a sheet, the default table on each has a default name (‘Table 1’). And it must be mentioned in the reference. And that is the secret sauce that makes both of these references work:

=Expenses::'Table 1'::C2
=Income::'Table 1'::B2

You don’t have to include the quotation marks around “Table 1” as you’re typing it. Numbers will handle that detail for you. But the space in the name is significant and can’t be omitted. Another perk: if you decide to give your tables less generic names at some point, the references in your formulas will update themselves automatically. You won’t break anything.

Tags: AppleexceliCloudNumbersSpreadsheets