This is a pocket-sized guide to 100 things you can do with Excel, and how to do them. When I say “pocket-sized”, I mean it: the book is smaller than A5, and so can easily fit into an inside jacket pocket or a small handbag.

At the end of the book is a handy list of keyboard shortcuts. More about this later.

So what kind of tips are included, and how useful are they? Well, they range from the dead simple, like adjusting column widths, to more complex ones, like pivot tables and data tables.

I’m very familiar with Excel, especially older versions, but I’ve only recently upgraded to Office 365 and am gradually getting to grips with new (to me) functionality. Therefore I was delighted to discover, within just a few minutes of opening the book, of an option called Flash Fill. I hadn’t heard of this before, but before I explain what it is, here’s the problem it solves.

Suppose you have a list of student names, with first and surnames in one column. What you really want to do, for the purpose of being able to sort the names alphabetically by surname, is separate out each name into two cells.

Obviously, you could cut and paste each surname, one at a time, and paste it into the neighbouring cell. Ideal if you have 20 hours of free time a week and nothing much to do!

I’ve always used a splitting formula to do this. However, despite having spent years using Visual Basic for Applications and pretty complicated formulae to achieve wonders in Excel, I can never for the life of me remember what the formula is: I always have to look it up. (Usefully, it is one of the “tips” included in the book.)

But Flash Fill enables you to achieve the same thing with a few key strokes. Once you have typed in the first name of the first full name in the cell to the right, Excel learns what you want to do. Very handy!

Another challenge you might have is duplicate entries, that is where you have entered a name or another item twice. The mistake is easy to spot if the list is fairly small, but becomes progressively more difficult as the number of rows containing the data exceeds around 10.

Enter Conditional Formatting, particularly the option for finding duplicates. As you can see, Excel shows the duplicates in all their unglory.

If you’re a head of department or subject leader, one of the things you will probably have to do is keep track of spending. When I was a HoD I found using the formula SUMIF() very useful, because that enables you to see how much you’re spending on different types of products. SUMIF() is one of the formulae included in the book, as well as another one, SUMIFS().

An Excel spreadsheet can be far more than a souped-up calculator. It’s really a modelling tool. Thus, going back to the previous example, as a HoD you might want to test different scenarios:

What if we get a smaller capitation allowance than last year?
What if the price of one of our software subscriptions goes up?
What if my capitation remains the same, but because of my subject’s amazing popularity the number of students opting for it rises by 20%

You can test for all these kinds of things without having to create multiple spreadsheets. One approach is to use data tables, for example. In the (embarrassingly simple) table above, I’ve used a data table to see how much money I’d have left if I spent various amounts on software.

There is also a section on calculating with dates. This can be surprisingly difficult if you’re not sure how dates are represented in Excel, but the author explains this simply and well.

Why might you need to calculate with dates? An obvious use might be working out how long it is before a deadline, like completing your end-of-term reports. This is illustrated in the screenshot below:

One of the strengths of “100 Top Tips” is that the more complex formulae are explained in plain language. For instance, combining the INDEX and MATCH functions can be a Herculean feat, but McManus spells out exactly what is going on:

=INDEX(range containing data to return, MATCH(data to search for, range to find it in, type of match))

Clearly, the book has a lot going for it, but it seems to be geared towards Windows rather than Mac users. This is evident in the keyboard shortcuts section, and at least one feature, that of the shortcut to the clipboard on the Home menu. I don’ think this is a game changer because the functions and formulae still work in the same way in both versions as far as I can tell.

Also, of course, the book is not geared towards teachers or education. However, it may introduce you to features you were not aware of (such as my experience of Flash Fill), and provide a handy reminder of how to achieve certain results. Therefore I highly recommend this book.

If you would like to buy it, you can click on the cover below. This will take you to the book on Amazon UK via an affiliate link. Doing so will not cost you any more money, but will help me to provide food for two ravenous kittens we acquired recently.

Alternatively, head on over to the author’s website, where you can view the table of contents and download a 16 page extract from the book in pdf format.

Click the cover to see this book on Amazon UK (affiliate link)

Please note: the author kindly supplied me with a complimentary copy of the book, but this has not influenced my review.

If you found this article interesting and useful, why not subscribe to my newsletter, Digital Education? It’s been going since the year 2000, and has news, views and reviews for Computing and ed tech teachers.