Back to Index

EXCEL 2010-2007 : COPY OR FORMAT SUBTOTALS ONLY

I need to copy or format (bold, color ...) the subtotals only

Here below some subtotals (you get them - 2007 : DATA Tab - OUTLINE Group - SUBTOTALS Button / 2003 : DATA - SUBTOTALS) :

List with subtotals

Collapse to required level (+ and - buttons)
Select the range of cells that you wish to copy-paste or format in bold, color ...

By default, Excel includes the collapsed cells in the selection. This can easily be checked if you copy (CTRL + C). The dotted line goes round the whole range :

Excel includes the collapsed cells in the selection : the dotted line goes round the range of cells

Paste this range of cells and the collapsed levels are inserted ... Moreover results are lost :

The above copied range of cells is copied : ugly result !

Solution to "save" at least the values : when you paste, click on the paste tag (bottom right) and choose either values (with or without the source format) - number 1 or a link to which you will add the source format through the PASTE SPECIAL dialog box - number 2 :

the paste options tag and its suggestions

But if you need to only copy-paste the results (without the intermediate values) or apply format to those results, you need to LIMIT THE SELECTION TO THE VISIBLE CELLS ONLY.

Select the range of cells and then
HOME Tab - EDIT Group - SELECT AND SEARCH Button - SELECT CELLS Command

the special dialog box

The selection "drops" the intermediate levels as a simple CTRL + C shows it :

A CTRL + C shows that the selection now drops the intermediate levels

All you have to do : copy-paste (by default it will be VALUES only) or apply the required format ...

© Copyright : Office Doc / Web development by Office Doc