TPF Financial Budgeting Services

We can help you escape from spreadsheet Hell!

We can also integrate your financial plans with any other business areas

If you want to replace silos of information with JOINED-UP PLANNING contact us now!


TPF Merchandise Planning Services

The Planning Factory offer a full service in all areas of merchandise planning

You may need:

  • An audit to measure the effectiveness of your current planning process
  • Assistance with implementation of 3rd party solutions
  • Design and implementation of custom solutions
  • A quickly implemented off the shelf solution
  • Training in merchandise planning concepts and techniques

Whatever your requirements, we will do our best to provide a solution that fits your business



Visitors currently browsing this site:





Colour Coding TM1 Spreadsheets

How do I colour code my spreadsheet cells so that users can see whether data can be entered or not?

This can be achieved using Excelís conditional formatting capability The spreadsheet shown below has 6 variables that are enterable, but only in the plan version. Actual LY which contains historic data has been protected from data entry using security.

The enterable N: levels displays with a green background.

The summary levels where data spreading must be used to enter data are displayed with a purple background.

The actual data is displayed with a white background

Tm1 Colour Coding Image 1

When a selection is made in any of the dimensions that raises the level of the data shown the display changes accordingly. In the screen shown below we have raised the product level from Ladies Hosiery Sock to the summary of all Ladies Hosiery. As a result the data displayed is no longer at the N: level and the green background has accordingly been changed to purple indicating that data entry can only be done using data spreading.

Tm1 Colour Coding Image 2

How have we done this?

In order to tell Excel's conditional formatting what to do we have to set some values that depend on two things

* Firstly whether or not the data is enterable at all. * Secondly whether the data is at the N: Level.

Both of these make use of hidden columns (Column A here) or rows (Row 8 here) as shown below

Tm1 Colour Coding Image 3

Telling Excel whether the data is enterable

In column A we place a 1 in the plan version row (e.g row 15) of the variable that is enterable. (Note that is the sheet shown the variable name is, in fact, entered in every row but has been self coloured in the plan rows to make the display easier to understand)

Telling Excel whether the data is at the N: level

Next to each title element that is selectable we place a formula that returns the element level.

For example in cell A4 we have =ELLEV(C4,D4) which returns the element level of the product selected. If the product is an N: level it will return 0 otherwise it will return the relevant level of the product selected.

In the cells in row 8 above the time elements we do the same, but in this case we also add the sum of the element levels in cells A3 to A6.

=SUM($A$3:$A$6)+ELLEV("IMPACT:TIME",E9)

This means that in each cell in row 8 we can now see the combined total of the element levels for all dimensions. If the combined total = 0 then the data shown will be N: level.

If the combined total is greater than 0 then the data shown cannot be N: level and thus data can only be entered using data spreading.

The combination of knowing whether data is rule based or enterable and knowing the level is the basis for setting the conditional formatting which is done as follows.

Select the entire data area and from the Excel menu select Format -> Conditional Formatting.

Enter the first condition

Formula Is =$D10="Actual LY"

and set the desired format - in this example a white background

Add the next condition

Formula Is =AND($A10=1,E$8=0)

and set the desired format - in this example a green background because the flag indicating that the data is enterable = 1, and the sum of the element levels = 0 indicating that the data is at the N: level and is therefore enterable normally

Add the final condition

Formula Is =AND($A10=1,E$8>0)

and set the desired format - in this example a purple background because the flag indicating that the data is enterable = 1, and the sum of the element levels is greater than 0 indicating that the data is higher than the N: level and is therefore only enterable using data spreading.

Note that the use of $ signs indicating the relative/absolute references in Excel is critically important here.

Tm1 Colour Coding Image 4

You can obviously change the format selected to suit your own aesthetic principles.

Drawbacks of using this method

Excel 2003 only allows a maximum of 3 conditions for conditional formatting, so using this method means that you canít use conditional formatting for traffic lighting exceptions.

Does it work in TM1 Web?

Yes.