Quick Tip – Enhancing reports with Data Bars

Friday, September 30, 2016 - By Jon Gardner

..Utilising Excel Conditional Formatting within a Jet Report

Where a picture can paint a thousand words….

There are many types of reports, but typically sales reports, where quantities, volumes or values need to be sorted and ranked from (say) high to low.  Implicit in this type of report are relativities – what percentage of sales do the top x% of customers account for?  How much bigger is one’s top customer in relation to one’s “least best” customer.  Put another way – does Pareto’s Rule apply to one’s customer base?  Should it?  And what impact might this have on the profitability of customers?

On the left we have what might be a typical report covering the customer sales for a period.  Simple, easy to build, and probably imparts just the information that the report sponsor requested.

The report on the right is exactly the same report but with the added visualisation created by using Excel’s Data Bars – part of its Conditional Formatting functionality.  Not only does this provide us with the basic numbers and the sorting but also the relativities between the (clear) top two customers and the tail.  Even without looking at the numbers in detail we have a feel for the situation; the report has immediate impact and tells a story….

How to set up Data Bars in a Jet report

Using Data Bars with a Jet report as outlined here is really easy to do. Because the =NL(Rows) functionality in Jet replicates the entire row: and all the functions, formula, text, colour and formatting, including Conditional Formatting in that first row, gets replicated too.  So once one has the top line correct in Design Mode, the report should simply take care of itself….

In this example one would drop down to Jet’s Design Mode and then copy the Cell I11 into Cell J11 (see below, left), and with the cursor on J11 click on Conditional Formatting in the Styles section of the Home Ribbon Bar.  A drop down list appears from which one should select the Data Bars option – scroll past the solid-fill or gradient-fill options – to the bottom, “More Rules”.

“More Rules” opens up the New Formatting Rule page and one can quickly skip the top part (to select a rule types), and go straight to the lower part.  From here one can choose one’s bar colour – solid-fill or gradient-fill, with border or without, extending in whichever direction….

Which ever selections are made the really important step is the one is to tick the box “Show Bar Only” highlighted in yellow.  Click OK – and then widen the column (Column J in the example) to the desired width, and run the report….

We hope you will find this little tip useful – it does make a difference to your reports.  If you have any questions – or have any further tips of your own – please do not hesitate to contact us.  Our Contact Form can be completed here