Using the Subtotal Option in Excel

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.

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

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.

With the appropriate columns selected, click the OK button.

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.

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.