How do I make a graph from an Excel spreadsheet?

All CoursesLog inData Science & Business AnalyticsData Science & Business AnalyticsAI & Machine LearningProject ManagementCyber SecurityCloud ComputingDevOpsBusiness an

How do I make a graph from an Excel spreadsheet?
Simplilearn - Online Certification Training Course Provider

All CoursesLog inData Science & Business AnalyticsData Science & Business AnalyticsAI & Machine LearningProject ManagementCyber SecurityCloud ComputingDevOpsBusiness and LeadershipQuality ManagementSoftware DevelopmentAgile and ScrumIT Service and ArchitectureDigital MarketingBig DataCareer Fast-trackEnterpriseOther SegmentsArticlesEbooksFree Practice TestsOn-demand WebinarsVideo TutorialsLive WebinarsHomeResourcesData Science & Business AnalyticsA Step-by-Step Guide on How to Make a Graph in Excel

Behavioral Analytics: Use Cases, Techniques and TechnologiesArticle

Data Analyst Salaries Around the World (Based on Industries, Countries and Experience)Article

Free eBook: Secret to Unlocking Tableau's Hidden PotentialEbook

How to Build a Successful Data Analyst CareerArticle

Data Analyst Job Description: Responsibilities, Skills Required, and Top Companies HiringArticle

How to Become a Data Analyst: A Step-by-Step GuideVideo Tutorial

Top 25 Excel Formulas You Should Know [Updated]Article

50 Excel Shortcuts That You Should Know in 2022Article

How to Ace Data Science Interviews With Manufacturing CompaniesWebinar

Data Science vs. Data Analytics vs. Machine Learning: Expert TalkArticle

A Step-by-Step Guide on How to Make a Graph in ExcelBy SimplilearnLast updated on Jul 16, 202219044

A Step-by-Step Guide on How to Make a Graph in Excel

Table of ContentsView More

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.

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

intro_graph_GraphInExcel

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

The next section will help you understand the different types of graphs available.

Business Analyst Master's ProgramGain expertise in Business analytics toolsExplore Program

Business Analyst Master's Program

What are the Types of Graphs Available in Excel?

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.

Type_barGraph

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.

Type_PieChart_Graph

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.

Type_lineGraph_Graph

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.

Type_scatterPlot_GraphInExcel

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.

Type_areaGraph_GraphInExcel

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.

Lets move forward and understand how to make a graph in Excel.

Data Cleaning

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.

remove_duplicates_command

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

How to Make a Graph in Excel

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

  • Bar Graph
  • Pie Chart
  • Scatter Plot

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

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

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

covid_dataset_1

Lets 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 youve selected. Inorder to do this, select the whole dataset, locate the Data tab and click on the Filter option.
filter_1
  • 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.
asia_filter_2_DashboardInExcel
  • 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.
asia_filter
  • 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.
/covid_dataset_GraphInExcel
  • 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.
chart_options
  • 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.
  • Here, the chart data range can be added by simply dragging the mouse and selecting the required data.
  • 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.
data_source.
  • 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.
asia_graph
  • To make your charts attractive and aesthetically appealing, you may change the color palette of the graph, add text or display more information about it, etc.
  • 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.

format_chart_DashboardInExcel

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

FREE Course: Introduction to Data AnalyticsMastery to Data Analytics Basics is a Click Away!Start Learning

FREE Course: Introduction to Data Analytics

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.
/pieChart_data
  • Select the data range. Then, click on the Insert Tab. You will find the PIE chart option available in the charts group.
pieChart_optionGraphInExcel
  • 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.
  1. 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.
  2. For labels on the horizontal axis labels, you may select confirmed cases, deaths, recovered, and active cases, and depict them on the chart.
  3. After specifying the entries, click on OK. This will display the pie chart on your window.
pieChart_dataSource
  • 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.
pie_chart_GraphInExcel

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:

  • Date
  • Confirmed Cases
  • Deaths
  • Recovered Cases
  • Active Cases
scatterPlot_data

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.
scatterPlot_selectedData.
  • Go to Insert Tab  Charts Section  Scatter Plot Option.
scatterPlot_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.
/scatterPlot_graph_GraphInExcel

In the final section, lets look at how to make a histogram.

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, lets look at an example.

The following Employee Salary dataset contains the columns:

  • Employee ID
  • Employee Name
  • Job Title
  • Total Pay

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

histogram_dataset.

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.
insert_histogram_GraphInExcel
  • 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.
histogram_1
  • 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.
histogram_formatAxis
  • 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.

histogram_formatAxis_2
  • Right-click on the chart and click on Add Data Labels to include the values on top of each range.
/addDataLabels
  • After formatting the histogram accordingly, we arrived at the following graph.
final_histogram_GraphInExcel.

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

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

Gain expertise in the latest Business analytics tools and techniques with theBusiness Analyst Master's Program. Enroll now!

Conclusion

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 Business Analytics Course offered by Simplilearn.

If you have any questions, please mention them in the comments section, and our experts will get back to you as soon as possible!

Find our Business Analyst Online Bootcamp in top cities:NameDatePlaceBusiness AnalystClass starts on 22nd Aug 2022,
Weekdays batchYour CityView DetailsBusiness AnalystClass starts on 5th Sep 2022,
Weekdays batchYour CityView DetailsBusiness AnalystClass starts on 10th Sep 2022,
Weekend batchYour CityView Details

About the Author

Simplilearn

Simplilearn

Simplilearn is one of the worlds leading providers of online training for Digital Marketing, Cloud Computing, Project Management, Data Science, IT, Software Development, and many other emerging technologies.View More

Business Analyst

Business Analyst21257 LearnersLifetime Access*

Post Graduate Program in Business Analysis

Post Graduate Program in Business Analysis4502 LearnersLifetime Access*

Business Analytics with Excel

Business Analytics with Excel41405 LearnersLifetime Access*

*Lifetime access to high-quality, self-paced e-learning content.Explore Category

Business Analyst Interview Questions

Next Article

Business Analyst Interview QuestionsBy Nikita Duggal498967Jul 20, 2022

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

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

Business Analyst vs Data Analyst: Differences and Career Paths Explained

Business Analyst vs Data Analyst: Differences and Career Paths ExplainedArticle

The Best Guide On How To Become A Business Analyst

The Best Guide On How To Become A Business AnalystVideo Tutorial

Data Analyst Resume Guide

Data Analyst Resume GuideEbook

Understanding the Role of an IT Business Analyst and How to Become One

Understanding the Role of an IT Business Analyst and How to Become OneArticle

The Top 10 Skills You Need to Have to Be a Business Analyst

The Top 10 Skills You Need to Have to Be a Business AnalystVideo TutorialprevNext

© 2009 -2022- Simplilearn Solutions

Follow us!Refer and Earn

CompanyAbout usCareers In the media Alumni speak Grievance redressalContact us

Work with usBecome an instructorBlog as guest

DiscoverSkillupSkillup SitemapResourcesRSS feedSimplilearn Coupons and Discount OffersCity Sitemap

For BusinessesCorporate trainingPartnersDigital Transformation

Learn On the Go!Get the Android AppGet the iOS App

Trending Post Graduate ProgramsProject Management Certification Course | Cyber Security Certification Course | PG in Data Science Program | Data Analytics Bootcamp Program | Business Analysis Certification Course | Digital Marketing Certification Program | Lean Six Sigma Certification Course | Cloud Computing Certification Course | Data Engineering Bootcamp | AI and Machine Learning Course | Full Stack Web Development Course

Trending Master ProgramsPMP Plus Certification Training Course | Big Data Engineering Courses | Data Science Certification Course | Data Analyst Certification Course | Artificial Intelligence Course | Cloud Architect Certification Training Course | DevOps Engineer Certification Training Course | Advanced Digital Marketing Course | Cyber Security Expert Course | MEAN Stack Developer Course

Trending CoursesPMP Certification Training Course | Big Data Hadoop Certification Training Course | Data Science with Python Certification Course | AWS Solutions Architect Certification Training Course | CISSP Certification Training | Certified ScrumMaster (CSM) Certification Training | ITIL 4 Foundation Certification Training Course | Java Certification Course | Python Certification Training Course

Trending ResourcesPython Tutorial | JavaScript Tutorial | Java Tutorial | Angular Tutorial | Node.js Tutorial | Docker Tutorial | Git Tutorial | Kubernetes Tutorial | Power BI Tutorial | CSS Tutorial

  • Terms of Use
  • Privacy Policy
  • Refund Policy
  • Reschedule Policy
  • © 2009-2022 - Simplilearn Solutions. All Rights Reserved. The certification names are the trademarks of their respective owners.smpl_2022-08-14
  • Disclaimer
  • PMP, PMI, PMBOK, CAPM, PgMP, PfMP, ACP, PBA, RMP, SP, and OPM3 are registered marks of the Project Management Institute, Inc.

Video liên quan