05
June
2016

The most valuable Excel advice

Recently I was lucky enough to compete in Microsoft's Excel Champs of South Africa. Throughout my progress through the competition I was asked on numerous occasions "What advice would you give to new Excel users?". Ultimately I won the competition and I am yet to see my answer to that question published in any article that has been written subsequently about the event. So I decided to write this article detailing my, not very newsworthy, yet in my opinion very valuable advice to Excel users at all levels.

My advice to all Excel users is as follows: Keep it simple, and don't split your data. Having trained literally thousands of Excel users in my life, I have noticed a very common trend amongst the majority of Excel users; they attempt to "organise" their data into a number of separate sheets.

For example, having a sheet for data collected during January, February, March and so on, or alternatively having a sheet for each client. Over the years I have heard a lot of people telling me that this is the way they "have to" do it because of a range of equally, in my opinion, unconvincing reasons. The disadvantages of organising your data in this way far outweighs any potential benefit of keeping your data like this.

So how should data be stored? As one long continuous list of information with no skipped rows or skipped columns, with headings at the top of EVERY column in the first row of the database and NO MERGED CELLS. If you need to organise data into months, then add an extra column called Month and list January next to all January records and February next to all February records etc. etc. If it needs to be organised by clients, then add a client column and so on and so forth. This is how Excel was designed to handle data, if you break this integrity, you are creating hours of unnecessary work for yourself in the future.

This might seem obvious but note the following examples of databases where integrity has been compromised:

Problem: Headers not in first row of database, sorting filtering and a lot of other functionality looks for information in the first row. (Incorrect)

 Problem: Headers not in first row of database, sorting filtering and a lot of other functionality looks for information in the first row. (Incorrect)

Corne2

Database title connected to database.

Problem: Headers not in first row of database, sorting filtering and a lot of other functionality looks for information in the first row. (Incorrect)

 

 If a total row is connected to the database, it becomes a record in the database.
Problem: If data is consolidating the total will be included in the result and will double everything. (Incorrect)

 corne3

 Title and Total row removed from database. Databases integrity stays intact. (Correct)

final

 When data is together, it is easy to create a consolidated view using formulas or Pivot Tables. What's more, it is easy to separate data automatically using a variety of methods. However, if it separated you would need to create 12 different formulas or Pivot Tables and then combine them in order to get a consolidated view of the data. Or alternatively you will need to recombine all the information manually. Even worse than this, sometimes people create an entire spreadsheet for what could have been summed up in a single record. For example, creating a "financial model", which uses multiple sheets input to determine an eventual cost of a service! Now we will need to consolidate potentially hundreds of workbooks in order to see an overview of your data!

I am not suggesting that there are never any good reasons to deviate from sound database formatting, merely that in 80% of scenarios, data is best left in a single database. Different sheets are intended for different databases, not little parts of the same database… The rule is, if the database has the same column headings, it's the same database.

If you have gotten to this point in the article, I truly commend your tenacity. The subject matter is understandably very dry, but these fundamentals are the building blocks of Excel and when data is good, Excel is loads of fun. Enjoy your spread sheeting, and change your thinking.

Leave a comment

You are commenting as guest.

Copyright ¬© 2018 Beth Horner & Associates | Computer Skills and Microsoft Office Training in Johannesburg, Gauteng | Disclaimer