Hello,
I work in a Big 4 in money management and accounting and I'm also programmer. This guide is originated from countless mistakes i've seen people make, from complete beginners and also from experienced people.
I've been using Excel, and also programming for 8 years in professional settings, so this should be relevant wether you're advanced or just a pure beginner. These advices will be guidances on good practices. This will help you have a good approach of Excel. It won't be about hyperspecifics things, formula, but more about how to have a steady, and clean understanding and approach of Excel.
This guide is relevant to you if you regardless of your level if you :
- Work a lot on Excel
- Collaborate, using Excel.
- Deliver Excel sheet to customers.
So without further do, let's get stared.
First of all, what do we do on Excel, and it can be summarized in the following stuff :
Input > Transformation > Output.
As input we have : Cells, Table, Files
As transformation we have : Code (Formulas, VBA) , Built-in tools (Pivot table, Charts, Delimiter, PowerQuery), External Tools
As output we have : The Spreadsheet itself, Data (Text, Number, Date) or Objects (Chart, PivotTable).
And we'll focus on in this guide on :
- How to apply transfomations in a clean way
- How to take Inputs in a maintenable way.
- How to display Output in a relevant way
Part 1 : How to apply transfomations in a clean way
When you want to apply transformations, you should always consider the following points :
- Is my transformation understandable
- Is my transformation maintanable
- Am I using the best tool to apply my transformation
How to make proper transformations :
Most people use these two tools to do their transformations
| Transformation | Use-Case | Mistake people make |
|---|---|---|
| Formulas | Transform data inside a spreadsheet | No formatting, too lenghty |
| VBA | Shorten complex formulas, Making a spreadsheet dynamic and interactable | Used in the wrong scenarios and while VBA is usefull for quick fixes, it's also a bad programming language |
Mistake people do : Formulas
We've all came accross very lenghty formula, which were a headache just to think of trying to understand like that one :
Bad practice =IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))
Here are some ways to improve your formula writing, make it more clear and readable :
1) Use Alt + Enter and Spaces to make your formula readable.
Turn this :
=IFERROR(MAX(CHOOSECOLS(FILTER(Ventes[#Tout];(Ventes[[#Tout];[Vendeur]]=Tableau4[Vendeur])*(Ventes[[#Tout];[Livreur]]=Tableau4[Livreur]));MATCH(Tableau3[Champ];Ventes[#En-têtes];0)));0)
Into this :
=IFERROR(
MAX(
CHOOSECOLS(
FILTER(Sales[#All];
(Sales[[#All];[Retailer]]=Criterias[Retailer]) *
(Sales[[#All];[Deliverer]]=Criterias[Deliverer])
);
MATCH(Parameters[SumField];Ventes[#Headers];0)
)
);
0)
Use Alt + Enter to return to the next line, and spaces to indent the formulas.
Sadly we can't use Tab into Excel formulas.
If you have to do it several time, consider using a Excel Formula formatter :
https://www.excelformulabeautifier.com/
2) Use named range and table objects
Let's take for instance this nicely formatted formula i've written,
=IFERROR(
MAX(
CHOOSECOLS(
FILTER(Sales[#All];
(Sales[[#All];[Retailer]]=Criterias[Retailer]) *
(Sales[[#All];[Deliverer]]=Criterias[Deliverer])
);
MATCH(Parameters[Field];Sales[#Headers];0)
)
);
0)
Explanation : It filters the Sales tables, with the Criterias values, and then retrieve the MAX value of the column Parameters[Field].
=IFERROR(
MAX(
CHOISIRCOLS(
FILTRE(Formulas!$H$1:$L$30;
(Formulas!$K$1:$K$30=Formulas!$E$8) *
(Formulas!$J$1:$J$30=Formulas!$F$8)
);
EQUIV(Formulas!$C$8;Formulas!$H$1:$L$1;0)
)
);
0)
Explanation : It filters some stuff with some other stuff within the sheet 'Formulas', and get the max value of that thing*.*
As a rule of thumb, you should be able to understand your formulas, without ever looking at the Excel sheet. /!\ If you need the Excel sheet to understand the formula, then it's a badly written formula /!\ .
3) When Formula gets too complex, create custom function in Vba or use Lambda functions.
When you want to use complex formulas with a lot of parameters, for instance if you want to do complicated maths for money management, physics on Excel, consider using VBA as a way to make it more. Based on the function in example, we could implement in VBA a function that takes in the following argument :
=CriteriaSum(Data, Value, CriteriaRange, GetMethod)
=CriteriaSum(Ventes[#Tout], MATCH(Tableau3[Champ];Ventes[#En-têtes];0), Tableau6[#Tout], "Max")
You can also use lambda functions in order to name your function into something understandable
=RotateVectorAlongNormal(Rotator, Normal)
We can understand what this function does just from its name and you don't have to spend 15 minute reading :
=IF(IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)=5;INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;EQUIV("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6));-INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;IF(INDEX(temp.xls!A:F;SUM(MATCH("EENU";temp.xls!A:A;0);MATCH("BF304";OFFSET(temp.xls!A1;MATCH("EENU";temp.xls!A:A;0)-1;0;MATCH("FCLI";temp.xls!A:A;0)-MATCH("EENU";temp.xls!A:A;0)+1;1);0))-1;5)<>0;5;6)))
To figure out what result you're supposed to have.
4) Your formula probably already exists.
That's probably what you've been thinking if you know about the DMAX formula. But it was on purpose to bring this point to your knowledge.
=BDMAX(Vente[#Tout];Champs[@Champ];Criteres[#Tout])
This does the job, and it's applicable to many cases. in 90% cases, there's inside Excel a function that will do exactly what you're looking for in a clear and concize manner. So everytime you encounter a hurdle, always take the time to look for it on internet, or ask directly ChatGPT, and he'll give you an optimal solution.
5) ALWAYS variabilize your parmaters and showcase them on the Same Sheet.
Both for maintenance and readability, ALWAYS showcase your parameters inside your sheet, that way the user understand what's being calculated just from a glance.
If you follow all these advices, you should be able to clear, understable and maintenable formulas. Usually behind formulas, we want to take some input, apply some transformation and provide some output. With this first
Mistake people do : VBA
The most common mistake people do when using VBA, is using it in wrong scenarios.
Here's a table of when and when not to use VBA :
| Scenario | Why it's bad | Suggestion |
|---|---|---|
| Preparing data | It's bad because PowerQuery exists and is designed precisely for the taks. But also because VBA is extermely bad at said task. | Use PowerQuery. |
| I want to draw a map, or something complex that isn't inside the Chart menu | It's a TERRIBLE idea because your code will be extremely lenghty, long to run, and Horrible to maintain even if you have good practices while using other tools will be so much easier for everyone, you included. You might have some tools restriction, or your firm might not have access to visualizing tool because data might be sensitive, but if that's the case, don't use VBA, switch to a True programming language, like Python. | Use PowerBI, and if you can't because of firm software restriction, use Python, or any other popular and recent programming language. |
| I want to make game because i'm bored in class on school computer | Now you have a class to catch up, you dummy | Follow class |
And here's a table of when to use VBA :
| Scenario | Why it's good |
|---|---|
| I want to make a complex mathematical function that doesn't exist inside excel while keeping it concise and easy to read | It's the most optimal way of using VBA, creating custom functions enable you to make your spreadsheet much more easier to understand, and virtually transform a maintenance hell into a quiet heaven. |
| I want to use VBA to retrieve environment and other form of data about the PC, The file I'm in | VBA can be usefull if you want to set some filepath that should be used by other tools, for instance PowerQuery |
| I want to use VBA to do some Regex | One Usecase would be the Regexes, Regexes are very powerfull tools and are supported in VBA and thus used as a custom function inside your project. |
| I want to ask my spreadsheet user for a short amount of inputs interactively | While spreadsheet can be used to fill a "Settings" or "Parameters" fields, sometime user can forget to update them, however with VBA we can forcefully query the user to input it with a MsgBox |
| I want to draw a very simplistic stuff to impress the client who's not very tech savy | As said earlier, VBA is the equivalent of the Javascript of a webpage, it can and should be used to make your spreadsheet dynamic. |
| I want to impress a client | Since trading used to be done in VBA, people tend to worship VBA, so using VBA can be usefull to impress a client. Now it's done in Python/C++, but people in the field are not aware yet, so you can still wow them. |
| I want to make game because i'm bored in class on school computer | Gets rid of boredom |
If you write VBA code, you should rely on the same rules as formulas for formatting given that you can be cleaner on VBA.
Part 2 : How to reference input.
When you reference input, you should always consider the following points :
- Is my reference durable
- Is my reference understandable
- Am I using the best tool to reference my input ?
Here the rule are simple :
How to properly reference your input :
| Use-Case | Good practice | Mistake people make |
|---|---|---|
| Inside a spreadsheet | Use table objects instead of ranges of the A1 Reference Style. If you reference a "constant" (Like speed of light, or debt charge rate, or some other global parameter) several times, use a named range | They don't use enough named range and table object and end up with "$S:$139598" named fields. |
| Outside of a spreadsheet | Use PowerQuery | They reference it directly in Excel or require the user to Do it manually by copying and pasting the Data in a "Data" Sheet. |
Outside of a spreadsheet
Always use PowerQuery. When using PowerQuery, you'll be able to reference Data from other file which will then be preprocessed using the transformation step you've set up.
Using PowerQuery is better because :
- PowerQuery is safer and faster than manually copy pasting
- PowerQuery automates entirely all the prepocessing
- PowerQuery tremendously faster than Excel for all its task
- PowerQuery is easier to maintain and understand even from someone who never used it
- PowerQuery is Built-in in Excel
Outside of a spreadsheet input referencing use cases
| Use-Case | PowerQuery | How people do it |
|---|---|---|
| You're a clinical researcher, every day you recieve Data about your patient which you need to import inside your spreadsheet that does your examination for you. You recieve 40 files, one for each patient, which you then need to combine inside your folder | Request your folder, and use the Append function upon setup. All the following times, just press Refresh ALL | Manual copy pasting every day. |
| You're working in a Sharepoint with Financial Data and happen to be available only when another colleague need to work on the same file on the same spreadsheet than you do | Use PowerQuery to import the Data, it'll be real time. | Wait for one person to be done, then start working. |
Part 3 : How to display output in a relevant and safe way :
As an output
When you display an output, you should always consider the following points :
- Is my output necessary to be displayed ?
- Is it displayed in a understable way ?
| Mistake people make | Good practice |
|---|---|
| Not using PowerQuery and having too many spreadsheet as a Result | Prepocess entirely in PowerQuery, and display only the final result. Your Excel file should hold in 5 sheets in most cases |
Then about how to communicate, and display it will depend on the target. However less is more, and most of the time, your spreadsheet can do the job only using 5 Sheets in most cases.
TL;DR : To have clean Excel Spreadsheets :
- Use PowerQuery for Large Data input and preprocessing
- Format your formulas, and use named range
- Use VBA to write custom functions when Formulas are getting too lenghty
- Keep your Sheet count to a minimum