Intro

This is the final post in a 3-part series on creating % of Total columns in Excel spreadsheets. A % of Total column displays values as a percentage of the total sum of the values in that column. I’ve already touched on how to do this with static data in Excel and also with a pivot table.

For those who might be new to ExcelWriter, ExcelTemplate, and data markers:

ExcelWriter’s ExcelTemplate method of creating Excel files is to design a template in Excel that contains data markers. Data markers are cell values that begin with %%= or %%=$ that specifies a database column, variable, or array to insert into the spreadsheet column. ExcelWriter does this by inserting a new row into the worksheet for each row of data being imported. When this happens, ExcelWriter updates the references to the cells that are affected by the row insertion.

Solution

For this exercise, I’m starting out with a simple table that’s has a Format as Table table style applied. Column C contains our sales data. Columnd D will be the % of Total column. The table header row is row 4 and the data marker row is row 5. Note that we have data markers %%=Data.StoreName and %%=Data.StoreSales to import the Store Name and Store Sales columns from the data source. The total row is computed with formulas =SUM(C5:C5) and =SUM(D5:D5). The data marker and total rows have been pre-formatted with currency and percentage number formats.

Our basic formula is % of Total = Individual Store Sales / Total Sales over all Stores.

Since ExcelTemplate dynamically inserts rows into the worksheet for each row of data in the data source, it updates any cell references that are affected by the insertions. In the percentages formula the Individual Store Sales needs to update for each row, but the Total Sales needs to remain constant.

Attempt #1

Let’s start with a formula: =(C5/C6) in cell D5, where C6 already contains the sum of the sales for all the stores. When ExcelWriter populates the template with data, ‘C5’ will update for each row (which is what we want), but unfortunately, so will ‘C6’, so the formulas will read: C5/C6, C6/C7, C7/C8 etc.

Initial Formula

Fully Populated

If we try to use an absolute reference on the total ($C$6), the formula will always point to C6, even when the new rows are inserted and the new total cell is C26.

Attempt #2

Let’s try using the SUM formula instead of referencing a cell with the total. If we use =(C5/SUM(C5:C5), we run into the same issue that we did with =(C5/C6). ExcelWriter will stretch the formula, but differently for each row: (C7/SUM(C7:C7), (C11/SUM(C11:C11).

We can try using an absolute reference ($C$5) instead of a relative cell reference (C5) for the first part of the SUM formula: =(C5/SUM($C$5:C5). This brings us closer to a solution, but not quite because the total sales value still isn’t pointing to the correct total row (26) for each data row:

Incorrect formula

Solution

The solution to this is to have a separate cell hold the value of the total sales over all the store. The cell will need to be above the data markers to avoid having it dynamically update. For example, if we put the formula =SUM(C5:C5) in cell C3, the range in the formula will expand to =SUM(C5:C25) when all the new rows are inserted.

 

Sum Formula Above

In percentage formula, the SUM formula is replaced with an absolute reference to the cell that contains the total sales value: =(C5/$C$3). This way, the C5 will be updated for each new row of data, the sales total in C3 will update correctly, and the percentages will be correct.

Formula In Table

Final Output

Related posts: