Collection of Excel Tips and Tricks, Part 2

This is the second article about Excel tips & tricks. Let’s dive right in.

How would you put totals in E1:E7 and A8:E8?

Most people would click in E, press Alt/=, Enter, then either drag the fill handle down or double click the fill handle (so far, that would look like this):

then click in A8, Alt/=, Enter, and drag the fill handle to E8. The finished product:

Here's the trick. First, make this your selection:

That is, include the extra row and column. Then simply Alt/= and you get the same result as before! One keystroke!

Next – You have this worksheet:

(Note rows 12-111 are not shown, but are similar in structure – numbers and a subtotal every so often). Question: What’s the best formula for the Grand Total in B123? I’ve seen a formula such as this:

which works (selecting each of the subtotals in column B), but is somewhat error-prone (you might click on a wrong cell or miss a subtotal or two), I’ve actually seen a formula like this which took 2 lines in the formula bar!

What about =SUMIF(A1:A122,"Subtotal",B1:B122)? In case you don’t know the SUMIF formula, it takes a range to test (A1:A122), checking wherever that range contains the word Subtotal, and if it does, add up the values from the “sum-range”, B1:B122.

How about =SUMPRODUCT(N(A1:A122="Subtotal"),B1:B122)? In case you didn’t know the SUMPRODUCT formula, it takes a list of numeric arrays and multiplies them together then adds the result. The expression A1:A122=”Subtotal” returns an array of TRUE/FALSE, which isn’t numeric, so this is passed to the N-function which changes TRUE to 1 and FALSE to 0 so it’s multiplying an array like {0;0;0;0;0;1;0;0;0;0;0;0;0;1;…} by the numbers and wherever there’s a 1 it corresponds to the value in the subtotal in column B and winds up summing just the subtotal values.

Here's another one – from cell B123, press Alt/=. This gives

Excel finds them for you! Cool, but the best formula in my opinion is =SUM(B1:B122)/2.

What? How does that work? Well, each section contains a few numbers and the subtotal:

The numbers B1:B7 total 733. So the values in B1:B7 plus the number in B8 is 733 + 733! It’s there twice, as is each section. Cool, right?

Did you know you can make text boxes have multiple columns?

Once you have your textbox drawn, you press Ctrl/1 to get Format Shape. Click Text Options (as shown below) and then click the right-most icon under Text Options, then click Columns:

This brings up this little form:

where you specify the number of columns as well as the spacing between columns!

You have this data and need to remove everything before the colon and the space after that:

I’ll show 5 ways to do that. The first 3 involve formulas. First, to find the :, use this FIND formula:

Knowing the position, we want to keep the data starting 2 positions to the right of that, and we need to incorporate the MID function, which takes a part of a string:

Now, we have to replace the data in column A with the data in B which is copy/paste special Values. This leaves the values in column B, so we have to clear that:

Here’s another formula using a new function, TEXTAFTER:

We only need to first 2 parameters:

This is clearly easier than the MID and FIND.

Next, we’ll look at Data/Text-to-Columns:

The delimiter is the colon. Unfortunately, we can’t use the space as well – this is a one-character delimeter. We we get this:

You can see that the 2^nd column begins with a space, so it needs more “tweaking” – need another formula: =Mid(B1,2,100):

We might as well put it in column A!

Then, just delete column B.

Another way to do this is to use Flash fill, found on the home tab under the AutoSum:

 (also found in the Data tab in the Data Tools Group). Flash fill is a great tool in which you supply an example or 2 and Excel “understands” what you’re trying to do and fills in the rest. So, for the first example, we need to only type in “Do this” (no quotes, no formula):

Then invoke the Flash fill command (shortcut is Ctrl/e):

Here, you can just delete column A!

OK, not for my favorite way to do this: Select column A, and bring up the Replace command (Ctrl/H):

In Find what, enter &*: (including a space after the colon). The asterisk represents a wildcard, meaning any characters up to and including a colon and a space. Replace this with nothing. Click Replace All and you’re done!: