Bertrand Signoret

If you haven’t met Goal Seek yet, allow me to introduce you. It’s a simple yet remarkably powerful tool that’s definitely worth knowing.

Let’s dive into a practical example to see just how helpful it can be:

• Let’s say you want to compute the minimum number of customers you need to have to be profitable (net result greater than 0);
• the number of customers is an input among other inputs like the average unit price of your products, the average costs of goods sold, the variable costs and the fixed costs ;
• the net result is the revenue – costs of goods sold – variable costs – fixed costs – depreciation & amorization – financial result – taxes.

To use Goal Seek, and find the minimum number of customers:

• Navigate to the cell where you want the result to be zero.
• Click on Data > What-If Analysis > Goal Seek (or hit Alt > A > W > G).
• Set the “To value” as 0.
• Specify the cell containing the number of customers under “By changing cell,” then hit OK.

Voilà! You now have the magic number of customers required to reach profitability.

In general, if you have multiple inputs, complicated formulas that give an output and you want to know the value of an input to reach a given value for an output, Goal Seek is your way to go.

I’m sure you know VLookUp but have you met its more versatile cousin XLookUp ? If you try it, you’ll never use VLookUp again.

Why? Well, let me enlighten you:

• Left-Side Retrieval: You can retrieve a value which is on the left of the looked up value ;
• No Column Counting Needed: No need to count the number of columns ;
• Auto-Adjusting Formula: If you add or delete a column, XLookUp will gracefully adapt.

Here is how to use it :

=XLOOKUP(E3;B:B;C:C)

If you want the value to be 0 in case the looked-up value is not found, just add 0 at the end of the formula:

=XLOOKUP(E3;B:B;C:C;0)

But wait, there’s more. You can even reverse the lookup, scanning from bottom to top:

=XLOOKUP(E3;B:B;C:C;0;0;-1)

As I said, you’ll never use VLookUp again!

In the image below, the same list of numbers is presented in two columns :

• On the left column, no particular format is applied ;
• On the right column, a format where thousands are separated by a space, negative numbers are within brackets and zeros are “-” is applied.

Personally, I find the right column to be so much easier to read. I use this format so often that I have a Macro. Here’s how you can have it too :

• First, add the developer tab : File > Options > Customize Ribbon and check Developer

• Second, record the Macro : Developer > Record Macro. Set the name and the letter lowercase L to launch the Macro using Ctrl+L. Make sure to select “Personal Macro Workbook” so that the Macro will be available across all Excel files :

• Third, edit the format of whichever cell you are on by doing this and only this : right-click > Format Cells > Custom > # ##0;(# ##0);- > Ok > Developer > Stop Recording

Now you are set. Just do Ctrl+L to change the format. If you want to check the Macro you have recorded you can do Alt + F11 and on the left, go to “VBAProject (PERSONAL.XLSB)” > Modules > Module 1. You should see a code like this :

Sub format_1()
'
' format_1 Macro
'
' Keyboard Shortcut: Ctrl+l
'
    Selection.NumberFormat = "#,##0;(#,##0);-"
End Sub

If you want to play with filters and see the results of the sum of a column, just add on top a subtotal(109;range) formula to see the sum of the visible cells only.

It is as effective as it is simple.

In Excel, we all have to deal with constants. Whether it is the number of customers, a price or the number of employees, they are constants. We call them constants but they can be seen as parameters. I propose making them all blue. It takes only a few seconds and can spare you a lot of trouble. In the sea of black formulas, blue constants stand out like beacons of clarity, guiding you through the intricate maze of data with ease.

On the contrary : revenue, costs, cash, are all calculated using a formula and should remain black.

Here is why :

• If you go back to your Excel file some time later, you will instantly discern what can be tweaked and what must remain untouched
• If you want to simulate results while changing the parameters, it is much quicker and easier to spot what can be changed
• If the file is used by multiple persons, it prevents for putting a value on a cell containing a very complex formula. What’s worse than redoing what has already been done?

Here is an example : on the left, we cannot make the difference between constants and formulas. On the right it is as clear as it can be :

What happens if you don’t follow this rule :

• People will take more time to understand how the file is working
• You may be subject to someone erasing a formula for putting a raw value
• It shows other people you are not used to very complex Excel files

So, let’s embrace the artist within and paint our constants blue. Let’s illuminate the path to clarity and efficiency in the realm of spreadsheets. After all, in the world of Excel, a stroke of color can make all the difference.