Histograms are useful when you wish to have a pictorial representation of data shown in a spreadsheet format. In such a graph there are rectangles that showcase area proportional to a variable’s frequency. Width is that of the class interval.
Add Histogram to Represent Data in Excel
- Step 1
Start by opening an existing Excel or spreadsheet. Here one will find columns of data that are correlated. For instance, if we find two columns of interrelated data in the spreadsheet shown below, the number of students in different classes is shown below and the frequency of numbers is found out in a corresponding column.
- Step 2
On the top panel of Excel, find Data tab. This is a function found in Data Analysis section. There are different functions showcased here such as What If Analysis, Forecast Sheet, and Subtotal, Group, Ungroup and others. If the Excel pack does not include this section, you can find free links online where the add-in Analysis ToolPak can be added as a handy extension tool to Excel.
- Step 3
In this extension there is a Data tab. When the Data Analysis option is clicked, a drop down menu of different choices comes up. Here one can select Histogram for insertion and Ok to exit.
- Step 4
Select the data range A2 to A19 in the Histogram panel for Input Range. To specify the ‘Input’ and ‘Bin’ range, one needs to place the cursor in a box and select range on the worksheet. There is a Collapse Dialog button which allows one to select the range on sheet. Click on the same button to return back to the dialog box for a Histogram.
The next field is for Bin Range. Here you can select C4:C8 as the range. Among the output, options select Output Range. It is found in the upper-left cell in the output table. Present data in the output table by descending order or Pareto option; one can also show a cumulative percentage line by the same option given. Here you can select the F3 cell. Check on the chart output option and then click on Ok.
- Step 5
When the above data is entered and completed, the resulting Histogram chart will appear as shown below. The two fields, Bin, and Frequency act as the X-axis and the Y-axis respectively.
How to Create a Histogram with PivotChart?
When bins are used in Excel and then used to represent histograms, the task can be time-consuming. On the other hand, when source data is put in a group, the drawing of a histogram chart is an easy one. PivotTable is one of the easy and fast ways one can summarize data automatically in Excel. Here is an example of Order Numbers and Delivery Data put in two columns.
- Step 1: Create PivotTable
To create such a table you need to click on the ‘Insert’ tab. Here Tables group exists. Under this option, one can find the PivotTable. You can then move the ‘Delivery’ field to ROWS and Order Number column can be Values. The screenshot below makes this clear.
- Step 2: In a PivotTable
The numeric fields are added up by default and so would be the column of Order Numbers. To create histogram a count is required rather than a summation. For this one can right-click on any cell containing order number and select to ‘Summarize Values’ by the > count.
- Step 3: Create Bins or Intervals
Start by creating bins or intervals. Grouping option can be used by right-clicking on a cell under the heading titled Row Labels. Select the Group option. In the dialog box given you need to specify start and end values as well as type in increment or interval length required in ‘By’ slot.
In the above example, one can set minimum delivery time to be 1 day and maximum to be 40 days with the increment interval to be set as 5. Upon clicking ‘OK’ PivotTable displays intervals as mentioned.
- Step 4: Plotting the Histogram
Here histogram needs to be drawn by creating a columnar pivot chart. Click on the ‘Analyze’ tab in the Tools group. The default column-based PivotChart will appear.
How to Add the Finishing Touches to a Histogram?
Step 1: Delete Legend
This can be done by clicking on the Chart Elements option. Remove the ticked option on Legend or highlight the legend, pressing Delete to remove the same.
Step 2: Editing the Title and Chart Layout
The title that comes on by default can be customized or edited as per one’s choice. The style of the title or the chart can be changed by Chart Styles option as found in PivotChart Tools where the ‘Design’ tab is found. Remove chart buttons through Field Buttons. Here you can use the ‘Analyze’ tab where Show/Hide options are present.
Step 3: Change Axis Labels
The Analysis ToolPak in Excel has options for adding on different axis labels as per bin numbers specified. One can choose to show display ranges in place of bin numbers. In order to change axis labels one can right-click on category labels and choose the option called ‘Select Data’. Here edit button helps to change legend entries.
The box for axis label range gives the option for labels you wish to display which can be separated with commas. To specify intervals one can enclose labels within double-quotes.
Step 4: Remove Space between Bars
If adjacent columns need to touch and gaps to be removed, it is something easily fixed. Empty space between bars can be removed by selecting bars, right-clicking on the same and choosing the option of formatting data series.
The pane on ‘Format Data Series’ sets ‘Gap Width’ can be set to zero. Once this is done, the columns do not have any gaps between each other.
Step 5: Other Modifications
There are other kinds of embellishments that one can add such as modifications to axes and chart titles, style and colors. Such changes are done to the chart above as seen below.
The above sections showcase how histograms can be placed on Excel and changes made using the different analytical and design tools offered.
How do you manually create a histogram?
- On the vertical axis, place frequencies. Label this axis “Frequency”.
- On the horizontal axis, place the lower value of each interval.
- Draw a bar extending from the lower value of each interval to the lower value of the next interval.
How do you create a histogram for grouped data in Excel 2016?
- Select the entire dataset.
- Click the Insert tab.
- In the Charts group, click on the ‘Insert Static Chart’ option.
- In the HIstogram group, click on the Histogram chart icon.
How many bins should a histogram have?
How do you read histograms?
What makes a good histogram?
What is a good histogram?
What is the best histogram shape for photography?
What is the difference between a histogram and a bar graph?
How do you read an RGB histogram?
What is a hot shoe in photography?
Why is it called a hot shoe?
What does Hot Shoe mean?
noun. Photography. a bracket on a camera body that provides support and electrical contact for an electronic flashattachment.
What is a Sony hot shoe?
Are Canon and Sony hot shoes the same?
What type of camera is the most popular to capture photos on?
What is a hot shoe adapter?
Are all hot shoes the same?
Ads by Google