Simply click in the data set (or the header row), click the Data tab in the Ribbon, click Subtotals in the Outline group and then click Remove All.īelow is the Subtotal dialog box (note the Remove All button at the bottom): Remove subtotals when you no longer need them 3 displays data, subtotals and grand totalsĥ.The levels show the following if you insert one set of subtotals: If you insert subtotals once, Excel creates three levels in the outline, but Excel can display up to eight levels. You can click the numbers on the top left to display different levels in the outline (also called collapsing or expanding the outline). When you insert subtotals using the Subtotal feature, Excel applies an outline and an outline pane appears on the left of the worksheet. So if you want to subtotal by city, sort by city first and then add subtotals at each change in city. Always sort first and then add subtotalsĪlways sort your data set first and then subtotal by the first sort key.
It's best to clean up these types of inconsistencies first before inserting subtotals. For example, if you have city data with Toronto spelled as Toronto, Tornto or Tononto, you would get 3 different subtotals. In order to use the Subtotal feature effectively, the data set must be clean or consistent. If you see Table Tools Design or Table Design in the Ribbon, your data set is an Excel table.īelow is the Table Tools Design tab in the Ribbon:ĭata sets should be set up correctly with unique column headings or field names in the first row, no merged cells and no blank rows or blank columns within the data set. To see if you data set is a table, click in the data and take a look at the Ribbon. If your data is an Excel table (created using the Table feature on the Insert tab or the Format tab in the Ribbon), you can't use the Subtotals feature. You can't use the Subtotal feature with Excel tables Recommended article: No Mouse? Using Keyboard Only Navigation in Microsoft Office (Part 1: The Ribbon)ĭo you want to learn more about Excel? Check out our virtual classroom or live classroom Excel courses >Ĭheck out the following tips when working with the Subtotals feature in Microsoft Excel. You can insert subtotals in Microsoft Excel data sets or lists using the Subtotal feature (which appears on the Data tab in the Ribbon) to summarize data.
That is, I would like the values in the first subtotal row to be (9+10)/2 (40+35)/2 and (28+24)/2 and in the second subtotal row to be (11+11+10)/3 (30+25+25)/3 and (24+19+20)/3.10 Useful Tips for Summarizing Data Using Excel's Subtotal Featureīy Avantix Learning Team | Updated April 9, 2021Īpplies to: Microsoft ® Excel ® 2010, 2013, 2016, 2019 and 365 (Windows) Is there any way to calculate the average sale value per day, instead of calculating the average sales value per line entry in every week? However, when trying to calculate the average sales per day (in each week), the average sales per line entry is give. The sum subtotals in an Excel pivot table are calculate as expected, with the correct sales totals per week (the line entries sum up to total sales for a day and the daily totals sum up correctly to weekly totals). In the sample data provided there are multiple line entries per day as well as multiple days per week.