What if you need some extra text on the subtotal rows in Excel? Episode 1995 shows you how.
Episode recap:
If you need additional text on a subtotal row
Collapse to #2 view
Select the range where the text should be
Home, Find & Select, Go To Special, Visible Cells
Build a formula pointing one row above and use Ctrl+Enter
Bonus trick for using SUM on most totals and Count on one
Excel's method puts Total on one row and Count on another
Better: Put Sum in all columns, then Ctrl+H to Replace
Replace (9, with (3,
Custom number format to show Count: 47
Episode recap:
If you need additional text on a subtotal row
Collapse to #2 view
Select the range where the text should be
Home, Find & Select, Go To Special, Visible Cells
Build a formula pointing one row above and use Ctrl+Enter
Bonus trick for using SUM on most totals and Count on one
Excel's method puts Total on one row and Count on another
Better: Put Sum in all columns, then Ctrl+H to Replace
Replace (9, with (3,
Custom number format to show Count: 47
Transcript of the video:
Learn Excel from MrExcel podcast, episode 1995 - Fill text on Subtotal Rows!
I'll be podcasting this whole book, go ahead and subscribe up there, in the top-right hand corner to the playlist!
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday, we talked about subtotals, I have another question that comes up frequently with subtotals, you see, it's easy to add numeric subtotals, but what about a text subtotal?
So I already subtotal by customer, that gets the customer name down here, but then I need the sales rep to come down as well.
Alright, let me show you this, so first I'm going to collapse down to the #2 view, and I want the sales reps to be over here, so I'm going to select the range where the sales reps are going to go, so in this case row 49 to row 580, and, I'm going to either use ALT+; Alt+; is the shortcut key for Find & Select, Go To - Special, Visible Cells Only.
All right, that selects those visible cells, and right now I notice I'm in row 49, I'm in column A, I need a formula here that's going to point to A48, the row just above me, so =A48 , the most important part here, CTRL+Enter, will enter that formula all the way down, and what we get is the last sales rep for each of these customers.
I've seen this in a couple of different places, sometimes we have an account number and customer name, you have to subtotal by account number because the customer name changes a little bit from order to order, so you can subtotal by account number, and then pull the customer names out.
Lots of different uses for this great trick that makes use of the Go To Special dialog box.
Here's one that's not in the book but that comes up a lot.
What if we need to sum most columns, but count another column?
And the way that Excel, how's do this, is just horrible: Data, Subtotal, At each change in: customer, we're going to use the count function, and we're going to add that to the sales rep field, just to get a count over there in the left-hand side, and then uncheck "Replace current subtotals".
But this is horrible because now we get a count on row 49, and a total on row 50, no, no, so just remove all.
All right, here's the way to do this, we want the count over in column A, so I'm going to do Data Subtotal, At each change in: Customer, Use the sum function, and we're going to do it to Sales Rep, Revenue, Profit, and Cost.
Click OK!
All right, and then on each row we get this total, right?
That's not what we want at all.
But when we look at this form that's =SUBTOTAL(9, and if we would edit this, there are choices, #2 is Count, #2 & 3 is Count Text, so what we need there is a subtotal 3, instead of subtotal 9.
Select the whole column, CTRL+H, for Find & Replace, and find every occurrence of (9, and change to (3, Replace All, and that now becomes a count out there in column A.
Here's yet another bonus tip, if you want that the people to know that that's a record count, select those cells, Alt+; , CTRL+1 for Format Cells, Custom, and we'll say "Count", and then the number, like that, so you can see how many there are record count or whatever you need out there.
Alright so, lots of different tips in the book.
You can order this whole book right now, 40 greatest tips, 25$, great deal!
Episode recap: if you need additional text on a subtotal row, collapse down to the #2 view, select the range from the first text down to the last text, Home, Find & Select, Go To - Special, Visible Cells, or just simply Alt+; build a formula pointing one row above, and then most importantly CTRL+Enter says: "Enter this similar formula in all of the cells in the selection." Bonus trick for using sum on most totals, and a count on one, don't use that horrible Excel method where you get the total on one row and the count on another.
Put the sum on all columns and then CTRL+H Replace, replacing (9, with (3, . And then finally a custom number format to show the word, count any word before a number in Excel!
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
I'll be podcasting this whole book, go ahead and subscribe up there, in the top-right hand corner to the playlist!
Hey welcome back to the MrExcel netcast, I'm Bill Jelen.
Yesterday, we talked about subtotals, I have another question that comes up frequently with subtotals, you see, it's easy to add numeric subtotals, but what about a text subtotal?
So I already subtotal by customer, that gets the customer name down here, but then I need the sales rep to come down as well.
Alright, let me show you this, so first I'm going to collapse down to the #2 view, and I want the sales reps to be over here, so I'm going to select the range where the sales reps are going to go, so in this case row 49 to row 580, and, I'm going to either use ALT+; Alt+; is the shortcut key for Find & Select, Go To - Special, Visible Cells Only.
All right, that selects those visible cells, and right now I notice I'm in row 49, I'm in column A, I need a formula here that's going to point to A48, the row just above me, so =A48 , the most important part here, CTRL+Enter, will enter that formula all the way down, and what we get is the last sales rep for each of these customers.
I've seen this in a couple of different places, sometimes we have an account number and customer name, you have to subtotal by account number because the customer name changes a little bit from order to order, so you can subtotal by account number, and then pull the customer names out.
Lots of different uses for this great trick that makes use of the Go To Special dialog box.
Here's one that's not in the book but that comes up a lot.
What if we need to sum most columns, but count another column?
And the way that Excel, how's do this, is just horrible: Data, Subtotal, At each change in: customer, we're going to use the count function, and we're going to add that to the sales rep field, just to get a count over there in the left-hand side, and then uncheck "Replace current subtotals".
But this is horrible because now we get a count on row 49, and a total on row 50, no, no, so just remove all.
All right, here's the way to do this, we want the count over in column A, so I'm going to do Data Subtotal, At each change in: Customer, Use the sum function, and we're going to do it to Sales Rep, Revenue, Profit, and Cost.
Click OK!
All right, and then on each row we get this total, right?
That's not what we want at all.
But when we look at this form that's =SUBTOTAL(9, and if we would edit this, there are choices, #2 is Count, #2 & 3 is Count Text, so what we need there is a subtotal 3, instead of subtotal 9.
Select the whole column, CTRL+H, for Find & Replace, and find every occurrence of (9, and change to (3, Replace All, and that now becomes a count out there in column A.
Here's yet another bonus tip, if you want that the people to know that that's a record count, select those cells, Alt+; , CTRL+1 for Format Cells, Custom, and we'll say "Count", and then the number, like that, so you can see how many there are record count or whatever you need out there.
Alright so, lots of different tips in the book.
You can order this whole book right now, 40 greatest tips, 25$, great deal!
Episode recap: if you need additional text on a subtotal row, collapse down to the #2 view, select the range from the first text down to the last text, Home, Find & Select, Go To - Special, Visible Cells, or just simply Alt+; build a formula pointing one row above, and then most importantly CTRL+Enter says: "Enter this similar formula in all of the cells in the selection." Bonus trick for using sum on most totals, and a count on one, don't use that horrible Excel method where you get the total on one row and the count on another.
Put the sum on all columns and then CTRL+H Replace, replacing (9, with (3, . And then finally a custom number format to show the word, count any word before a number in Excel!
I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
