Data Tables in Excel

Attached is an example of two different ways of tabulating data in Excel, which have different applications depending on what you want to do.

1) A simple PivotTable: To create this, just choose the corner cell of the PivotTable and go to Data -> PivotTable. Select the entire data range when prompted, including headers, and then drag and drop the relevant headers onto the left and top axes. Voila.
If you update the data, just right-click the pivottable and Refresh Data to update the pivottable

2) Data Tables: The real point of this example. It generates something like a pivot table, but updates in real time. You can overtype on the axes to change the categories, but the flipside is that if you want extra categories or to entirely change the axes you have to regenerate the table.
I have tables like this updating several times per second with real-time prices, so I find this to be more useful than having to refresh pivottables.

How is it done? Column H is the key. The formula in each pink cell says "IF [Product Type] = [Green Product cell] AND [Country] = [Green Country cell] THEN [Show the Sales value] ELSE [Show 0]"

You can see by changing the text in the green cells that the corresponding value of sales is displayed in the pink column wherever it is matched.

The data table takes two preset axes (K4:M4, J5:J7) and in the top left corner (J4) is the table formula. The table formula here is simply the sum of the pink column. Select from J4:M7 and choose Data -> Table. Pick the Row Input Cell as [Country] (H3) and the Column Input Cell as Product (H2). Lo and behold, the data table generates the appropriate sums. I have added on the totals myself.
Effectively the datatable tries every combination of the axis cells in the Row and Column Input cells and returns the value of the formula in the top-left corner as a result. By linking the Input Cells to the final result using the pink column, we generate the table.

If you update some sales data in the table, you'll see that the data table refreshes immediately because it is all calculated directly from formulae. Alternatively try reordering the axis headers just by typing over them.
You can put as many steps in before the calculation in J4 as you need; this is just a simple illustrative example.

zaza