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.

Is fiddling with Excel a good or a bad use of time?

I'd really like to know what everyone thinks about this question - because I am not sure myself.

I have always fiddled with Excel until I got it to do what I wanted. For me, personally, it has worked out very well as I turned the skills I developed as a result into a successful business! But was it good for my employers at the time?

Granted, they got some good spreadsheet solutions in the end, but would it have been more effective to bring someone in who already knew how to do it, rather than use my time to get there by trial and error!

I'm sure I'm not alone as someone who likes to make sure they find a way, but it can be all too easy to spend far more time than could be justified in financial terms. Once the problem has been solved, the skills are there for next time, but is it the most efficient approach?

There are a few alternatives to fiddling with it until you get there using Google and Excel's help facility, they all have a financial cost but can considerably reduce the time spent:

1. Excel training - this can obviously be useful but is often too generic to then apply to your real problems when you get back to the office. I have found a one-to-one approach is often more effective, working with the client's own spreadsheets and problems. Another approach is to have training tailored to your business or industry (the service I offer to Accountants in Practice at Excellent Accountancy works along these lines)
2. Subscribe to a service where you have someone to ask - my Excel Advice by Email subscribers get this kind of service by email for just £75 per year
3. Get someone else to do it - I have my own service for this at needaspreadsheet.com

My suspicion is that any one of these could be right, depending on the relative value of your time vs your business cash, and whether you ultimately want the skills in-house.

If you have plenty of time and no cash (especially if you want to develop the skills yourself), then keeping fiddling is probably the best route (it worked for me!). At the other end of the scale, your time is usually more valuable than the cost of getting the job done outside, and this for many is a no-brainer if the primary purpose is not to build your own Excel skillset.

I'd love to hear what you do now, and what you think is best as they may not be the same!

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.

It ain't what you do, it's the way that you do it!

One of the most common misconceptions I come across when helping others to get the most out of Excel is the belief that it is all about learning new functions and capabilities. This misconception is compounded as most Excel training will teach you new functions and capabilities! My Pivot Table Training videos are no exception to this!

Where learning new functions is most certainly useful, it is rarely what holds people back. We can generally learn new functions from a quick Google search or just by using the fx button.

What really transforms what you can achieve using Excel is your approach. If you get the thinking right, you can always find the functions you need to achieve what you want.

My OAP Approach to Excel
One useful method I adopt to help clients change their approach is to teach my 'OAP method'.

This helps to separate the different tasks a spreadsheet needs to do, so that it does them all well. Trying to address all of these steps together is where most people come a cropper!

O is for Obtain
The O in my OAP approach is for Obtaining Data. This is key to getting your spreadsheet right and will make everything else easier.

Whether the data to be used is to be entered directly into the spreadsheet, or imported from another database or system, there are two factors to take into account:

• Does the layout make it easy to input the information?
• Is it laid out in a way that makes the other steps easier?

What should be completely ignored at this stage, is the layout of the final output! This is important, and is the most common reason people get bogged down with cumbersome and inflexible spreadsheets.

Any data to be entered should be in one place, using tools such as drop-down lists to make input as easy as possible.

Where there are multiple transactions or records, these should be held in a list with one row per transaction or record, with column headings and no blank column headings. Formatting is very much secondary here.

For example, a list of invoices should have columns for Date, Invoice Number, Amount, etc.

Where multiple lists of transactions or records are to be used, these should ideally have their own sheets, with no other information on them.

A is for Analyse

For my readers abroad, that is how we spell it in the UK!

This is where the calculations are done.

If the data has been collected in the right format (see O for Obtain above), we can add any calculations to the lists by adding extra columns alongside. Because the format is right, these calculations can just be copied down so that they are applied to every row.

This is also true for looking up additional data from the other lists in the spreadsheet. For example, we can use the VLOOKUP function to add address columns to an invoice list, by pulling the information from a customer list held on a separate sheet.

The objective in this step is to ensure that on one sheet we have columns for all of the items we will need in the final output. These will either have been populated via data entry (or import), or have been calculated or looked up.

P is for Present

Finally we start to address the final presentation, but this is now a lot easier as the all of the data we need is now accessible in a format that makes it easy to report on and use.

We can now use Pivot Tables to present the information in many different ways or functions such as  VLOOKUP  and SUMIF or COUNTIF to pull the data into specific cells if a pivot table does not do the job.

We can also use a combination of pivot tables and the GETPIVOTDATA function to give us the most flexibility.

If you work in a UK accountancy practice, I offer a service specifically for you that will really help you change how you use Excel at Excellent Accountancy.

For everyone else, please let me know if I can help with anything, or alternatively why not get me to do it for you at needaspreadsheet.com.

Click here for our our exclusive offer on Online Excel Training 

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