Tuesday, May 27, 2014

Creating an Automatically Updating Histogram in 7 Steps in Excel With Formulas and a Bar Chart

This is one of the following two articles on creating Histograms in Excel

Creating a Histogram With the Histogram Data Analysis Tool in Excel

Creating an Automatically Updating Histogram in 7 Steps in Excel With Formulas and a Bar Chart

 

Creating an Automatically

Updating Histogram in 7

Steps in Excel With

Formulas and a Bar Chart

A histogram ultimately provides the count of observations in each bin. These bin counts can be obtained using Excel formulas as follows:

excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

There are two formulas that will provide the bin counts. The COUNTIFS() formula works in Excel versions 2007 and later. The SUMPRODUCT() formula works in previous Excel versions and the current version as well.

Cell J4 contains the following formula:

=COUNTIFS($F$4:$F$29,”>-2.5”,$F$4:$F$29,”<=-1.5”)

The COUNTIFS() formula counts the rows in the given range that all of the listed criteria are met. In this case the COUNTIFS() formula will count all rows from 4 to 29 in which the data in F4:F29 is both greater than –2.5 and less than or equal to –1.5. The syntax for the formula is the following:

COUNTIFS(range 1, criteria 1, range 2, criteria 2, ….)

The ranges must all have the same rows.


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

In this example the following histogram bar chart will be created from the X-Y data in cells I4 to J8 that contain the bins counts.


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 1 – Highlight the Y Data

Select the data in cells J4 to J8.


excel, excel 2010, excel 2013, histogram, graph

 

Step 2 – Select Chart Type

Make the following selections:

Insert tab / Column / 2-D Clustered Column
excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This produces the following basic bar chart:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 3 – Insert X-Axis Data

Right-click anywhere in the chart and choose Select Data as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This brings up the following dialogue box. Under the label Horizontal (Category) Axis Labels on the right side, select the Edit button.


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This brings up a blank Axis Labels dialogue box as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

Select the X-Axis data in cells I4 to I8 as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This produces the following labels for the X-Axis:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

Format these labels by right-clicking on the X-Axis, select Bold, and then set the font size in the cell font dialogue box as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This produces the following X-Axis:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 4 – Delete the Legend

Select the legend and delete it.


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 5 – Insert Chart Title

Click anywhere on the chart to bring up the Chart Tools menu. Choose the Layout tab / Chart Title / Above Chart as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

This creates up the following basic chart title:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

Highlight the text and type in the correct title as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 6 – Insert X-Axis Label

Right-click anywhere on the chart to bring up the Chart Tools menu. Choose Layout tab / Axis Titles / Primary Horizontal Axis / Title Below Axis as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

Highlight the basic X-Axis label that appears. Type in the correct X-Axis text, select Bold, and select the correct font size as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Step 7 – Insert Y-Axis Label

Right-click anywhere on the chart to bring up the Chart Tools menu. Choose Layout tab / Axis Titles / Primary Vertical Axis / Horizontal Title as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

After correcting the Y-Axis label by typing in the correct text, making it Bold, and setting the correct font size as was just done for the X-Axis label, the final bar graph will appear as follows:


excel, excel 2010, excel 2013, histogram, graph (Click on Image to See an Enlarged Version)

 

Excel Master Series Blog Directory

Statistical Topics and Articles In Each Topic

 

Posted by at

4 comments:

  1. shopifyMarch 14, 2022 at 2:24 AM

    Outsourcing save time There are various benefits of Outsourcing saving time as it allows companies to become more cost-effective and efficient.

    ReplyDelete
  2. AnonymousMarch 14, 2022 at 4:14 AM

    Web Application Development Process In the next step, you need to map out the workflow of how you are going to work. Get the answer to what needs to happen to solve the problem in terms of a web application.

    ReplyDelete
  3. whome2249August 26, 2022 at 1:02 AM

    It's reasonable to think of therapy as a mental health check when it comes to asking for help, so this site https://us.calmerry.com/blog/therapy/do-i-need-therapy-signs-its-time-to-see-a-therapist/ is where you can find out if therapy is needed, there's a whole blog on the subject, signs that it's time to see a therapist

    ReplyDelete
  4. Angel17May 25, 2023 at 10:28 AM

    Thanks for sharing these tips. So useful. I can really apply this. truckpartsuperstore.ca

    ReplyDelete

Subscribe to: Post Comments (Atom)