AutoSum All Around

The MrExcel podcast is sponsored by "MrExcel XL" book, with 40 excel tips + Excel cartoons, cocktails, tweets and jokes.

Learn Excel from MrExcel podcast, episode 1980 - Faster AutoSum!

Welcome back to the MrExcel netcast, I'm Bill Jelen. Well, before we can move on in the book, we need to build a quick little report here, and so, I'm just going to start from scratch, on a blank worksheet. And, across the top, I want to have week numbers, so, any word followed by a space and a number, is something that the fill handle will work with. So I'm going to put week 1 through week 5 at the top, and then, down the left-hand side, back from episode 1978, we created a custom list that will work with the fill handle, so we'll put that down the side, alright. And I have to type in some numbers, but I don't want to bore you by typing the numbers, so the fast, fast way to make fake data, is a great function called RANDBETWEEN, I am a huge user of RANDBETWEEN, because I'm always creating fake data sets, in my live power Excel seminars, so RANDBETWEEN 10,000, 20,000, is going to give us random numbers. Now this is great for me because I'm creating fake data. I don't know many uses in real life, I mean, I know, hey look, it's tempting, next year's budget process, really fast with RANDBETWEEN, but I don't recommend that at all.

Alright so, there are numbers I'm going to copy and paste as values. Alright, so now, if we were going to give this report to our manager, our manager would say "Well this is great, but can you give me totals?" Totals on the right hand side, and totals at the bottom. Alright so, normally, the first formula anyone creates as you come here, you either hit Alt+= , or the AutoSum, and it proposes a formula. You would accept that formula and copy it across. Also, up here, same thing, AutoSum will add across, accept that formula, and copy it down, but there's a much, much faster way to go.

I don't know who was crazy enough to try this the first time, but it's a beautiful trick: select all of your numbers, plus one extra row, plus one extra column, like that, and then, either alt equals, or the AutoSum, and BAM, they have the totals, all the way around the right hand side! I don't know why it is, but if I'm in a room full of accountants, there's always someone who wants to know: that corner cell, G11, is it adding down, or is it adding across! And I always say "It doesn't matter, does it?" It should be the same either way, but it is adding across. We ran into one guy who always adds across, and it adds down, and it has a little task to make sure that they're equal.

OK now, we have to do a little bit of formatting here. First off, I despise the comma style, this comma right here! The comma that's easy for us to use, it adds decimal places, it does weird things when there are zeros, so I'm going to CTRL+Z and undo that. There's nothing in this drop-down, that gives me the common style that I want. I find that I always have to go in to format cells. And the fast way to do this: You see these little arrows here? These are called dialogue launchers! I call it "Take me back to the old way!" Of course, we can press CTRL+1 to get here as well, but that dialog launcher takes us to the number tab, I go to number, thousand separator, zero decimal places, Click OK, like that. THAT's the comma style that I wish was here!

Now, a little bit more formatting we're going to do, just to make it look a little bit better, I'm going to choose the title, go to Cell Styles, just outside the view here is something called title, and then I'm going to choose all of my headings, and go to Cell Styles and choose Heading 4! There are a lot of cell styles in here, and I don't know why they think the Check Cells should be in grey, And Output Cells should be in that grey, I mean, I HATE all of these, except 4, heading 4 and title, are the two that i use over and over and over again.

In fact, you know, wouldn't it be cool, if we could take this comma style, and add it to the Cell Styles? Well you can. So I'm going to choose one cell that has that. I'm going to create a New Cell style, I want to call it CommaGood, and turn off everything, except for the number style. Click OK. Alright, so now, on a larger screen, I would actually be able to see CommaGood, it would be right there, in the visible part of the Cell Styles, because you get to see the first four. Now that's beautiful, except. The problem that I have is, it only works in this workbook!

Alright next week, we'll get to page 20 of the book, I'm going to show you how to make that comma style, CommaGood, be part of all future workbooks that you create. Can't wait 'till we get to that point, hang on for that. Alright, and then finally, just right justify our headings here, and it looks good, we are good to go! Now, tomorrow's episode will pick up with this report, and I'll show you a lot of cool tricks.

Today though, let's just take a quick Episode Recap: We use the fill handle with any word and a number. Fill handle with a custom list from episode 1978. We're using RANDBETWEEN for fake data, but not your budgets. AutoSum around by choosing the data plus the total row and a column, Then hitting Alt+= , we use the Heading 4 style and the Title style, talked about the number tab up here, that comma style, I hate it! I showed you how to use the dialog launcher, and then created a CommaGood as a new style. But it won't be until we get to next week's episodes, that I'm adding a comma style to all future workbooks.

If you're liking these books, there are many, many more tips like these, and many more as you can get. Buy the book, click that "i" on the top-right hand corner. Thanks to Bob Umlas for the AutoSum trick, he wrote that in one of his "Excel Outside the Box" books.

Want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!