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:

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

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

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.

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

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