In this post, I’ll show you how I like to use Conditional Formatting in my Excel models to add a disabled look to some cells based on a criteria. With this technique, you will get clear visual indications that will simplify inputs and avoid errors.

The context

As an example, you need to enter monthly sales volumes for a product that is yet to be launched. The spreadsheet is layed out with one column for each month. The goal is to have the input cells shaded for the months previous to the launch date.

Of course, you could do that manually, but that wouldn’t be fun nor very flexible. Instead I’ll show you how to setup conditional formatting to have it done automatically without resorting to macros.

Model Layout

The solution

1. Setup Data validation

To make sure the launch date inputs are valid, you need to add data validation with a drop down list of all months next to each product.

2. Setup Conditional Formatting

The next picture demonstrates how the conditional formatting is set, with L$6 being the evaluated month and $D11 being the launch date.



Conditional Formatting setup

3. Adjust formulas

Since this technique only changes formats and leaves the input in the shaded cells, you need to adjust the formulas that are connected to the inputs. This insures that your results are not affected by inputs in the shaded cells. You can see how I have adjusted the annual sums and the revenue calculation to consider the launch date by downloading the example.

Final thoughts

As it is setup in the example, you have to adjust the inputs each time you change the launch date. The conditional formatting acts only as visual help for inputs. However, it could also be interesting to have formulas that adjust with the launch date instead of the inputs.

Download:

Conditional Format Controls – Part Deux.xls (size 124Kb)

Other related post:

Conditional Formats Control – The Warm-Up

I have found that this method is really useful in reading formulas because you can promptly see a displaced reference that points to a wrong column.

This week, I wanted to quickly see if I had formulas that might be referring to a wrong column. In order to do so, I switched Excel in R1C1 reference style and simply used Excel’s Find functionality to look for C[1] and C[-1] across the workbook.

As an example, I have a formula to get the revenues in another worksheet. In a consistently structured workbook, that formula would look like this in R1C1:

=Rev!R14C 'Refers to the Rev sheet, row 14 and same column

However, if my formulas are shift by one column, they might look like one of these:

=Rev!R14C[1] 'Refers one column to the right
=Rev!R14C[-1] 'Refers one column to the left


If you find some offsetting errors, simply replace the [1] or [-1] with nothing. Beware of collateral damages by clicking “Find All” and reviewing the list of results before replacing all occurences.

Other related post:

R1C1 Enlightenment
Seven tips to develop a structured model

Basically, the depreciation model needs the following inputs:

• Monthly capital expenditures forecasts
• Useful life of the assets

With those inputs we’ll be able to calculate:

• Monthly depreciation
• Accumulated CAPEX
• Accumulated depreciation

The next picture shows you how the model is setup.

Click the picture to enlarge

From my experience, to calculate depreciation most people use a simple SUM across the number of months (e.g.: =SUM(M10:X10)/12). However, this function is error-prone and inflexible.

To build a better formula, we’ll replace the range in the SUM function with OFFSET which will give us a flexible and appropriate width. The next picture illustrates the formula.

The tricky part is highlighted in blue. You want the range to include the capital expenditures of the previous months, but not too much. You are limited by the two elements in the MIN function.

• $D17 which represents the useful life of the assets.
• Q$7 which refers to a helper row with the index of the current month in the model.

So, if we are in the 5th month of the model and the useful life of the assets on that line is 12 months, we would widen it only to get 5 months of capital expenditures. However, from the 12th month on, the second parameter becomes irrelevant.

Download the model

Since this blog is all about developing better models, I strongly suggest you download the file with the example discussed above.

In addition to what is presented here, you’ll see a lot of additional explanations and 2 other uses of the OFFSET function to get yearly totals and also to get year-end balances.

If you still have problems with the OFFSET function, I suggest you read the OFFSET tutorial and download the attached file.

What do you think? Do you have any other use for OFFSET you would like to share?

Other related post:

Improve Table Visualization using OFFSET and Conditional Formatting

This tip can be applied on a table where the items of the main field are layed out on multiple lines, just like in the next picture.

Table before Conditional Format

It is possible to improve the readability of that table with formatting by separating the group of rows for each account with a border and by changing consecutive identical items to light gray.

Table after Conditional Format

Here are the rules that were applied to achieve this.

A few notes

• Rule #1 is applied to all cells in the table, whereas rule #2 is only for column A.
• This setup is valid for Excel 2007 or later. To adapt it for previous versions, in column A, you need to add a rule that combines rule #1 and rule #2 and apply borders and pale gray fonts. This is due to the fact that only one Conditional Formatting rule could be applied in older Excel versions (download the accompanying workbook for more details).
• I’ve put both the A1 and R1C1 formula in the table. I sometime find it easier to work in R1C1 while doing Conditional Formatting, the main reason being that I don’t have to bother with what is the active cell while editing the formula.
• I could have used a simpler formula without the OFFSET function like =$A2=$A3 instead. However, I prefer the OFFSET version since it is robust to row insertion.

You can download the accompanying workbook to see the table in action and with additional comments.

If you want to learn more about R1C1 reference style, I encourage you to read this post.

OFFSET is a useful function that adds lots of flexibility to your Excel models.

Using it can help you transform what would have been formula modifications into simple inputs changes. A common example is to use it to automatically compute monthly figures into year-to-date results by changing one input instead of changing formulas each month.

Download the OFFSET tutorial.xls file, to learn how to use this function.

The file includes:

• A quick reference table that describes each function parameters
• An interactive tool to visualize how changing the parameters affects the results (screenshot below)
• An applied example to compute year-to-date results

The interactive OFFSET tutorial tool in action

Click on the link to download the file: OFFSET tutorial.xls

Feel free to share it!

UPDATE: I have updated the file to correct the conditional formatting rules when negative width or height inputs are used in the OFFSET tutorial tool.

One of the improvement mentioned is the ability to repeat down labels facilitating the use of formulas with the pivot table as a source. If you don’t want to wait for the next Excel version, here’s a trick to get to the same result.

Before getting to the repeat label part, we need to make sure the pivot table is set properly, more precisely the row fields. We’ll have to adjust the field settings for each row field except for the last one on the right. In the pictured example we have three row fields (Cie, Category and Product) therefore the following steps will be done twice in this case.

To adjust the settings of a field, you simply need to select one of its elements, right-click and then select “Field Settings”.

• In the “Subtotals & Filters” tab, remove the sub-totals
• In the “Layout & Print” tab. You settings should as in the next picture

Note: In Excel 97-2003, you remove the subtotals in the field settings dialog and then click on the “Layout…” button from there.

Finally, simply add some helper columns next to your pivot table. Take a look at the following picture for the setup.

Helper column with repeated labels

Other thoughts

Beware: Your pivot table might stretch up or down so you want to make sure that you have enough formulas.

If you don’t like the look of your pivot table, duplicate it and hide the less esthetic section or even the whole sheet.

You can download a file with the pivot table before and after here: Getting More From Your Pivot Tables

In it’s basic form, you can use it with a simple condition depending on the cell value. However, a more interesting yet underused way of working with conditional formats is with a formula condition. Let’s see how we can use both.

Conditionally format a cell based on its value

Let’s start with an example where a model user needs to input a product mix (e.g. overall sales to be broken down into the different categories). Obviously, the total mix should be 100%. The next picture shows how the model could be layed out.

I have added a conditonal format rule that will change total cells to red when the value is different than 100%. To do so, follow these steps:

1. Select the cells on which you want “Conditional Formatting”. (In this case the cells with the totals)
2. In the “Style” section of the “Home” tab, click “Conditional Formatting” then “New Rule…”. (In Excel 97-2003, you will find it in the format menu)
3. Select “Use a formula to determine which cell to format”
4. In the edit rule description, set it to cell value not equal to 1.

Conditionally format a cell based on a formula

Following on our previous example, suppose we want to highlight the input cells like in the picture below.

To add the conditional formatting, simply follow these steps:

1. Select the cells on which you want “Conditional Formatting” (I.e.: the input cells).
2. In the “Style” section of the “Home” tab, click “Conditional Formatting” then “New Rule…”. (In Excel 97-2003, you will find it in the format menu)
3. Select “Use a formula to determine which cells to format”.
4. Type the formula to be evaluated in the “Format values where this formula is true:” section. In our example, the formula looks like this: =SUM(E$23:E$26)<>1

Conditional Format gives you a quick glance to verify the inputs and might even highlight errors that would have normally been missed. As an example, look closely at the second picture, you’ll notice that the total seems to be 100% when in fact it’s not.

Note that, in some instance, you might want to add rounding to your conditions when your totals are close but not quite equal to 100%.

You can download the companion file used for the examples here: Conditional Formats - The Warm-Up

Are you looking to:

• Quickly find the sources of all your #REF errors found in some cells?
• Audit your model for potential undetected problems?

Simply use Excel’s Find functionality to find all of the #REF errors.

• Press CTRL-F to launch the “Find” dialog
• Type #REF in the “Find what:” section
• Click the “Options” button to expand the dialog, if it’s not expanded yet.
• Select “Workbook”in the “Within” drop-down
• Click “Find All”

Your Find dialog should look like this

Note that, you won’t find #REF in names and hidden worksheets, but you will find those in hidden cells, as long as the worksheet is visible.

Bonus tip

The next time you have an unused section or worksheet you’re thinking of deleting:

1. Save your document
2. Check for #REF using the steps above
3. Delete your section
4. Check again for #REF

I often find myself having a hard time explaining what a financial model is because to me, it could be almost anything.

That said, I’ll set aside the financial part today and show you a very simple Excel model, useful for those of us who have kids in elementary school. It’s my personal version of the random cards you might have at home to help the kids learn their alphabet or addition tables.

The model is rather simple. You have one column with all the possible elements and next to it, another with the formula =RAND(), which gives you a random number between 0 and 1 at each recalculation. Then you simply have to look up the letter next to the smallest value. Each time you want to change the letter, you simply need to press F9. Excel will then recalculate all RAND() formulas. You can see it in action demonstrated in the picture below.

You can download it here (file size: 101 KB). It also includes an addition table. I’ll let you work out the multiplication table.

Have fun!

Update

• A1 (default)
• R1C1

But why should you care? You might think the default A1 reference style is sufficient for you, but that would leave opportunities only the R1C1 reference style allows.

Let’s first see the difference between both reference styles

• R[ ± x ]: Row offset
• C[ ± x ]: Column offset
• Ri: Row i
• Ci: Column i

For example, in R1C1 reference style, an absolute reference to cell B2, would be =R2C2 and a relative reference to the cell above would be =R[-1]C.

I suggest, you try it with simple formulas in Excel to familiarize yourself with its structure.

I rarely use the R1C1 reference style to design my formulas, but with time, I’ve found different uses for it, such as:

Check for identical formulas

Look carefully in the formula bar and you’ll notice every copy of a formula is identical to the original. When I’m not familiar with a model, I like to check complex formulas in R1C1 before modifying them to make sure that they are in fact identical, not just looking like it, and doesn’t simply look like it.

A common catch could be as simple as the one in the next picture, where the hidden row made the formulas in cells B14 and B16 look identical

Quick Find & Replace

You can also quickly modify the same formulas copied over multiple range by leveraging the uniqueness of the R1C1 reference.

As an example, you might have a model with monthly and quarterly formulas next to each other just like in the picture below. In R1C1, you simply highlight the entire row and do a quick Find & Replace to modify your them.

Conditional Formats

When modifying conditional format formulas in the Conditional Formatting Rules Manager (available in Excel 2007 only), it’s often easier to work in R1C1 so as not to be dependant on a cell from which the reference is established, just like you would in A1 reference type.

VBA Code

You’ll also find that using R1C1 references will give you more robust code that won’t crash on the first column insertion.

It can also be handy when you want to know the column number instead of its letter reference (e.g. A=1, Z=26, …)