Copy only visible cells after applying "Subtotals"

Hello,

in an XL-list I need to copy only a block of subtotals to another area, 
without the detail rows in between. I believe there once was a command like 
"paste only visible cells" (or similar), but I can't find a solution. 
Anybody who knows how to do this ?

Thank you in advance,

H.G. Lamy 


0
H
11/16/2009 6:04:59 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1136 Views

Similar Articles

[PageSpeed] 44

You can copy the visible cells.

Show just the subtotals
select the range to copy
Edit|goto (or F5 or ctrl-g)|special|visible cells only

Then edit|copy
and paste where you want.



"H.G. Lamy" wrote:
> 
> Hello,
> 
> in an XL-list I need to copy only a block of subtotals to another area,
> without the detail rows in between. I believe there once was a command like
> "paste only visible cells" (or similar), but I can't find a solution.
> Anybody who knows how to do this ?
> 
> Thank you in advance,
> 
> H.G. Lamy

-- 

Dave Peterson
0
Dave
11/16/2009 6:29:05 PM
Dave,
that was it, many thanks!
Regards,

H.G. Lamy

"Dave Peterson" <petersod@verizonXSPAM.net> wrote in message 
news:4B0199F1.EBAD3361@verizonXSPAM.net...
> You can copy the visible cells.
>
> Show just the subtotals
> select the range to copy
> Edit|goto (or F5 or ctrl-g)|special|visible cells only
>
> Then edit|copy
> and paste where you want.
>
>
>
> "H.G. Lamy" wrote:
>>
>> Hello,
>>
>> in an XL-list I need to copy only a block of subtotals to another area,
>> without the detail rows in between. I believe there once was a command 
>> like
>> "paste only visible cells" (or similar), but I can't find a solution.
>> Anybody who knows how to do this ?
>>
>> Thank you in advance,
>>
>> H.G. Lamy
>
> -- 
>
> Dave Peterson 


0
H
11/16/2009 6:38:49 PM
Reply:

Similar Artilces:

Grand Totals without subtotals
Hi, I am preparing a number of tables, all of different sizes. I woul like to record a command in a macro that will calculate the Grand Tota of a specific column even though the number of rows in that column wil vary for each table I prepare. The 'subtotals' command is perfect fo this, except I do not require to subtotal any column at a specifi change, only Grand Total the entire column. I have tried using simple 'sum' forumla, however this doesn't seem to work in tables wit a larger/smaller number of rows as it selects the exact cell I recorde in the macro. Does any...

When using subtotals in Excel, how do you bold the dollar amount?
I am trying to create a report using Excel that I use subtotals for. I sort it by Department and I always get the department name bolded, but have to manually bold the corresponding dollar amount. Any suggestions? Here is one way... After creating all the subtotals, collapse the outline so that only the subtotal rows are visible. Select all the data, then press Alt-: (Alt and a colon) to select only the visible cells within the whole range you just selected. Another way is to press F5, then Special >> Visible cells only >> OK. Now apply Bold or any other formatti...

Excel, how copy subtotals without copying all lines inbetween?
In Excel, once I have used the subtotal function, how can I copy and paste the subtotals into a new worksheet or new file without also copying all the inbetween lines? Try this: Hide the detail rows Select the range you want to copy Edit>Goto> Click the [Special Cells] button Select: Visible Cells Edit>Copy Go to the destination range Edit>Paste Special>Values Is that what you're looking to do? *********** Regards, Ron "confounded" wrote: > In Excel, once I have used the subtotal function, how can I copy and paste > the subtotals into a new works...

How do I copy subtotals from excel without it also copying the det
Hi Reduce the amount of subtotal detail to what you require. Hit F5 and click on Special and then Visible Cells Only. You can now use Ctrl+C to copy this. Go to where you want to paste these totals, and use Edit|Paste Special|Values to paste. -- Andy. "slh" <slh@discussions.microsoft.com> wrote in message news:68A19CFA-9366-46A5-B021-7FCB228C7BF7@microsoft.com... > ...

How do I copy/paste subtotals without all the extra data?
I am trying to either do a vlookup or copy and paste the subtotals without getting all the data in the hidden rows? number of ways you can do this...i had the same question earlier. 1. highlight the subtotals results that you want to copy. hit F5 special and "visible cells only" then ctrl C, which sould highligh with white lines the subtotals you want to copy. then ctrl V t wherever you want. it'll get rid of all the data in the hidden row and leave only the subtotal results. 2. copy and paste from Excel into Word and back again. That should d the trick. 3. Copy a...

Subtotals
Hello, I use subtotals to help me sum the totals of the differing nominal accounts in my management accounts. I collapse the spreadsheet so I can see the totals of each nominal and then highlight each line so that I can format the subtotals making them bold and increase the font size to make them stand out once printed. There can be many nominals and lines that differs from time to time throughout the accounting period. Is there a way I can do this quickley? Regards, Chris. If you mean that you want to bold the amount/count etc for each line that says Total in the leftmost colum...

subtotals to a different page.
Is it easily doable to have the subtotals from sheet 1 calculate (show?) on sheet 2? I have used the Data->Subtotals to get what I want on the sheet but when I want to copy only the subtotals to another sheet, I get all the cells. I have been exporting the info to Access and then doing a selective import and filter and then exporting it back to Excel. Very round about and clumsy. Thanks you very much for looking and any answers. Lee Do your data|subtotals use the outlining symbols to the left to hide all the details select the range to copy Edit|goto|special|visible cells only edit...

Copy and Paste Subtotals only.
How do you copy and paste subtotals only? I use the Subtotal feature in Excel to determine the maximum values for different entities in Excel. I reduce the list to show the subtotals only and I want to copy and paste these values in a separate spreadsheet. However, when I copy and paste the maximum values, Excel pastes all of the data. Thanks, Jim -- Jim15 ------------------------------------------------------------------------ Jim15's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26300 View this thread: http://www.excelforum.com/showthread.php?threadid=4...

Pivot Table Percentages for Subtotals
In a Pivot Table I am trying to get Percentages of a subtotal such that the subtotal is 100%. See Below. I would like to maintain the format. I can get to the results before the %. That is the left 3 columns. I thought I could get the percentage with "Field Settings show data as:" command. But I have been unsuccessful. Any help would be greatly appreciated Dept...................Funct...........Jan.......... 1..................A...........10...........16.7% .....................B...........20...........33.3% .....................C...........30...........50.0% 1.Total..........

Copying only subtotals
In doing Data>Subtotals and then clicking on the 2 to show only subtotals, how do I copy and paste only those subtotals into another spreadsheet without it pasting in all the data in between? Thank you. Connie Sorry....I found my own answer here: http://www.dailydoseofexcel.com/archives/2004/04/19/copy-subtotals-only/ Excellent! "Connie Martin" wrote: > In doing Data>Subtotals and then clicking on the 2 to show only subtotals, > how do I copy and paste only those subtotals into another spreadsheet without > it pasting in all the data in between?...

PivotTable Automatic Subtotals
I have a PivotTable with Customer, Date grouped by Year and Month. The table has AutoFormat Report 6 applied. The Date group is set to show items with no data. This is important and must stay that way. I use the even spacing to create a static report with a difference line automatically added. There are some customers that do not have data for all listed years. So they may have information for 2004 but nothing in 2005. The table is displaying the grouping as such: Customer 123.00 2004 123.00 Jan 123.00 ...

copied subtotals only
Excel 2007 I had three very large sheet 70,000 plus rows. I arranged and subtotaled these as needed and then copied only the subtotals by using Find and Select > Go To special > visible cells only then copy and pasted this info to a new sheet. When I save these in the 97-2003 format i got a message that said it was too many lines and would be truncated. Since I only had about 15 rows of data on each sheet. Now when I do control > end the cursor goes to N65536. I know there is no data past row 30. The file says it's over 6 megs in size and it should only be a few hundred K at...

bring back ability to format subtotals in pivot tables
You used to be able to highlight all of the subtotals for a given field and with one click and then either shade, change font, etc. and that seems to no longer be possible. If someone knows how to do this, please let me konw! If you enable selection in the pivot table, you'll be able to select parts of the pivot table, and format them. To enable selection: From the Pivot toolbar, choose PivotTable>Select Click on Enable Selection "Pam-ADPT" wrote: > You used to be able to highlight all of the subtotals for a given field and > with one click and then eith...

subtotals copying
i would like to know an easy way to copy the subtotals - from using the subtotals function - into a new worksheet. You cannot just copy the page because the copy then includes all of the individual lines that you want subtotaled. Currently, the only way i know how to is to hold down the Control key and click each (subtotaled) individual line. This is rough when i have several thousand subtotals to copy. thanks Apply your data|subtotals Use the outline symbols to just show what you want select that range edit|goto|special|check visible cells only now do copy|paste Dave o wrote: > ...

Subtotals
I want to use the indexing feature provided in excel by the subtotal function in word for an A to Z directory, does anybody know if this i possible -- Message posted from http://www.ExcelForum.com Its hard to follow you, but I think you want to be able to collapse text in Word. The Excel sub-totalling function is not relevant, but maybe you can use the Word Outlining feature for this? "ianripping >" wrote: > I want to use the indexing feature provided in excel by the subtotals > function in word for an A to Z directory, does anybody know if this is > possible? > ...

Copying Subtotals?
How do I copy just the subtotal information to a new worksheet? Hi Robin, If you are just copying the information over, just copy it and on the new worksheet, do a paste special onto it Aloys "Robin Funnell" <Robin Funnell@discussions.microsoft.com> wrote in message news:FE504B23-96FB-4B0F-9D9F-366B1E8B1A7E@microsoft.com... > How do I copy just the subtotal information to a new worksheet? On your subtotaled page, summarize to the level that you want to copy (by clicking on the numbers on the upper left). Select all of the rows. Then select Edit - Go to, hit the ...

Copying Subtotals
I have a sheet to which I've applied the Subtotals command. I can click on the outline symbols to see just the rows with the subtotals but would like to take just those subtotal rows and copy their values to another sheet. How can I do that? Thanks Ray Kanner Select the visible part that you want to copy, press F5, click special and select visible cells only, copy and paste -- Regards, Peo Sjoblom "Ray Kanner" <kanner@us.ibm.com> wrote in message news:12eea01c3c0e5$2f764630$a601280a@phx.gbl... > I have a sheet to which I've applied the Subtotals > com...

Copy subtotals
I have a worksheet with subtotals, but I'd like to take just those subtotals to another sheet for further manipulation. Any ideas? Thanks, Diane Collapse the groups and select all the subtotals. Hit <F5> Click on "Special" Click on "Visible Cells Only" Then <OK> Right click in the selection and choose "Copy" Then navigate to wherever you wish, and then "Paste". -- HTH, RD ============================================== Please keep all correspondence within the Group, so all may benefit! ===========================================...

copy subtotals with detail
i have produce a table with group data as subtotals and require to copy one subtotal and the relating data(detail). i know how to copy the totals only but i would like to be able to do the opposite of this and copy everything relating to that sub total. Is this even possible? Thanks PS. another way to look at it is to delete the subtotal and the detail is delete also -- crunkenmonkey ------------------------------------------------------------------------ crunkenmonkey's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=26132 View this thread: http://www.excelfo...

Applying formulas only to the subtotals of a data list
I have a large data list with subtotals (in fact with nested subtotals). I have collapsed the list so that only the subtotals are showing. Next I set up a few columns to the right of the data list with the intent of applying certain calculations, again ONLY to the subtotals. Here lieth the problem: when I set up the formulas for the first subtotal and attempt to simply drag it down, Excel obviously assumes that the copied formula applies to the next row of the full-blown, uncollapsed data list. Is there any way to accomplish the task without having to manually create a separate table ...

copy subtotals to new excel 97 book
Book1 has 3 columns - area codes, telephone number and number of calls. when area code changes, there's a subtotal. I want to copy all the subtotals to a new Book without copying and pasting each individual subtotal cell one at a time. Go to your new spreadsheet and select the cell you want the data to be "copied" to. Press = Now open the spreadsheet that you're pulling the data from and click on the cell that contains the information you want. Press enter Voila! What you just did is create a link. Now when you edit the original spreadsheet, your new spreadshee...

Do nested subtotals have an errror in Excel 2003
I cannot nest subtotals in Excel 2003 without weird results I am summarizing a report by carrier, State, and Plan. Using the same tecnique as in previous versions, the nesting does not work. The second set of sub-totals ( State) seems to ignore the first level (Carrier). Excel 2003 was changed to handle subtotaling in a different way. You should get the former behavior by adding this dword key to your registry: HKEY_CURRENT_USER\Software\Microsoft\Office\11.0\Excel\Options\Excel97Subtotals and setting its value to 1. -- Jim "rlevitas" <rlevitas@discussions.microsoft.com&...

Subtotals format
I have a worksheet with a column for Name and a column for Amount. The subtotal in the inserted subtotal line does not show the subtotal amount in bold, though the subtotalled name is in bold. Is there some way to show the subtotalled amount in bold as well? Thanks for your help. Only by hand,..... or by code, if that's a viable solution. Vaya con Dios, Chuck, CABGx3 "Quimera" <chimera926@hotmail.com> wrote in message news:vz39n.63832$PH1.7410@edtnps82... >I have a worksheet with a column for Name and a column for Amount. The >subtotal...

Avereging the Subtotals in Reports
I am trying to do this in the Reports. Lets say, in the details, there are different account numbers with corresponding balances. These balances are totalled in the footer of that Group (month) =Sum([balances]). Then, I need to Average all the monthly totals in the footer of the Report. In the report footer, if i use =Avg([balances]) , it averages ALL the details, NOT the monthly totals. Any help? thanks. I would create a totals query that can be added to the report's record source query. For instance, if you want to find the Average of the Monthly Freight values fr...

Vertical Subtotals
Hello, I hope I am posting to the correct group. I have received a spreadsheet from someone that has subtotals in it (Data, Subtotals), but instead of the subtotals (the 1,2,3 in the top left corner) being horizontal they are vertical. Does anyone know how this is done? Could it be done through VBA? I have searched the internet as well as a number of books on Excel and can't seem to find this mentioned anywhere. Defintely has me intrigued. Thank you very much for any help, Michelle Data|Group It's not automatic like Data|subtotals, so they could have done lots of work. Gemma wr...