Table AĬonstants for the SUBTOTAL() function 11: Count items in a group Table A lists the constants you need to know. SUBTOTAL() has two arguments: a constant that determines the mathematical evaluation and the range it evaluates. Understanding this function is important to getting the right results quickly. You can insert this function yourself, but the Subtotal feature is more efficient when working with large amounts of data.
When you use the Subtotal feature to insert a subtotaling row, Excel inserts a SUBTOTAL() function. When you're done, convert the range to a table. At this point, you can apply the Subtotal feature as you normally would. In Excel 2003, choose Lists from the Data menu and then select Convert To Range. To do so, right-click the table, choose Table from the resulting shortcut menu, select Convert To Range, and then click Yes to confirm this action. But you can still get the information you want by converting the table to an ordinary range. You can't use the Subtotal feature with Excel tables (the grownup version of lists) or with lists. Select the visible cells before applying formats and styles to subtotaling rows. As you can see in Figure G, only the subtotaling rows reflect the formats you applied in step 3. Press 3 in the side pane to review all the records.Press + to select only the visible cells.Press 2 in the Subtotal pane to the left to hide the detail records.Using the same feature used in #7, you can quickly format only subtotals, as follows: The easiest way to distinguish those subtotals is to format them, but doing so to each one individually would be tedious and inefficient. You might have noticed that the subtotaling results get a bit lost in the details. Figure FĬopying just the visible records is the trick to copying only the subtotaling rows. (The pasted set doesn't skip row numbers indicating hidden records.)Īfter hiding the details records, you can copy the subtotaling rows. As you can see in Figure F, Excel copied only the subtotaling rows. Press +C to copy only the selected visible cells to the Clipboard.This is the equivalent of pressing, clicking Special, selecting the Visible Cells option, and clicking OK. Select the subtotaling subset, C6:G62, shown in Figure E.To illustrate, click 2 to hide the detail rows in any subtotaling example (we'll work with the data in Figure B).
Fortunately, Paste Special offers an easy workaround. However, if you attempt to copy only the subtotals, Excel will copy the subtotaling rows and the detail records. You can review subtotals without the detail records by clicking 2 in the subtotal levels pane (#4). Use subsequent subtotal tasks to add different subtotaling functions to the same subset. Uncheck the Replace Current Subtotals option. Figure C shows the settings for adding a second subtotaling row for summing the units on order, shown in Figure D. You must uncheck the Replace Current Subtotals option when inserting subsequent rows or you'll end up with only the current settings. You can combine different functions by running two different subtotal tasks. Figure BĪ single row of subtotals is efficient. Figure AĬheck more than one field to view additional subtotals in the same row. Figure A shows the settings required to return an average for both the Unit Price and Units In Stock fields, shown in Figure B.
You can add subtotals that use the same function to more than one column, at the same time. You can hide the pane without removing the subtotaling rows. To temporarily hide (and unhide) this pane, press +8. While the Subtotal feature is active, Excel displays a pane to the left that shows three viewing levels: To do so, click Subtotal in the Outline group and then click the Remove All button at the bottom of the resulting dialog box. 3: Remove subtotalsĮventually, you might want to remove the subtotaling rows and the levels pane to the left of the sheet. Specifically, sort by the column that contains the grouping value before you execute the Subtotal feature. 2: Always sort firstīecause the Subtotal feature evaluates groups, you must sort your data first. Otherwise, Excel will evaluate slightly different entries as separate groups, and the subtotals will be incorrect for your needs. For instance, you wouldn't want to enter Baked Goods & Mixes, Baked Goods and Mixes, Baked Good & Mixes, and so on. Each group's value must be the same across all records. The Subtotal feature evaluates groups, so in order for subtotaling functions to return the correct results, you must use consistent values. These 10 tips will help you get the most from the feature once you learn the basics. You can get the same results manually, but Subtotal is quicker. Excel's Subtotal feature is geared for efficiency.