Excelmate

Most of us are familiar with applying conditional formatting to cells either directly or indirectly using a formula, but what about charts? There are lots of chart types to choose from but none of them give you the option to conditionally format them. In order to do this we need to create some dummy data and use this to apply our formatting.

Using some simple data we would get this result – a standard one colour chart as there is only one series. Simple, does what it’s supposed to do but we can’t tell if the numbers are good, bad or indifferent. We could manually colour each column but if number change then we have to adjust everything manually again. So now we need to create a new table with some dummy data. First lets create our conditional rules – what are our lower and upper boundaries for each rule?

Here I have created a table of 3 ‘rules’ or categories setting lower and upper values to identify ‘Poor’, ‘Average’ and ‘Good’ sales figures. Now to create our new table…. First create some column headings that will be used as our series names.

This can be typed in manually or if you change your rules use a more dynamic style formula to make up the label; =”Between “&E2&” – “&E3 where E2 and E3 contain our Min and Max values. To see if the values in our data table fall between the Min and Max values we will need an IF statement;

If the value in the table is greater than or equal to our Min value, test if it is less than or equal to our Max value. If it is, then display the value from the table, otherwise show #N/A. We use NA() in our formula so that nothing is plotted in the chart when we create it. Copy this formula down for all rows of data and across for each of the ‘rules’ we have set up. We end up with something like this…

As a quick sanity check you should see a value appear in only one of the ‘rule’ columns. If this has worked properly we can now go ahead and create our chart. Select the label values (in this example the months), press Ctrl and then select all the values we have just created using our ‘rules’. This will give us…

Because we have multiple series the chart looks a little odd as the columns are not centered. Right click on any of the columns, select Format Data Series and under Series Options set Overlapped to 100%.

Once this is set up, if the values change in your data, the columns will automatically be formatted based on their value. So with a new set of values we now have this;

Sometimes it is obvious where the high and low points on a line chart are, sometimes not.

Wouldn’t it be nice if you could automatically show the high and low points on a chart and perhaps use a custom marker to make them stand out?  Well you can….using dummy data.

Taking some basic data as an example….

The high point is clear to see but the low point is not so obvious and we need to check the data to see the figures to see that November is lower than June.

You can manually change the appearance of the marker in the chart but if the numbers change for any reason you will then have to manually change the marker, so we need some dummy data to help us identify high and low values in our data which we then need to superimpose on top of our real data.

In a new column add an IF statement to identify the highest value(s) in the data.

=IF(B2=MAX($B$2:$B$13),B2,NA())

This will test the value of cell B2 against all the other cells and if it is the highest value it will be displayed in cell C2. If not, then it will display #N/A.

IMPORTANT: By using NA() it means that nothing will be displayed in the chart. If you use 0, then it will try to plot it. This is a useful little trick to know when charting and is used in many custom type charts using dummy data.

Then, in the next column do the same but this time to identify the lowest value(s).

=IF(B2=MIN($B$2:$B$13),B2,NA())

You should now have this:

Click into the data and create a line chart using ALL the data including the dummy series. This will then give us the following chart:

Straight away we can see there are now 3 series on the chart, but because of our formula using NA() only the MAX and MIN points are visible on the two dummy series.

CAUTION: If you have multiple high or low points, because we have used a line chart it will try to join them with a line. Make sure that you go into FORMAT DATA SERIES, LINE COLOR and set this to NONE and avoid the problem below.

Now to create a custom marker to make the high and low points to make them more than obvious.

Select SHAPES from the INSERT tab, and select a shape of some sort. In this example I am going to select an upwards pointing arrow.

Size and format your shape as appropriate. While the shape is selected press Ctrl + C, then click on your chart and click on the series that represents the high value(s) to select it. Press Ctrl +V – job done. Do the same for the other series and the end result will be like this:

Once the custom marker is in the chart you can delete the original shape.

As well as using shapes you can use any image you like but make sure you save it as a small image before copying it to your chart otherwise the picture will appear in its original size in the chart not the size you reduced it to by clicking and dragging the grab handles.

Now, if the values are changes, the high and low point markers will automatically adjust according the values in the table.

When you have multiple sheets in a workbook or even several workbooks containing data you might want to keep an eye on it’s frustrating having to keep switching from sheet to sheet or having several workbooks open just so you can see what happens in one cell when you make a change to a cell somewhere else.

If you have used Excel for some time you may be aware of the WATCH WINDOW. You can use this to reference any cell in any workbook and ‘see’ what happens to its value as you make changes in another cell. This will work in the same worksheet, different worksheets and different workbooks.

But what if seeing a cell value is not enough? You might be interested in viewing a chart or pivot table or a whole range of cells.

This is where the CAMERA feature comes in handy.

First of all we need to add the CAMERA to the quick access toolbar (QAT) or create a new tab and group if using 2010 to place a button on the ribbon.

To modify the QAT:

      1.      Right click on the QAT

      2.      Select Customize Quick Access Toolbar

      3.      Under Choose commands from: select Commands not in the ribbon

      4.      Scroll down until you find Camera

      5.      Click to select Camera and then click on Add

      6.      Click on OK

Now to use the camera…..

Select the cell or cells you want to ‘take a picture’ of, and click on the Camera icon in the QAT. The mouse pointer will change into cross-hairs. Select the location on the sheet/workbook you want the window to appear in, then click and drag the cursor to create an area in which to show the cells you have just selected.

Then click on

  Click & drag the cursor to get this….

By default the new window will be the same size as the original selection but because this is now an image, it can be resized so that it is not in the way of any existing data in your new sheet/workbook.

If you want to view a chart you can’t simply select the chart itself to take a picture. Highlight calls where the chart is placed, then click on the Camera.

Then do exactly the same as above, click and drag the cursor to create a viewing area. And there you have it. You can now see what is going on in another sheet or workbook and you are not restricted to simply viewing cell data.

In the example below I am displaying the Camera view alongside the original data just to show the resized view.

Now for the word of WARNING:

If you move cells you have taken a picture of, then no problem, but if you move a chart or other ‘floating’ object that sits on top of your selected cells, then it will move away from the Camera window. In the example below, I have coloured in the cells I selected to view the chart. I have then moved the chart across and down, and if you look at the Camera view on the left, you will see that I can still see the same sized area of coloured cells but the chart itself has moved.

You’ve just spent ages sorting out your spreadsheet and are very pleased with the end result. Now it’s time to print. You press the print button, then you see it…. “Printing 1 of 150 pages”!!! In your panic you can’t press the cancel button quickly enough…already you hear tutting and comments around the printer  as people realise that their print job will not come out for ages and that whoever is printing is churning out dozens of pages with only 1 character on each page.  Easily done, but equally easy to prevent.

Before pressing the print button check out the page break view on the view tab.

Next problem you have though, only the first page has your column headings displayed which makes reading the other sheets a bit more difficult. No doubt, like everyone has at some point, you work out where the page breaks are and copy/paste the first row so that each page has its own headings. Problem is, that if you add or remove any rows you then have to go through everything again to reset the headings to the correct row.

No need!

Go to the page layout tab and click on print titles.

This opens a new window and you’ll see the option to repeat rows at top or repeat columns at left.  Select the row(s) and/or columns you want to see repeated…job done!

On the backdrop of the comedy panel quiz 8 Out of 10 Cats there is a statistic that goes along the lines of “53% of surveys are made up”. Although this is there for comedic effect, it actually says quite a lot about statistics or KPI in general. People presenting data make sure that you only see what they want you to see and in the best possible light.

Excel tends to be the default application to do data analysis and a few simple techniques allow you to disguise or distort facts very easily and present them in a favourable light to the unsuspecting viewer (techniques I have used to great effect on numerous occasions!). Check this blog by Jon Peltier, the Excel charting guru, that shows just how easily data can be distorted to prove a point when in fact there is no real point to make and can mislead the viewer into believing the author’s viewpoint (http://bit.ly/lyQaM2)

Also, when information is presented is it almost always without any context. Without reference points it is virtually impossible to decide how we should interpret the data. A simple example: theVaticanStatehas the highest crime rate in the world. This is actually true, based on crimes committed per capita of population, but without any other information we might be led to believe that the Pope and his cardinals run an international crime syndicate akin to S.P.E.C.T.R.E!

In a recent retweet by fellow Tweep (@pimpyourcharts) there was this particularly good quote; “Statistics are like a bikini – what they conceal is more important than what they reveal”. This takes me back to my first point – presenters and tend to only show you what they want you to see.

So next time you are forced to endure a mind and backside numbing 100+ slide presentation (the more slides the better…apparently), when it comes to the stats pages, stir yourself from your presenter induced coma, smile, perhaps nod or look even pleasantly surprised to lull your presenter into a false sense of security. Then while they show off their ‘tailored’ KPIs, ask yourself this very simple question; “What are they NOT telling me?” And then, at the end of the presentation take full advantage of the offer of “Any questions?”, pause briefly then throw in the proverbial cat amongst the pigeons! “Slide 93 showed some interesting facts, but….”, then sit back and smugly watch the presenter squirm and try to come up with plausible answers to the ‘missing data’.

The 1980s....for those of us who were there, despite recession, miners’ strikes, shoulder pads and strange haircuts it was a great decade for a number of things – music (I bet you just started doing the actions to YMCA or Oops upside your head!), the arrival of the first mobile phones, CDs  and one event that left me totally non-plussed: the PC.

Remember those unsightly boxes that basically did very little and relied on huge floppy discs or tapes to transfer data. People would try to get me to be excited over the new technology, but adding numbers or playing space invaders on a tiny greyscale screen was in my eyes hardly worth getting excited over.

And so my avoidance of PCs continued, right through university, two years working in the Far Eastand even four years into my first job.  It was not until 1996 (yes...you did read that right) that I was forcibly led to a PC to enter data into a spreadsheet to print off lists of shipments heading back toJapan every night. I could have done the work quicker manually but change was coming.

Realising that I couldn’t escape, the best strategy was to learn how to use it properly. I invested in an Excel for Dummies book and read it from cover to cover trying everything out.  And so here began my route to the dark side to become an Excel geek!

With my next job I was introduced to Office 95 and ever increasing amounts of data analysis. Once again, I invested in a book. This time it was the Microsoft Excel manual – over 900 pages if I remember correctly, and as before I started at page 1 and worked my way through the entire book trying out as much as possible. As the data amounts increased and the type of analysis became more complex I wasn’t prepared to spend hours manipulating data before I could actually make a start on analysing it which pushed me to learn more functions and work out what each and every bit of the menu could do. The final part of the jigsaw was automation and it was not until several jobs later that I managed to get some formal training on VBA. My journey to the dark side was complete! I was now officially the office Excel geek.