✨ Get inspired with the Stoic Value Notes: Your weekly guide to project & business management frameworks, Stoic wisdom and value investing insights. ✨
Key Takeaways
When keeping certain best practices in mind, working and collaborating with Excel will become a breeze.
Efficient use of MS Excel most often does not come from knowing all the formulas - but from structuring the file properly.
Answer the question at the end and get access to a free 10-days e-mail course about MS Excel.
Today, we will look at some best practices in Excel – that is, things you should ideally consider and what to avoid in order to work productively in Excel.
Those are mostly simple things, but from my personal experience, I can attest that they are often overlooked.
Let's go!
I / You shall only use correct and complete data.
"Elementary, Watson."
That's probably what Sherlock Holmes would say to this.
Obviously, it is much easier to work with cleaned data. Therefore, please ensure that the data you work with is complete and correct. If you receive data that doesn't meet these criteria, clean it before working with it.
II / You shall categorise, not colour.
Coloured rows, for example to indicate a status, are only somewhat helpful. You can't really filter effectively by colour.
Additionally, others who work with your file might not automatically understand what the colours mean (even if you include a legend).
It is better to add an extra column (e.g., "Status") and indicate the status of the data in each row there.
III / You shall, whenever possible, provide a key/ID.
To allow you and others to reuse the data in your worksheet in other contexts, e.g., to enrich each row with additional data, it is advisable to add an ID (identification) or key per row.
This also makes it easier to refer to a specific data row when collaborating with others.
IV / You shall (ideally) not merge cells.
If it’s not absolutely necessary, it’s advisable to avoid merging cells. This way, you won’t encounter problems later when you want to filter certain columns, as filters cannot be applied to merged columns.
V / You shall avoid “hiding” data - grouping is better.
Columns you don’t want to display can be hidden in two ways: "hide" or "group (hide)".
If you hide them, others unfamiliar with your file might not see them and therefore cannot understand the data.
If you look closely at the image below, you’ll see that column D "is missing". Instead, you’ll see two almost invisible dividers indicating the hidden column.
However, if you group columns, a button will always be displayed allowing you to show or hide the columns. This way, you and others can immediately see that there are more columns available.
VI / You shall use data validation and verification.
When multiple people work on the same file and input data into a list, it is advisable to use data validation to ensure that only valid values are entered into the cells. This ensures data quality and avoids errors during entry.
VII / You shall format only at the end.
While editing a worksheet, you will inevitably change the structure of your list or file repeatedly. That’s perfectly fine. However, if you always format your data to make it "look nice", you might end up repeating this step several times. Therefore, try to wait with the "cosmetics" until the end. This will save you time and prevent duplicate work.
VIII / You shall name the columns.
Ensure that all your columns have a meaningful header. This will make it easier for you and others to understand and work with the file.
IX / You shall specify units and number formats.
This point ties into point 8. To make it immediately clear in which currency, quantity, or time unit the data is provided, include this in the column header.
Alternatively, you can note this in a separate row below the column header to keep your column headers clear & clean.
The main thing is to note it somewhere.
X / You shall document your assumptions.
When working with lists and tables that are constantly changing, especially when it comes to planning or budgeting data, it is advisable to document your assumptions well.
This doesn't necessarily have to be in Excel itself. However, it often happens that we don't have all the information when planning and have to make assumptions.
To avoid questions later on about how we arrived at the numbers and relationships, it is better to document the assumptions immediately.
These best practices are recommendations. There may be justified cases where it makes sense to deviate from them.
Moreover, many of the points mentioned above not only help you but also in collaborating with others. So, also try to put yourself in the shoes of the recipients of the file. This helps to tailor your work to the intended audience.
If you have any questions or uncertainties, you can simply reply to this email. I also welcome your feedback.
I am thinking about launching an e-mail course about Excel (in English) with the goal to show you what Excel can do. Also, to make your work with Excel more efficient as well as effective.
And with that, enjoy your weekend.
Happy excelling.
PS:
What is one best practice you adhere to when working with Excel? Let me know in the comments.
If you’ve enjoyed this issue, don’t forget to subscribe here for weekly actionable project management insights, as well as tools and productivity tips delivered straight to your inbox.
If you think this would be helpful to anyone you know, please share this newsletter and connect with me on X and LinkedIn for daily project management inspiration.