Skip main navigation

Preparing data visualisations with Excel

Learn how to prepare 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.
Screenshot shows a table in Excel

Step 2: Table headings

The first row of the data table should include data headings.
Screenshot of table headings in Excel

Step 3: Add missing headers

Ensure that none of the columns with data is without a heading.
Screenshot of column headings in Excel

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.
Screenshot of an extra column headings in Excel

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.
Screenshot of blank column cells in Excel

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!
This article is from the free online

Financial Analysis for Business Performance: Reporting and Stakeholder Management

Created by
FutureLearn - Learning For Life

Our purpose is to transform access to education.

We offer a diverse selection of courses from leading universities and cultural institutions from around the world. These are delivered one step at a time, and are accessible on mobile, tablet and desktop, so you can fit learning around your life.

We believe learning should be an enjoyable, social experience, so our courses offer the opportunity to discuss what you’re learning with others as you go, helping you make fresh discoveries and form new ideas.
You can unlock new opportunities with unlimited access to hundreds of online short courses for a year by subscribing to our Unlimited package. Build your knowledge with top universities and organisations.

Learn more about how FutureLearn is transforming access to education

close