Stack tables to simulate an Excel sheet

Many users moving to Numbers from Excel miss Excel's ability to create Named Ranges within a spreadsheet. Actually, Numbers does have named ranges! And in some ways they more convenient than Excel.

The Numbers approach is to create separate tables for each discrete block of data, with Header Rows and Footer Rows (and sometimes Header Columns) containing formulas that do the work. In effect, each column and each row in a Numbers table behaves like a named range. Each column takes the name of the value you put in the Header Row cell for that column. You can then use that name, or simply the column letter, in formulas to refer to the range of body cells in that column. And each of these ranges expands dynamically! For example, a formula in a Header Row or Footer Row (or Header Column) will automatically adjust to include all Body Cells as new Rows or Columns are inserted or deleted in a table.

To take advantage of this feature, it often pays to break up your data into discrete Numbers tables. Then you can position the tables on your sheet so that they look like one big table.

In this example, stock is purchased from a number of suppliers and resold.

Subtotals are required, so we need Footer Rows to allow new rows to be inserted.

A separate table for each Supplier. Each table has a Header Row and a Footer Row.

Another table for the Grand Totals. No Header or Footer, just Cell Fill

Now hide the table names and slide the tables together so that they appear to be one table. Hint: Numbers > Preferences > Rulers > Alignment Guides (tick every box). Then, if needed, use the arrow keys on your keyboard to finally nudge them into alignment.

Now add another row to the 'Stock from Supplier A' table (KKK, highlighted in green for clarity). The tables below politely move down to make way for the new row. The totals automatically adjust.

Close enough to an Excel Sheet?