Improve Excel Reports with Conditional Formatting

If you have been using Excel templates to report LIMS data you are probably well aware of the power and flexibility Excel provides. Excel’s conditional formatting feature offers another technique you can use to improve the readability of your reports. Consider conditional formatting, for example, when you want to highlight values that meet a specific condition.

The screen above shows a simple monthly operating report. Recipients of this report who know the acceptable range of values for each column can review the summary statistics and scan the data to see if any daily values are out of range. Providing a visual means to identify values that are outside acceptable limits will improve the report’s utility.

To add conditional formatting begin by selecting the appropriate cell(s) then use Format | Conditional Formatting. Note that if your Excel template’s macro automatically copies a named range of formulas depending on the data exported, you only need to add conditional formatting to the cells within the name range being copied. To use values as the formatting criteria, select “Cell Value Is” as the first condition in the Conditional Formatting dialog, choose the appropriate comparison phrase (between, equal to, greater than, etc.) then enter the constant value(s) or formula(s). Now click the [Format] button and select the formatting to apply when the cell value meets the condition or the formula returns True. If necessary, use the [Add] button to add another condition. You can specify up to three conditions.

The screen below shows the results after applying the conditional format shown above to the cells in column C.

To delete conditional formatting, select the cell(s), use Format | Conditional Formatting then click the [Delete] button. Select the condition(s) to delete then click the [OK] button. To find cells with conditional formatting, use Edit | Go To, click the [Special] button then select the Conditional formats option. Use the All option to find any cells with conditional formatting or use the Same option to find cells with the same conditional formatting as the selected cell.

Read more about the capabilities of conditional formatting in Excel’s online help. Add this useful feature to your Excel reports and give your users a new way to experience their data.