The Most Valuable Microsoft Excel Advice
I was fortunate enough to compete in Microsoft’s ‘Excel Champs of South Africa’. Throughout the competition, judges repeatedly asked me, “What advice would you give to users learning Excel?” My answer for beginners through to advanced users was simple: Keep it simple and don’t split your data unnecessarily.
As an Excel specialist, I have trained thousands of users, and over the years I’ve noticed a common trend: many people attempt to organise their data across multiple sheets. Often, they create separate sheets for January, February, March, or for individual clients. They claim this is the only way to manage their information, yet the reasons they give rarely justify the additional work. In reality, the disadvantages of splitting data far outweigh any perceived benefits.
So how should data be stored? Always keep your data in one continuous list, with no skipped rows or columns, headings in the first row of each column, and no merged cells. Then, if you want to organise by months, add a “Month” column and label each record. To organise by clients, add a “Client” column, and so on. This approach aligns perfectly with how Microsoft Excel is designed. Breaking this structure may save time initially, but it leads to hours of unnecessary work when filtering, analysing, or summarising your information. Properly structured data allows Excel to work for you, not the other way around.
Even though it might seem obvious, problems still occur when database integrity is compromised. For example:
Problem: Headers not in the first row of a database. Many Excel functions, including sorting and filtering, rely on headers being in the first row. When headers are misplaced, these functions break, creating errors and frustration.
Problem: Headers not in first row of database, sorting filtering and a lot of other functionality looks for information in the first row. (Incorrect)
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)
Title and Total row removed from database. Database integrity stays intact. (Correct)
When data is stored together, creating a consolidated view with formulas or PivotTables becomes simple. Separating data can even be automated. However, when data is split across multiple sheets, you often need to create multiple formulas or PivotTables and then combine them manually. In some cases, entire spreadsheets are built for what could be a single record, such as a “financial model” that pulls from multiple sheets to calculate a service cost. This can lead to consolidating hundreds of workbooks just to get a clear overview.
Keeping data structured and centralized saves time, reduces errors, and makes analysis much easier. With the right approach, you can generate insights quickly without the frustration of piecing together scattered information.
I’m not suggesting that you should never deviate from proper database formatting. However, in roughly 80% of scenarios, data works best in a single database. Use different sheets only for completely separate databases. The rule is simple: if the column headings match, it’s the same database.
If you’ve read this far, I commend your focus. The topic may seem dry, but these fundamentals form the backbone of Microsoft Excel. When your data is clean, Excel becomes intuitive, powerful, and even fun. Enjoy your spreadsheets — and change the way you think about data.
