From the Not Just Numbers blog:

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.

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.