Using the Subtotal Option in Excel

Body

Overview

When viewing data in Excel, you can utilize the Subtotal feature to view data totals for various columns and at various levels. This tutorial shows you how to set up your view of the data, view subtotal information, and copy and paste only the selected data into a new sheet.

Instructions

Step 1: On the left side of the page, right-click the 1 row to select the entire first row of the worksheet.
Step 2: Click the Delete menu item.
Step 3: To select all cells within a worksheet, click the arrow in the top left corner of the rows and columns.
Step 4: Double-click the border between any two columns to auto-adjust all the column widths.
Step 5: To edit the format for multiple columns, select one or more columns with numeric data that you wish to convert to a money format. To select more than one column, press the left mouse button and drag the mouse to select several columns in a row.
Step 6: With the appropriate columns selected, click the Comma Style button.
Step 7: The numbers in the selected columns now have a monetary format.
Step 8: Next, you can subtotal one or more columns of data. Click the Data tab.
Step 9: The Subtotal window is displayed. Select the columns you want to subtotal by selecting the appropriate checkboxes.
Step 10: Click the Subtotal button.
Step 11: The Subtotal window is displayed. Select the columns you want to subtotal by selecting the appropriate checkboxes.
Step 12: With the appropriate columns selected, click the OK button.
Step 13: The Subtotal widget appears on the left side of the worksheet
Step 14: To collapse a section to only show its subtotal, click a Minus [-] icon to the left of the appropriate section under Level 2.
Step 15: You can collapse multiple sections to limit the amount of visible data by clicking the appropriate [-] button.
Step 16: Alternatively, you can click the number of the level that you want to collapse in the top left corner of the spreadsheet.
Step 17: The worksheet now only displays the subtotals for each section. To expand any of the sections, click the Expand [+] button.
Step 18: Select the cells that you would like to copy.
Step 19: Click the Home tab.
Step 20: Click the Find & Select button.
Step 21: Click the Go To Special... menu.
Step 22: The Go To Special window is displayed. Click the Visible cells only option.
Step 23: Click the OK button.
Step 24: Click the Copy button.
Step 25: You have successfully copied only the visible cells. Locate and select the cell where you want to paste the data.
Step 26: Click the Paste button.
Step 27: To adjust the width of an individual column, double click the right border of the column letter.

In this article, you will learn how to use the Subtotal functionality in Microsoft Excel. This information can be helpful as you view data produced by queries in OneSource systems, but it can be applied to other Excel spreadsheets as well.

1. Step 1

To begin, ensure that the top row of the worksheet is occupied by column titles . Depending on the report, you may need to delete the top row so that you can filter and sort the information in the columns.

In this example, row 2 contains the column titles (Contract, Project, LOC ID, etc), so you need to delete the top row.

On the left side of the page, right-click the 1 row to select the entire first row of the worksheet.

2. Step 2

Click the Delete menu item.

3. Step 3

The column names now appear in the top row.

You may wish to eliminate the excess whitespace in some of the cells. Excel gives you the option to adjust all cells at the same time.

To select all cells within a worksheet, click the arrow in the top left corner of the rows and columns.

4. Step 4

Double-click the border between any two columns to auto-adjust all the column widths.

Example shown here: Double-click the border between Column A and Column B.

5. Step 5

The column widths have been adjusted to better fit the existing data.

Some of your financial information may not be in money format.

To edit the format for multiple columns, select one or more columns with numeric data that you wish to convert to a money format. To select more than one column, press the left mouse button and drag the mouse to select several columns in a row.

6. Step 6

With the appropriate columns selected, click the Comma Style button.

7. Step 7

The numbers in the selected columns now have a monetary format.

8. Step 8

Next, you can subtotal one or more columns of data.

Click the Data tab.

9. Step 9

Click the Outline button.

Note : Depending on your display size or Excel version, you may not see this button. Look for the Subtotal link in the Data toolbar instead.

10. Step 10

Click the Subtotal button.

11. Step 11

The Subtotal window is displayed.

Select the columns you want to subtotal by selecting the appropriate checkboxes.

12. Step 12

With the appropriate columns selected, click the OK button.

13. Step 13

The Subtotal widget appears on the left side of the worksheet. The widget allows you to view different levels of data by opening or collapsing the rows using the Minus [-] buttons under each level.

Level 1 shows the data at the highest level - the Grand Total. If you collapse the Minus [-] button under Level 1 (located next to the last row in the spreadsheet), you would only see the Grand Total amount.

Level 2 shows data at the Subtotal level. You can click any of the [-] buttons under Level 2 to collapse sections down to the Subtotal level.

Level 3 shows all of the data, down to to smallest level (each row).

14. Step 14

To collapse a section to only show its subtotal, click a Minus [-] icon to the left of the appropriate section under Level 2.

Example shown here: Click the [-] button to the left of row 4.

15. Step 15

You can collapse multiple sections to limit the amount of visible data by clicking the appropriate [-] button.

Click the [-] button to the left of the total in row 7 to collapse the rows that total to row 7 (rows 5 and 6 in this example).

16. Step 16

Alternatively, you can click the number of the level that you want to collapse in the top left corner of the spreadsheet.

Example shown here: 2 button to collapse data to the subtotal level.

17. Step 17

The worksheet now only displays the subtotals for each section.

To expand any of the sections, click the Expand [+] button.

18. Step 18

You may need to copy and paste the data you see in your spreadsheet. However, if you just highlight a section to copy and paste, it will include all of your hidden rows.

If you wish to copy and paste only the displayed subtotals, you will need to follow these additional steps.

Select the cells that you would like to copy.