Grand Totals without subtotals
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
Hide the detail rows
Select the range you want to copy
Click the [Special Cells] button
Select: Visible Cells
Go to the destination range
Is that what you're looking to do?
> 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
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.
"slh" <email@example.com> wrote in message
...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
3. Copy a...Subtotals
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
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?
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
Thanks you very much for looking and any answers.
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.
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
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:
"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:
...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
> 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.
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:
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
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
> ...Copying Subtotals?
How do I copy just the subtotal information to a new worksheet?
If you are just copying the information over, just copy it and on the new
worksheet, do a paste special onto it
"Robin Funnell" <Robin Funnell@discussions.microsoft.com> wrote in message
> 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?
Select the visible part that you want to copy, press F5, click special and
select visible cells only,
copy and paste
"Ray Kanner" <firstname.lastname@example.org> wrote in message
> 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?
Collapse the groups and select all the subtotals.
Click on "Special"
Click on "Visible Cells Only"
Right click in the selection and choose "Copy"
Then navigate to wherever you wish, and then "Paste".
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?
PS. another way to look at it is to delete the subtotal and the detail
is delete also
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
Go to your new spreadsheet and select the cell you want the data to be
Now open the spreadsheet that you're pulling the data from and click on the
cell that contains the information you want.
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:
and setting its value to 1.
"rlevitas" <email@example.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,
"Quimera" <firstname.lastname@example.org> wrote in message
>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
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,
It's not automatic like Data|subtotals, so they could have done lots of work.