Excel Grand Totals: You DON'T Need Long Formulas

By Bob Umlas, Excel MVP

Still using long formulas to create grand totals in Excel? There is a better way!

Suppose you have a worksheet which looks something like this:

grand-1

Clearly, what’s needed is the grand total in cell B38. Notice that there are several subtotals in the list. The formula in cell B9, for example, is =SUM(B4:B8).

How many of you would use the formula =B3+B9+B14+B20+B25+B32+B37 to solve the problem?

grand-2

 

 

 

 

I’ve seen formulas like this many many times, and often a lot longer because the list is longer. It does work, producing the correct answer, but it’s tedious to do and certainly subject to errors because you might click the wrong cell.

In this blog I’ll describe a few alternative ways to do this, leaving the best for last!

The first way is probably the easiest, but the formula is perhaps just as long. While the cell B38 is selected, press Alt/= (that is, hold the Alt key down and press the = sign). This creates this formula:

grand-3

Wow! That’s cool! Excel “finds” all the other Sum formulas and picks out the correct cells! Not likely to produce an error! But there’s no hunting for the correct cells to use. One down side to this technique is that it won’t work on a range which is horizontal, only vertical, as this one is.

Let’s look at some shorter ones:

grand-4

This doesn’t look much shorter, but it’s pretty much the same size no matter how many cells are involved. So it’s definitely shorter than something like

=B1+B6+B11+B17+B20+B26+B32+B37+B40+B44+B50+B54+B60+B66+B81+B90+B99+B106+B112+B121+B126+B130+B134+B154+B159+B144+B176+B188+B192+B198+B206

!!

And I’ve seen longer! (4 lines worth!)

OK, what is SUMIF? It’s a built-in Excel function which works like this: Wherever the first range (A1:A37) contains the word “Subtotal”, add the values from the 2nd range (the 3rd parameter). The actual syntax is:

grand-5

That’s pretty cool too! But this could also lead to an error if somewhere in column A the word Subtotal were misspelled, or contained a space at the end (“Subtotal ”)!

OK, ready for the winner? Make sure you’re sitting down first.

How about =SUM(B1:B37)/2:

grand-6

What? How can that work?

Well, let’s look at the formula in B37, for example:

grand-7

That’s adding up the values in B33:B36. Those values total 2,131. If I add the values in B33 thru B37 (including the subtotal), I get 4,262 – the 2,131 from B33:B36 and the same 2,131 from B37. So I’m adding the values in twice! So if I divide by 2, I get the right answer! Each of the sections containing the subtotal had the values in there twice – the individual values plus the subtotal! So dividing by 2 solves the problem!

This makes the technique work horizontally as well, using a formula something like =SUM(A2:AK2)/2.

Here’s the same data we were looking at, but turned on its side:

grand-8

Clearly, we get the same answer!

No more lonnnngggggg formulas to do this task!

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.


How to Hide and Unhide Columns and Rows

By Bob Umlas, Excel MVP

Here we’ll be exploring the various ways to hide and unhide rows and columns in Excel. The bulk of the discussion will address columns, but it applies to rows as well (unless otherwise noted).

How to Hide Columns

You can change a column width by dragging the column separator (the little line between columns) left or right:

hide-1

In the following figure, the column separator between D and E is clicked (notice the cursor shape), the mouse is held down, and dragged to the right:

hide-2

If you drag it to the left enough it will narrow or hide the column (when the width becomes 0 it’s hidden):

hide-3      hide-4

When you let go of the mouse there’s a double-line indicating that column D is missing (this shape varies with the version of Excel used):

hide-5

But when headings are not shown (from the View tab you can turn them off), there’s no indication a column is missing. Here, it looks like cell D3 is selected, but in fact it’s E3 because column D is hidden:

hide-6

Another way to hide the column is select any cell in the column, say D1, then on the Home tab, in the Cells group, there’s a Format dropdown which has access to the Column Width:

hide-7

When you issue the command, you get a dialog with the current width, like this:

hide-8

You can type in any number from 0 to 255, however when you type 0 the column is hidden.

You can also hide a column via Home/Format/Hide & Unhide/Hide Columns.

There’s also a shortcut to hide a column. Whichever column is selected will become hidden when you press ctrl/0 (a row will hide when you press ctrl/9).

All of the above applies to more than one column as well. If you select A1:E1 and use any of the methods, all 5 columns will hide.

How to Unhide Columns

Okay, how about unhide a cell? Again, there are several ways. It’s not as easy to select a hidden column, however! So you can type the cell address into the name box to get the cursor into the column. Or, you can press the F5 key (Go To) and type in the cell address. Here, D1 was entered into the name box:

hide-9

The cell D1 became selected. You can unhide this with the column width command we saw above, change the 0 which will be there to 8.43, or any number, etc. This is not literally an unhidden cell– it’s resetting the column width to a number. When you unhide, it will restore the width to what it was before it was hidden! You can unhide it by Home/Format/Hide & Unhide/Unhide Columns:

hide-10

Ctrl/shift/0 (ctrl/shift/9 to unhide rows) will do the job as well. You can also select the columns before and after the hidden column and unhide them (only the hidden one will be affected). Here, C thru E is selected:

hide-11

And finally, you can carefully position the cursor a little to the right of the D-column separator so it looks something like this:

hide-12

then drag to the right which will reveal column D!

Lastly, suppose you have text like this:

hide-13

And you want to widen column C so it all fits. There are a few ways to do this. You can drag the column separator between C and D to the right and visually adjust it. You can double-click the column separator which does a “best-fit” for the longest cell in that column. Or you can select a few cells (vertically or horizontally) or the entire column, and use Home/Format/AutoFit Column Width:

hide-14

Enjoy!

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.


Excel's JUSTIFY Command

Excel’s JUSTIFY Command

By Bob Umlas, Excel MVP

Have you heard of the Justify command? Let’s take a look at it here.

Suppose you have the data below:

justify-1

and you’d like to make it look like this:

justify-2

You may not know that Excel has this ability already built-in! It’s called the Justify command, found on the right side of the Home tab:

justify-3

What this feature does is reflow text. To use it, you have to make a specific selection first. In the above example, the selection had to be A1:C3 or longer (A1:C4, A1:C100, etc.):

justify-4

From this selection, issuing the Justify command would reflow the text.

You could have selected just columns A and B and reissued the same command for this result:

justify-5

(The resulting selection, A1:A4, happens after the command is completed. The entire columns A:B were selected prior to using Justify.

It aligns with the column widths. Notice the difference with different column widths here (column B is wider, column C is pretty narrow), where A:C was selected:

justify-6

OK, here’s another trick: if you select A1:D3 and issue the justify command, you get this:

justify-7

It reflows text from 3 lines into 2 lines! If you had selected A1:G3, you’d see this:

justify-8

Pretty cool.

Let’s look at a new example. Say you have the data in column A, below, and you want to make it look like cell C1:

justify-9

If you select A1:J7, for example (remember, there’s nothing yet in C1) and issue the command, you’d see this:

justify-10

So here’s a trick to overcome that. In cell C1 we’ll enter a formula to both change A1 to text and insert a comma. That formula is =A1&”, “, and we fill down to C7:

justify-11

We have the makings of a solution, but we don’t want that ending comma in cell C7, so we change that formula to =A7&”” which appends an empty string but it’s now text:

justify-12

We’re not yet ready to use the Justify command because, if you read the message above, it also can’t contain formulas! So we have to copy C1:C7, then paste only the values back into the same place. Ctrl/C can copy, and we can use the Paste Special command to paste just the values:

justify-13justify-14

 

 

 

 

 

 

 

 

 

So this:

justify-15

becomes this:

justify-16
And we’re complete!

[trx_infobox style=”regular” closeable=”no” icon=”icon-desktop”]Learn more about IIL’s Microsoft Excel training >> [/trx_infobox]

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.

 

 

 


Demystifying Excel VLOOKUP

By Bob Umlas, Excel MVP

Using VLOOKUP in Excel seems to be a mystery to many. But you do it all the time in real life—you look up a word in a dictionary and scan a little to the right to read the definition. Well, it could be the same thing in Excel:

excel-vlookup-1

If you wanted to use Excel to look up a word in this dictionary, the syntax would look something like this:

excel-vlookup-2

The syntax is =VLOOKUP(what,where,returning which column,approximate match?)

“What” you’re looking up is cell G1, or Banana.

“Where” you’re looking it up is in the dictionary which is A1 thru B5 (A1:B5)

“Returning which column” is from the second column, or 2 (not “B”, because Excel’s syntax requires a number, not a letter).

“Approximate Match” is FALSE because we want an exact match.

Let’s say I looked up “Grape” in this dictionary:

excel-vlookup-3

The #N/A indicates it’s “Not Available”. Therefore “Grape” is not in this dictionary.

You may be wondering, “Why would anyone want an approximate match?” Well, suppose you’re looking up your salary in a tax table, and suppose you make $75,000. Here’s the fictitious table:

excel-vlookup-4

Your $75,000 isn’t in the table. It would be wrong here to get a #N/A error! You do owe taxes! You owe 12% of the first $60,000, and 12.75% of anything over that. So we’d use the TRUE instead of FALSE in the VLOOKUP formula (Since it’s assumed TRUE if omitted, we can just omit it):

excel-vlookup-5

The .12 is the 12% found next to the $60,000 item (cell B4) because we didn’t ask for an exact match. (When you do ask for the approximate match, the lookup column must be in sequence). Note that Excel does not also pick up the formatting, so the 12% is shown as .12. The value is the same, of course. Also note that the range we’re looking at is A2:C7 – we don’t include the headings in row 1.

In the following figure, we see that the 12% is for the first $60,000, so we need to “find” that $60,000:

excel-vlookup-6

Then these 2 values in column F need to be multiplied together to get the base tax amount:

excel-vlookup-7

The remaining part is to find the amount over the $60,000, then the tax on that overage. This is seen here:

excel-vlookup-8

This is a little trickier as we’re looking at a few calculations simultaneously. The difference is E1 minus F2; the $75,000 less the $60,000. This is places in parentheses so we can multiply that difference by the tax found in the 3rd column, the VLOOKUP(E1,A2:C7,3). This gives the result of $1912.50.

The total tax can now be found by adding the overage to the base:

excel-vlookup-9

For you adventurous folks, here’s the same answer all done in one cell:

excel-vlookup-10

A few final notes:

If the table started in column J, the column reference would still be 1, 2, or 3, not 10, 11, and 12, (the column number for J, K, and L) because the formula is looking for the relative column in the range. The above formula would be:

excel-vlookup-11

Learn more about IIL’s Microsoft Excel training >> 

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.

 


Excel Basics: Separating First and Last Names in One Cell

By Bob Umlas, Excel MVP

Here in my first blog post, I’m going to show you how to separate first and last names in one cell using Excel. Imagine you have this worksheet, where the name is in column A:

excel-1

 

 

 

 

 

 

 

 

 

 

 

 

Your job is to separate the first and last names so you wind up with this:

excel-2

 

 

 

 

 

 

 

 

 

 

 

 

Then you can delete column A.

The 3 ways I’m going to explain here are:

  1. Formulas
  2. Text-to-Columns
  3. Flash Fill (For Excel 2013 or later)

The formula way is the most difficult, but also perhaps most instructive. To do this, I’ll describe the use of some built-in functions, namely FIND, LEFT, and MID.

Let’s also assume that we know the format is always last name, comma, and first name. Let’s look at cell A2 in detail. If we know the position of the comma, we can determine the location of the first name (the last name always begins the cell).

The FIND function does this job. The syntax (rule) is =FIND(what you’re looking for, in what string): If we put this formula in cell B2: =FIND(“,”,A2), we’ll see 6:

excel-3

 

 

 

This is because the comma is in the 6th position of “Jones, Bob”.

Since the comma is always followed by a space, we then know that the first name is 2 characters after the comma. The MID function takes characters from the “middle” of a string of characters, and the syntax is =MID(cell,start,length). So if in cell C2 we put =MID(A2,B2+2,255), we’ll see this:

excel-4

 

 

 

Notice that we added 2 to the value in B2. The 255 is arbitrary, but certainly large enough for the largest first name, and also the maximum number allowed in the MID function!

We can combine these last 2 cells’ solution into 1. Instead of using B2, we can put what’s in B2 (the FIND function) and substitute it:

excel-5

 

 

 

So we are no longer using cell B2! We can delete column B:

excel-6

 

 

 

OK, now we need the last name, and put it in cell C2. We know that we want all the characters to the left of the comma. That’s =FIND(“,”,A2)-1. Note the “-1”. We don’t want to include the comma. This is where we need to use the LEFT function. It takes this syntax: =LEFT(cell,length). Well, we know the length is that FIND function with the -1:

excel-7

 

 

 

Practically done! Select cells B2:C2, and double-click the fill handle (that’s the cursor when it sits on top of the little square at the bottom right of the selection):

excel-8

 

 

It will copy that formula down to the bottom:

excel-9

 

 

 

 

 

 

 

 

 

 

 

 

 

 

OK, we’re ready for the 2nd technique, Text-to-Columns.

First you select the names in A (from A2:A24), then invoke the Text-to-columns command found on the Data tab:

excel-10

 

 

That will bring up a dialog box from which you can choose delimited or fixed width:

excel-11

 

 

 

 

 

 

 

 

 

 

 

In this case, it’s delimited (by a comma). So click the Next button to see step 2 of 3:

excel-12

 

 

 

 

 

 

 

 

 

 

You have to click the Comma checkbox, as it isn’t checked initially. Now click Finish. (You could click Next, but there’s nothing on step 3 that applies to this situation. Text-to-columns can be used in many other situations as well.

When you click Finish, you’ll see this:

excel-13

 

 

 

 

 

 

 

 

 

 

 

 

You have the names split, but you have last then first, not first then last. You can select B2:B24, click Cut (or Ctrl/x), then click on A2 (which contains “Jones”), then use Right-mouse click and select Insert Cut Cells:

excel-14

 

 

 

 

 

 

and then you’re done.

The third technique is only if you have Excel 2013 or later. It’s called Flash Fill. You start by typing the first result (Bob) in cell B2, and continue typing the 2nd result in C2. As you do, you’ll see Excel suggest the rest in a light gray color, because it recognizes the pattern:

excel-15

 

 

 

 

 

 

 

 

 

All you need do is press enter, and you’re done.

Then go to cell C2 and type the first 2 last names in C2 and D2:

excel-16

 

 

 

 

 

All there is to do is delete column A!

The only disadvantage to the latter 2 techniques is that they’re not dynamic. That is, if you change A2 to Williams, Jared, the data in cells B2 and C2 won’t change. But the formula-based version will change.

Learn more about IIL’s Microsoft Excel training >> 

Bob Umlas has been voted an “MVP” (Most Valuable Professional) by Microsoft each year since 1994 for his contributions to Excel online forums and he is known world-wide for his expertise. As an MVP, he meets yearly with fellow MVPs at Microsoft’s headquarters in Redmond, where he has access to the product developers. He has also been a beta tester for new versions of Excel since version 1.5 and is the author of several books including This isn’t Excel, it’s Magic! (available on the IIL Bookstore), Excel Outside the Box, and More Excel Outside the Box.