Excel Guidelines for Professionals

We have been researching spreadsheets for almost a decade! We’ve seen it all: from spreadsheets that neatly solve a business need to complete horror stories. Over the years, people have often asked us: What defines a good spreadsheet? How do I make it better? Subscribe now to find out with new Excel Guidelines for Professionals every other week!

Our Excel Guidelines

In the list below you’ll find all the Excel Guidelines we’ve published so far, from newest to oldest.

Excel Guidelines for Professionals - No 17 :: Every Spreadsheet Needs a Second Opinion :: PerfectXL

Guideline 17: Every Spreadsheet Needs a Second Opinion

Even your piece-of-art spreadsheets contain mistakes or at least risks. Our advise is to ask for a second opinion on every spreadsheet you create, to avoid errors.

About a second opinion on your spreadsheet

Guideline 16: Clean Your Input Data

Garbage in, garbage out? Not if you pay serious attention to cleaning your input data first. And if possible, in such a way that it becomes an automated process for the next time you use the same spreadsheet.

Take time to clean your input data

Excel Guidelines for Professionals - No 15 :: PerfectXL

Guideline 15: Avoid Long Formulas

“The more the words, the less the meaning [Ecclesiastes 6:11]”

Most of you fervent Excel users will likely be bigger friends with numbers than flamboyant expressions, but this topic really calls for a Biblical citation. Even Thomas Jefferson, 19th century president of the United States, remarked: “The most valuable of all talents is that of never using two words when one will do.”

Read why you should avoid long formulas

Excel Guidelines for Professionals - No 14 :: PerfectXL

Guideline 14: Pay Attention to Units and Number Formats

Excel horror stories are often related to accidentally changing, shifting or changing units or number formats. Where Americans think in Dollars, Europeans might think in Euros. Large organizations think in thousands, small organizations might calculate in cents. And the English have their own units for everything.

More about the use of units, number and date formats

Excel Guidelines for Professionals - No 13 :: PerfectXL

Guideline 13: Keep a Formula Close to its Input

Place formulas as close as possible to their input variables. You have a lower chance of mistakes, you make optimal use of Excel’s support, and your spreadsheet becomes easier to carry over to someone else (what’s new?)

Read more about keeping formulas close to their input

Excel Guidelines for Professionals - No 12 :: PerfectXL

Guideline 12: Don’t Neglect Excel Errors

However tempting it may be, it is a bad idea to leave standard Excel errors in your workbook. Because after a while, you no longer know whether you left a mistake consciously or whether there is something wrong with your spreadsheet.

Read why you shouldn’t neglect Excel errors

Excel Guidelines for Professionals - No 11 :: PerfectXL

Guideline 11: Prefer INDEX and MATCH over VLOOKUP

There are several ways to search in Excel. The VLOOKUP and the combination of INDEX and MATCH are well known. To our great frustration, VLOOKUP is used a lot more often than the INDEX and MATCH, while the latter are really much better. INDEX and MATCH are less error prone and a lot more efficient.

Why we prefer INDEX and MATCH over VLOOKUP

Excel Guidelines for Professionals - No 10 :: PerfectXL

Guideline 10: How To Manage File Versions in Excel

What version did I receive from you? Oh, I have modified the previous version. Did the board make a decision based on that version? Many nightmare has arisen because of confusion about the correct file versions of Excel documents. In the file itself, it is often difficult to see what the latest version is. And is the latest version the right version, or was it a “temporary break” to try something?

Read more about version management of Excel documents

Excel Guidelines for Professionals - No 09 :: PerfectXL

Guideline 09: Array Functions Are Past Times

Long ago, Excel was not as clever and advanced as today, but the so-called array functions have been around for a long time. However, people do not know them, and do not understand them well. Perhaps because of the very different nature of the formulas or the strange way of activating (CTRL-SHIFT ENTER instead of just ENTER).

Read more about array functions in Excel

Excel Guidelines for Professionals - No 08 :: PerfectXL

Guideline 08: The Dangers of Hidden Information

Do not hide anything except some sheets. Excel has too many nice options. In Guideline 6, we already advised not to use the option of merging cells, or only if really necessary. In this guideline we talk about the dangers of hidden information.

The dangers of hidden information in spreadsheets

Excel Guidelines for Professionals - No 07 :: PerfectXL

Guideline 07: Use Consistent Labels

Numbers without meaning are worthless. Excel is a program for numbers, but it is only useful if it is clear to the reader or user what these numbers mean. So, make sure that you provide all data and formulas of textual information. Let’s call these information labels.

Read more about consistent labels

Excel Guidelines for Professionals - No 06 :: PerfectXL

Guideline 06: Do Not Merge Cells

It is so tempting! Merge cells in Excel so they form a header above two or more columns. Yes, it looks nice but resist the temptation because it can be dangerous.

Read why we advise not to merge cells

Excel Guidelines for Professionals - No 05 :: PerfectXL

Guideline 05: Delete What You Don’t Need

Compare transferring a spreadsheet to renting your house via Airbnb. It remains more neat when you clean it first, right? Messy stimulates messy, neat stimulates neat. Remember an average spreadsheet will have 12 users after you! What would happen when the chain starts with a messy file?

Read more about clean spreadsheets

Excel Guidelines for Professionals - No 04 :: PerfectXL

Guideline 04: Separate Input & Output

When you read a book or a report or a website, we read from top to bottom and from left to right. Remember that setup when you create a spreadsheet and follow this structure, both within a worksheet and between them. We advise to distinguish separate input, output, calculation, documentation and dashboard sheets.

Read more about the separation of Input and Output

Excel Guidelines for Professionals - No 03 :: PerfectXL

Guideline 03: Calculate Formulas Only Once

Sometimes, you need the exact same formula, based on the same numbers, in multiple places. Like a calculation for inflation, on which you base several other formulas. You might be tempted to calculate the formula twice, but resist this temptation: Calculate formulas only once!

Read more about the single use of calculations in Excel

Excel Guidelines for Professionals - No 02 :: PerfectXL

Guideline 02: No Fixed Numbers in Formulas

We have all seen “magic numbers” like this = SUM(A4:A12)*0.145. However, the use of magic numbers is a bad idea. A future user will not know where the number originates from. And fixed numbers don’t change when needed, and thus might be overlooked when a change is made.

Read more about fixed numbers in spreadsheets

Excel Guidelines for Professionals - No 01 :: PerfectXL

Guideline 01: Document your Work

What separates the great spreadsheets from the other ones, is documentation! This will help future users of the spreadsheets to really understand your work. Even when the future user is you. Your spreadsheet should contain a ‘Documentation’ sheet as first, or last worksheet. Explain the future reader what the spreadsheet is for, where you obtained the data and where the results may be used.

Read more about documenting your work

Excel Guidelines for Professionals - Introduction :: PerfectXL

Introduction to our Excel Guidelines

Excel is easy, right? Enter some figures, formulate some calculations and you’ve got immediate results. A spreadsheet is born. But then it grows, and grows, and… On average, spreadsheets continue to be developed for five years, and they are used by thirteen different users. It’s been estimated that 7 billion dollars are lost each year due to errors in spreadsheets. Hence our Excel Guidelines for Professionals.

Introduction to our guidelines

Never miss a Guideline!