Excel Data Visualisation: A Powerful Decision Making Tool

Data visualisation is a powerful tool that allows you to view data more clearly. This is vitally important for businesses to help them with crucial decision making.

Excel Data Visualisation: A Powerful Decision Making Tool

What is data visualisation?

Data visualisation is the process of turning raw data into graphical representations. It works by taking selected data in Excel and then choosing from a variety of different charts the one that is best suited to the data. These charts can make it easy to communicate trends in data and draw conclusions.

How can data visualisation help businesses?

Data visualisation can help businesses make better decisions through identifying key trends, patterns, and outliers in their data. In turn, it can help them communicate insights to stakeholders more effectively.

Tutorial

Charts in Excel can be created in two different ways depending on the complexity of the data. The first method is to make a chart from a simple dataset and is always a good place to start if you have this data already available.

The second method is to convert a more detailed transactional dataset (e.g. daily sales transactions) into a PivotChart (a visual equivalent of a Pivot Table). This method is ideal for converting ‘raw’ data into summary reports and provides more scope for greater insights.

On the other hand, the first method is more suited to data that has already been summarised but is more limited in showing insights. This blog will show both approaches starting with the first method as it is the simpler of the two to master.

Creating a Chart (first method)

Let’s say an Area Manager is tasked with comparing the sales performance of Sales Reps in terms of quantities of products sold. The Excel table below contains the summary totals for each Rep:

Excel Data Visualisation: A Powerful Decision Making Tool

 

1. Provided there are no blank rows or columns

within the dataset, you can just select any cell

in the data and go to INSERT > PIE CHART ICON

Excel Data Visualisation: A Powerful Decision Making Tool

2. Select the 2-D Pie icon

3. The Pie Chart will now appear – see below:

Excel Data Visualisation: A Powerful Decision Making Tool

Editing a Chart

The ‘Chart Title’ and ‘Legend’ (Colour key for a category list) are 2 of the 3 ‘Chart Elements’ that go to make up a Pie Chart. The 3rd element is ‘Data Labels’ which can be displayed using the ‘Chart Elements’ icon: Excel Data Visualisation: A Powerful Decision Making Tool

  1. Click on the icon and tick the box for ‘Data Labels’

Excel Data Visualisation: A Powerful Decision Making Tool

  1. With the ‘Chart Title’ box ticked, select the text ‘Quantity’ and type ‘Total Product Quantity by Sales Rep.’ This is a ‘free’ text box that allows you to enter your own customised text
  2. With the ‘Legend’ selected, hover to the right and you will see a > icon. Select ‘Right’ to move the legend to the right-hand side of the ChartExcel Data Visualisation: A Powerful Decision Making Tool
  3. To add percentages to the Chart, go to the ‘Chart Elements’ icon and then DATA Labels – More Options.Excel Data Visualisation: A Powerful Decision Making Tool
  4. Note a list of tick boxes to display different ‘Label Options’. Tick ‘Percentage’ and untick ‘Value’ in the list

The result of all these changes can be seen below:

Excel Data Visualisation: A Powerful Decision Making Tool

Creating a PivotChart (second method)

The same Area Manager now wants to see more details in the Sales Reps’ performance e.g. Sales broken down by Individual Rep and Product. As the original dataset used in the first method only contains quantities by Rep, the Area Manager will need another more detailed set of data – a section of the data is shown below:

Excel Data Visualisation: A Powerful Decision Making Tool

Ensure the data has headings for each column and there are no blank rows or columns within the data itself.

    1. Select the PivotChart and go to PIVOTCHART ANALYSE > INSERT SLICER and tick for Product Description Select any cell within the data and go to INSERT > PIVOTCHART > PIVOTCHART (sub-list) The following dialog box will appear:Excel Data Visualisation: A Powerful Decision Making Tool
      Ensure all data is referenced in the ‘Table/Range’ field and then click OK. This action creates a new sheet with 3 objects: PivotChart, Pivot Table and PivotChart Fields pane:Excel Data Visualisation: A Powerful Decision Making Tool
      Think of the PivotChart Fields pane as the ‘control panel’ used to build the PivotChart and PivotTable
    2. In the Field List, tick the fields for Rep_LastName and Quantity to produce the following visuals:
      Excel Data Visualisation: A Powerful Decision Making Tool
      Notice that the default settings are to Sum the Quantities and create a ‘Column Chart.’
    3. To change it to a Pie Chart, select the chart and go to DESIGN > CHANGE CHART TYPE, select the Pie Chart option and click OK.
    4. Add Data labels and set them to Percentage as covered in the second method.
    5. To analyse sales broken down by Product, you can create a slicer (visual filter):
      Select the PivotChart and go to PIVOTCHART ANALYSE > INSERT                          SLICER and tick for Product Description
      Excel Data Visualisation: A Powerful Decision Making Tool
    6. Now click on an individual product (e.g. backpack) and notice how it interacts with the PivotChart and PivotTable:

Excel Data Visualisation: A Powerful Decision Making Tool

Industry sector examples

Examples of data visualisation from other industries could include:

      • HR Managers analysing number of sick days across departments
      • Data Analysts in healthcare checking patient attendance for different types of medical treatment
      • Events Coordinators tracking cost/revenue of events on a monthly basis
      • Customer Service Managers analysing call volume, call times and customer satisfaction scores generated from helpdesk data

Conclusion

Data visualisation is key to understanding your data better and can be applied using one of the methods described. If detailed data is available, the second method, “PivotChart” is likely to give you greater insights into your data. Many other industries (not just sales related) can benefit from this approach. So why not try out these methods on your own data and see how this extremely powerful tool can help you gain a competitive edge within your business.

Further Reading

Excel Or Power BI – Which is Better for Business Reporting?

7 Essential Excel Tricks Every Office Worker Needs to Know

How to Use Sparklines in Excel to Visualise Data Trends

Sparklines are a powerful tool in Excel that allow you to visualise data trends in a condensed chart format. Unlike normal Excel charts, Sparklines are embedded in a single cell, making them ideal for displaying trends in sales figures, website hits, and other data over a period of time.

In this blog post, we’ll show you how to create and customise Sparklines in Excel, and provide some examples of how they can be used in different business roles and industries.

For instance, sales managers can use Sparklines to track sales figures over time, while marketing professionals can use them to monitor website traffic. Sparklines can also be used in finance to track stock prices or in healthcare to monitor patient vitals.

There are 3 types of Sparkline available, “line”, “column” and “Win/Loss”:

How to Use Sparklines in Excel to Visualise Data Trends

In the following example we see how a sales manager can quickly create Sparklines to easily analyse and compare performance across multiple sales regions.

Creating a Sparkline

Unlike a normal Excel chart that ‘floats’ on top of the spreadsheet, a Sparkline is embedded in a single cell. Create a Sparkline for Region A figures Jan-Oct as follows:

  1. Select a cell to the right of the data you want to visualise – see below:

How to Use Sparklines in Excel to Visualise Data Trends

2. Go to INSERT > SPARKLINES > LINE

3. In the ‘Data Range’ field, select all the cells containing numbers to the left of the selected cell. Click OK to display the following Sparkline

How to Use Sparklines in Excel to Visualise Data Trends

Editing a Sparkline

  1. To see each point of the Sparkline more clearly, go to SPARKLINE > SHOW > HIGH POINT.

Note the same values of 28 for both March and July give rise to 2 red markers on the Sparkline.

How to Use Sparklines in Excel to Visualise Data Trends

Reducing the July figure to 26 would result in just one marker for March – see below:

How to Use Sparklines in Excel to Visualise Data Trends

2. Reducing the July figure to 26 would result in just one marker for March – see below:

How to Use Sparklines in Excel to Visualise Data Trends

3. Now change the August figure to -5 and then change the type to WIN/LOSS

4. Untick ‘High Point’ and tick ‘Negative Point’

5. Select a different Sparkline style to reflect RAG status colours (Red, Amber, Green) i.e. Red (negative values) and Green (positive values)

Note the main purpose of the WIN/LOSS Sparkline is to show which values are positive or negative – see below.

How to Use Sparklines in Excel to Visualise Data Trends

6. Change the type back to the default ‘Sparkline’ and set the following:

a. Tick ‘High point’ and ‘Low point’

b. Untick ‘Negative points’

c. Sparkline colour to blue.

Copying a Sparkline

Once you have created a Sparkline, all its attributes can be copied down to represent other rows of data:

How to Use Sparklines in Excel to Visualise Data Trends

The result is shown below:

How to Use Sparklines in Excel to Visualise Data Trends

2. To edit the Sparklines, select any one within a single cell, do the edits and all of them will be changed as a group

3. If you want to change one specific Sparkline without affecting the others, go to SPARKLINE > GROUP > UNGROUP

Deleting Sparklines

Unlike for normal charts and data generally, the delete button will not work for deleting Sparklines.

Select the Sparklines and go to SPARKLINES > GROUP > CLEAR > CLEAR SELECTED SPARKLINES

Conclusion

In conclusion, Sparklines are a great way to visualise data trends in Excel, and can be used in a variety of business roles and industries. By following the steps outlined in this blog post, you can create and customise Sparklines to better understand your data and make more informed decisions.