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 (filter: To display only the rows in a list that satisfy the conditions you specify. You use the AutoFilter command to display rows that match one or more specific values, calculated values, or conditions.) 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 (outline: Worksheet data in which rows or columns of detail data are grouped so that you can create summary reports. The outline can summarize either an entire worksheet or a selected portion of it.) 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
