Quick Tip – Displaying alternate row shading

Monday, July 25, 2016 - By Jon Gardner

..making reports easier to read

Using alternate row shading can make spreadsheets much easier to read – especially if they are wide reports. This is easily accomplished with Jet Professional when using the NL(Rows) command in conjunction with Excel’s Conditional Formatting functionality.

For this functionality to work conditional formatting requires the use of a formula – entered directly into the New Formatting Rule dialogue box.

In the example (left) the conditional formatting formula used is as follows –

              =MOD(ROW(),2)=0

This formula returns the row number using the ROW function and the MOD function returns its value. For even numbered rows the MOD function returns the number 0 and all the selected cells in that row will be formatted.

This conditional formatting is set up on the first row when Jet Professional is in design mode. When the NL(Rows) functionality expands (when the report is run), so the conditional formatting function also gets replicated down the page to provide the look shown.

Setting up the functionality for your Jet Professional report is easy.  In the example shown we have used Jet’s NL(Rows) command, but the conditional formatting functionality can easily be adapted to highlight columns if the report uses Jet’s NL(Columns) function.

Having first written your Jet Report – and tested it fully – drop back into Design mode and highlight the cells in the row you wish to colour (ideally have the NL(Rows) function on an even-numbered line).

To specify conditional formatting based on a formula, go to Home ⇒ Styles ⇒ Conditional Formatting ⇒ New Rule.  The New Formatting Rule dialogue box opens. Click the rule type Use a Formula to Determine Which Cells to Format, and then specify the formula in the manner shown.  Please note that the formula must begin with an equals sign (=).

Once the formula is entered click Format ⇒ Fill and select the colour for your even-numbered row.

Click on OK ⇒ OK and, if the selected cells are on an even-numbered row they should now be coloured.

Conditional formatting such as this vastly improves the look and presentation of reports. If a report looks good people will want to look at its content and the information it imparts. If you need any further information, or a copy of our sample report, please click here – Contact us