Conditionnal Format Controls – Part Deux

By Sebastien Labonne | Published: December 17, 2009

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

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.

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

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)

Spotting Errors

By Sebastien Labonne | Published: November 17, 2009

In a workbook, I like to be consistent in the use of columns across all worksheets. If the first month of my model is in column L in one worksheet, then it will be the same in all sheets.

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.

How to Model Depreciation

By Sebastien Labonne | Published: November 11, 2009

Completing the OFFSET series, let’s examine a real-life example on how to use the OFFSET function to model depreciation.

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?

Improve Table Visualization using OFFSET and Conditional Formatting

By Sebastien Labonne | Published: November 3, 2009

With our knowledge of the OFFSET function, here’s a tip on how to use that function combined with Conditional Formatting to make a table more legible by decluttering.

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.

Rule	Description	A1 Formula (from row 2)	R1C1 Formula
#1	Add bottom border	=$A2<>OFFSET($A2,1,0)	=RC1<>OFFSET(RC1,1,0)
#2	Change font to pale gray	=$A2=OFFSET($A2,-1,0)	=RC1=OFFSET(RC1,-1,0)

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.

Posted in Tips | Tagged Conditional Format, Excel Function, Format, OFFSET, Tables, Visualization | 1 Comment

OFFSET Function Tutorial

By Sebastien Labonne | Published: October 28, 2009

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.

Posted in Function Tutorial | Tagged Excel Function, OFFSET, Tutorial | 3 Comments

Getting More From Your Pivot Tables

By Sebastien Labonne | Published: October 19, 2009

In A Few More PivotTable Improvements in Excel 2010, Diego Oppenheimer, Program Manager on the Excel team at Microsoft, discusses new pivot table features being implemented for the next release of Excel.

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

Posted in Getting More From... | Tagged Pivot Tables, Tips | 1 Comment

Conditional Formats Control – The Warm-Up

By Sebastien Labonne | Published: October 13, 2009

Conditional formats is a wonderful feature that incrementally adds format to a cell based on some criterias. You could use it to highlight cells by adding a color fill, changing borders or modifying any other format element.

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:

Select the cells on which you want “Conditional Formatting”. (In this case the cells with the totals)
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)
Select “Use a formula to determine which cell to format”
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:

Select the cells on which you want “Conditional Formatting” (I.e.: the input cells).
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)
Select “Use a formula to determine which cells to format”.
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

Posted in Excel Functionality, Model Techniques | Tagged Conditional Format, Controls, Error Handling | Leave a comment

Improve your model now! #REF Editions

By Sebastien Labonne | Published: September 21, 2009

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:

Save your document
Check for #REF using the steps above
Delete your section
Check again for #REF

Posted in Improve Your Model Now! | Tagged Auditing, Errors, Find, Tips | Leave a comment

Back to School – The Excel Model

By Sebastien Labonne | Published: September 11, 2009

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

There is a new version of the file with Jim Carson’s suggestion of adding random colors to the letters. Thanks Jim!

Posted in Models | Tagged Model, RAND | 2 Comments

R1C1 Enlightenment

By Sebastien Labonne | Published: September 3, 2009

You might know that Excel has two different reference styles for formulas:

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

Reference Type	A1 Style	R1C1 Style
Relative	=A1	=R[ ± x ]C[ ± x]
Row-Absolute	=A$1	=RiC[ ± x]
Column Absolute	=$A1	=R[ ± x ]Ci
Absolute	=$A$1	=RiCi

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, …)

Posted in Excel Options | Tagged Auditing, Conditional Format, Find & Replace, Options, R1C1, Reference Style, Settings | 2 Comments

Search
Auditing Best Practices Conditional Format Controls Data Tables Data Validation Depreciation Error Handling Errors Excel Function Excel Functionality Find Find & Replace Format Formula Go to Input Model Model Structure OFFSET Options Pivot Tables R1C1 RAND Reference Style Scenario Sensitivity Settings Shortcuts Tables Technique Tips Trigger Tutorial Visualization
WP Cumulus Flash tag cloud by Roy Tanck and Luke Morton requires Flash Player 9 or better.
RSS updates
- All posts
- All comments
Email updates

Enter your email address:
Share this blog
Recent Posts
Recent Comments
Blogroll
Links
- ModelCom
Meta
The Financial Modeler

Leverage the power of Excel

Conditionnal Format Controls – Part Deux

The context

The solution

Final thoughts

Download:

Other related post:

Spotting Errors

Other related post:

How to Model Depreciation

Download the model

Other related post:

Improve Table Visualization using OFFSET and Conditional Formatting

A few notes

OFFSET Function Tutorial

Getting More From Your Pivot Tables

Other thoughts

Conditional Formats Control – The Warm-Up

Conditionally format a cell based on its value

Conditionally format a cell based on a formula

Improve your model now! #REF Editions

Bonus tip

Back to School – The Excel Model

Update

R1C1 Enlightenment

Check for identical formulas

Quick Find & Replace

Conditional Formats

VBA Code

Search

RSS updates

Email updates

Share this blog

Recent Posts

Recent Comments

Blogroll

Links

Meta

The Financial Modeler