When Excel won’t total a PivotTable there is a reason why – and a workaround
When you are working with a PivotTable in Excel and if the data you are looking at comes from two different columns in the original worksheet you won’t be able to create automatic totals for it in the PivotTable.
So, as much as you ask Excel for totals, you’ll be disappointed.
However, you can create manual totals. To do this, click in the table and from the Options tab choose Fields, Items & Sets > Calculated Field. In the dialog, click in the Name box and type the name Overall Total. Click in the Formula box and write a formula that totals the fields you want to total. In our example it will be Net Hardware Sales plus Consulting.
To write the formula click next to the = symbol in the Formula box. Click Net Hardware Sales in the Fields box and click Insert Field. Add a + symbol to the formula, then click Consulting in the Fields box and click Insert Field.
The resulting formula will read: = ‘Net Hardware sales’ + ‘Consulting ‘
When you are done, click Ok and the calculated field will be added to the table and it will be visible alongside the other fields in the PivotTable.
