If you have a detailed list report in Planning Analytics for Excel that has many (more than say 10) rows without any totals, then it can be hard for the user of the report to read across the data. Using alternate row formatting can assist with making reports much easier to consume.
Remember the old 132 column dot matrix printers? Remember what colours the paper was? Yeah, I know, those printers were around with the dinosaurs, but for those of us that do remember, they used light blue and white alternate row colours. This made them much easier to read when the reports landed on your desk (yes, in paper form).
Alternate Row Colours in Dynamic or Universal Reports
In Planning Analytics for Excel, there are two report types that we would typically want to use alternate row colours. Those are Dynamic and Universal Reports. The reason is that those two report types would typically be used to create long, variable length reports. For example, a sales report identifying every customer, or every product. You might have 200 rows today and 250 tomorrow and so on. Scanning across the data could be difficult. So we will use alternate row colours to make it easier to read.
Here is an example of a Dynamic Report using alternate row formatting:
The Key to Alternate Row Formatting in TM1
The most important trick to use is the Excel formula =MOD(ROW(),2)=0. You would use this inside conditional formatting and then define the formatting to be applied when it is true. This resolves in every even row number having the formatting you define.
How to Format a Report with Alternate Colours
In a Dynamic Report, the Format Area drives how the body of the report is formatted. Therefore you need to modify the format area and use Excel’s conditional formatting to achieve the desired result.
In a Universal Report, the method is similar to what we describe below, but you will be formatting the body of the report directly, using a combination of the Leaf indicators and MOD formulas to drive the outcome.
Here is how to do it for a Dynamic Report:
- Click Show Format Areas in the Dynamic Report part of the Planning Analytics for Excel ribbon.
- In the format area, select the range that covers where you want alternate row colours to be applied. In this report I am only interested in formatting the Leaf level rows, so I have selected all the cells that will be used for those rows.
- From the main Excel ribbon, select Conditional Formatting, Manage Rules.
- Leaving the dropdown in the dialog box as Current Selection, click the New Rule box.
- Then in the New Formatting Rule dialog, select Use a formula to determine which cells to format.
- In the formula space, enter “=MOD(ROW(),2)=0” (without the inverted commas!).
The ROW function here returns the current row number.
The MOD function returns the reminder after dividing the first value by the second. For example, if you had MOD(7,2), this would return a 1 as there is a three 2’s in 7 with 1 left over (the remainder). Therefore if row 20 was being evaluated with this entire formula, it would be MOD(20,2), which, because there is exactly 10 lots of 2 to get 20, would have a remainder of 0 and thus the condition would be True. As it is true the conditional formatting is triggered.
- Then hit the Format box and set the format.
- Then just click Ok, Ok and Ok again to exit the conditional formatting. Now in the Format Area, if you are on row 9, like my example above, there will be no visible formatting (because MOD(9,2) does not equal 0, and thus the condition is false).
- Hit Rebuild in the PAX ribbon and you will then see the alternate row formatting.
Do You Need Help with Planning Analytics for Excel?
Creating awesome reports in Planning Analytics for Excel is an art. We’re the artists. Give us a call if you need some help and we’ll create you an amazing
