Debbie from Virginia asks about adding new calculations to the subtotal lines in a data set with subtotals. Episode 658 shows how to do this.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have a question sent in by Debbie from Virginia.
If you have a question for the netcast please feel free to send it in.
I'll try and get to you on a future podcast.
Debbie was actually at one of my power excel seminars, and I always show how to do subtotals there.
She said she had a little bit different problem though, so we added subtotles by account.
Let's say "Data" "Subtotals" and say it, every change in account we want to add the SUM function to "Profit" "COGS", "Revenue" and "Quantity".
Click "OK" and ofcourse that adds subtotals in very very quickly.
Great way to, to quickly add subtotals and as I show off in the power excel seminar, we have three new buttons over here, The group and outline buttons.
and when we press the number 2 button, what we get, just a great view of one line per customer But in this unfortunate situation we have the account numbers, and we really want to have the customer name.
Is there a way to copy data down to all of the subtotal rows from let's say the row above.
Well, there is a way to do it and here's what I'm going to do.
I'm going to select all of these cells here from the very first subtotal on row 6 down to the last subtotal on row 591.
Now normally if I would enter a formula here, it would get entered in all of the cells.
I really only want to enter this formula in the visible cells.
Those cells on the subtotal row.
Fast way to do that is to hold on Alt and hit ; That's the shortcut for "Edit" "Go to" "Special" "visible cells only".
"OK". Now I've selected just those subtotal rows.
I happen to be in cell E6.
I basically want a formula here that says give me the value from E5.
So = E5.
But I want to enter that similar formula in every cell in the selection, so I'm going to use Ctrl enter.
"Control enter" and very quickly it takes the data from one row above and copies it down.
Now you have to be very careful when you do this that you make sure to do that Alt ; Otherwise, you're going to fill in all of the cells all the way down and it becomes a disaster.
But provided you select the blanks and then Alt ; to select only those rows = and then whatever cell is immediately above the active cell.
"Control enter" will solve the problem Thanks to Debbie for sending that question in.
Thanks to you for stopping by. We'll see you next time for another net cast from MrExcel.
Today we have a question sent in by Debbie from Virginia.
If you have a question for the netcast please feel free to send it in.
I'll try and get to you on a future podcast.
Debbie was actually at one of my power excel seminars, and I always show how to do subtotals there.
She said she had a little bit different problem though, so we added subtotles by account.
Let's say "Data" "Subtotals" and say it, every change in account we want to add the SUM function to "Profit" "COGS", "Revenue" and "Quantity".
Click "OK" and ofcourse that adds subtotals in very very quickly.
Great way to, to quickly add subtotals and as I show off in the power excel seminar, we have three new buttons over here, The group and outline buttons.
and when we press the number 2 button, what we get, just a great view of one line per customer But in this unfortunate situation we have the account numbers, and we really want to have the customer name.
Is there a way to copy data down to all of the subtotal rows from let's say the row above.
Well, there is a way to do it and here's what I'm going to do.
I'm going to select all of these cells here from the very first subtotal on row 6 down to the last subtotal on row 591.
Now normally if I would enter a formula here, it would get entered in all of the cells.
I really only want to enter this formula in the visible cells.
Those cells on the subtotal row.
Fast way to do that is to hold on Alt and hit ; That's the shortcut for "Edit" "Go to" "Special" "visible cells only".
"OK". Now I've selected just those subtotal rows.
I happen to be in cell E6.
I basically want a formula here that says give me the value from E5.
So = E5.
But I want to enter that similar formula in every cell in the selection, so I'm going to use Ctrl enter.
"Control enter" and very quickly it takes the data from one row above and copies it down.
Now you have to be very careful when you do this that you make sure to do that Alt ; Otherwise, you're going to fill in all of the cells all the way down and it becomes a disaster.
But provided you select the blanks and then Alt ; to select only those rows = and then whatever cell is immediately above the active cell.
"Control enter" will solve the problem Thanks to Debbie for sending that question in.
Thanks to you for stopping by. We'll see you next time for another net cast from MrExcel.
