D.A.R.E. to be different

A common misconception when it comes to learning about Excel and how to use it is that it is all about how do I use and implement all these functions and their capabilities. This misconception is further compounded by the fact that most Excel training just simply teaches you new functions and capabilities and does not dive into the VERY important stages that you need to think about way before your fingers hit the keyboard and enter data into a spreadsheet.

We are not saying that you should not learn new functions and capabilities in Excel. A quick Google search (or search ExcelSuperSite of course!!) and you can typically learn all you need to know about new functions. This is certainly important and helpful, but it is rarely what holds people back from creating and developing useful spreadsheets.

What really transforms what users can achieve using Excel is the methodology/approach they use to create and design their spreadsheets right from the very start. If you get the thinking and design right, right at the very beginning it will make your spreadsheet that much easier to use and WILL save you a lot of time and effort further on down the track. You can always search and learn the functions when you need to, to achieve your desired outcome.

Our D.A.R.E. Methodology to Creating and Developing Spreadsheets

The methodology we use, and advise our clients to use to change their approach to spreadsheet design is our D.A.R.E. Methodology.

D.A.R.E. is an acronym for the 4 separate phases/areas we teach and use when creating or developing spreadsheets.

D.A.R.E. stands for:

Using a methodology like D.A.R.E. helps to separate the different areas/functions of a spreadsheet so they are undertaken in their own areas (may or may not be on separate worksheets) and do not cause unnecessary complications. Trying to address the Data, Analysis, and Reporting functions all combined in 1 area or on the same worksheet is where a lot of people go astray.

D is for Data

The D in our D.A.R.E. Methodology is for Data. Get this component right and you’ll take a giant leap in front of most other spreadsheet designers.

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

• Does your Data Area of your spreadsheet make it easy to enter the information? and
• Does the layout of your data area facilitate the Analysis of the information?

A factor that should be completely ignored at this stage is the layout of your presentation/reporting of your results. Don’t get me wrong, this is important, just don’t get bogged down into what your results should look like till you have thought about the Data and Analysis areas of your spreadsheet.

Formatting should play second fiddle in any Data areas in your spreadsheets. The concept behind these areas is that they are information dumps of your data which will then be used in the Analysis areas. These areas are purely “working” areas of your spreadsheets and will never be used to display final results or added to reports etc.

Data from multiple sources should ideally be broken out into their own sheets, with no other information on them. This makes them easier to update and helps eliminate the possibility of human errors creeping into the data.

A is for Analysis

The A in our D.A.R.E. Methodology is for Analysis. This is where the calculations are done in our spreadsheet.

If you have correctly set up the Data areas within your spreadsheet (see D for Data above), we can simply add any calculations alongside (or on another sheet) our information and not have to spend large amounts of time re-working/re-formatting our initial datasets to undertake our analysis.

The primary objective of our Analysis area is to analyse, re-arrange, and perform calculations, etc so that we end up with areas in our spreadsheets that have all the final information we need to be displayed in the final output. These areas will either have been populated via manual data entry (or import from other systems) or have been calculated or looked up from other areas within our spreadsheet.

R is for Report

The R in our D.A.R.E. Methodology is for Report. This is the stage where we address the output of the final presentation.

If you have followed the above 2 steps, the Reporting/presentation stage should now be a lot easier as all of the information we need to present has been calculated and analysed in the Data and Analysis areas of our spreadsheet.

These are the areas of your spreadsheets that you “pretty up”, add colours and borders, add graphs and dashboards, change fonts and sizes etc let your “creative side” take over to develop presentations worthy to be included in final reports.

E is for Evaluate

The E in our D.A.R.E. Methodology is for Evaluate. This is the stage where we look back over everything we have done in our spreadsheet and asks ourselves – does it actually make sense.

In reality, this part of the methodology should be undertaken all throughout the other 3 phases and not simply left till the end.

The reason we undertake this step is so that we apply a common sense factor to our spreadsheets. We may have developed an easy to use spreadsheet where data is updated with little trouble, analysis is completed by pressing a few buttons or running a few calculations, and have beautifully presented tables and graphs to go into final reports, BUT if looking at the numbers or graphics, etc and the figures just don’t make sense, we may as well ditch the whole lot as we won’t be able to use it.

Performing this phase can sometimes be difficult for the person who has put all the effort into creating and developing the spreadsheet in the first place. They can be “too close” to the information to see something that may be glaringly obvious for another. Don’t be afraid to look for help during this phase and seek out feedback to ensure all your hard work isn’t in vain.

There you have it, our D.A.R.E Methodology. The next time you have to create or enhance a spreadsheet put this methodology into practice and you will find your spreadsheets that much easier to use with the added benefits of saving you time and effort in the future.

Continue the Discussion

So do you use a methodology when you create your spreadsheets or do you just jump in and start entering data? Do you use a similar methodology? If not, how to you approach creating and developing your spreadsheets? Continue the discussion and add your thoughts in the comments section at the bottom of this article.

Please Share

If you liked this article or know someone who could benefit from this information, please feel free to share it with your friends and colleagues and spread the word on Facebook, Twitter and/or Linkedin.