Christmas is coming...

Everyone is subscribing to make sure they don't miss
out on their exclusive Secret Santa Gift

Are you all ready for Christmas? If you’re one of those people who have had all of your presents in for weeks, I don’t want to know – it only makes me feel inadequate!

One present I have got ready though is my exclusive Secret Santa gift for my subscribers, which will be emailed out on Christmas day.

If you haven’t already subscribed, do it now in the box on the right of the blog - so Santa doesn't miss you out!

Today's post is a little different, I just wanted to pick up on some of the feedback I received from my post a couple of weeks ago, asking what you wanted to learn from the blog.

Thanks again for the response, it's great to know that so many people are appreciating my posts and to find out what you want to see in the future. After all, a blog is worth nothing without its readers!

Last week I picked up on Bob's request to learn how to prepare a simple cashbook - I hope that did the job for you Bob!

Some other requests, which I think will make their way into future blog posts are:

• A few of you wanted to know more about pulling data into Excel from accounting systems, such as Sage. I have posted on this before, but didn't really cover the detail
• There were a number of requests for more on charts (including sparklines) and pivot tables
• Another popular request was a simple cashflow forecasting spreadsheet

There were also a few things requested that I think I have covered in earlier posts, although they may benefit from a revisit:

• Rolling Graphs
• Dropdown Lists

I also received a couple of requests for more on VBA, however this is an area I intentionally avoid on the blog, as the readership is primarily made up of Excel users rather than programmers. VBA is a massive area in itself, which has much more to do with programming than with Excel. In my experience, most Excel challenges can be handled without VBA, as long as a little creativity is applied.

There are many blogs out there that do a very good job on VBA, for those who do want to read more on it.

I also received a few meatier requests that go beyond a single blog post, and these might be more appropriately addressed in future video training packages, for example:

• Automating the management accounts process
• More depth on my OAP approach

Thanks again for all the feedback, keep it coming! 

Well, if you're are one of those that still haven't subscribed, that’s it for 2012. Unless you go to the top of the blog and subscribe now.

If you have subscribed, you’ll be hearing from me on Christmas Day, with your exclusive Secret Santa Gift.

Either way, have a fantastic Christmas break and I look forward to continuing our journey together in 2013.

Back to the Christmas Shopping!

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: A simple cashbook - the OAP way

Thanks everyone for your response to last week's post, telling me what you want to learn from the blog. I've had some great input and will be including some of your requests over the coming weeks. If you haven't yet submitted a request, I'd love to hear from you, just enter it here.

One request was from Bob, an accountant:

"I would like to know the simplest way to create a cashbook for my clients that enabled them to record their income and expenditure and be able to have a quick snapshot of their business- a poor man's QuickBooks if you like.
I have tried several times but end up with 100+ worksheets."

I have not seen Bob's spreadsheet, but I have seen many like it and the problem always comes down to the approach taken. Without knowing how to break an Excel job down,  we end up with a complex beast that still doesn't really do the job. This is why I developed my OAP approach which breaks any Excel job down into three steps:

O - Obtain the data
A - Analyse the data
P - Present the results

By breaking the job down like this, each stage can be focused on its purpose - providing the best input for the next stage.

The accountant's idea of a cashbook developed on paper, where all of these parts of the job needed to be done as part of the same "worksheet", otherwise you would be increasing the workload by having to rewrite information. This rewriting costs no time in a spreadsheet, therefore a different approach can, and should, be taken.

O - Obtain the data

For this job, Bob needs a simple data entry sheet where his client can enter each of the transactions, one row per transaction, in one long table, month after month, year after year. This is the best form to capture the data to make it easy to analyse.

The data entry is done in columns A to E. We will discuss columns F to I later in this post.

The following blog posts might be useful in understanding this approach:

99% of Excel users get this wrong - How do you lay out your data?

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

The Type and Expense/Income Name columns are dropdowns, Type being a simple dropdown with the choices being Receipt or Payment, the Expense/Income Name column dropdown being driven by an editable list on a separate sheet:

The Name column (A) is used to drive the list. The category column on this sheet is another simple dropdown (Income or Expenditure), which we will use later.

A - Analyse the data

Once the data entry sheet is in this format, we can use formulae in columns F to I, to calculate the additional information we need to provide the numbers that we will ultimately present.

Columns F and G use the YEAR and MONTH functions to strip out the year and month from the date field so that we can filter reports by these values.

Column H is a simple IF statement that returns the value if column B says "Receipt", otherwise it returns the value times minus one:

=IF(B2="Receipt",C2,-C2)

Column I uses VLOOKUP to return the category column for the categories list above.

P - Present the results

Finally, we use a Pivot Table to present the results:

You can now report on the data for any month or a whole year, from the dropdowns at the top and we have three worksheets - one for data entry, one for the report, and one to enter the information for the dropdown lists.

Although this is a very simple cashbook, the same principles can be used to both add additional data entry columns, and different reports - running off the same data list.

As a response to Andrew, another accountant who asked that I provide more examples of the spreadsheets used, you can download this one here.

There is protection on the data entry and list sheets that you will need to turn off to view the formulae, which you can do by selecting Unprotect Sheet from either the Review ribbon, or the Tools Menu (under the Protection subheading), depending on which version of Excel you are using - there is no password.

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 - What would you like to learn?

How would you like a future blog post to provide help on a subject of your choosing?

I am working on planning subjects for blog posts for the new year and would really appreciate hearing what you would like to learn about (as long as it is Excel-related!).

Just fill in your request in the form below (along with  a couple of other bits of information that will help me to put it into context), and I will use the responses to guide me when planning my future posts.

For those of you who would rather see an Excel Tip in this post - I haven't forgotten you. There is a quick Excel Tip  for you - when you complete the form.




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

EXCEL TIP: Look up commission or tax rates from earning bands

Just a short post today on a very useful use for the VLOOKUP function.

Typically when we use VLOOKUP, we are looking for an exact match from a list. For example, we might look up a customer's address, based upon their account code.

But what if we want to look up the correct commission rate or tax rate, based upon what earnings band a particular value falls into?

VLOOKUP handles this extremely well, when you understand its mysterious fourth (optional) argument.

The syntax of a VLOOKUP function is:

=VLOOKUP(lookup value,range to lookup,column to return,TRUE or FALSE)

I you need a recap on using the VLOOKUP function, take a look at my earlier post on the subject.

First of all though, let us outline the problem with an example.

We have a cell (D2) containing an income figure upon which we need to calculate a sales commission.

However, the rate of commission changes depending on the size of the income figure as follows:

0 - 9,999  pays 10%
10,000 - 49,999 pays 20%
and 50,000+ pays 25%

How do we calculate what rate to use?

We could use IF statements, however this falls down with more bands and is very inflexible when it comes to changing the bands. A far better solution is to use the VLOOKUP function.

We need to create a two column table with the start of the band in the first column, and the rate to use in the second. This table must be in ascending order of earnings, i.e.

When we set the final argument in the VLOOKUP to TRUE (or leave it blank), it scans the lookup table for the highest earnings value that is still less than the lookup value, so:

=VLOOKUP(D2,A2:B4,2,TRUE)

will return the rate we are looking for.

We can also use exactly the same approach for other similar problems, such as looking up tax rates.

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

Richard Branson, First Group, The Department for Transport and your Excel Spreadsheet

A major news story in the UK in recent weeks has been the embarrassing U-turn of the government on their award of the West Coast rail franchise to First Group, following complaints from the incumbent, Richard Branson's Virgin.

Full story here

The problems appear to have been caused by unrealistic assumptions on rates of inflation and passenger growth.

This got me thinking about the use of assumptions and variables in Excel spreadsheets generally.

Obviously, any assumptions should be clearly visible and I would suggest (if space allows) above the forecast (or whatever the main content of the spreadsheet is).

I'm sure we all know this to be good practice but it doesn't mean we always do it!

A second, slightly more subtle, rule that I always (try to) follow helps avoid so many of these types of errors.

Make these assumptions an active part of the spreadsheet

What I mean by this is instead of entering the text, say, "Inflation is assumed to be at the rate of 2%", enter the name of the variable in one column and the value in another, e.g.

Then wherever inflation is used in the spreadsheet, always reference this cell (B3 in the example above). This way you always know that what you have stated in the assumptions is what your spreadsheet is using.

You can refer to this cell in two ways, either:

1. Directly, i.e. $B$3 - Note the dollar signs to ensure that if you copy your formula elsewhere in the spreadsheet, it still references the cell, or;
2. As a named range, i.e. name cell B3 Inflation and refer to the range Inflation in your formula.

The second approach makes the formula easier to understand, but the first is usually quicker.

Another significant benefit of this approach is when these rates need to change. I come across so many spreadsheets that calculate VAT (a UK sales tax - currently 20%) on the value in cell A2 as =A2*0.2. When the rate changes, all of the calculations need to be checked, where if the VAT rate had been entered in a cell as above, it would be as simple as changing the rate in that cell!

Simple tips, I know, but many a complex spreadsheet has been brought down by not following them - just ask the Department for Transport!

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: Manipulating Account Codes

There are many times, particularly in accounting spreadsheets, where we are using codes e.g. Stock Codes, Nominal/Sales/Purchase Ledger Codes. Often there is useful information hidden within these codes, such as Bin Locations, Departments, Divisions, Regions, etc.

By pulling out this information, we can then lookup additional information, sort or filter by it, or use it for analysis in a pivot table.

But how do we get at it?

Fixed formats

The simplest situation is where the code is a fixed length and the information we need is always in the same position.

Here we can use the function LEFT, RIGHT or MID depending on its position.

=LEFT("text",x) returns the leftmost x (first) characters in "text"
=RIGHT("text",x) returns the rightmost x (last) characters in "text"
=MID("text",y,x) returns x characters starting at position y in "text"

So, where the cell A1 contains the fixed format account code 123-456-789,we can pull out any of the three sets of numbers as follows:

To return the 123, use =LEFT(A1,3)

To return the 456, use =MID(A1,5,3) and

To return the 789, use =RIGHT(A1,3)

The functions above will allow you to extract any section of a fixed format code, but what if the code has a more complex structure?

Flexible codes

Sometimes a code may have a less fixed structure, for example if in the code above, the length of the code was variable, as was the length of each of the three components - so it could take (for example) the form 12-345-67, or 1234-56-789. As long as there is some indicator of where the change is, then we can still do it. In this case we have the "-".

We can use the functions above, but we also need to use the functions FIND and LEN to calculate the arguments for those functions.

=FIND("text to find","main text"

,x) returns the position of "text to find"  in "main text" 

, starting at position x. This last argument is optional and it starts at 1 if left out.

=LEN("text") returns the length of "text", in characters.

so where A1 again contains the account code 123-456-789

=LEFT(A1,FIND("-",A1)-1) returns 123

Explanation:  FIND("-",A1) returns the position of the first "-" and by deducting 1 we have calculated the position of the last character of the first section (3) and therefore the x argument for the LEFT function

=MID(A1,FIND("-",A1)+1,FIND("-",A1,FIND("-",A1)+1)-1-FIND("-",A1)) returns 456

Explanation: Not as scary as it looks when you break down the three arguments for the MID function.

The first argument is the easy one, just the cell with the code in, A1

The second argument, being the starting point of the code we are looking for is FIND("-",A1)+1, which is simply the position of the first "-", plus 1.

The third argument is the tricky one which needs to find the number of characters to return. We do this by finding the position of the character before the second "-" and deducting the position of the first "-"

FIND("-",A1,FIND("-",A1)+1) returns the position of the second "-" by using the FIND function again but using the optional start position argument so that we do not start looking until the character after the first "-". We have already worked out a formula to find this position (as it was the second argument of the MID function. We then deduct 1 to get the character before it.

FIND("-",A1) returns the position of the first "-" which we deduct, giving the full third argument of the MID function as FIND("-",A1,FIND("-",A1)+1)-1-FIND("-",A1).

=RIGHT(A1,LEN(A1)-FIND("-",A1,FIND("-",A1)+1)) returns 789

Explanation: Here we use the length of the string minus the position of the second "-" (calculated as above) to determine the number of characters to return from the RIGHT function.

There are many other possibilities, but a combination of these functions should give you a route to pull out pretty much anything from a code.

Good Luck!

If you enjoyed this post, go to the top left corner 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: Identifying irregular period-ends

A couple of weeks ago I did a post on identifying the calendar month-end from a particular date, and I promised to explain how to do this when your month-ends are irregular - as is still quite common in a manufacturing environment. Here is that post.

You may have any set of rules that govern when the month-end (or period-end if they're really irregular) is - "last Friday of the month" for example, but there are nearly always exceptions - usually around Christmas and other holidays.

The most flexible way to address this issue is to have a table of month-ends held in the spreadsheet that can be adjusted to whatever you want.

This can be a simple two column table with period start-dates in the first column and period end-dates in the second.

You can then use VLOOKUP to find the first start date that is before the transaction date in question. We can then return the period-end date from the second column.

For example, where the range containing the two-column table described above is called PERIODENDS and the transaction date in question is held in cell A1, we can use:

=VLOOKUP(A1,PERIODENDS,2) to return the corresponding period-end date.

If we wished to return the period number, we could add this in a third column in the table and use:

=VLOOKUP(A1,PERIODENDS,3)

Notice that I have not entered the optional fourth argument in the VLOOKUP function [range lookup]. This argument is normally entered as FALSE if you wish to look for an exact match, but if omitted (or entered as TRUE) the VLOOKUP identifies the first entry in the column (the column must be in ascending order) which is exceeded by the lookup value. In this case, the first period start date that the transaction date exceeds - i.e. exactly what we want!


If you enjoyed this post, go to the top left corner 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: Making Accounting periods useful in Excel

Often we have data, particularly in accounting systems, stored by Year and Period Number.

This may be weekly takings, with year being the accounting year and the period number being the week number, or it might simply be account movements or payroll by accounting year and accounting period, 1 to 12.

This is a sensible way to store the data but it can make selecting a range quite tricky, so I thought I would share a little trick I use to simplify this problem.

The problem:


Where data has a date attached, for example a transaction date, or a month end date for the type of data we are discussing here, we can use the following IF statement to determine whether a transaction/movement is within a date range:

Where A2 holds the transaction date and G1 holds the Start Date of the range we want to look at and H1 holds the End Date.

=IF(AND(A2>=$G$1,A2<=$H$1),"Yes","No")

Answers the question whether A2 is within the range G1 to H1.

(If you need a refresher on the IF statement, take a look at my earlier post).

Where we have a Year and Period Number, this doesn't work. If the range straddles a year end, you want to include, say, period 12 from the first year but not from the second.

The Solution:

To make it much easier, we need to create a working column that combines the two into a sequential number. We can do this with a simple calculation:

Where B2 holds the year and C2 holds the period we can enter the following in D2:

=(B2*100)+C2

This will convert the year and period into a 6-digit number where all periods are in sequence, so...

Where B2=2012 and C2 =7, the formula returns 201200 + 7 = 201207

It is possible that the year and period are held as text rather than as a number, in this case you would use the VALUE function to convert the text to values:

=(VALUE(B2)*100)+VALUE(C2)

If we now state the start and end of the period range in the same format (in cells G1 and H1 as before), we can use the same approach as for dates to identify the range using our working column D, i.e.

=IF(AND(D2>=$G$1,D2<=$H$1),"Yes","No")

Column D could also be used to sort the data, as we now how have a column that identifies the correct chronological order.


If you enjoyed this post, go to the top left corner 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: Finding the month end

Here is a useful tip for finding the month-end from a transaction date

I often have a need to identify the month-end date relating to a particular transaction during the month, i.e. we have the date of the transaction and need to return the date of the month-end.

NB: Most businesses in my experience tend to use calendar months for accounting periods these days, however some (particularly in manufacturing) still have month-ends on a particular day of the week. My tip below will only work for calendar months, but I may cover irregular month-ends in a future post (hint, you will need VLOOKUP).


Another NB: It has been pointed out to me by a number of readers (see James Travers comment below) that there is a quicker solution than that outlined below for users of Excel 2010. It uses a function which I didn't know was there as I didn't need it, having an approach that served me well in earlier versions. In Excel 2010, you can use =EOMONTH(A1,0), where A1 contains the transaction date. The method below is still worth knowing as you will need it if a file has to be accessed by earlier Excel versions. It also uses a number of techniques for manipulating dates that you can use for other purposes.

Here we can use an interesting and very useful feature of the DATE function, along with the YEAR and MONTH functions.

The DATE function returns a date given the year month and day, and is entered as follows:

=DATE(year,month,day)

so =DATE(2012,7,16) returns 16th July 2012 (in whatever date format you have set for the cell).

The YEAR, MONTH and DAY functions work the other way, in that they pull out the year, month and day numbers respectively from a date, so if cell A1 contains the date 16th July 2012, =YEAR(A1) will return 2012.

We could use this to pull out a particular day of the month from any transaction, so if we wanted to return the 25th day of the month of the transaction (where the transaction date,16th July 2012, is held in cell A1), we could use:

=DATE(YEAR(A1),MONTH(A1),25)

which would return 25th July 2012.

However, the actual month-end day obviously varies between the 28th and the 31st. This is where the useful quirk of the DATE formula comes in. Day zero of the month is the last day of the previous month, so

DATE(2012,8,0) returns 31st July 2012.

We can therefore amend the DATE formula above to provide the month end for the transaction date in A1,  to provide:

The Answer:

=DATE(YEAR(A1),MONTH(A1)+1,0)

And don't worry, those of you who are seeing a potential problem around the year end, when MONTH(A1) equals 13. The month formula works in a similarly useful way, so that month 13 of 2012 is month 1 of 2013, so:

=DATE(2012,13,0) returns 31st December 2012 or even;

=DATE(2012,15,25) returns 25th March 2013.


If you enjoyed this post, go to the top left corner 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 conditional formatting to format the whole row

Colour-coding can make it much easier for humans to read a spreadsheet, as our eyes and brains are wired up to treat differences in colour as important. For example, you may colour rows as red, amber or green based upon a status level - possibly, in a stock list, how close an item is to being out of stock.

If you do this, do you do it manually?


Many users know about Conditional Formatting, but do not know how to format whole rows in this way. I, for one, used it for years without knowing how to do this - but it's really simple when you know how.


It involves using Conditional Formatting's formula feature with Excel's ability to fix references using the dollar sign.


Let us take a very simple example of stock, where we wish to show lines with less than five items as red, less than ten items as amber and ten items or over as green.


Assume we have a heading row so the stock data starts at row 2, and the stock level is the last column of the data and is held in column H.


Highlight cells B2 to H1000, or down to whatever row more than covers the number of stock items you might have. Select Conditional Formatting (from the Home ribbon on Excel 2007/2010, or from the Fomat menu on Excel 2003) and select "Use a formula to determine which cells to format" (in 2007/2010) or "Formula is" (in 2003).


2003 and 2007/10 work slightly different in this respect, as 2003 allows you to add up to 3 conditions using the Add button and 2007/10 allows many rules to be added and managed.

The following formulae should be entered as the three conditions in Excel 2003, or as 3 separate rules in 2007/2010. In each case you will determine the format to be used if this condition is true using the Format button next to where you enter the formula. This works very similar to the normal dialogue box you get when formatting cells.

For the red:

Formula         =$H2<5
Format          Fill Red

For the green:


Formula         =$H2>=10
Format          Fill Green


For the amber:


Formula         =AND($H2>=5,$H2<10)
Format          Fill Orange

(read more on the AND function here, under combining conditions)

The most important thing to note here is the use of the dollar sign. What we are doing here is fixing the column (H), but leaving the row flexible, so that all cells in the highlighted range, look along their own row to column H to apply the criteria. Also note that you should enter the formula as if you were entering it for the first row of the range - this is why we have entered H2 as row 2 is the top row that we have highlighted.

One other thing to note is that the formula is always preceded by an equals sign, even if it has an equals sign in the criteria. So, if we had wanted to format red when H2 was equal to 5, we would have, rather oddly, entered =$H2=5.

This technique has many applications, and is really simple when you get the hang of it.

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

Time saved using spreadsheets always beats time saved writing them!

I see many time-saving tips for Excel users that speed up the time it takes to prepare a spreadsheet, such as keyboard shortcuts, text-to-columns, etc.

Where these are all very useful, I sometimes feel that an important truth is often overlooked.

A spreadsheet gets built once, but is used hundreds of times!

It is always worth having this at the back of your mind (or stuck on the wall above your desk!) when you are building a spreadsheet.

Anything that can be done when setting up the spreadsheet to speed up the experience for the user (even if that's you) will pay for itself many times over.

This understanding can often be the difference between an amateur spreadsheet and a professionally produced one. They both do the job, but the good spreadsheet will let you do it in half the time, saving far more than the cost of doing it properly in the first place.

There are many ways in which a good spreadsheet does save the user time, such as drop-down lists, logical data layout and conditional formatting, but the key is to know it's important. If the user is having to copy and paste, change formatting or type something more than once, these are good indicators that the spreadsheet could have been better designed.

If this is the case, it is costing you every month, week or day (depending how often the spreadsheet is used) and it needn't be the case. Take another look at it and see where it can be improved - or get it rewritten by a professional. Either way, you will be amazed at the time savings that are possible.

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

Do you deny how much your team uses Excel?

"A prudent question is one-half of wisdom."

Francis Bacon

In too many years of experience helping organisations to make better use of Excel, one truth has become evident:

The higher you move up an organisation, the less they think the organisation uses Excel.


I am currently talking to many accountancy practices and this seems even more true in that industry.


Causes of Excel Blindness
There are a number of reasons why this should be true but it can be incredibly damaging for reasons I will go into later in this post.


I believe that the main causes are as follows:

1. The day-to-day experience of those involved - The higher you go in any organisation, the more your role involves relationships and meetings, rather than hands on number-crunching. Naturally those producing the numbers and analysing them use Excel far more than those discussing them;
2. Belief in computer systems - Having invested in accounting and/or ERP packages (or in the case of accountancy practices, accounts preparation software), and been sold the omnipotent nature of these packages by the software companies, it can be difficult to believe (or face) that there are still large amounts of work done on spreadsheets.
3. Over-simplification of what processes involve - A manager, for example, may understand that a member of the team is emailed a particular piece of information from a customer,a supplier, another branch or another department, and that they enter this into the system. The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system. All of this work is done in Excel - and the manager is completely unaware.

Why does it matter?

It matters because it can be incredibly damaging to an organisation in terms of both financial risk and inefficiency

Financial Risk

Not understanding the systems that your business relies upon can lead to errors in systems management don't even realise are being used. The controls in the ERP system or accounting software may be perfectly adequate, but if the information entered is coming from a spreadsheet with an inbuilt error, they won't be of much use.

Inefficiency

In most organisations, this is by far the greater cost. Many staff are using Excel for a great deal of their time and have very little training or understanding of how best to use it. Many hours can be cut from most employees' working weeks, with a little bit of focus on this area.

Let is use the situation described earlier to demonstrate both the efficiency and financial risk elements:

"The member of the team involved will know that they receive this information as an Excel file and they apply numerous sorts and filters, delete columns and rows, calculate totals, etc. before they enter it (or even import it) into the system."

This whole process could be automated in Excel so that a couple of hours of messing about, could be reduced to a couple of clicks. This not only saves those couple of hours every month, week, or even day, but ensures that the same conversion is applied to this spreadsheet each time, significantly reducing the risk of error.

What can be done?

The first step is to acknowledge how much your organisation uses Excel. No matter how sophisticated your systems are, there are many jobs around the edges of the system for which Excel is not only the best tool, it is often the only tool capable of the flexibility required. Acknowledging this can lead to some very quick improvements to efficiency.

Ensure that you have access to someone (either inside the organisation or from outside) who can look at this Excel use and suggest and/or implement improvements and train staff. I offer this type of service to UK accountancy practices via my Excellent Accountancy business, but please feel free to email me if you wish to discuss how to go about it for any other type of organisation.

Excel is not just Word for numbers, it does form parts of your key processes - whether you like it or not.

Imagine if you took the same lax approach to any other key processes in your business.

A little time and/or money invested in improving how you use Excel will go a long way, as it is such an untapped area in most businesses.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Use Excel to generate invoices and report on sales

I come across many small businesses who use Excel (or Word) to produce an invoice template that they can email or print and post to their customers.

Usually the sale detail is entered on the face of the template and this is saved as a separate file for each invoice. The problem here is that to be able to report on this information for accounting purposes, or sales analysis, the information needs to be recorded somewhere else in one file (either another spreadsheet, or an accounting software package).

What is really needed is to be able to enter the sale details once and for this to be used to populate both the invoice template and the sales reports.

If an accounting package is being used, the best answer would usually be to use an invoicing module in the accounting software and ditch the external template, however if this is not an option, or an accounting system isn't used, then we need the invoice template and the sales history to be linked.

Here is my approach to this problem, in the simple scenario where each invoice has one line of detail. If more lines are required a variation of this approach can be used, but this is a little too complicated to go into in this blog post.

General Approach
This approach involves three main worksheets within the one spreadsheet, Customer Data, Sales Data (what accountants often call a Sales Day Book) and the Invoice Template.

Customer Data
This worksheet, as the name suggests, is where all of the customer data is held. The sheet should follow the rules for laying out data that I outlined in an earlier post (How to lay out data in Excel). It should have column headings for name, address 1, address 2, etc. and one row for each customer, new customers are just added to the bottom of the list as needed.

The first column should be a unique identifier for that customer, it may be possible to use the name for this but you would need to add a new customer on a change of name rather than editing the existing one. Often better is to use the approach used by most accounting systems and allocate each customer a unique Account Code. I often use Conditional Formatting on this column to identify any duplicates.

The spreadsheet will use this sheet to provide the customer address details for the invoice.

Sales Data (or Sales Day Book)
This sheet should again follow the rules mentioned above. Here the business will record all sales, one line per sale. Headings should include (at least) Invoice No (in the first column), Date, Amount, Customer Account Code (the unique identifier mentioned in Customer Data above) and Description. Depending on needs the amount may be a calculated column, multiplying entries in columns for price and quantity. The business may also need a column for VAT or any other sales tax. The Customer Account Code column should use Data Validation to restrict entries to those codes in the first column of the Customer Data sheet.

The VLOOKUP function can then be used to pull in the customer name and address details in columns to the right of the entered data, using the customer account code to look that information up from the Customer sheet.


Invoice Template
If the business is already using an Excel template then this can be used as the basis of this sheet, otherwise some work will need to be done to produce a satisfactory layout for the invoice.

Ultimately there should only be one editable cell on these sheet - the Invoice Number which should be a dropdown based on the Invoice Number column of the Sales Data sheet.

VLOOKUP is then used on the rest of the invoice to pull the Customer Name and Address, Date, Description, Amount, etc. into the appropriate cells from the Sales Data sheet, based upon the Invoice Number selected.

I would recommend that to email the invoice to a customer, that it is printed to pdf first. This not only gets round the problem that all of your other sales data is in the same spreadsheet, but looks far more professional than sending a spreadsheet to the customer.

Reporting
As the sales data is all correctly laid out and, depending on the size of the business, at least a year's worth of sales can be stored in the one spreadsheet (usually many more) then Pivot Tables can be used to report on these sales in any way that is required.

Just don't forget to make sure you take regular backups and there is no need to record your sales anywhere else.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Adding categories to data in Excel - how to save you time later

In my previous post, I stated that one of the major problems with how most Excel users lay out their data, is using a column for each category.

In the feedback I have had from that post, it was felt that this point needed further explanation and/or an example, so I thought I would provide both here.

First of all, here is the point as it appeared in the original post (it was point number 3):

Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)

• Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
• Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
• You can use data validation drop-down lists to select the appropriate category for each row;
• This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

Let's look at some sales data laid out the wrong way:

I have left out any extra data (other than the date) to keep it simple. With the data laid out like this, you could use the SUM function to calculate monthly totals, but you can't do a lot more than that. If you were to use the data in a pivot table, you would have to add the data as 12 data fields, making it very cumbersome and inflexible.

Also, if you wanted to do any calculations on this data, such as calculating VAT, or any other Sales Tax for that matter, you would need another 12 calculated columns!

You then get into further problems if you want to analyse the data from another perspective - by salesperson for example.

A better approach 

Now let's see a better way to lay this data out:

You can also automate the month column using the following formula in cell C2:

=CHOOSE(MONTH($A2),"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec")

Laid out like this you can use the month as a way of analysing the amount in a pivot table for example:

Its real power however comes when you wish to add additional analysis and calculations, so for example you could add additional analysis columns for Salesperson and Country, and a VAT column (being 20% of amount):

You can then produce all manner of pivot tables, here are just a few examples:

It would be just as easy to show the months as columns - the only reason I haven't is to make best use of the space.

And remember, you can at any stage easily add further analysis or calculation columns as your reporting needs change.

I hope this has explained this point in more detail and even more importantly, highlighted the value of getting it right!

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

99% of Excel users get this wrong - How do you lay out your data?

"Learn the fundamentals of the game and stick to them. Band-Aid remedies never last."

Jack Nicklaus (Champion US golfer) 

When someone comes to me with a problem in an existing spreadsheet, the problem is invariably in the layout of the data. The spreadsheet is built for one purpose and works OK for that until something slightly different is required and it proves almost impossible to get the report that's needed.

If a few simple rules are followed when laying out your data, then producing additional reports from that data, and using it for different purposes, becomes simple, instead of the nightmare it is for many users.

These rules apply to any lists of data, be it monthly financial information, transactional data (such as lists of sales, purchases, payments or receipts), customer or supplier lists. If you are going to store data in your spreadsheet to produce reports from, you need to follow these rules.

At the heart of these rules is the approach - you are not laying out your final report here, you are laying out the data in a format that can be reported from! These are two very different things (see my OAP approach to reporting in Excel).

The rules to follow:

1. Columns with headings and no gaps
• Every column should have its own UNIQUE heading, in the first row;
• There should be no empty columns;
• These columns represent the fields of a database, e.g. Customer Code, Customer Name, Telephone Number, Email Address, etc.
2. One row per record and no gaps
• Every record should have all of its data on one row. E.g. in the above example, one row per customer;
• There should be no empty rows;
3. Don't group data by putting it in different columns (THIS IS THE ONE THAT ALMOST EVERYONE GETS WRONG)
• Don't split out financial or numerical data into separate columns to categorise the data into months, expense categories, customers, agents, etc.
• Do have one column for the financial or numerical data and create a column for month, expense category, customer or agent, to categorise each row;
• You can use data validation drop-down lists to select the appropriate category for each row;
• This one is counter-intuitive because in any report, you will almost certainly will want a column (or row) for each of these categories - but if you do this in the data you will massively restrict what you can do with it.

The benefits:

• Data following the rules above is perfectly prepared to be analysed using countless tools within Excel, for example: pivot tables, autofilter, SUMIF, COUNTIF, etc.
• Most changes to the data don't require a change to the data layout. New categories, e.g. expense categories, customers, agents, etc. can just be added to the drop-down lists. Any new entries in these columns will be automatically picked up by pivot-tables, autofilter, etc. with no work involved.If you had to create a new column each time, you would also need to edit every report that used the data.
• You can choose to analyse the data by any category you want. It takes seconds to edit a pivot table that has a column for each month and change it to a column for each expense category. This is almost impossible if the data was laid out in those columns.
• You can add additional category columns to the data if needed and these can even be calculated from the data. You might, for example, introduce departments - simply add a department column to the raw data, and your pivot tables can analyse the data by this category as well, or instead of existing categories.

As you can see, if you lay out your data according to these rules, you can do pretty much anything you want with it. The spreadsheet can grow with your business, and with any additional reporting requirements you want to add.

It can take a little bit of time to get your head around point 3, but believe me, you'll be pleased you decided to be among the 1% that get this right.

If you'd prefer me to redesign your spreadsheet for you, just visit www.needaspreadsheet.com and let me know what you need and I will send you a fixed price quote.

If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.

Connecting with those we're trying to help

"The more you explain it, the more I don't understand it."

Mark Twain

Constantly, as accountants, we need to communicate important messages to non-accountants and, in doing so, we risk coming across as detached number-crunchers, resulting in our message not really connecting.

No matter what our technical ability, if we do not have this power to connect, we don't get the chance to add the value we know we can!

I experienced this last week at the breakfast networking group that I attend every Thursday morning.

I have been going for around 3 months, with a 60 second talk every week to explain what I do (develop spreadsheets to help businesses streamline their admin). I had so far had a few pieces of business come from it, but not a great deal.

This week, I had a 10-minute slot and chose, rather than wax lyrical about what I do, to demonstrate what I had done the previous week for one of the other members. This was a relatively simple spreadsheet to record customer and sales information, with various reports from the data.

I demonstrated how the business owner could now record each piece of information once and use that same information to (at the click of a mouse) produce his invoices and sales reports, and track the success of his marketing efforts, as well as manage his callbacks.

I could almost hear the pennies dropping around the room.

I picked up three new opportunities straight away, and pretty much everyone (including a visitor who had never been before) said how every business they know would benefit from what I had shown.

The lesson I took from this is that we have to put ourselves in the other person's shoes and (and I think this is the key) demonstrate what the result means to them. Without them seeing this, anything we say will fall on deaf ears.


If you enjoyed this post, go to the top left corner of the blog, where you can subscribe for regular updates and your free report. If you wish to help me to provide future posts like this, please consider donating using the button in the right hand column.