Data Visualization in Excel Tutorial

What is data visualization?

Data visualization is the presentation of data (both qualitative and quantitative data) in graphical format. In Excel, charts and graphs are used to make a visual representation of data.

 

Benefits of data visualization

Through data visualization you can easily:

  1. Visualize data (make sense of data, especially big data)
  2. Classify and categorize data
  3. Find a relationship among data
  4. Understand the composition of data
  5. Understand the distribution of data
  6. Understand the overlapping of data
  7. Determine patterns and trends
  8. Detect outliers and other anomalies in data
  9. Predict future trends
  10. Tell meaningful and engaging stories to decision-makers

Data presentation is a very important skill for an optimizer (marketer, analyst). In fact, it is so valuable that LinkedIn lists it as one of the top skills that can get you hired.

Excel charts are commonly used for data visualization and presentation. But selecting the right Excel chart is always a challenge.

If you use an incorrect Excel chart for your analysis, you may misinterpret data and make the wrong business and marketing decisions.

If you use an incorrect Excel chart for your presentation, then stakeholders may misinterpret your charts and take wrong decisions. Therefore selecting the right Excel chart is critically important.

Get the E-Book (40 Pages)

 

Most common data types that can be visualized

Following are the most common data types that can be visualized:

#1 Quantitative data (also known as interval/ratio data) is the data that can be measured.

For example 10 customers, sales, ROI, weight, etc.

#2 Qualitative data is the data that can be classified/categorized but it can not be measured.

For example colors, satisfaction, rankings, etc.

#3 Discrete data – quantitative data with a finite number of values/observations.

For example 5 customers, 17 points, 12 steps, etc.

#4 Continuous data – quantitative data with value / observation within a range/interval.

For example sales in the last year.

#5 Nominal data – qualitative data that can not be put into a meaningful order (i.e. ranked).

For example {Blue, Yellow, Green, Red, Black}

#6 Ordinal data – qualitative data that can be put into a meaningful order (i.e. ranked).

For example, {Very Satisfied, Satisfied, Unsatisfied, very unsatisfied} or {Strong dislike, dislike, neutral, like, strong like}

 

The anatomy of an Excel chart

In order to read an Excel chart, it is important that you understand the various components of the chart.

Consider the following data table in Excel:

This data table has got five variables: ‘Month’, ‘Sales’, ‘Cost’, ‘Profit’, and ‘ROI’:

This data table is made up of categories and data series:

Categories – Here the first category is ‘Jan’, the second category is ‘Feb’, the third category is ‘Mar’ and so on.

Data series – A data series is a set of related data points.

Data point – Data point represents an individual unit of data. 10, 20, 30, 40, etc are examples of data points. In the context of charts, a data point represents a mark on a chart:

Consider the following Excel chart which is made from the data table mentioned earlier:

anatomy of Excel chart

This chart is made up of the following chart elements:

anatomy of Excel chart2

  1. Primary Horizontal Axis:
  2. Primary Vertical Axis
  3. Secondary Vertical Axis
  4. Primary Horizontal Axis Title
  5. Primary Vertical Axis Title
  6. Secondary Vertical Axis Title
  7. Chart Title
  8. Data Labels
  9. Gridlines
  10. Legend
  11. Trendline

In Excel, categories are plotted on the horizontal axis and data series are plotted on the vertical axis:

categories data series

From the chart above, we can conclude the following:

  • Months are plotted on the primary horizontal axis.
  • Sales, cost, and profit are plotted on the primary vertical axis.
  • ROI is plotted on the secondary vertical axis.

 

Following are examples of other Excel chart elements:

  1. Data Table with legend keys
  2. Data Table with no legend keys
  3. Error bars (Standard Error)
  4. Error bars (Percentage)
  5. Error bars (Standard Deviation)
  6. Primary Major Horizontal Gridlines
  7. Primary Major Vertical Gridlines
  8. Primary Minor Horizontal Gridlines
  9. Primary Minor Vertical Gridlines
  10. Linear Trendline
  11. Exponential Trendline
  12. Linear Forecast Trendline
  13. Moving Average Trendline

#1 Data table with legend keys

 

#2 Data table with no legend keys

 

#3 Error bars (standard error)

If you want to see the margin of error as a standard error amount than use the standard error bar.

About Error Bar

An error bar is a line through a point on a graph, parallel to one of the axes, which can help you see margins of error at a glance.

 

#4 Error bars (percentage)

If you want to see the margin of error as a percentage than use the percentage error bar.

 

#5 Error bars (standard deviation)

If you want to see the margin of error as a standard deviation than use the standard deviation error bar.

 

#6 Primary major horizontal gridlines

About Chart Gridlines

Chart gridlines are the faint lines that appear on the plot area of a chart. They are used to make the data in a chart that displays axes easier to read. They can appear both horizontal and vertical.

 

#7 Primary major vertical gridlines

 

#8 Primary minor horizontal gridlines

 

#9 Primary minor vertical gridlines

 

#10 Linear trendline

Use the Linear trendline if your data set is linear (resembles a straight line) and the data values are increasing or decreasing at a steady rate.

About Trendlines

The trendlines are used to graphically display trends in data. A trend is a movement in a particular direction.

A trend can be short (or seasonal), intermediate, or long term. Longer the trend more significant it is. For example, a 3 months trend is not as significant as 3 years trend.

 

#11 Exponential trendline

Use the exponential trendline if data values increase or decrease at increasingly higher rates.

 

#12 Linear forecast trendline

Use the Linear forecast trendline if your data set is linear (resembles a straight line), the data values are increasing or decreasing at a steady rate and you want to forecast the data.

 

#13 Moving average trendline

Use the moving average trendline if there is a lot of fluctuation in your data.

 

How to add a chart to an Excel spreadsheet

In order to add a chart in Excel spreadsheet, follow the steps below:

Step-1: Open MS Excel and navigate to the spreadsheet which contains the data table you want to use for creating a chart.

Step-2: Select data for the chart:

Step-3: Click on the ‘Insert’ tab:

Step-4: Click on the ‘Recommended Charts’ button:

Step-5: Select the chart you want to use from the ‘Insert chart’ dialog box and then click on the ‘ok’ button:

You should now be able to see the chosen chart inserted in your spreadsheet:

 

How to add, change, or remove a chart element

In order to add, change or remove a chart element in Excel (2013 or above), follow the steps below:

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart and then from the ”Design‘ tab click on the ‘Add Chart Element‘ drop-down menu:

 

Step-3: Select the chart element you want to add, change or remove from one of the drop-down menus. For example, if you want to add a data table in your chart then click on ‘Data Table‘ > ‘With legend Keys‘:

data table with legend keys

You can now see your chart along with the data table:

 

How to add a trendline to a chart

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart and then from the ”Design‘ tab click on the ‘Add Chart Element‘ drop-down menu.

Step-3: Click on the ‘Trendline’ drop-down menu and then select the type of trendline you want to add to your chart:

 

How to change the color or style of a chart

Step-1: Open MS Excel and navigate to the spreadsheet which contains the chart you want to edit.

Step-2: Select the chart and then from the ”Design‘ tab click on the ‘Change Colors‘ drop-down menu to the change the colors used in your chart:

If you want to change the style/design of the chart then click on one the styles under the ‘Design’ tab:

 

How to build data visualizations in Excel

You can build a data visualization in excel through the following charts and graphs:

  1. Clustered column chart
  2. Combination chart
  3. Stacked column chart
  4. 100% stacked column chart
  5. Bar chart
  6. Line chart
  7. Number chart
  8. Gauge chart (Speedometer chart)
  9. Pie chart
  10. Stacked area chart
  11. Venn diagram
  12. Scatter chart
  13. Histogram
  14. Actual vs. target chart
  15. Bullet chart
  16. Funnel chart

Other articles on Excel charts

   

EXCLUSIVE FREE TRAINING REVEALS....

"How to use Digital Analytics to generate floods of new Sales and Customers without spending years figuring everything out on your own."


(even if you are completely new to analytics)


Here’s what we’re going to cover…

#1 Why digital analytics is the key to online business success.

​#2 The number 1 reason why most marketers are not able to scale their advertising and maximize sales.

#3 Why Google and Facebook ads don’t work for most businesses & how to make them work.

#4 ​Why you won’t get any competitive advantage in the marketplace just by knowing Google Analytics.

​#5 The number 1 reason why conversion optimization is not working for your business.

​#6 How to advertise on any marketing platform for FREE with an unlimited budget.

​#7 How to learn and master digital analytics and conversion optimization in record time.

 
   

My best selling books on Digital Analytics and Conversion Optimization

Maths and Stats for Web Analytics and Conversion Optimization
This expert guide will teach you how to leverage the knowledge of maths and statistics in order to accurately interpret data and take actions, which can quickly improve the bottom-line of your online business.

Master the Essentials of Email Marketing Analytics
This book focuses solely on the ‘analytics’ that power your email marketing optimization program and will help you dramatically reduce your cost per acquisition and increase marketing ROI by tracking the performance of the various KPIs and metrics used for email marketing.

Attribution Modelling in Google Analytics and Beyond
Attribution modelling is the process of determining the most effective marketing channels for investment. This book has been written to help you implement attribution modelling. It will teach you how to leverage the knowledge of attribution modelling in order to allocate marketing budget and understand buying behaviour.

Attribution Modelling in Google Ads and Facebook
This book has been written to help you implement attribution modelling in Google Ads (Google AdWords) and Facebook. It will teach you, how to leverage the knowledge of attribution modelling in order to understand the customer purchasing journey and determine the most effective marketing channels for investment.

Himanshu Sharma

Digital Marketing Consultant and Founder of Optimizesmart.com

Himanshu helps business owners and marketing professionals in generating more sales and ROI by fixing their website tracking issues, helping them understand their true customers' purchase journey and helping them determine the most effective marketing channels for investment.

He has over 12 years of experience in digital analytics and digital marketing.

He was nominated for the Digital Analytics Association's Awards for Excellence. The Digital Analytics Association is a world-renowned not-for-profit association that helps organisations overcome the challenges of data acquisition and application.

He is the author of four best-selling books on analytics and conversion optimization:

error: Alert: Content is protected !!