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.
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.
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.
Click the Delete menu item.
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.
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.
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.
With the appropriate columns selected, click the Comma Style button.
The numbers in the selected columns now have a monetary format.
Next, you can subtotal one or more columns of data.
Click the Data tab.
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.
Click the Subtotal button.
The Subtotal window is displayed.
Select the columns you want to subtotal by selecting the appropriate checkboxes.
With the appropriate columns selected, click the OK button.
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).
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.
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).
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.
The worksheet now only displays the subtotals for each section.
To expand any of the sections, click the Expand [+] button.
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.
Click the Home tab.
Click the Find & Select button.
Click the Go To Special... menu.
The Go To Special window is displayed.
Click the Visible cells only option.
Click the OK button.
Click the Copy button.
You have successfully copied only the visible cells. Locate and select the cell where you want to paste the data.
Example shown here: open a new sheet by clicking the Add Sheet [+] icon at the bottom of the window.
Click the Paste button.
The copied cells are displayed. You may need to adjust the column width to better fit your view.
To adjust the width of an individual column, double click the right border of the column letter.