Search Not Just Numbers

Tuesday 27 November 2012

EXCEL TIP: Creating a rolling graph

A time-taking Excel task that I see many clients accepting as necessary, is copying and pasting graph data every month to populate graphs that show a rolling twelve months. The data is copied and shifted back a month - pasting over the oldest month - with the new month's data entered in the gap.  As well as the time taken, this also has the drawback of losing historical data. The problem is only intensified if we are looking at weekly, or even daily, data.

Another approach I have seen is to enter the data continuously and edit the graph source data to look at the new range. This can still be very fiddly and even more hassle when there are multiple graphs.

If we apply my OAP approach to the problem, we come up with a different approach that requires little more than entering the new month's data.

We first of all look at Obtaining the data. The simplest way to collect the data is to continuously enter the months as they occur - just entering each new month in a column out to the right.

We then look at Analysing the data - well, here we want to see whichever 12 months we wish to report on in the same place every time, so that we do not need to move data, or redirect the graphs.

Finally, we Present the data by pointing the chart at the 12 month sheet.

We just need a simple system to populate the graph data sheet with the 12 months.

The OFFSET function is a great tool for this.

Enter the month at the top of each column in the main data sheet, going out to the right as far as you want into the foreseeable future and enter the data each month as it happens, e.g.



On a second tab you want a similar layout but with only 12 months. The only data you enter on this tab will be the current month (in cell M2). I have used the date format mmm-yy throughout with all dates being the first of the month.


The month in cell L2 is calculated using the formula

=DATE(YEAR(M2),MONTH(M2)-1,1)

this can then be copied back along the row as far as B2, each time it calculates the 1st of the month before. The DATE function is covered in this earlier post.

Then cell B1 includes the function:

=MATCH(B2,'Data Entry'!$2:$2,0)-1

This looks for the date that is in B2, in row 2 of the Data Entry sheet and returns how many columns along it finds it. I have deducted 1 from the result to provide the number of columns to offset from column A to find the correct data.

Cell C1 is just B1+1, continuing in the same vein along the row to M1 (=L1+1).

We then use the OFFSET function in the data fields to use the number in row 1 to pull in the correct data from the data entry sheet.

The formula in the first item of data in cell B4 is:

=OFFSET('Data Entry'!$A4,0,'Graph Data'!B$1)

This returns the cell that is offset from cell A4 of the data entry sheet by zero rows and B1 (11 in the example) columns.

Notice the dollar signs. By applying the dollar signs as we have, when copied along the row, the formula will continue to look at column A of the data sheet, and when copied down, it will continue to look at row 1 of the Graph Data sheet. Meaning that the formula can be copied all the way across and down to as many data fields as we want to add.

Set up all of your graphs to point at the 12 months on the Graph Data sheet and you will never need to change them.

In future, you just need to enter the data in the data sheet as it becomes available, and enter the last month of the 12 month period you want your graphs to look at in cell M2 of the Graph Data sheet.

I hope you've found this useful. I am currently planning some video training packages and I would really appreciate it if you could use the comments below this blog post (and others in future), to let me know what you find most useful about these posts - and what you don't - to help direct my planning.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report "The 5 Excel features that you NEED to know".

9 comments:

  1. Glen

    Enjoyed this post and could be quite useful for 12 month reporting, rather than our traditional FY. I will use it for our rolling forecasts that we hope to produce. These functions are great for creating dynamic reports, I use the INDEX function as well for YTD totals.
    Video packages would be a great idea and I think just on a "How to..." approach that won't be too long and people can use as an aide memoire.

    ReplyDelete
  2. Thanks for the comment Andrew.

    Take a look at an earlier post I did on using OFFSET for budgets:

    http://www.notjustnumbers.co.uk/2010/02/using-offset-function-in-microsoft.html

    You might find this useful for your YTD totals.

    ReplyDelete
  3. Hi Glen
    Long time no see! I am new to your forum and enjoyed this post too. Quite a useful tip when you think how easy it is for financial data to be automatically extracted from an acccounts system into a DataEntry sheet aswell. Paul

    ReplyDelete
  4. Paul

    I noticed you'd subscribed. Good to hear from you.

    I do a lot of stuff importing from financial systems, however accounting systems can be very bad at providing this kind of rolling data, so many people need to key this data in. Typically you the tables hold current year figures, but not the last 12 months - without a lot of jiggery pokery!

    ReplyDelete
  5. Oh Glen! Why didn't I think of this about 18 months ago - it would have saved me a lot of messing about. Thank you.

    ReplyDelete
    Replies
    1. Tricia

      I'm sorry I didn't post this 18 months ago!

      Delete
  6. I add your blog to my favourite.

    regards,
    rafyandi.sjofjan@siemens.com

    ReplyDelete
  7. A sample sheet would be useful, and the same process but including the vertical axis as well as the horizontal one

    ReplyDelete
    Replies
    1. Thanks for the feedback, I'll try to cover those in a future post.

      Delete