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.
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.
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.
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.
Let’s move forward and understand 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:
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?
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
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.
- 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.
- 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.
- 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.
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.
In the final section, let’s look at how to make a 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
- Job Title
- Total Pay
We will demonstrate how to create a histogram that depicts the number of employees under each salary group.
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 creating a graph in Excel.
Gain expertise in the latest Business analytics tools and techniques with the Business Analyst Master's Program. Enroll now!
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 Certification Course with Excel 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!