Mac Numbers Reference Cells in Different Sheets

Occasionally you will have a Mac Numbers file with multiple tables and sheets. And you want to include all of them into your formula. But you don’t know how. You are new to Mac Numbers. The instructions below will show you.

1. How to reference cells in a different table

First scenario, you want to reference a cell in a different table but on the same sheet. Because your reference cell and your formula are on the same sheet, you don’t have to spell out the sheet. However, you have to spell out the table name which contains the cell.  

The formula is “Table::Cell”. Take a look at the example below. The formula on table 2 references cell C5 on Table Test.

2. How to reference cells in a different sheet

What about referencing a cell in a different sheet? Simply you insert the sheet name in front of your reference cell and table. 

The formula is “Sheet::Table::Cell”. Take a look at the example below. The formula is on Sheet 2. But the formula calls cell D5 on Table 1 on Sheet 1.

If the cell to which you reference is on the same sheet as your formula, you can omit the sheet parameter. And Mac Numbers still knows where to find the cell.

3. How to preserve the row or column of the reference cells

The beauty of a spreadsheet application is that you can copy the formula to multiple cells. And the application auto adjusts the reference cells.

But sometimes you don’t want your reference cells to change. You want the formulas on multiple cells to reference that one fixed cell. So now you want to preserve the row or column of the reference cells. 

To do so, you insert “$” character to the row or column position of the cell. For example, if you want to preserve column C, you type “$C5”. If you want to perverse row 5, you type “C$5”. And you type “$C$5” if you want to preserve both row and column.

Personally, I hate typing. I type slowly and easily make mistakes. So I use the shortcut to avoid errors. I press Command-K (⌘–K). Mac Numbers preserves the column only the first time I press. Press those keys again and Mac Numbers preserves only the row. Press once more time and Mac Numbers preserves both column and row.