how to make a graphical representation of data in excel

You can then freely customize your chart’s title.

When you have a title you like, click Home on the top navigation bar, and use the font formatting options to give your title the emphasis it deserves. See these options and my final graph below:

How to Create a Pivot Table in Excel: A Step-by-Step Tutorial

How to Use Excel Like a Pro: 29 Easy Excel Tips, Tricks, & Shortcuts

How to Sort in Excel and Keep Your Data Organized

How to Use Power Queries in Excel

How to Use the SUBTRACT Function in Excel

How to Password Protect an Excel File

How to Use the COUNTIF Function in Excel

19 Best Free Marketing & Sales Templates for Microsoft Excel

How to Use VLOOKUP Function in Microsoft Excel [+ Video Tutorial]

Merge Cells in Excel in 5 Minutes or Less

Tired of struggling with spreadsheets? These free Microsoft Excel Graph Generator Templates can help

Marketing software that helps you drive revenue, save time and resources, and measure and optimize your investments — all on one easy-to-use platform

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.

Leave a Comment Cancel reply

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

How To Create Graphs In Excel: A Step-By-Step Guide

August 9, 2024

Key Takeaways:

• Creating graphs in Excel can be done easily by following a step-by-step process, making it accessible even to beginners.
• Different types of graphs in Excel can be used to visually represent your data, such as line graphs for trends, bar graphs for comparison, pie charts for percentages, and scatter plots for correlation.
• Customizing your graph with advanced features such as data labels, trendlines, and secondary axis can enhance the clarity and effectiveness of your visual representation of data.

Do you struggle to create meaningful data visuals? Excel is a powerful tool for turning complex data into impressive graphs. This guide will walk you through the steps to produce powerful visualizations and help you showcase your data.

Beginner’s Guide to Creating Graphs in Excel

Excel is an amazing instrument! It can help you make eye-catching graphs from your data. To a beginner, it can seem intimidating to create graphs in Excel, however, with the right instructions, it can be easy. Here we will explain the steps in detail.

• First , open a fresh workbook and enter the data.
• As we go, we’ll share tips to make it more efficient. Let’s start!

Opening a new workbook and entering data

Open Microsoft Excel on your computer and click “ Blank Workbook ” to create a new spreadsheet.

In cell A1, enter the name of the variable you want to display on your graph.

For example, “ Sales “.

In cell B1, enter the corresponding values for that variable – “ 5000 “, “ 7000 “, “ 9000 “, etc.

Continue entering data for any additional variables or categories.

Structuring your spreadsheet correctly is essential .

Set up column and row headers that describe the categories and variables in your data set.

Ensure all columns have uniform data types (text or numbers) across each row.

Check for spelling errors and duplicated entries , as mistakes can lead to incorrect graphs.

Use descriptive labels for axis titles, such as “ Time ” instead of “ Years ” when measuring sales over time on an X-axis.

Now, explore the different types of graphs available in Excel for displaying various kinds of data accurately and efficiently .

Exploring the Different Types of Graphs in Excel

For data visualization in Excel, graphs are a must! In this section, we uncover the different types of graphs available and how to use them for effective communication.

We’ll start with line graphs , great for showing changes over time. Then, we’ll create bar graphs – ideal for comparing data sets. Thirdly, pie charts – perfect for displaying proportions and percentages. Finally, scatter plots – they show correlations between variables. For each type, you’ll learn how to make them step-by-step, so you can begin visualizing data in a meaningful way.

Using line graphs to show trends

Enter data into Excel with the time/date in one column and variable in another . Highlight columns and click ‘Insert’, then ‘Line Chart’. Choose a type of graph, e.g. basic line, stacked line, or markers. Customize the chart with titles, labels, legends, etc. Analyze the chart for patterns or anomalies. Share the graph by exporting or embedding.

Line graphs are great for seeing changes over time . For instance, tracking sales figures for a product over months. Also, compare multiple lines to visualize correlations. Trend analysis based on past data for future decisions. Plus, bar graphs for comparison.

Creating bar graphs for comparison

Open an Excel workbook and enter your data into the cells.

To select the cells for the bar graph, click and drag the cursor over them.

Go to the ribbon menu’s ‘Insert’ tab. Click on ‘Column’ or ‘Bar Chart’.

Use the ‘Chart Elements’, ‘Charts Styles’ menus to choose the layout, design, colors, and style.

Customize any advanced settings, such as axes, scale, alignment, or gridlines. Then save your graph.

Bar graphs are great for numeric data that represents different categories or groups. This makes it simple to compare values across multiple variables. Plus, they are clear when displaying simple data sets with high contrast between bars. When designing a chart for comparison, always consider the size of sample group, consistency of units, and context of comparisons.

Bar charts have been used for centuries. They’ve been found in ancient Egyptian hieroglyphs from around 2000 BC.

Pie charts are another effective way to represent distributions with more complex pieces than simple values. They are useful for displaying percentages and proportions.

Pie charts for percentages and proportions

Pie charts are circular graphs that show the proportions of values within a whole. In Excel, each part of the chart is represented by a slice. This can be used to compare data, like in a grocery store. For example, the popularity and contribution of apples, bananas, and oranges towards total revenue could be analyzed. This could be done with a pie chart, giving an immediate overview of which fruit is selling more than others.

Additionally, scatter plots can also be used to look for patterns within data sets.

Scatter plots for correlation

A scatter plot is a graph for showing the relationship between two sets of data. We’re talking about correlation scatter plots , which means looking at how two variables link.

To understand better, let’s make a table. For example, we have data on the number of hours studying and the test scores of ten students. Here’s the table:

Using Excel, we can create a scatter plot of this data. The horizontal axis displays the hours studied and the vertical axis is the test scores.

The graph will show if there’s a positive or negative correlation between the two variables. A positive correlation means when one increases, the other does too. A negative correlation means when one increases, the other decreases.

To emphasise the importance of understanding scatter plots for correlation, here’s a story about a friend. He used to believe all-nighters were a good way to study for exams. But after he made a scatter plot of his study hours and test scores, he realised there was no clear relationship between them. He adjusted his study habits and managed his time better.

Now that we know about scatter plots for correlation, let’s discuss Step-by-Step Guide to Creating Graphs in Excel .

Step-by-Step Guide to Creating Graphs in Excel

I work with data often . I understand how important it is to make clear and concise graphs. I’m thrilled to teach you how to make graphs in Excel ! We’ll go through the entire process:

• From selecting data,
• to choosing the type of graph,
• to customizing the appearance.

You’ll learn to make professional-looking graphs quickly . Let’s start creating effective graphs!

Selecting data and choosing the graph type

Select the data points you want to include in your graph by highlighting cells or columns. Navigate to the ‘Insert’ tab on the top menu bar and select a chart type, such as line charts, column charts, or pie charts.

A new window will appear where you can customize your chart. Edit titles, axis labels, color scheme, or chart style. Visualize the data. The raw data is still underneath the chart.

Selecting the right data and chart type is important. It helps your reader understand the data accurately and efficiently.

For example, during my summer internship at a local marketing firm, I used graphs to understand sales trends better. After creating individual graphs for each social media account’s openings and customer engagement rate, I could see which channels were most promising for promotion advertisements.

The next step is customizing the appearance with Excel’s tools. This makes it easy to identify certain aspects of the data, like color coordination, mapping changes, and overlay effect analysis .

Customizing appearance with Excel’s tools

Select the graph and head to the “Format” tab. From the list, pick “Chart Styles” . Look through the styles and choose one that meets your requirements. To adjust colors, go for “Color Saturation,” “Color Tone,” or “Colorful Gradient” . Font styles and sizes can be amended in the “Font” section.

To add titles and labels, click on ‘Add Chart Element’ drop-down menu, and select Axis. These steps will help you to polish your graph and make it easier to comprehend for your viewers.

You can also try out other options such as changing chart backgrounds, line thickness and patterns, adjusting marker sizes and shapes. Use Excel’s tools to make your graphs look professional and attractive , while precisely presenting data insights.

It is essential to tailor your visualizations to your audience’s needs in order to ensure that they understand and remember the key details. Discover further advanced features to take your graphs to the next level!

Advanced Features to Enhance Your Graph

Excel graphs are a must for data analysis! Microsoft Excel has lots of features to make custom, eye-catching charts. Let’s dive into the advanced features that can make your graphs even better.

We’ll go over:

• Adding labels for clarity
• Making trendlines to show patterns
• Combining graphs to display multiple points
• Adding a second axis for dual measurements

Get ready to create professional, info-packed Excel graphs!

Adding data labels for clarity

Add data labels to Excel graphs with these simple steps:

• Click the chart element you’d like to label.
• Go to the “Layout” tab from the Ribbon.
• Choose “Data Labels” from the “Labels” group.
• Select the radio button for the labels you’d like to show.
• Adjust size and font style.
• Position the labels clearly but without being intrusive.

Data labels make it easy to spot trends and patterns . They also help present results to unfamiliar audiences. Professionals using Excel find this handy for understanding market trends in line charts or showing demographic variance in stacked bar charts.

For example, a financial analyst tracks monthly revenue across business units. They make a stacked column chart of total sales broken down by categories. By adding data labels to the top section of each column, they can see which products contribute most to each business unit.

On to trendlines to show patterns!

Creating trendlines to show patterns

• Step 1: Click on the data series to create a trendline.
• Step 2: Click on the “Add Chart Element” option.
• Step 3: Select “Trendline” and choose the type you need.

Trendlines are great for large datasets. They can show long-term trends, seasonal cycles, or any other patterns. You can see if your data is increasing or decreasing. For example, tracking sales figures over months. A trendline helps tell if sales are going up or down.

Trendlines aren’t definitive proof. Other factors may be involved. To confirm, use tools like those used by Data Scientists .

Excel is great for analyzing sales data. One organization had difficulty understanding regions’ performance. With 50 regions and two years of sales records, trendlines helped them identify the regions with maximum and minimum revenue. This gave them a chance to improve those regions.

Different types of graphs can provide better understanding of data trends.

Combining different types of graphs

Begin by selecting X and Y values for your data. Choose the first graph type, such as a line or bar graph , and make the graph. Right-click on the chart, then select “select data” from the drop-down menu. In the “Select Data Source” window, click “Add” for each data set.

To keep your message clear, contrast colors and patterns for each graph type. Get creative with secondary charts for a stacked column chart on top of a line chart. This allows you to show proportions and trends. Combining different types of graphs makes your visuals more impactful. Try it today!

Then, explore dual measurements with a secondary axis for even more detail.

Adding secondary axis for dual measurements

To add a secondary axis in Excel , first select the data series you want to plot. Go to ‘Chart Tools’ and click ‘Change Chart Type’. In the dialog box, select the chart type for your second data series. Check ‘Secondary Axis’ and click OK.

Excel will scale both axes so they fit on the same graph. Customize the formatting by right-clicking the axis and selecting ‘Format Axis’. Adjust features such as tick marks, labels, and title.

Remember, a secondary axis doesn’t change the underlying data. Be careful when interpreting results – compare like with like.

TechRepublic’s article explains adding a secondary axis is one of many advanced features of Excel. Enhance visualizations to make them informative and engaging .

The next heading is ‘ Sharing Your Graph with Others ‘. Export or embed your Excel graph into another document or presentation.

Sharing Your Graph with Others

Diving deep into Excel’s graph-making, I uncovered both exciting secrets for perfect visualizations and ways to share this info quickly. Sharing graphs is essential, be it a corporate presentation or a school assignment. Here’s two splendid methods to share graphs: export as an image, or share over email/social media.

Let’s start the hassle-free journey of sharing your graphs with the world!

Exporting graph as an image for easy sharing

In order to easily share your graph via email, social media, or mobile messaging apps, you can export it as an image directly from Excel! Here’s how:

• Click on the graph you want to share.
• Select File from the top left corner of Excel.
• Scroll down and click Export .
• Choose a location to save the file, name it, select the desired format ( PNG or JPG ), and hit save.

Remember – all elements, such as labels, titles, and axis scales, will be included in the image. Make sure these are all appropriate before saving!

Pro tip: Always save your files using high-resolution settings. Some platforms may require specific resolutions for images – check those beforehand.

Sharing your graph via email or social media platforms

Share your graph with 3 easy steps!

• Open email/social media platform.
• Attach graph.

When emailed, add a snappy subject line. A message to explain why you’re sharing the graph will help people understand its importance.

For social media, craft a post & check privacy settings. Posting in groups can be useful for targeting specific people.

A great example: My friend Johnnie had amazing products but no marketing content. I suggested creating Infographics using Excel Graphs with branding , then introducing it to LinkedIn Groups . It worked – people responded & sales increased within days.

Some Facts About How to Create Graphs in Excel: A Step-by-Step Guide:

• ✅ Excel offers a variety of graph types including bar charts, line charts, and pie charts. (Source: Microsoft)
• ✅ Users can create graphs in Excel by selecting their data, choosing a graph type, and customizing the graph with various design options. (Source: Excel Easy)
• ✅ Excel provides tools for adding lines, titles, and labels to graphs to make them more informative and visually appealing. (Source: BetterCloud)
• ✅ Users can easily format and manipulate graphs in Excel to meet their specific needs and preferences. (Source: Lifewire)
• ✅ Excel also allows users to create dynamic graphs that update automatically when underlying data changes. (Source: Office Support)

FAQs about How To Create Graphs In Excel: A Step-By-Step Guide

1. how do i create a graph in excel.

To create a graph in Excel, first select the data range you want to use for your graph. Then, go to the “Insert” tab, and choose the type of graph you want to create from the options available. Excel will automatically create a graph based on the data you selected.

2. How do I customize my graph in Excel?

Once you have created a graph in Excel, you can customize it by selecting the graph and using the “Chart Tools” tab that appears. From there, you can change the chart type, modify the layout, and format various elements of the graph (such as the axis labels and title).

3. How do I add data labels to my graph in Excel?

To add data labels to your graph in Excel, select the graph and go to the “Chart Elements” option in the “Chart Tools” tab. From there, choose “Data Labels” and select the type of labels you want to use.

4. Can I create a graph using non-numeric data in Excel?

Yes, Excel allows you to create graphs using non-numeric data (such as text or dates), but you will need to use a different type of graph. For example, a pie chart can be used to show the proportion of different categories of data.

5. How do I change the scale of my graph in Excel?

To change the scale of your graph in Excel, select the graph and go to the “Format Axis” option in the “Chart Tools” tab. From there, you can adjust the minimum and maximum values of the axis or choose a logarithmic scale.

6. How do I save my graph in Excel?

To save your graph in Excel, go to the “File” tab and choose “Save As.” From there, select the desired file format (such as a PDF or image file), choose a filename and location, and click “Save.”

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. 

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

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.

How to Make a Graph in Excel with a Lot of Data: Step-by-Step Guide

Creating a graph in Excel with a lot of data might sound overwhelming, but it doesn’t have to be. By following a few simple steps, you can transform your large datasets into easy-to-understand visual representations. This guide will walk you through each step, ensuring you can create a clear and informative graph in Excel, even with lots of data.

How to Make a Graph in Excel with a Lot of Data

Creating a graph in Excel helps to visualize large amounts of data in a manageable and interpretable format. Here’s how you can do it step-by-step.

Step 1: Open Excel and Input Your Data

Open a new Excel workbook and input your data into a spreadsheet.

Make sure your data is organized in rows and columns with clear headers. This will make it easier for Excel to interpret and plot your data correctly.

Step 2: Select the Data Range

Click and drag to highlight the range of data you want to include in your graph.

Selecting the correct data range is crucial. This tells Excel exactly which data points to include in your graph, ensuring it accurately reflects your dataset.

Step 3: Go to the ‘Insert’ Tab

Navigate to the ‘Insert’ tab on the Excel ribbon.

The ‘Insert’ tab is where you’ll find all the options for creating different types of charts and graphs. It’s like the toolbox for all your graphing needs.

Step 4: Choose Your Chart Type

Click on the type of chart you want to create, such as a bar chart, line chart, or scatter plot.

Different types of charts are suitable for different kinds of data. For example, line charts are great for time series data, while bar charts are useful for categorical data.

Step 5: Customize Your Chart

Use the ‘Chart Tools’ to customize your graph, including titles, labels, and colors.

Customization helps make your graph more readable and visually appealing. You can add titles, adjust axis labels, and even change the colors to match your presentation theme.

Step 6: Adjust Data Series if Needed

If you have a lot of data, you may need to adjust your data series to make the graph clearer.

Excel allows you to modify which data series are displayed and how they are formatted, helping to prevent your graph from becoming too cluttered.

After following these steps, your data will be represented in a graph that is easier to understand and analyze.

Tips for Making a Graph in Excel with a Lot of Data

• Simplify Your Data : If your dataset is too large, consider summarizing it or using a sample to make the graph more manageable.
• Use Filters : Excel’s filter feature can help you focus on specific parts of your data without overwhelming your graph.
• Label Clearly : Clear labels can make your graph much more informative and easier to read.
• Limit Data Points : Too many data points can make a graph hard to read. Consider splitting your data into multiple graphs if necessary.
• Use Data Smoothing : For time series data, data smoothing can help you visualize trends without the noise.

Frequently Asked Questions

How do i add a title to my excel graph.

You can add a title by clicking on the ‘Chart Elements’ button (the plus sign next to your chart) and checking the ‘Chart Title’ option. Then, you can click on the title text box to edit it.

How can I change the chart type after creating the graph?

Select your chart, go to the ‘Chart Tools’ on the ribbon, and click on the ‘Change Chart Type’ button. From there, you can choose a different chart type.

Can I add multiple data series to one graph?

Yes, you can add multiple data series. When you select your data, make sure to include all the columns or rows you want to appear in the graph.

How do I export my Excel graph?

Right-click on the graph and select ‘Copy.’ Then, you can paste it into a Word document or PowerPoint presentation. Alternatively, you can save the graph as an image by right-clicking and selecting ‘Save as Picture.’

What should I do if my graph looks too cluttered?

You can simplify your graph by removing unnecessary data series, using data filters, or splitting the data into multiple graphs.

• Open Excel and input your data.
• Select the data range.
• Go to the ‘Insert’ tab.
• Choose your chart type.
• Customize your chart.
• Adjust data series if needed.

Creating a graph in Excel with a lot of data might seem like a daunting task, but by following these straightforward steps, you can turn even the most complex datasets into clear, visually appealing graphs. Remember, the key to a great graph is simplicity and clarity. Don’t be afraid to experiment with different chart types and customizations until you find the perfect representation of your data.

If you’re new to Excel, or just looking to brush up on your skills, there are plenty of resources available to help you learn more. Consider exploring online tutorials or Excel courses for further reading. So, the next time you need to visualize a large dataset, you’ll be well-equipped to create an informative and engaging graph in Excel. Happy charting!

Matt Jacobs has been working as an IT consultant for small businesses since receiving his Master’s degree in 2003. While he still does some consulting work, his primary focus now is on creating technology support content for SupportYourTech.com.

His work can be found on many websites and focuses on topics such as Microsoft Office, Apple devices, Android devices, Photoshop, and more.

Share this:

• Click to share on Twitter (Opens in new window)
• Click to share on Facebook (Opens in new window)

Related Posts

• How to Make a Graph in Google Sheets: A Step-by-Step Guide
• How to Download a Graph or Chart as a Picture from Excel: Step-by-Step Guide
• How to Make a Graph in Excel: A Step-by-Step Guide for Beginners
• How to Combine Two Graphs in Excel: A Step-by-Step Guide for Beginners
• How to Build a Graph in Excel: Step-by-Step Guide for Beginners
• How to Combine Two Graphs in Excel: A Step-by-Step Guide
• How to Make a Line Graph in Excel: A Step-by-Step Guide for Beginners
• How to Copy Graph from Excel to Word: A Step-by-Step Guide
• How to Make a Line Graph in Word: A Step-by-Step Guide
• How to Make a Line Graph with Multiple Lines in Excel: Step-by-Step Guide
• How to Create a Bar Graph in Excel: A Step-by-Step Guide for Beginners
• How to Make a Bar Graph in Excel with 2 Variables: A Step-by-Step Guide
• How to Insert a Graph in Google Docs: A Step-by-Step Guide
• How to Make a Bar Graph in Excel: A Step-by-Step Guide for Beginners
• How to Create a Graph in Word: A Step-by-Step Guide
• How to Make a Pie Chart in Google Sheets
• How to Create Graphs in Excel: A Step-by-Step Guide for Beginners
• How to Make a Pie Chart in Excel 2013: A Step-by-Step Guide
• How to Make a Graph in Word: A Step-by-Step Guide

Get Our Free Newsletter

How-to guides and tech deals

You may opt out at any time. Read our Privacy Policy

Excel Charts: A Complete Overview

What Is a Chart in Excel?

Charts in Excel serve as powerful tools for visually representing data. Whether you’re analyzing sales figures, tracking trends, or comparing different categories, Excel offers a variety of chart types to suit your needs.

Types of Charts in Excel

1. column chart (vertical bar chart).

• Categories are plotted on the horizontal axis, and values on the vertical axis.
• Ideal for comparing different categories and their values.

2. Line Chart

• Category data is evenly distributed on the horizontal (X) axis, while values are evenly distributed on the vertical (Y) axis.
• Used to visualize trends over time.

3. Pie Chart

• Displays one data series through slices of a pie, showing proportions or percentages.
• Useful when you have a single data series with non-negative values.

4. Bar Chart

• Categories are plotted along a vertical axis, and values along the horizontal axis.
• Allows easy comparison between individual items. Available in 2-D and 3-D formats.

5. Area Chart

• Combines features of a bar chart and a line chart.
• Illustrates trends over time and total values across a trend.
• Available in 3-D format as well.

6. Scatter Chart

• Represents data points for two variables in a two-dimensional plane.
• Each data point is a dot, scattered based on the variables’ values.
• Add more data for better comparison.

7. Map Chart

• Represents values and categories across geographical regions.
• Requires region/country/state or postal code data.

8. Stock Chart

• Displays stock price data (e.g., opening, closing, high, low, volume).
• Helps analyze stock price fluctuations.

9. Surface Chart

• Three-dimensional chart showing relations among three data sets.
• Uses topographic maps, colors, and patterns to highlight value ranges.

10. Radar Chart

• Compares multiple data series.
• Axes radiate from a central point, connecting data points using lines and areas.

11. Treemap Chart

• Hierarchical view of data, useful for comparing different category levels.
• Color-coded for clarity.
• Available since Office 2016.

12. Sunburst Chart

• Also displays hierarchical data.
• Rings or circles represent hierarchy levels.

13. Histogram Chart

• A histogram chart displays the frequency distribution of data within a specified range.
• Each column in the histogram represents a “bin,” which groups data points.
• Available in newer versions of Excel (since Office 2016).

14. Box & Whisker Chart

• Used to display data distribution into quartiles and highlight the mean.
• Vertical lines (whiskers) extend from boxes to represent variability.

15. Waterfall Chart

• Shows the running total of financial data.
• Helps understand the impact of additions or subtractions on the total.
• Color codes differentiate positive and negative data.

16. Funnel Chart

• Displays values across multiple levels in a process.
• Shape resembles a funnel due to descending order of values.

17. Combo Chart

• Combines different chart types for comprehensive data analysis.
• Includes a secondary axis for better understanding.
• Available in Excel 2013 and newer versions.

Creating a Basic Chart in Excel

Suppose we have a dataset in the range B4:D10 that contains the yearly sales history of a company.

• Highlight the data you want to use ( B4:D10 ).

• Go to the  Insert  tab.

• Click on the Insert Column icon. A drop-down will appear.
• Select Clustered Column from the 2-D Column section.

A Clustered Column will appear on the worksheet.

Creating a chart using the Recommended Charts option

The Recommended Charts  option gives different types of charts based on your data .

• Select your data. go to the Insert tab and choose Recommended Charts from the Charts group.

• Select the desired chart from the Insert Chart window and click on OK .

Changing the Chart Layout and Style

• Select the chart and go to the  Chart Design  tab.
• Click on  Quick Layout  and choose a desired layout.

After selecting the desired layout, the chart will automatically change.

• Still in the  Chart Design  tab, select a style from the  Chart Styles group.

Adding, Changing or Removing Chart Elements

• Excel charts consist of several elements, including axes, axis titles, chart titles, data tables, data labels, error bars, gridlines, legends, lines, trendlines, and up/down bars.
• Click on the chart to activate it.
• Go to the  Chart Design  tab.
• Click the  Chart Elements  icon.
• Choose the desired element (e.g., data table with legend keys ).

After that, you can see the chart with a data table.

• When you select a chart, a plus icon appears on the top-right side . If you click on the plus icon, you will see the chart elements . You can select or deselect the elements to add or remove them.

• Double-clicking the data table opens the  Format Data Table pane for further customization.

You can also remove elements by selecting them and pressing the  Delete key.

Switching Row and Column Data

• Sometimes, you need to change how data is grouped in a chart.
• Select the chart.
• Choose  Switch Row/Column  from the  Data  group.

• This rearranges the data to show month-wise data within a year.

Changing Chart Type

After creating a chart, you can change its type:

• Click  Change Chart Type  in the  Type  group.

• Select the desired chart type and click  OK .

Formatting Charts

Make your chart visually appealing:

• The Format Axis pane will appear on the right side of the screen.

• Adjust display units and tick marks (e.g., set units to hundreds and place tick marks inside).

• Repeat for the X-axis.

You can also change the position of the labels and number system of the axis from the Format Axis pane.

2. Formatting the Chart Title

• Click the chart title box and type your desired title .

• Double-click the title to open the  Format Chart Title  pane.
• Explore different title and text options.

3. Formatting the Legend

• Double-click the legend to open the  Format Legend  pane.

• Customize legend position (e.g., right ).

4. Formatting Data Label

Data labels provide essential information about your chart. Here’s how to format them in Excel:

• First, select your chart.
• Click on the plus icon ( Chart Elements ) that appears near the chart.
• Check the Data Labels  option.
• Now your chart displays data labels.

• Double-click on any data label to open the  Format Data Labels  pane.
• In the pane, you’ll find various options tailored for data labels.
• Go to the  Number  section.
• In the  Format Code box, type  0.0,”K”  and click  Add .

• The data labels will now reflect the new format.

5. Format Data Series

Data series formatting enhances chart appearance. Follow these steps:

• Double-click on the data series to open the  Format Data Series  pane.
• Here, you can customize the series appearance.
• For example, select a color from the options.

• After applying a new color, the chart will look like below.

• Adjust the  Series Overlap  value.

6. Format Chart Area

Make your chart visually appealing by formatting the chart area:

• Double-click on the chart area to open the Format Chart Area  pane.
• In the pane, select  Solid Fill and change the fill color.

• The chart area color will update accordingly.

Moving a Chart

In Excel, there are times when you need to move the chart to a different sheet. You can do that using the Move Chart option. Follow the steps below to move a chart in Excel:

• Select the chart to get the Chart Design option.

• Select New sheet in the Move Chart box and click on OK .

As a result, the chart will be moved into a new sheet.

You can also move the chart as an object on a different sheet. You need to select the second option from the Move Chart box and choose the desired worksheet.

Copying a Chart

While creating dashboards or reports, you need to copy a chart . You can copy a chart and paste it elsewhere. Follow the steps below to copy a chart:

• Right-click on the chart and click on Copy from the context menu.

• After that, go to your desired location/file and press Ctrl + V .

Here, we have pasted the chart into a new worksheet.

Resizing a Chart

Resizing a chart is often necessary when you are working with a lot of data. You can resize the chart using the double-headed arrow. To resize a chart, follow the steps below:

• Select the chart and place the cursor on the small circle of the chart. The cursor will change into a double-headed arrow .

• You can also resize the chart using the Format tab.
• Click on the chart and the Format tab will appear.
• You can resize the chart from the Size group.

Filtering Chart in Excel

Filtering a chart can help you visualize a specific amount of data in Excel. To filter a chart, you can follow the steps below:

• You can check/uncheck Series and Categories from the filter options. Here, we unchecked April and May from the Categories section.

After applying the filter, the chart will show the selected data.

Keeping Charts Up to Date

Sometimes, after creating a chart, we need to add new data to the chart. In that case, the chart doesn’t update the data automatically. For example, if we add the data for July to the chart, the chart doesn’t update the data.

To keep charts up to date, you can follow the steps below:

• Select the chart area and the chart data will be selected automatically .
• Adjust the chart data range using the double-headed arrow .

The chart will show the new data.

Tips: Before creating a chart, you can convert the chart data range into a table and then, create a chart using the data. This way the new data will automatically be updated in the chart.

How to Select the Best Chart in Excel

There are many types of charts in Excel. To select the best type of chart, you need to understand what type of data you are working with. You need to keep the following things in mind while selecting the chart:

• Understand the data.
• What you need to show with your chart.
• Identify the chart type.
• Determine patterns and trends.
• Different charts for different data.

Download Practice Workbook

You can download the practice workbook from here:

Excel Charts: Knowledge Hub

• Refresh Chart in Excel
• Add a Trendline in Excel
• Customize Excel Charts
• Excel Axis Scale
• Data for Excel Chart
• Add Data Series in Excel Chart
• Add a Data Table with Legend Keys
• Create Dot Plot in Excel
• Create Thermometer Chart in Excel
• Bubble Chart in Excel
• Excel Doughnut Chart
• Excel Pareto Chart
• Burndown Chart in Excel
• Excel Distribution Chart
• Make a Comparison Chart in Excel
• Progress Chart in Excel
• Excel Control Chart
• How to Plot an Equation in Excel
• Excel Chart Not Working
• Excel Advanced Charting
• Find Intersection of Two Curves in Excel
• Show Intersection Point in Excel Graph
• Create a Weight Loss Graph in Excel
• Make a Budget Constraint Graph on Excel
• Create Mekko/Marimekko Chart in Excel
• Create Activity Relationship Chart in Excel
• Secondary Axis in Excel
• Markers in Excel
• Dynamic Excel Charts
• Excel Standard Curve
• Gantt Chart in Excel

<< Go Back To Learn Excel

What is ExcelDemy?

Tags: Learn Excel

Mursalin Ibne Salehin holds a BSc in Electrical and Electronics Engineering from Bangladesh University of Engineering and Technology. Over the past 2 years, he has actively contributed to the ExcelDemy project, where he authored over 150 articles. He has also led a team with content development works. Currently, he is working as a Reviewer in the ExcelDemy Project. He likes using and learning about Microsoft Office, especially Excel. He is interested in data analysis with Excel, machine learning,... Read Full Bio

Leave a reply Cancel reply

ExcelDemy is a place where you can learn Excel, and get solutions to your Excel & Excel VBA-related problems, Data Analysis with Excel, etc. We provide tips, how to guide, provide online training, and also provide Excel solutions to your business problems.

Contact  |  Privacy Policy  |  TOS

• User Reviews
• List of Services
• Service Pricing

• Create Basic Excel Pivot Tables
• Excel Formulas and Functions
• Excel Charts and SmartArt Graphics
• Advanced Excel Training
• Data Analysis Excel for Beginners

Advanced Excel Exercises with Solutions PDF

Table of Contents

What is a graph in excel , reasons to use graph in excel, types of graphs in excel, data cleaning, how to make a graph in excel, choose the right course, a step-by-step guide on how to make a graph in excel.

How to Create a Graph in Excel Step 1: First, input the data into Excel. Step 2: Go to the 'Insert' tab and choose a graph type. Step 3: Select the data you want to use. Step 4: Click 'Insert' to add your chosen graph. Step 5: Adjust which data appears on each axis if necessary. Step 6: Change the chart’s layout and colors. Step 7: Resize the chart's legend and axis labels as needed. Step 8: Modify the Y-axis measurement options if required. Step 9: Reorder your data if it improves clarity. Step 10: Add a clear title to your graph. Step 11: Save or export your graph for use in reports or presentations.

Microsoft Excel is a very useful data management tool used widely by almost every organization today to analyze and interpret data. A Graph in Excel is a design tool that helps us visualize data. Excel has a variety of graphs and charts that can be used to represent data in different ways. This article will help you understand the different types of graphs available in Excel, and learn how to make a graph in Excel.

In simple terms, a graph is a visual element that represents data in a worksheet . You will be able to analyze the data more efficiently by looking at a graph in Excel rather than numbers in a dataset. Excel covers a wide range of graphs that you can use to represent your data. Creating a graph in Excel is easy. The graph below depicts the sum of active COVID cases that are grouped by WHO region.

Looking at a graph helps us analyze various metrics just by taking a glance at it.

Become a Data Scientist with Hands-on Training!

Excel has most of the common graphs that are used for statistics. The types of graphs in Excel include:

1. Bar/Column Graphs

A bar graph shows information about two or more groups. Bar graphs are mainly used to make comparisons across a range. 

2. Pie Graphs

A pie chart is nothing but a circular graph representing data in the form of a pie/circle. It is divided into different sections, each one representing a proportion of the whole.  

3. Line Graphs

A line graph is formed by connecting a series of values/data points using straight lines. A line graph can be used when you want to check whether the values are increasing or decreasing over some time. 

4. Scatter Plot

A scatter plot, also called a coordinate graph, uses dots to represent the data values for two different variables, one on each axis. This graph is used to find a pattern/ relationship between two sets of data.

5. Area Chart

An area chart depicts the change of two or more data points over time. They are similar to the line charts, except the area charts are filled with color below the line. This chart is useful to visualize the area of various series relative to each other.

Before you make a graph in Excel, it is important to first cleanse your data. The next section will cover a few Data Cleaning techniques.

6. The New and Improved Bar Graph (Now Called a Clustered Bar Graph)

A clustered bar graph in Excel displays data horizontally, making it simple to compare different series side by side. This chart is great for seeing the differences between categories. For example, you can use a clustered bar graph to compare sales figures for different products across several stores. By doing this, you can quickly spot which products are performing better in different locations, helping you make informed decisions about inventory and sales strategies.

7. Ditto for Column Charts (Renamed Clustered Column Charts) Clustered column

Clustered column charts show data vertically with each column representing a different data series. This type of chart is perfect for comparing items and seeing their rankings. For example, if you want to compare the performance of different sales teams across various states, a clustered column chart can clearly show which team is leading in each state. The taller columns make it easy to see which teams are performing best and which may need more support.

8. Radar Chart

Radar charts are used to compare multiple variables on a single chart, using a shape that connects various points. Each point represents a different variable, and the shape formed helps you see patterns and strengths. For example, you might use a radar chart to compare different candidates' skills across areas like leadership, communication, and problem-solving. This helps you see where each candidate excels and where they might need improvement.

9. Funnel Chart

Funnel charts are useful for visualizing how data moves through different stages of a process. The wide end represents the starting point, and the funnel narrows as it progresses. This chart is great for analyzing processes like sales pipelines. For example, a funnel chart can show how many leads move from initial contact to final sale, highlighting where the biggest drop-offs occur and helping you focus on improving those stages.

10. Histogram Chart

Histograms display data by grouping it into ranges and showing how frequently values fall into each range. This type of chart is useful for understanding distributions. For example, if you want to analyze the age range of your customers, a histogram can show how many customers fall into each age group. This helps you identify trends, such as a shift in the age demographics of your target audience.

11. Box and Whisker Chart

A box and whisker chart, or boxplot, summarizes data by showing its distribution and any outliers. It includes a box that represents the middle 50% of the data, with lines extending to show the range. This chart is useful for comparing different data sets. For instance, you could use a box and whisker chart to compare test scores across different schools, revealing variations in performance and highlighting any unusual results.

12. Pareto Chart

Pareto charts use both bars and a line to show the frequency of issues and their cumulative impact. This helps you identify which problems to tackle first for the most significant improvement. For example, if you’re analyzing customer complaints, a Pareto chart can show which issues are the most common and have the biggest impact, helping you prioritize your efforts to resolve them.

13. Surface Chart

Surface charts offer a three-dimensional view of data, showing how different variables interact. This type of chart helps identify optimal combinations of factors. For example, you could use a surface chart to analyze how varying price and advertising spend affect sales, revealing the best conditions for maximizing revenue.

14. Sunburst Chart

Sunburst charts are used to visualize hierarchical data with concentric rings. Each ring represents a level of the hierarchy, making it easy to see relationships and structure. For example, a sunburst chart can show the breakdown of a company's departments and sub-departments, helping you understand how different parts of the organization fit together.

15. Treemap Chart

Treemap charts display hierarchical data using nested rectangles, where the size and color of each rectangle represent different values. This chart helps visualize complex data structures in an organized manner. For example, a treemap can show revenue contributions from various business units, with each rectangle’s size reflecting revenue and color indicating performance levels.

16. Stock Chart

Stock charts are designed to handle financial data, such as stock prices over time. They often include features like zooming and scrolling for detailed analysis. For instance, a stock chart can show daily price movements and trading volumes, helping investors track market trends and make informed investment decisions.

17. Waterfall Chart

Waterfall charts illustrate how sequential positive and negative values impact a total value. This chart is useful for understanding the cumulative effect of various factors. For example, you could use a waterfall chart to track monthly revenue changes, showing how different elements like new sales and refunds contribute to the final revenue figure.

18. Filled Map Chart

Filled map charts visualize data on a map by coloring different regions based on data values. This chart is useful for seeing how data varies geographically. For example, a filled map can show sales performance by region, with different colors indicating varying levels of sales, helping you identify regional trends and make strategic decisions.

19. Combo Chart

Combo charts combine two or more chart types, such as columns and lines, to provide a comprehensive view of data. This allows for more detailed analysis. For instance, a combo chart might show sales figures as columns and profit margins as a line, offering a complete picture of financial performance and helping answer complex data questions. 

Let's now focus on how to make a graph in excel. 

Data cleaning is the most crucial step to eliminate incomplete and inconsistent data. 

Remove Duplicate Values

You need to make sure your data is organized and free from duplicates. Duplicate values strongly affect the data that you are working on while creating graphs. Hence, it is essential to eliminate duplicate data by clicking on the Remove Duplicates option available on the Data Tab.

Use Find and Replace Tool to Clean Data

When it comes to Data Cleansing, find and replace is a great tool. Using this, you can find all the zeros and remove them. You can also replace all the formula references. 

Remove Extra Spaces

You can get rid of unwanted spaces between words or numbers which aren’t visible using the TRIM function. The syntax is:

=TRIM(text)

This function takes input as text and eliminates extra spaces. This results in no leading and trailing spaces, and only one space between the words. 

• You must select the data for which a chart is to be created.
• In the INSERT menu, select Recommended Charts.
• Choose any chart from the list of charts Excel recommends for your data on the Recommended Charts tab, and click it to preview how it will look with your data.
• Please click on All Charts if you are unable to locate a chart you like.
• Click on the chart that you prefer and then click OK.
• You can add chart elements such as axis titles or data labels, customize the appearance of the chart, or change the data displayed in the chart by clicking on Chart Elements, Chart Styles, and Chart Filters in the upper-right corner of the chart.
• Click on the chart TOOLS tab on the ribbon to add additional design and formatting capabilities and then click the options you desire under the DESIGN and FORMAT tabs.

Creating a graph in Excel is easy. This step-by-step tutorial will show you how to make a graph in Excel. The demo helps you create:

• Scatter Plot

NOTE: The dataset we will use comprises the data on Coronavirus cases, country-wise. It has records of: 

• WHO Region of every country. 
• Confirmed cases
• Active cases
• Recovered cases

You can download the above dataset using this link . Take a look at a summary of the dataset below. 

Let’s move on to understand how to create a bar graph in an easy and simple way. 

1. Bar Graph

A bar graph helps you display data using rectangular bars, where the length of each bar is a numeric value depending on the category it belongs to.

Follow the steps listed below to know how to create a bar graph in Excel.

• Import the data: There are numerous other ways to import data into your Excel workbook, depending on your file format. To do this, locate the Data tab → Get & Transform Data section → Get Data option and click on it. On clicking, a list of various options will appear, to import data from different sources. You can copy and paste this data into your workbook as well. 
• When you choose to work on a specific set of data within the dataset in discussion, you can use the Filter option. This option filters the data based on the requirements you’ve selected. Inorder to do this, select the whole dataset, locate the Data tab and click on the Filter option. 

• Once you click on the Filter option, a downward-pointing arrow appears on the column headers of the particular dataset. You will need to click on the arrow to reveal options to select the required data.

• In this case, let's filter the data according to a particular WHO Region. 
• You can do so by selecting the Filter option that appears on the column header - WHO Region.
• You will be able to locate a drop-down box with various filters. These can be added to the data.
• The filters can be applied on multiple parameters and sorted in ascending or descending order. 

• Here, select the South-East Asia region, and by clicking OK, you will be able to view data from the South-East Asian regions alone.

• Once data that is required is ready, a bar graph can now be created. As shown you locate the INSERT TAB → Charts section → Bar Graph option and select the type of bar graph that best suits your requirement.

• After selecting the appropriate bar chart, you can see a blank window that is open on the Excel sheet. On right-clicking on this blank window, you should find an option to Select Data. Clicking on it will open the Select Data Source window on your screen.
• Now, select the Legend entries (or Vertical axis). In the current example, we would select the Y-axis values as confirmed cases, recovered cases, deaths, and active cases. 
• On the other hand, as for the horizontal axis, select all the countries that we have filtered in the current example from the WHO region, i.e., South-East Asia. 

• After specifying the appropriate values, click on OK. Excel will now display a graph on your worksheet. You can go ahead and format your graph based on your requirement. 

• Double click on the chart window to locate various customization options in the toolbar, available to you.
• You may also arrive here by selecting the option beside the chart. 
• This will open a drop-down box consisting of various Chart Elements.

For this example, do select the Legend checkbox for displaying the confirmed cases, recovered cases, active cases, and deaths on the graph. Further, the Chart Title box can be selected to add a title.  

This was all about creating a bar graph in Excel. Let’s move ahead and learn how to create a pie chart. 

2. Pie Chart

A pie chart is a circular graph that represents data by dividing the circle into sectors, where each sector illustrates a proportion to the whole.

Follow the steps mention below to learn to create a pie chart in Excel.

• From your dashboard sheet, select the range of data for which you want to create a pie chart. We will create a pie chart based on the number of confirmed cases, deaths, recovered, and active cases in India in this example.

• Select the data range. Then, click on the Insert Tab. You will find the PIE chart option available in the charts group.

• Select an appropriate pie chart from a range of pie charts available. Clicking on it will open the PIE chart in a window. Right-click on the chart window and click on Select Data. The Select Data Source window will be opened where you can choose what data you want to be displayed on your pie chart.
• Here, the chart data range can be added by simply hovering your mouse to select the data required. 
• You can now select the legend entries (or vertical axis). In this current example, you will need to select India to display the cases pertaining to a specific country.
• For labels on the horizontal axis labels, you may select confirmed cases, deaths, recovered, and active cases, and depict them on the chart. 
• After specifying the entries, click on OK. This will display the pie chart on your window. 

• You can click on the icons next to the chart to add your finishing touches to it. Clicking on the chart elements will show you options where you can choose to display or hide data labels, chart tiles, and legend. You can choose from various styles by clicking on the chart styles. This lets you style your chart based on your requirement. You can also add multiple colors in your graph to make it look more presentable.
• You can also format the data by clicking on the Format data labels. This will show you different label options from which you can check and uncheck the different options available under it. 

In the next section, you will learn how to create a Scatter Plot in Excel. 

3. Scatter Plot

A Scatter Plot consists of a horizontal axis, a vertical axis, and a series of dots where each dot represents data values.  

To demonstrate this, we use a Day-wise COVID dataset that has the columns:

• Confirmed Cases
• Recovered Cases
• Active Cases

Follow the steps below to understand how to create a Scatter Plot in Excel.

• Select the required data. In this example, we depict the relation between date and confirmed cases using a Scatter Plot. So, we select only the columns containing the date and confirmed Cases. 

• Go to Insert Tab → Charts Section → Scatter Plot Option.

• Click on the appropriate Scatter Plot option. This will plot the values. You can format the chart, select a chart title, and add axis labels too. As you can notice, we have created a scatter plot on Date vs. Confirmed Cases.  From the figure, we can easily conclude how the confirmed cases of COVID rise drastically every day. 

4. Histogram

A Histogram is a frequency distribution graph that uses rectangles/bars to group data into ranges and indicates the frequency of occurrence for each range.

To understand how histograms work, let’s look at an example.

The following Employee Salary dataset contains the columns:

• Employee ID
• Employee Name

We will demonstrate how to create a histogram that depicts the number of employees under each salary group.

Follow the steps mentioned below to create a simple histogram.

• Select the data from the sheet on which you want to make a histogram.
• Click on the Insert Tab, you will find the Insert Statistic Chart option in the Charts group.
• A drop down will appear from where you can select the desired histogram chart.

• The histogram chart gets displayed. To customize your histogram, click on the icons that appear next to it.
• Clicking on the chart elements will show you options where you can choose to display or hide axis titles, data labels, chart tiles, and Legend, etc.
• Clicking on the chart styles lets you style your chart to your requirement. You can add multiple colors in your graph to make it look more presentable.

• To format your histogram plot, double click on the graph. You will see the Format Axis window open on the right side.
• Click on Axis options → Horizontal Axis. 

• You will find a list of different options to modify your histogram chart. 
• By Category: You can select this option if you have repeated lists of categories and if you want to know the sum or count based on those categories. 
• Automatic: This is the default option. It automatically decides what bins to create in the histogram. As you can notice, 5 bins are created in our chart.
• Bin Width: This option defines the range width. 

In this example, we have specified the bin width as 40000. 

• Number of Bins: In this option, you can enter the number of required bins. The chart will be created with the specified number of bins.
• Overflow Bin: This can be used when you want all the values over a certain value to be grouped together in the histogram. 

We have specified 300000 as our overflow value. Any value higher than 300000 will be grouped under the last range.

• Underflow Bin: This can be used when you want all the values below a certain value to be grouped together in the histogram. 

We have specified 80000 as our overflow value. Any value lower than 80000 will be grouped under the last range.

• Right-click on the chart and click on Add Data Labels to include the values on top of each range. 

• After formatting the histogram accordingly, we arrived at the following graph.  

This histogram successfully depicts the total number of employees grouped by salary range.  

This is all you need to know about how to make a graph in Excel.

Simplilearn's Data Science courses provide a comprehensive understanding of key data science concepts, tools, and techniques. With industry-recognized certification, hands-on projects, and expert-led training, our courses help learners gain the skills needed to succeed in the data-driven world. Upgrade your career with Simplilearn today!

Program Name Post Graduate Program In Data Science Professional Certificate Course In Data Science DS Master's Geo Non US Program IN All Geos University Caltech IIT Kanpur Simplilearn Course Duration 11 Months 11 Months 11 Months Coding Experience Required No Yes Basic Skills You Will Learn 8+ skills including Supervised & Unsupervised Learning Deep Learning Data Visualization, and more 8+ skills including NLP, Data Visualization, Model Building, and more 10+ skills including data structure, data manipulation, NumPy, Scikit-Learn, Tableau and more Additional Benefits Upto 14 CEU Credits Caltech CTME Circle Membership Live masterclasses from IIT Kanpur faculty and certificate from E&ICT Academy, IIT Kanpur Applied Learning via Capstone and 25+ Data Science Projects Cost $$$$ $$$ $$ Explore Program Explore Program Explore Program

Graphs make it easier to analyze trends and patterns in data. We hope this article has helped you provide a basic introduction to the types of graphs available and how to make a graph in Excel. Improve your Excel skills by enrolling in the Post Graduate Program In Data Science offered by Simplilearn.

1. How do I create a chart with multiple data in Excel?

To learn how to create a chart in Excel with multiple data series, first select the range of data you want to include. Then, navigate to the Insert tab and pick a chart type from the Charts group. Excel will generate a chart that displays all your selected data series. Adjust titles, colors, and styles as needed to effectively showcase your data, giving you insight into how to create graphs from Excel data.

2. How do I insert a chart in Excel?

Inserting a chart in Excel is simple. First, select the data you want to visualize. Next, go to the “Insert” tab and click on “Recommended Charts”. You'll see a list of chart types that Excel suggests based on your data. Choose one that fits your needs, and Excel will insert the chart into your worksheet. You can then customize it to better suit your preferences.

3. Can I combine two charts in Excel?

Yes, you can combine two charts in Excel. If both charts are of the same type, like two bar charts, you can copy one and paste it into the other. Excel will merge them into a single chart. For different types of charts, consider using a combo chart or manually adjusting the data series to integrate them effectively.

4. How can I customize the title of my Excel graph?

Customizing the title of your Excel graph is easy. Click on the chart to select it, then go to the “Chart Design” tab. Click on “Add Chart Element”, choose “Chart Title”, and select your preferred title option. You can then type your desired title directly into the Chart Title box and format it to match your chart's style.

5. How do I add data labels to my Excel graph?

To add data labels to your Excel graph, click on the chart to select it. Then, go to the “Chart Design” tab and click “Add Chart Element”. Select “Data Labels” and choose where you want them to appear on the chart. The data labels will show up next to each data point, helping you highlight specific values and make your chart more informative.

Data Science & Business Analytics Courses Duration and Fees

Data Science & Business Analytics programs typically range from a few weeks to several months, with fees varying based on program and institution.

Recommended Reads

Data Analyst Resume Guide

Data Analyst Job Description: What Does a Data Analyst Do?

How to Become a Data Analyst? Navigating the Data Landscape

Business Intelligence Career Guide: Your Complete Guide to Becoming a Business Analyst

How to Become a Data Analyst?

Data Analyst vs. Data Scientist: The Ultimate Comparison

Get Affiliated Certifications with Live Class programs

Data analyst.

• Industry-recognized Data Analyst Master’s certificate from Simplilearn
• Dedicated live sessions by faculty of industry experts

Business Analyst

• Industry-recognized certifications from IBM and Simplilearn
• Masterclasses from IBM experts
• PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

• Excel Charts Tutorial
• Excel Charts - Home

Excel Charts - Introduction

• Excel Charts - Creating Charts
• Excel Charts - Types
• Excel Charts - Column Chart
• Excel Charts - Line Chart
• Excel Charts - Pie Chart
• Excel Charts - Doughnut Chart
• Excel Charts - Bar Chart
• Excel Charts - Area Chart
• Excel Charts - Scatter (X Y) Chart
• Excel Charts - Bubble Chart
• Excel Charts - Stock Chart
• Excel Charts - Surface Chart
• Excel Charts - Radar Chart
• Excel Charts - Combo Chart
• Excel Charts - Chart Elements
• Excel Charts - Chart Styles
• Excel Charts - Chart Filters
• Excel Charts - Fine Tuning
• Excel Charts - Design Tools
• Excel Charts - Quick Formatting
• Excel Charts - Aesthetic Data Labels
• Excel Charts - Format Tools
• Excel Charts - Sparklines
• Excel Charts - PivotCharts
• Excel Charts Useful Resources
• Excel Charts - Quick Guide
• Excel Charts - Useful Resources
• Excel Charts - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

In Microsoft Excel, charts are used to make a graphical representation of any set of data. A chart is a visual representation of data, in which the data is represented by symbols such as bars in a bar chart or lines in a line chart.

Charts Group

You can find the Charts group under the INSERT tab on the Ribbon.

The Charts group on the Ribbon looks as follows −

The Charts group is formatted in such a way that −

Types of charts are displayed.

The subgroups are clubbed together.

It helps you find a chart suitable to your data with the button Recommended Charts.

Chart Tools

When you click on a chart, a new tab Chart Tools is displayed on the ribbon. There are two tabs under CHART TOOLS −

Recommended Charts

The Recommended Charts command on the Insert tab helps you to create a chart that is just right for your data.

To use Recommended charts −

Step 1 − Select the data.

Step 2 − Click Recommended Charts .

A window displaying the charts that suit your data will be displayed.

How to Create Charts and Graphs to Visualize Data in Excel

Excel is a powerful tool that can help you not just organize data but also visualize it in the form of charts and graphs. Creating visual aids such as charts and graphs can help you communicate complex information to others more easily. In this article, we will discuss everything you need to know about creating charts and graphs in Excel to visualize data.

Table of Contents

The Importance of Visualizing Data

Most people are visual learners, which means they understand information better when they see it in the form of a chart or graph. In addition, visualizing data can help you spot trends and patterns in the data that you may have missed if you were just looking at the raw data. Whether you are presenting data in a business or academic setting, using a chart or graph can make the data more accessible and meaningful to your audience.

Furthermore, visualizing data can also help you communicate complex information more effectively. For example, if you are presenting data on the performance of a company over the past year, a chart or graph can quickly show the audience which areas of the business are doing well and which areas need improvement. This can save time and prevent confusion that may arise from presenting the same information in a written report. Overall, visualizing data is a powerful tool that can enhance communication and understanding in a variety of settings.

Understanding the Different Types of Charts and Graphs in Excel

Excel offers many types of charts and graphs, so it’s important to choose the right one to communicate the data you have. Some of the most commonly used charts include bar, pie, line, scatter, and area charts. Bar charts are great for comparing values across multiple categories, while pie charts are ideal for showing how a whole is divided into its component parts. Line charts are useful for showing trends over time, while scatter charts are great for showing how two sets of data relate to each other. Area charts, on the other hand, are great for showing how a whole changes over time or how multiple components contribute to a whole. It’s important to understand the strengths and limitations of each chart type to choose the right one for your data.

In addition to these commonly used charts, Excel also offers more specialized chart types such as waterfall, radar, and bubble charts. Waterfall charts are useful for showing how positive and negative values contribute to a total, while radar charts are great for comparing multiple variables. Bubble charts are ideal for showing the relationship between three sets of data, with the size of the bubbles representing the magnitude of the third variable.It’s also important to consider the design and formatting of your chart. Choosing the right colors, fonts, and labels can make a big difference in how easily your audience can understand and interpret the data. Additionally, adding titles, legends, and annotations can provide context and help highlight important points. By taking the time to choose the right chart type and design, you can effectively communicate your data and make a strong impression on your audience.

Selecting the Right Chart or Graph for Your Data

Once you have a better understanding of the different types of charts and graphs in Excel, it’s time to choose the one that fits your data best. To select the right chart or graph, you need to consider the data you have and the message you want to convey. Some things to consider include the type and format of the data and the number of categories or variables involved. It’s important to choose a chart or graph that makes sense in the context of your data and the message you want to convey.

Another important factor to consider when selecting a chart or graph is the audience you are presenting to. Different types of charts and graphs may be more effective for different audiences. For example, a bar chart may be more effective for presenting data to a general audience, while a scatter plot may be more effective for presenting data to a technical audience.

It’s also important to consider the overall design and aesthetics of the chart or graph. The colors, fonts, and layout can all impact how the data is perceived and understood. It’s important to choose a design that is visually appealing and easy to read, while still accurately representing the data.

Building a Chart or Graph in Excel: Step-by-Step Guide

Now that you know which chart or graph type to use, it’s time to create your visual aid. Building a chart or graph in Excel is a straightforward process that involves selecting the data, choosing the chart type, and customizing the chart. Here are the basic steps to follow:

• Select the data that you want to include in the chart or graph.
• Click on the “Insert” tab in Excel and choose the type of chart or graph you want to create.
• Select the chart or graph type in the dropdown menu.
• Customize the chart or graph by changing the title, axis labels, and other elements as needed.
• Use the “Chart Tools” tab in Excel to tweak the formatting and design of your chart or graph.
• Save your chart or graph as a separate file or insert it into a document or presentation.

Customizing Your Chart or Graph to Better Communicate Your Message

Customization is key when it comes to creating a chart or graph that effectively communicates your message. You can customize the chart title, legend, axis labels, and more to make sure that the chart or graph is easy to understand and supports your key message. You can also add color and shading to your chart or graph to make it more visually appealing without sacrificing clarity.

Creating Trendlines and Forecasts in Excel Charts and Graphs

Trendlines and forecasts are essential elements of many types of charts and graphs. A trendline is a straight or curved line that shows the general direction of the data as it changes over time. A forecast is a projection of future values based on the existing data. Excel makes it easy to create trendlines and forecasts within your chart or graph to help you better communicate the data and make predictions.

Adding Labels, Titles, and Legends to Your Chart or Graph

Clear labeling and titling are essential to ensure that your chart or graph is easy to understand. You can add labels, titles, and legends to your chart or graph to provide additional context and help people interpret the data. For example, you can add axis labels that describe the data included on each axis, and a chart title that summarizes the message you want to convey.

How to Format and Style Your Chart or Graph for Maximum Impact

The visual appeal of your chart or graph can be just as important as its content when it comes to communicating your message effectively. You can format and style your chart or graph to make it more visually appealing and easier to understand. For example, you can use bright colors and bold fonts to draw attention to key parts of the chart or graph. However, it’s important to ensure that your formatting and style choices don’t detract from the clarity and meaning of the data.

Applying Conditional Formatting to Data Before Visualizing in Your Chart or Graph

Excel allows you to use conditional formatting to highlight data trends within your spreadsheet before visualizing them in your chart or graph. Conditional formatting allows you to apply formatting, such as color shading or icon sets, to cells based on certain criteria. This is particularly useful when you want to draw attention to trends or patterns in your data before creating your chart or graph.

Tips and Tricks for Effective Data Visualization in Excel

Here are some additional tips and tricks to help you create effective data visualizations in Excel:

• Keep it simple, focusing on the data that really matters and avoiding excess clutter.
• Make sure your chart or graph is scalable and can be easily adjusted to fit different screen sizes and formats.
• Use labels and titles to provide context and help people understand the data more easily.
• Choose colors carefully to make sure they are easily distinguishable and don’t clash.
• Test your chart or graph on different devices and platforms to make sure it looks good and works well.

Best Practices for Sharing and Presenting Your Excel Charts and Graphs

Creating a great chart or graph is just the first step. To effectively communicate your message, you also need to share and present your visuals in a clear and compelling way. Here are some best practices for sharing and presenting your Excel charts and graphs:

• Include your chart or graph in a well-designed document or presentation that provides context and additional information.
• Provide a clear and concise summary of the key message that the chart or graph is intended to convey.
• Make sure your audience can easily read the chart or graph by using a large font size and high contrast colors.
• Consider using animated or interactive elements to help people understand the data more easily.
• Be prepared to explain the data and insights shown in the chart or graph, and answer any questions that your audience may have.

Troubleshooting Common Issues When Creating Charts and Graphs in Excel

Even with the best planning and preparation, there may be times when you encounter issues when creating charts and graphs in Excel. Some of the most common issues include problems with formatting, errors in the data, and issues with the chart type. If you encounter a problem, the best course of action is to search online for solutions or ask for help from colleagues or online forums. There are many resources available to help you troubleshoot issues and make sure your chart or graph looks and works the way you intended.

Advanced Techniques for Complex Data Visualization Projects

For those who are ready to take their data visualization skills to the next level, Excel offers many advanced features and techniques. These include data analysis tools such as pivot tables, as well as more complex chart types such as histograms and bubble charts. There are also many add-ins and plugins available that can help you create even more complex and sophisticated data visualizations. With practice and patience, you can become an expert data visualizer and create charts and graphs that truly communicate your message with clarity and impact.

That concludes our in-depth guide to creating charts and graphs in Excel. With the tips and techniques discussed in this article, you should now have all the tools you need to create compelling, informative, and impactful visual aids for your next project.

By humans, for humans - Best rated articles:

Excel report templates: build better reports faster, top 9 power bi dashboard examples, excel waterfall charts: how to create one that doesn't suck, beyond ai - discover our handpicked bi resources.

Explore Zebra BI's expert-selected resources combining technology and insight for practical, in-depth BI strategies.

We’ve been experimenting with AI-generated content, and sometimes it gets carried away. Give us a feedback and help us learn and improve! 🤍

Note: This is an experimental AI-generated article. Your help is welcome. Share your feedback with us and help us improve.

5 Ways To Make a Scatter Plot in Excel

Are you wondering how to make a scatter plot in Excel? Follow along with this thorough tutorial to learn this must-have data visualization skill.

A scatter plot helps you to show the relationship between two sets of numerical or quantitative analysis values. It’s used abundantly to visualize scientific, engineering, and statistical data on an XY chart where both axes contain values.

Microsoft Excel offers multiple easy ways to draw and share scatter or xy plots. Follow along with this tutorial until the end to practice the easiest techniques to create 2D and 3D scatter plots.

Using the Quick Analysis Tool

Suppose, you’d like to analyze the statistical relationship between the above dataset of store visitors and sales using an Excel scatter plot. You can use the Quick Analysis tool from the right-click menu. Alternatively, it’ll also show up if you select the input dataset in newer Excel editions, like Excel 2021, Excel 365, Excel for the web app, etc.

Let’s consider, you’re using Excel for Microsoft 365 desktop app. Open the worksheet containing the input data for the XY plot. Select the entire dataset. The Quick Analysis tool prompt will show up in the bottom right corner.

Click on the Quick Analysis tool icon or press Ctrl + Q to access the Quick Analysis tool user interface.

Now, navigate to the Charts tab and click on the Excel Scatter plot button.

Excel will automatically insert a scatter chart with default settings (Bounds, Units, and Label Position) for the X and Y axes. The XY chart also includes an automatic line for easier comparison of two sets of values.

A drawback of this method is the Quick Analysis tool isn’t available on earlier Excel editions like Excel 2010, Excel 2007, etc.

Using the Insert Scatter or Bubble Chart Command

The default location for all chart tools including Excel scatter plot is the Charts commands block of the Insert tab.

If you’re using Excel 2013 or newer, you’ll get the Recommended Charts command button. It takes you to an elaborate list of XY plots.

So, select the target dataset and click on the Insert Scatter drop-down menu. Choose any of the following scatter chart types:

• Scatter (only markers)
• Scatter with Smooth Lines and Markers
• Scatter with Smooth Lines
• Scatter with Straight Lines and Markers
• Scatter with Straight Lines

I picked the Scatter with Smooth Lines and Markers option. You can see the result above.

If there’s a third set of values to be plotted in the same graph, you can try the bubble chart style as well. It’s available in Insert > Insert Scatter > Bubble .

You can choose from any of the following two bubble chart models:

For instance, if I create a bubble chart for the above dataset, the values in the Sales and Visitors columns will be used to plot the markers for the Y and X axes. The Profits column values will define the bubbles’ diameters in the chart.

Using a Keyboard Shortcut

After selecting the target dataset, press Alt + F1 to insert the default column chart in the active worksheet.

Click on the chart populated by Excel to access the Chart Design tab in the ribbon.

Click on the Change Chart Type command button in the Type block.

The Change Chart Type dialog box will show up with All Charts as the selected tab.

Click on the left-side navigation panel’s X Y (Scatter) category.

On the right side, you should see the preview of the default scatter model with only markers.

You can change the scatter plot type on the top and choose an appropriate graph from the preview. Click OK to apply the changes you’ve made.

Excel will change the column chart to scatter plot instantly.

Adding a New Data Series

Suppose, you already have the above scatter plot in your Excel worksheet.

If you wish to add a third set of values in the same chart, select the existing graph.

Paste the new data series in column C or any other column to the right of the existing dataset.

Right-click on it and choose Select Data from the context menu.

The Select Data Source dialog box will pop up.

Delete the existing cell range reference of the Chart data range field.

Select the new cell range from the worksheet. Click OK save.

You should now see two sets of markers on the scatter chart.

Click on the chart again and select Change Chart Type from the Type commands block in the Chart Design tab.

Choose Combo from the left-side navigation pane in the Change Chart Type dialog.

You should see the series names and corresponding chart types on the right-side preview pane .

Click on the drop-down menus for both the series and choose Scatter .

Checkmark the checkbox for Secondary Axis for any of the data series. For example, checking the Secondary Axis for Profits would show as the additional series axis labels on the right side of the chart.

The final chart will look like the one shown above.

📒 Read More : 6 Ways to Add a Secondary Axis in Microsoft Excel

Using Excel VBA

You can also create the default or a custom scatter plot using the Excel VBA tool. It doesn’t require you to go through VBA programming courses. Just use the scripts I’ve shared below to create cool VBA macros that can magically insert XY plots.

Firstly, follow along with the tutorial below to learn the techniques to create a VBA macro using a VBA program code:

📒 Read More : How To Use The VBA Code You Find Online

If you’re ready, check out the scripts and their functions outlined below

Default Scatter Plot in Excel Using VBA

The following script lets you create the default scatter plot. It’s similar to the one you create using the Quick Analysis tool or the Insert Scatter command in the Excel ribbon menu.

After creating the macro, press Alt + F8 to launch the Macro dialog box.

There, select the CreateScatterPlot macro and hit Run .

The VBA script will start populating a series of input boxes and confirmation pop-ups to guide you through entering data and formatting the output chart. Find below the pop-ups and their functionalities:

• Use your mouse to select the data series column for the Y axis .

• An input box so you can enter the X-axis values by selecting the column using a mouse.

• If you wish to add a custom chart title, click Yes in this notification box.

• This is the input box where you should type in the chart title.

• Click Yes if you want to add axis titles. Excel will use the column headers to populate the titles.

Find above a scatter plot created using this VBA script.

Plotting Three Sets of Data Series

You can create a Combo scatter chart using three sets of data series using the following script:

When you execute the VBA macro created from the above script, a cascade of pop-ups will walk you through the process. I’ve listed these below:

• Use your mouse cursor to choose the data column for the X-axis.

• In the next prompt, you’ll need to enter the cell range for the first data series.

• Similarly, enter the cell range for the second data series in the third prompt.

Excel VBA will populate the scatter chart as instructed.

Useful Customizations for a Scatter Plot

You might need to customize various technical elements of the scatter chart using the Format Chart Area navigation pane.

Firstly, you must click on the chart to get the Format Chart Area tool. It should be on the right.

Click the Chart Options drop-down menu and choose Horizontal (Value) Axis .

Go to the Axis Options menu and expand the Axis Options list of items.

From here, you can modify the Bounds and Units to make the chart axis labels relevant to the markers.

For example, in the above chart, the markers are far away from the axis cross sections because Bounds and Units haven’t been customized appropriately.

So, changing the Bounds Minimum to 1,500 points should fix the spread of the X-axis .

Similarly, you can adjust the Bounds of the Y-axis . Just navigate to the Vertical (Value) Axis menu in the Format Axis navigation panel. Then, follow the same steps to edit Bounds Minimum from Axis Options .

To add a trendline and its equation, click on the chart to get the Chart Elements button. It’s the Plus (+) sign in the top right corner.

Click Chart Elements and checkmark the check box for Trendline .

Now, double-click on the trendline to open the Format Trendline navigation panel. Select the following options:

• Display Equation on chart
• Display R-squared value on chart

Conclusions

So far, you’ve learned and practiced the best methods to make a scatter chart in Microsoft Excel.

In addition, you’ve learned how to customize a scatter plot to fit your data visualization requirements.

Which method would you like to recommend? Comment below!

About the Author

Subscribe for awesome Microsoft Excel videos 😃

• Pivot Table Tips and Tricks You Need to Know
• Everything You Need to Know About Excel Tables
• The Complete Guide to Power Query
• Introduction To Power Query M Code
• The Complete List of Keyboard Shortcuts in Microsoft Excel
• The Complete List of VBA Keyboard Shortcuts in Microsoft Excel

Related Posts

8 Ways To Add Percentages to Pie Chart in Microsoft Excel

Jun 17, 2024

Find out how to add percentages to a pie chart in Excel by following along...

5 Ways to Add a Trendline in Microsoft Excel

May 11, 2024

Learn how to add a trendline in Excel in this quick and simple Microsoft Excel...

5 Ways to Switch the X and Y Axis in Microsoft Excel

May 9, 2024

Today, I'll show you how to switch the X and Y axes in Excel in this quick and...

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

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

Submit Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Get the Latest Microsoft Excel Tips

Follow us to stay up to date with the latest in Microsoft Excel!

How to Make a Graph in Excel?

Step-by-step guide on how to create a graph in excel, how to edit a graph in excel, data visualization tips, additional resources.

Create compelling Excel graphs and charts!

In addition to working with large volumes of data, finance and accounting professionals need to learn how to make a graph in Excel. Data visualization (visual representation of data in charts or graphs) is critical to many jobs. The graphical visualization of data is an effective method to communicate information to readers quickly. Charts and graphs identify trends and patterns in the data, as well as to detect anomalies and outliers.

Among finance and accounting professionals , Microsoft Excel remains the top choice for many tasks, and data visualization is no exception. MS Excel allows for creating various types of charts and graphs. In addition, the templates can be easily modified to improve the financial models. Finally, graphs and charts created in Excel can be exported to other applications to include them in your report or presentation.

The guide discusses the steps to create any type of chart in Excel:

• Enter the data in Excel. Also, the data can be imported into Excel from other applications.
• Ensure that the data is organized in a table format, and all variables are carefully labeled.
• Select the data that will be used to create a graph. Don’t forget to include the labels as well.
• When the required data have been selected, click Insert -> Charts. If you know exactly what type of graph is suitable for your task, choose from one of the templates. If you are not sure about the graph type, click Recommended Charts In this case, Excel will provide you several graph options that fit best to your data.

Follow the four simple steps above and you can create a graph in Excel. However, if you want your data visualization to be compelling to the readers, you must also know how to edit your graph. It can be done in several ways:

• Left-click your graph. On the top panel in Excel, the Chart Tools section will pop up. Under the section, there are two buttons: Design and Format.

• Design: With this button, you can edit the design of your graph. You can add or remove elements on your graph (e.g., axis titles, labels, legend), change the layout or style of a graph, swap data over the axis, change the data range used in a graph, and change the chart type.
• Format: The Format option allows for changing the appearance of the graph. For example, you can change the colors of the chart’s elements and add shapes and shape effects to it.

• Left-click your graph. On the right side of the chart, three buttons will appear: Chart Elements , Chart Styles , Chart Filters .
• Chart Elements: With this feature, you can add or remove chart elements such as axis, axis titles, chart title, etc.
• Chart Styles: This feature allows for altering the style of the chart, as well as the color palette used in the chart.
• Chart Filters: This function enables filtering the results displayed on your chart.

• Right-click your graph and a new menu will pop up. In this menu, you can Change chart type , Select data , or Format chart area .

Now you know how to create graphs and charts in Excel. However, it is not the end of the story. Data visualization is not simply stacking several graphs together, but is concerned with the ability to convey the correct message from the data to the reader in a compelling way. Here are some tips that will allow you taking your data visualization skills to a new level:

1. Keep it simple

“Keep it simple” remains the golden rule in data visualization. Always try to make your graphs or charts as simple as possible. Remember that a reader should be able to understand the message that your chart intends to convey quickly.

2. Choose the right chart

Know the key differences between various types of charts such as bar, line, pie charts, etc. Learn about the advantages and disadvantages of each type of chart. This fundamental knowledge will ensure that you choose the most appropriate type of graph in your situation.

3. Pick the right colors

Color is a powerful tool in data visualization. Selecting the appropriate colors for a chart or graph may help your readers to grasp the key pieces of information quickly. When you use the right colors for a chart, remember that too similar colors cannot convey the differences between data points while extremely contrasting colors, as well as too many colors, can be distracting for a reader.

4. Properly label data

Data labeling is crucial to powerful data visualization. For example, it always a good idea to label axes of your chart and main data categories. Nevertheless, be aware that excessive labeling on your chart can be distracting to your readers.

5. Don’t use special effects

Don’t use special effects (e.g., 3D) unless necessary. For example, a 3D feature on a bar chart is not necessary since a bar chart considers only two dimensions. Special effects may only distort dimensions on a chart, and a reader can be easily confused.

CFI is the official provider of the Financial Modeling and Valuation Analyst (FMVA)™ certification program, designed to transform anyone into a world-class financial analyst.

To keep learning and developing your knowledge of financial analysis, we highly recommend the additional CFI resources below:

• Advanced Excel Formulas
• Error Bars in Excel
• How to “Insert Data From Picture” In Excel
• Excel Dashboards & Data Visualization Course
• See all Excel resources

Excel Tutorial

To master the art of Excel, check out CFI’s Excel Crash Course , which teaches you how to become an Excel power user.  Learn the most important formulas, functions, and shortcuts to become confident in your financial analysis.

Launch CFI’s Excel Course now

to take your career to the next level and move up the ladder!

• Share this article

Create a free account to unlock this Template

Access and download collection of free Templates to help power your productivity and performance.

Already have an account? Log in

Supercharge your skills with Premium Templates

Take your learning and productivity to the next level with our Premium Templates.

Upgrading to a paid membership gives you access to our extensive collection of plug-and-play Templates designed to power your performance—as well as CFI's full course catalog and accredited Certification Programs.

Already have a Self-Study or Full-Immersion membership? Log in

Access Exclusive Templates

Gain unlimited access to more than 250 productivity Templates, CFI's full course catalog and accredited Certification Programs, hundreds of resources, expert reviews and support, the chance to work with real-world finance and research tools, and more.

Already have a Full-Immersion membership? Log in

• Data Science
• Data Analysis
• Data Visualization
• Machine Learning
• Deep Learning
• Computer Vision
• Artificial Intelligence
• AI ML DS Interview Series
• AI ML DS Projects series
• Data Engineering
• Web Scrapping

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

Similar reads.

• How to Delete Discord Servers: Step by Step Guide
• Google increases YouTube Premium price in India: Check our the latest plans
• California Lawmakers Pass Bill to Limit AI Replicas
• Best 10 IPTV Service Providers in Germany
• 15 Most Important Aptitude Topics For Placements [2024]

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

How-To Geek

I always use excel to create heat maps: here's how you can too.

Your changes have been saved

Email is sent

Email has already been sent

Please verify your email address.

You’ve reached your account maximum for followed topics.

The Best Excel Keyboard Shortcuts I Use as a Power User

Tidy up your excel spreadsheet with the new trimrange function, how to import tables from the web to excel 365, quick links, what is a heat map and what are they used for, create your data set, apply conditional formatting, remove visible values and gridlines (optional).

Excel is known as a complex number cruncher that only experts can use, but, in my view, labeling the program with such a sweeping generalization undermines its ability to make life easier. And in no scenario is this truer than in its wish to help you visualize data more efficiently.

That brings me to heat maps, which you can easily create in Excel to represent values relative to each other using colors.

In today's fast-paced world, where everyone seems to be in a rush, displaying data in a way that can be easily interpreted and analyzed—such as in a heat map—is essential. Excel lets you automatically color code figures to demonstrate their relationship with one another, using darker colors for higher numbers and lighter colors for lower numbers, for example. This means you can see trends and anomalies at a glance.

But this Excel tool isn't exclusively reserved for corporate finances or complicated data analysis. Indeed, you can use Excel to create heat maps for pretty much anything, from displaying your sports team's on-field strengths to showing how climate change is impacting temperatures over time.

The colors in the examples above are automatically generated using numerical data (which has been hidden—we'll look into this later) and conditional formatting . Let's explore in more detail how this can be done.

Excel has a separate tool for creating geographical heat maps (for example, if you wanted to color each country based on GDP per capita or millimeters of rainfall per year). However, in this article, we're going to explore how to create all other types of heat maps manually.

The first step is to create your statistical data in its simplest form. If you're starting with a blank worksheet, type your column and row parameters, and insert your data. If you wish, you can format your Excel table so that it's easier to add more data later on. If you already have your completed data set, make sure it's presented in a way that lends itself to creating a heat map in the next step (such as removing empty rows and columns in a table).

To generate the two sample heat maps shown above, we started with this (details of the number of bonuses each employee received each month):

And this (how many goals were scored from a certain location on a soccer pitch):

To create the soccer pitch in Excel, I inserted it as a PNG image , meaning the cells underneath the graphic remained visible. You can do the same with any image outline to create a heat map in Excel.

The next step is to apply the color scales to your data. First, select all the cells that will form the heat map. In the example below, I've selected all the cells on the soccer pitch, so that any data I might add later on will also be picked up by the color rules I set.

If you are applying the conditional formatting to cells underneath an image, you'll need to use your arrow keys to navigate to the correct cell, as you can't select a cell underneath a graphic using your mouse. Then, hold Shift while using your arrow keys to select the relevant cells.

Next, click "Conditional Formatting" in the Home tab on the ribbon, and hover over "Color Scales." From there, you can choose the color scale that works best with how you want to display your data.

In my case, I'll choose the "Green To Yellow" scale.

If none of the preset options pique your fancy, click "More Rules" instead. This will launch the New Formatting Rule dialog box, where you can switch to a three-color scale (rather than the default two colors), with more specific rules about how the values affect the colors to be displayed.

To change or remove the color scale after you have applied it, select the cells again, click "Conditional Formatting," and select either " Manage Rule s" or "Clear Rules."

The final step in optimizing your heat map involves hiding the figures and removing the gridlines , if doing so will improve your data visualization.

To hide the figures, select the cells to which you applied the conditional formatting in the previous step. Then, in the Home tab, click the "Number Format" icon in the bottom corner of the Number group.

Then, click "Custom" in the Category menu, and type ;;; (three semicolons) into the field box.

When you click "OK," the numbers will disappear from the cells, though you can still see them in the formula bar when you select the relevant cells.

Removing the gridlines is much more straightforward. In the View tab on the ribbon, uncheck "Gridlines" in the Show group.

Heat maps are just one of the many ways to visualize data in Excel, and which method you choose depends on the type of data you have, and how you want to present it. For example, you can create dynamic charts with dropdown lists , insert a combo chart that combines a column and line graph into a single chart, and use pivot tables to analyze your data more comprehensively.

• Microsoft Excel
• Microsoft 365

Filter by Keywords

Microsoft Excel

How to create a pareto chart in excel.

Praburam Srinivasan

Growth Marketing Manager

September 1, 2024

Start using ClickUp today

• Manage all your work in one place
• Collaborate with your team
• Use ClickUp for FREE—forever

Do you think every action always has an equal and opposite reaction?

Many experts believe that 80% of outcomes actually stem from just 20% of efforts. This idea, known as the Pareto principle or the 80/20 rule, was introduced by Italian economist Vilfredo Pareto in 1896.

The 80/20 rule, visualized through Pareto charts, is a powerful tool for identifying the key contributors to a problem or success. This helps you focus on the most significant factors, allowing for more targeted and efficient improvements.

While you can create a chart or Pareto diagram using Microsoft Excel, it often involves complex steps and can be time-consuming.

But you don’t have to scratch your head figuring out how to create a Pareto chart in Excel? In this guide, we’ll walk you through the process step-by-step process.  We will also explore some alternative methods to help you create a Pareto chart effortlessly.

Let’s begin!

For Excel 2016, Microsoft Office 365, and later versions

For the excel 2013 version, for the excel 2010 version, manual data labels and updates, complex formatting options, integration challenges, no real-time collaboration and updates, lack of scalability features, understand your focus areas, collaborate with your teams, get real-time information in a compact dashboard, harness the power of the pareto principle with clickup.

Creating a Pareto Chart in Excel

The process of creating a graph in Excel depends on the version of Excel you’re using and your Excel skills. 

With Microsoft Excel 2016, Excel 365 browser or app, you can create a Pareto chart in a few simple steps.

However, older versions of Microsoft Excel lack the chart tools you need and there’s no direct option to create a Pareto Chart. You will have to combine the line chart and a column chart to get the Excel Pareto chart.

Let’s explore both processes one by one.

To create a Pareto chart in Excel 2016 or later versions, follow the steps below:

Step 1: Prepare your data

• To create a Pareto chart, list all the values and categories in descending order. Start by adding all your data to an Excel sheet , as shown in the image below

In this example, we have used the response from freelancers who switched to part-time or full-time freelancing in 2020. The survey is intended to capture the most significant factors contributing to the switch since the COVID-19 pandemic.

• Next, go to the Data tab, and in the Sort and Filter group, click on Descending Order (ZA with the arrow pointing downward)

• Once you have your data in descending order, calculate the cumulative count. To do so, enter the formula shown in the C3 tab . Replicate this for the cells below to get the cumulative count for each

• Finally, add the cumulative percentages for all values. To get the cumulative percentage for each cell, follow the formula in the D3 tab . You need the correct column chart values to get the right formulas 

Note: Cell C9 contains the total number of responses from freelancers who switched to part-time or full-time freelancing post-COVID-19. Thus, we have counted the cumulative % starting from the top factor (Job Loss Due to COVID-19) to the last factor in our survey.

Step 2: Insert the Pareto chart

• Once your data is ready, select the entire table and go to the Insert tab. Here, you will find the ‘ Pareto Chart ’ option under the Statistical grouping

• This will insert the Pareto chart in your sheet. Then, go to the Chart option and select Format to make changes to the title, horizontal axis, vertical axis, or more

Step 3: Format the Pareto chart

To ensure that the Pareto chart in Excel provides complete information, make sure that you do the following with your data:

• Adjust the axis labels: Rename the horizontal axis to display the categories and the vertical axis to show the values or percentages
• Add a title: Give your Pareto chart a descriptive title
• Customize colors and styles: Adjust the colors, fonts, and other visual elements to make the chart more appealing

While Excel 2013 doesn’t have a built-in Pareto chart type, you can still create one using the Combo chart. This requires a few extra steps compared to newer Excel versions, but the process is straightforward.

Similar to the steps mentioned in Excel 2013 and later steps, you will need to add cumulative percentages and columns to your data. Sort the data in descending order before calculating the cumulative values.

Step 2: Create a Combo chart

Once your data is ready, select the table and follow the steps below:

• Select data: Select the entire data table
• Insert chart: Go to the ‘ Insert’ tab and choose ‘Recommended Charts’
• Select Combo chart: Move to the ‘ All Charts’ tab and select the ‘ Combo’ chart type

Under the Combo chart option, select the following settings:

• For the Count Series (usually the first column), choose the ‘ Clustered Column ‘
• For the Cumulative % series (the newly added column), select ‘Line’ and look at the ‘ Secondary Axi s’ box

Step 3: Refine the chart

By now, you will have the Pareto Chart in Excel 2013. However, we recommend these additional steps to get the refined look that you see in the new Excel versions.

• Set maximum percentage: Right-click on the secondary y-axis (the one for percentages) and choose ‘ Format Axis ‘. Set the ‘ Maximum’ value to 100%
• Adjust bar width: Right-click on the bars and select the ‘ Format Data Series ‘. Adjust the ‘ Gap Width’ to reduce spacing between bars
• Hide legend (optional): If you don’t need the legend, right-click on it and choose ‘ Hide Legend’

That’s it! You’ve successfully created a Pareto chart in Excel 2013.

For those who have Microsoft Excel 2010, creating a Pareto Chart can be slightly challenging. In this version, Excel reporting has neither the Pareto chart option nor the feature to combine two charts using the ‘ Combo Chart Type ‘ feature. 

Here’s how you can do it:

You can follow the instructions we gave in the earlier section. First, organize your data in a table with two columns—the categories and the corresponding values. Then, arrange them in descending order of value and add cumulative and percentage values. You can do this manually using a formula or the ‘SUM’ function in Excel 2010.

Step 2: Create a basic chart

• Select your entire data table
• Scroll to the ‘ Insert ‘ tab on the ribbon
• Click on the ‘ Charts ‘ button within the ‘ Charts ‘ group
• Choose the ‘ 2-D Clustered Column ‘ option

This will generate a basic column chart with two data series: one representing the values (count) and another for the cumulative percentage.

Step 3: Format the cumulative percentage

• Right-click on the bars representing the cumulative percentage
• Select ‘ Change Chart Series Typ e’ from the context menu
• Choose the ‘ Line ‘ chart type in the ‘ Change Chart Type ‘ window

Step 4: Add a secondary vertical axis

If you have reached this step, you will notice a bar chart with a flat line. To get the curve that you want to see in the Pareto Chart, add a secondary vertical axis on the right-hand side. To get this:

• Right-click on the newly created line representing the cumulative percentage
• Select ‘ Format Data Series ‘
• In the ‘ Format Data Series ‘ pane, under ‘ Series Options ‘, check the box next to ‘ Secondary Axis ‘

This will add a secondary vertical axis on the right side of your chart specifically for the cumulative percentage values.

Step 5: Refine your chart

With this, you have successfully created a Pareto chart in Excel 2010. To give it the same look and feel that you see with the latest Excel versions, you can make the finishing touches by:

• Setting the maximum value: Right-click on the secondary axis and choose ‘ Format Axis ‘. Under ‘ Axis Options ‘, adjust the ‘ Maximum ‘ value to 100%. This ensures the cumulative percentage line stays within a 0-100% range
• Adjust bar width: You can adjust the width of the bars for better visibility. Right-click on the data bars and select ‘ Format Data Series …’ You can modify the ‘ Bar Width ‘ settings in the formatting options
• Hide legend (optional): If the legend is unnecessary, right-click on the legend and choose ‘ Hide Legend ‘

Limitations of Creating Pareto Charts in Excel

Microsoft Excel is a popular data visualization tool and is still widely used in most organizations. However, despite its simplicity and ease of use, the platform has several limitations, especially when performing a deeper analysis.

Some limitations can significantly impact your data analysis’s efficiency, accuracy, and effectiveness, especially for Pareto charts. These include:

It is difficult to sync and import large datasets from various data channels in Excel. We’ll have to input data manually or export it as a report from other tools into Excel. This is time-consuming and increases the risk of introducing human errors. 

Creating visually appealing Pareto charts in Excel often involves complex formatting techniques, which can be challenging for users who are not proficient in Excel’s features.

While Excel offers some customization options, mastering them can be difficult for everyday users. Modern project management chart tools allow users to do this quickly, using drag-and-drop options with detailed controls over the look and formatting of the Pareto chart.

Although Excel formats and reports are easy to integrate with most tools, integrating data from other project monitoring tools into Excel is not that easy. Integrating Excel charts with other project management or collaboration tools can be cumbersome, hindering efficient workflow and often creating data silos.

Excel does not offer real-time sync or integration with other tools. This can make getting realistic or accurate information with a chart quite a challenge. The Pareto chart has to be updated each time with new information to make it relevant, and managing multiple versions of the same chart can often be difficult, leading to confusion or potential errors. 

The biggest challenge with Excel or any spreadsheet tool is that the app slows down as the data becomes more complex and larger. It can lead to slow responses, noticeable lag in speed, or the spreadsheet not functioning properly or crashing.

These limitations on creating and maintaining a Pareto chart can significantly impact your productivity and the quality of your data analysis. 

To overcome these challenges and create more efficient, accurate, and insightful Pareto charts, one may consider alternative options and project control tools specifically designed for data visualization and collaboration.

Create Pareto Charts with ClickUp

Microsoft Excel may be sufficient for creating a basic Pareto chart. However, for an interactive Pareto chart that updates itself with the latest information and inputs, you’ll need a more advanced solution.

To apply the Pareto principle to your data analysis, try using project control software like ClickUp . This tool allows you to:

ClickUp’s Table View lets you arrange and visualize your data from multiple tools and solutions in a single dashboard. Forget manual entry—use the import function or copy-paste your data directly into the table.

Generate your data in real-time to keep you informed with the latest updates. Organize your data easily using drag-and-drop columns without any Excel formulas or complex processes.

Use ClickUp’s Stacked Bar Graph Template to visualize complex data sets by converting them into interactive stack bar graphs, similar to the Pareto chart. This helps you to:

• Highlight the relationship between multiple data sets
• Understand patterns in your data and recognize the focus areas
• Compare different data sets visually and categories in an easy-to-understand visual chart

Consider the ClickUp Whiteboards feature to gain a collaborative workspace to create your Pareto chart alongside other project elements like tasks, roadmaps, and workflows.

Team members can work on the chart simultaneously, discuss changes, and update the data. This promotes transparency and ensures everyone is on the same page.

With multiple spreadsheet templates , create a chart to get a simple overview of your Pareto chart highlights in your dashboard. An easy-to-use option is the ClickUp Bar Graph Whiteboard Template , which allows you to compare quantitative data visually. This template helps in:

• Visualizing your data in real time using intuitive graphs and a Pareto chart
• Engaging in real-time with your team to brainstorm strategies or identify focus areas
• Organizing your information with custom status, fields, views, and more

A powerful feature of ClickUp is its ability to generate data from multiple tools and visualize it in a single dashboard. This means no more switching between apps or opening multiple windows for your project analysis.

With ClickUp Dashboards , connect your data with other elements like images and links. 

It allows you to leverage the 80/20 principle to get your Pareto chart highlights in one window, helping you understand what works for your organization and what doesn’t.

Try Excel alternatives like ClickUp to:

• Say goodbye to time-consuming formulas and formatting challenges
• Ensure everyone is aligned and contributing to the analysis
• Integrate your Pareto chart with your project management workflow

While ClickUp offers excellent analytical and visualization features to make it the ideal tool for creating Pareto Charts, its utility extends beyond it. This all-in-one project and productivity management solution is a comprehensive solution for all your organizational needs, including:

• Serving as a centralized hub for all your documents: With ClickUp Docs , you no longer have to manage your tasks, projects, documentation, and analysis separately. Just connect them all and store them in a centralized hub to make it easier for your entire team to retrieve and update documentation
• Managing projects and tasks : A major hassle for many teams is working together on action items and collaborating with other team members. Use ClickUp Tasks to simply convert your notes into action items or assign tasks directly. This allows your teams to know exactly what needs to be done and keeps everyone on track
• Setting goals and tracking KPIs : Use ClickUp Goals to apply the 80/20 principle and understand how each activity and task is helping you achieve your targets. By using impact-based indicators, track how much progress you are making in a particular project, removing any noise or unwanted tasks that prevent you from reaching your goals
• Centralizing chat for your entire team:  Are you missing important updates or messages from your team because you are not connected to the communication channel they’re using? With the ClickUp Chat view , all scattered conversations can be brought into one centralized place. 

The Pareto analysis isn’t just about efficiency; it’s about achieving focused excellence. By prioritizing the vital tasks that yield the most significant results, you can optimize your time, reduce stress, and boost your overall productivity.

With process improvement tools like ClickUp, this journey becomes even more seamless. From setting priorities and tracking progress to reducing unnecessary workload, ClickUp empowers you to embrace the Pareto Principle and achieve remarkable outcomes.

Ready to embark on a journey toward focused success? Get started with ClickUp today! 

Questions? Comments? Visit our Help Center for support.

Receive the latest WriteClick Newsletter updates.

Thanks for subscribing to our blog!

Please enter a valid email

• Free training & 24-hour support
• Serious about security & privacy
• 99.99% uptime the last 12 months

Graph Representation Learning enhanced Semi-supervised Feature Selection

New citation alert added.

This alert has been successfully added and will be sent to:

You will be notified whenever a record that you have chosen has been cited.

To manage your alert preferences, click on the button below.

New Citation Alert!

Please log in to your account

Information & Contributors

Bibliometrics & citations, view options, index terms.

Computing methodologies

Machine learning

Learning paradigms

Unsupervised learning

Dimensionality reduction and manifold learning

Learning settings

Semi-supervised learning settings

Machine learning algorithms

Machine learning approaches

Learning latent representations

Information systems

Data management systems

Database design and models

Graph-based database models

Information systems applications

Data mining

Theory of computation

Theory and algorithms for application domains

Machine learning theory

Semi-supervised learning

Recommendations

Semi-supervised feature selection for graph classification.

The problem of graph classification has attracted great interest in the last decade. Current research on graph classification assumes the existence of large amounts of labeled training graphs. However, in many applications, the labels of graph data are ...

Discriminative semi-supervised feature selection via manifold regularization

Feature selection has attracted a huge amount of interest in both research and application communities of data mining. We consider the problem of semi-supervised feature selection, where we are given a small amount of labeled examples and a large amount ...

Instance selection method for improving graph-based semi-supervised learning

Graph-based semi-supervised learning (GSSL) is one of the most important semi-supervised learning (SSL) paradigms. Though GSSL methods are helpful in many situations, they may hurt performance when using unlabeled data. In this paper, we propose a new ...

Information

Published in.

Association for Computing Machinery

New York, NY, United States

Publication History

Check for updates.

• Research-article

Contributors

Other metrics, bibliometrics, article metrics.

• 0 Total Citations
• 23 Total Downloads
• Downloads (Last 12 months) 23
• Downloads (Last 6 weeks) 23

View options

View or Download as a PDF file.

View online with eReader .

Login options

Check if you have access through your login credentials or your institution to get full access on this article.

Full Access

Share this publication link.

Copying failed.

Share on social media

Affiliations, export citations.

• Please download or close your previous search result export first before starting a new bulk export. Preview is not available. By clicking download, a status dialog will open to start the export process. The process may take a few minutes but once it finishes a file will be downloadable from your browser. You may continue to browse the DL while the export process is in progress. Download
• Download citation
• Copy citation

We are preparing your search results for download ...

We will inform you here when the file is ready.

Your file of search results citations is now ready.

Your search export query has expired. Please try again.