{"id":6933,"date":"2024-01-15T15:58:53","date_gmt":"2024-01-15T15:58:53","guid":{"rendered":"https:\/\/www.stl-training.co.uk\/b\/?p=6933"},"modified":"2024-01-15T15:58:53","modified_gmt":"2024-01-15T15:58:53","slug":"excel-data-visuals-decision-making","status":"publish","type":"post","link":"https:\/\/www.stl-training.co.uk\/b\/excel-data-visuals-decision-making\/","title":{"rendered":"Excel Data Visualisation: A Powerful Decision Making Tool"},"content":{"rendered":"

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

\"Excel<\/p>\n

What is data visualisation?<\/h2>\n

Data visualisation is the process of turning raw data into graphical representations. It works by taking selected data in Excel<\/a> 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.<\/p>\n

How can data visualisation help businesses?<\/h2>\n

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.<\/p>\n

Tutorial<\/h2>\n

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.<\/p>\n

The second\u00a0method 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 \u2018raw\u2019 data into summary reports and provides more scope for greater insights.<\/p>\n

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\u00a0method as it is the simpler of the two to master.<\/p>\n

Creating a Chart (first method)<\/h2>\n

Let\u2019s 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:<\/p>\n

\"Excel<\/p>\n

 <\/p>\n

1. Provided there are no blank rows or columns<\/p>\n

within the dataset, you can just select any cell<\/p>\n

in the data and go to INSERT > PIE CHART ICON<\/p>\n

\"Excel<\/p>\n

2. Select the 2-D Pie icon<\/p>\n

3. The Pie Chart will now appear \u2013 see below:<\/p>\n

\"Excel<\/p>\n

Editing a Chart<\/strong><\/p>\n

The \u2018Chart Title\u2019 and \u2018Legend\u2019 (Colour key for a category list) are 2 of the 3 \u2018Chart Elements\u2019 that go to make up a Pie Chart. The 3rd<\/sup> element is \u2018Data Labels\u2019 which can be displayed using the \u2018Chart Elements\u2019 icon: \"Excel<\/p>\n

    \n
  1. Click on the icon and tick the box for \u2018Data Labels\u2019<\/li>\n<\/ol>\n

    \"Excel<\/p>\n

      \n
    1. With the \u2018Chart Title\u2019 box ticked, select the text \u2018Quantity\u2019 and type \u2018Total Product Quantity by Sales Rep.\u2019 This is a \u2018free\u2019 text box that allows you to enter your own customised text<\/li>\n
    2. With the \u2018Legend\u2019 selected, hover to the right and you will see a > icon. Select \u2018Right\u2019 to move the legend to the right-hand side of the Chart\"Excel<\/li>\n
    3. To add percentages to the Chart, go to the \u2018Chart Elements\u2019 icon and then DATA Labels – More Options.\"Excel<\/li>\n
    4. Note a list of tick boxes to display different \u2018Label Options\u2019. Tick \u2018Percentage\u2019 and untick \u2018Value\u2019 in the list<\/li>\n<\/ol>\n

      The result of all these changes can be seen below:<\/p>\n

      \"Excel<\/p>\n

      Creating a PivotChart (second\u00a0method)<\/h2>\n

      The same Area Manager now wants to see more details in the Sales Reps\u2019 performance e.g. Sales broken down by Individual Rep and Product. As the original dataset used in the first\u00a0method only contains quantities by Rep, the Area Manager will need another more detailed set of data \u2013 a section of the data is shown below:<\/p>\n

      \"Excel<\/p>\n

      Ensure the data has headings for each column and there are no blank rows or columns within the data itself.<\/p>\n

        \n
      1. \n
          \n
        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
          \nEnsure all data is referenced in the \u2018Table\/Range\u2019 field and then click OK. This action creates a new sheet with 3 objects: PivotChart, Pivot Table and PivotChart Fields pane:\"Excel
          \nThink of the PivotChart Fields pane as the \u2018control panel\u2019 used to build the PivotChart and PivotTable<\/li>\n
        2. In the Field List, tick the fields for Rep_LastName and Quantity to produce the following visuals:
          \n\"Excel
          \nNotice that the default settings are to Sum the Quantities and create a \u2018Column Chart.\u2019<\/li>\n
        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.<\/li>\n
        4. Add Data labels and set them to Percentage as covered in the second<\/span> method.<\/span><\/span><\/li>\n
        5. To analyse sales broken down by Product, you can create a slicer (visual filter):
          \nSelect the PivotChart and go to PIVOTCHART ANALYSE > INSERT\u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 SLICER and tick for Product Description
          \n\"Excel<\/li>\n
        6. Now click on an individual product (e.g. backpack) and notice how it interacts with the PivotChart and PivotTable:<\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

          \"Excel<\/p>\n

          Industry sector examples<\/h3>\n

          Examples of data visualisation from other industries could include:<\/p>\n

            \n
          1. \n
              \n
            1. \n