Microsoft Excel is the most used software for statistics in the world. Whether you are a student, a business man, accountant or from any walks of life that involves numbers and graphs, you must have used it. And it is impossible to do statistics without graphs and charts, and Microsoft Excel has tons of options when it comes to graphs. So, for that we have prepared the most complete guide on how to make a graph in excel, encompassing all kinds of available graphs.
Therefore, this is going to be the last guide you will ever have to read on how to make graphs in excel, hopefully.
Graphs & Charts available in Excel
Microsoft Excel has tons of types of charts and graphs and while all of them can be used to visualize any kind of data. However, it is best to choose what kind of graph or chart will suit to your needs the best.
Therefore, learning what kind of graphs are available is necessary to learn how to make a graph in excel.
Microsoft Excel 2016 and 2019 has 14 types of charts or graphs. These are:
- Column Chart
- Line Chart
- Pie Chart
- Bar Chart
- Area Chart
- Scatter Charts
- Stock Chart
- Surface Chart
- Radar Chart
- Sunburst Chart
- Box & Whisker
Also, all of these types have further sub-types.
You can find these types by going to “Insert” ribbon, then clicking “Recommended Charts” and then under “All Charts” tab.
Let’s go over each type one-by-one:
Column Charts are best to visualize the difference or comparison of information of different categories. For example, in terms of blogging, comparing traffic coming from different sources to your website during a period.
Excel has seven different column chart types: clustered, stacked, 100% stacked, 3-D clustered, 3-D stacked, 3-D 100% stacked, and 3-D, as shown in picture below:
Line Charts are best suited to show continuous data over time. The lines connect each data point which visualize that whether the values have increased or decreased during a period.
The best example of this can be charting temperature of a room or atmospheric temperature over time.
Once again, seven line chart options are available in Microsoft Excel, which are line, stacked line, 100% stacked line, line with markers, stacked line with markers, 100% stacked line with markers, and 3-D line.
Pie Charts are made for showing information of various categories of full whole of 100%. Each value is represented as a piece of the pie so you can identify the proportions.
There are five pie chart types which are 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.
Bar Charts are basically horizontal versions of column charts. There is no other difference.
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.
Are charts also show changes in values over time, like line charts. However, Area charts also show the area under the curve to visualize the product of both variables on x and y axis.
There are six area charts which are area, stacked area, 100% stacked area, 3-D area, 3-D stacked area, and 3-D 100% stacked area.
Scatter charts are similar to line graphs, because they are useful for showing change in variables over time. However, scatter charts are used specifically to show how one variable affects another which is called as correlation.
There are seven scatter chart options which are 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.
Used to display high, low and closing prices of stock. Not used widely. Only used much in finance industry.
However, they still can be used to show the range of values from lowest to minimum.
There are four types of stock charts available in Excel. They are High-Close, Open-High-Low-Close, Volume-High-Low-Close and Volume-Open-High-Low-Close.
Surface charts are used to represent data across a 3D 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, otherwise, they will only get confused.
And don’t use surface charts if other charts can also work. They should only be last resort due to high complexity.
Four types of surface charts are available in MS Excel. 3-D surface, wireframe 3-D surface, contour, and wireframe contour.
Radar Charts are best when your intention is to show the relationship between all variables from a reference point.
For example, They can be used for comparing strengths and weaknesses of different products or strategies.
There are three radar chart types in MS Excel namely radar, radar with markers, and filled radar.
Treemap chart is best used to visualize hierarchical data, where various variables are connected to each other. It is done by using nested figures which are usually rectangles.
A sunburst chart is used to display hierarchical data like Treemap, however in a circular format where each level of the hierarchy is represented as a ring.
Top level categories make up the inner ring, and sub-categories are plotted as outer rings.
Histogram shows data using bars of different heights. It is kind of like bar chart, however the difference is that a histogram groups the data into ranges instead of representing each variable as itself.
Box & Whisker
Box and whisker plots are very effective and easy to read, as they can summarize data from multiple sources and display the results in a single graph. Box and whisker plots allow for comparison of data from different categories for easier, more effective decision-making.
It is usually used in quality control.
A waterfall chart is a series of column graphs that show positive and negative changes over time
Waterfall Chart helps in understanding the cumulative effect of sequentially introduced positive or negative values. These intermediate values can either be time based or category based.
How to Make a graph in Excel
When it comes to making a graph or chart in excel. There are 5 steps, which are:
Step 1: Entering Data
Excel sheet has columns and rows. You can put your data into a sheet by inputting it into boxes.
So, just designate which column represents which variable and start writing down your data.
Step 2: Choosing the Range
After you have entered data, you need to select the range over which your chart is to be made.
You can do this by highlighting the data range from which you want to make a chart, 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.
Step 3: Creating the Chart
After you have selected the data range, go to insert ribbon and click Recommended Charts Button, which was mentioned above.
From there choose the type of chart you want to make, which will be dependent upon the kind of data you have.
For this tutorial, we will make a simple line chart.
Step 4: Editing the Chart
After creation of chart or graph, you need to edit it.
This includes changing the title, name of values on x and y axis and changing color and style etc.
You can do this by adding more chart elements by clicking on + icon on top right side of chart. This will open a menu of various chart elements. Just select the ones you need.
Options available here are shown in picture above.
Step 5: Formatting the Chart
Now, you need to stylize and format the chart. Styles can be chosen by clicking on the brush under the + button.
You can get further formatting options by double right clicking anywhere on the chart, and a formatting panel will open up on the right side of screen.
Here, you can click any component of chart by clicking the component, and start formatting it. Many more options are available here like line transparency, width and so much more.
Some Tips for Graphs and Charts
Excel may provide tons of styles and formatting options, but at the end of the day, it is you who have to decide which style to choose and how to format it. So, in order to make a compelling chart, follow these simple tips:
- Make Charts Simple.
- Use clean texts and wisely.
- Choose appropriate themes.
- Place elements only if needed.
So, that is all you need to know about how to make a graph in excel. Try these steps and tips and hopefully, all these tips will be useful to you.
Apart from learning how to make a graph in excel, also read:
Thanks for Reading. Be sure to leave your comments.