Reflets Magazine #157 | How to Master Excel – With or Without AI

Artificial intelligence can certainly help you use Excel. But how do you stay in control of the final results? Jérémy Borot (E05) gives his advice in Reflets Mag #157. You have special free access to this article... don’t forget to subscribe! 

In October 2024, The Economist ran an article titled “Why Microsoft Excel won't die”. The magazine was celebrating the software’s 40^th anniversary, a rare level of longevity in the IT world. Excel, or its alternatives, is here for the long-haul. So it's best to learn how to use it!

In the right hands, spreadsheets can be powerful tools. But for the inexperienced, they are an endless time-suck and nonsense machine. You can produce a fascinating analysis in a matter of minutes, just as you can spend dozens of hours creating a workbook of muddled sheets with incomprehensible formulas and an illegible layout. It can keep you up at night and cause you to make big mistakes. I know because it's happened to me, more than once.

So how do you create a spreadsheet quickly and effectively? Here's an succinct overview in seven steps, based on a classic but complex example: the five-year business plan template.

Start With a Cover Page

Like a book or film. Create a sheet, hide the gridlines and add a large area of text. Then, write the title of your template in large font. Right underneath, the month and year. Below, explain the purpose of the file and its context. Write as if you were talking to a colleague. Describe the template, how it works and the purpose of each sheet. If you can write this executive summary easily, it's a good sign for the rest.

Continue With the End

After the title, create the output sheet. This is where the results of the template will appear. In our example, this could be the five-year P&L, with the years in the columns and the profit and loss in the rows. This sheet must only contain the results. No hypotheses or equations. Ideally, it should fit on one screen to make it easier to share, and anyone who reads it should be able to understand it at a glance. Take care with the structure of the rows and columns, as this will be used for the engine.

Establish the History

Create a separate sheet for the data used as a starting point. Last year's figures, past official audited results, raw data from systems, etc. Do not modify this information and indicate where and when you obtained it. Often, your future projections will be based on past events. Have you ever heard the expression "garbage in garbage out"? Well, it's true! So take time to check the quality of information you use as a basis for your work.

List the Hypotheses

Continue with the input sheet. It should include all the input data that can be manipulated in your workbook: the parameters that you want to play with or that you want to let your users adjust. All the elements needed to create scenarios must be gathered together on this single sheet, and not scattered all over the place. Make them more visible, for example, by colouring hypothesis cells yellow to draw the eye.

Build the Engine

Now for the heart of the reactor. It can have as many sheets as you like. Excel has no limits, and they're free. The important thing is that each sheet is individually easy to understand. In fact, give each one a name! Place a big title in cell B2. Organise these sheets according to the structure of the output sheet. Try to fit everything into one screen. And make sure the sheets look alike, or are even identical. If the five years of your plan are presented in columns, the same year must correspond to the same column letter on every sheet. This will simplify your formulas considerably.

Tips and Tricks

Learn about formulas. There's one for almost everything. Start with COUNT, SUM, SUB TOTAL, IF, ISERROR, SUMIF, CONCATENATE, ROUND, AVERAGE, QUARTILE, MIN, MAX, then watch a few videos on the Internet such as The top 50 Excel formulas. You'll save time. 

Break down calculations. Long-winded formulas are complicated to write, impossible to read and a source of errors. It's better to create five similar sheets that carry out the calculation in stages rather than one with convoluted formulas. 

Make sure you know how to use the tool. If "pivot table", "paste special", "record macro" and "select multiple sheets" don't mean anything to you... go and find out! It will help. 

Take care with the presentation. Hide the gridlines, avoid using borders, provide a key for colours used and choose them carefully, use indentation, give each sheet a title and subtitle, leave column A blank, name the sheets and the file, and place the cursor at A1 on the cover page when you save the spreadsheet. Your readers will thank you. 

Anticipate questions. For example, allow for two or three extra years in columns. Have you been asked for a three-year plan? Make a five-year one! If it’s a five-year plan, make it for eight years! People always ask "Could we add two years to the plan?". Try to anticipate any other requests! 

Get some help. Find a colleague who enjoys building templates. Some people love spreadsheets, others avoid them like the plague. Don't make life hard for yourself! If it's not your thing, give the job to someone who will enjoy it!

Test the Results

Well done! Your template will now display output data based on the input data. Make sure it’s robust by entering zero for all hypotheses. By observing the results of a few simple scenarios, you can quickly identify the majority of errors (which are inevitable) in your formulas. Is there a result that surprises you? Double-check it! 9 times out of 10, you will have made a mistake. 1 time out of 10, the result will be correct, but surprising. This means people will ask you about it. Prepare the appropriate explanation in advance.

Finally, Move With the Times

Published in Reflets Magazine #157. Read the issue exceptionally in free access. Subscribe to read our upcoming issues!  

Picture : © AdobeStock