. Subtotal Video You can watch the steps for creating subtotals, and preventing duplicate grand totals, in the Subtotal video, show below. The written instructions are below the video. Your browser can't show this frame.
Here is a link to the page Sort the Data Before applying subtotals, the data must be sorted by the columns on which you want to base the subtotals. In this example, Category and Product will be subtotaled, so the data is sorted by those two columns. Apply the First Subtotal After the data is sorted, follow these steps to apply the first subtotal. In this example, the Category column will be subtotalled first. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals.
By Greg Harvey. You can use Excel 2010’s Subtotals feature to subtotal data in a sorted list. To subtotal a list, you first sort the list on the field for which you want the subtotals, and then you designate the field that contains the values you want summed — these don’t have to be the same fields in the list. Open Excel for Mac and enter two dates next to each other in cells A1 and B1. Next, in another cell, enter this formula Instead, you can force line breaks in Excel for Mac 2011 so your text looks better and is readable. First, on the Home tab of the Ribbon, click on Wrap Text to make sure text wraps in.
![Group and subtotal in excel Group and subtotal in excel](/uploads/1/2/5/5/125565512/331663417.png)
In the 'At each change in' box, select the first column that you want to base the subtotals on - Category in this example. Select the function that you want to use when totaling the columns. Select all the columns in which you want a subtotal.
Remove the check mark from 'Replace current subtotals' (unless there are existing subtotals that you want to remove). Check or uncheck the page break and summary below data options, based on your preferences.
Click OK, to apply the Subtotals. The data will show a subtotal after each change in the Category column, and there will be a Grand Total at the bottom of the data. At the top left, grouping buttons are added, so you can view specific parts of the data: 1 - Grand Total only 2 - Grand Total and Subtotals 3 - All data and totals You can also click the + and - buttons in the grouping bar, to show or hide sections of the data.
Apply the Second Subtotal Next, repeat the previous steps to apply the second subtotal. In this example, the Product column will be subtotalled second. Be sure to remove the check mark from 'Replace current subtotals', so the Category subtotals are not removed. After the second subtotals are applied, the data will show a subtotal after each change in the Category column, and each change in the Product column, and there will be a single Grand Total at the bottom of the data. Another grouping button is added at the top left of the worksheet.
Remove Subtotals If you no longer need the subtotals, follow these steps to remove them. Select a cell in the list, and on the Excel Ribbon, click the Data tab, then click Subtotals.
Click the Remove All button, to remove the Subtotals. Duplicate Grand Totals With some data, a second Grand Total might appear, if you add a second layer of subtotals. This occurs if there are errors in the columns that are being totaled. In the screen shot below, there are two Grand Total rows, because there is an error in the Total Price column Prevent Duplicate Grand Totals To prevent duplicate grand totals, use the IFERROR function, or IF and ISERROR functions, to handle the errors.
For example: =IFERROR(E2.D2,') If you can't alter the formulas to prevent errors, you can hide the duplicate Grand Total rows, after creating the subtotals. Download the Sample File Click here to. The zipped file is in xlsx format, and does not contain macros. More Tutorials.