Alternating Row & Column Formatting Options in Excel reports

Knowledge Base
1

A common report formatting requirement we see for tabular data is the need to add an alternating row or column color to make data easier to read. There are 2 main options that can be used in Excel to define this formatting, which ReportWORQ also supports:

  1. Conditional format rules
  2. Excel table formatting

The attached Excel file has examples of these format options, which are described below:
Alternating Row Formatting Sample.xlsx (15.7 KB)

Conditional Format Rules
Conditional formatting rules must always be manually configured, but provide the greatest control over report row + column formatting. They also work very well with dynamic reporting features from data sources that are supported in ReportWORQ, like Planning Analytics’ Active Forms.

You can, for instance, make a conditional format rule based on whether or not the current row is an even row (see the “Mod Conditional Formatting” worksheet in the attached workbook for this example):
=MOD(ROW(),2)=0

If you don’t want the first row’s format color to potentially change as rows + columns get inserted into your report you can even create your own formula in a separate, hidden column that uses your own logic (see the “Custom Conditional Formatting” worksheet in the attached workbook for this example):

  • Value in cell A4: 1
  • Formula starting in cell A5: =IF(A4=1,0,1)
  • Conditional format formula: =$A4=1

Excel Table Formatting
You can define a range of data in Excel as a table, via the “Format as Table” option in the Home ribbon tab. Tables support alternating row, column header, and other format styles. Once defined, Excel automatically applies the selected style to that Table data range. See the “Table Formatting” worksheet in the attached workbook for an example of this.

Table formatting is most useful for static reports or reports that are being manually updated with new rows and columns of data-- as long as data is added within the table, the formatting gets automatically applied.

Excel Table formatting is also an excellent choice if you are using some of the ReportWORQ dynamic reporting options like the AIModeledExport and RWSQL functions. These functions can directly output dynamic sets of data from those data sources into an existing Excel table, making for easy report formatting.

Table formatting is not recommended for reports that are based on dynamic reporting options available from data sources like Oracle Smart View, Planning Analytics, and Workday Adaptive Planning, where the number of rows and columns may change during refresh, sometimes removing table formatting or leaving some rows + columns outside of the Excel Table range.

Using RWSUPPRESS
For people who use RWSUPPRESS in reports with the above alternating row + column formatting options: you should select the “Delete” Mode option in your RWSUPPRESS formulas, otherwise the alternating formatting will also be applied to hidden rows.

More Information
Additional Microsoft reference material on both of the above formatting options can be found here:
Apply shading to alternating rows or columns in a worksheet

1 Like