Making a Histogram in Excel for Mac: 2 Techniques
Prologue
In this article, you’ll learn how to make a histogram in Excel for Mac. There will be two methods to do so.
The Analysis ToolPak will be used in the first method, and the second method will involve inserting a clustered column chart.
The first feature is not available in Excel for Mac 2011, so we’ll use the COUNTIF formula to find the frequency to create a histogram by using the Recommended Charts feature.
What Is Histogram?
The histogram is a graphic representation of the distribution of a value across a number of ranges, or bins. This is basically a column chart that has no gaps between the bars. The height of the vertical bars represents the number of data points that a bar contains. Therefore, more data is represented by a taller bar.
Applications for Histogram
We utilize this tool in many areas of our lives, some of which are listed below:
- If you’ve used photo editing software, you’ve seen the histogram. This is used to set the brightness, contrast, and color balance of an image.
- This is used to visualize computer resource utilization patterns. This tool can display CPU usage, RAM usage, network traffic, disk usage, etc.
- You can use this to visualize the demographics of the target audience for a product. We can use this, for example, to determine which age group is most interested in our Excel articles.
- Buyer behavior, product ratings, and customer preferences can be easily understood using this.
- Using this feature, a teacher can visualize the grade distribution of the class. It will show what grade the majority of the students receive. This is particularly useful if the teacher wishes to curve the grade.
Using Data Analysis to Make Histogram on Mac
In this first method, you’ll need to use the Data Analysis feature to create a histogram in MacOS. Before doing so, you must enable the Analysis ToolPak Add-ins.
- Firstly, Select Excel Add-ins from the Tools tab.
- Secondly, the Add-ins dialog box will pop up.
- Thirdly, select the Analysis ToolPak and press OK.
- Afterward, you’ll see the Data Analysis feature appear on the Data tab.
- Now, let’s look at the dataset for this article. This dataset shows the ages of the 24 players on the Brazil national soccer team.
- Moreover, there are also bins for this data. The four data points are taken randomly.
- Now, click on Data Analysis from the Data tab.
- Then, select Histogram and press OK.
- After that, another dialog box will appear.
- Then, provide the following information in the fields:
- Input Range: The age range of the players (C3:C26).
- Bin Range: The assumed bin range (E12:E15).
- Output Range: Set the location where the output will be shown (H6).
- Next, Select Chart Output.
- Afterward, press OK.
- You’ll get the following image.
- This doesn’t quite look like a histogram. Remember, they are gapless!
- So, we’re going to fix this next.
- Double click on any of the columns.
- Then, the Format Data Point dialog box will appear.
- Set the Gap width to 0%.
- This will turn the column chart into a histogram.
- Furthermore, you can tweak the look of the histogram by applying chart styles.
- Select the histogram.
- Then, from the Chart Design tab, select Style 4.
- Finally, it will enhance the histogram for Excel Mac.
Using Recommended Charts Feature to Make Histogram
The Data Analysis feature is not available in Excel 2011 for Mac. So, in this method, you’ll learn about an alternative method to create a histogram on Mac. We’re using the same data as the previous method in this section.
Now, we’ll need to use four formulas to count the frequencies. These formulas count the number of ages that are older than the bin value but younger than the previous value.
- Firstly, use the following formula in cell H6. The first formula counts the number of players whose ages are greater than or equal to 31.
=COUNTIF(C3:C26,”>=”&F6)
- Secondly, use the following formula in cell H5. The second formula counts the number of players who are older than or equal to 27 but younger than 31, and so on.
=COUNTIF(C3:C26,”>=”&F5)-G6
- Thirdly, insert the following formula in cell H4.
=COUNTIF(C3:C26,”>=”&F4)-G6-G5
- Fourthly, use the following formula in cell H3.
=COUNTIF(C3:C26,”>=”&F3)-G6-G5-G4
- Afterward, select the bin and the frequency range with labels (i.e., F2:G6).
- Then, select Recommended Charts from the Insert tab.
- After that, a list of available charts will be shown.
- Select Clustered Column from the list.
- Then, a basic clustered column chart will pop up.
- Finally, reduce the gap to 0% and apply a chart style to finish creating a histogram in Excel Mac.
Facts About Histogram for Mac Excel
- The Analysis ToolPak is not available in Excel for Mac 2011. So, you should use the second method to create a histogram on Mac.
- The choice of bins can affect your histogram. So, use trial and error to see which bins are great for your dataset.
- Histograms provide a summary of the data, so they may lose a lot of data details.
- Outliers can be hard to visualize in the histograms.
- You can only use this with a single variable.
- We’re using Microsoft Excel for Mac version 15.27 (161010). If you’re using other versions, the features of this article may look different.
Download Section
You can download the Excel file here.
Epilogue
In this article, you’ve seen two methods to create a histogram in Excel for Mac. If you’ve got anything to share, please feel free to comment below. You can also follow Excel Flash on Twitter and YouTube.
Next Read
- This will appear soon