add-in combo box date excel 2000 excel 2002 excel 97 excel XP format formatting formula grouping loan - amortization macros subtotal sum excel tutorial workbook worksheet vba
Adding the Subtotal Icon to a Toolbar
Add the subtotal icon to the Excel menu bar if you use subtotals regularly. Right-click a toolbar, and from the shortcut menu, select Customize. Select the Commands tab, and from the Data category, drag the Subtotals icon to the Excel menu bar (see figure on next page). Click Close.
Adding Subtotals
Example:
Add a subtotal for each customer in a list of invoices. See the figure below.
Note
Before you begin to use the Subtotal technique, you must sort the data table according to the subtotal field. This field calculates a subtotal each time an item in a field is altered. Sorting the data prevents the calculation of unnecessary, meaningless subtotals.
1. Select a cell in the Customer Name field (column).
2. Click the Sort Ascending icon.
3. Click the Subtotals icon, or from the Data menu, select Subtotals.
4. In the At each change in drop-down list, select Customer Name.
5. In the Use function drop-down list, select Sum. (Of course, you are not limited to this function.)
6. In the Add subtotal to drop-down list, select one or more checkboxes to specify the columns that contain financial data and/or quantities. In the example below, you would select Quantity, Income, VAT and Total.
7. Click OK.
Result
The Subtotals technique automatically adds a SUM function to each customer. The Subtotals technique also adds Level Buttons to the left of the column labels row.
Level Button 1 Provides a total of the entire list. Hides all of the rows and only displays the grand totals.
Level Button 2 Only provides totals of visible subtotal rows. The rows of data are hidden.
Level Button 3 All rows are visible, including subtotal rows. See the figure above.
The Subtotals formula in cell F7 is =SUBTOTAL(9,F4:F6).
You can find a more detailed explanation of the SUBTOTAL function in Summing filtered data section of Chapter 18, Filtering.
Tip – Would you like to hide the subtotal level buttons?
Press Ctrl+8. To display the subtotal Level Buttons, press Ctrl+8 again.
Removing subtotals from a list
Select a cell in the list (after you have added subtotals). Click the Subtotal icon (or from the Data menu, select Subtotals), and click Remove All.
Tip – Quickly remove subtotals
Select any cell in the data area and press the Sort Ascending button. Excel will automatically remove the subtotals.
Adding subtotals according to two fields
You can add subtotals according to two fields, the primary and secondary sort order.
Example:
Add subtotals: The first level of subtotals is Market, and the second level is Customer Name.
1. In the Subtotals dialog box, click Remove All to remove the subtotals.
2. Sort the data in each field. The order for sorting is the opposite of the order of subtotals according to the primary and secondary fields. The primary field is Customer Name, and the secondary is Market.
3. Click the Subtotal icon. In the At each change in drop-down list, select Market.
4. Click OK.
5. Click the Subtotal icon. In the At each change in drop-down list, select Customer Name.
6. Clear the checkbox beside Replace current subtotals (keep subtotals for the Market field).
7. Click OK.
8. Click Level Button 2.
You can see the result in the figure below. There are four Level Buttons here compared to three Level Buttons when you subtotal according to a single field. Level Buttons 2 and 3 enable you to subtotal according to primary and secondary fields.
Adding additional subtotals and using additional functions
You can continue to insert additional subtotals by ensuring that the checkbox beside Replace current subtotals is not selected. In the Subtotals dialog box, select additional functions such as AVERAGE, COUNT, and others.
Adding subtotals to a date field
When discussing AutoFilter (see Chapter 18, Filtering), we explained how to use AutoFilter to handle dates. AutoFilter recognizes date formats and allows you to filter by format.
Problem: When working with Subtotals, you cannot use the technique of changing the date format.
Solution: Subtotals according to month and year.
Add two additional columns to the list. In the first column, enter the MONTH function. It will return the month of a date represented by a serial number. In the second column, enter the YEAR function. It will return the year corresponding to a date. Copy the formulas to all the cells in the columns.
Caution
Sort the list according to primary and secondary sort order, as explained earlier, before adding subtotals.
Printing
In the Subtotals dialog box, select the checkbox beside Page break between groups. Each group of subtotaled data will be printed on a separate page.
Copying consolidation of subtotals
Problem: You cannot use the standard copy-and-paste techniques to copy a consolidation of subtotals. If you copy and paste a list of consolidated subtotals, all of the data, including the hidden rows of data, are copied.
Solution: Select the visible cells before copying.
1. Be sure that the rows of data are hidden and that only the summary of the subtotals is visible on the sheet.
2. Select a cell in the data region, and press Ctrl+*.
3. To select visible cells, press Alt+; or press F5.
4. In the Go To dialog box, click Special.
5. In the Go To Special dialog box, select Visible cells only.
6. Click OK.
Now copy and paste the consolidation of the subtotals into a different sheet. Only the values of the data are pasted.
Applying colors to subtotal rows
We have already discussed the importance of applying colors to specific records. Applying colors to subtotal rows will allow you to easily distinguish them from the other rows of data in the sheet.
Applying colors to subtotal rows
Select the visible cells (see Copying a consolidation of subtotals). Click the Fill Color icon on the Formatting toolbar, and select a color. Display the hidden rows in the sheet, and check the results.
Applying colors to subtotal rows according to the subtotal level
Changing Styles
1. Insert subtotals.
2. From the Data menu, select Group and Outline, Settings.
3. Click Apply Styles.
4. Select a cell at subtotal level 2. From the Format menu, select Style.
5. For the style called RowLevel2, click Modify, change the style as desired, and click OK.
6. Use this technique to change the styles of all the subtotal levels.
Conditional Formatting
When you insert subtotals, some cells remain empty in the rows containing the subtotals. See the figure in Adding subtotals according to two fields.
1. Select Cell A1, press Ctrl+*, and select the data table.
2. From the Format menu, select Conditional Formatting.
3. In the first argument, select Formula Is.
4. In the formula box, enter the formula =ISBLANK($D1).
5. Click Format, and select the desired formatting.
6. Click OK.
7. Repeat these steps for the second argument.
8. Click OK.
Replacing/deleting the word Total
When you insert subtotals to a list, the word Total is added automatically to each subtotal.
To delete the word Total,select the column in which the text appears and press Ctrl+H (or from the Edit menu, select Replace). In the Find what box, type the word Total. Leave the Replace with box empty. Now click Replace All.
Caution
The SUBTOTAL function includes the characters that form the word Total. Therefore, it is important that you only select the column containing the word Total before you perform the search and replace operation. If you do not select a defined region, the characters in the word Total will be deleted from all of the subtotal functions. If you do this, you will end up with a SUB function instead of a SUBTOTAL function (if there even is such a thing).