data representation excel

Data Analysis and Visualization with Excel (A Complete Guideline)

In this article, we will show you how to perform data analysis and visualization with Excel. There are three stages in data analysis and visualization. To begin, we will show you how to do data processing. Then, we will describe how to analyze data with various Excel features and functions. We will explore pivot tables, data sorting, data validation, and many more techniques. At the end, we will explore how to visualize data with different useful charts.

Data analysis and visualization with Excel is very significant for gaining valuable insights and understanding patterns and outliers of a dataset.

Download Practice Workbook

You can download this practice workbook while going through the article.

What is Data Analysis and Visualization?

Data Analysis is a process of collecting, organizing, and analyzing raw data to find relevant information. This information is vital to make data driven decisions. By doing data analysis, we can get valuable insights into the dataset and ensure accuracy in our decision making.

On the other hand, visualization is basically the graphical representation of the dataset. Different types of charts can help us to understand our data points and identify patterns, trends, and outliers of the dataset. Visualization makes our raw data more comprehensible and accessible.

Excel comes with many powerful features and functions for data analysis and visualization.

Steps of Data Analysis and Visualization with Excel

When it comes to data analysis and visualization with Excel, we will use the following steps.

1. Data Processing 2. Data Analysis 3. Data Visualization

We will explore these steps at length in the following sections.

1. Data Processing

In this section, we will learn about data processing. This is the first step of data analysis and visualization. It has the following segments.

1.1. Data Collection

First, you have to collect information. The information should be collected in a systematic way from a trustworthy source. The source can be any database, online platform, or any web page. Then this data needs to be further processed.

1.2. Data Organization

After collecting the data, the next step is to organize the data in such a way that it is easily understandable to people. The data can be arranged in a tabular form with a clear title and headings.

1.3. Data Cleaning

The organized data may require further cleaning. It may include blank spaces, unwanted empty cells, and duplicate values. So, data cleaning is basically making the dataset accurate and errorless.

Here, we have processed our dataset accordingly. The dataset contains the sales quantity of products such as mobiles, laptops, and televisions of a company. The sales quantity is provided monthly basis. We will use this dataset for our data analysis and visualization with Excel.

2. Data Analysis

There are many techniques for data analysis in Excel. We will show you these techniques one by one.

2.1. Use Excel Functions

There are numerous functions available in Excel for data analysis. Let us walk you through some useful functions and their applications.

2.1.1. Use SUM Function

We can use the SUM function to find out the total number of mobile, laptop, and television sold in a year.

Go to cell C17 and put the following formula.
Select cell C17 and use Fill Handle to AutoFill data in range D17:E17 .

2.1.2. Use INDEX & MATCH Functions

You can use the combination of INDEX & MATCH functions to find out the sales quantity of a particular product for any month.

Go to cell G5 and type the name of any month there.
Go to cell H5 and put the following formula.
You will see the sales quantity of mobiles for that particular month.
You can change the month name in cell G5 and the sales quantity of mobile will be shown in cell H5 .

Formula Breakdown

MATCH(G5,B5:B16,0) : The MATCH function searches for the value of cell G5 within a specified range B5:B16 . The 0 indicates that it must be an exact match. Result : 7

2.1.3. Use VLOOKUP & MATCH Functions

You can use the combination of VLOOKUP & MATCH functions to get the sales quantity of any product for any month.

Go to cell H4 and put the name of any month there.
Go to cell H5 and put the product name there.
Go to cell H6 and put the following formula.

You can see the sales quantity of the product for the given month in cell H6 .

MATCH(H5,B4:E4,0) : The MATCH function searches for the value of cell H5 within a specified range B4:E4 . The 0 indicates that it must be an exact match. Result : 3

2.1.4. Combine SUM & SUMIFS Functions

We can combine the SUM & SUMIFS functions to get the number of products sold for any month.

Go to cell G5 and put the name of the month.
You will see the total number of products for that month.

SUMIFS(C5:C16,B5:B16,G5) : The SUMIFS function sums the value of range C5:C16 but it only includes those values where the corresponding cells of range B5:B16 match the value of cell G5 . Result : 125

SUMIFS(D5:D16,B5:B16,G5) : The SUMIFS function sums the value of range D5:D16 but it only includes those values where the corresponding cells of range B5:B16 match the value of cell G5 . Result : 272

SUMIFS(E5:E16,B5:B16,G5) : The SUMIFS function sums the value of range E5:E16 but it only includes those values where the corresponding cells of range B5:B16 match the value of cell G5 . Result : 147

2.1.5. Apply MAX, INDEX & MATCH Functions

You can apply the MAX function to get the most quantity sold in a month for a particular product. Then you can use the INDEX & MATCH functions to find out the month in which the product was sold the most.

Go to cell G6 .
Put the following formula to find the maximum number of laptops sold in a month.

Then go to cell H6 .
Put the following formula to find the name of that month.

2.2. Apply Conditional Formatting

You can apply conditional formatting to understand the relative quantity of individual products sold over the span of 12 months.

Select range C5:C16 .
Go to the Home tab >> select Conditional Formatting >> Data Bars .
Now select any fill color to highlight the cells.

Do the same steps for range D5:D16 and range E5:E16 as well.

2.3. Use Pivot Table

Go to the Insert tab >> Pivot Table >> From Table/Range .

A dialog box will appear. Set the dialog box as shown below.
Put the following range as the Table/Range .
Set location of the pivot table in cell G4 of the Existing Worksheet and press OK .

Now set the PivotTable Fields as shown below. Drag the fields in different areas.

You will have the pivot table with the grand total of the products.

2.4. Sort a Product

You can sort any product in ascending or descending order. We will sort the dataset in descending order with respect to the Mobile column .

Go to the Home tab >> Sort & Filter >> Sort Largest to Smallest .

Select Expand the selection >> press Sort .

The dataset will be sorted according to the sales quantity of mobile phones in descending order.

2.5. Filter Data

Select range B4:E16 .
Go to the Home tab >> Sort & Filter >> Filter .

You will have the filter icons in all the columns.
Select the filter icon of the Month column .
Choose the month you want to see and click on OK .

You will see the filtered data of that month only.

2.6. Use What-If Analysis Feature

Excel comes with a strong feature named What-If Analysis . It is a combination of techniques and tools that help you to predict the impact on the results of your formulas and models. It comes with the following features.

Goal Seek: This feature predicts the value of a parameter to achieve a specific output. You need to specify the target value and Excel will determine the input value in such a way that it reaches the desired outcome.

Scenario Manager: In this feature, you can define different scenarios for different inputs. You can switch between the inputs to see how the output varies.

Data Table: It allows you to create a table and evaluate multiple results based on input values. You can use these tables with one or two variables to see how different inputs affect the output.

We will show you an example of the Goal Seek feature. The total units of television sold is 1564 . We want to generate a revenue of $1564000 . We will find the unit price of televisions required to reach this revenue.

Put the following formula in cell H7 .
Go to the Data tab >> What-If Analysis >> Goal Seek .

Set the Goal Seek dialog box as shown below.

Apply Goal Seek Analysis to Get Unit Price

You will get the unit price in cell H6 that is required to reach desired revenue which is $1564000 .

Unit Price Obtained from Goal Seek Analysis

2.7. Apply Data Validation

You can use data validation to choose any month from a given list. Here, we will find the sales quantity of laptops for any month.

Select cell G5 .
Go to the Data tab >> select Data Validation .

Data Validation dialog box will appear.
Choose List in the Validation criteria .
Set Source location as follows.

You will have a dropdown in cell G5 .
Select any month from the dropdown list.

List Created by Data Validation with Excel

You will see the sales quantity of laptops for that month.

INDEX & MATCH Functions Applied in Excel

2.8. Use Excel Table

We can create a table to filter data, automate formulas and find parameters like sum, average, maximum, minimum, and so on.

Select range B4:C16 .
Go to the Insert tab >> Table .

Check the My table has headers option.

The table will be created.
Select Total Row from the Table Design tab.

You will see the total number of televisions in cell C17 .

You will also have a drop-down in cell C17 . From there you can choose any parameter you want to see.

2.9. Use Analyze Data Feature

Go to the Home tab >> Analyze Data .
You will find different options like pivot tables and charts to analyze your dataset.

If you do not see the Analyze Data option in your Home tab, you have to customize it.

Right-click on the Home tab >> select Customize Ribbon .
Add a New Group >> set its position >> select All Commands >> find Analyze Data from the list >> Add this to the newly created group >> click OK .

2.10. Use Analysis ToolPak Add-in

You can perform a wide range of analysis using the Analysis ToolPak add-in. Follow the steps below to activate it.

Go to the File tab.
Select Options .
Excel Options dialog box will appear.
Select Add-ins >> Excel Add-ins in the Manage field >> Press Go .

Check the option Analysis ToolPak >> click OK .

2.10.1. Analyze Descriptive Statistics

Now we will do some analysis with the sales quantity of laptops.

Go to the Data tab >> Data Analysis .

Data Analysis dialog box will appear.
Select Descriptive Statistics >> press OK .

Descriptive Statistics dialog box will appear.
Set the input range, output range, and other properties as shown below.

You will see the descriptive statistics of laptop in your worksheet.

2.10.2. Analyze Anove: Single Factor

Now we will talk about the Anova analysis. Anova stands for Analysis of Variance . It is a statistical method to compare the means of two or more groups and determine if there is any significant difference between the groups.

The Data Analysis dialog box will appear.
Select Anova: Single Factor >> press OK .

Anova: Single Factor window will appear.

You will find the analysis in your worksheet.

3. Data Visualization

After data analysis, we will try to visualize our dataset. Data visualization is the way to understand the pattern of the dataset. It also helps us to identify the outliers.

3.1. Excel Column Chart

Go to the Insert tab >> click on the columns icon drop-down >> select a suitable column chart.

You will have the column chart in your dataset. Give it a suitable title.

3.2. Excel Bar Chart

Go to the Insert tab >> click on the columns icon drop-down >> select a suitable bar chart.

You will have the bar chart in your dataset. Give it a suitable title.

3.3. Excel Line Chart

Go to the Insert tab >> click on the line chart icon drop-down.
Select Line with Markers under the 2-D Line category.

You will have the line chart in your dataset. Give it a suitable title.

3.4. Excel Pie Chart

Go to the Insert tab >> click on the pie chart icon drop-down >> select an appropriate 2-D or 3-D pie chart.

You will see the pie chart showing the sales quantity of mobiles in your worksheet.

3.5. Excel Doughnut Chart

Select range B4:B16 >> press the Ctrl button >> select range D4:D16 .
Go to the Insert tab >> click on the pie chart icon drop-down >> select the chart under the Doughnut category.

You will see the doughnut chart showing the sales quantity of laptops in your worksheet.

Advanced Data Visualization

1. excel waterfall chart.

A waterfall chart is a unique chart that shows how positive and negative values contribute to the total. With waterfall charts, it is easy to understand the variation of values over a period of time. We will show you a waterfall chart for the sales quantity of televisions.

Select range B4:B16 >> press the Ctrl button >> select range E4:E16 .
Go to the Insert tab >> click on the waterfall chart drop-down >> select the chart under the Waterfall category.

You will see the waterfall chart showing the sales quantity of televisions.

2. Animated Column Chart

Animated column chart displays data dynamically with animation effects. It does not create a static chart but rather the chart elements change or update over time, making it easier for you to understand the trend or pattern or the data points.

Go to the Animated Chart worksheet.
Go to the Developer tab >> Visual Basic .

Select Insert >> Module .

Paste the following code in your VBA Macro Editor .
Press the Run button or F5 key to run the code.

VBA Code to Create Animated Column Chart

You will see the animated column chart in the Animated Chart worksheet.

VBA Breakdown

These nested loops go through and copy values from myRng range to myArr array, one by one.
These lines select cell G4 in the Animated Chart worksheet and add a new chart to the sheet.
This line sets the chart type to a clustered column chart.
These lines activate the chart object, cut, and paste it into the Animated Chart worksheet.
These nested loops iterate through each cell in the myRng range and set its value from the myArr array. After setting each value, the DoEvents function allows the system to process events and update the screen to show the changes. Then, there is a time delay specified by the delay_time variable before moving to the next row. This is how animated chart effect is created.

Things to Remember

There are a few things to remember in data analysis and visualization with Excel.

Instead of typing from a list, use Data Validation to ensure accurate data entry.
Refresh your pivot table whenever you update your dataset.
While using Data Analysis add-in, be careful with the selection of your input and output range.

Frequently Asked Questions

1. Why should I use Anova? Anova is used to compare the trend difference between multiple datasets. It shows the variation of averages between multiple groups, how categorical variables affect outcome, and analyzes the validity of the given dataset. So, it is very useful for data analysis.

2. What is the difference between inferential and descriptive statistics? Inferential statistics make predictions about a large group with a relatively smaller dataset. On the other hand, descriptive statistics describe various parameters of a dataset by summarizing it.

3. What is the advantage of using the Analyze Data feature? Analyze Data feature comes with a lot of useful functions and tools, customizable capabilities, user friendly interface, and automated calculation techniques. It is much more flexible and far better than manual data analysis techniques.

In this article, we have explored data analysis and visualization with Excel in detail. By mastering these techniques, you can confidently analyze and visualize your data. If you have any questions regarding this essay, don’t hesitate to let us know in the comments. Also, if you want to see more Excel content like this, please visit our website, and unlock a great resource for Excel-related content.

What is ExcelDemy?

Tags: Visualisation in Excel

Md. Abu Sina Ibne Albaruni holds a BSc in Mechanical Engineering from Bangladesh University of Engineering and Technology. He is a dedicated employee of the ExcelDemy project. He has written quite a large number of articles for ExcelDemy. Besides, he has expertise in VBA. He efficiently automates Excel issues using VBA macros and actively engages in the ExcelDemy forum, offering valuable solutions for user interface challenges. His areas of interest in work and study span MATLAB, Machine Learning,... Read Full Bio

Business Insights

Harvard Business School Online's Business Insights Blog provides the career insights you need to achieve your goals and gain confidence in your business skills.

Career Development
Communication
Decision-Making
Earning Your MBA
Negotiation
News & Events
Productivity
Staff Spotlight
Student Profiles
Work-Life Balance
AI Essentials for Business
Alternative Investments
Business Analytics
Business Strategy
Business and Climate Change
Design Thinking and Innovation
Digital Marketing Strategy
Disruptive Strategy
Economics for Managers
Entrepreneurship Essentials
Financial Accounting
Global Business
Launching Tech Ventures
Leadership Principles
Leadership, Ethics, and Corporate Accountability
Leading with Finance
Management Essentials
Negotiation Mastery
Organizational Leadership
Power and Influence for Positive Impact
Strategy Execution
Sustainable Business Strategy
Sustainable Investing
Winning with Digital Platforms

Creating Data Visualizations in Excel: What to Keep in Mind

Professional creating data visualizations in Excel

14 Oct 2021

One of the most valuable and abundant resources businesses have at their disposal is data. With vast amounts of data being generated every minute of every day, the insights gleaned can inform virtually every business decision —often resulting in favorable outcomes.

There are many data visualization tools on the market designed for creating illustrations for business purposes. Fortunately, one of the most popular and easy-to-use options is likely already installed on your computer: Microsoft Excel.

If you don’t have access to Microsoft Excel, consider using free options like Google Sheets for a similar, albeit more limited, experience.

While Excel isn’t visualization software, it’s a versatile, powerful tool for professionals of all levels who want to analyze and illustrate datasets. Here are the types of data visualizations you can create in Excel and the steps involved in doing so, along with some tips to help you along the way.

Access your free e-book today.

Types of Data Visualizations in Excel

There are different data visualization techniques you can employ in Excel, depending on the data available to you and the goal you’re trying to achieve, including:

Area charts
Scatter plots

Other visualization techniques can be used to illustrate large or complex data sets. These include:

Gantt charts
Highlight tables
Bullet graphs

More advanced visualizations, such as those that include graphic elements like geographical heat maps, may not be possible to create in Excel or require additional tools.

Related: 6 Data Visualization Examples to Inspire Your Own

How to Create Data Visualizations in Excel

The steps involved in creating data visualizations in Excel depend on the type of graph or chart you choose. For basic visualizations, the process is largely the same. More complex datasets and illustrations may require additional steps.

To craft a data visualization in Excel, start by creating an organized spreadsheet. This should include labels and your final dataset.

Then, highlight the data you wish to include in your visual, including the labels. Select “insert” from the main menu and choose the type of chart or graph you’d like to create. Once you’ve made your selection, the visualization will automatically appear in your spreadsheet.

Right-click on the chart or graph to edit details, such as the title, axes labels, and colors. Doing so will open a pop-up or side panel that includes options to add a legend, adjust the scale, and change font styles and sizes.

Your browser cannot play the provided video file.

Tips for Creating Visualizations in Excel

1. choose the right type of visualization.

To create an effective data visualization, it’s critical to choose the right type of chart or graph. Consider the type of data you’re using, the size of your dataset, and your intended audience.

A mismatch between the type of data being leveraged and the visual used to present it can be detrimental to viewers’ understanding of the information. Whether you’re working with qualitative or quantitative data, for example, impacts how you should display the information.

Your intended audience also influences how simplified or complex your illustration should be. For instance, when presenting to a large audience or high-level stakeholders, it’s helpful to distill your presentation to highlight key trends and insights rather than individual data points.

Business Analytics | Become a data-driven leader | Learn More

2. Remove Irrelevant or Inaccurate Data

Ultimately, your visualization’s quality is only as good as that of the data you use. For this reason, it’s important to clean data after it’s been collected to remove any irrelevant or inaccurate information. This process is often referred to as data wrangling or data cleaning.

Failure to thoroughly clean data prior to using it can be detrimental to its integrity and lead to inaccurate or misleading data visualizations .

Related: What Is Data Integrity & Why Does It Matter?

3. Provide Context For the Visualization

If necessary, include a key or legend and additional context to help viewers make sense of your illustration.

For example, consider a heatmap that shows the frequency of COVID-19 infections in a location over a specific period. To form a clear understanding of the information being presented, viewers need to know details such as the period being examined, the data source, and what each color means.

This context is important because it helps viewers interpret the information being displayed. Without a key clearly defining the coloring system, for instance, it would be virtually impossible to know what each color indicates, rendering the heatmap useless.

4. Tell A Story

Finally, the key to crafting a compelling visualization is to use data to tell a story. If the data illustrates a trend or supports a hypothesis, your visualization should make that clear. After all, the purpose of visualizations is to present findings in a way that’s easy for viewers to digest and understand.

Telling a story not only makes your visualization more interesting and engaging but also aids in data-driven decision-making . In addition, it helps stakeholders understand the essence of your findings and, in turn, informs their decision-making processes .

A Beginner's Guide to Data and Analytics | Access Your Free E-Book | Download Now

Making Data-Backed Business Decisions

Data visualization is a powerful tool when it comes to addressing business questions and making informed decisions. Learning how to create effective illustrations can empower you to share findings with key stakeholders and other audiences in a manner that’s engaging and easy to understand.

You don’t have to be in an analytics or data science role to take advantage of data visualizations. Professionals of all levels and backgrounds can develop data skills to more effectively communicate within their organizations and make informed decisions.

Are you interested in improving your analytical skills? Learn more about Business Analytics —one of the three courses that comprise CORe —which teaches you how to apply analytical techniques in Excel to solve real business problems.

About the Author

Excel Data Visualization: 20 Charts, Graphs, and Plots To Master

Excel Data Visualizations

This guide will take you through the basics of Excel data visualization , including how to create 20 of the most eye-catching and useful charts, graphs, and plots.

You will learn how to use them effectively to present data, uncover insights and tell compelling stories. By the end of this guide, you will be a pro at visualizing data with Excel.

1. Bar Charts

A bar chart is a type of graph or chart used to represent the relative amounts or values of different categories or groups of data. It consists of rectangular bars , with the length of each bar proportional to the value it represents.

Bar charts can represent a wide range of data, including counts of items, frequencies of events, totals, percentages, and other summary statistics. They are one of the most commonly used visual tools for presenting data, and they can be used to effectively communicate a wide range of information.

Bar charts come in two main flavors: horizontal and vertical . While the vertical bar chart visualized data on the horizontal (X) axis, a horizontal bar chart does the opposite. No matter the type of bar chart, you can also add a 3D style in Excel. Learn how to create a bar chart in Excel right now.

2. Line Graphs

Unlike bar charts, line graphs are more appropriate to visualize continuous data , typically showing changes over time. It is often used to show trends and patterns in data in a simple and straightforward way.

For example, line graphs are useful for visualizing and understanding trends in data over time, such as seasonal change, sales, performance metrics, and so on. They are also useful for visualizing comparisons between different data sets, such as when comparing metrics like profit and revenue over time.

Line graphs come in two main varieties: line charts and area charts. While area charts fill in the volume beneath the data points, line graphs leave the area blank (like below). These types of graphs in Excel can also be stacked and 3D.

3. Pie Charts

Pie charts visualize qualitative data. They are most often used to show the proportion of each data category. For example, they can display the percentage of each region’s population or the percentage of each type of sales.

They are not useful for showing comparisons between data points or calculating actual values. Pie charts are an old-school data visualization tool that is often criticized for being overused, poorly designed, and difficult to read.

They have been largely superseded by other visuals such as bar charts, column charts, or even the more stylish doughnut chart (see #4) that is often easier to read and interpret.

4. Doughnut Charts

If you don’t like pie, perhaps you might prefer a doughnut? A doughnut chart, that is! Although doughnut charts are a relatively new data visualization tool, they are quickly gaining popularity amongst data visualization experts.

A doughnut chart is really just a variant of a pie chart with a hole in the center. Instead of showing categories like slices of a pie chart, doughnuts show each category as arcs .

Doughnut charts are especially good for visualizing one or more series of data, typically showing a comparison between different groups. They are easy to create using Excel, and they offer a unique and interactive way to visualize data.

5. Column Charts

A column chart is a type of graph or chart that uses vertical bars to represent different categories of data, with the height or length of the bar representing the magnitude or frequency of the category.

Column charts are useful for showing how a particular metric is distributed across a categorical axis. For example, they may show the percentage of each sales territory, the percentage of profit generated by each product category, etc.

They are commonly used in Excel business presentations and reports to quickly illustrate trends and patterns in data. The two main varieties that you can create in Microsoft Excel are: stacked and clustered.

7 Data Analysis Tools in Excel

8 Ways to Use Excel for Business

6. area graphs.

An area graph is a type of chart or graph that displays information using a range of data points connected by straight lines and filled in with color or shading . It is similar to a line graph, but the area between the x-axis and the line is filled in with color or shading to indicate volume.

Area graphs emphasize the magnitude of change over time and can be used to compare different categories of data. They are typically used to illustrate trends over time or changes across different categories, such as population growth in different countries.

The area under the line can also be used to show cumulative totals. The types of varieties in Microsoft Excel for area graphs are stacked, 3D, and various combinations of stacked and 3D.

7. Scatter Plots

A scatter plot is a type of graph used to plot pairs of numerical data on a two-dimensional (2D) graph . It is used to show the relationship between two variables and can be used to determine if there is a correlation between them.

Scatter plots are also referred to as scatter diagrams, scatter graphs, or scattergrams. A scatter plot is created by plotting each point in a dataset on a two-dimensional graph. Each point represents one pair of data from the dataset.

The horizontal axis of the graph typically represents the independent variable , while the vertical axis of the graph represents the dependent variable . It is important to note that the axes do not need to be in order for the graph to be valid. This means that the independent and dependent variables could appear on either axis.

8. Bubble Graphs

A bubble graph, also known as a bubble chart or bubble plot, is a type of chart that displays data using circles. Each circle represents an individual data point, with the size of the circle representing the value of the data point.

Bubble graphs are used to compare and analyze the relationships between different data points, and can be used to identify patterns or correlations . Bubble graphs can be used to visualize data from a variety of different sources, such as demographics, financial information, and more.

In Excel, it’s also possible to add 3D effects to each data point. So instead of a circle, each point is a sphere. They provide an effective way to visualize and interpret large amounts of data quickly and easily, making them useful tools for researchers, analysts, and decision-makers.

Maps are used to visualize one or more series of data, typically showing the distribution of one or more metrics across geographical regions . They are created by plotting values to a list of countries/regions, states, counties, or postal codes.

Maps are useful for visualizing the distribution of data across geographical regions. For example, they can show the distribution of sales across different countries , the distribution of product adoption rates across different states, etc.

Maps are extremely effective visuals when the data is geographic in nature. They are best used when the data you are visualizing can be mapped with geographic coordinates. Another type of map is a “heat map”, which allows you to visually represent the relative size of data points.

READ MORE: What Is Geographic Information Systems (GIS)?

10. Stock Charts

A stock chart is a type of chart used to visualize and analyze the price history of a particular stock or financial market. Stock charts are an incredibly useful tool that can be applied in a variety of different contexts, from financial trading to marketing and product management.

There are various different types of stock charts available in Excel. The two most common are the candlestick chart and the line chart. Candlestick charts are typically used to analyze short-term price movements, whereas line charts are generally used to analyze price movements over a longer time period.

The chart is composed of a series of “candlesticks” which represent different price actions during the trading period. The open, high, low, and close prices of the security are represented in each candlestick. The body of the candlestick is the area between the opening and closing prices.

11. Surface Graphs

Surface graphs are one of the more unique chart options available in Excel and are useful for visualizing multiple dimensions of data . This allows users to visualize the relationships between different data sets at a glance.

They are useful for visualizing complex relationships between data sets that are not easily summarized in a single chart. Surface graphs are a bit more challenging to create than other types of charts.

For example, surface graphs are often used to represent the relationship between variables such as temperature, pressure, density, and volume. There are different varieties of surface graphs including 3D surface , wireframe , and contour .

12. Regression Plots

A regression plot is a type of chart used to visualize the relationship between two variables, typically denoted as x and y. It is used to analyze the linear relationship between the two variables and draw a line of best fit, or regression line, through the data points on the chart.

The line of best fit is used to make predictions about the relationship and can be used to estimate the value of one variable when the other is known. Regression plots are used in a wide variety of fields, such as economics, sociology, psychology, and engineering.

An R-squared value is a statistical measure that represents the proportion of the variance for a dependent variable that’s explained by an independent variable or variables in a regression model. Generally, the higher the R-squared value (closer to 1), the better the model fits your data.

13. Radar Charts

Radar charts, or spider charts, are another unique chart type that is useful for visualizing multiple dimensions of data. They display multivariate data in the form of a two-dimensional chart with three or more quantitative variables represented on axes starting from the same point.

Each variable is plotted along its own axis on the chart and connected together to form a polygon. Radar charts are useful for comparing the features of multiple items on one or more variables.

They can also be used to identify trends or patterns in the data. Radar charts can be used to compare different products, teams, or employees and to track changes over time. They are also often used to compare data from different sources or locations.

14. Treemaps

A tree map is a type of data visualization that uses nested rectangles to represent the hierarchical structure of a dataset. Treemaps are useful for displaying the relative size of different categories in a dataset and their relationships to one another.

They can be used to display large amounts of information in an easy-to-understand format, making them a popular choice for visualizing data in many different fields. Treemaps are particularly useful for exploring and comparing multi-dimensional datasets , such as budgets, market shares, and population trends.

They can also be used to compare distributions between different kinds of data, such as income levels or geographical regions. Treemaps can be used to analyze a variety of data sources, including business intelligence, financial analytics, and scientific research.

15. Sunburst Charts

A sunburst chart, also known as a radial tree diagram, is a type of data visualization that is used to represent hierarchical data in an easy-to-understand graphical format. They are becoming increasingly popular due to their ability to quickly provide insights into complex data sets.

A sunburst chart is composed of several rings or levels that branch out from the center to show different categories or values associated with the data. The size of each ring or level corresponds to the size of the category or value it represents, with larger rings indicating larger values.

Sunburst charts are especially useful for showing how different parts of a whole are related, and for displaying the relative sizes of different elements in a hierarchy. They can be used to visualize data in many different fields, such as business, finance, marketing, science, engineering, and technology.

16. Histograms

A histogram is a type of graph that is used to compare and contrast data sets , identify patterns, and understand trends. It displays the frequency or number of observations of a particular data set in a given range of values.

The data points are grouped together into bins , and the height of each bin indicates the frequency of observations within that bin. Histograms are useful for analyzing large amounts of data, such as survey results, financial data, and scientific experiments.

Histograms can be used to quickly identify the shape of a distribution , such as whether it is symmetric, skewed to the left, or skewed to the right. They can also be used to identify outliers and areas of potential interest.

17. Box & Whisker Plots

A box & whisker plot (also known as a boxplot) is a graphical representation of the distribution of numerical data. It is a way of displaying the five-number summary of a data set, which includes the minimum, first quartile, median, third quartile, and maximum value of the data set.

The boxplot is useful for quickly summarizing the median, spread, and skewness of a data set. It also helps to identify outliers and compare multiple data sets. It also helps to compare different sets of data and identify similarities or differences between them.

Boxplots are often used in exploratory data analysis to quickly visualize a dataset’s distribution and get a better understanding of its characteristics. It can be used to identify patterns, trends, and outliers in the data.

18. Waterfall Diagrams

A waterfall diagram is a type of chart that illustrates how different components of a process are dependent on one another . This type of chart is typically used to visually depict the progression of a project or process, from the beginning stages to the end result.

The waterfall diagram shows each step of the process or project and how the completion of one stage impacts the next. This allows for an easy understanding of the dependencies of each step, as well as a clear view of how each stage connects to the overall goal .

In addition, the waterfall diagram also allows for a better understanding of the timeline of a project or process, highlighting any areas that may have delays or need additional attention. Waterfall diagrams can be used in many different types of projects, including software development, product design, marketing campaigns, and more.

19. Funnels

A funnel chart is a type of chart used to visualize the progressive reduction of data as it moves through a process. It is commonly used to illustrate the steps in a sales, conversion, or user onboarding process.

Funnel charts can help organizations better understand their processes and identify areas for improvement. For example, a funnel chart can reveal where potential customers are dropping out or where there are issues with user onboarding.

The chart can also be used to compare different processes, such as comparing two different marketing campaigns or different versions of a website. But the focus is displaying values or drop-off from one stage to the next.

20. Custom Combination Charts

Custom combination charts, or simply “combo”, are created by combining different types of data visualization , such as multiple columns, line, and area graphs. The main purpose of custom combination charts is to visualize multiple data sets at once.

This can be useful when wanting to display multiple sets of data in a single chart and to make it easier to compare the different sets of data. Combination charts can also be used to compare data from different sources or to show the effects of an intervention or policy change.

For example, a custom combination chart can be used to show sales figures for different products on the same chart, with one set of data represented as a line chart, and another set represented as a bar chart.

That concludes our tour of the most common charts, graphs, and plots that you can visualize data with. With the ability to create complex and visually stimulating charts , graphs, and plots, Excel allows users to quickly and accurately analyze and present data in an easily digestible format.

Whether you are a beginner or an experienced user, learning to visualize data with Excel and understanding the different charts, graphs, and plots available is an essential skill for anyone who works with data.

Now that you know all about visualizing data with Excel, it’s time to put your knowledge into practice. We invite you to fire up Microsoft Excel and get ready to create some awesome data visualizations .

Related Data Visualization

Data Storytelling: Where Does Data Fit In? A Beginner’s Guide

10 Best Data Visualization Tools and Software For Your Business

What is Data Visualization and Why Do We Need It?‍

Scatterplots vs Bubble Graphs: What’s the Difference?‍

How to Create a Bar Chart in Excel

A Comprehensive Guide to Paving a Career in Data Visualization

Tips For Pursuing a Career in Data Visualization

Visualizing Data in Excel: A Comprehensive Guide

By STC

July 15, 2023

Explore the diverse data visualization possibilities in Excel that aid in analyzing and interpreting your data effectively.

Introduction

Welcome to our comprehensive guide on visualizing data in Excel. In this article, we will delve into the world of data visualization and provide you with valuable insights on how to create compelling visual representations of your data using Excel. Whether you are a beginner or an experienced Excel user, this guide will equip you with the knowledge and techniques to effectively communicate your data through visually appealing charts and graphs.

Why Data Visualization Matters

Data visualization is a powerful tool that enables us to make sense of complex datasets. It allows us to identify patterns, trends, and outliers that might not be immediately apparent in raw data. Visualizing data in Excel not only enhances our understanding of the information at hand but also enables us to communicate our findings to others in a clear and concise manner.

Getting Started with Excel Charts

Selecting the Right Chart Type Choosing the appropriate chart type is crucial for effectively representing your data. Excel offers a wide range of chart options, including bar charts, line charts, pie charts, scatter plots, and more. Consider the nature of your data and the message you want to convey when selecting the most suitable chart type.
Formatting and Customization Excel provides extensive formatting and customization options to refine the appearance of your charts. From adjusting axis labels to modifying colors and styles, these features allow you to create visually appealing charts that align with your brand or presentation requirements.
Adding Data Labels and Annotations To enhance the clarity of your visualizations, Excel enables you to add data labels and annotations. These labels provide additional context and make it easier for your audience to interpret the information being presented. You can include axis labels, data point labels, and explanatory text to further enrich your charts.

Advanced-Data Visualization Techniques

Creating PivotCharts PivotCharts are a powerful feature in Excel that allows you to visualize data from pivot tables. By summarizing and aggregating data, pivot tables provide a comprehensive overview that can be transformed into dynamic and interactive charts. Utilizing PivotCharts enables you to explore and analyze complex datasets with ease.
Utilizing Advanced Charting Features Excel offers advanced charting features that can take your visualizations to the next level. From trendlines and error bars to 3D charts and sparklines, these tools allow you to add depth and sophistication to your data representations. Experimenting with these features can help you create visually striking charts that captivate your audience.

Best Practices for Effective Data Visualization

To ensure your data visualizations have maximum impact, keep the following best practices in mind:

Simplify and Declutter Avoid cluttering your charts with excessive information or unnecessary embellishments. Focus on the key message you want to convey and remove any elements that distract from that message. Remember, simplicity is key when it comes to effective data visualization.
Use Color Strategically Colors can evoke emotions and draw attention to specific areas of your charts. Use color strategically to highlight important data points or to group related information. However, be mindful of accessibility considerations and ensure that your color choices are accessible to individuals with color vision deficiencies.
Tell a Story with Your Data Data visualization is not just about presenting numbers; it’s about telling a story. Structure your visualizations in a way that guides your audience through a narrative. Start with an introduction, present the main findings, and conclude with a clear takeaway or call to action.

In conclusion, mastering the art of visualizing data in Excel can significantly enhance your ability to analyze and communicate complex information. By selecting the right chart types, utilizing advanced techniques, and following best practices, you can create visually compelling representations that effectively convey your data’s story. We hope this comprehensive guide has provided you with the knowledge and inspiration to create outstanding data visualizations in Excel. Start exploring the power of data visualization today and unlock new insights from your data.

Check StoryTelling with Charts – The Full Story

About the author

We are passionate about the power of visual storytelling and believe that charts can convey complex information in a captivating and easily understandable way. Whether you're a data enthusiast, a business professional, or simply curious about the world around you, this page is your gateway to the world of data visualization.

Never miss a good story!

Subscribe to our newsletter to keep up with the latest trends!

How to Visualize Data in Excel (With Examples)

According to Linearity , it is easier for the brain to retain data when they’re visually represented. Especially for larger data sets, it is always a good idea to illustrate your data with color-coded formatting, charts, and graphs.

Excel is a powerhouse when it comes to analyzing data. It also contains many data visualization tools, including 19 types of charts, heat maps, and 4 library icon sets.

We’ll be going through these tools and discuss what data they best represent through examples!

Charts in Excel

Charts are best used to monitor performances and analyze results. If you’re creating a presentation to compare annual sales, represent it in a chart.

Excel offers a variety of charts from simple bar charts to even map charts! Each chart is usually of these types:

Clustered Chart : Visualize your chart in a 2-D format that includes the horizontal (X), and vertical (Y) axis.
Stacked Chart : Illustrates the contribution of each data value in the entire data set.
100% Stacked Chart : Show the relative percentage of each data value in the set. The total value of one stacked chart is equal to 100.
3-D Chart : Includes a third axis called the depth (Z) axis.

To insert charts in Excel, first select data. Then, go to the Insert tab. On the Charts section, click on the chart you wish to insert.

If you’re unsure of what chart to use, select your data and click on Recommend Charts from the Charts section to get suggestions.

With that, let’s see every chart in detail.

Column Chart

Column charts are vertical bars that represent items on the horizontal (X) axis and frequency on the vertical (Y) axis.

Column charts are clustered, stacked, 100% stacked, and 3-D column charts. These charts (except 3-D column charts) can be formatted to show values in 2-D or 3-D.

A line chart is used to show trends over a specific interval. When data increases, the line flows vertically upwards. Similarly, if the progress declines, it moves down the chart.

Excel offers line charts, stacked line charts, 100% line charts, and 3-D line charts. You can insert markers representing the data value specified on your data set.

A pie chart illustrates the total composition of an object or a task. If you need to break the pie chart further down, insert either a pie of pie or a bar of pie chart instead of a pie chart.

Excel allows you to format these charts in both 2-D and 3-D format.

Doughnut Charts

A doughnut chart is similar to a pie chart, except it can also be used to display negative values in the chart. The data value in a doughnut chart is represented in percentages that sum up to 100%

Data values in a bar graph are represented as horizontal bars. The bar stretching the farthest is the greatest value, while the bar closest to the Y axis is the smallest value.

You have the option to insert bar charts as clustered, stacked, or 100% stacked.

An Area chart looks quite similar to a line chart. While they both display trends, an area below the plotted lines in an area chart is filled with a color. This makes data analysis easier when there are multiple trends to follow.

Area charts are of three types: Area chart, stacked area chart, and 100% stacked area chart. Excel offers all three area charts in 2-D and 3-D formatting.

Scatter and Bubble Chart

Scatter and Bubble charts establish a relationship between two data sets. If you’re trying to visualize the cost of making and the total profit made on a product, creating such plots is the correct way to go.

Create scatter charts with just the markers, smooth lines, or both—smooth lines and markers. Similarly, you have the option to insert scatter lines with straight lines or straight lines with markers.

For bubble charts, formatting is available in 2-D and 3-D effects.

Stock Chart

As the name gives it away, the stock chart is handy when it comes to monitoring the rise and fall of stocks.

If you’ve scrapped such data from the web, create a stock chart to visualize how the stock you’ve invested in is performing in the market.

A stock chart is also useful in tracking geographical data such as temperature rise and annual precipitation.

There are multiple types of stock charts depending on the number of your data series in Excel. These types include High-low-close , Open-high-low-close , Volume-high-low-close , and Volume-open-high-low-close .

For Excel to identify your data, always label and arrange them according to the chart type.

Surface Chart

Surface charts are mostly used by engineers and geologists to determine the elevation of an area. Surface charts use three variables when plotting data, making it a 3-D chart.

Surface charts are of four types in Excel consisting of 3-D surface , wireframe 3-D surface , contour , and wireframe contour chart .

Radar Chart

Radar charts, also referred to as spider charts, are used to compare items on a specific characteristic.

If you’re quantitatively comparing the qualities of products, use radar charts to spot which feature a certain product stands out the most.

You can insert markers with markers or even fill them up with colors in Excel.

Other Charts (Available for Excel 2016 and Later)

The above-mentioned charts are available in all versions of Excel. However, if you’re on Excel 2016 or later, you have the option to insert other charts including:

Treemap chart
Sunburst chart
Histogram charts
Box and Whisker charts
Waterfall charts
Funnel charts
Combo charts

A heat map is a visualization technique where you color-code numbers according to their values. Heat maps are excellent for when you’re analyzing weather reports, profit-loss, and even a mark sheet.

Excel offers six 3-color and 2-color scales to create heat maps. Additionally, you have the option to customize the color palette to create a heat map.

You can create heat maps on Excel range, tables, and even PivotTables .

Let’s create a Green-Yellow-Red color scale heat map in the sales sheet below:

Icon sets are a part of the conditional formatting utility. You can insert icons on your data set based on a hierarchy or a custom rule.

There are four types of icons— Directional , Shapes , Indicators , and Ratings .

Directional

Directional icons show if the values have increased, increased, or remained constant in the form of arrows.

Excel allows you to choose from seven sets of arrows when inserting directional icons. Use directional icons when analyzing financial data such as a sales report.

Let’s enter directional icons to compare the sales made by Julie in twenty days:

According to the results, Julie’s performance is not that great.

Shapes and Indicators

As Shapes and Indicators are more or less the same, I’ve grouped them under the same category.

Let’s review my expenses for the past six months as an example. I’ve set a budget of $50-$100 for each category. However, I’ve gone over budget in a few areas.

We’ll be inserting shapes to make this visualization:

Go to Home > Conditional Formatting > Icon Sets .

As the data illustrates, I have gone over my budget every month except October. This might be my sign to start budgeting more seriously!

Excel offers five types of rating icons. You can represent your ratings in the form of 3 stars, 5 quarters, 4 boxes, 4 ratings, and 5 ratings.

In this table, I have the total rating of 10 different hotels on a scale of 1-5. Let’s enter the 5 ratings icons to represent these icons.

According to the illustration, Hotel Safe Haven and Hotel Paradise have the highest ranges. Whereas, Hotel Heaven and Lake View Hotel have the lowest ratings on average.

Use Analyze Data in Excel (Excel 365)

If you’re on Excel 365, use Excel’s Analyze Data feature to visualize your data set.

Excel uses its smart AI in the Analyze Data tool to generate meaningful visuals, mainly charts and PivotTables.

In this sheet, we have a stock report. Let’s analyze this data using the Analyze Data tool.

Look for appropriate charts and tables under Discover Insights .

Visualize Excel Data via Power BI

If you frequently need to visualize data, load your Excel sheet to Power BI .

Power BI is a Microsoft 365 E5 package and is a powerful visualization utility. Using Power BI, build visuals using all sorts of charts, graphs, and data tables.

Asmi is a content writer specializing in MS Office. She loves writing Excel-related content to assist other users to learn their way around the program. She started using Excel when she first entered the industry as a marketing intern. Soon, she realized that learning the often-overlooked spreadsheet program was no piece of cake. In her articles, she attempts to brief out complex topics to help beginner to intermediate-level Excel users. Asmi uses Excel for everything, including creating a grocery list to managing her personal finance. She’s a bit dramatic when it comes to the Flash Fill feature; so much so that could write a eulogy out of it. When she’s not working, you’ll find her learning a new recipe, or cleaning up her mess (out of the failed recipe).

How to compare two columns in excel using vlookup, how to share excel files with multiple users, how to filter based on a list in excel.

Type above and press Enter to search. Press Esc to cancel.

[email protected]

Spreadsheets Expert

Everything about Excel & Google Sheets

How To Create Data Visualizations in Excel? A Complete Guide

T ransform your Excel sheets from simple spreadsheets to storytelling tools with beautiful and intuitive “data visualizations in Excel”. This powerful feature turns complex data into clear, engaging visuals, making it easier to spot trends, patterns, and insights.

Data visualization in Excel represents numerical value in a visual format. It is the way to organize data in a spreadsheet in a more accessible and organized format. Confidently navigate through the myriad of chart types and customization options available in Excel to effectively communicate your data’s story.

Excel templates can be used for data visualization. Excel as a data visualization tool has several charts, graphs, and maps you can use to visualize your data, like bar graphs, line charts, pivot tables, etc.

This guide will take you step-by-step through creating impactful data visualizations, ensuring your presentations and reports stand out with professional polish and clarity. Let’s dive in and bring your data to life!

What is Data Visualization in Excel?

Definition : Data visualization is the graphic representation of data that makes it easier to interpret. We can create Data visualizations using tools like Data Wrapper, Google Charts, and others. Additionally, data is organized and visualized using an Excel spreadsheet.

Let’s explore Excel’s data visualization features in many different ways. We can use different Excel charts and graphs to visualize data. Additionally, data visualization using Excel templates is possible.

Column charts, bar charts, pie charts, progress bars , line charts, area charts, scatter charts, surface charts, Sankey diagrams , and many others are available in Excel.

Different Types of Data Visualizations in Excel

Excel can be used for several data visualization techniques, including:

Column Chart

It is a simple type of graph where data is shown as vertical bars. Select the data and the required option from the Column chart menu to build a column chart. As we can see, several options exist for the Column chart; the best option must be picked.

The chart can be formatted as required.

Pie charts or diagrams display the percentage share each data type makes up. The pie chart helps us rapidly understand the proportion contribution. To create a pie chart, pick the required columns, then pick the relevant pie chart option from the Pie menu.

Horizontal bars are the only difference between this chart type and a column chart. Select the suitable bar chart from the Bar option to make a horizontal bar.

A line graph is commonly drawn to show data that changes over time. It consists of two axes: the x-axis and the y-axis. Each axis represents a different dataset. It is formed by connecting a series of points using a straight line.

It can be used to check whether the values are increasing or decreasing over time.

Pivot Table

A pivot table is a tabular representation of data used in data visualization that is used to group, sort, and summarize huge volumes of data.

Advanced Excel Charting Techniques

There are the following advanced Excel charting Techniques used for data visualization:

Combining multiple data series

When you have multiple data sets that you want to compare in a single chart, you can combine them into one chart using Excel’s capability to handle multiple data series. Here’s how to do it

Ensure that your data is organized properly. You may have two columns or rows for categories (e.g., months and sales)
Highlight the entire dataset, including the category labels and all the data series.
Go to the “Insert” tab in Excel’s ribbon, and select the chart type that best suits your data. Common choices include clustered column/bar charts for comparing data in different categories or line charts for showing trends.
Excel will create a basic chart with all the data series. You can further customize the appearance of each data series by changing colors, styles, and labels. It helps differentiate and highlight each series.

advanced excel charting techniques for visualize data inexcel 1

If you have multiple data series, including a legend in your chart is crucial. The legend identifies which color or symbol corresponds to each data series.
Enhance the visual appeal of your chart by adjusting fonts, colors, and chart elements as needed.

Creating pivot charts

Pivot charts are powerful tools for summarizing and visualizing large datasets quickly. They work with pivot tables to provide a dynamic way to analyze data. Here’s how to create a pivot chart:

Ensure your data is organized in a tabular format, with columns for different attributes such as dates, categories, and values.
Pick your data, go to the “Insert” tab, and click “PivotTable.” Configure the pivot table to summarize your data as desired, choosing rows, columns, and values.

advanced excel charting techniques for visualize data inexcel 2

With your pivot table created, select any cell within it. Then, go to the “Insert” tab and click “PivotChart.” Choose the chart type you want to use (e.g., bar chart, line chart).

advanced excel charting techniques for visualize data inexcel 3

Excel will generate a pivot chart linked to your pivot table. You can customize the chart by add the fields from the pivot table to the chart’s elements to create meaningful visualizations.

How to Make a Heat Map Visualization in Excel?

Create a geographic heat map in Excel quickly by using conditional formatting. Colors on a heat map represent the individual values.

To make a heat map, follow the procedures below:

Choose the range
On the Home tab, select Conditional Formatting from the Styles category.

Under Color Scales, select a subtype.
Format Right-clicking or hitting CTRL + 1 will choose a cell.
Select Custom from the menu.

Excel offers data visualization tools , from basic charts to advanced mapping features. You can adapt Excel’s visualization capabilities to their specific needs, whether you are a beginner or an expert.

This article introduced the Excel data visualization concept and outlined various visualization techniques. Excel empowers users to communicate data insights, make informed decisions, and create compelling visual narratives , enhancing data analysis processes.

With over two decades of experience in writing about Microsoft Excel, Google Sheets, and various other spreadsheet tools, Muhammad Nadeem Salam is your go-to expert for all things data. Since 2004, he has been passionately sharing his knowledge and insights through engaging and informative blog posts, helping countless readers unlock the full potential of their spreadsheet tools.

Nadeem Salam

How to Compare Two Columns in Excel?

How to Create a Sankey Diagram in Excel?

How to Insert Check Mark (Tick ✓) Symbol in Excel?

How to Quickly Remove Multiple or All Hyperlinks in Excel?

Begin typing your search term above and press enter to search. Press ESC to cancel.

Excel Visualization: A Guide to Clear Data Presentation for Beginners

I once struggled with dull data tables.

Numbers clustered in rows and columns become a blur. But with Excel visualization , you can empower your audience to make informed decisions based on the data presented. Excel charts and graphs replace chaos, revealing patterns and trends.

Convey ideas efficiently with the right visual. It’s not just about creating a chart; it’s about making data understandable and engaging.

In this article, I’ll guide you step-by-step on transforming your Excel data into insightful visuals.

Let’s get started!

Table of Contents

Understanding the Basics of Excel Visualization

Excel provides various visualization options, whether 2D or 3D versions, standard, stacked, or 100% stacked options. It’s all about finding the right fit that best represents your data and message.

The Excel Charting Interface

Let’s start with creating a chart in Excel.

When you click on the Insert tab in Excel, you’ll see various chart types that you can use to visualize your data.

The Excel charting interface provides a wide range of options, from line and area charts to bar and column charts. When you click on a chart, the ‘ Chart Tools ’ contextual tab provides additional features for customizing your charts.

Types of Data for Visualization

Excel visualization data can be broadly categorized into numerical, categorical, and time-series data.

Numerical data includes values that can be measured, such as sales figures or temperature readings.
Categorical data includes information such as names, labels, or groups.
Time-series data involves values measured over time, such as stock prices or website traffic.

Excel offers different chart types depending on your data type.

Selecting the Right Chart Type

Selecting the right chart type is half the battle for effective data visualization in Excel.

Pie charts are best for part-to-whole comparisons. Use line charts for time series or trends. Bar or column charts are the most suitable for categorical comparisons.

However, consider more advanced chart types for more complex data sets.

Scatter plots are excellent for correlation analysis , while histograms and box plots are ideal for distribution analysis of quantitative data.

It’s all about understanding your data and determining the best way to display it.

Steps for Visualizing Data in Excel – Creating Basic Charts

Creating basic charts in Excel is a fundamental skill for anyone looking to present data in a visual format.

Excel offers a variety of chart types, each with unique properties and use cases. The key to successful chart creation in Excel is understanding these different chart types and knowing how to present your data most effectively with them.

Organizing Your Data

Before you dive into creating Excel charts, it is crucial to organize your data correctly .

Well-organized data will make the charting process easier and the resulting charts more meaningful. Ensure your data is clean, error-free, and arranged clearly and logically.

This will make it easier to select the data for your charts and create visuals that effectively communicate your data analysis results.

Pie and Donut Chart

Pie charts are popular for showing the proportion of different categories within a whole. While visually appealing, they are often misused and can lead to misleading interpretations.

Generally, they are most effective when comparing a few categories representing parts of a whole.

On the other hand, donut charts are a variation of pie charts with a hole in the middle (as the name implies!). Like pie charts, they can display multiple data series, but they should be used sparingly.

To create a pie chart in Excel:

Select the data you want to visualize
From the “ Insert ” tab, choose “ Pie ” from the chart options.
You can customize your chart by changing the colors, adding labels, and adjusting other settings in the “ Format Chart Area ” pane.

Here’s a video guide on how to create a donut chart:

Line and Area Chart

Line and area charts are handy when dealing with time-series data . These charts plot data points on a graph and connect them with a line, allowing you to see trends over time.

Check out this video for a step-by-step guide on how to create a line chart:

One of the business essentials when working with line and area charts is customizing the axis and gridlines. This can help make your chart more readable and meaningful .

The “ Format Axis ” pane allows you to customize the axis labels, adjust the scale, and add gridlines.

Column and Bar Graph

Bar and column charts are Excel’s most commonly used chart types. They are excellent for comparing different categories of data.

While bar charts and column charts are often used interchangeably, there is a difference: A bar chart presents data horizontally , while a column chart presents data vertically . This distinction can influence how easily your audience interprets the chart.

You can also choose between a stacked or clustered bar and column chart layout.

In a stacked chart , data series are stacked on each other, while in a clustered chart , they are placed side by side.

To create a bar or column chart:

Select the data
Then choose either “Bar” or “Column” from the chart options in the “ Insert ” tab
Remember to format the chart and the axis labels to make the chart easier to understand

Advanced Charting Techniques

In this section, I’ll describe how to present complex data in a visually appealing and easily understandable format. Since each dataset is unique, treat these charts as ideas for meaningfully presenting your data.

Combination Charts

This type of chart combines the features of line and column charts, allowing you to present mixed data more comprehensively.

For example, when you have a target and actual data for comparison , a combination chart can be the perfect tool for visualization.

Clicking the Chart Design tab on the ribbon allows you to change the chart type and create a customized combination chart.

This allows you to have your target values in columns and the actual values marked along the line, which provides a clearer visualization of your data.

Trendlines and Data Analysis

Another essential feature of Excel charts is the ability to add trendlines. These can be linear, polynomial, or moving average trendlines.

A trendline graphically displays trends in your data , and you can extend it beyond the actual data to predict future values.

Along with trendlines, interpreting R-squared values is also crucial in data analysis. This will help you understand the relationship between your dependent and independent variables, thus enhancing your analysis results.

Check out our detailed how-to post on adding trendlines to Excel charts .

Conditional Formatting in Charts

Conditional formatting is another advanced charting technique in Excel that can enhance your data visualization. You can also add data bars, color scales, and icon sets.

These features allow you to customize your charts based on certain conditions, making it easier for your audience to understand your data. Applying these formatting options enables you to create more engaging and visually appealing charts for your data presentation.

Creating a Tornado Chart in Excel

Tornado charts are particularly effective when comparing and contrasting different variables . A well-crafted tornado chart can help you visualize how changes in several factors can impact a specific outcome – for example, the impact of inflation on NPV and IRR results.

Here’s a video showing you how to create a tornado chart:

Designing a Funnel Chart in Excel

Funnel Charts in Excel are highly effective tools for monitoring sales processes or any other process that narrows down over time.

Here are two quick methods for designing funnel charts in Excel:

Building a Waffle Chart in Excel

Waffle charts, also known as square pie or waffle bar charts, are a great way to visualize individual data points compared to the whole data set. They are a fun and engaging way to present percentages or proportions.

Here is a simple method for creating waffle charts:

Data Visualization Tips – Enhancing Chart Aesthetics

The aesthetics of your Excel chart play a significant role in how effectively your data is communicated.

A visually appealing chart is easier to understand and engages your audience. Enhancing chart aesthetics involves working with various chart elements and features, such as colors, styles, and data labels.

Adding data labels, for instance, provides additional information on your chart, making it easier to interpret.

Besides, you can customize the chart’s colors and styles to match your presentation theme or company branding.

Check out this post for more information on good dashboard design principles .

Working with Chart Elements

Working with chart elements can significantly improve the readability and effectiveness of your data visualization.

Some key chart elements you can manipulate include titles, legends, and data labels.

Data labels provide additional context to your data and can be customized to suit your chart
Modify axis labels and gridlines to adjust their appearance and improve readability. Check out this video on how to add gridlines to your Excel charts:

These chart elements can enhance your aesthetic appeal and make your data easier to interpret.

Customizing Chart Colors and Styles

Spicing up your Excel charts is easier than you think.

The ‘ Chart Design ‘ tab in the Excel ribbon allows you to alter your charts’ aesthetics significantly.

Navigate to the ‘ Chart Styles ‘ section, and you’ll see various styles for your chart.

Looking for a bit more customization? No problem! Simply click the ‘ Change Colors ‘ dropdown and choose a color scheme.

You can use Excel’s preset color schemes or create a custom color palette for brand consistency. Minor visual changes can significantly affect your chart’s overall look and feel.

3D Charts and Effects

Adding a third dimension to your charts can make them pop . But be careful.

While 3D effects can add a specific wow factor, they can also lead to misinterpretations of your data if they are not used properly.

To add 3D effects to your charts, click the ‘ Chart Styles ‘ and choose a style with 3D effects.

Remember, though, that 3D effects should be used sparingly and only when they can enhance the understanding of the data. Overuse of these effects can lead to cluttered, confusing charts. When it comes to 3D effects, less is often more .

Advanced Excel Graphics

Beyond the basic charts, Excel offers advanced graphics capabilities to take your data presentation to the next level.

This includes using Sparklines, shapes, and icons, among other features.

Sparklines are mini-charts within individual cells, each representing a row of data. They give a quick snapshot of trends, helping you understand your data at a glance.

Excel offers line, column, and win/loss types of Sparklines that you can add with the Quick Analysis tool.

Using Shapes and Icons

Remember to appropriately format these shapes and icons to convey the right message and not distract from the data.

Portraying a Story Through Data

Excel visualization is not just about creating charts or diagrams; it’s about telling a story with your data. This is where the concept of data storytelling comes in.

It’s about using visualization tools to highlight key points and trends in your data, making it easier for your audience to understand and absorb.

It’s not unlike creating a plot in a novel where rows and columns of data are the characters, and the chart is the narrative arc. Every element should convey your story effectively and compellingly, from simple bar charts to intricate trend analysis.

Exporting and Sharing Your Visualizations

Once you’ve created your data visualization in Excel, it’s important to know how to share it! This involves exporting the visual representation of data in a format that others can easily access.

Whether you’re sharing a simple bar graph or a complex infographic, the export method will depend on the intended use of the chart/graphic.

This process can be as simple as saving your chart as an image or embedding Excel visuals in PowerPoint presentations and documents.

Saving Charts as Images

One of the simplest ways to share visualizations is by saving them as images .

To do this, right-click the chart and select ‘Save as Picture.’ Several image formats are available, each with its uses.

For instance, JPEG is great for photographic images, while PNG is ideal for images with transparent backgrounds. However, it’s important to consider the resolution of your image. High resolution is crucial for clear, crisp images, especially if they’re intended for print.

Embedding Excel Visuals in Presentations and Documents

Embedding them in presentations and documents is another way to share your Excel visualizations.

This can be done in two ways: linking and embedding .

Linking refers to connecting the original Excel file and the document where it’s inserted. Any changes made to the original file will automatically update in the document (assuming the link isn’t broken ).
Embedding involves inserting a copy of the chart into the document. While this won’t update automatically, it ensures that the chart will always be available, regardless of the status of the original file.

Both methods have advantages and should be chosen based on your specific needs.

Frequently Asked Questions

What are some common mistakes for beginners to avoid in data visualization with excel.

Common mistakes include overcrowding the chart with too much data, using inappropriate chart types, neglecting to label axes or data points clearly, and choosing colors or styles that reduce readability.

What are the best practices for presenting Excel data visually to a non-technical audience?

Focus on simplicity and clarity .

Use straightforward chart types, avoid technical jargon, and highlight key takeaways. Ensure your charts are well-labeled, and use annotations or callouts to draw attention to important data points.

What are some resources to learn more about Excel visualization?

For more tips and tricks, visit my YouTube channel . Alternatively, look at Chandoo’s training, where I learned many excellent dashboard design ideas.

Can Excel visualization help in career development?

Absolutely! Proficiency in Excel visualization is a valuable skill in many industries.

It’s especially relevant in fields like data science, finance, marketing, and others involving large amounts of data. Effectively communicating data through graphical representation can give you a significant advantage in your professional journey.

Data Visualization in Excel

This course is part of Excel Skills for Data Analytics and Visualization Specialization

Taught in English

Some content may not be translated

Instructors: Nicky Bull +1 more

Instructors

Instructor ratings.

We asked all learners to give feedback on our instructors based on the quality of their teaching style.

Financial aid available

55,873 already enrolled

(1,092 reviews)

Recommended experience

Intermediate level

We expect that you have some background with Excel. You can move around the interface and create basic formulas.

Skills you'll gain

Microsoft Excel
Data Visualization

Details to know

Add to your LinkedIn profile

See how employees at top companies are mastering in-demand skills

Build your subject-matter expertise

Learn new concepts from industry experts
Gain a foundational understanding of a subject or tool
Develop job-relevant skills with hands-on projects
Earn a shareable career certificate

Earn a career certificate

Add this credential to your LinkedIn profile, resume, or CV

Share it on social media and in your performance review

There are 7 modules in this course

In an age now driven by "big data", we need to cut through the noise and present key information in a way that can be quickly consumed and acted upon making data visualization an increasingly important skill. Visualizations need to not only present data in an easy to understand and attractive way, but they must also provide context for the data, tell a story, achieving that fine balance between form and function. Excel has many rivals in this space, but it is still an excellent choice, particularly if it's where your data resides. It offers a wealth of tools for creating visualizations other than charts and the chart options available are constantly increasing and improving, so the newer versions now include waterfall charts, sunburst diagrams and even map charts. But what sets Excel apart is its flexibility, it gives us total creative control over our designs so if needed we could produce our own animated custom chart to tell the right story for our data.

Over five weeks we will explore Excel's rich selection of visualization tools using practical case studies as seen through the eyes of Rohan, an environmental analyst. Rohan is required to produce visualizations that will show trends, forecasts, breakdowns and comparisons for a large variety of environmental data sets. As well as utilising the usual chart types he wants to use conditional formats, sparklines, specialised charts and even create his own animated charts and infographics. In some cases, he will also need to prepare the data using pivot tables to drill down and answer very specific questions. We are going to help him achieve all this and present our finished visualizations in attractive reports and dashboards that use tools like slicers and macros for automation and interactivity. These are the topics we will cover: Week 1: Dynamic visualizations with conditional formatting, custom number formatting, sparklines and macros Week 2: Charting techniques for telling the right story Week 3: Creating specialised and custom charts Week 4: Summarising and filtering data with pivot tables and pivot charts Week 5: Creating interactive dashboards in Excel This is the second course in our Specialization on Data Analytics and Visualization. The first course: Excel Fundamentals for Data Analysis, covers data preparation and cleaning but also teaches some of the prerequisites for this course like tables and named ranges as well as text, lookup and logical functions. To get the most out of this course we would recommend you do the first course or have experience with these topics. In this course we focus on Data Visualization in Excel, join us for this exciting journey.

Welcome and critical information

What's included.

1 video 3 readings 1 discussion prompt

1 video • Total 3 minutes

Course Introduction • 3 minutes • Preview module

3 readings • Total 35 minutes

Welcome to Data Visualization in Excel • 15 minutes
Course goals and weekly learning objectives • 10 minutes
Important information about versions and regions • 10 minutes

1 discussion prompt • Total 15 minutes

Set your goals and be successful • 15 minutes

Data Visualizations using Conditional Formatting, Sparklines and Number Formats

This week we will explore a host of data visualisation tools that do not include charts. We will learn about conditional formatting, including custom conditional formatting using formulas, drop down lists, and macros. We will then move on to learning about sparklines, shapes, and custom number formats.

6 videos 4 readings 5 quizzes 1 discussion prompt

6 videos • Total 48 minutes

Week 1 Introduction • 5 minutes • Preview module
Trend-based conditional formats • 6 minutes
Value-based conditional formats • 7 minutes
Macros for interactive visualizations • 9 minutes
Visualizations with Sparklines and Shapes • 10 minutes
Custom number formats • 9 minutes

4 readings • Total 55 minutes

Read me before you start: Quizzes and Navigation • 10 minutes
Download the Week 1 workbooks • 5 minutes
Week 1 Practice Challenge • 30 minutes
Week 1 Toolbox • 10 minutes

5 quizzes • Total 50 minutes

Trend-based conditional formats • 10 minutes
Value-based conditional formats • 10 minutes
Macros for interactive visualizations • 10 minutes
Custom number formats • 10 minutes

1 discussion prompt • Total 30 minutes

Week 1 Creative Corner • 30 minutes

Mastering charting techniques

This week we will explore a range of options to visualise your data using charts. We will look at standard charts such as line charts, pie charts, and scatter charts – to help you visualise your data. We will also look at charts to add to your standard toolkit such as area charts, donut charts, and bubble charts – which will add that wow factor to your work.

6 videos 3 readings 6 quizzes

6 videos • Total 51 minutes

Week 2 Introduction • 4 minutes • Preview module
Charting techniques • 9 minutes
Pies and doughnuts • 9 minutes
Line charts and trendlines • 9 minutes
Area charts • 8 minutes
Scatter and bubble charts • 10 minutes

3 readings • Total 45 minutes

Download the Week 2 workbooks • 5 minutes
Week 2 Practice Challenge • 30 minutes
Week 2 Toolbox • 10 minutes

6 quizzes • Total 95 minutes

Test your skills: Weeks 1 and 2 • 45 minutes
Charting techniques • 10 minutes
Pies and doughnuts • 10 minutes
Line charts and trendlines • 10 minutes
Area charts • 10 minutes

Specialized charts

This week we will explore a range of innovative and creative charts – many of which are new to Excel. These charts include: hierarchy charts, waterfall charts, funnel charts, and geospatial charts. We will also look at customising and creating our charts when we look at population charts and gauge charts – giving you ideas to be innovative and creative.

6 videos 3 readings 5 quizzes 1 discussion prompt

6 videos • Total 49 minutes

Week 3 Introduction • 3 minutes • Preview module
Hierarchy charts • 7 minutes
Waterfall and funnel charts • 6 minutes
Geospatial charts • 7 minutes
Custom chart techniques - population charts • 9 minutes
Creative chart techniques - gauge charts • 13 minutes
Download the Week 3 workbooks • 5 minutes
Week 3 Practice Challenge • 30 minutes
Week 3 Toolbox • 10 minutes
Hierarchy charts • 10 minutes
Waterfall and funnel charts • 10 minutes
Geospatial charts • 10 minutes
Custom chart techniques - population charts • 10 minutes
Creative chart techniques - gauge charts • 10 minutes
Week 3 Creative Corner • 30 minutes

Create an Interactive Dashboard Using Pivot Charts and Slicers

This week we take a turn into data analytics by exploring Pivot Tables. We will look at how to summarise data with Pivot Tables, as well as how to customise, group, sort, and filter pivot data. We will then look at performing calculations in Pivot Tables. Finally we explore Pivot Charts which help us visualise the data in our Pivot Tables.

7 videos 3 readings 7 quizzes

7 videos • Total 67 minutes

Week 4 Introduction • 4 minutes • Preview module
Summarise data with PivotTables • 10 minutes
Customise PivotTables • 10 minutes
Group and sort pivot data • 10 minutes
Filter pivot data • 8 minutes
Perform calculations in pivots • 11 minutes
PivotCharts and other visualizations • 10 minutes
Download the Week 4 workbooks • 5 minutes
Week 4 Practice Challenge • 30 minutes
Week 4 Toolbox • 10 minutes

7 quizzes • Total 105 minutes

Test your skills: Weeks 3 and 4 • 45 minutes
Filter pivot data • 10 minutes
Perform calculations in pivots • 10 minutes

Complete the Dashboard with Creative Visualisations and Dynamic Charts

This week we will explore putting all our tools and knowledge together in aesthetic, interactive, and informative dashboards. Putting dashboards together will also help you revise all the tools and concepts that you have gathered along the way. By the end of the week you will be able to incorporate themes, macros, hyperlinks, as well as utilise slicers to make your dashboards interactive.

6 videos • Total 53 minutes

Week 5 Introduction • 4 minutes • Preview module
Dashboard planning and layout • 11 minutes
Add charts to a dashboard • 10 minutes
Create responsive infographics • 10 minutes
Slicers for interactive dashboards • 7 minutes
Dashboard drilldowns and protection • 9 minutes
Download the Week 5 workbooks • 5 minutes
Week 5 Practice Challenge • 30 minutes
Week 5 Toolbox • 10 minutes
Dashboard planning and layout • 10 minutes
Slicers for interactive dashboards • 10 minutes
Dashboard drilldowns and protection • 10 minutes
Week 5 Creative Corner • 30 minutes

Final assessment

This final assessment covers all content in weeks 1 to 5.

1 quiz • Total 60 minutes

Final assessment: All weeks • 60 minutes

Macquarie is ranked among the top one per cent of universities in the world, and with a 5-star QS rating, we are recognised for producing graduates who are among the most sought-after professionals in the world. Since our foundation 54 years ago, we have aspired to be a different type of university: one focused on fostering collaboration between students, academics, industry and society.

Recommended if you're interested in Business Essentials

Macquarie University

Excel Power Tools for Data Analysis

Excel Skills for Data Analytics and Visualization

Specialization

Excel Fundamentals for Data Analysis

Fundação Instituto de Administração

Supply Chain & Operations

Why people choose coursera for their career.

Learner reviews

Showing 3 of 1092

1,092 reviews

Reviewed on Jul 9, 2021

Highly recommended! Course videos are well-paced and have very clear explanation. Each topics are taught deep and detailed, with a step-by-step process. Thank you!

Reviewed on Dec 6, 2022

Every course from this University and this pair of professors about Excel is great! It is not boring and a lot of things are covered.

Reviewed on Mar 28, 2024

The course content was excellent however the rushed pace of the videos made it loose spontaneity and flow with constant stopping and rewinding non conducive to learning.

New to Business Essentials? Start here.

Open new doors with Coursera Plus

Unlimited access to 7,000+ world-class courses, hands-on projects, and job-ready certificate programs - all included in your subscription

Advance your career with an online degree

Earn a degree from world-class universities - 100% online

Join over 3,400 global companies that choose Coursera for Business

Upskill your employees to excel in the digital economy

Frequently asked questions

What versions of excel can i use.

This course teaches some of the latest features and visualisations available in recent versions of Excel. We encourage learners to use Excel in the Office 365 suite to get access to these great new features. Most features are available in Excel 2016 and 2013, and we have provided alternative ways of creating the visualisations for these versions. Excel 2010 and prior versions are no longer supported by Microsoft and do not have the necessary features for this course.

When will I have access to the lectures and assignments?

Access to lectures and assignments depends on your type of enrollment. If you take a course in audit mode, you will be able to see most course materials for free. To access graded assignments and to earn a Certificate, you will need to purchase the Certificate experience, during or after your audit. If you don't see the audit option:

The course may not offer an audit option. You can try a Free Trial instead, or apply for Financial Aid.

The course may offer 'Full Course, No Certificate' instead. This option lets you see all course materials, submit required assessments, and get a final grade. This also means that you will not be able to purchase a Certificate experience.

What will I get if I subscribe to this Specialization?

When you enroll in the course, you get access to all of the courses in the Specialization, and you earn a certificate when you complete the work. Your electronic Certificate will be added to your Accomplishments page - from there, you can print your Certificate or add it to your LinkedIn profile. If you only want to read and view the course content, you can audit the course for free.

What is the refund policy?

If you subscribed, you get a 7-day free trial during which you can cancel at no penalty. After that, we don’t give refunds, but you can cancel your subscription at any time. See our full refund policy Opens in a new tab .

Is financial aid available?

Yes. In select learning programs, you can apply for financial aid or a scholarship if you can’t afford the enrollment fee. If fin aid or scholarship is available for your learning program selection, you’ll find a link to apply on the description page.

Excel Charting Basics: How to Make a Chart and Graph

By Joe Weller | January 22, 2018 (updated May 3, 2022)

Share on Facebook
Share on LinkedIn

Link copied

Organizations of all sizes and across all industries use Excel to store data. While spreadsheets are crucial for data management, they are often cumbersome and don’t provide team members with an easy-to-read view into data trends and relationships. Excel can help to transform your spreadsheet data into charts and graphs to create an intuitive overview of your data and make smart business decisions.

In this article, we’ll give you a step-by-step guide to creating a chart or graph in Excel 2016. Additionally, we’ll provide a comparison of the available chart and graph presets and when to use them, and explain related Excel functionality that you can use to build on to these simple data visualizations.

What Are Graphs and Charts in Excel?

Charts and graphs in Microsoft Excel provide a method to visualize numeric data. While both graphs and charts display sets of data points in relation to one another, charts tend to be more complex, varied, and dynamic.

People often use charts and graphs in presentations to give management, client, or team members a quick snapshot into progress or results. You can create a chart or graph to represent nearly any kind of quantitative data — doing so will save you the time and frustration of poring through spreadsheets to find relationships and trends.

It’s easy to create charts and graphs in Excel, especially since you can also store your data directly in an Excel Workbook, rather than importing data from another program. Excel also has a variety of preset chart and graph types so you can select one that best represents the data relationship(s) you want to highlight.

Tired of static spreadsheets? We were, too.

Although Microsoft Excel is familiar, you were never meant to manage work with it. See how Excel and Smartsheet compare across five factors: work management, collaboration, visibility, accessibility, and integrations.

Watch the full comparison

When to Use Each Chart and Graph Type in Excel

Excel offers a large library of charts and graphs types to display your data. While multiple chart types might work for a given data set, you should select the chart that best fits the story that the data is telling.

In Excel 2016, there are five main categories of charts or graphs:

Column Charts: Some of the most commonly used charts, column charts, are best used to compare information or if you have multiple categories of one variable (for example, multiple products or genres). Excel offers seven different column chart types: clustered, stacked, 100% stacked, 3-D clustered, 3-D stacked, 3-D 100% stacked, and 3-D, pictured below. Pick the visualization that will best tell your data’s story.

Bar Charts: The main difference between bar charts and column charts are that the bars are horizontal instead of vertical. You can often use bar charts interchangeably with column charts, although some prefer column charts when working with negative values because it is easier to visualize negatives vertically, on a y-axis.

Pie Charts: Use pie charts to compare percentages of a whole (“whole” is the total of the values in your data). Each value is represented as a piece of the pie so you can identify the proportions. There are five pie chart types: pie, pie of pie (this breaks out one piece of the pie into another pie to show its sub-category proportions), bar of pie, 3-D pie, and doughnut.

Line Charts: A line chart is most useful for showing trends over time, rather than static data points. The lines connect each data point so that you can see how the value(s) increased or decreased over a period of time. The seven line chart options are line, stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line.

Scatter Charts: Similar to line graphs, because they are useful for showing change in variables over time, scatter charts are used specifically to show how one variable affects another. (This is called correlation.) Note that bubble charts, a popular chart type, is categorized under scatter. There are seven scatter chart options: scatter, scatter with smooth lines and markers, scatter with smooth lines, scatter with straight lines and markers, scatter with straight lines, bubble, and 3-D bubble.

There are also four minor categories. These charts are more use case-specific:

Area: Like line charts, area charts show changes in values over time. However, because the area beneath each line is solid, area charts are useful to call attention to the differences in change among multiple variables. There are six area charts: area, stacked area, 100% stacked area, 3-D area, 3-D stacked area, and 3-D 100% stacked area.

Stock: Traditionally used to display the high, low, and closing price of stock, this type of chart is used in financial analysis and by investors. However, you can use them for any scenario if you want to display the range of a value (or the range of its predicted value) and its exact value. Choose from high-low-close, open-high-low-close, volume-high-low-close, and volume-open-high-low-close stock chart options.

Surface: Use a surface chart to represent data across a 3-D landscape. This additional plane makes them ideal for large data sets, those with more than two variables, or those with categories within a single variable. However, surface charts can be difficult to read, so make sure your audience is familiar with them. You can choose from 3-D surface, wireframe 3-D surface, contour, and wireframe contour.

Radar: When you want to display data from multiple variables in relation to each other use a radar chart. All variables begin from the central point. The key with radar charts is that you are comparing all individual variables in relation to each other — they are often used for comparing strengths and weaknesses of different products or employees. There are three radar chart types: radar, radar with markers, and filled radar.

Another popular chart is a waterfall chart, which is essentially a series of column graphs that show positive and negative changes over time. There is no Excel preset for a waterfall chart, but you can download a template to help make the process easier. For a full walkthrough, read How to Create a Waterfall Chart in Excel .

‌ Download Waterfall Chart Template in Excel

Top 5 Excel Chart and Graph Best Practices

Although Excel provides several layout and formatting presets to enhance the readability of your charts, you can maximize their effectiveness with other methods. Below are the top five best practices to make your charts and graphs as useful as possible:

Make It Clean: Cluttered graphs — those with excessive colors or texts — can be difficult to read and aren’t eye catching. Remove any unnecessary information so your audience can focus on the point you’re trying to get across.

Choose Appropriate Themes: Consider your audience, the topic, and the main point of your chart when selecting a theme. While it can be fun to experiment with different styles, choose the theme that best fits your purpose.

Use Text Wisely: While charts and graphs are primarily visual tools, you will likely include some text (such as titles or axis labels). Be concise but use descriptive language, and be intentional about the orientation of any text (for example, it’s irritating to turn your head to read text written sideways on the x-axis).

Place Elements Intelligently: Pay attention to where you place titles, legends, symbols, and any other graphical elements. They should enhance your chart, not detract from it.

Sort Data Prior to Creating the Chart: People often forget to sort data or remove duplicates before creating the chart, which makes the visual unintuitive and can result in errors.

How to Chart Data in Excel

To generate a chart or graph in Excel, you must first provide the program with the data you want to display. Follow the steps below to learn how to chart data in Excel 2016.

Step 1: Enter Data into a Worksheet

Open Excel and select New Workbook .
Enter the data you want to use to create a graph or chart. In this example, we’re comparing the profit of five different products from 2013 to 2017. Be sure to include labels for your columns and rows. Doing so enables you to translate the data into a chart or graph with clear axis labels. You can download this sample data below.

‌ Download Column Chart Practice Data

Step 2: Select Range to Create Chart or Graph from Workbook Data

Highlight the cells that contain the data you want to use in your graph by clicking and dragging your mouse across the cells.
Your cell range will now be highlighted in gray and you can select a chart type.

In the following section, we’ll walk you through the specifics of creating a clustered column chart in Excel 2016.

How to Make a Chart in Excel

After you input your data and select the cell range, you’re ready to choose the chart type. In this example, we’ll create a clustered column chart from the data we used in the previous section.

Step 1: Select Chart Type

Once your data is highlighted in the Workbook, click the Insert tab on the top banner. About halfway across the toolbar is a section with several chart options. Excel provides Recommended Charts based on popularity, but you can click any of the dropdown menus to select a different template.

Step 2: Create Your Chart

From the Insert tab, click the column chart icon and select Clustered Column .

Excel will automatically create a clustered chart column from your selected data. The chart will appear in the center of your workbook.
To name your chart , double click the Chart Title text in the chart and type a title. We’ll call this chart “Product Profit 2013 - 2017.”

We’ll use this chart for the rest of the walkthrough. You can download this same chart to follow along.

‌ Download Sample Column Chart Template

There are two tabs on the toolbar that you will use to make adjustments to your chart: Chart Design and Format . Excel automatically applies design, layout, and format presets to charts and graphs, but you can add customization by exploring the tabs. Next, we’ll walk you through all the available adjustments in Chart Design .

Step 3: Add Chart Elements

Adding chart elements to your chart or graph will enhance it by clarifying data or providing additional context. You can select a chart element by clicking on the Add Chart Element dropdown menu in the top left-hand corner (beneath the Home tab).

To Display or Hide Axes:

To Add Axis Titles:

To Remove or Move Chart Title:

Click None to remove chart title.
Click Above Chart to place the title above the chart. If you create a chart title, Excel will automatically place it above the chart.
Click Centered Overlay to place the title within the gridlines of the chart. Be careful with this option: you don’t want the title to cover any of your data or clutter your graph (as in the example below).

To Add Data Labels:

To Add a Data Table:

None is the default setting, where the data table is not duplicated within the chart.

Note: If you choose to include a data table, you’ll probably want to make your chart larger to accommodate the table. Simply click the corner of your chart and use drag-and-drop to resize your chart.

To Add Error Bars:

To Add Gridlines:

To Add a Legend:

To Add Lines: Lines are not available for clustered column charts. However, in other chart types where you only compare two variables, you can add lines (e.g. target, average, reference, etc.) to your chart by checking the appropriate option.

To Add a Trendline:

Note: You can create separate trendlines for as many variables in your chart as you like. For example, here is our chart with trendlines for Product A and Product C.

How to make multiple trendlines on Excel chart

To Add Up/Down Bars: Up/Down Bars are not available for a column chart, but you can use them in a line chart to show increases and decreases among data points.

Step 4: Adjust Quick Layout

How to change layout of elements in Excel chart

Step 5: Change Colors

The next dropdown menu in the toolbar is Change Colors . Click the icon and choose the color palette that fits your needs (these needs could be aesthetic, or to match your brand’s colors and style).

Step 6: Change Style

For cluster column charts, there are 14 chart styles available. Excel will default to Style 1, but you can select any of the other styles to change the chart appearance. Use the arrow on the right of the image bar to view other options.

Step 7: Switch Row/Column

In this example, switching the row and column swaps the product and year (profit remains on the y-axis). The chart is now clustered by product (not year), and the color-coded legend refers to the year (not product). To avoid confusion here, click on the legend and change the titles from Series to Years .

Step 8: Select Data

Step 9: Change Chart Type

You can also save your chart as a template by clicking Save as Template …

Step 10: Move Chart

Step 11: Change Formatting

Step 12: Delete a Chart

To delete a chart, simply click on it and click the Delete key on your keyboard.

How to Make a Graph in Excel

Because graphs and charts serve similar functions, Excel groups all graphs under the “chart” category. To create a graph in Excel, follow the steps below.

Select Range to Create a Graph from Workbook Data

Now you have a graph. To customize your graph, you can follow the same steps explained in the previous section. All functionality for creating a chart remains the same when creating a graph.

How to Create a Table in Excel

If you don’t need to visualize your data, you can create a table in Excel instead. There are two ways to format a data set as a table: manually, or with the Format as a Table button.

Manually: In this example, we manually added data and formatted as a table by including column and row names (products and years).
Use Excel’s Format as Table Preset: You can also input raw data (numbers without any column and row names).

Related Excel Functionality

Excel is one of the most widely-used tools across all industries and types of organizations. Charts and graphs are great tools to visualize your work, but there are many ways to elevate your data in Excel.

We’ve created a list of additional features that allow you to do more with your data:

Pivot Tables: A pivot table allows you to extract certain columns or rows from a data set and reorganize or summarize that subset in a report. This is useful tool if you only want to view a particular segment of a large data set, or if you want to view data from a new perspective.
Conditional Formatting : A powerful feature that allows you to apply specific formatting to certain cells in your spreadsheet. You can use conditional formatting to highlight key pieces of information, track changes, see deadlines, and perform many other data organization functions.
Dashboards: A powerful, visual reporting feature that pulls data from one or several datasets to display key performance indicators (KPIs), project or task status, and several other metrics. This gives the audience (team members, executives, clients, etc.) a snapshot view into project progress without surfacing private information.
Collaborative Charts: To avoid version control issues and allow multiple team members to edit a chart simultaneously, you’ll want to use a collaborative chart tool. The desktop versions of Excel do not support this, but you can use Excel for Office 365, Microsoft’s cloud-based web application, or several other online chart tools.
Data Series: A data series is any row or column stored in your workbook that you’ve plotted into a chart or graph. Once you’ve created your chart, you can add additional data series to it: Simply highlight the additional data you want to add and the chart will automatically update.

Make Better Decisions, Faster with Charts in Smartsheet

Empower your people to go above and beyond with a flexible platform designed to match the needs of your team — and adapt as those needs change.

The Smartsheet platform makes it easy to plan, capture, manage, and report on work from anywhere, helping your team be more effective and get more done. Report on key metrics and get real-time visibility into work as it happens with roll-up reports, dashboards, and automated workflows built to keep your team connected and informed.

When teams have clarity into the work getting done, there’s no telling how much more they can accomplish in the same amount of time. Try Smartsheet for free, today.

Discover why over 90% of Fortune 100 companies trust Smartsheet to get work done.

Certifications

Data Visualization with Excel Charts: A Comprehensive Guide

December 27, 2022

What are Excel Charts?

An Excel chart is a graphical representation of data. It helps provides an opportunity to tell a compelling story to the audience through data visualization.

Data visualization helps to understand the data patterns and connections. It is important to focus on the information that matters and tell a story derived from the data.

According to Cambridge dictionary [1] , a chart is a drawing that shows information in a simple way, often using lines and curves to show amounts.

Quite often we spend a lot of time on data analysis . Being able to choose the right type of chart to give a correct representation of data is equally important as doing the right analysis. If the chart type is not correct, it becomes difficult to say a convincing story.

In this article, we will discuss the different types of charts and type of data they are suitable for.

A bar chart is the most commonly used chart type. As the name suggest, it is composed of a series of bars illustrating a variables development. It is easy to read and understand. A Bar chart is frequently used to compare data across time frames (like revenue/ profit increase from one time to another).

These charts are great to use when we want them to track the development of one or two variables. For example, if we want to compare the revenues vs previous year.

Limitation of Bar chart is that we cannot use it to compare more than 1 or 2 variables. If we have multiple variables on the Bar chart, it is difficult to understand the trend of the variables.

Do you know 10 most used Financial Functions in Excel

Stacked Column Chart

A stacked column chart is most commonly used to compare data over time. In this chart, the data series is stacked on top of each other in vertical columns. Such charts are also easy to create and understand.

An example for usage of this chart is for comparing revenue contribution by segments.

The limitation of this chart is that it may appear complex and difficult to read if there are more than 4 or 5 segments or if the data series is too long.

To get over such limitation, we can also show data with 100% stacked column chart (as shown below).

Know more about 80 PowerPoint Shortcut Keys

A line chart is a chart showing a line or multiple lines showing development of single or multiple variables over time. It allows us to track development of several variables over time in a single chart. Such charts are easy to read and understand.

Such charts are most used while comparing share price movements for multiple companies, indices etc. over both short term and long term. See Example of a line chart showing share price movement of top three Private Banks in India.

Line charts are also used by analysts to understand the correlation between two or more different variables. Like movement in Oil Prices with Oil Rig deployment or comparing base metal prices with consumption/demand etc.

Such charts are useful to analyze data for a time series. It cannot be used if we do not have data for a time series/period.

Combination Chart

Combination charts is created by combining different type of charts. Like a Combo Bar chart and a line chart . Such charts are useful when we want to analyze data trend over a time.

For example, comparing the revenue growth with margins. Such charts are useful when we want to compare data across multiple variables.

A Pie chart is a circular chart showing slices (breakup) of data by proportions. The entire pie chart represents 100% of data while the slices represent proportions of data. Sum of all slices should be equal to the Total Pie (100%).

For example, if we want to see revenue contribution by different segments of a company, we can use a Pie chart. A larger Pie size for a segment means, the larger share of revenue contribution by that segment.

The limitation of using a Pie chart is that it can only be used for one single variable . We cannot use the pie chart when we have more than one variable or if we want to see change in variable from one period to the other.

Learn 100+ Useful Excel Short Cuts

Scatter Plot

These charts are often used in field of statistics and data science. It consists of data points depicted across two variables (x-y axis).

A scatter plot chart is used when we want to see the pattern/ correlation between two variables. For example, if we want to see a correlation between Customer satisfaction index and Total revenues, we can show the correlation through a scatter plot chart.

Relationships between variables could differ by situations. For Example, it could have positive or negative correlation or have linear or non-linear correlation.

A scatter plot chart cannot be used when we don’t have 2 or more variables or when want to see the time pattern.

Waterfall Charts

A waterfall chart is a great way to show variance analysis from one point to the other. These charts help easily visualize the drivers which are responsible for the change. For Example. how the profit or revenue of a company grew from one period to the other.

We can’t use the waterfall chart for data that does not explain the drivers for change from one period to another. Therefore, if we do not have the intermediary steps or variables leading to change in performance from one period to another, we cannot use the waterfall chart.

Learn to use charts in business presentation and make it effective.

Tree Map Charts

A Tree map chart is not often used but is an effective chart for data visualization. It is a hierarchical chart. It is useful for visualization of data which has multiple level of segmentation. It gives a pictorial representation of how data is spread across various segments/ sub-segments. In a Tree Map, each segment is represented by a large rectangle and segments of data are represented by smaller rectangles. Tree map charts are great highlighting the contribution of each items to the whole.

A Tree Map chart cannot be used if the data set is not hierarchical meaning when data cannot be divided into segments and sub segments. It also cannot be used if we want to see the time pattern for multiple variables in a data set.

Bubble Chart

A Bubble chart is an extension of a scatter plot chart, where in addition to a scatter plot (2 dimensions represented by the x axis and y axis), the size of the bubble represents the 3 rd variable.

It is often used effectively to show the performance comparison across different segments of a company or across different peers.

In the above chart, shows the performance comparison across different companies in an industry. The x-axis shows the revenue growth, the y-axis shows the margin and the size of the bubbles represent the revenues for different Companies.

A bubble chart also cannot be used when we don’t have 2 or more variables or when want to see the time pattern.

Histogram Chart

A Histogram chart is similar to a Bar Chart and is used when we want to show the frequency distribution of data. For this chart, we can group data into different bins, and we can easily summarize where the data concentration is.

This chart is very effective when we want to show the distribution of data within a range. For example, Data showing average Weight of population between in a society.

A Histogram Chart cannot be used, If the data set has multiple categories of variables.

While data analysis is important, the correct presentation of data is equally important. Therefore, It is necessary to choose the right type of chart for data visualization, so that we are able to say a compelling story to the audience.

To improve your data analysis and visualization skills, please visit our online course Data Analyst Skills Training in Excel . This program will help you Go from beginner to an Expert in Data Analytics and Visualization skills in Excel? Join this program to create reports quickly through hands-on projects.

Keep Learning.

[1] https://dictionary.cambridge.org/dictionary/english/chart

Share This Post:

6 thoughts on “data visualization with excel charts: a comprehensive guide”.

[…] charts are a type of data visualization tool that display changes in a value over time. They are particularly useful for showing the impact […]

[…] To learn more about how to use these different types of charts, check out our comprehensive guide on making charts with Excel. […]

Say, you got a nice post.Much thanks again. Keep writing.

I am so grateful for your article post.Really thank you! Really Great.

Your writing is both informative and engaging. This post was a fantastic read!

What’s Going down i’m new to this, I stumbled upon thisI have discovered It positively helpful and it has aided me out loads.I’m hoping to contribute & help other customers like its helped me.Good job.

Add a Comment Cancel reply

Save my name, email, and website in this browser for the next time I comment.

Get A 5X Raise In Salary

Reset Password

Insert/edit link.

Enter the destination URL

Or link to existing content

Generative AI
Office Suites
Collaboration Software
Productivity Software
Augmented Reality
Emerging Technology
Remote Work
Artificial Intelligence
Operating Systems
IT Leadership
IT Management
IT Operations
Cloud Computing
Computers and Peripherals
Data Center
Enterprise Applications
Vendors and Providers
Enterprise Buyer’s Guides
United States
Netherlands
United Kingdom
New Zealand
Newsletters
Foundry Careers
Terms of Service
Privacy Policy
Cookie Policy
Copyright Notice
Member Preferences
About AdChoices
E-commerce Affiliate Relationships
Your California Privacy Rights

Our Network

Network World

How to use Excel as a data visualization tool

Microsoft excel has more dataviz capabilities than you may realize. find out how to make your data stand out with charts, pivottables, sparklines, slicers and more..

Everyone knows Microsoft Excel as a number cruncher, but there’s a whole lot more to the popular spreadsheet program. Just as Excel can perform basic data analysis functions , it has a surprising number of data visualization tools under the hood.

“Excel isn’t explicitly a data visualization tool, it’s a spreadsheet,” says Excel developer and consultant Jon Peltier . However, one of Excel’s strengths is its flexibility, he adds. “It’s kind of a Swiss Army knife of software. In addition to formulas, Excel has structures in the worksheet that give it additional functionality for preparing data for analysis in data visualization.”

Read on for a guide to using Excel’s dataviz tools to present your data. We use the Office 365 version of Excel for Windows in the descriptions below; some features may not be available if you use a different version.

The most obvious data visualization option in Excel is its array of charts. Over the years, Microsoft has added more and more chart types to Excel, from basics like bar, line, and pie charts to more advanced styles like scatter and surface charts. Excel 2016 for Windows saw the addition of six new chart types including treemap, sunburst and waterfall, while Excel 2019 added funnel and map charts. (All the new chart types are available to Office 365 subscribers.) See “ Excel 2016 and 2019 cheat sheet ” for details about the new chart types.

A treemap chart shows categories and subcategories of data as different-sized blocks. (Click image to enlarge it.)

To add any kind of chart to a worksheet, select the data you want to chart, click the Insert tab in the Ribbon and click the Recommended Charts button. The Recommended Charts feature looks at your data and suggests a few chart types that will display that data in most impactful way. If none of its suggestions suits you, click the All Charts tab and choose the type you want. To add a trendline to the chart, click Design > Add Chart Element > Trendlines and choose a trendline style.

PivotTables and PivotCharts

A basic Excel feature from which many other visualizations derive is the PivotTable. Doing proper data visualization in Excel starts with knowing how to use PivotTables and understanding how they work, says Peltier.

PivotTables provide a summary of your data, whereby thousands of rows and dozens of columns can be packaged into an easily readable form so you can highlight information and spot trends. They also let you group your data together in different ways so you can draw conclusions more easily, such as how the bottom line for a stat would look without a certain source.

PivotTables are particularly useful if you have long rows or columns of values. Excel has a basic summarization feature, the SUM (=Sum) function, which is great if you want to total up a dozen rows and a few columns. But what if you have hundreds of rows and many columns? PivotTables are like an amped-up SUM function.

The “pivot” part of a PivotTable is the ability to rotate (or pivot) the data in the table in order to view it from a different perspective. Again, this reflects greater data manipulation than the SUM function. For example, if you wanted to look at annual sales but exclude a month or quarter, you can do it with a click or two in a PivotTable. SUM requires more manipulation.

Three PivotTables on the right sum up the raw data on the left. (Click image to enlarge it.)

In the image above, you see the original data on the left, with columns for Product, Origin, Day sold, Price, Unit Sales, and Dollar Sales. There are three PivotTables next to the columns comparing sales of each fruit from imported vs. local sources, total sales for each type of fruit, and how each fruit sold by day of the week. This type of insight is simply impossible with the SUM function.

It’s easy enough to create a PivotTable. Select the rows and columns, with the label at the top of each column, from your data set. Click the Ribbon’s Insert tab, and the first two options are PivotTable and Recommended PivotTables.

With Recommended PivotTables, Excel looks at the contents of the sheet and presents you with a list of thumbnails for various PivotTables generated from all of the categories in your spreadsheet. Select the PivotTable you want and click OK.

The alternative way requires a few more clicks but lets you build a table using your own criteria, in case the manner you’d like to sort the data isn’t among the Recommended options. Click the PivotTable button and you will be asked to select the data range, the rows and columns to be included in the table. Click and drag to cover the range you want and click OK.

Whichever method you use to create the table, Excel will open a new worksheet with the table in it. If you like, you can copy it into the original data sheet as I did in the screenshot. Once you select OK, the PivotTable Fields pane will open on the right side of the screen; here you can select the fields by which to filter your data. If you chose a Recommended PivotTable, you can tweak it by checking and unchecking the fields in the PivotTable Fields pane.

PivotTables have the side benefit of finding sloppiness in your spreadsheets. For example, I do all my personal accounting in Excel, income and expense. In generating a PivotTable for 2018 I saw I had my estimated federal taxes entered as both “Fed” and “Feds,” while deposits were listed either under the publication name or “Deposit.” This forced me to go back and clean up the entries for a more accurate view in the table.

PivotCharts are visual representations of PivotTables. To insert a PivotChart, go to the Ribbon’s Insert tab and select PivotChart. It will ask you to select the data source range, just like a PivotTable, and pick the fields to chart.

PivotCharts let you visually represent the data from a PivotTable. (Click image to enlarge it.)

Excel lets you apply filters to PivotTables to focus on a smaller portion of their data. Slicers are canned filters that slice up data by categories, letting you quickly turn on and off categories to drill down into specific information.

Slicers are inserted from an existing PivotTable, so you must make the table first. From there, go to the Insert tab and click the Slicer button. Using our fruit sales spreadsheet, you can create slicers for specificity on day, location, or unit sales. In this example, we select Product, Origin, and Day.

From there, three boxes pop up for each category. Under Product, select Apples, under Origin select Imported, and leave Day alone. Now we know the total number of imported apples sold that week and on which days. (In the example below, the days shown are automatically generated from the data. Imported apples were sold only on Wednesdays, Thursdays and Saturdays, so the slicer lists those days.)

Slicers let you quickly filter data by category, such as the product, place of origin, and day sold. (Click image to enlarge it.)

Unlike filters, slicers can work across multiple PivotTables. And that is where Slicers work best, on multiple PivotTables. For a single PivotTable, a slicer is not really different from a filter.

You connect two or more PivotTables by first creating them, then right-clicking on any of them and selecting Report Connections. This pops up a box with the connected tables. Initially, only one will be checked, the first one you made. By checking the others, you build the connection.

So again using our fruit spreadsheet, by connecting two PivotTables I can report on product and origin, and thus get a lump sum unit sales figure for locally grown fruit.

Build slicer connections between PivotTables for even more specificity. (Click image to enlarge it.)

A sparkline is a tiny basic chart that appears within a single Excel cell. You can make a line, column, or win/loss sparkline within the cell and use it to highlight trends in your data.

For example, “you might have a dashboard showing how five divisions of your company have done every quarter over the last two years,” says Peltier. If you want a visual representation of how each division has performed, he adds, “you won’t have room on the screen for five regular charts. That type of information is nicely suited to sparklines.”

Sparklines are mini-charts contained in single cells that highlight data trends, as shown at lower right. (Click image to enlarge it.)

You can create a sparkline from data in a table or a PivotTable. Select the data range, such as sales for a 12-month period, then go to the Insert tab on the Ribbon and select Line, Column or Win/Loss from the Sparklines section. Confirm the source data range, choose a destination cell for the sparkline, and click OK.

Remember that a sparkline goes in a single cell, so you’ll want to select data for it from a single row or a single column. That said, you can add multiple sparklines simultaneously by choosing a data range from multiple rows or columns, and choosing the corresponding number of destination cells for the sparklines. In other words, if you want three sparklines to show trends for three rows of data, select three destination cells.

Conditional formatting

Scanning over rows and columns of numbers in a spreadsheet can make your eyes glaze over. To make it easier to spot important data points and trends, you can use Excel’s conditional formatting feature to a table, PivotTable, or range of cells. For instance, you can tell Excel to make high values green and low values red; it will automatically lighten or darken the red and green color depending on how severe the high or low.

Three types of conditional formatting in a single cell: color, bar, and arrow. (Click image to enlarge it.)

Conditional formatting can also be used to highlight profit and loss, giving a good visualization of profit and loss over time, among regions, for different products, and so on. In addition to changing cells’ colors, you can use data bars or a set of icons.

To add conditional formatting, select the data you want to format, select the Home tab on the Ribbon and click the Conditional Formatting button in the Styles group. There you can choose which cells to highlight (e.g., greater than or less than a value, or the top or bottom 10%) and/or apply data bars, color scales or icon sets to the whole selection. The best part is you can add multiple formats. I added a light-to-dark color scale, arrows to indicate an increase or decrease from the previous month, and bars comparing the data in the cells to the single largest monthly entry.

An ancillary feature to conditional formatting and sparklines is adding tiny graphics to a spreadsheet using the Webdings font. “You can’t link to formulas but you can add pizzazz to a workbook by inserting a static picture to describe what your data is,” says Ken Puls, who runs the Excelguru Blog . This helps you summarize information so a manager can get in, see the overall trend and get out, Puls says.

The embedded icons can demonstrate things like upward and downward trends, profit and loss, or status level. To add an icon to a worksheet, go to the Insert tab on the Ribbon, select Icons in the Illustrations group, choose an icon and click Insert. The image appears on your worksheet, with “handles” so you can easily resize, rotate or move it.

Linked data types

Microsoft has recently rolled out an Excel feature it calls linked data types to Office 365 subscribers. Instead of simply numbers or strings, cells can now hold values that linked to an online data source — in this case, Microsoft’s Bing search engine.

Right now there are only two data types: stocks and geography. Excel recognizes a stock ticker symbol or state name in a cell and pulls in relevant data from Bing in real time. Cells with one of these data types include an icon indicating their status, and clicking on the icon brings up a card with relevant information.

A stock cell, for instance, can show all kinds of information about the company and its stock performance, such as current price, 52-week high and low, P/E, and more. And a geography cell can show anything from a location’s population to its power consumption.

Click a linked geography cell to see all sorts of information about that location. (Click image to enlarge it.)

Adding stock cells is quite simple. Enter in a bunch of stock tickers or company names in a single column, then highlight them and click on the Data tab in the Ribbon. In the middle of the Ribbon you’ll see a Data Types group with buttons for Stocks and Geography.

Click Stocks, and your tickers will be replaced with the names of the companies and an icon to the left of each name. Selecting a company will pop up a menu where you can select the types of data you want the company’s card to show.

Geography is also interesting. As with stocks, you enter a bunch of locations — states, countries, cities, etc. — in a single column, select the cells and click Data > Geography. You need to be precise, though. Entering GA, CA, MN and NC and then clicking the Geography button did not yield Georgia, California, Minnesota, and North Carolina for me, but Gabon, Canada, Mongolia, and New Caledonia. Geo, Calif, Minn, and N.C. did the trick. The full state names also work.

Office 365 subscribers can perform a variety of stock and location comparisons and analysis in Excel. (Click image to enlarge it.)

The array of information about locations that Bing offers is astounding, covering basics like population and GDP but also esoterics like fossil fuel consumption and life expectancy.

Excel’s new linked data types are available only to Office 365 subscribers. Microsoft says it hopes to roll out additional linked data types, but so far the company hasn’t said when.

Introduced in Excel 2016 for Windows (it’s not available for Macs), the Forecast Sheet feature uses AI to project values in the future based on past performance. It works with historical, time-based data and creates a chart with high, low, and median projections. The more data you have to work with, the more precise a prediction it makes.

Using historical data, Excel can project where your data points are headed. (Click image to enlarge it.)

To create a forecast, you must use time-based historical data. Put your cursor in one of the data cells, go to the Data tab on the Ribbon, and in the Forecast group, select Forecast Sheet. On the screen that appears, choose the type of chart (line or bar), the forecast end date, and other options. Click Create, and Excel creates a new worksheet with a chart that contains both the historical data and the predicted values, which are shaded differently than the historical data. This is considered especially helpful for seasonal data.

Quick Analysis

One last tip: If you use Excel 2016, Excel 2019, or Excel for Office 365 in Windows, you can use the Quick Analysis feature to quickly apply an array of visualizations, including conditional formatting, charts, PivotTables and sparklines, to selected data. Highlight the cells you want to analyze, and the Quick Analysis icon pops up near the lower-right corner of the highlighted data. Click the icon and choose the type of visualization you want to add.

The Quick Analysis feature gives you a variety of tools for analyzing your data instantly. (Click image to enlarge it.)

Data visualization in Excel has never been easier.

More from this author

The desktop processor market is suddenly hot again, excel: your entry into the world of data analytics, wikileaks reveals potent windows malware from the cia, most popular authors.

Show me more

Google consolidates ai teams into deepmind to scale capacity.

Zoom offers AI-based updates to its Workplace collaboration space

Report: Microsoft-OpenAI ownership might get conditional OK from EU regulators

Why the world will be wearing more technology in the future

Is AR/VR set for another growth spurt? | Ep. 143

Voice cloning, song creation via AI gets even scarier

Is AR/VR set for another growth spurt?

Excel Tutorial: How To Show Data In Graph In Excel

Introduction.

Visual representation of data is essential for understanding and interpreting complex information. One effective way to display data visually is by creating graphs in Excel. In this tutorial, we will provide a brief overview of how to show data in a graph in Excel, allowing you to effectively convey your data to others.

Key Takeaways

Visual representation of data is essential for understanding complex information.
Excel offers a variety of chart types to effectively display data.
Step-by-step instructions on creating basic charts in Excel are provided.
Customizing and formatting charts in Excel allows for clearer communication of data.
Exploration of Excel's Chart Tools tab can enhance chart creation and customization.

Understanding Chart Types in Excel

Charts are a great way to visually represent your data in Excel. With a variety of chart types available, it's important to understand the different options and how to choose the right one for your specific data.

Column Charts:

Bar charts:, line charts:, pie charts:, area charts:, scatter plots:, combo charts:.

It's important to choose the right chart type to effectively convey your data. Consider the following factors when selecting a chart type:

Data Relationship: Determine if your data shows a comparison, distribution, trend, or relationship.
Data Visualization: Think about which chart type will best represent your data visually.
Audience: Consider who will be viewing the chart and what type of visualization will be most easily understood by them.
Data Complexity: For complex data sets, a combination of chart types may be necessary to effectively display all the information.

How to Create a Basic Chart in Excel

Excel makes it easy to create visually appealing charts and graphs to represent your data. Follow this step-by-step guide to learn how to display your data in a graph in Excel.

To create a basic chart in Excel, you first need to select the data that you want to include in the chart. Follow these steps:

Select your data: Click and drag to select the range of cells that contain the data you want to include in the chart.
Navigate to the Insert tab: Once your data is selected, go to the Insert tab in the Excel ribbon.
Choose a chart type: From the Charts group, select the type of chart you want to create. For a basic chart, you can choose from options like a column, line, or pie chart.

Once you have selected your data, you can insert and customize a basic chart in Excel using the following steps:

Insert the chart: After choosing a chart type, click on the specific chart style you want to use, and Excel will insert the chart into your worksheet.
Customize the chart: With the chart selected, you can customize its appearance and layout using the Chart Tools and Format tabs that appear when the chart is selected. You can change the chart title, axis labels, colors, and more to suit your preferences.
Move and resize the chart: To adjust the placement and size of the chart, click and drag it to reposition it within the worksheet. You can also click and drag the sizing handles to adjust the dimensions of the chart.

Customizing Charts in Excel

When creating charts in Excel, it's important to not only display the data accurately but also to make the charts visually appealing and easy to understand. Excel provides a range of tools for customizing charts to suit your specific needs.

Changing chart titles and axis labels

Adding or editing chart titles: To add a title to your chart, select the chart and go to the "Chart Tools" tab. Click on "Chart Layout" and choose "Chart Title" to add or edit the chart title.
Modifying axis labels: To change the labels on the x-axis or y-axis, select the axis and right-click to access the format options. From here, you can change the title, font, and orientation of the axis labels.
Customizing data labels: You can also customize the data labels in the chart by selecting the data series and using the format options to change the font, color, and position of the labels.

Formatting chart elements such as colors and styles

Changing chart colors: Excel allows you to easily change the colors of your chart elements. Select the chart and go to the "Chart Tools" tab, then choose "Design" and click on "Change Colors" to pick a new color scheme for your chart.
Adjusting chart styles: You can also modify the overall style of the chart by selecting a different style from the "Chart Styles" options in the "Design" tab. This allows you to change the overall look of the chart with just a few clicks.
Customizing chart elements: If you want to further customize specific elements of the chart, such as the plot area or the gridlines, you can right-click on the element and choose "Format" to access a wide range of formatting options.

Adding Data Series to a Chart

When creating a graph in Excel, it's important to be able to add multiple data series to the chart to effectively visualize the data. Let's take a look at how to do this and some tips for formatting and distinguishing between different data series.

How to add multiple data series to a chart

Select the chart: First, select the chart where you want to add multiple data series. This will ensure that any changes or additions are made to the correct chart.
Add data series: To add a new data series to the chart, go to the "Design" tab on the Ribbon and click on "Select Data." This will open a new window where you can add, edit, or remove data series.
Edit data series: In the "Select Data Source" window, you can click on "Add" to add a new data series or "Edit" to change the name, values, and other attributes of an existing data series.
Finalize changes: After adding or editing data series, click "OK" to apply the changes to the chart. You should now see the new data series added to the graph.

Tips for formatting and distinguishing between different data series

Use different colors: To make it easier to distinguish between different data series, use different colors for each series. This will make it easier for viewers to identify and compare the different sets of data.
Use different line styles or markers: In addition to colors, you can also use different line styles or markers for each data series. This can further enhance the visual distinction between the series in the chart.
Provide a legend: If you have multiple data series in the chart, it's a good idea to include a legend that explains what each series represents. This will help viewers understand the data being presented.
Consider using trendlines: If you have multiple data series that represent trends or patterns, consider adding trendlines to each series to show the direction and magnitude of the data.

Using Excel's Chart Tools

Excel's Chart Tools tab provides a variety of options and features for creating and customizing graphs to display your data in a visually appealing and informative way.

A. Introduction to the Chart Tools tab in Excel

The Chart Tools tab in Excel is where you can find all the necessary tools for creating, formatting, and customizing charts, graphs, and other visual representations of your data. It is essential for anyone looking to present data in a more visually engaging manner.

B. Explanation of various chart options and features available within the Chart Tools tab

1. Chart Types

Excel offers various chart types such as column, bar, line, pie, scatter, and more, allowing you to choose the most suitable option for representing your data.

2. Chart Layouts

The Chart Tools tab provides a range of predefined chart layouts to help you quickly format and arrange your chart elements.

3. Chart Styles

Excel's Chart Tools tab comes with different styles and color schemes that you can apply to your chart to make it visually appealing and coherent with your presentation or report.

4. Chart Elements

You can customize various chart elements such as titles, data labels, axes, and legends to provide context and clarity to your audience.

5. Chart Formatting

Excel's Chart Tools tab enables you to format specific chart elements, including colors, fonts, borders, and effects, to match the overall look and feel of your presentation or report.

6. Chart Analysis

The Chart Tools tab also includes features for analyzing and visualizing data trends, patterns, and relationships within your graph through trendlines, data labels, and other analysis tools.

As we wrap up this Excel tutorial on showing data in a graph, it's important to summarize what we've covered. We've learned how to select data and create various types of charts, including bar graphs, line graphs, and pie charts. We've also explored how to customize the appearance of our charts to make them more visually appealing and easier to understand.

I encourage you to put your newfound skills to practice and create charts using your own data. As you become more comfortable with creating basic charts, I also encourage you to explore advanced chart features in Excel, such as trendlines, combination charts, and sparklines. The world of data visualization is vast, and Excel offers powerful tools to help you bring your data to life.

Immediate Download

MAC & PC Compatible

Free Email Support

Related aticles

Mastering Excel Dashboards for Data Analysts

The Benefits of Excel Dashboards for Data Analysts

Exploring the Power of Real-Time Data Visualization with Excel Dashboards

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

How to Connect Your Excel Dashboard to Other Platforms for More Focused Insights

Unlocking the Potential of Excel's Data Dashboard

10 Keys to Designing a Dashboard with Maximum Impact in Excel

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Essential Features for Data Exploration in Excel Dashboards

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Interpreting Excel Dashboards: From Data to Action

Unleashing the Power of Excel Dashboards

Different Approaches to Excel Dashboard Design and Development

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Best Excel Dashboard Tips for Smarter Data Visualization

Leverage Your Data with Excel Dashboards

How to Create Effective Dashboards in Microsoft Excel

Crafting the Perfect Dashboard for Excel

Dashboards in Excel: Managing Data Analysis and Visualization

An Introduction to Excel Dashboards

Best Practices for Designing an Insightful Excel Dashboard

How to Create an Effective Excel Dashboard

Choosing a selection results in a full page refresh.

Excel Data Analysis Tutorial
Excel Data Analysis - Home
Data Analysis - Overview
Data Analysis - Process
Excel Data Analysis - Overview
Working with Range Names
Cleaning Data with Text Functions
Cleaning Data Contains Date Values
Working with Time Values
Conditional Formatting
Subtotals with Ranges
Quick Analysis
Lookup Functions
PivotTables
Data Visualization
Data Validation
Financial Analysis
Working with Multiple Sheets
Formula Auditing
Advanced Data Analysis
Advanced Data Analysis - Overview
Data Consolidation
What-If Analysis
What-If Analysis with Data Tables
What-If Analysis Scenario Manager
What-If Analysis with Goal Seek
Optimization with Excel Solver
Importing Data into Excel
Exploring Data with PivotTables
Exploring Data with Powerpivot
Exploring Data with Power View
Exploring Data Power View Charts
Exploring Data Power View Maps
Exploring Data PowerView Multiples
Exploring Data Power View Tiles
Exploring Data with Hierarchies
Aesthetic Power View Reports
Key Performance Indicators
Excel Data Analysis Resources
Excel Data Analysis - Quick Guide
Excel Data Analysis - Resources
Excel Data Analysis - Discussion
Selected Reading
UPSC IAS Exams Notes
Developer's Best Practices
Questions and Answers
Effective Resume Writing
HR Interview Questions
Computer Glossary

Excel Data Analysis - Data Visualization

You can display your data analysis reports in a number of ways in Excel. However, if your data analysis results can be visualized as charts that highlight the notable points in the data, your audience can quickly grasp what you want to project in the data. It also leaves a good impact on your presentation style.

In this chapter, you will get to know how to use Excel charts and Excel formatting features on charts that enable you to present your data analysis results with emphasis.

Visualizing Data with Charts

In Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of the data, in which the data is represented by symbols such as bars in a Bar Chart or lines in a Line Chart. Excel provides you with many chart types and you can choose one that suits your data or you can use the Excel Recommended Charts option to view charts customized to your data and select one of those.

Refer to the Tutorial Excel Charts for more information on chart types.

In this chapter, you will understand the different techniques that you can use with the Excel charts to highlight your data analysis results more effectively.

Creating Combination Charts

Suppose you have the target and actual profits for the fiscal year 2015-2016 that you obtained from different regions.

We will create a Clustered Column Chart for these results.

As you observe, it is difficult to visualize the comparison quickly between the targets and actual in this chart. It does not give a true impact on your results.

A better way of distinguishing two types of data to compare the values is by using Combination Charts. In Excel 2013 and versions above, you can use Combo charts for the same purpose.

Use Vertical Columns for the target values and a Line with Markers for the actual values.

Click the DESIGN tab under the CHART TOOLS tab on the Ribbon.
Click Change Chart Type in the Type group. The Change Chart Type dialog box appears.

Click Combo.

Change the Chart Type for the series Actual to Line with Markers. The preview appears under Custom Combination.

Your Customized Combination Chart will be displayed.

As you observe in the chart, the Target values are in Columns and the Actual values are marked along the line. The data visualization has become better as it also shows you the trend of your results.

However, this type of representation does not work well when the data ranges of your two data values vary significantly.

Creating a Combo Chart with Secondary Axis

Suppose you have the data on the number of units of your product that was shipped and the actual profits for the fiscal year 2015-2016 that you obtained from different regions.

If you use the same combination chart as before, you will get the following −

In the chart, the data of No. of Units is not visible as the data ranges are varying significantly.

In such cases, you can create a combination chart with secondary axis, so that the primary axis displays one range and the secondary axis displays the other.

Click the INSERT tab.
Click Combo in Charts group.
Click Create Custom Combo Chart from the drop-down list.

The Insert Chart dialog box appears with Combo highlighted.

For Chart Type, choose −

Line with Markers for the Series No. of Units
Clustered Column for the Series Actual Profits
Check the Box Secondary Axis to the right of the Series No. of Units and click OK.

A preview of your chart appears under Custom Combination.

Your Combo chart appears with Secondary Axis.

You can observe the values for Actual Profits on the primary axis and the values for No. of Units on the secondary axis.

A significant observation in the above chart is for Quarter 3 where No. of Units sold is more, but the Actual Profits made are less. This could probably be assigned to the promotion costs that were incurred to increase sales. The situation is improved in Quarter 4, with a slight decrease in sales and a significant rise in the Actual Profits made.

Discriminating Series and Category Axis

Suppose you want to project the Actual Profits made in Years 2013-2016.

Create a clustered column for this data.

As you observe, the data visualization is not effective as the years are not displayed. You can overcome this by changing year to category.

Remove the header year in the data range.

Now, year is considered as a category and not a series. Your chart looks as follows −

Chart Elements and Chart Styles

Chart Elements give more descriptions to your charts, thus helping visualizing your data more meaningfully.

Click the Chart

Three buttons appear next to the upper-right corner of the chart −

For a detailed explanation of these, refer to Excel Charts tutorial.

Click Chart Elements.
Click Data Labels.

Click Chart Styles
Select a Style and Color that suits your data.

You can use Trendline to graphically display trends in data. You can extend a Trendline in a chart beyond the actual data to predict future values.

Data Labels

Excel 2013 and later versions provide you with various options to display Data Labels. You can choose one Data Label, format it as you like, and then use Clone Current Label to copy the formatting to the rest of the Data Labels in the chart.

The Data Labels in a chart can have effects, varying shapes and sizes.

It is also possible to display the content of a cell as part of the Data Label with Insert Data Label Field.

Quick Layout

You can use Quick Layout to change the overall layout of the chart quickly by choosing one of the predefined layout options.

Click the chart.
Click the DESIGN tab under CHART TOOLS.
Click Quick Layout.

Different possible layouts will be displayed. As you move on the layout options, the chart layout changes to that particular option.

Select the layout you like. The chart will be displayed with the chosen layout.

Using Pictures in Column Charts

You can create more emphasis on your data presentation by using a picture in place of columns.

Click on a Column on the Column Chart.

In the Format Data Series, click on Fill.

Select Picture.

Under Insert picture from, provide the filename or optionally clipboard if you had copied an image earlier.

The picture you have chosen will appear in place of columns in the chart.

You might have to present customer survey results of a product from different regions. Band Chart is suitable for this purpose. A Band Chart is a Line Chart with an added shaded area to display the upper and lower boundaries of groups of data.

Suppose your customer survey results from the east and west regions, month wise are −

Here, in the data < 50% is Low, 50% - 80% is Medium and > 80% is High.

With Band Chart, you can display your survey results as follows −

Create a Line Chart from your data.

Change the chart type to −

East and West Series to Line with Markers.
Low, Medium and High Series to Stacked Column.

Your chart looks as follows.

Click on one of the columns.
Change gap width to 0% in Format Data Series.

You will get Bands instead of columns.

To make the chart more presentable −

Add Chart Title.
Adjust Vertical Axis range.
Change the colors of the bands to Green-Yellow-Red.
Add Labels to bands.

The final result is the Band Chart with the defined boundaries and the survey results represented across the bands. One can quickly and clearly make out from the chart that while the survey results for the region West are satisfactory, those for the region East have a decline in the last quarter and need attention.

Thermometer Chart

When you have to represent a target value and an actual value, you can easily create a Thermometer Chart in Excel that emphatically shows these values.

With Thermometer chart, you can display your data as follows −

Arrange your data as shown below −

Select the data.
Create a Clustered Column chart.

As you observe, the right side Column is Target.

Click on a Column in the chart.
Click on Switch Row/Column on the Ribbon.

Right click on the Target Column.
Click on Format Data Series.
Click on Secondary Axis.

As you observe the Primary Axis and Secondary Axis have different ranges.

Right click the Primary Axis.
In the Format Axis options, under Bounds, type 0 for Minimum and 1 for Maximum.
Repeat the same for Secondary Axis.

Primary and Secondary Axis have Different Ranges

Both Primary Axis and Secondary Axis will be set to 0% - 100%. The Target Column hides the Actual Column.

Right click the visible column (Target)
No fill for FILL
Solid line for BORDER
Blue for Color

Axis → Primary Horizontal
Axis → Secondary Vertical
Chart Title
In the chart, right click on Primary Vertical Axis
In Format Axis options, click on TICK MARKS
For Major type, select Inside

Right click on the Chart Area.
No line for BORDER

Resize the chart area, to get the shape of a thermometer.

You got your thermometer chart, with the actual value as against target value being shown. You can make this thermometer chart more impressive with some formatting.

Insert a rectangle shape superimposing the blue rectangular part in the chart.
Gradient fill for FILL
Linear for Type
180 0 for Angle
Set the Gradient stops at 0%, 50% and 100%.
For the Gradient stops at 0% and 100%, choose the color black.
For the Gradient stop at 50%, choose the color white.

Insert an oval shape at the bottom.
Format shape with same options.

The result is the Thermometer Chart that we started with.

Gantt Chart

A Gantt chart is a chart in which a series of horizontal lines shows the amount of work done in certain periods of time in relation to the amount of work planned for those periods.

In Excel, you can create a Gantt chart by customizing a Stacked Bar chart type so that it depicts tasks, task duration, and hierarchy. An Excel Gantt chart typically uses days as the unit of time along the horizontal axis.

Consider the following data where the column −

Task represents the Tasks in the project
Start represents number of days from the Start Date of the project
Duration represents the duration of the Task

Note that Start of any Task is Start of previous Task + Duration. This is the case when the Tasks are in hierarchy.

Create Stacked Bar Chart.

Right-click on Start Series.
In Format Data Series options, select No fill.

Right-click on Categories Axis.
In Format Axis options, select Categories in reverse order.

Adjust the range
Major Tick Marks at 5 day intervals
Minor Tick Marks at 1 day intervals
Format Data Series to make it look impressive
Give a Chart Title

Waterfall Chart

Waterfall Chart is one of the most popular visualization tools used in small and large businesses. Waterfall charts are ideal for showing how you have arrived at a net value such as net income, by breaking down the cumulative effect of positive and negative contributions.

Excel 2016 provides Waterfall Chart type. If you are using earlier versions of Excel, you can still create a Waterfall Chart using Stacked Column Chart.

The columns are color coded so that you can quickly tell positive from negative numbers. The initial and the final value columns start on the horizontal axis, while the intermediate values are floating columns. Because of this look, Waterfall Charts are also called Bridge Charts.

Consider the following data.

Prepare the data for Waterfall Chart

Ensure the column Net Cash Flow is to the left of the Months Column (This is because you will not include this column while creating the chart)

Add 2 columns – Increase and Decrease for positive and negative cash flows respectively

Add a column Start - the first column in the chart with the start value in the Net Cash Flow

Add a column End - the last column in the chart with the end value in the Net Cash Flow

Add a column Float – that supports the intermediate columns

Compute the values for these columns as follows

In the Float column, insert a row in the beginning and at the end. Place n arbitrary value 50000. This just to have some space to the left and right of the chart

The data will be as follows.

Select the cells C2:H18 (Exclude Net Cash Flow column)
Create Stacked Column Chart

Right click on the Float Series.
Click Format Data Series.

Right click on Negative Series.
Select Fill Color as Red.

Right click on Positive Series.
Select Fill Color as Green.

Right click on Start Series.
Select Fill Color as Grey.
Right click on End Series.
Delete the Legend.

Right click on any Series
In Format Data Series options, select Gap Width as 10% under Series Options

Give the Chart Title. The Waterfall Chart will be displayed.

Sparklines are tiny charts placed in single cells, each representing a row of data in your selection. They provide a quick way to see trends.

You can add Sparklines with Quick Analysis tool.

Select the data for which you want to add Sparklines.
Keep an empty column to the right side of the data for the Sparklines.

Click SPARKLINES . The chart options displayed are based on the data and may vary.

Click Line . A Line Chart for each row is displayed in the column to the right of the data.

PivotCharts

Pivot Charts are used to graphically summarize data and explore complicated data.

A PivotChart shows Data Series, Categories, and Chart Axes the same way a standard chart does. Additionally, it also gives you interactive filtering controls right on the chart so that you can quickly analyze a subset of your data.

PivotCharts are useful when you have data in a huge PivotTable, or many complex worksheet data that includes text and numbers. A PivotChart can help you make sense of this data.

You can create a PivotChart from

A PivotTable.
A Data Table as a standalone without PivotTable.

PivotChart from PivotTable

To create a PivotChart follow the steps given below −

Click the PivotTable.
Click ANALYZE under PIVOTTABLE TOOLS on the Ribbon.
Click on PivotChart. The Insert Chart dialog box appears.

Select Clustered Column from the option Column.

Click OK. The PivotChart is displayed.

The PivotChart has three filters – Region, Salesperson and Month.

Click the Region Filter Control option. The Search Box appears with the list of all Regions. Check boxes appear next to Regions.

Select East and South options.

The filtered data appears on both the PivotChart and the PivotTable.

PivotChart without a PivotTable

You can create a standalone PivotChart without creating a PivotTable.

Click the Data Table.
Click the Insert tab.
Click PivotChart in Charts group. The Create PivotChart window appears.
Select the Table/Range.
Select the Location where you want the PivotChart to be placed.

You can choose a cell in the existing worksheet itself, or in a new worksheet. Click OK.

An empty PivotChart and an empty PivotTable appear along with the PivotChart Field List to build the PivotChart.

Choose the Fields to be added to the PivotChart

Arrange the Fields by dragging them into FILTERS, LEGEND (SERIES), AXIS (CATEGORIES) and VALUES

Use the Filter Controls on the PivotChart to select the Data to be placed on the PivotChart

Excel will automatically create a coupled PivotTable.

PRO Courses Guides New Tech Help Pro Expert Videos About wikiHow Pro Upgrade Sign In
EDIT Edit this Article
EXPLORE Tech Help Pro About Us Random Article Quizzes Request a New Article Community Dashboard This Or That Game Popular Categories Arts and Entertainment Artwork Books Movies Computers and Electronics Computers Phone Skills Technology Hacks Health Men's Health Mental Health Women's Health Relationships Dating Love Relationship Issues Hobbies and Crafts Crafts Drawing Games Education & Communication Communication Skills Personal Development Studying Personal Care and Style Fashion Hair Care Personal Hygiene Youth Personal Care School Stuff Dating All Categories Arts and Entertainment Finance and Business Home and Garden Relationship Quizzes Cars & Other Vehicles Food and Entertaining Personal Care and Style Sports and Fitness Computers and Electronics Health Pets and Animals Travel Education & Communication Hobbies and Crafts Philosophy and Religion Work World Family Life Holidays and Traditions Relationships Youth
Browse Articles
Learn Something New
Quizzes Hot
This Or That Game New
Train Your Brain
Explore More
Support wikiHow
About wikiHow
Log in / Sign up
Computers and Electronics
Spreadsheets
Microsoft Excel

How to Create a Graph in Excel

Last Updated: March 13, 2024 Fact Checked

This article was co-authored by wikiHow staff writer, Jack Lloyd . Jack Lloyd is a Technology Writer and Editor for wikiHow. He has over two years of experience writing and editing technology-related articles. He is technology enthusiast and an English teacher. This article has been fact-checked, ensuring the accuracy of any cited facts and confirming the authority of its sources. This article has been viewed 1,850,210 times. Learn more...

If you're looking for a great way to visualize data in Microsoft Excel, you can create a graph or chart. Whether you're using Windows or macOS, creating a graph from your Excel data is quick and easy, and you can even customize the graph to look exactly how you want. This wikiHow tutorial will walk you through making a graph in Excel.

Bar - Displays one or more sets of data using vertical bars. Best for listing differences in data over time or comparing two similar sets of data.
Line - Displays one or more sets of data using horizontal lines. Best for showing growth or decline in data over time.
Pie - Displays one set of data as fractions of a whole. Best for showing a visual distribution of data.

For example, to create a set of data called "Number of Lights" and another set called "Power Bill", you would type Number of Lights into cell B1 and Power Bill into C1
Always leave cell A1 blank.

For example, if you're comparing your budget with your friend's budget in a bar graph, you might label each column by week or month.
You should add a label for each row of data.

You can press the Tab ↹ key once you're done typing in one cell to enter the data and jump one cell to the right if you're filling in multiple cells in a row.

A bar graph resembles a series of vertical bars.
A line graph resembles two or more squiggly lines.
A pie graph resembles a sectioned-off circle.

You can also hover over a format to see a preview of what it will look like when using your data.

On a Mac, you'll instead click the Design tab, click Add Chart Element , select Chart Title , click a location, and type in the graph's title. [2] X Research source

Windows - Click File , click Save As , double-click This PC , click a save location on the left side of the window, type the document's name into the "File name" text box, and click Save .
Mac - Click File , click Save As... , enter the document's name in the "Save As" field, select a save location by clicking the "Where" box and clicking a folder, and click Save .

Community Q&A

You can change the graph's visual appearance on the Design tab. Thanks Helpful 0 Not Helpful 0
If you don't want to select a specific type of graph, you can click Recommended Charts and then select a graph from Excel's recommendation window. Thanks Helpful 0 Not Helpful 0

Some graph formats won't include all of your data, or will display it in a confusing manner. It's important to choose a graph format that works with your data. Thanks Helpful 3 Not Helpful 4

↑ https://www.youtube.com/watch?v=L5LBon70v_o
↑ https://www.youtube.com/watch?v=pxilXspS2UA

About This Article

1. Enter the graph’s headers. 2. Add the graph’s labels. 3. Enter the graph’s data. 4. Select all data including headers and labels. 5. Click Insert . 6. Select a graph type. 7. Select a graph format. 8. Add a title to the graph. Did this summary help you? Yes No

Send fan mail to authors

Is this article up to date?

Featured Articles

Watch Articles

Terms of Use
Privacy Policy
Do Not Sell or Share My Info
Not Selling Info

Keep up with tech in just 5 minutes a week!

Using Graphics To Represent Data Series In Excel

Key takeaway:.

Using graphics in Excel is a powerful tool for visualizing and understanding complex data series.
Creating charts in Excel offers a flexible and customizable way to represent data series, including the ability to add and format data series within a chart and customize chart options to fit your needs.
Conditional formatting is another effective way to represent data series in Excel, allowing for easy identification of important data points and trends. However, it is important to consider the pros and cons of using graphics, including the advantages of improving comprehension and the potential disadvantages of overcomplicating the data.

Are you struggling to make sense of complex data sets in Excel? With the right visuals, you can turn your data into a clear and understandable format. Learn how to use graphics to represent data series in Excel.

Overview of Graphics in Excel

The role of graphics in Excel is significant in visually representing data series. Excel provides a range of graphic tools to make the presentation of data more appealing, precise, and clear. Despite this, an overview of graphics in Excel is necessary to make wise use of its resources. Understanding the basics of graphics in Excel , such as chart types, data layout, and data labels, can enhance the user’s capacity for data presentation.

Customizing visual data representation in Excel is a crucial aspect of making impactful decisions. It is vital to use the right graphics tools to create polished and appealing visuals that cater to specific users’ needs. Understanding the fundamental concept of organizing and summarizing data in Excel can help produce more meaningful graphics.

It is essential to note that graphs in Excel have limitations, such as single or categorical datasets, minimal label customization, and difficulty interpreting complex data analysis. It is therefore important to choose the best graph type for the specific task at hand , keeping in mind the message being communicated.

For instance, a bank’s HR department presented employee’s performance data in a stacked bar graph; the message conveyed was unclear as it was difficult to analyze the relationships between parameters. After some review, the HR department discovered that a scatter plot better represented the data clusters in a helpful, digestible way. By changing the graph type, the message was effectively communicated, and clear decisions were made.

Using Charts to Represent Data Series

In Excel, you need to know how to use charts to represent data series . Mastering three techniques is key: creating a chart, adding/formatting data series and customizing options . Here, we will discuss these techniques and how to use them to make your charts look great and tell the story of your data.

Creating a Chart

When it comes to visually representing data series, creating a chart can be an effective way to do so. Through the use of graphics in Excel, data can be easily and accurately illustrated for presentation or analysis purposes.

To create a visual representation of data in Excel without using words like HTML tags or table formats , one can use <td>, <tr> and <table> tags to create columns and headers that display real data. This provides a quick and easy way to organize information that is both aesthetically pleasing and functional.

Creating a chart in Excel has many benefits beyond the obvious aesthetic appeal. Unlike other methods of displaying information, charts allow for an easier comparison between variables as well as highlighting trends over time or different categories. Furthermore, charts help make the information more accessible and digestible for those who may have difficulty interpreting numbers on their own.

Don’t miss out on the opportunity to present your data in a clear and intuitive manner – create impressive charts with powerful graphical representations to convey your message with confidence. By utilizing all the tools available in Excel, anyone can transform raw data into meaningful insights that are sure to impress clients or colleagues alike.

Get your data series looking sharp and chart-topping with these formatting tips.

Adding and Formatting Data Series in a Chart

When it comes to utilizing charts in Excel, it is paramount to have a clear understanding of how to add and format data series. To ensure that your chart communicates information effectively, you must invest effort into creating an organized and well-structured presentation of the gathered data.

Here’s a 5-step guide on how you can easily adjust and format the Data Series within your chart:

Select the chart type
Enter data using spreadsheet cells.
Select the entire range additional labels.
Add axis titles if necessary.
Format data series by changing color themes

Apart from colors, other important features such as markers, line styles, fill patterns amongst others could be added visually via this technique.

It is fundamental to remember that each new data set added will generate its own distinctive color automatically. However, you may choose to switch up these default color settings for specific ones exclusive to your selection or preference.

This best practice has been followed across industries and has stood the test of time; companies and organizations ranging from startups onward have since leveraged this method of Representing Data Series in Excel.

As with most successful technological advancements, this technique came into use over time due to various usability studies conducted regarding customer satisfaction and effective communication. This has led over time to widespread adoption across businesses for analysis purposes.

Why settle for a default chart when you can customize it to match your mood and personality?

Customizing Chart Options

One can modify chart options to represent data series more effectively. This involves customizing the appearance of the chart, labels, axes and adding elements.

By choosing a chart type and modifying its properties, one can change the look and feel of the graph significantly. Font types, axis labels and numbers all can be tailored to suit one’s needs. One can also add vertical or horizontal error bars or target lines to compare performance against set targets.

To gain more clarity for complex datasets, manipulating chart elements such as legends, gridlines and data labels play a crucial role. By hiding or showing them selectively, one can avoid cluttering while highlighting key messages effectively.

To use charts to their full potential, it is useful to practice by creating many charts in different styles with varying sets of data. Careful attention should be paid to selecting appropriate colors that highlight crucial information in the dataset without overpowering them visually. By utilizing these methods creatively, valuable insights can be gained from even challenging sets of unfamiliar data.

Who needs a mood ring when you can just use conditional formatting to see how your data series is feeling?

Using Conditional Formatting to Represent Data Series

Try using conditional formatting in Excel to make your data series stand out! It lets you apply and customize various formatting options. This helps emphasize patterns and trends. To customize the formatting options and make your data look visually appealing, here’s what you need to do:

Apply conditional formatting to data
Then customize the formatting options

Once done, your data will look great !

Applying Conditional Formatting to Data

Applying Formatting Rules to Data Series in Excel

To improve data representation, conditional formatting can be applied. This feature enables users to highlight specific values with customized colors and styles.

Here’s a 3-Step Guide for Applying Formatting Rules to Data:

Select the cells that you want to format.
Click “Conditional Formatting” on the Home tab of the Excel Ribbon.
Apply a formatting rule – choose either Custom Format or one of the preset rules. Custom Rule allows you to set conditions such as cell values, colors and fonts.

Excel provides unique features like icon set, gradient fill, and data bars that can enhance your data series representation to facilitate quick analysis.

Fear Of Missing Out:

Data visualization is an important skillset required in most professional positions across various fields; applying conditional formatting can make your spreadsheet more insightful and impressive for stakeholders interpreting your work.

Conditional formatting: because sometimes colors speak louder than words.

Customizing Conditional Formatting Options

Customizing how conditional formatting works can improve the representation of data series in Excel. By adjusting options, you can focus on certain elements to highlight trends, relationships, or anomalies. Here’s a step-by-step guide to help:

Go to the Home tab .
Select Conditional Formatting from the Styles section.
Pick one of the rules to customize (e.g. Color Scales), click Manage Rules , and modify as desired.
Click OK to save changes and apply new formatting to your data series.

This approach allows further thought and creativity when presenting data trends graphically. It enables users to select exactly what they want highlighted on each visualization and, therefore, adjust how much attention viewers may pay to various factors.

One other possibility is tailoring images or graphs displaying data series by customizing labels or titles. Combine such relative ease with Microsoft’s powerful software capable of performing complex computations in its data tables offers valuable advantages for numerous application fields.

For instance, an otherwise unknown American economist named Edward Tufte revolutionized that very field in his fight against deceptive charts: designs deliberately confusing through lack of clarity – “chartjunk.” Tufte pioneered contemporary visualization principles focused on conveying accurate information with efficient visual methods – still reliant on skillful customization.

Bearing this in mind provides inspiration for greater customization efforts on Excel graphics functions; versatile tools that incorporate these vibrant principles while also offering detailed adjustments.

Can’t decide if using graphics to represent data series is worth it? Let’s weigh the pros and cons, but either way, your bar chart jokes are sure to be a hit.

Pros and Cons of Using Graphics to Represent Data Series

Weigh the pros and cons of using graphics to represent data series in Excel. Explore the advantages and disadvantages. We will go through two sub-sections: merits and demerits. This will help you decide how to visualize your Excel data best.

Advantages of Using Graphics

Using graphical representations in data series provides benefits such as clear communication of complex information, making large amounts of data easily understandable. Furthermore, graphics help represent important trends and correlations that might have gone unnoticed. They also allow for the identification of outliers and patterns that may be impossible to detect through plain text. Additionally, using appealing graphics that are professionally designed can enhance the visual appeal and aid in persuading viewers to take action based on captured data.

Moreover, graphics have been proven to improve memory retention and make it easier to recall important information. Using visuals also saves time while presenting a large amount of data to a vast audience who may not have a lot of time. Lastly, visually representing data makes it easier for others to understand the information presented even if they are not familiar with graphs.

Truthfully speaking, I once had to present a statistical report on various marketing campaigns at an industry conference. After examining vast arrays of reports from different individual sources my team worked with, I found it hard to summarize all the statistical terms until I used visualization tactics and diagrams that enabled me to deliver an impactful presentation effortlessly .

Sorry pie chart , it’s not you, it’s me. I just can’t trust you to accurately represent my data.

Disadvantages of Using Graphics

The downsides of utilizing graphics to represent data series are worth considering before opting for them. It is crucial to weigh the advantages and drawbacks before making a decision. Here are some potential issues that may arise when using graphics:

Overcomplication of Data: Graphics can sometimes complicate data instead of simplifying it, making it hard for viewers to understand.
Distortion of Information: Visual representations such as graphs can distort data based on how they are presented.
Incorrect Interpretation Areas with Low Values: Scaling or zooming graphics can make differences between areas with low values appear significant, skewing interpretations of data.
A Professional Designer’s Fee May Be Required: Employing someone who knows how to create informative and professional graphs can be expensive.
Data Security Concerns: Transmitting or sharing graphic files containing sensitive data could lead to security breaches if measures are not in place to protect them.
Inefficient Use of Time: Producing complex graphical illustrations can take time away from other essential aspects of data analysis, consuming valuable time preparing the presentation rather than achieving actionable insights.

It is also important to remember that these problems mentioned previously don’t outweigh the benefits that one might gain by incorporating useful graphics. However, it is necessary to weigh each application’s potential outcomes critically.

As an illustration, during a significant project with many teams collaborating in various locations worldwide, precise figures were needed quickly from different units. Unfortunately, due to difficulties understanding non-numeric forms of documentation designed by many departments within the company, drawing conclusions was challenging for independent team leads. In this case, standard visualizations would have been more universal and accurate.

Five Facts About Using Graphics to Represent Data Series in Excel:

✅ Excel offers various chart types to represent data, such as line charts, bar charts, pie charts, and scatter charts. (Source: Microsoft)
✅ Charts in Excel can be customized with various formatting options, such as colors, fonts, and labels. (Source: Excel-easy)
✅ Sparklines, mini-charts within a cell, can be used to represent data trends in a compact manner. (Source: AddictiveTips)
✅ Excel also allows for importing data from external sources, such as CSV and TXT files, to create charts. (Source: Ablebits)
✅ Using graphics to represent data in Excel can help visualize trends, patterns, and insights that may not be immediately apparent from raw data. (Source: HubSpot)

FAQs about Using Graphics To Represent Data Series In Excel

What are some benefits of using graphics to represent data series in excel.

Using graphics to represent data series in Excel can make it easier to understand and analyze data, identify patterns and trends, and communicate your findings to others. Graphics can also help you quickly identify outliers or anomalies in your data.

What types of graphics are available in Excel?

Excel offers a variety of graphics, including bar graphs, line graphs, scatter plots, histograms, and pie charts. You can also customize these graphics by changing colors, fonts, and other design elements.

How do I create a graphic in Excel?

To create a graphic in Excel, select the data that you want to include in the graphic, click on the Insert tab, and then select the type of graphic you want to create from the Graphs section. From there, you can customize the graphic by adding titles, labels, and other design elements.

How can I change the appearance of a graphic in Excel?

To change the appearance of a graphic in Excel, you can use the formatting options available in the Design and Format tabs. For example, you can change the colors of the bars or lines in a graph, add data labels, or adjust the size and position of the graphic on the worksheet.

What are some best practices for using graphics to represent data series in Excel?

Some best practices for using graphics to represent data series in Excel include selecting the appropriate type of graphic for your data, keeping the graphic simple and easy to understand, and using labels and titles to provide context. It’s also important to avoid distorting the data or using graphics that are too cluttered or confusing.

What resources are available to help me learn more about using graphics in Excel?

Excel offers a number of built-in resources, including tutorials and tips, to help you learn more about using graphics to represent data series. You can also find a wealth of information and tutorials online, including video tutorials, blogs, and user forums.

school Campus Bookshelves
menu_book Bookshelves
perm_media Learning Objects
login Login
how_to_reg Request Instructor Account
hub Instructor Commons
Download Page (PDF)
Download Full Book (PDF)
Periodic Table
Physics Constants
Scientific Calculator
Reference & Cite
Tools expand_more
Readability

selected template will load here

This action is not available.

2.4: Using Excel to Create Graphs

Last updated
Save as PDF
Page ID 22226

It can be difficult to be perfectly precise when creating graphs by hand. Also, it can be very time consuming if presented with a large amount of data. Technology can be used instead to create professional and precise graphs, and many pieces of technology also do much of the calculation, enabling the use of much larger amounts of data.

In this section, you will learn about several of the most common types of graphs and how to create them in Excel.

The first step will always be to summarize the data into a frequency distribution or a grouped frequency distribution (as appropriate). You can do this by hand, or in Excel sorting the data then using the $= \text{COUNT}()$ function, or to count the items automatically, use Pivot tables or the $= \text{COUNTIF}()$ function.

Different kinds of graphs are best used for different kinds of data. Bar Graphs and Pie Charts are used for categorical variables. Whereas Histograms and Frequency Polygons are more generally used for numerical variables.

Bar graphs are regularly used with categorical data to compare the frequency of different categories. However, they can also be used with numerical data as well.

Example $\PageIndex{1}$

Twenty people were asked their favorite colors and their responses are shown below. Construct a bar graph in Excel that shows the frequency of each color.

Enter the data into a single column in Excel, each color should be in its own cell.

Create a frequency distribution. Each color will be a category. Then count up how many times that color appears. If you want, you can automatically do this in Excel using a pivot table or using the $= \text{COUNTIF}()$ function. Another great option is to sort data entered already in Excel so it is easier to count.

Enter the frequency table into Excel. At this point, the top of your spreadsheet may look something like this.

Then highlight the frequency distribution, and go to Insert, then in the Charts section click to Insert a Bar or Column Chart.

Once that button has been selected, this is the option menu that pops up.

The top three options under 2-D Bar are the most common to use. The top left one, Clustered Column, will give a basic bar chart or a grouped bar chart if you're using more than one variable. The top middle one, Stacked Column, is used only if you're using more than one variable, it shows the bars in a stacked format. the top right one, 100% Stacked Column, is used only if you're using more than one variable, it shows stacked bars with sections given as a percent to compare parts of a whole.

Occasionally, the 3-D Bar options are used for effect. You should typically use the vertical column options instead of the horizontal options.

Choose the Clustered Column for this example and out pops our graph.

Many options can be edited such as the title, the color of the bars, axis descriptions, etc. The default title (if you don't highlight the headings in your frequency distribution) is "Chart Title" which should always be changed to something more meaningful.

Pareto Graphs

Pareto graphs are a special kind of bar chart that order the bars largest to smallest.

Example $\PageIndex{2}$

Use the same data from the last example to create a Pareto Graph in Excel.

Just like in the previous example, you need to start with a frequency distribution. But this time, you need to sort the frequency distribution by frequency.

Make sure to highlight the entire frequency distribution, not just the frequency column so that the correct frequencies stay with the correct colors.

On the Home ribbon, select Sort & Filter, then Custom Sort.

Then create a bar chart using the new sorted frequency distribution, following the same steps from the previous example.

Always be sure to have a more descriptive title than "Chart title".

Pie charts display data in a circle, with "pie" slices for each category. For Pie charts to work, the data must total 100% so the entire pie is filled. The relative size of the slices display the percent of each category out of the whole.

Example $\PageIndex{3}$

Use the same data from the first example to create a Pie Chart in Excel.

Begin by creating a frequency distribution of the data in Excel, just like in Example 1.

Then highlight the frequency distribution, and go to Insert, then in the Charts section click to Insert Pie or Doughnut Charts.

The top left option, Pie, will give a basic pie chart.

The Pie of Pie or Bar of Pie options could be used to highlight a particular section of the whole pie chart. This is used when there is a cluster of very small pie slices. Occasionally, the 3-D options or the Doughnut are used for effect.

Choose the Pie for this example and out pops our graph.

Pie charts are limited because they don't show differences between similarly sized slices. For example, in the above chart, it's difficult to see the difference between the sizes of blue, pink, purple, and red since they're close in size. Excel does give the option to label the size of the slices, which is a good option if this issue occurs.

Histograms are used for numerical data to compare the frequency of different values or classes of values. Histograms show a graphical view of the overall shape of a distribution. Histograms are very similar to bar charts, but should be displayed without space between the bars. Bar charts are mostly used for categorical data, whereas histograms are only used for numerical data.

Example $\PageIndex{4}$

Twenty Elementary Statistics students were asked their heights and their responses are shown below in inches. Construct a histogram in Excel that shows the frequency distribution of the heights.

Start the same way as creating a bar chart.

Enter the data into Excel, each number in its own cell. Create a grouped frequency distribution. We need to use a grouped frequency distribution (where there are ranges of numbers instead of individual numbers) because there are too many individual numbers. The idea is to summarize the data, not show the exact same data in graphical form. Decide on the classes, ensure the class sizes are the same for all the classes, then count up how many times that number occurs. To make it easier, sort the data, then use the $=\text{COUNT}()$ function.

After creating the frequency distribution in Excel, your spreadsheet may look something like this.

Highlight the frequency distribution in Excel, go to Insert, then in the Charts section click Insert a Bar or Col umn Chart, then choose the Clustered Column. This creates a bar chart of this data.

This is not a histogram. To make it a histogram right click on the graph, then choose Format Chart Area from the options.

Click the down arrow next to Chart Options, then choose Series "Frequency". It's called Frequency since that's what I named the column containing the frequencies. Yours may be called Series "...", where the dots could be something else.

Click on the button that looks like a bar chart, then change the Gap Width to 0%.

This gives us a correct histogram.

Excel does offer an option to automatically create a histogram, which is not recommended. If you use the automatic option, Excel overrides the classes you choose. It also displays the classes in interval notation, which is not the best option for readability. Here is the graph created using the automatic histogram function.

The resulting graph is not as informative and will not be considered correct.

There are adjustments that can be made in Excel to fix it, but I've found it much easier to create histograms by starting with a bar chart first then changing the gap width rather than attempting to fix an automatic histogram.

Frequency Polygons

Frequency polygons show the overall shape of a distribution, similarly to histograms. However, they are especially useful for comparing different variables on the same graph. These can also be called Line Graphs, or if you are looking at a relationship over time, then these are used as a Time Series graph.

Example $\PageIndex{5}$

Makila and Jannai are in a club that fundraises money by selling cookies. The sell lasts five weeks. Below is shown how many boxes they each sold each week. Create a line graph depicting this data.

Start by entering the data into Excel. After entering the data, it may look something like this.

Highlight all the data entered into Excel, go to Insert, then click the Insert Line or Area Chart option.

Choose the top left option for a Line chart.

This will create a Line chart, showing both people's sales by week.

This chart has a seperate line for each person and a meaningful title. It also has a key that shows which color line goes with which person.

From the chart, Makila made more sales overall, though less in week 3 than Jannai. Makila's sales went up and down, but Jannai's went steadily up each week.

Using Excel to create graphs can give us precise information in an easy to read format. It can be a lot faster and more precise than creating graphs by hand, and it allows the possibility of using large amounts of data.

Data will always need to be organized into a frequency distribution or a grouped frequency distribution first. Graphs should not be created based on raw data.

Excel Tutorial
Excel Formulas
Excel Shortcut Keys
Data Analysis in Excel
Formatting in Excel
Excel Workbooks
Statistical Functions

Data Visualization in Excel

Pivot Tables in Excel
MS Excel Quiz
Excel Interview Questions
Advance Excel
Data Formatting in Excel
Types of Trendlines in Excel
How to Create a Waffle Chart in Excel
How to Enter Large Numbers/Credit Card Numbers into Microsoft Excel?
Assigning Excel Macro to Objects
Radar Chart or Spider Chart in Excel
Excel Chart with Two Trendlines
Highlight Rows Based on a Cell Value in Excel
Combination Charts in Excel
How to Calculate Euclidean Distance in Excel?
How to Add Trendline in Excel Chart
How to Create a Timeline or Milestone Chart in Excel?
How to Calculate the Interquartile Range in Excel?
How to Format Chart Axis to Percentage in Excel?
How to Calculate Weighted MAPE in Excel?
Actual vs Target Chart in Excel
Custom Lists in Excel
How To Create Dot Plots In Excel?
Freezing Panes in Excel to Lock Rows and Columns

Data Visualization is the representation of data in a graphical format. It makes the data easier to understand. Data Visualization can be done using tools like Tableau, Google charts, DataWrapper, and many more. Excel is a spreadsheet that is used for data organization and data visualization as well. In this article, let’s understand Data Visualization in Excel.

Excel provides various types of charts like Column charts, Bar charts, Pie charts, Linecharts, Area charts, Scatter charts, Surface charts, and much more.

Steps for visualizing data in Excel:

Open the Excel Spreadsheet and enter the data or select the data you want to visualize.
Click on the Insert tab and select the chart from the list of charts available or the shortcut key for creating chart is by simply selecting a cell in the Excel data and press the F11 function key .

A chart with the data entered in the excel sheet is obtained.
You can design and style your chart with different types of styles and colors by selecting the design tab.
In Excel 2010, the design tab option is visible by clicking on the chart.

The Excel data is as follows:

The column chart obtained for the data by following the above steps:

When excel data contains multiple columns and if you want to make a chart for only a few columns, then select the columns required for making the chart and press the ‘F11’ function key or click on the Insert tab and select the chart from the list of charts available.

We can also select the required data columns by doing right-click on the chart and click on the ‘ select data ‘ option. Now, data can be added or removed for making the chart.

For swapping rows and columns in the chart, use the ‘S witch Row/Column ‘ option available in the design tab.

We can also make different types of charts for the same spreadsheet data by clicking on the ‘ Change Chart Type’ option in the Design tab .

To make your chart more clear, use the ‘Layout’ tab. In this tab, you can more changes to your chart like editing the chart title, adding labels to your chart, adding a legend, and adding horizontal or vertical grid lines.

Example 3: Formatting Chart Area

To format the chart area, right-click on the chart and select the option ‘ Format chart Area ‘.

The format chart area provides various options for formatting the chart like Filling the chart with patterns and solid colors, Border colors, Styles for borders, the shadow effect for your chart, and many more. Formatting makes the chart look more attractive and colorful.

Example 4: Creating Sparklines

Sparklines in Excel are small charts that fit in the data cells of the excel sheets.

Steps for Creating Spark Lines:

Select the Excel data range for sparklines as shown in the below figure.
Click on Sparklines in the Insert tab and select any one of the three sparklines.

Add the Location Range and Data Range for the creation of sparklines and click ok.

Finally, the F, G, H columns are displayed with the line, column, and Win/loss sparklines.

We can also color these sparklines by the Design tab as shown below:

We can Mark data points and also change Sparkline Color.

Please Login to comment...

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

You are using an outdated browser. Upgrade your browser today or install Google Chrome Frame to better experience this site.

IMF at a Glance
Surveillance
Capacity Development
IMF Factsheets List
IMF Members
IMF Timeline
Senior Officials
Job Opportunities
Archives of the IMF
Climate Change
Fiscal Policies
Income Inequality

Flagship Publications

Other publications.

World Economic Outlook
Global Financial Stability Report
Fiscal Monitor
External Sector Report
Staff Discussion Notes
Working Papers
IMF Research Perspectives
Economic Review
Global Housing Watch
Commodity Prices
Commodities Data Portal
IMF Researchers
Annual Research Conference
Other IMF Events

IMF reports and publications by country

Regional offices.

IMF Resident Representative Offices
IMF Regional Reports
IMF and Europe
IMF Members' Quotas and Voting Power, and Board of Governors
IMF Regional Office for Asia and the Pacific
IMF Capacity Development Office in Thailand (CDOT)
IMF Regional Office in Central America, Panama, and the Dominican Republic
Eastern Caribbean Currency Union (ECCU)
IMF Europe Office in Paris and Brussels
IMF Office in the Pacific Islands
How We Work
IMF Training
Digital Training Catalog
Online Learning
Our Partners
Country Stories
Technical Assistance Reports
High-Level Summary Technical Assistance Reports
Strategy and Policies

For Journalists

Country Focus
Chart of the Week
Communiqués
Mission Concluding Statements
Press Releases
Statements at Donor Meetings
Transcripts
Views & Commentaries
Article IV Consultations
Financial Sector Assessment Program (FSAP)
Seminars, Conferences, & Other Events
E-mail Notification

Press Center

The IMF Press Center is a password-protected site for working journalists.

Login or Register
Information of interest
About the IMF
Conferences
Press briefings
Special Features
Middle East and Central Asia
Economic Outlook
Annual and spring meetings
Most Recent
Most Popular
IMF Finances
Additional Data Sources
World Economic Outlook Databases
Climate Change Indicators Dashboard
IMF eLibrary-Data
International Financial Statistics
G20 Data Gaps Initiative
Public Sector Debt Statistics Online Centralized Database
Currency Composition of Official Foreign Exchange Reserves
Financial Access Survey
Government Finance Statistics
Publications Advanced Search
IMF eLibrary
IMF Bookstore
Publications Newsletter
Essential Reading Guides
Regional Economic Reports
Country Reports
Departmental Papers
Policy Papers
Selected Issues Papers
All Staff Notes Series
Analytical Notes
Fintech Notes
How-To Notes
Staff Climate Notes

World Economic and Financial Surveys

World economic outlook database, download weo data: april 2024 edition.

By Countries

country-level data

By Country Groups

aggregated data and commodity prices

Entire Dataset

Have a weo data question submit it through the weo question form..

The World Economic Outlook (WEO) database contains selected macroeconomic data series from the statistical appendix of the World Economic Outlook report , which presents the IMF staff's analysis and projections of economic developments at the global level, in major country groups and in many individual countries. The WEO is released in April and September/October each year.

Use this database to find data on national accounts, inflation, unemployment rates, balance of payments, fiscal indicators, trade for countries and country groups (aggregates), and commodity prices whose data are reported by the IMF.

Data are available from 1980 to the present, and projections are given for the next two years. Additionally, medium-term projections are available for selected indicators. For some countries, data are incomplete or unavailable for certain years.

Changes to the April 2024 Database

Ecuador’s fiscal sector projections are excluded from publication for 2024–29 because of ongoing program discussions.
Vietnam has been removed from the Low-Income Developing Countries (LIDCs) group and added to the Emerging Market and Middle-Income Economies (EMMIEs) group (featured in the WEO Report only).
For West Bank and Gaza, data for 2022-23 previously excluded from publication pending methodological adjustments to statistical series are now included. Projections for 2024-29 are excluded from publication due to the unusually high degree of uncertainty.

Computer Science > Machine Learning

Title: tslanet: rethinking transformers for time series representation learning.

Abstract: Time series data, characterized by its intrinsic long and short-range dependencies, poses a unique challenge across analytical applications. While Transformer-based models excel at capturing long-range dependencies, they face limitations in noise sensitivity, computational efficiency, and overfitting with smaller datasets. In response, we introduce a novel Time Series Lightweight Adaptive Network (TSLANet), as a universal convolutional model for diverse time series tasks. Specifically, we propose an Adaptive Spectral Block, harnessing Fourier analysis to enhance feature representation and to capture both long-term and short-term interactions while mitigating noise via adaptive thresholding. Additionally, we introduce an Interactive Convolution Block and leverage self-supervised learning to refine the capacity of TSLANet for decoding complex temporal patterns and improve its robustness on different datasets. Our comprehensive experiments demonstrate that TSLANet outperforms state-of-the-art models in various tasks spanning classification, forecasting, and anomaly detection, showcasing its resilience and adaptability across a spectrum of noise levels and data sizes. The code is available at \url{ this https URL }

Submission history

Access paper:.

HTML (experimental)
Other Formats

References & Citations

Google Scholar
Semantic Scholar

BibTeX formatted citation

Bibliographic and Citation Tools

Code, data and media associated with this article, recommenders and search tools.

Institution

arXivLabs: experimental projects with community collaborators

arXivLabs is a framework that allows collaborators to develop and share new arXiv features directly on our website.

Both individuals and organizations that work with arXivLabs have embraced and accepted our values of openness, community, excellence, and user data privacy. arXiv is committed to these values and only works with partners that adhere to them.

Have an idea for a project that will add value for arXiv's community? Learn more about arXivLabs .

IMAGES

Try out Data Bars in Excel for clear graphical data representation
How to Make a Line Graph in Excel Introduction A line graph is a visual
Excel Data Visualization
How to create charts on Microsoft Excel to create a visual representation
Excel Data Visualization: Mastering 20+ Charts and Graphs
Provide perfect excel sheet with graphical representation by Luqmanm84

VIDEO

Using Multiple Means of Representation in Microsoft Excel
Descriptive Analysis-How to display data in Charts inside Cells
How to use Sparkline's to show data trends in an excel sheet
Graphical representation, you’re data in excel
Create an Image of Excel Data #excel #shorts
Excel Color Scale Explained: Visualizing Data Like a Pro! in Tamil

COMMENTS

Visualizing Data in Excel
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.
Data Analysis and Visualization with Excel (A Complete Guideline)
1. Data Processing. In this section, we will learn about data processing. This is the first step of data analysis and visualization. It has the following segments. 1.1. Data Collection. First, you have to collect information. The information should be collected in a systematic way from a trustworthy source.
What to Consider When Creating Data Visualizations in Excel
Tips for Creating Visualizations in Excel. 1. Choose the Right Type of Visualization. To create an effective data visualization, it's critical to choose the right type of chart or graph. Consider the type of data you're using, the size of your dataset, and your intended audience.
Excel Data Visualization: 20 Charts, Graphs, and Plots To Master
Excel Data Visualizations. This guide will take you through the basics of Excel data visualization, ... (also known as a boxplot) is a graphical representation of the distribution of numerical data. It is a way of displaying the five-number summary of a data set, which includes the minimum, first quartile, median, third quartile, and maximum ...
Excel Tutorial: How To Create Data Visualization In Excel
Step 2: Go to the "Insert" tab and click on the "Bar Chart" button. Step 3: Choose the specific type of bar chart you want to create, such as a clustered bar chart or a stacked bar chart. Step 4: Customize your bar chart by adding a title, axis labels, and any other relevant formatting options.
Visualizing Data in Excel: A Comprehensive Guide
Formatting and Customization. Excel provides extensive formatting and customization options to refine the appearance of your charts. From adjusting axis labels to modifying colors and styles, these features allow you to create visually appealing charts that align with your brand or presentation requirements. Adding Data Labels and Annotations.
How to Visualize Data in Excel (With Examples)
3-D Chart: Includes a third axis called the depth (Z) axis. To insert charts in Excel, first select data. Then, go to the Insert tab. On the Charts section, click on the chart you wish to insert. If you're unsure of what chart to use, select your data and click on Recommend Charts from the Charts section to get suggestions.
How To Create Data Visualizations in Excel? A Complete Guide
Then, go to the "Insert" tab and click "PivotChart.". Choose the chart type you want to use (e.g., bar chart, line chart). Excel will generate a pivot chart linked to your pivot table. You can customize the chart by add the fields from the pivot table to the chart's elements to create meaningful visualizations.
Excel Visualization: A Guide to Clear Data Presentation for Beginners
To create a pie chart in Excel: Select the data you want to visualize. From the " Insert " tab, choose " Pie " from the chart options. You can customize your chart by changing the colors, adding labels, and adjusting other settings in the " Format Chart Area " pane. Here's a video guide on how to create a donut chart:
Data Visualization with Excel: Creating Compelling Charts and ...
With your data selected, click on the Insert tab in Excel, and then click on the chart type that you want to create. Excel will then create a chart or graph based on the data that you selected ...
Data Visualization in Excel Course (Macquarie University)
This course is part of the Excel Skills for Data Analytics and Visualization Specialization. When you enroll in this course, you'll also be enrolled in this Specialization. Learn new concepts from industry experts. Gain a foundational understanding of a subject or tool. Develop job-relevant skills with hands-on projects.
10 Tips for Visually Analyzing and Presenting Data in Excel
Compares individual values with the sum of those values. Comparing the sales generated by individual products with the total sales enjoyed by a firm. Whole-to-whole. Compares individual data values and sets of data values (or what Excel calls data series) to each other. Comparing sales revenues of different firms in your industry.
How to Make Charts and Graphs in Excel
Step 3: Add Chart Elements. Adding chart elements to your chart or graph will enhance it by clarifying data or providing additional context. You can select a chart element by clicking on the Add Chart Element dropdown menu in the top left-hand corner (beneath the Home tab). To Display or Hide Axes: Select Axes.
Data Visualization Best Practices in Excel
Data Bars. Data bars in data visualization provide a visual representation of data values in a range, making it easier to compare and analyze data. They are a useful tool in Excel for quickly identifying trends and patterns. Here is a table showcasing the usage of data bars: Data Type. Data Bars. Sales.
Data Visualization with Excel Charts: A Comprehensive Guide
An Excel chart is a graphical representation of data. It helps provides an opportunity to tell a compelling story to the audience through data visualization. Data visualization helps to understand the data patterns and connections. It is important to focus on the information that matters and tell a story derived from the data.
Excel Tutorial: How To Make Graphical Presentation In Excel
A. Creating a simple bar or pie chart using Excel's chart tools. Excel's chart tools make it easy to create visually stunning bar or pie charts to represent your data. Follow these simple steps: Select your data: Start by selecting the data that you want to include in your chart. This will typically be a range of cells containing your data.
How to use Excel as a data visualization tool
Highlight the cells you want to analyze, and the Quick Analysis icon pops up near the lower-right corner of the highlighted data. Click the icon and choose the type of visualization you want to ...
Excel Tutorial: How To Show Data In Graph In Excel
Navigate to the Insert tab: Once your data is selected, go to the Insert tab in the Excel ribbon. Choose a chart type: From the Charts group, select the type of chart you want to create. For a basic chart, you can choose from options like a column, line, or pie chart. B. Instructions on inserting and customizing a basic chart.
Excel Data Analysis
In Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of the data, in which the data is represented by symbols such as bars in a Bar Chart or lines in a Line Chart. Excel provides you with many chart types and you can choose one that suits your data or you can use the Excel ...
How to Create a Graph in Excel
Click and drag your mouse from the top-left corner of the data group (e.g., cell A1) to the bottom-right corner, making sure to select the headers and labels as well. 8. Click the Insert tab. It's near the top of the Excel window. Doing so will open a toolbar below the Insert tab. 9. Select a graph type.
Using Graphics To Represent Data Series In Excel
Customizing visual data representation in Excel is a crucial aspect of making impactful decisions. It is vital to use the right graphics tools to create polished and appealing visuals that cater to specific users' needs. Understanding the fundamental concept of organizing and summarizing data in Excel can help produce more meaningful graphics.
2.4: Using Excel to Create Graphs
In this section, you will learn about several of the most common types of graphs and how to create them in Excel. The first step will always be to summarize the data into a frequency distribution or a grouped frequency distribution (as appropriate). You can do this by hand, or in Excel sorting the data then using the = COUNT() = COUNT ...
Data Visualization in Excel
Data Visualization is the representation of data in a graphical format. It makes the data easier to understand. Data Visualization can be done using tools like Tableau, Google charts, DataWrapper, and many more. Excel is a spreadsheet that is used for data organization and data visualization as well.
World Economic Outlook (WEO) Database, April 2024
The World Economic Outlook (WEO) database contains selected macroeconomic data series from the statistical appendix of the World Economic Outlook report, which presents the IMF staff's analysis and projections of economic developments at the global level, in major country groups and in many individual countries.The WEO is released in April and September/October each year.
TSLANet: Rethinking Transformers for Time Series Representation Learning
Time series data, characterized by its intrinsic long and short-range dependencies, poses a unique challenge across analytical applications. While Transformer-based models excel at capturing long-range dependencies, they face limitations in noise sensitivity, computational efficiency, and overfitting with smaller datasets. In response, we introduce a novel Time Series Lightweight Adaptive ...

Data Analysis and Visualization with Excel (A Complete Guideline)

What is Data Analysis and Visualization?

Steps of Data Analysis and Visualization with Excel

1. Data Processing

1.1. Data Collection

1.2. Data Organization

1.3. Data Cleaning

2. Data Analysis

2.1. Use Excel Functions

2.2. Apply Conditional Formatting

2.3. Use Pivot Table

2.4. Sort a Product

2.5. Filter Data

2.6. Use What-If Analysis Feature

2.7. Apply Data Validation

2.8. Use Excel Table

2.9. Use Analyze Data Feature

2.10. Use Analysis ToolPak Add-in

3. Data Visualization

3.1. Excel Column Chart

3.2. Excel Bar Chart

3.3. Excel Line Chart

3.4. Excel Pie Chart

3.5. Excel Doughnut Chart

Advanced Data Visualization

2. Animated Column Chart

Things to Remember

Frequently Asked Questions

What is ExcelDemy?

Leave a reply Cancel reply

Business Insights

Creating Data Visualizations in Excel: What to Keep in Mind

Types of Data Visualizations in Excel

How to Create Data Visualizations in Excel

Tips for Creating Visualizations in Excel

2. Remove Irrelevant or Inaccurate Data

3. Provide Context For the Visualization

4. Tell A Story

Making Data-Backed Business Decisions

About the Author

Excel Data Visualization: 20 Charts, Graphs, and Plots To Master

Excel Data Visualizations

1. Bar Charts

2. Line Graphs

3. Pie Charts

4. Doughnut Charts

5. Column Charts

7 Data Analysis Tools in Excel

8 Ways to Use Excel for Business

7. Scatter Plots

8. Bubble Graphs

10. Stock Charts

11. Surface Graphs

12. Regression Plots

13. Radar Charts

14. Treemaps

15. Sunburst Charts

16. Histograms

17. Box & Whisker Plots

18. Waterfall Diagrams

19. Funnels

20. Custom Combination Charts

Related Data Visualization

Data Storytelling: Where Does Data Fit In? A Beginner’s Guide

10 Best Data Visualization Tools and Software For Your Business

What is Data Visualization and Why Do We Need It?‍

Scatterplots vs Bubble Graphs: What’s the Difference?‍

How to Create a Bar Chart in Excel

A Comprehensive Guide to Paving a Career in Data Visualization

Tips For Pursuing a Career in Data Visualization

Visualizing Data in Excel: A Comprehensive Guide

Introduction

Why Data Visualization Matters

Getting Started with Excel Charts

Advanced-Data Visualization Techniques

Best Practices for Effective Data Visualization

About the author

How to Visualize Data in Excel (With Examples)

Charts in Excel

Column Chart