Wondering how to turn your boring Excel spreadsheet into something a little more interesting and engaging? To help you, take the time to read this blog post and learn how to create different graphs and charts using Microsoft Excel.
1. Histogram: Data Analysis Tool
A histogram is a commonly used graph to display the underlying frequency distribution, it is used to summarize discrete or continuous data, and shows how often each different value in a set of data occurs. It is a tool used to calculates individual and cumulative frequencies for a cell range of data and data bins.
Histograms can display and provides a visual and graphical representation of a large amount of data, it is used to show how many of a certain type of value or variable occurs within a specific range in a data set. A histogram is used when you are unsure of what to with a large set of data or you have continuous measurements and you want to understand the distribution of values and look for outliers.
How to create a Histogram using Analysis ToolPak
Load the Data Analysis ToolPak in Excel: with the Analysis ToolPak enabled and bins specified, perform the following steps to create a histogram.
Step 1: On the Data tab, in the Analysis group, click the Data Analysis button. In the Data Analysis dialog, choose Histogram and click OK.
Step 2: In the Histogram dialog window, specify the Input range, Bin range, and Output range. Don’t forget to click the Chart Output checkbox, and then click OK.
Step 3: To remove space between bars, select the bars, right-click, and choose Format Data Series.
Step 4: On the Format Data Series pane, set the Gap Width to zero.
Step 5: Optional: If you want to change the bar colors, click Vary color by point.
You can enhance and customize your Excel histogram by modifying the chart title, axes titles, and changing the chart style or colors.
2. Box Plot
Box plot is commonly used to show statistical data such as school grades or scores and data from before and after a process change. It is useful when comparing samples and testing whether data is distributed symmetrically. It is also used to show the minimum value, first quartile, median, third quartile, and maximum value of a data set. Box plot summarizes data from multiple sources and displays the results in a single graph.
How to create a Blox plot in Excel
Step 1: Highlight your data, on the Insert tab, in the Charts group, click the Statistic Chart symbol and select Box and Whisker.
Your box plot will pop right into your spreadsheet.
Step 2: To customize your box and whisker plot, right-click, and choose Format Data Series.
You can also change the design of your box plot on the Design tab.
3. Scatter Plot
A scatter plot works best when comparing large numbers of data and shows a relationship between two variables. Scatter plots can also show if there are any unexpected gaps in the data and determine if there are patterns or correlations between two variables and show how much one variable is affected by another. It is best to use when you want to visualize your data set.
How to create a Scatter Plot in Excel
Step 1: Select two columns with numeric data, on the Insert tab, in the Charts group, click the Scatter symbol and select desired template.
The scatter plot will be immediately inserted in your excel worksheet.
Step 2: To add title, on the Design tab, click Add Chart Element and select both the Horizontal and Vertical axis titles.
Step 3: To add Trendline that shows the generalization of your data, select Trendline and choose Linear.
You can customize or change the design of your scatter plot on the Design tab.
4. Pie chart
Pie Charts are commonly used to show percentage or proportional data as well as when dealing with grouped data or data which is categorized. A pie chart works really well with categorical data, it represents large data in a simple graphical manner and is good for visualizing how each part contributes to that whole.
How to create Pie Chart in Excel
Step 1: Highlight you data, on the Insert tab, in the Charts group, click Pie symbol and select desired template.
Step 2: Your Pie chart will pop right into your spreadsheet. Change design on the Design tab (optional).
Step 3: To customize your pie chart, right-click, and choose Format Data Labels.
5. Bar Graphs
Bar graphs are commonly used to compare things or items between different groups and data that show changes over time. Bar graphs are used to display data that is classified into ordinal or nominal categories. Bar graphs can be displayed with vertical columns, horizontal bars, comparative bars, or stacked bars. It is ideal for comparing two or more values, or values over time, displaying a large number of categories, and used for comparing a part to a whole relationship of category.
How to create a Bar Graph in Excel
Step 1: Highlight your data, on the Insert tab, in the Charts group, click Bar Chart button and select your desired template.
Step 2: To change color, design, and other layout, click Design tab.
Step 2: To customize your bar graph, right-click, and choose Format Data Labels.
Related Links:Make a Histogram Using Excel’s Histogram tool in the Data Analysis ToolPak Creating a boxplot in Microsoft Excel 365 Creating a Scatter Plot in Excel 2016 Excel Pie Chart – Introduction to How to Make a Pie Chart in Excel How to Make a Bar Graph in Excel