Monthly Archives: February 2016

Excel – Using Report Filters in Pivot Tables

In a previous blog I showed you how to create a basic Pivot table. We looked at how to add fields to columns, rows and values areas to quickly summarise information from a list of data.

One part of the grid I did not cover last time was the FILTERS segment. This works in exactly the same way as the other segments we use to build our Pivot table, i.e. click and drag field names into it.

ScreenHunter_211 Feb. 21 15.50

The FILTERS area

As the name suggests, fields dragged into this area can be used as a filter. These can be very useful to keep your Pivot tables relatively simple and can help to reduce the overall size of the Pivot table.

Using some basic data, I will build up a Pivot table, but first without applying a FILTER field.

ScreenHunter_211 Feb. 21 15.48

Pivot with no FILTERS applied

This produced a Pivot table 118 lines long, including totals and subtotals. It’s OK, it does its job, but perhaps we can improve it or at least simplify it a bit.

By moving the location and subject fields into the FILTERS area, we then get this;

ScreenHunter_211 Feb. 21 15.52

Same Pivot with FILTERS added

We may have lost some of detail that was visible before but the table is now only 8 rows long. No more incessant scrolling up and down to see results. Now…to see the detail, I can be very specific in what I see by clicking on the drop downs next to my FILTER fields.

ScreenHunter_211 Feb. 21 15.54

Location filter items

I can now see and select any one or more of the locations that appear in my location field. Note that I have ticked the SELECT MULTIPLE ITEMS option at the bottom of the list in order to be able to pick more than one location. If I don’t tick it, then I can only pick one location at a time. Personally, I would always tick this option whether I am picking one or several items.

Once you’ve made your choice, click on OK.

ScreenHunter_211 Feb. 21 15.58

Single item selected

Here, I have picked just one location (Birmingham) and below I have chosen three;

ScreenHunter_211 Feb. 21 15.59

Multiple items selected

Note that when multiple items are selected that’s all it tells you in the drop down – MULTIPLE ITEMS. If you need reminding of which ones you picked just click on the drop down again.

ScreenHunter_211 Feb. 21 16.01

Checking when multiple items selected

Also note that with each FILTER applied the size of my Pivot table remains unchanged. This won’t be the case every time, but at least you shouldn’t end up with the table being 10 rows and then jumping to 150 rows with the next filter, but this will be entirely dependent on your data and how you structure your base Pivot table.

If you are going to apply lots of filters, you can control how these are laid out above your Pivot table. If I keep on adding more and more FILTERS I get this;

ScreenHunter_211 Feb. 22 13.54

Five filter fields added

There’s nothing really wrong with this, but if you prefer you can distribute the filters over several columns and rows which might suit you better.

To customise this, click in your Pivot table and then go to the OPTIONS or ANALYZE tab (depending on which version of Excel you have) in the PIVOTTABLE TOOLS tabs and click on OPTIONS.

ScreenHunter_211 Feb. 22 16.43

Pivot table options window

In the OPTIONS window, select the LAYOUT & FORMAT tab. Here, there are two settings you can use;

  • Display fields in report filter area
  • Report filter fields per row

The first option determines whether it fills rows before moving across to the next column (DOWN, THEN OVER) or fill across the columns first, then move to the next row (OVER, THEN DOWN).

Use the second option to set the number of fields you want to see per row. So going back to our earlier example, with five FILTERS, if I set the options to OVER, THEN DOWN using three fields per row we get this;

ScreenHunter_211 Feb. 22 14.23

Customised filters layout

As with any settings, there is no right or wrong, only what suits you and the number of FILTERS you want to create.

As a word of advice, rather than anything else, try to stick to top level fields to have in your FILTERS. Although I have put date in the example above, this is generally not a good field to use in FILTERS as it is too granular. I used to manage inventory across a number of warehouses across Europe, and good top level FILTERS were things like product line, warehouse number, consumable/non-consumable flags, rather than part number. It meant I could quickly narrow down my output to consumable items for a specific product line, in a specific warehouse. As with everything, the choice is yours.

So, other than enable you to filter your Pivot table results, what else can you do with FILTERS?

Lurking under the OPTIONS button, is something called SHOW REPORT FILTER PAGES.

ScreenHunter_211 Feb. 22 15.24

Show report filter pages option

To get to this, make sure you click on the arrow next to OPTIONS rather than on the OPTIONS button itself. Note that this option is greyed out if no fields are present in the FILTERS area.

You should then see a window with a list of any FILTERS you have in place.

ScreenHunter_211 Feb. 22 15.58

List of filters in the current Pivot table

(I’ve reverted back to the original Pivot table I created….simpler to view)

Pick any one of the FILTERS shown and click on OK.

Then check out the new tabs that appear in your workbook!

ScreenHunter_211 Feb. 22 16.01

New tabs in the workbook

What this does is create a separate worksheet for each item in the selected filter. In this example, it has created a separate sheet for each course. Had I selected location, then a separate sheet would have been created for each location.

Each sheet contains a Pivot table in its own right. But why do this, create a whole load of new sheets when the information is already nicely packaged in a single Pivot table? Let me ask you this…are the people you send your Pivot tables to happy using Pivots and do they know what they are doing when using drop downs etc. within those Pivots? Probably not. By sending the information like this, all your users have to do, is go to the tab that interests them and view it. No need to apply filters, or click on anything that is likely to cause panic or confusion. This may seem like a damning indictment of the average Excel user, but you don’t know what you don’t know, and if Pivots are a mystery, it’s easy to click or even worse, double click on something and a new sheet appearing or alter the structure of the Pivot with the user not knowing why something happened or how to correct it.

So that’s FILTERS for you. Useful to quickly narrow down output in a Pivot and also making Pivot data more accessible to non-Pivot users.




Excel – Using INDEX & MATCH (2)

You’ve probably come across the MIN and MAX functions which give you the lowest and highest values, respectively, in a range of cells. This is fine as long as all you need to know is the lowest or highest figure, but what if you want to know who or what got the lowest/highest figure?

Let’s take a simple example;

IM basic list

Basic table data

So we have a list of people and the amounts they have spent. With a quick scan of the data you can see that Mrs Delores Turner had the lowest spend, and Mr Louie Moreno had the highest. This is easy because there is only a very small amount of data, but if there were hundreds or even thousands of lines it would not be so obvious.

By using the MIN and MAX functions I can quickly find the lowest and highest values using MIN(B2:B11) or MAX(B2:B11), but it doesn’t tell me “who” spent these values.

So how can you get around this?

This is where INDEX and MATCH come in. If you are unfamiliar with these two functions, check my blog which will show you how they work. For the rest of this blog I will assume you have either read the blog or are already familiar with INDEX and MATCH.

To get the name of the person with the lowest/highest spend I need the following formula;



Formula to find the person with lowest spend

The MATCH part of the formula find finds the lowest value in cells B2:B11. It then uses this number and finds its position within the range. In this example the MATCH function will return the value 4 i.e. it is the 4th value in the selected column of data. The index function then looks within range A2:A11 and looks in the 4th row – the value returned by MATCH. You don’t need to reference the column because there is only one column selected in the array portion of the INDEX function.

The find the person with the highest spend, the formula would be;



Formula to find person with highest spend

So as you can see INDEX and MATCH, although pretty useless on their own as we saw in the earlier blog, when combined they are a useful duet of functions to know and should be part of your “must know” functions in Excel.

Excel Guest Blog -Spreadsheet Passwords (Facts about Protection) by Tom Urtis


Password security

Spreadsheet password protection is a topic of major concern for Excel users, rightly so. Information in worksheets can be confidential, needing to remain undisturbed with formulas that must be protected from deletion.

It’s wise for an Excel user to voice his or her curiosity of spreadsheet protection, or has questions about just how secure a password-protected spreadsheet really is. When people know the facts without scare tactics or hyperbole, they can make the best decisions for themselves when armed with objective, unbiased information.

As protection platforms go, Microsoft’s products have inherent weaknesses. In its defense, Microsoft has never claimed to have reliable spreadsheet protection. In Office applications, a password is like the lock on your home’s front door; its primary purpose is to keep your friends out. If someone really wants to get in, they will get in.

Try this: open a new workbook, go to Sheet1 and protect it with the password “test” (without the quotes, lower case just as you see it here). Now unprotect Sheet1 but instead of using the password “test”, use the password “zzyw”.

Take comfort that Microsoft is like any other company, in that virtually any application is hackable. Here’s some background on Excel spreadsheet password protection:

When someone password protects a sheet in Excel, they generate a 16-bit 2-byte hash, a technical term for a number generated from a string of text by a function called the MD5 Message Digest Algorithm. An MD5 hash has fewer numeric characters than the actual password text, making it unlikely but not impossible to be replicated. Note that “replicated” is not the same as “duplicated”.

When unprotecting a protected sheet, the password value is compared to the MD5 hash. Excel allows for up to 255 password characters in its worksheet protection scheme. Since it is a case-sensitive scheme, there are over 90 acceptable characters, which translate into the multiple trillions of password text possibilities. Since the combination of possible passwords is much greater than the combination of possible MD5 hashes, some passwords can share the same MD5 hash value.

The MD5 hash is a standard mixing algorithm, executed as follows:

  • Take the ASCII values of all characters.
    • Shift left the first character 1 bit.
    • Shift left the second 2 bits.
    • Continue for quantity of characters up to 15 bits, with the higher bits rotated.
    • XOR those values.
    • XOR the count of characters.
    • XOR the constant 0xCE4B.

As you may know, XOR is a logical term associated with a mathematical compound statement, an acronym for “exclusive or”. In this case, statement “A” is the password value you type in. Statement “B” is the generated MD5 hash. The XOR operation returns TRUE when only one of its combinations is TRUE. This translates to more than one password value possible in the context of a truth table:

A B XOR Result

By the way, if you wanted to reproduce the actual password, and not just a compatible one, it’s a virtual certainty that it literally could not be accomplished during your lifetime.

There are 94 standard characters (26 of A-Z; 26 of a-z; 10 of 0-9; and 32 special such as #,%,!, and so on). That means, for every character there are 94 possibilities.

To extrapolate using the example of an eight-character password, the number of characters to test is
94 x 94 x 94 x 94 x 94 x 94 x 94 x 94
which equals

At the hefty pace of 100,000 password attempts per second, it would take 1,932 years to recover the exact password. And that’s just with 8 characters; with the 255 max it can take millions of years.

What all this boils down to is, if you don’t want to expose your Excel spreadsheets to *any* possible password circumvention, don’t share them. However, the likelihood of someone guessing a compatible hash is very slim, though there are commercially-sold password cracking programs.

One thing is sure, you are in good company: the whole world is in the same boat with this Excel protection issue. As you understand the spreadsheet password protection scheme, you can make your own informed decisions about what and what not to risk putting in your workbook, and how or with whom you share access to your workbooks.

Blog kindly provided by Tom Urtis (Excel MVP). If you are interested seeing more blogs and tips by Tom follow him on Twitter (@tomurtis) or visit his website Atlas Consulting