How to build a histogram in Excel

Being a spreadsheet, Microsoft Excel could not have had a functionality to plot histograms, these column charts. It is a clear way, because simple, of representing a distribution by classes, for example the distribution of the averages of the pupils of a class in college or in high school.

Steps

Part 1 of 3: prepare histogram data in Excel

Step 1. Run Microsoft Excel

Its icon is a green folder with a white “X” on the cover. The home page offers you to create different files.

In macOS, Excel may open directly on a blank spreadsheet: then go to 3^e stage.

Step 2. Create a new document

Click on New workbook in the upper left corner of the window (Windows), or click File, then on New workbook (Mac).

Step 3. Determine the extreme values of your sample

It is in fact a question of determining the amplitude of your data in order to operate a relevant division into classes.

Suppose you have 5 data between 17 and 225, the minimum data is 17 and the maximum 225, and the amplitude is 208

Step 4. Determine the number of classes

Each will be represented by a column. The simplest way to determine the number of classes is to divide the maximum value (here 225) by the size of the distribution, that is, the number of values (here, 5) in the sample: round off the result. This method is especially valid for small samples with a homogeneous dispersion. For larger distributions (several tens or hundreds of data), other methods of division into classes (we also speak of discretization) are possible and desirable.

Sturges' rule proposes to split a range of values into classes according to the following formula: k = 103 × log10 (N) { displaystyle k = { frac {10} {3}} times log_ {10} (N) }

${displaystyle k={frac {10}{3}}\times log_{10}(N)}$

, formule dans laquelle k{displaystyle k}

est le nombre de classes et N{displaystyle N}

le nombre de données. Tous calculs faits, vous arrondirez le résultat (k{displaystyle k}

) à l'entier le plus proche. Cette règle est applicable aux distributions symétriques et dont les valeurs ne sont pas trop étalées.
La loi de Rice propose un découpage de la plage de valeurs en classes selon la formule suivante: k=2N{displaystyle k=2{sqrt {N}}}

, N{displaystyle N}

étant le nombre de données. À titre d'exemple, pour un échantillon de 200 données, calculez la racine carrée de 200 (environ 14, 14), puis multipliez par 2, soit 28, 28, arrondi à 30. Cette formule convient mieux aux distributions non homogènes ou avec des valeurs aberrantes.

Step 5. Determine your classes

You now know how many classes you need, it is now necessary to determine the limits (upper and lower) of the different classes. All the classes follow each other: if one of them ends with 19, the next one starts at 20. You must include the two extreme values in your breakdown.

Say you have collected about 100 results from a 100-point test. You want to analyze this test, so you decide to do classes with an amplitude of 10 (the first from 0 to 9, the next from 10 to 19, etc.).
It is convenient, but not necessarily legitimate, to make classes with round amplitudes (10, 20, 50 or 100).
If you have one or two outliers (radically different from the other values), under certain conditions you can eliminate them, otherwise you integrate them: everything will depend on the analysis that is requested of you.

Step 6. Put your data in column A

Type each of your data, one under the other in a column, the TO for the sake of convenience.

You have a sample of 40 data items: you are going to type them in, one per cell, in cells ranging from A1 To A40.

Step 7. Display the upper limits of the classes

Type them under each other, for example, in column C from cell C1. Here is ! Everything is in place, you can move on to the actual creation of the histogram.

This step is not necessary with a Windows PC

Part 2 of 3: Create a histogram in Windows

Step 1. Select all of your data

Click on the first cell of the column TO, then while holding down the Shift key, click the last cell that contains data.

Step 2. Click the Insert tab

It's in the green ribbon at the top of the Excel window. Thus, the different groups of the insertion menu appear below.

Step 3. Click Recommended Graphics

You will find this button on the left in the group Graphics (3^e from the left) of the toolbar Insert. A configuration window is then displayed.

Step 4. Click the All Graphics tab

It's on the right at the top of the window.

Step 5. Click Histogram

This tab is in the third position in the left column.

Step 6. Choose a histogram template

Click on the icon furthest to the right (simple histogram, without added curve), then on OK. Thanks to its computing power, you have a histogram of the data you have selected.

Step 7. Open the context menu for the horizontal axis

Right click on the horizontal axis, and in the context menu that appears, click on Shaping the axis, then in the pane Axis format, click on the top Axis options.

Step 8. Check the Interval between marks check box

It is in the middle of the menu, but the mention is now in the section Graduations.

Step 9. Enter the interval between marks

To the right of the mention Interval between marks, type the width of the columns in the field, then validate with Enter. Excel will then take this into account, this space can be modified.

Say you go for classes with an amplitude of 10, then you would type 10

Step 10. Put titles on the chart and the axes

Often, these two pieces of information are necessary in order to be able to analyze the graph later.

For the axis titles: click on the graph, then in the ribbon at the top, click on Chart Tools, then on Disposition –> Axis title –> Title of the main horizontal axis. Right click on the default title, then click Edit text. The process is the same for the vertical title.
For the title of the chart: click on the chart, then in the ribbon at the top, click on Chart Tools, then on Disposition –> Chart Title. Choose one of the three possibilities, then right-click on the default title, then click on Edit text.

Step 11. Save your document

Press Control + S, give the document a name, assign a destination folder, and click Save.

Part 3 of 3: Create a histogram in macOS

Step 1. Select the data and class limits

Click on the first cell of the column TO, then while holding down the Shift key, click the last filled cell in the column VS: thus are selected and the data of the sample and the limits of the classes.

Step 2. Click Insert

It's in the green ribbon at the top of the Excel window.

Step 3. Click on the histograms icon

To do this, click on the tab Insertion, then in the group Graphics, click on the topmost and leftmost icon. A drop-down menu unfolds.

Step 4. Click on the desired histogram

You have the choice between 6 types of vertical histograms, choose the one you want by simply clicking on it: the graph appears on the screen.

Do not choose what is called a "Pareto histogram", the icon is certainly a histogram, but there is also a curve (orange)

Step 5. Check your histogram

Before definitively saving your graph, see if it is correct and if it allows a correct analysis of the population plotted. If not, edit the classes and update the chart.