Business Automation and Software Blog

Excel Tips and Tricks: Subtotal Visible Cells

Posted by Robert Baran on Fri, Jan 04, 2013 @ 02:35 PM

Excel Tips and Tricks / Business Reporting Question: How can I quickly calculate the total sales for each month and copy only the visible cells or subtotals to a new worksheet

Answer: By using the Subtotal command and visible cells only option

Why: If some cells, rows, or columns on the worksheet are not displayed, you have the option of copying all cells or only the visible cells. By default, Excel copies hidden or filtered cells in addition to visible cells. If this is not what you want, follow the steps in this tip to copy visible cells only. For example, you can choose to copy only the displayed summary data on an outlined worksheet.

Applies To: Excel 2003, 2007, and 2010

1. Refer to the data given below:

Excel Tips and Tricks

2. When a subtotal command is applied to the sorted data the result will be as below:

Excel Tips and Tricks

3. To display only the monthly totals select as below:

Excel Tips and Tricks

4. To select only the visible cells as given above;

a. On the Home tab, in the Editing group, click Find & Select, and then click Go To Special.

Excel Tips and Tricks
b. Under Select, click Visible cells only, and then click Ok.
c. On the Home tab, in the Clipboard group, click Copy.

Excel Tips and Tricks

Keyboard shortcut: CTRL+C

d. Select the upper-left cell of the paste area.
e. On the Home tab, in the Clipboard group, click Paste

Keyboard shortcut: CTRL+V

Contact PositiveVision ► ► for a Complimentary  Reporting Consultation

Topics: Business Reporting, Excel Tips and Tricks