Don't underestimate the seductive power of a decent vocabulary

Isn't that just brilliant? I saw that quote on Facebook this week, from LawsofModernWoman. It went straight into first place on my list of favourite quotes, kicking the long-standing favourite off it's perch - "Never use a big word when a diminutive alternative will suffice".

Both of these quotes segue nicely into what I would like to cover today... there are sometimes many different ways to do the same thing in Excel. To have a decent 'vocabulary' of Excel tricks will make your day easier, and will increase your seductive power (to your PC and Excel geeks, at least)! But to remember that the most complicated method is not always the best method, well, that might just take you into hero status.

So here is Part 1 of a series in how to increase your Excel 'vocab' and hopefully teach you a couple of  easy to use tricks or shortcuts. The data we will be using is from a list of the 'supposed' best selling books of all time in the UK. Just a detour here - this list is provided by Nielson Bookscan, via the Guardian (click on the link for the full list of the Top 100. I haven't included it here!) I am having serious trouble believing this is the best selling list of all time, so am hoping it applies to maybe the last 10 years. Anyhow, it had good data layout so I used it. Suspend your belief when you look at the results. If nothing else, it will give you ideas for your beach reads....

Tip 1 - Filters are sometimes better than pivot tables.

I know that just 2 weeks ago I was singing the praises of pivot tables in my blog post, but there are times when you just need to have a quick check on something or you don't want to create a completely new table of data. If so, use the filter.

To get the filter onto your data, click somewhere in your table, and go to Data - Sort & Filter - Filter. This will apply the arrows to your header row.

Important point 1 - You can't have blank rows or columns in your table. If you have blank rows, the filter will only apply down to the last row before the blank row. If you have blank columns, the filters won't always go the whole way across. So have a complete, space free table of data before you begin.

Then select which column you want to filter on. In our data below, you may want to check how many of the top 100 books are in the Children's and Young Adult Fiction categories. So you click on the little downward arrow in the Genre column, and get the filter dialog box. At the bottom, all the genres will be selected, so first click on the tick mark next to Select all, to deselect them, and then choose the topics you want to look at:

Press ok, and your table will look like this:

That is 17 out of the Top 100, excluding Annuals and Picture books. How do you know 17 without counting each line? Look at the bottom left hand corner, and it tells you:

Now, what if you wanted to know how many of these 17 books had volume sales of over 1.5 million? You click on the arrow on the Volume Sales column, and select Number Filters - Greater than, and type in 1500000 in the box that appears, like so:

The answer to that is 11, all the Harry Potter and Twilight books. So, you can apply filters to more than one column at a time. Note, too, from the box above, that you can apply more than one filter to the same column. Just select your choices from the drop down boxes.

Important Point 2 - Always be aware of when you have a filter applied. You can easily assess this by looking at the colour of the row numbers. If they are blue, you have a filter applied somewhere. The arrow icon on the column header changes if you have a filter applied so you can easily assess which column is filtered.

To clear a filter, go to Data - Sort & Filter - Clear.
You can also filter by colour. You may have noticed some of the book titles are blue. I have highlighted all the books I have read. To apply this filter, do as shown in the image below:

This is ridiculously handy if you are fond of using colour to highlight cells. Sadly, I appear to choose all my books from best seller lists. Happily, I still have 48 to get through before I exhaust the list.

Anyway, onto to shortcuts:

Shortcut number 1: To put on, or take off, filters in a hurry, press and hold ALT, whilst pressing D F F

Shortcut number 2: To clear all filters that are currently applied, press and hold ALT, whilst pressing D F S

Tip 2 - You don't always need a formula in a cell to calculate something

Don't worry, this is a quick tip. Using our example above, with all the books I have read filtered, how would you calculate the total number of books sold? Or the average sales volume? Without typing in a formula?

Easy - highlight all the values in the Sales Volume column, and look at the bottom right hand corner. You will see this:

That's your answer. Nice and simple, no need for a formula. If you right click on the toolbar next to these numbers, you can select some other options, like minimum and maximum.

Very useful if you're in a hurry and you need to shout an answer to a irate / impatient / cranky boss!

Our newsletter this week will cover how to remove duplicate data using 2 different methods....sign up here.

This was part 1 of 'Improving your Excel vocabulary.' Part 2 will follow next week. Have a good one!


Happy spreadsheeting!