Use Consistent Labels
Numbers without meaning are worthless. Excel is a program for numbers, but it is only useful if it is clear to the reader or user what these numbers mean. So, make sure that you provide textual information for all data and formulas, let’s call these pieces of text information labels.
Always Label Your Spreadsheet
Even if you are just making relatively simple calculations, you need to label your small spreadsheet. We cannot emphasize this enough, that the average spreadsheet will be used for a period 5 years and is used by an average of 12 different people. By the way, with providing labels, we are not referring to the use of Excel’s Named Ranges/Names. Those features have their own sets of pros and cons, we will discuss them in length in another article.
Choose Meaningful Labels
Do not just use random names, but think about a consistent system that fits the purpose of the spreadsheet. The shorter the names the better (certainly the column names) but make sure they stay meaningful. We have encountered spreadsheets where the column names “Rev08”, “Revenue 2008” and “2008 Revenue” were all used. This is a bad idea. Make a choice! A consistent way of labeling makes your spreadsheet easier to understand and it makes search functionality (CTRL-F) much more effective. When possible, we suggest starting with a letter and not a number in labels. That way Excel knows immediately that it is a label and it will not accidentally be used in calculations.
Use 1 Row and 1 Column for Naming
When you create a table, name each column and row. If possible, use only 1 row and 1 column for naming and not two because this can result in all kinds of disadvantages with subsequent editing. For example, there will be issue when you try to sort the table or try to create a pivot table. Only Excel’s chart function can handle double labels on the x-axis well, which means you are limiting the future users options when you use two rows or columns for labeling.
Corporate Naming Schemes
If your company uses spreadsheets widely, it is worthwhile to agree on consistent naming schemes. That makes the spreadsheets become more readable, and will probably result in massive amounts of time saved as people will be able to open a spreadsheet and understand it much quicker. This rule of thumb also counts for formatting and color schemes, keeping spreadsheets as consistent as possible company wide is always a good idea.
How PerfectXL Can Help You
Faithful readers of our guidelines will now wonder: Well how does PerfectXL help me to use consistent labels? Unfortunately, PerfectXL does not help! This is one thing that you’ll have to do yourself. We can suggest though that you check the risk overview after you add your documentation in case you accidentally created cell shifts, moved information, or inadvertently broke some calculation chain. PerfectXL Explore will find these issues and guide you through the process of fixing the issues quickly.
- It’s fast & easy to use
- Get detailed insights
- Save frustration and time
Read more about exploring Excel files
On this page you will find a step-by-step walkthrough of the PerfectXL Explore Tool. This page will give you information on how you can use explore to gain a better understanding of complex Excel files.
This is the technical page for PerfectXL Explore. Here you will find information like system requirements, release notes, and answers to most questions about our software. If you have more questions please use the form at the bottom of the page.
In this video we’ll show you how to run a quick basic check of the input sources of your Excel files and how to determine if there is any unknown input. We’ll also show you a powerful way to visualize your spreadsheet’s information flow to help determine the function and dependability of each worksheet.
When we read a book or a report or a website, we read from top to bottom and from left to right. Keep this in mind when you create an Excel file. Try your best to follow that structure both within each worksheet and between them. We advise you to distinguish separate input & output sheets as well as sheets for calculations, documentation and dashboards.
Treat transferring a spreadsheet like you would treat the process of renting your house via Airbnb. Most people realize it will stay neater if you clean it first. Mess stimulates mess, and neatness stimulates neatness. Remember that in an average spreadsheet there will be 12 different users that have to use it after you! What do you think happens when this chain starts with a messy file?
Garbage in, garbage out. So pay serious attention to cleaning your input data as it is the foundation of your spreadsheet and if possible, do it in such a way that it becomes an automated process for the next time you use the same spreadsheet.
Understanding the workings of a spreadsheet is key to correctly interpreting its results. Spreadsheet visualization is an essential feature of PerfectXL Explore. It allows you to create a graphical model of the general flow of information, which also shows of risks, input & output and data & formula flows.
Curious to see what your spreadsheet’s all about? PerfectXL Explore gives you a great starting point. An overview of worksheets, input sources and potential risks are all part of our general spreadsheet characteristics section. It also gives you an insight into the amount of formulas used in your spreadsheet.
Be honest with yourself, how much time does it take you to spit through an Excel file? The opportunities in Excel are endless but this comes with a downside. PerfectXL has the solution. Starting now you can gain a better understanding of your spreadsheets, to help you in improving, documenting, and cleaning up your files. Here are 9 tasks that PerfectXL Explore makes easy.