Of course that’s true but I didn’t think of the title. However, I have had similar reactions from students and delegates on the odd occasion when I have demonstrated something in Excel that they hadn’t even dreamed of let alone done.

I have bought another book on Excel, this time it’s by Bob Umlas and it’s called, This isn’t Excel, it’s Magic! The book’s published by the International Institute for Learning Inc. I bought the second edition and that’s the one written for Excel 2007 whereas edition one, should you still need it, was written for Excel 2003. It cost me £9.89 plus p&p.

Unlike the John Walkenbach tomes I have reviewed before, this is the Twiggy or Kate Moss version.

Bob Umlas was the first or one of the first Excel MVPs: most valuable professional. This means he knows Excel inside out and he is prepared to share his knowledge freely every working day of the year. Bob works for the IIL and in addition to this book I recommend his FREE webinars on Excel. Go to www.iil.com and look for those webinars. I have attended a couple of Bob’s webinars already and they are well worth while.

The book, then, comprises 111 (cricketers beware!) top tips: things that you might not ordinarily know about Excel but once you’ve learned them you’ll wonder how you ever survived without them. The book is arranged as follows:

Section	Tips
Features	1 – 28
Formatting	29 – 35
Formulas	36 – 69
Keyboard shortcuts	70 – 79
Miscellaneous	80 – 90
Printing	91 – 95
VBA	96 - 111

I guarantee that you will know some of these things but let me give you just a few examples from the book: obviously for copyright reasons I can do no more.

6 Combining cells with Paste Special à Skip Blanks

Imagine you’ve got a spreadsheet like this:

For some reason your b, d, f and h have become detached or moved from column A and you want to put them back without dragging and dropping or even creating this formula in cell A2 that you then copy and paste into cells A4 and A6: =B1

No, do this:

à Copy the range B1:B7
à Select cell A2
àRight click cell A2 and select Paste Special
àClick Skip Blanks … bottom left of the Paste Special dialogue box
àClick OK

And this is what you should get:

Now delete the contents of column B. Try it: so simple. While you’re there, in case you have never used Paste Special before, I suggest you play around with it.

For example, imagine you have copied a table from the internet and you find that some if not all of the numbers in your sheet are being shown as text rather than values. Now, Excel is supposed to allow you to change from text to values in a trice but I find it sometimes just does not work so try this.

Firstly, you can see that the numbers in column B of the worksheet below are formatted as text because they are left and not right justified and that MIGHT be a clue that they are formatted as text.

The second clue can be seen at the bottom of the screen where you will see this: I have selected all five cells, B5:B9 and all that Excel is telling me is that there are five things to count.

Let’s imagine that you have tried everything you know but the numbers remain stubbornly formatted as text: type the number 1 somewhere in your worksheet, away from A4:B9 in this case and then

à select the cell with the number 1 in it, A2 in this case, then press Ctrl/+ (ie, hold down the Ctrl key and then press the + key then let go of both) to copy the number 1
à Select the range of text as numbers you want to convert to numbers: B5:B9 in this case
à Select Paste Special
à Select Multiply
à Click OK

That should solve your problem! Try it!

How do we know we have succeeded? Well, take a look at the bottom right of the screen and you will see this:

Which is showing the average of the five numbers, the count of them and the sum of them.

Tip 29 Formatting Comments

Like many people I have been using Comments in my spreadsheets for years. Up until now I just created a Comment and left it at that. I really never thought about formatting my Comments so I found this tip enlightening.

For any cell where you want to add a Comment (explanation, warning, formula …) just right click the cell and select Insert comment:

You will see something like this when you have finished:

Put your cursor over the cell which contains the Comment you want to edit, right click and select Edit Comment … like I just did in the screenshot above. Now click on the BORDER of the Comment and then select the Change Shape tool in Excel 2007 (in Excel 2003 select View … Toolbars … Drawing … Draw Menu … Change Autoshape). The Change Shape Tool? It’s a bit complicated first time round but the next screenshot will help:  having clicked on the border of your Comment

Click the Office Button …  … to reveal the Excel Options dialogue box:

You can see that I have now selected Customise and then chosen Drawing Tools| Format Tab from the Choose commands from drop down box. From there I highlight Change Shape and Add it to the Quick Access Toolbar (QAT) (stay with me, we’re getting there!)

Now you have added the Change Shape tool to the QAT all you do is take a look at the top right of the screen to find the QAT:
 

Now, making sure you still have the border of the Comment selected, select the Change Shape tool in the QAT and choose the shape you would like your Comment to become. Like these:

Change background colours and gradients and so on and you’ll have your own custom Comments.

By the way, the QAT part is an added bonus because you can put any command there that you like … moreover, once you have finished with the Change Shape tool, you can remove it from the QAT by right clicking it and selecting Remove … as simple as that. Again play around with this and if you get stuck there is a brilliant video to watch from Excel’s Help (in the 2007 version anyway).

Type quick access toolbar in full into the Help Search bar and you will be able to play that video.

Tip 67 Using notes inside formulas via the N-function

converts non number to a number, dates to serial numbers.True to 1, anything else to O. Well, you can take advantage of this knowledge inside a formula. For example:

New Table features

If you have entered a table (new to 2007), it is automatically assigned a name, like Table1 etc. You can reference parts of the table in formulas. If you enter the beginning of a formula for a table and include the opening bracket, like =table1[, Excel provides an intellisense tool tip:

To get the headers, you could ctrl/shift/enter =table1 [#Headers] if there were more than one column in the table, or simply enter the formula (without ctrl+shift) if there was only one column:

Here are the last 2:

Tip 69 Excel 2007 comes with 5 new built-in functions (excluding the new CUBE
functions):

SUMIFS, AVERAGEIFS, COUNTIFS, AVERAGEIF and IFERROR. Let's take a
brief look at them in action:

Please note: if you do buy Bob Umlas’ book you will notice that the answers I am presenting below are different from Bob’s. The reason is that Bob has used a larger database than the one he has presented in the book. Hence, I have copied his data from page 143 only whereas Bob’s range of data extends considerably further than the range we see on that page.

The COUNTIFS function is like the SUMIFS and AVERAGEIFS functions, except the range to sum (or average) is not included. It's still pairs of criteria, but they're merely counted:

You can calculate that 1533/3 is 467.67, verifying that the SUM/COUNT is the AVERAGE!

I think what follows is fantastic as I have been using ISERROR for years now: compare the old with the new and appreciate the savings in time and memory!

The last new function is the IFERROR function, which basically shortens something like this:

to this:

The syntax is IFERROR(value,value_if_error) which means that if it is NOT an error, it returns the value of the calculation, as in the first longer example above. In English, it's "If the VLOOKUP returns an error, show "Not Found." Otherwise, do the VLOOKUP."

But this requires you to code the same VLOOKUP formula twice in the same function. The IFERROR avoids that!

Keyboard Shortcuts

Tip 70 Ctrl/Shift/~,1,2,3,4,5,6 for quick formatting

These simple keyboard shortcuts can help with number formatting (here, the value 1.5 was entered in the cell):

Tip 71 Borders from Keyboard

Ctrl/shift/7 creates an outline border
Ctrl/shift/- removes border

Inserting date and time shortcuts
Ctrl/; will insert the date in the m/d/yyyy format
Ctrl/: will insert the time in h:mm AM/PM format

Tip 75 Ctrl/[ more powerful that its equivalent, Go To Precedents

If a cell has =SUM(B2:E4), then with that cell selected, use F5 (Goto)/Special and select Precedents. The result is that cells B2:E4 will be selected. Ctrl/[ does the same thing.

However, if a cell has a link to a cell in a closed workbook (the entire path would be shown in the cell), then using F5 (Goto)/Special/Precedents would give an error message:

The equivalent keyboard shortcut is ctrl/shift/[.

Tip 76 Show Corners of Selection Easily

Press ctrl/full stop (period) successively selects corners of the selection, even if the entire selection is large.

Conclusions

There you are mostly from the book, but a fair amount from me. This book is so inexpensive that it is worth buying, reading and having on your shelf for ready reference. It’s not an earth shattering book except that perhaps one in five of the tips are not to be found anywhere else or not to be found easily. Even a seasoned user of Excel spreadsheets could well find something new here.

 

 

Compiled by Duncan Williamson
October 2008

Reference

Extracted from Bob Umlas (2007) This isn’t Excel, it’s Magic! 2/e IIL Publishing

© Webmaster Duncan Williamson 2008