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".

EXCEL TIP: How to make data entry screens easy to use

Most spreadsheets require some kind of data to be entered into cells by the user and there are a couple of simple steps you can take to make this a lot easier for the user to do.

Use colour
First of all, use colour to make it easy to see which cells require data entry. I find that using a quite light colour for the rest of the sheet and "No Fill" for the data entry cells tends to suggest that these are the ones that require input.

Take a look at the example below:

Use protection
No, this is not a safe sex tip. You can use the protection feature in Excel to guide people to those cells by stopping them being able to click in the other cells on the sheet.

Do this by highlighting the data entry cells, selecting "Format Cells", and on the Protection tab, unticking the box that says "Locked".

Then select "Protect Sheet" from the Review tab on the ribbon (or from Tools -> Protection in older versions of Excel). Then untick the box under "Allow all users of this worksheet to:" that says "Select locked cells" (it is up to you whether you use a password, but it is not necessary for this purpose).

This will stop the user from clicking in any of the other cells on the sheet, and will even allow you to use the TAB key to toggle through the data entry cells.

That's it for this week. Short but sweet!

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".

EXCEL TIP: Using data in sentences

Microsoft Office users will tend to use Excel to handle numbers and data, and Word to handle text - which, of course, is how it should be.

Although, I know many accountants who would use Excel to write letters if they could - and many Office users who are not au fait with the workings of spreadsheets that will stretch Word's data handling capabilities well beyond what is decent!

There are, however, legitimate reasons for using text in Excel and some very useful functions that make it work well.

In particular, I am thinking of those times where we want to write sentences that use data.

Some examples are:

• Spreadsheet headings that might include a date, or a department name;
• Notes to accounts that might refer to actual money values within a sentence.

I am sure you can think of many more.

Excel has a really simple way of handling this. and at the heart of it is the ampersand character (&).

The ampersand character can be used to join any pieces of text together in a formula. This text can be actual text (included in quotation marks), or references to text.

So, for example:

="My spreadsheet"&" won't work" returns My spreadsheet won't work

or more usefully:

="Balance Sheet as at 31st March "&A1 returns Balance Sheet as at 31st March 2012 if cell A1 contains the current year (2012)

="The Creditors balance of £"&A1&" includes £"&A2&" due to Group Companies" returns The Creditors balance of £35623 includes £5261 due to Group Companies - where cells A1 and A2 contain those numbers

You can even force the format of the data by using the TEXT function. In the example above, replacing A1 with TEXT(A1,"#,##0") will format it as 35,623. The TEXT function obeys all of the standard rules for formats - including dates, etc.

Now you can stop all that fiddling around with the text elements of your reports and have Excel populate that too!

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".