Want to see immediate results? Set an area to the bottom and right of a massive spreadsheet where you can add totals and see them at a glance. In Episode #1447 Bill shows us how to do this with only a few keystrokes.
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
...This blog is the video podcast companion to the book, Learn Excel 2007 through Excel 2010 from MrExcel. Download a new two minute video every workday to learn one of the 512 Excel Mysteries Solved! and 35% More Tips than the previous edition of Bill's book!
"The Learn Excel from MrExcel Podcast Series"
MrExcel.com — Your One Stop for Excel Tips and Solutions. Visit us today!
Transcript of the video:
MrExcel Podcast is sponsored by Easy-XL.
Learn Excel from MrExcel, Episode 1447 – Add Totals Today's question is sent by Irving.
Irving says, he's an ancient CPA but a novice in Excel.
He has a huge spreadsheet with data going from B5 all the way out to AJ 2202.
He wants to add totals to the bottom row and to rightmost column out here.
So rather than try and work with this big spreadsheet, let's just talk about the concepts here on a nice small spreadsheet, so we can all see what's going on.
This trick works provided that row 1 has no blank cells, and the first column has no blank cells.
So I start here in cell B4, hold down Ctrl and Shift.
While those are held down, Ctrl and Shift, I press the down arrow, which selects that first column, then the right arrow, which selects all the data.
Why didn't I just do Ctrl *? Because that would have selected the region and week, and I don't want to do that.
So now, I actually let go of the Ctrl key.
Now I just have the Shift key held down, right arrow, down arrow, that selects one more row and one more column, and then I can go let go of Ctrl and Shift.
Now that I've selected this data set, watch how easy this is.
I'm going to press Alt =, all the equals.
And what does it do?
It throws some formulas all the way around the outside, so all those formulas are brand new.
I could have used the Sigma, the AUTOSUM button up here in the Home tab or back here on the formulas tab but Alt = is this shortcut for that.
All right.
Now, let's go to Sheet 2 and try this on the big data set, big data set here.
So we start up in the first numeric cell, Ctrl, Shift, down arrow, right arrow.
Let go of Ctrl.
I still have Shift, right arrow, left arrow.
And I'm actually having to scroll over here, so you can see that I've selected all of the data, plus one extra row, one extra column, Alt =. Aha, did you see those zeros?
That's kind of freaky.
I'm going to do Ctrl period (Ctrl .), Ctrl period (Ctrl .) to get down to that last cell.
It took so long to calculate this data because it was so big.
You see zeros there, but once they had time to calculate, we actually have real answers there.
So a couple of cool shortcut tricks there.
What I’m using, let me go back to the small data set, is Ctrl arrow key.
That was Ctrl, left arrow key will move to the edge of the data.
Ctrl, right arrow key will move to the edge of the data.
Ctrl, down will move down.
Ctrl, up will move up to the edge of the data really until it encounters a blank.
So from here, Ctrl, left will stop at B3, but by adding in the Shift, it says, hey, select everything from here down to the results.
So Ctrl, Shift, down arrow will go until it encounters a blank.
A good trick to know.
It gets you to the bottom of the data set very, very quickly provided there's no blank cells.
If you have a blank cell, put a zero there.
All right.
I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
Learn Excel from MrExcel, Episode 1447 – Add Totals Today's question is sent by Irving.
Irving says, he's an ancient CPA but a novice in Excel.
He has a huge spreadsheet with data going from B5 all the way out to AJ 2202.
He wants to add totals to the bottom row and to rightmost column out here.
So rather than try and work with this big spreadsheet, let's just talk about the concepts here on a nice small spreadsheet, so we can all see what's going on.
This trick works provided that row 1 has no blank cells, and the first column has no blank cells.
So I start here in cell B4, hold down Ctrl and Shift.
While those are held down, Ctrl and Shift, I press the down arrow, which selects that first column, then the right arrow, which selects all the data.
Why didn't I just do Ctrl *? Because that would have selected the region and week, and I don't want to do that.
So now, I actually let go of the Ctrl key.
Now I just have the Shift key held down, right arrow, down arrow, that selects one more row and one more column, and then I can go let go of Ctrl and Shift.
Now that I've selected this data set, watch how easy this is.
I'm going to press Alt =, all the equals.
And what does it do?
It throws some formulas all the way around the outside, so all those formulas are brand new.
I could have used the Sigma, the AUTOSUM button up here in the Home tab or back here on the formulas tab but Alt = is this shortcut for that.
All right.
Now, let's go to Sheet 2 and try this on the big data set, big data set here.
So we start up in the first numeric cell, Ctrl, Shift, down arrow, right arrow.
Let go of Ctrl.
I still have Shift, right arrow, left arrow.
And I'm actually having to scroll over here, so you can see that I've selected all of the data, plus one extra row, one extra column, Alt =. Aha, did you see those zeros?
That's kind of freaky.
I'm going to do Ctrl period (Ctrl .), Ctrl period (Ctrl .) to get down to that last cell.
It took so long to calculate this data because it was so big.
You see zeros there, but once they had time to calculate, we actually have real answers there.
So a couple of cool shortcut tricks there.
What I’m using, let me go back to the small data set, is Ctrl arrow key.
That was Ctrl, left arrow key will move to the edge of the data.
Ctrl, right arrow key will move to the edge of the data.
Ctrl, down will move down.
Ctrl, up will move up to the edge of the data really until it encounters a blank.
So from here, Ctrl, left will stop at B3, but by adding in the Shift, it says, hey, select everything from here down to the results.
So Ctrl, Shift, down arrow will go until it encounters a blank.
A good trick to know.
It gets you to the bottom of the data set very, very quickly provided there's no blank cells.
If you have a blank cell, put a zero there.
All right.
I want to thank you for stopping by.
I’ll see you next time for another netcast from MrExcel.
