How to Summarize Data in Excel

By Editorial Team

Updated July 21, 2017

There are several ways to accomplish a single task in Microsoft Excel. Three different ways to summarize the data on your spreadsheet quickly and easily are listed below. Assume you have a dataset of "Customer," "Date," "OldSales," "NewSales" and "Total" in Row 1. These steps show you how to get totals by customer. Choose the method that best meets your needs and comfort level.

Use the Subtotals method. Choose one customer name by selecting a cell in column A. Click the "AZ" sort button--you can find this on the standard toolbar. Next, select "Data" and then "Subtotals." The Subtotals dialog box should appear. Make sure you've checked off "OldSales," "NewSales" and "Total." Click "OK" to see the summary data in bold on your spreadsheet. Press the "2" in the top left corner of your spreadsheet to see only the summary data.

Try the Consolidate method. Select a blank cell to the right of your data. Click "Data" and "Consolidate." In the "Reference" section in the "Consolidate" dialog box, select your entire table and click "OK". This produces a new row with summarized totals for each unique customer in Column A. Delete the "Date" field in the summary results.

Create a Pivot Table. Select a blank cell to the right of your data. Click "Data" and "Pivot Table and PivotChart Report." Select your entire data table and click "Finish." Choose to add "Customer" and "Total" to the new pivot table. Excel sorts the summarized data by unique customer.

×