Skip to main content
HomeTutorialsData Visualization

Visualizing Data in Excel

Learn about Excel's various data visualization options that can help you analyze and interpret your data.
Apr 2023  · 12 min read

Excel needs no introduction, but many users don’t fully harness the tool's power. In this tutorial, we’ll cover the different visualization options available in Excel to help you analyze and interpret your data.

Defining Data Visualization

We’ll first start by defining what data visualization is. Data visualization is a graphical representation of data. By utilizing charts, graphs, maps, etc., we can provide a simple and accessible way to understand our data and identify trends and outliers within our datasets. Note that Excel uses the term "chart" to mean a "plot". For example, a bar plot is called a bar chart in Excel terminology.

The purpose of this tutorial is to walk you through some basic charts to visualize your data before jumping into more advanced techniques later on. We highly recommend you check out our Data Visualization cheat sheet to learn more about the most common visualizations and when to use them.

Example Dataset

We first need a dataset to work with before creating any visualizations. This tutorial will use a simple dataset containing sales data for a local electronics store. The dataset contains information on the number of units sold for various product types in 2022 and totals for columns and rows.

Month

TVs

Mobile Phones

Laptops

Total

1/1/2022

145

335

82

562

2/1/2022

145

362

126

633

3/1/2022

105

311

95

511

4/1/2022

171

259

93

523

5/1/2022

178

277

107

562

6/1/2022

167

292

145

604

7/1/2022

200

385

77

662

8/1/2022

181

388

78

647

9/1/2022

152

291

83

526

10/1/2022

143

345

102

590

11/1/2022

114

399

99

612

12/1/2022

109

250

101

460

Total

1810

3894

1188

 

We’ll be working with this dataset throughout this tutorial. You can download the data file from GitHub.

Alternatively, you can import the dataset using the following steps:

  • Open Excel and create a new workbook.
  • Copy the dataset above and paste it into cell A1

Format the cells as needed (e.g., adjust column width, apply bold formatting to headers, etc.).

Electronics dataset in Excel

Creating Basic Charts in Excel

Excel has multiple options for choosing a particular chart type. For example, if you want to create a column or bar chart, you are often presented with various visualization options. For example, there are 2D and 3D versions and normal, stacked, and 100% stacked options. Depending on your requirements, you can choose the visualization type that best suits your needs.

An example of the various visualization options under column/bar chart.

Excel bar charts

Bar charts are one of the easiest charts to interpret, enabling the person viewing the chart an easy way to compare categorical data quickly. On a bar chart, the categorical data is on the y-axis, and the values are on the x-axis.

To create a bar chart:

  • Select the data range A1:D13
  • Click the "Insert" tab in the Excel ribbon
  • Click on the columns icon button dropdown, and under the “2-D Bar” category, choose “Clustered Bar”

Bar chart showing sample data

Excel column charts

A column chart, also known as a vertical bar chart, helps visualize data where categories are placed on the x-axis and the values on the y-axis. Similar to bar charts, they help visualize data across categories.

To create a column chart in Excel:

  • Select the data range A1:D13
  • Click the "Insert" tab in the Excel ribbon
  • Click on the columns icon dropdown, and under the “2-D Column” category, choose “Clustered Column”

You can now see a column chart that displays the number of units sold for each product category by the month. 

Column chart showing sample data

Excel line charts

A line chart is the most useful way to capture how a numerical variable changes over time. This is helpful to identify trends in numeric values.

To create a line chart in Excel:

  • Select the data range A1:D13
  • Click the "Insert" tab in the Excel ribbon
  • Click on the line chart dropdown, and under the “2-D Line” category, choose “Line with Markers"

You can now see a line chart displaying units sold each month split by product category. This enables you to compare each product category's performance over time easily. 

Line chart with sample data

Excel pie charts

A pie chart is most commonly used to show the proportions of a whole. It’s like visualizing fractions when you were in high school. With this pie chart, we want to compare the total sales between the three categories.

To create a pie chart in Excel:

  • First, select the data range B1:D1
  • Second, using the command (for Mac) or ctrl (for Windows), select the second date range: B14:D14
  • Click the "Insert" tab in the Excel ribbon
  • Click on the pie chart dropdown, and under the “2-D Pie” category, choose “Pie”

Pie chart with sample data

Advanced Excel Visualization Techniques

Excel scatter plots

A scatter plot is commonly used to visualize the relationship between two variables. It can be useful for quickly surfacing potential correlations between data points. We’ll create a scatter plot to compare the number of TVs and laptops sold.

To create a scatter plot in Excel:

  • Select the data range A1:C13
  • Click the "Insert" tab in the Excel ribbon
  • Click on the scatter plot dropdown, and under the “Scatter” category, choose “Histogram”
  • Click "Scatter or Bubble Chart" and choose "Scatter with Smooth Lines and Markers" 

Scatter chart with sample data

Excel waterfall chart

A waterfall chart is a special chart that helps illustrate how positive and negative values can contribute to a total. They can be great for visualizing changes over time. In our example, we’ll compare the total sales for each month regardless of the categories.

To create a waterfall chart in Excel:

  • First, select the data range A2:A13
  • Second, using the command (for Mac) or ctrl (for Windows), select the second data range E2:E13
  • Click on the waterfall chart dropdown, and under the “Waterfall” category, choose “Waterfall”

We’ll only see positive values in our example because the Total column only contains positive values, but this chart can be great for comparing financial data and changes over time.

Waterfall chart with sample data

Customizing and Formatting Excel Charts

Aside from creating charts in Excel, there are many options to customize and format a chart. From here, we’ll discuss some common formatting options that’ll help you enhance your visuals.

First, create a column chart based on our previous guidance that displays the number of units sold for each product category by the month.

Chart elements

Chart elements include titles, legends, data labels, gridlines, and axes. You can add, remove, or modify these elements as needed.

Click on the chart to select it.

  • In the Excel ribbon, click the "Chart Design" tab
  • Click the "Add Chart Element" dropdown to access the available chart elements

Add Chart Element dropdown menu

Chart title

  • To add a chart title, click "Add Chart Element" > "Chart Title" and choose one of the available options (e.g., "Above Chart" or "Centered Overlay"). You can then click the title placeholder and type your desired title
  • To remove a chart title, right-click on the title and select “Delete”

For this tutorial, rename the chart “Electronic Store Sales 2022”

Legend

  • To modify the chart legend, click "Add Chart Element" > "Legend" and choose a position for the legend (e.g., "Right" or "Top")
  • To remove the legend, click "Add Chart Element" > "Legend" > "None"

For this tutorial, move the legend to the top of the chart.

Data labels

  • To add data labels, click "Add Chart Element" > "Data Labels" and choose one of the available options (e.g., "Center" or "Above"). This will display the data values directly on the chart
  • To remove data labels, click “Add Chart Element” > “Data Labels” > “None”
  • To remove data labels from individual categories, right-click on the data label of the chosen category; this will highlight all relevant data labels. You can then click “Delete”

For this tutorial, add data labels for all categories.

Your chart should now look like this: 

An updated column chart with title, legend, and data labels.

Chart Styles and colors

On top of customizing individual chart elements, you can change your chart's overall look and feel by applying different styles and color schemes.

To customize a chart style, select the visual you want to update. Then take the following steps:

  • In the Excel ribbon, click the "Chart Design" tab
  • Under the “Chart Styles” section, there is an option to change styles and change colors
  • For chart styles: Browse the available styles and click one to apply to your chart
  • For color changes: Click the “Change Colors” dropdown and choose a color scheme

Excel chart-style options

By implementing small visual changes, we can see a big difference in the aesthetic of the whole chart. Here’s an example of how our chart has changed by choosing Style 6 and Monochromatic Palette 8. 

An updated column chart with color gradient categories and a new style

Formatting Excel chart axes

To improve the readability of our chart, we can format our axis in several ways, including axis titles, scale, or even visibility.

Axis titles

  • To add an axis title, click "Add Chart Element" > "Axis Titles" and choose "Primary Horizontal" or "Primary Vertical." You can then click the title placeholder and type your desired title
  • To remove an axis title, right-click on the axis title and click “Delete”

For this tutorial, add an x-axis title “Month” and a y-axis title “Products Sold.”

Axis scale and number format

To adjust the axis scale or number format:

  • Right-click the axis you want to modify and choose "Format Axis"

In the "Format Axis" pane, you can change the minimum and maximum values, major and minor units, or number format.

Format axis panel

For this tutorial, we intend to keep these options the same but feel free to play around and test what each does.

Axis visibility

Finally, if you would like to remove the axis labels from showing, you can take the following steps:

  • In the Excel ribbon, click the "Chart Design" tab
  • Click the "Add Chart Element" dropdown and navigate to "Axes"
  • By default, both options will have a darker gray box surrounding them, to remove an Axes, simply de-select the axes you’d like to remove

Default highlighted axes options

Other Excel formatting options

There are other formatting options available, including modifying data series colors, adjusting chart and plot area backgrounds, and customizing gridlines.

To access these options:

  • Right-click the chart element you want to modify (e.g., data series, plot area, or gridlines)
  • Select "Format [element]"

Formatting gridlines option

Conclusion

In this tutorial, we have covered some of Excel's most common visualization techniques, including basic charts such as columns and bars and adding advanced charts such as histograms and scatter plots. Additionally, we’ve covered how to level up your visualizations by applying customization and formatting. Following these steps, you can create compelling visualizations to analyze and understand your data better.

If you’d like to see what our final visualization could look like, take a peek below:

Final customized column chart

As with anything, practice makes perfect. Experiment with all the available options in Excel. Happy visualizing! If you want to learn more about some of the essential Excel formulas, check out our separate tutorial.

You can also check out two of our courses to learn more; Data Analysis in Excel and Excel and Spreadsheets.

Excel Data Visualization FAQs

What is data visualization in Excel?

Data visualization in Excel refers to the graphical representation of data using various charts, graphs, and other visual elements. It helps users to understand and analyze complex data more effectively by revealing patterns, trends, and relationships within the data.

What types of charts and graphs are available in Excel for data visualization?

Excel offers a wide range of charts and graphs for data visualization, including but not limited to:

  • Column and bar charts
  • Line and area charts
  • Pie and doughnut charts
  • Scatter and bubble plots
  • Radar and waterfall charts
  • Treemaps and sunburst charts
  • Histograms and box plots

Can I create a dynamic chart in Excel that updates automatically as data changes?

Yes, you can create a dynamic chart in Excel by using named ranges or tables for your data source. When new data is added or existing data is updated, the chart will automatically adjust to reflect the changes.

How can I choose the right chart type for my data in Excel?

Choosing the right chart type depends on the type of data you are working with and the insights you want to gain from the visualization. Consider the following guidelines:

  • Use column or bar charts for comparing discrete categories or time periods.
  • Choose line or area charts to display trends over time.
  • Opt for pie or doughnut charts to represent parts of a whole.
  • Select scatter or bubble plots to show the relationship between two or more variables.
  • Use radar or waterfall charts for specialized comparisons and visualizations.
  • Employ treemaps or sunburst charts to display hierarchical data.
Topics
Related

How to Earn a Microsoft Excel Certification in 2024: Top Tips and Resources

Discover step-by-step instructions, exam insights, and expert tips to achieve Excel certification.
Matt Crabtree's photo

Matt Crabtree

12 min

Top 25 Excel Interview Questions For All Levels

A guide to the most common Excel interview questions for beginner, intermediate, and advanced users to ace the technical interview.
Chloe Lubin's photo

Chloe Lubin

17 min

Data Storytelling and Visualization with Lea Pica from Present Beyond Measure

Richie and Lea cover the full picture of data presentation, how to understand your audience, leverage hollywood storytelling and much more.
Richie Cotton's photo

Richie Cotton

71 min

Excel Shortcuts Cheat Sheet

Improve on your Excel skills with the handy shortcuts featured in this convenient cheat sheet!
Richie Cotton's photo

Richie Cotton

4 min

Types of Data Plots and How to Create Them in Python

Explore various types of data plots—from the most common to advanced and unconventional ones—what they show, when to use them, when to avoid them, and how to create and customize them in Python.
Elena Kosourova's photo

Elena Kosourova

21 min

Excel Regex Tutorial: Mastering Pattern Matching with Regular Expressions

Discover the power of Regular Expressions (RegEx) for pattern matching in Excel. Our comprehensive guide unveils how to standardize data, extract keywords, and perform advanced text manipulations.
Chloe Lubin's photo

Chloe Lubin

12 min

See MoreSee More