Excel’s formulas are powerful. As an example we can create one formula that can be copied down to add a percentage calculation to a subtotalled list. This formula demonstrates a couple of useful techniques.

The image below shows a subtotalled list.

This has been created using the Subtotal option in the Data ribbon tab.

We want to add a column that shows the percentage of the totals for each line item. You could enter a different formula for each section or you could use one formula that will work in all the cells in column C.
One formula that works is cell C2, that can be copied down, is

=B2/VLOOKUP("*Total",A2:$B$1000,2,0)

This demonstrates two interesting formula techniques.

The first is the use of the wildcard character * in the VLOOKUP formula. The text “*Total” refers to any text that ends in Total. Using this as the entry to lookup will identify the next total row after the current cell.

This will return the total amount relating to the current cell. VLOOKUP always finds the first entry in a table and ignores the others.

This assumes there are no detail entries in column A that end in the word Total. Only the total rows end in Total.

The second technique is the use of a relative reference (A2) for the start of the VLOOKUP table reference. This means the table reference will start in column A of the row that contains the formula. As the formula is copied down the column, the table reference reduces one row at a time. The final cell of the table reference is fixed. The fixed reference is arbitrary ($B$1000). It only needs to be past the end of the subtotalled table.