Excel – Applying Conditional Formatting to Charts

Hopefully you are familiar with applying conditional formatting to cells, and creating rules to apply more complex formatting rules. If not, check out my blog to see how you can apply conditional formats to cells.

However, when it comes to charts there is no in-built conditional formatting functionality where you can create some sort of rule and   apply it to a series.

In order to create the “illusion” of conditional formatting on charts we have to create some dummy data based on our base data.

Let’s start with a simple table.

Simple sales table

When converted into a chart it gives us this.

Standard chart

As you can see we have a range of products being sold and have different sales figures against each one. To make things stand out a bit more it would be nice to apply some colour coding to the bars of the chart to show which sales are good, bad or indifferent or simply to show on a colour scale how sales are performing.

For this example, let’s say I just want to show a RAG (Red/Amber/Green) system for each product.

First, decide what your break points are going to be i.e. what determines if something should be red, amber or green;

Red <=1500

Amber <= 3500

Green > 3500

Once we have our break points we can set up our table to create the dummy data.

Add a couple of new lines above your table and if you want, label them something like Lower and Upper to represent the lower and upper bounds of your range of values e.g. 0 to 1500 for red.

It’s worth also adding some labels so that these appear in your legend when you create the chart so people understand the meaning of the colours. In this example, I used the formula = C2&” – “&C3 to build up the label for the red values.

Modified table to create dummy data

Now we build up some IF statements to determine which category the actual value from the base data falls into i.e. Product A should be in the amber section. We will start by creating a nested IF statement in cell C5 (in this example) that will test if the sales value for Product A is between 0 and 1500. If it is, then display the number, otherwise show #NA. We need to have #NA as this prevents anything from being plotted in the chart. By fixing certain elements of the formula (see my blog on absolute vs. relative referencing if you are unsure about what I have done) so that the formula can be copied across the columns and then down the rows ensuring it works in all cases.


Once applied to all the cells in the modified table, each sales value for each product should only appear once in the new columns.

Completed dummy data table

Now it’s time to replot the chart but this time using the dummy data.

Highlight all the data apart from the original values.

Chart data selected

Use a stacked column chart to display your data and then apply whatever formatting you want to get your end chart.

Conditionally Formatted Chart

Because of the way the data has been set up, if any of the values change, the formatting you have applied will automatically change based on the values.

Updated numbers



PowerPoint – Alternatives to Bullet Points

Bullet points in presentations have a very bad reputation, not because they are necessarily bad things, but because they are over-used and misused. Seth Godin (marketing/presentation guru) describes bullets as aggressive, and usually represent a disorderly random list that is easily forgettable. Garr Reynolds (author of Presentation Zen) argues that text laden bullets are actually a barrier to good communication. Even psychologists have studied the effect of bullet points and Chris Atherton (cognitive psychologist) concluded that “bullets don’t kill, bullet points do”.

So, all in all, a pretty damning indictment of bullet points.

Unfortunately, the dominance of the text heavy bullet point slide still exists in the workplace today. It is still far too commonplace to see presentations made up almost entirely of bullet points.

I mean…does this look in any way interesting?

Bullet point hell!

Yawn! You wouldn’t want to sit through that, so why inflict it on others?

Now we know that bullet points are the devil incarnate, what are the alternatives?

The best solution that all the leading presentation experts point to, is use a picture. It may be a cliché to say a picture paints a thousand words…but it does. Images, if chosen carefully will elicit an emotion from the audience which they will remember more easily and far longer than they will a list of words.

Assuming you work for a slightly more conservative/traditional organisation which expects to see bullet points and the whole screen image thing is a step too far, then try to get a bit more creative in how you present your bullets.

Use SmartArt

SmartArt graphics are ready-made and editable graphics that can convert the humble list into something a bit more interesting. As with bullet points themselves, don’t over use SmartArt and definitely don’t use the same one for every slide.

If you have bullet points, and you have managed to reduce the text down to key words, you can convert your bullet points into a SmartArt graphic very easily.

Here we have some very basic bullets;

Basic bullet points

On the HOME tab, look at the PARAGRAPH group and you’ll find the option CONVERT TO SMARTART.

Convert to SmartArt

Click on that to open a selection of SmartArt graphics.

Initial list of SmartArt

If you don’t like any of those shown, click on MORE SMARTART GRAPHICS and choose from the whole list of available designs which are arranged by category.

Full categorised SmartArt list

Click on your preferred graphic and PowerPoint does the rest.

Converted bullets

Use the formatting tools to customise the appearance and if you want to, you can add some simple animation (nothing too distracting!) to show your “bullets” one at a time rather than all in one go. Remember, if there is text on a slide your audience will read it, and not only that, they will read it far quicker than you will present it, so anything you say about your slide is of no interest as everyone knows what’s coming up.

Speech Bubbles

A simple alternative might be speech bubbles or similar shapes that contain a word or two. Again…only use key words not paragraphs!

Thought/speech bubbles

In the example above, it does mean having more slides to show my three points but that’s not an issue. Remember, you don’t want people staring at the same slide for too long, and by that, I mean no more than a minute or two. If the slide becomes a distraction turn the screen black (press the B key) and just talk to your audience.

Notice that I have changed the eye position in each slide to look towards the thought bubble (done with Photoshop). It might seem like a minor point but as viewers, if we see a face, we are automatically drawn to it and then we automatically follow the direction of the eyes. So, if the eyes are looking in the wrong direction away from the object you’re trying to get people to look at, it causes a subconscious meltdown.

So, there you have two alternative ways of showing bullet points. There are of course many other ways of doing this, using custom graphics etc. but these two are readily available in PowerPoint and easy to create.

There is however, a caveat to all of this. When putting together a presentation there needs to be a common theme or design running throughout so that the audience knows that all the points you are making are somehow connected. The temptation to pick random images and graphics to simply avoid bullet points won’t be as effective as a set of slides using a common theme or design.

Putting together visually impactful and meaningful presentations takes lots of thinking, planning and preparation.

This last point is probably what pushes people towards creating bullet lists – they are quick and easy to put together, requiring little thought or planning. Unfortunately, that translates into uninteresting and unmemorable slides. A short list is sometimes the way to go…just don’t turn your entire presentation into one massive list!

Excel/PowerPoint – Distortion Free Image Column Fill (Infographic style)

In an earlier blog, I showed you how to covert a standard stacked column chart into something that looked more like a battery infographic, to create a more visually interesting slide. This time I’ll show you how you can replace columns in a chart with an image but set it up so that you don’t get image distortion which is created by images being stretched or compressed by the different values in your table.

Let’s start with the basic chart.

Default Excel issue chart

Default Excel issue chart

As with all charts….it’s done its job but that is where it ends.

You can then format the chart to contain an image going through the normal FORMAT DATA SERIES > FILL > PICTURE OR TEXTURE FILL and select an image from your computer. Unfortunately, although it “works”, the image becomes distorted – higher values stretch the image and smaller values compress it, giving you this;


You could argue it doesn’t make much difference, but people will invariably read more into the image than is necessary – “oooh….I wonder what a blunt tip means?”, “does the size of the rubber tell us anything?”

So you need to find a way of keeping the proportions.

To achieve this we need to create three separate images;

  1. Rubber tip
  2. Main body of the pencil
  3. Pencil tip

In case you are wondering, I used Photoshop to cut out the pencil and then split it into 3 sections. Not everyone has access to Photoshop, so you may need to use some more basic cropping tools. By using Photoshop I am able to control size and blank space around my cut-out shapes more easily.

Splitting out the pencil

Splitting out the pencil

It also means we need to split out the data. At the moment we have a single series – one value per month in our table.


Original, basic data table

This now needs to be split into three to match the number of images we have. Let’s give a value of 10 to represent the tip and 10 to represent the rubber end. So our new data table now looks like this:

Modified data table

Modified data table

Now re-create the table, but this time use a STACKED COLUMN chart. You should now get this:

Stacked column chart

Stacked column chart

As before, you now need to format each series by inserting the appropriate image into each series i.e. image of the tip in the top series, the main body of the pencil in the middle series and finally the rubber tip into the bottom series. Once complete you will get an undistorted image in your columns, with each tip and rubber end the same size.

Take away lines and axes, add some values above the pencils and you end up with what appears to be an infographic of some sort, ideal for a PowerPoint presentation:  a nice clean image free of clutter and unnecessary detail which hopefully will be a bit more memorable than the default offering in Excel.

Final chart/infographic

Final chart/infographic

Here is another example using exactly the same principles but applied to a bar chart;


PowerPoint – Make a Chart Look like an Infographic


Far too often, people will add charts to their presentations without even attempting to make them vaguely interesting or memorable. At the end of the day, data is data and is not necessarily the most interesting thing on earth, but there is no need to bore people to death showing an endless procession of slides made up of bullets and standard charts.

Infographics are one way to make your slides more interesting and hopefully more memorable. You’ll see all sorts of infographics in the press and on the web, but many of these are created by graphic designers using specialist software which most of us will not have access to.

However, you can use in-built formatting options, and drawing tools within PowerPoint to create infographic-type shapes and images. In earlier blogs, I showed you how to create shiny spheres  and transparent cylinders using only the tools and options you find in PowerPoint.

At the top of the page, you have a standard 100% chart and next to it is the same chart but with three added shapes and some formatting to make it look like a battery. Hopefully, people will remember the battery and what parts of the business give it a “full charge” compared to the rather dull standard Excel chart.

Working directly in PowerPoint, insert a chart into a slide.


Select 100% STACKED COLUMN chart type.


Click on OK.

Edit the data in the worksheet that opens.


You may need to SWITCH ROW/COLUMN to get the values stacked up properly, but once done you will get the standard Excel chart with whatever colours it selects for you by default.


It’s done its job. you could add some labels but it’s hardly memorable.

So let’s convert it into something more akin to an infographic.

First of all, get rid of anything you won’t need in the final “image” i.e. x-axis, grid lines etc. You can always remove or add elements depending on the final look you want to create.

Now to add the shiny effect to the chart to make it look like it might be a cylinder and has some depth to it:Click on one of the segments in the chart to select it.

If you are using Excel 2010 or earlier you will find a number of “interesting” preset gradients. Some of these are actually quite good for what we want to do.


If you are using 2013 or later then these preset options have disappeared and become a selection of standard colours which admittedly are less gaudy but you will have more work to do to get the final desired effect.


Whichever version you have, you will need to adjust the GRADIENT STOPS, adding light and shade sections to the chart segment. If you need to add GRADIENT STOPS just click on plus button next to the GRADIENTS STOPS . Move the marker to the appropriate position and then select a colour. This is entirely down to personal choice but for reference these are the settings I chose to achieve the look in this blog (use whichever colour you want to set up your marker positions);

  1. Stop 1: 0%, blue-gray, Accent 1, darker 50%
  2. Stop 2: 13%, light blue, Accent 1, lighter 40%
  3. Stop 3: 30%, blue-gray, Accent 1, darker 50%
  4. Stop 4: 78%, light blue, Accent 1, lighter 40%
  5. Stop 5: 82%, white, background 1, darker 5%
  6. Stop 6: 100%, blue-gray, Accent 1, darker 50%


Apply the same gradient settings to each segment picking a different colour each time. This is not as bad as it sounds. When you select GRADIENT for each segment the settings you applied are still there. All you will have to do is change the colours.

Once complete, you should have something like this;


All you need to do now is add some shapes top and bottom of the chart to create the battery effect.

Using the standard shapes, use the RECTANGLE: ROUNDED CORNERS and create two blocks to represent the top and bottom of the battery, and then a smaller third block to represent the positive or cathode end of the battery. Line them up and send them to the back so they don’t hide any of the chart. Apply the same gradient to each rectangle….and done.

There you have an editable chart that looks like a battery!

To edit your data, right click on the battery and select EDIT DATA.


Change your numbers (better still…get the data to update itself) and close the workbook to see your new updated chart/battery.



Excel – Create a Custom Matrix Background

If you have ever been on any sort of management course, you will have been shown a variety of matrixes to represent data, strategy etc. These can be quite visual and can help to identify products or people who fit into different areas allowing you to target specific areas rather than try and tackle everything. Most of the time, you will see these in PowerPoint or Word, and have been manually created and are not particularly accurate and are not dynamic in any way.

So how can we create a useful matrix on which we can plot data?

First of all we have to decide on the type of chart that will best display our raw data. In the case of matrix displays a scatter chart is best, plotting results based on two set of values, effectively giving us x and y co-ordinates.

Using recent enquiries I have had, some examples of this might be:

  • strategic importance of work compared to the revenue it will generate
  • knowledge transfer from long serving staff due to retire – level of critical knowledge vs. the amount of time before retirement

In both of these examples, the business wants to identify work or people that might have a critical impact on the business so that they can be prioritised ahead of less critical work or where very specific and unique knowledge sits with one person, and ensure that an individual’s knowledge is recorded or transferred before they leave.

Let’s take the critical knowledge transfer and create a chart to track that.

One piece of information I will need is the amount of time between today and the day the person is scheduled to retire, and I might break that down into three segments;

  • less than 3 months
  • 3-6 months
  • 6 months to one year

The other piece of information I will need is the level of knowledge an individual might have and assign scores or levels (these are likely to be subjective);

  • Low
  • Medium
  • High

So we may find ourselves with data like this;

Basic starting data

Basic starting data

Because we can’t plot “words” in a scatter chart I need to change the Low-Medium-High ratings to numerical values. Depending on how you measure this you can use a simple 1-2-3 or have something with a few more breakpoints – choice is yours.

So now our new table might look like this (I’ve used a basic data validation list for the rating);

Data with added scores/rating values

Data with added scores/rating values

We can now create a simple scatter chart using the due retirement and rating columns.

Standard scatter chart

Standard scatter chart

The data has plotted OK but nothing jumps out at us to start dealing with those who will be shortly retiring and have critical knowledge level of high. What we need is some sort of background with clear segments to help identify target employees.

We could create or find an image that contains the number of segments we need but the proportions may not be that accurate, so the image will have to be stretched or simply won’t fit in with our data. So we are going to create the background manually using dummy data.

As we have three levels of criticality and 3 time periods we will need a 3 x 3 grid pattern.

Away from your real data, type the following values;

Dummy data to create bandings

Dummy data to create bandings

Using these values create a stacked column chart…

Base chart

Base chart

…and get what you see above. At this point we need to do a few minor adjustments;

  • Fix the maximum value of the Y-axis to 3
  • Set the gap between series to no gap or 0%
Change axis maximum value

Change axis maximum value

Change settings on the chart - gap width

Change settings on the chart – gap width

Our chart will now look like this;

ScreenHunter_214 Aug. 09 11.23

Before we start applying different colours to the various segments that make up the chart, we have to move the axes out of the way. The axes need to be moved because we need to copy the “real” data onto the chart, and in order for the data points to look as they did in the first basic chart (with no background) we need to use secondary x and y axes. If this is not done, the real data looks back to front and upside down as you need to read the values off the secondary axes rather than off the normal x and y axes. It’s not a major issue but best to sort out at this stage.

Right click on either axis. If you select the x axis first then select the axis option VERTICAL AXIS CROSSES AT MAXIMUM CATEGORY.

Right click on the y axis and from the options select HORIZONTAL AXIS CROSSES AT MAXIMUM AXIS VALUE.

The chart should now look like this;

ScreenHunter_214 Aug. 09 11.24

Apply formatting of some sort to your segments. Use any of the fill options available to you at this stage – solid colours, gradients, images or patterns…more options than you can shake a stick at.

The path is now clear to add the real data to the chart.

Highlight the due retirement and rating columns as we did in the original plain scatter chart and copy them.

Depending on how much data you have, you can create a separate column using a formula to only identify people who have a leave date within the next year…no need to plot people who are not going for another 10 years!

Click on the chart that is being used to represent the 3 x 3 grid of our matrix. On the HOME tab click on the arrow below PASTE and select PASTE SPECIAL. Instead of the normal options you see if you do this (paste values, paste link etc.) you will see this dialog box;

Paste special window for chart data

Paste special window for chart data

Select all the options as in the picture above and click on OK. The chart that appears looks very odd as it is trying to plot your copied data as stacked columns – the same chart type as the base chart with our grid.

Our chart with the added "real" data

Our chart with the added “real” data

If you’re using 2010 right click on the new series you just added and select CHANGE SERIES CHART TYPE, and select SCATTER WITH ONLY MARKERS type chart. If you’re using 2013 or later then it will open the COMBO chart dialog window. Select the correct series and select SCATTER.

Set the correct combination charts

Set the correct combination charts

ScreenHunter_214 Aug. 09 11.28

New series changed over to the correct scatter type plot

Now, it’s purely down to formatting. Depending on what you are presenting and how you want to highlight data points will be entirely up to you. In my version here, you will see I have added shading to differentiate between the high/mid/low ratings and some extra labels to identify the criticality levels, as well as some arrows to highlight the timescale – the latter having been added manually.

Completed chart with custom gradients and additional labels/arrows

Completed chart with custom gradients and additional labels/arrows

The only other thing I have added are some custom labels. As we are working with a scatter chart we can’t attach any useful labels to the data points. I have added a new column with a formula to pick out the names of people who have a criticality level of high and their leave date is within the next three months. I then used Rob Bovey’s xy chart labeller which is a free add-in that is perfect for this sort of job. If you are not familiar with this add-in, read my blog http://wp.me/p2EAVc-et which explains how it works and where you can get it from – an absolute must for these sorts of jobs.

So that’s one way of creating a custom background to make a matrix type display using Excel. Once you have your basic set up for the background, i.e. number of segments you want to measure against, it’s down to your raw data. Depending on your own Excel skills, use data validation, dynamic ranges, formulas etc. and whether you are working with static or continuously evolving data, how much interaction and dynamism you want to build into your matrix. With a little thought and preparation you can construct a pretty useful matrix type chart that will highlight data points, be they people or activities that need attention.

One Note – The forgotten tool in the Office suite

If you’re already using One Note then you will know how useful it can be and also how easy it is use. If you are amongst the majority who have never used it or as is often the case, not even noticed it is installed on your laptop or PC, then read on…

As the name suggests it’s a notebook, but it offers a lot more than just somewhere to jot down the odd comment or two.

One Note allows you to organise data, notes, web links, images etc. into sections, with each section containing pages. Each page is quite literally a blank canvas where you can add notes etc. anywhere you like. So unlike Word which is fixed, in other words, you start writing at the top of the page unless you press enter 10 times to start further down the page, you can start typing anywhere on the page, just like a paper notebook, with the advantage of being able to move or copy notes anywhere within the page, notebook or even to another notebook.

Let’s look at the structure of a notebook;

  •  First of all you can have multiple notebooks.
  •  Each notebook can be divided into multiple sections.
  •  Each section is made up of pages and sub-pages.

There is a great diagram showing how a notebook is structured within One Note itself which you can see below.

one note structure

Notebook structure

To create a new notebook go to FILE, NEW and set where the notebook will be created (web, network or My Computer), give it a name and click on CREATE NOTEBOOK.

To add new sections just click on the tab with a star on it or right click on an existing tab and select NEW SECTION. When the tab is created the name of the section is active. Type a name for your new section and press enter. You can rename a tab at any point by right clicking on the tab and selecting rename.

Section tabs

Section tabs

By default, a section has one page. Rather than click or anything else on the tab on the right hand side of the page, you type the page name or heading in the dotted box in the top left hand corner of the page. To add new pages, click on NEW PAGE above the page name tabs and use the same steps as above to name your new page.

Once you have your basic structure, it’s down to adding content.

You can add all sorts to your notebook;

  • Text
  • Tables
  • Pictures
  • Screen clippings (using the Snippet tool)
  • Links
  • Files (as attachments)
  • Files (readable – whole printout)
  • Scanned documents
  • Voice memos
  • Video memos
Insert options

All the insert options in the Ribbon

Note there isn’t an insert text option – just start typing wherever your cursor is.

To help you flag content, you can add tags. You’ll find these on the HOME tab. You can easily create to do notes, highlight questions etc. You can add new ones and you can move tags that you use regularly to the top of the list so you can use a keyboard shortcut to add the tag. Once tags are in place you can search by tag name to find them quickly again.


List of available tags

There is also a DRAW tab, which gives you a number of drawing tools. Pick from various pens, colours and thicknesses to create free hand drawings, as well as draw shapes and graphs. Obviously, this will work much better if you have either a touch enabled screen or a graphics tablet rather than trying to write or draw with a mouse which is pretty hopeless to say the least.

draw tab

The Draw tab

So that’s pretty much everything you can add to a page from within One Note.

Also note that One Note automatically saves anything you do, so don’t go looking for the save button. You can do SAVE AS to save pages and sections etc. as new pages, new sections or even new notebooks.

Now…where One Note sits high above all the other applications is its level of accessibility across applications and devices.

Just look for the One Note buttons in most applications.


one note button

You receive a mail that relates to a project you are tracking in one of your notebooks and would like to have a copy of it along with your notes – just click on the One Note button and the open mail item will be copied. You will be prompted to select the notebook and page to send the mail to.

save note location

Pick where you want to save a note

The e-mail in its entirety will now be in your notebook and can be annotated etc. like any other note.

Alternatively you can e-mail pages directly from One Note. If you have minutes from a meeting, action points etc. in One Note just click on E-MAIL PAGE – no need to copy/paste the contents or re-write them.

email button

Internet explorer:

In IE (bit not Firefox) you can create notes that are directly linked to a web page (One Note linked note), or even send the web page itself to One Note. As with e-mail you’ll be prompted to select a specific page in a notebook. Just right click on the web page and select whichever option you prefer.


linked notes button

From within PowerPoint you can add LINKED NOTES. As always, select a page to add the linked note to, start typing something and One Note will add a PowerPoint icon next to the note. Then, from within One Note you can click on the icon and it will open the presentation at the slide you created the linked note from.


linked notes button

Like PowerPoint, you can create notes that have links to documents created in Word. Look for the Word icon next to a note and click on it to open the document at the page relating to the linked note.

Mobile Devices:

You can download One Note to your mobile phone – for both Android and iOS. OK…it doesn’t have all the features of the desktop version but it’s great for quick notes, sending images etc. while you’re out an about. You can view all your notebooks and make edits through your phone.

ios image

Screenshot of One Note on an iPhone

The final great feature of One Note is that you can access your notebooks from any laptop, PC where you are a user. You can even install One Note on both Android and Apple phones and link it back to your notebooks. The convenience of this is that you can be anywhere – see something interesting, or suddenly come up with an idea, you can make a note, record video or a voice memo and save it straight to One Note which is then automatically synchronised to all your other devices. Even if you don’t have access to any of your devices but can get on the internet, sign in to your Microsoft Live account and access your notebooks from any PC, laptop or tablet without the need to even install One Note. If you have shared notebooks then all of One Note’s features are available to all team members. Brilliant, and so convenient it’s untrue.


Cross device connectivity

For those of you in a work environment you may not be so lucky. Depending on network and access restrictions, you may find yourself limited to using One Note on your PC or laptop only, and unable to create shared notebooks, or link to mobile devices as it all depends on having a Live account which most businesses will not allow. If you can create networked notebooks, then this can be very useful if a team of you are working on a project. Each team member can update files, add comments etc. to the notebook, and any added comments have tags showing who added the comment, as well as the date and time it was added. This makes One Note a great collaborative tool, even if you are restricted to your network. If you have access to the full range of functionality including mobile apps then even better.

One Note has the potential to be a very powerful tool across multiple users especially for remote teams who need a simple and convenient way to keep in touch and share files, thoughts, images etc. quickly and easily. Even if you can’t use it to its full potential, simply using it as a private notebook is an excellent way to manage all your projects and all kinds of information in a variety of media types.

As a quick note, if you have Windows 10 installed, One Note is pre-installed, but this is only the app version not the desktop version. It works fine, but you don’t have all the functionality of the full version. You will need to download the desktop version direct from Microsoft.

So if you haven’t tried using One Note yet, take a look. Once you start using it you’ll find out just how easy it is to use and how useful it can be.

Excel – Dealing with Non-Printable Characters

Every so often you may encounter a problem with Excel where a function is not working properly, even though you know that your function syntax is correct. And when you manually check the data you find that the function is not returning the correct answer.

So what is going on?

This problem happened to me again recently and it took a little while for the penny to drop as it is fairly rare, but highly annoying when it does.

And it is down to something called “non-printable characters”. These pesky characters are invisible, which makes it doubly difficult to identify. These tend to crop up in reports that have been downloaded from other systems, which for reasons unknown, slip in characters of some sort that cannot be seen or identified on screen, but nevertheless cause mayhem with a number of functions.

As an example I’ll use the data which caused me the most recent problem.

Sample data causing the problem

Sample data causing the problem

The company I work for uses a web based database to manage courses and delegates and some information was extracted to generate a catalogue within a workbook (sample above). I wrote some VBA code to help users find courses either by name of ID number and in one instance, the dreaded end-debug screen kept on appearing.

The form I created allows users to enter partial names or IDs and search for information based on that partial string. When they entered the number 26, a message told them that two records had been found.

Message returned by the VBA code

Message returned by the VBA code

This was done using a COUNTIF function. However, there were in fact 6 records that contained the number 26.

...but autofilter finds more records

…but autofilter finds more records

So on the one hand the COUNTIF function was finding 2 records, but the loop in the code which populated an array in the memory was crashing as it found a third record that contained the number 26. The dynamic array was not big enough as the size was recalculated based on the findings of the COUNTIF function.

Initially I looked at formats – text vs. numbers for example.

I also looked for any spaces that might have caused an issue.

I manually added a COUNTIF function in the worksheet to double-check the findings of the function created in VBA but this gave the answer 2 as well.


Finally, realising that this was an extract from a non-Excel source, I tried using the CLEAN function suspecting that there may be some non-printable characters in there somewhere. The CLEAN function removes all non-printable characters from text. The syntax is very simple:

=CLEAN(cell reference)

In a new column I wrote this formula and then copied all the “cleaned” text values and used PASTE SPECIAL VALUES over the original cells and retested the COUNTIF function.

…and hey presto…it found all 6 records.

So next time your functions are not working and seem incapable of identifying values in cells, try applying the CLEAN function to your data, it might just be the solution to your problem.

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 https://excelmate.wordpress.com/2014/12/04/excel-using-index-match-part-1 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