Blog Archives

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;

distorted-pencil-chart

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-data

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;

petrol-shipments-chart

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.

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 – How to Create a Trellis Chart

There are many ways of displaying data within Excel, and the basic charts that come as standard are perfectly adequate for most jobs. A line chart or clustered column chart is often the best way to display data over time. They are both clear and simple to interpret and at a glance can show you a trend or pattern.

There are times, though, when you might want to do comparisons of data. Having multiple lines or columns can sometimes work, but there may be times when you want to compare two set of data side by side.

This is where a Trellis chart may be useful.

A Trellis chart allows you to place to data points on the same line showing a direct comparison between the two data points. Let’s say we want to compare sales of a number of products over a two year period.

We could use either a line or column/bar chart. It works, but perhaps having the data for each customer on a separate line might be better.

Using standard fare columns and line charts

Using standard fare column and line charts

First of all we need to set up our data correctly in order to create the Trellis chart.

In a column to the left of your data add a series of zeros. This will make more sense a few steps further on.

Our zero values t help create data points for labels

Our zero values t help create data points for labels

Add two more columns to the right of your data and label these the same as your two original data columns – in this example 2014 and 2015.

In your new columns rank your rows in descending order.

Setting a rank for each row

Setting a rank for each row

Next add one more column and let’s call it LABELS. Add the same descending rank numbers again to this one too.

Adding a ranked labels column

Adding a ranked labels column

Our data table is now set up.

Select all the data from column C to F and click on INSERT, and from there select SCATTER chart, which will give you this;

Initial chart appearance

Initial chart appearance

Click on the chart to activate it, and go to the FORMAT tab. From the CURRENT SELECTION group on the far left, use the drop down to select elements from the chart.

The drop down to select an element of the chart

The drop down to select an element of the chart

First, select LABELS. You should now see this;

Re-selecting our data

Currently selected data for labels

Grab the PURPLE box that is currently around the 2015 series, and drag it over to the column made up of zeros in column A, so you should end up looking like this;

...after the change has been competed

…after the change has been completed

Now select 2014 series and this time drag the PURPLE box over the data in column B. Your chart should now be taking shape;

ScreenHunter_204 Aug. 31 11.49

Starting to take shape

Remove any vertical grid lines, as these are distracting as we want to be able to compare two data points along a line rather than on a grid.

No vertical gridlines

No vertical grid lines

At the moment the LABELS on the Y axis don’t tell us anything useful, they are merely markers that have helped us to align the data points. We need to attach some labels that tell what each line represents i.e. the customer name.

Create some custom labels in some blank cells. If you have 2013 (or later, assuming the same functionality is carried over) click on the chart, then click on the plus symbol and click the arrow to the right of DATA LABELS and select MORE OPTIONS. Untick the Y VALUE option to remove the current labels and tick VALUE FROM CELLS instead.

Then highlight the cells containing the custom labels you created a moment ago. Make sure you select LEFT as the position. You may need to adjust the PLOT AREA to make sure the labels are clear of the chart. Format the Y axis so that so LABELS are set to NONE. Finally make the markers “invisible” as we don’t need to see them, but they do need to remain in place, and remove LABELS from the LEGEND. Your chart should now look like this;

With added custom labels so it makes more sense

With added custom labels so it makes more sense

Feel free then to customise your chart as you see fit to make sure people understand what you are showing them.

The completed Trellis chart with a bit of customisation

The completed Trellis chart with a bit of customisation

If you don’t have Excel 2013, you can still do all this, but you will need the X Y CHART LABELER add-in by Rob Bovey. For more information on this useful little add-in see my blog  https://excelmate.wordpress.com/wp-admin/post.php?post=897&action=edit

So that’s how you can build a Trellis chart. It’s an alternative chart to a column or line chart to compare values. Like all custom charts (or even just charts in general), it’s about using the most appropriate chart for the job, rather than just using it because you know how to, but this one does have its uses and can help to tell a visual story.

Excel – Using the “X Y Chart Labeler”

If you have ever looked around for tutorials on charts or read through books on charting techniques, one thing that pops up regularly is the X Y Chart Labeler by Rob Bovey.

This is a great little add-in, and best of all it’s free. You can download it from http://www.appspro.com/Utilities/Utilities.htm

So what does it do, and why is it so great?

When you create a chart in versions up to 2010, you can add labels to your series with one of the following;

  • Series name
  • Category name
  • Value
Label options 2010

Label options 2010

In most cases this is ample, but what if you wanted to put something different? You could re-label everything in your data table but then it probably won’t make sense to anyone else. If you use scatter diagrams or bubble charts you can’t even do that as you need both x and y coordinates to place your markers and these act as labels. Identifying the individual scatter points or bubbles becomes quite difficult. You could add text boxes manually to everything but if the data point moves the text box doesn’t, so you have to move everything again by hand. All quite painful really.

So this is where the X Y Chart Labeler comes in.

On the Ribbon

On the Ribbon

Let’s take a simple example using a table showing sales by customer. What might be of interest is to show the name of the account manager against the customer sales. I could show sales for each customer or sales for each account manager but not both at the same time. Not easily, anyway.

Basic table showing customers and sales

Basic table showing customers and sales

You create your chart as normal, but then you create a set of custom labels, or as in this case the names of the account managers. You will need one label per data point/series in order for it to work, but there is functionality within the tool to show/hide individual labels.

List of account managers

List of account managers

Assuming you have installed and turned on the add-in, click on the X Y CHART LABELS tab and click on ADD LABELS.

Tab detail of the Chart Labeler

Tab detail of the Chart Labeler

The add labels form

The add labels form

Pick the series you want to label from the drop down list, then select the cells that contain your new custom labels. Select where you want them to appear (top/bottom/centre) and click on OK.

Completed chart with custom labels

Completed chart with custom labels

Your new labels will now appear on the chart. Job done!

If the position is not quite right, click on MOVE LABELS.

Move labels form

Move labels form

Set the number of points you want your labels to move with each click of the arrows, and using the arrows, relocate your labels. You can apply the move to all labels in one go, or select individual labels to apply the movement to.

Other functionality within this add-in;

Manual labeller: allows you to manually label individual data points using either already existing values in cells or by typing a value directly into the form.

Manual labels form

Manual labels form

Delete labels: as the name suggests, you can delete labels for a whole series or all labels in the chart.

Delete labels form

Delete labels form

Help: it comes with a searchable help file, although to be fair, you can’t really go wrong using this add-in.

Help file

Help file

If you have Excel 2013 (and I’m guessing that this will continue to apply to later versions too) you do have the option to attach custom labels, using cells from your worksheet.

Click on the chart to activate it and click on the plus symbol that you see top right hand corner of the chart area. Select CHART LABELS then click on MORE OPTIONS.  Near the top of the window that appears on the right hand side, you will see the option VALUE FROM CELLS. Tick the option and then select the cells containing your custom labels.

Custom label option in Excel 2013

Custom label option in Excel 2013

Knowing that this functionality exists in 2013+, is there a need for the add-in? Probably yes! You still need the add-in to move, show/hide labels etc. more easily than doing it all manually, so from point of view it’s worth having. Perhaps later versions will include this sort of extra functionality, but until then use the add-in.

Now…some of you will be looking at this and thinking “wow, this is brilliant….but they’ll never let me download it at work”. We all have over zealous IT security police and if they don’t know what it is or can’t support it (even though it needs no support) they block it. The add-in runs off VBA code and Rob Bovey, the developer, has not locked the code down, so you, or any despotic IT jobs worth can look at the code quite easily and read all the helpful notes and comments that are in the modules and forms.

Forms & modules in the add-in

Forms & modules in the add-in

Sample code & comments visible to all

Sample code & comments visible to all

The add-in is perfectly safe. Rob Bovey is a recognised MVP or Microsoft Most Valued Professional, so he’s not some random programmer who is dumping dodgy code on the internet. The add-in is also mentioned by the likes of Mr Excel, Jon Peltier and other leading lights in the Excel world as the go to tool for this job. So the only reason the IT bod will refuse to load it is because he/she knows nothing about VBA, not because the code is seen as a justifiable threat!

So if you need to add custom labels to a chart, get this add-in. Once you have it you’ll be able to apply it to all sorts of custom charts. It’s quick and easy to use.

 

Excel VBA – Automatically Close an Inactive Workbook

A big problem I’m sure many of you have encountered is being locked out of a workbook saved on your network because someone is in it, or more likely, has opened it and forgotten to close it when they’ve finished. There may well be a message giving you some user ID or some other vague name of the person who is currently in the workbook but you have no idea who that person is, whether they are on your floor or even in the same building. Even if you can work out who they are, chances are they are not at their desk and the computer screen is locked. So you end up simply waiting to see if the workbook is ever freed up and praying that no one else beats you to the released workbook.

So what are your alternatives?

You could opt to have a shared workbook allowing multiple users to access it at the same time. This can work, but someone has to take overall ownership of the workbook to agree changes and any conflicts that may arise within the workbook between users. There are also restrictions applied to some of the functionality within a shared workbook, such as not being able to add or modify data validation rules. Some of the lost functionality will definitely add restrictions that may make the workbook unworkable for many people. Check the Microsoft website for a full list of what you can’t do in a shared workbook (https://support.office.com/en-us/article/Use-a-shared-workbook-to-collaborate-79bd9dee-1aa3-49b5-bc27-a1ad28ffcbce?CorrelationId=4365b78b-4666-4d41-adcb-32c850345466&ui=en-US&rs=en-US&ad=US#__excel_features_that)

Another option is to keep the workbook “un-shared” but make it close itself down after a set period of inactivity. Like that, if someone does walk away from the workbook without closing it or simply has it open despite having finished whatever they were doing, a built in timer detects that nothing has happened and automatically closes the workbook so someone else can get in.

In order to do this we are going to need a bit of VBA code.

We will need 3 routines to start with (the name I will give to each routine is shown in brackets…feel free to call yours whatever suits you best);

  1. Start the timer (StartClock)
  2. End the timer (StopClock)
  3. Close the workbook (ShutDown)

Open the Visual Basic Editor (Alt + F11) and find the workbook you want to automate. Right click on it and select INSERT and then click on MODULE.

Adding a new module

Adding a new module

The three routines we need to make all this work need to be created here so they can be called on by event procedures we will create a bit later.

We will need a global variable to store the time each time any of the routines start up. At the top of your module (in General Declarations) declare a PUBLIC variable so it can be recognised anywhere within our project/workbook.

Public NoActivity as Date

The first procedure we will create is to close the workbook and save any changes that have been made to it (you could also choose to not save changes to prevent half-finished data from populating your spreadsheet).

Sub ShutDown ()

Application.DisplayAlerts = False

With ThisWorkbook
.Save
.Close
End with

End Sub

Here we turn off any warning messages in case any pop up, save any changes and then close the workbook. If you don’t want to save any changes you can use .SAVED = TRUE to replace .SAVE.

Sub StartClock ()

NoActivity = Now + TimeValue(“00:05:00”)

Application.OnTime NoActivity , “ShutDown”

End Sub

Here, we populate the variable with the current time plus whatever period you have decided is your limit for inactivity. In this example I’m setting it to 5 minutes.

ONTIME says when we reach now plus 5 minutes, run the SHUTDOWN routine

Sub StopClock ()

On Error Resume Next

Application.OnTime NoActivity, “ShutDown”, , False

End Sub

In this routine, in case anything goes wrong ignore it, and once again if the time limit is reached run the SHUTDOWN routine. The FALSE bit is just there to clear any previously set procedure.

Now that our three routines have been created, we now we need to create some event procedures that will trigger these routines.

If you are unfamiliar with EVENT PROCEDURES, they are routines that start automatically when something happens in the workbook such as the user selecting another sheet. With an EVENT PROCEDURE you are not relying on the user to press a button to run a routine, it simply happens automatically when triggered by an event in the workbook or worksheet.

Double click on ThisWorkbook in the Microsoft Excel Objects folder of your workbook.

Accessing events on ThisWorkbook

Accessing events on ThisWorkbook

Then, from the left drop down box above the script area, select WORKBOOK.

Selecting the Workbook area to add events

Selecting the Workbook area to add events

The editor automatically adds the OPEN event, which in this case is one of the events we need.

In the event add the code CALL STARTCLOCK;

Private Sub Workbook_Open()

Call StartClock

End Sub

Now, whenever the workbook is opened, the STARTCLOCK routine starts and therefore the timer starts. It may be worth adding a message box here too to warn users that the workbook will automatically close after x minutes of inactivity.

In terms of events that mean the user is doing something in the workbook, we want the following events to restart the timer;

Workbook_SheetCalculate

Workbook_SheetSelectionChange

Might be worth adding this event at worksheet level to stop the timer if people are just browsing in the workbook rather than editing…it is still a form of activity…albeit unproductive!

Worksheet_SelectionChange

And we need a final event to stop the timer just before closing the workbook;

Workbook_BeforeClose

So all our event code will look something like this;

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call StopClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_Open()

MsgBox “Please note that this workbook will close,” & vbCr & _
“saving any changes you have made after 5 minutes of  inactivity”, vbInformation, “Warning”

Call StartClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

Call StopClock

Call StartClock

End Sub

—————————————————————————————————————————

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

Call StopClock

Call StartClock

End Sub

Next time you want to post a workbook on a shared drive at work, you now have the following options:

  • leave the workbook as it is and hope that people are sensible and close it when they are done
  • set it up as a shared workbook, accepting whatever restrictions this imposes
  • create a workbook with its own built in shutdown mechanism if nothing happens within a set timescale.

If you want to download a copy of the workbook with this code in it, then follow this link – https://onedrive.live.com/redir?page=view&resid=2C036E200F2C8BCF!255&authkey=!AI887S1-lcOY6RU

 

Excel – Creating a Basic Waterfall Chart

Waterfall charts are used to show how you got from one figure, let’s say at the start of the year, to how you ended up at the figure at the end of the year, but instead of showing a total value for each period, it will show you the change in value from one period to another.

Let’s take a simple example…

Standard column chart

Standard column chart

Above we can see the cash amount left over at the end of each month. If we chart this with a basic column chart it shows those values as solid columns. We can see that there is a change from month to month but we can’t see by how much unless we work it out in our heads.

The waterfall chart (sometimes called a flying bricks, bridge or Mario chart) will display the change in value whether positive or negative as a floating bar. This type of chart is used in a number of ways but examples might include showing cash flow over time, or changes to inventory or staff levels.

Instead of a standard column chart we want to create this;

Waterfall chart

Waterfall chart

There are add-ins you can buy to create a variety of waterfall charts and I will mention this again later. If all you want though is a basic waterfall chart then it can be achieved relatively simply using a handful of formulas and a bit of formatting.

First we need to modify our data table.

Insert four new columns. You can call them anything you like. In this example I have named them;

  • Invisible
  • Increase
  • Decrease
  • Variance

We also need to add a couple of new rows to the table. We need an opening balance (i.e. how much had in the bank at the end of December 2014) and a closing balance at the end of the year.

This will now give us the following layout;

Modified table required to create the waterfall data

Modified table required to create the waterfall data

Next, calculate the change from one period to another.

Simple bit of maths, one cell minus another

=F3 – F2

…and copy down.

Variance calculation

Variance calculation

Now we need to create what will become the floating bars in our waterfall chart. We need one calculation to identify positive values and one for negative values.

In D3 I need the following formula;

=IF(E3>0,E3,0)

This tests the value in the VARIANCE column. As we are looking for positive variances we test for values greater than 0. If the value is negative display a 0 (zero).

Increases calculated

Increases calculated

Now for the negative values. In cell E3 enter the following formula;

=IF(E3<=0,ABS(E3),0)

This time show which values are negative, but as we need these values to be displayed as floating bars, the values need to be converted to positive ones, hence the use of the ABS (absolute) function.

Decreases calculated

Decreases calculated

As a quick check, in each pair of cells representing an INCREASE and a DECREASE, you should find one cell with the value 0 and the other with a positive value in it.

The final calculation we have to do now is work out the INVISIBLE value. We need this to create a value to calculate the start value of our DECREASE/INCREASE bars.. The close up below of the bars in the final waterfall chart shows what we are trying to calculate.

Calculating where bars have to start from

Calculating where bars have to start from

To work these out we need to add the INVISBLE value to the INCREASE minus the DECREASE. Copy across the OPEN and CLOSE values into your INCREASE column and enter the formula as shown below.

Calculating the height of the supporting invisible series

Calculating the height of the supporting invisible series

Looking at the numbers, the sum of the INVISBLE and the DECREASE values in one line should add up to the INVISBLE value in the line above.

Quick check to ensure values are correct

Quick check to ensure values are correct

Copy your formula down and we now have a full set of data from which to build the chart.

Select cells A1 to the bottom of your INCREASE column.

Selecting your data for the chart

Selecting your data for the chart

Go to the INSERT tab and select the 2-D STACKED COLUMN chart.

Ready to apply final formatting

Ready to apply final formatting

All we need now is a bit of formatting to get the final effect.

Select the INVISIBLE series and format the bars to have NO FILL. Then remove INVISBLE from the LEGEND as it is no longer required.

If you want, colour code the INCREASE/DECREASE bars. In this example I will make INCREASE bars green and DECREASE bars red.

To differentiate your OPEN and CLOSE balances from the variance bars, colour these differently. You may have to click twice on the end bars to select them separately from the rest of the series. Pick any colour you like.

As a finishing touch, you can add the variance values to the floating bars using Rob Bovey’s free add-in – X Y CHART LABELER from www.appspro.com

Completed waterfall chart

Completed waterfall chart

And there we have our final chart.

If you don’t fancy doing all those calculations then you might want to consider buying Jon Peltier’s PELTIER TECH CHART UTILITY add-in. The full advanced edition sells currently (April 2015) for $99 (http://peltiertech.com/Utility20/). At that price it may not be for everyone, but if you have to build complex waterfall charts (as well as some other types included in the add-in) on a regular basis then it’s worth investing in.

Excel – Using the Data Form

If you find you are working a lot with long lists of data, you probably find after some time that distinguishing one line from another becomes increasingly difficult…a form of snow blindness if you like. You can format the list as a table and shows lines in alternating colours which might help but wouldn’t it be nice sometimes to be able to see only one line at a time.

Data list formatted as a table with banded rows

Data list formatted as a table with banded rows

Well…you can, using a FORM. You can go to the length of creating a form in VBA, and adding all kinds of buttons and clever functionality, but there is actually a built in FORM creator which will build a form when required on any data as long as it is in a list format i.e. column headings only, no row headings.

In 2003 or earlier versions of Excel, the FORM was available from the standard menu. Since 2007, it has been relegated to just another button in the CUSTOMIZE THE QUICK ACCESS TOOLBAR (2007+) or CUSTOMIZE THE RIBBON (2010+) options.

You will find the FORM button under COMMANDS NOT IN THE RIBBON or ALL COMMANDS.

Finding the FORM button to add to the QAT or RIBBON

Finding the FORM button to add to the QAT or RIBBON

Add this to either the QAT or RIBBON and you are ready to go!

So now you have access to the FORM, what does it do and how does it work?

Click anywhere in your data table and click the FORM button.

The FORM button added to the QAT

The FORM button added to the QAT

 

 

 

 

 

And without any further intervention by you, or working your way through multiple steps of a wizard, Excel will create a user FORM for you.

Viewing a single record using the data form

Viewing a single record using the data form

Using the column headings, there is a separate line for each heading and a text box displaying the information held in that column for a single record.

In the top right hand corner you will see the total number of records in your list. Using the scroll bar between the data and the buttons you can move from one row to another using the arrows at the top and bottom of the scroll bar or click and drag the scroll bar itself to move quickly through the records.

Not only does the FORM allow you view records, but you can also edit the information directly in the FORM which will update the spreadsheet. There is extra functionality available using the buttons on the right hand side.

Buttons on the data form

Buttons on the data form

New: To add new records to your list click here and start typing the information directly into the text box next to the heading. Sometimes, you will see some values/data in the form that does not appear in a white text box. This will be a calculated field or perhaps a look up function based on something you enter in another box. Because these cells in the worksheet are reliant on a formula you cannot enter information directly here.

A form showing both data entry boxes and un-editable formulas

A form showing both data entry boxes and un-editable formulas

One major downside to using the FORM is that if your data table has drop down lists to select values from, these are not transferred to the FORM, so you need to type values in manually.

One advantage of using a FORM to add new records, is that if your data is stored as a NAMED RANGE, any new records you add are automatically added to the NAMED RANGE so you don’t have to manually adjust the REFERS TO bit in the NAME MANAGER. If you are unsure about NAMED RANGES then refer to my blog http://wp.me/p2EAVc-99 to find out more. There is however, one caveat to this. A FORM will only add to an existing NAMED RANGE if it is called “database”. Call it anything else and the NAMED RANGE is fixed and it will not automatically expand to include new records.

Delete: Does exactly what you would imagine…it deletes the record from your table/list – permanently. Note that I said it deletes the record, not just the highlighted piece of information in the FORM.

Restore: If you make a change to anything in the FORM other than delete a record, this button becomes active and allows you restore the data back to its original value.

Criteria: I’ll jump to CRITERIA next because the FIND PREV and FIND NEXT buttons are generally used in conjunction with this button. When you click on CRITERIA it clears the FORM. This now becomes a search FORM. Type search criteria in one or more of the text boxes. You can enter whole values or you can use wildcards. Using FIND PREV and FIND NEXT then allows you to scroll through records that match your search criteria. When you click on CRITERIA you will see a CLEAR button that will clear the form of any criteria you have entered, and a FORM button that will return you the standard form.

So what wildcards can I use?

*(star/asterisk) denotes any number of characters.

H* will search for any value that begins with H followed by any number of characters.

*h will search for any value that ends in h.

? (question mark) denotes any single character

H?p will search for Hip, Hop, Hup, H3p etc.

You can then combine these together or use them in conjunction with < or >.

>h* searches for any names (for example) that begin with the letter h, I, j, k etc. and beyond.

<>h* will search for any values that don’t start with h.

Example showing multiple search criteria

Example showing multiple search criteria

So use any operators you are familiar with (<, >, <=, >=, <>) either on their own or with wildcards and you should be able to find just about anything in your list. Use the FIND PREV and FIND NEXT to scroll through the records that meet your criteria.

And finally Close which unsurprisingly closes the FORM.

So next time you are working with a list of data, give the FORM a go, it might be of use…might not.

Excel – Using the Formula Auditing Tools

Once you’ve created a spreadsheet or you get one from someone else, one of the more painful things you end up doing at some point is trying to work out which cells feed into any of the formulas throughout the workbook or alternatively, which cells are dependent on any given cell(s).

Finding which cells feed into a single formula is easy, just by clicking on the formula cell which then highlights all the cells that feed into it.

Checking a single formula's data source

Checking a single formula’s data source

However, this is very limited as you can only check out one formula at a time and there is no way of knowing if any of the cells that feed into your formula are in turn dependent on other cells for their values short of checking each and every cell which of course no sane person would do.

So how can you check this without spending hours looking at all the cells in your worksheet/workbook?

This is where the FORMULA AUDITING tools come in. On your FORMULAS tab you will find the FORMULA AUDITING group.

Formula auditing group

Formula auditing group

In this blog I am going to concentrate on TRACE PRECEDENTS and TRACE DEPENDENTS, ERROR CHECKING and will write separately about EVALUATE FORMULA.

I will briefly mention SHOW FORMULAS before starting on the main topics of this blog. As the name suggests, this will convert all cells with formulas on the current worksheet from displaying their values to displaying the formulas contained within them.

Viewing all formulas on a worksheet

Viewing all formulas on a worksheet

You can achieve the same effect using CTRL + ` (on an English – UK QWERTY keyboard). It’s a quick way of viewing all formulas on a worksheet without the need to view each cell separately. Can’t say any more than that!

So how do you find out which cells feed from or into another cell?

Let’s start by finding out which cells supply a formula cell with its inputs. Click on any cell containing a formula and then click on TRACE PRECEDENTS.

ScreenHunter_191 Mar. 29 13.13

Showing precedents feeding into a formula cell

Any cell(s) that provide data to your formula should now be highlighted. But do any cells feed those before they feed into the formula cell? Simply click the TRACE PRECEDENTS button again.

Tracing inputs further back

Tracing inputs further back

Keep on clicking until it can go no further i.e. you have reached the original source to all the data feeding into your formula.

Now that you’ve found out all the cells that feed into your formula, is the result of your formula used anywhere else. In other words, are cells DEPENDENT on this one?

Staying on the same formula cell, now click on TRACE DEPENDENTS.

Tracing cells that are dependent on the current cell

Tracing cells that are dependent on the current cell

As with TRACE PRECEDENTS if you continue to click the TRACE DEPENDENTS button you will eventually get to the final cell in the chain that is influenced, however remotely, by the currently selected cell.

There will be times when you check for PRECEDENTS or DEPENDENTS and you will see this;

Showing data comes from outside the current worksheet

Showing data comes from outside the current worksheet

This tells you that data from an external source is feeding into the cell. This can mean from another worksheet in the same workbook, or from another workbook altogether. Double click the dashed line and it will open the GO TO dialog box.

Finding external cells that make up part of the formula

Finding external cells that make up part of the formula

This will show you the source of the data and if you click on the reference and click on OK, it will take you the feeder cell if it’s in the same workbook, or if the other workbook is open at the time.

There comes a point where there are too many lines/arrows to follow. To remove any lines/arrows click on REMOVE ARROWS to remove all visible lines/arrows, or select REMOVE PRECEDENT ARROWS or REMOVE DEPENDENT ARROWS depending on what you want clear off screen.

Remove trace arrows

Remove trace arrows

Alternatively, you can trace precedent and dependent cells by using CTRL + [and CTRL + ] respectively which simply highlights any cell(s) that either precede of follow the currently highlighted cell.

If your formulas produce error messages such as #DIV/0! And you have eliminated syntax errors, it could be that one of the feeder cells somewhere in the chain of cells has an error. Use ERROR CHECKING to find and correct those errors.

Error checking options

Error checking options

Click on TRACE ERROR to show all cells feeding into the formula producing the error. This works just like TRACE PRECEDENTS showing any external links.

Error tracing arrows

Error tracing arrows

For more information select ERROR CHECKING. This will show you where the error is and what type of error it is. You then have access to a number of options such as EDIT IN FORMULA BAR or IGNORE ERROR in case the problem is simply due to incomplete or missing data which will correct itself once it is updated.

Error tracking options

Error tracking options

So next time you are not sure where a formula gets its inputs from, or you need to trace an error you can now use your AUDITING TOOLS to help you out a bit.

 

 

Excel – Dynamic Range 12 Month Rolling Horizontal Chart

Horizontal 12 Month Rolling Chart

After publishing my blog on dynamic ranges to create a 12 month rolling chart (http://wp.me/p2EAVc-ah), I have received a number of enquiries about how to apply this to a horizontal table of data.

Personally, I prefer to have my data tables set vertically as it gives me far more options to slice and dice my data, apply filters, pivots etc. Based on the number of questions I have had there seem to be quite a number of businesses out there using a horizontal data layout.

So here is how it’s done;

The formula you will need will be as follows (bearing in mind you may need to adapt this to your own data table);

=OFFSET(Sheet1!$B$2,0,COUNT(Sheet1!$2:$2)-1,1,-12)

Our anchor point is $B$2, and always starting in the same row (0). The COUNT function counts the number of cells in row 2 and takes away 1 to give the correct end cell i.e. the last cell in the row to contain a numerical value. The height of the range is 1 row but the width is 12 columns back from the last cell that was identified by the COUNT function.

As we are applying this to a chart, we also need to create a dynamic range to pick out the correct labels;

=OFFSET(DynHorizData,-1,0)

As explained in the earlier blog, this dynamic range is based on the dynamic data range we created above. “DynHorizData2 is just the name I assigned to the range.

And there you have it…a 12 month rolling chart based on a horizontal data table.

To see an example using the formulas above follow the link below to open a workbook (Excel 2013) called “Horizontal Dynamic Chart.xlsx”. The workbook is read only, so click on FILE and Save As to download the file and then you can edit the data and see what happens.

https://onedrive.live.com/redir?resid=2C036E200F2C8BCF!242&authkey=!AKh4mjBK2AWbTnw&ithint=file%2cxlsx