Pular para o conteúdo principal

# Visualizing Data in Excel

abr. de 2023  · 12 min leer

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.).

## 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.

### 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”

### 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.

### 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.

### 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”

### 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"

### 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.

## 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

#### 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:

### 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

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.

### 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.

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

### 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]"

## 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:

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 our courses to learn more: Data Analysis in Excel or browse all of our Excel courses.

Gain the skills to maximize Excel—no experience required.

## Excel Data Visualization FAQs

### What is data visualization in Excel?.css-18x2vi3{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:rotate(0.5turn) translate(21%, -10%);-moz-transform:rotate(0.5turn) translate(21%, -10%);-ms-transform:rotate(0.5turn) translate(21%, -10%);transform:rotate(0.5turn) translate(21%, -10%);-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

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?.css-167dpqb{-webkit-flex-shrink:0;-ms-flex-negative:0;flex-shrink:0;height:18px;padding-top:6px;-webkit-transform:none;-moz-transform:none;-ms-transform:none;transform:none;-webkit-transition:-webkit-transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);transition:transform 0.3s cubic-bezier(0.85, 0, 0.15, 1);width:18px;}

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
• 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.
Temas

blog

### Excel vs Tableau: Choosing the Right Data Analysis and Visualization Tool

Choose Excel for versatile data processing and comprehensive analysis, and Tableau for advanced visuals and seamless integration with various data sources.

Laiba Siddiqui

11 min

tutorial

In this tutorial, you'll learn how to create visualizations to display data and gain more meaningful insights with spreadsheets.

12 min

tutorial

In this tutorial, you will learn how to analyze and display spreadsheet data using Tableau and make more data-driven decisions.

Parul Pandey

14 min

tutorial

### How to Create a Dashboard in Excel in 3 Easy Steps

Learn everything you need to know about how to create a dashboard in Excel, with tips and examples.

Joleen Bothma

12 min

tutorial

### Data Visualization with Power BI

Learn how to analyze and display data using Power BI and make better, more data-driven decisions.

Parul Pandey

16 min

code-along

### Visualizing Sales Data with PivotCharts in Excel

In this code along, we delve into the world of data analysis and visualization using PivotCharts and PivotTables on a sales dataset.

Agata Bak-Geerinck

See MoreSee More