Charts and Dashboards: Dashboard Design – Using Tables and Conditional Formatting

Welcome back to this week’s Charts and Dashboards blog series.  This week, we will talk about using tables and conditional formatting when designing a dashboard.

Dashboards do not necessarily contain only charts.  We can use a tabular report as the source document for a dashboard table or chart component, provided we limit the number of rows returned. 

When using tables for charts or dashboards, it is important that we format the number components properly.  You can read about number formatting and conditional custom number formatting as needed.  Additionally, conditional formatting is the best and simplest choice to highlight data and assist visualisation, explore and analyse data, detect critical issues, and identify patterns / trends.  Conditional formatting is an Excel feature that does allow users to apply formats, such as colours, icons and data bars, to a cell or range of cells, and have that formatting change, depending upon the value of the cell or the value of a corresponding formula.

For example, we have an ‘Order Volume’ data table with order volumes greater than 25 displayed in a different colour:

In this table, the ‘Sales Amount’ points which lie in top 20% are highlighted in red, making them stand out.

In this case, we have the ‘Total Sales’ in a particular month shown in a comparison with the ‘Target sales’ by means of data bars. The bars stretching to the right are blue in colour, indicating positive values, which means ‘Total Sales’ are above the target, and the bars stretching to the left are red in colour indicating negative values, which in contrast, means that ‘Total Sales’ do not meet the target.

Color Scales (sic), like a heat map, are employed to show the value in a cell, relative to the values in the other cells in the selected range, by using the cell shading to display the difference in cell value. In the table below, we can quickly see the months with higher ‘Total Sales’ coloured in a darker shade:

Icon Sets can also be used when describing the movement up, slightly up, slight down and down e.g. in ‘Total Sales’.

Conditional formatting options in Excel are not restricted to built-in options. We can define our own rules and format a range of cells satisfying a particular condition, like in the example below, where both highlighting rules are used to point out the highest sales in a month, and icons are used to get the movement in ‘Total Sales’.

That’s it for this week. Check back next week for more Charts and Dashboards tips.