Hello,

I work in a Big 4 in Finance 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 clients.

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

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(Ventes[#Tout];
                                                 (Ventes[[#Tout];[Vendeur]]=Tableau4[Vendeur]) *
                                                 (Ventes[[#Tout];[Livreur]]=Tableau4[Livreur])
                                );
                                MATCH(Tableau3[Champ];Ventes[#En-têtes];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 :

2) Use named range and table objects

Let's take for instance this nicely formatted formula i've written,

=IFERROR(
          MAX(
               CHOOSECOLS(
                           FILTER(Ventes[#Tout];
                                                 (Ventes[[#Tout];[Vendeur]]=Criteres[Vendeur]) *
                                                 (Ventes[[#Tout];[Livreur]]=Criteres[Livreur])
                                );
                                MATCH(ChampCible[Champ];Ventes[#En-têtes];0)
                          )
              );
0)

Explanation : It filters the Ventes tables, with the Criteres values, and then retrieve the max value of the column ChampCible[Champ].

=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 finance, 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 :

And here's a table of when to use VBA :

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 :

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

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 ?

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

My US-Central app is down and can't even access the resource to open a ticket for it. Looks like it may be widespread:

Like most of you, when I have an issue I first google it and notice that are always at the top of the results. Then when I check the answers out, it's always variations of:

• try these 20 steps, if all fails, reinstall OS.

The answers on there never understand the actual problem, so they never get close to the solution.

The PSA is to always skip that site altogether, and check out more user-dedicated forums (even Reddit is decent for this).

Here for posterity is my example:

Now the first result will have you literally spending all day, several hours work, doing pointless troubleshooting. Because the guy - a self-described "installation specialist and 9 year Windows MVP" simply does not understand the problem, so will throw everything at it.

This is in a nutshell.

The second search result, is a more user-dedicated forum (which I haven't actually heard of before). Here, the click directs to the solution, which takes 10 seconds to apply and test. Don't even need to restart Explorer. Thankfully, I gave up on the first result without wasting any time.

Moral of the story is: don't trust long generic copy/paste lists of troubleshooting, look for answers where it seems like the responder understands your specific issue. If in doubt, make a thread here on this subreddit (or indeed, on tenforums).

Here are the links for anyone interested:

(this answer requires the user to also click the below link and do all those steps too):

Whereas here, the first comment has the specific solution:

Feel free to share your own examples :D

I have a pc and mac hooked to the main tv that I use for torrents, scrypted, and homebridge. The ability to stream torrents to all TVs and devices is extremely cool. Then I was told it can also serve as a remote server for my audiobooks (via Prologue) to all of our iOS devices. Set that up and omfg. Add to that a massive support community with lots of q&a fixes out there already for little problems and I am very happy.

The only issue remaining is that my server is on the mac and I have to toggle NordVPN on/off (bound to qBittorrent) because I haven’t figured out split tunneling for Plex so I can keep remote access on. Trying to keep the Plex server on the mac and keep the pc dedicated to homebridge/scrypted. Also don’t like to be solely a leech on torrents.

I switched to iPhone and iOS over 10 years ago for a more consistent software experience. Android, despite its flexibility, was often buggy with crashing apps. I was thrilled with iPhone's smoothness and reliability.

However, in recent years, I've noticed similar issues to my old Samsung: apps crashing or freezing, and inconsistencies across the Apple ecosystem. I believe Apple should prioritize polishing their OS over introducing too many new features, as stability was their main USP and differentiator imo.

Anyone else feel the same way? (Btw my keyboard froze as I was typing this)