Preparing data visualisations with Excel

You have just learned the steps to create a visualisation. This article explains the steps to prepare data visualisations for yourself and so develop the practical skills needed to visualise a data set.

Step 1: Identify your data table

First, organise your data into a table or a form that can be read to build a data analysis and/or a data visualisation.

Step 2: Table headings

The first row of the data table should include data headings.

Step 3: Add missing headers

Ensure that none of the columns with data is without a heading.

Step 4: Remove multiple data headers and merge them into one table

If a column already has a header that conveys the necessary details, then there shouldn’t be another header on the top of it. This could lead to confusion. Although, you may choose to create a new column for that header.

Step 5: Clean any annotations in data

Data should include correct denominations (eg percentages should be clearly indicated as percentages). The same applies for currency, numbers and the number of decimal places.

Step 6: Blank cells

Ensure that blank cells are filled in with correct information.

Step 7: All rows should have the same number of columns

Ensure that your data table has the same number of columns in each row. The data headers should refer to the same type of data across all rows.

Step 8: Check summaries (averages, totals, etc)

Many data tables contain summary rows with the total or average amounts for specific columns. (You would also find descriptive/headers rows merely for layout reasons.)

Step 9: Adjust final data layout

You can use different types of tables such as a simple table, a pivot table, charts, graphs and other tools to visually present your final data.

Below are a couple of helpful tools and videos you can use to familiarise yourself with Microsoft Excel and data visualisation.

Read: [Overview of Excel tables] [1]. (https://support.office.com/en-us/article/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c)

Read: [Create a PivotTable to analyze worksheet data] 2

Step 10: Create a chart in Excel

Do you know how to create a chart in Excel? If not, watch the video below and then follow the steps to create a chart in your Excel sheet.

Watch: Microsoft Excel [3]: Create a chart

You can follow these steps to create a basic chart as provided by Microsoft Office support.

1. Select the data for which you want to create a chart.
2. Click INSERT > Recommended Charts.
3. On the Recommended Charts tab, scroll through the list of charts that Excel recommends for your data, and click any chart to see how your data will look.
4. If you don’t see a chart you like, click All Charts to see all the available chart types.
5. When you find the chart you like, click it, then click OK.
6. Use the Chart Elements, Chart Styles, and Chart Filters buttons, next to the upper-right corner of the chart to add chart elements like axis titles or data labels, customize the look of your chart, or change the data that is shown in the chart.
7. To access additional design and formatting features, click anywhere in the chart to add the CHART TOOLS to the ribbon, and then click the options you want on the DESIGN and FORMAT tabs.

Now that you’ve seen how to create a visualisation in Excel, think about the applications for using Excel for data visualisation in your context. Are there opportunities to use apply visualisations with Excel?

Share your thoughts in the comments.

References:

1. Overview of Excel tables [Internet]. Microsoft; [date unknown]. Available from: https://support.microsoft.com/en-us/office/overview-of-excel-tables-7ab0bb7d-3a9e-4b56-a3c9-6c94334e492c?ui=en-us&rs=en-us&ad=us

2. Create a PivotTable to analyze worksheet data [Internet]. Microsoft; [date unknown]. Available from: https://support.microsoft.com/en-us/office/create-a-pivottable-to-analyze-worksheet-data-a9a84538-bfe9-40a9-a8e9-f99134456576?ui=en-us&rs=en-us&ad=us

3. Create a chart [Internet]. Microsoft; [date unknown]. Available from: https://support.microsoft.com/en-us/office/video-create-a-chart-4d95c6a5-42d2-4cfc-aede-0ebf01d409a8?ui=en-us&rs=en-us&ad=us#ID0EAABAAA=Try-it!