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.

https://preview.redd.it/74i03ye0aadd1.png?width=1398&format=png&auto=webp&s=21782b7f1405c741522f44430001a4e397c07b18

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

I work in a Big 4 in money management and I’m also programmer, Here’s Excel Best practices