Excel Formulas

Conceive Excel Formulas that are Easier to Write and Maintain

Are you dealing with nested Excel Formulas with a jungle of parenthesis and semicolons?

Limit the space for errors with simpler Formulas. It requires technique and hard work but here you will receive useful guidelines to achieve it.

Whatever the technique to be used you need to fully grasp the most used Excel Functions

Here are some useful articles:

Planning before typing will avoid you to be trapped in long and bored chains of arguments and functions. There are other techniques that will empower you to work smarter with formulas, before you know them let me tell you a short Excel Formula story…

Karen spent two weeks of hard work; she was building a complex Spreadsheet full of advanced Vlookup formulas her boss requested.

In the due date, her face changed from glory to desperation. A keystroke triggered a disaster…

The #REF! error spread like sugar over the Sheet. Now something is wrong, somewhere. How did she fix this due-date-error?

She needed to focus on the solution but…

An imminent call from her boss increased her frustration; she didn’t have clarity to solve the problem.

Working without a layout in Excel is terrible; you cannot predict where the next blast could occur.

You can avoid this unnecessary frustration by conceiving smarter formulas.

How to conceive Excel Formulas that are Easier to Write and Maintain?

Here’s how…

1. Master Excel Concepts/Functions to Build any Formula and to Make It Simpler

Are you the guy who says: “I don’t need the instructions, let me just do it? This method often takes longer than following the directions in the manual.

It takes time, effort but the more you learn the more proficient you become, the more you learn you are able to remove the obvious and reach simplicity.

Compare this Excel Formula…

=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8+A9*B9+A10*B10

With this other one…

=SUMPRODUCT(A2:A10;B2:B10)

Just simpler and more elegant.

Read these articles: Learn the Way to Show Messages Instead of Results

2. Plan First, Write Formula Next

Are you one of those Excel users who write formulas directly on the cell? Then Listen to this:

“By failing to prepare you are preparing to fail.” – Benjamin Franklin

Are you a busy professional that can never seem to find enough time. Proper planning is NOT a waste of time.

3. Add Intermediate Steps to Limit the Depth of your Excel Formula

Build shorter Formulas by adding intermediate steps in cells, then use the intermediate ranges nested in formulas.

This technique empowers you to debug, add and read Formulas more efficiently even week, months or year ahead.

4. Never Introduce Hard Coded Numbers

Keep in mind that introducing hard coded numbers in your Formulae is a short term gain. You may say “I write it just to get a quick answer” but remember than a common question after you deliver an answer is “what if?”…

Be aware of other reasons, read: 7 Critical Reasons to Avoid Hard Coded Numbers

Other times you may introduce constants as hard coded number because you are sure they are fixed quantities, but this decision may lead to unnecessary manual work in the future and may make your formula not so much readable.

Read: How to Store Constants in a Central Location with Excel Names

5. Develop your Excel User Defined Functions Library

What looks better?

=IF(F$1=”Shift 1″;IF($B2<16;IF($C2>16;16- $B2;$C2-$B2);0);IF(F$1=”Shift 2″;IF($C2<16;0;IF($B2>=16;$C2-$B2;$C2- 16))))

Or…

=HOURSPERSHIFT(B2;C2;2)

If you are not an insane coder you like the second option.

Excel Custom Functions are very versatile but they need to be constructed according to the Excel conventions you are so accustomed, this way you can use UDFS frequently as Built-In functions, read here how to do it: Excel UDF

Return from Excel Formulas to Excel Spreadsheet Authors Home