A PivotTable summarizes many rows by category. With Product, Category, and Amount columns, you can calculate sales totals per category without writing a formula for every row. Clear headers and numeric source values matter.
Prepare the source table
Give each column one clear header. Remove fully empty rows inside the table and avoid merged cells. Check that Amount contains numbers, not text that merely looks numeric.
Review apparent duplicate sales before deleting them. Two genuine transactions can have identical field values; removing one would understate the total.
Total sales by category
Select a cell in the source table and choose Insert > PivotTable in Excel. Confirm the range and place the result on a new worksheet.
Move Category to Rows and Amount to Values. If Excel chooses Count of Amount, inspect the source values, then change Value Field Settings to Sum. Compare one category against a manual calculation.
How to do it
- Check headers and number types.
- Choose Insert > PivotTable.
- Place Category in Rows and Amount in Values; verify that the aggregation is Sum.
Refresh after source changes
When the source changes, use Refresh on the PivotTable. If new rows fall outside the original range, update the source range or use an Excel Table so it expands with new rows.
SheetCommand does not create PivotTables. You can use it to inspect or clean the source file, then build the PivotTable in Excel.
Frequently asked questions
Why does my PivotTable count instead of sum?
Amount may contain text. Check the underlying cells and select Sum in Value Field Settings.
Can SheetCommand create a PivotTable?
No. This tutorial covers Excel; SheetCommand can help inspect and clean the source data.
Sources and further reading
SheetCommand
Check the data before your PivotTable
Get a source-data summary and review blank rows before creating the PivotTable in Excel.