Histogram

Added in 2016
If the data values are continous it makes sense to display a sequence of numbers on the x-axis indicating the inclusive upper bound for each interval. ??
https://support.office.com/en-gb/article/Create-a-histogram-85680173-064b-4024-b39d-80f17ff2f4e8?ui=en-US&rs=en-GB&ad=GB



Alternative

alt text

Start with a list of values, 30 random values between 1 and 30 (B3:B32).
Create a table with 3 columns, Range, Upper, Frequency (D2:F12).
In the first column (D) add the ranges which you would like displayed on your histogram. We are going to use blocks of 3: 0-3, 3-6, 6-9, etc
In the second column (E) add the numerical value which is the largest in each range: 3, 6, 9, 12 etc
In the third column (F) use the FREQUENCY function to add an array formula to return the frequency of each range.

alt text

Step 1 - Create Clustered Column Chart

Select the cells F3:F12 and create a Clustered Column Chart

alt text

Step 2 - Add Another Series

Select cells "E6:F7" from the middle of the data range
Select Copy
Click on the Chart Area
Click on the Paste drop-down and select Paste Special. This will display the following dialog box.
Select "Categories (X Labels) in First Column

alt text
alt text

Step 3 - Change Chart Series Types

Click on either of the chart series and right mouse click.
Select Change Series Chart Type Change to display the Change Chart Type dialog box.
Change series 1 to be on Seconday Axis
Change series 2 to be an XY Scatter chart type

alt text
alt text

Step 4 - Add Secondary Horizontal Axis

Select the Chart Tools, Design Tab, Add Chart Element, Axes, Secondary Horizontal
This will invert the chart

alt text
alt text

Step 5 - Remove Secondary Vertical Axis

Select the Chart Tools, Design Tab, Add Chart Element, Axes, Secondary Vertical
Unselect Secondary Vertical to remove the secondary vertical axis
Remove the horizontal gridlines
Click on the gridlines and press Delete.

alt text

Step 6 - Cross Axis at Zero

Change the secondary axis to cross at 0
Select the vertical (Value) axis and right mouse click.
Select Format Axis Change
Display the Axis Options and change the "Horizontal axis crosses, axis value" to 0

alt text
alt text

Step 7 - Remove the Secondary Horizontal Axis

Select the Chart Tools, Design Tab, Add Chart Element, Axes, Secondary Vertical
Unselect Secondary Horizontal to remove the secondary horizontal axis

alt text

Step 8 - Change Horizontal Axis Scale

Select the horizontal (Value) axis and right mouse click
Select Format Axis to display the Axis Options
Change the Minimum = 0, Maximum = 30 and Major = 3 (this is the interval width)

alt text
alt text

Step 9 - Hide Scatter Line Markers

Select the scatter markers and right mouse click
Select Format Data Series to display the Series Options
Select the "Fill & Line" options and expand the Marker Options Marker Options, select None

alt text
alt text

Step 10 - Remove Bar Gaps

Remove the gaps between the bars
Click on one of the columns and select Format Data Series Series Options, change the gap width to "0"

alt text


© 2023 Better Solutions Limited. All Rights Reserved. © 2023 Better Solutions Limited TopPrevNext