0 votes
33 views
in Excel by (26.0k points)
How to create a histogram in Excel: step by step instructions

1 Answer

0 votes
by (148k points)
 
Best answer
How to build a histogram in Excel from table data Section "Diagrams" Stacked bar charts Data analysis package Conditional Formatting Additional distribution ranks How to draw a histogram How to combine a bar chart and a graph in excel Chart online Conclusion Video instruction copying moving Rotation We sign the object

How to build a histogram in Excel from table data

image Charts are a great tool to visualize data from different sources. Not many people know how to build a histogram in Excel from table data. In fact, there is nothing complicated here. Let's look at the various options.

Section "Diagrams"

So, let's get down to business.

  1. First you need to create a table. The values ​​can be arbitrary.

image

  1. The next step is to extract the data.

image

  1. Open the menu-tab "Insert" and click on the icon for working with the histogram.

image

You will be offered to build in various most popular ways:

  • regular histogram;
  • bulk;
  • ruled;
  • volumetric line.

You can hover over each of them and see examples. To view other options, you need to click on the corresponding item.

image

Please note that there are several types of construction in each category.

  1. When hovering over each item, in addition to the preview, brief information about the purpose will also be displayed so that the user can make the right choice.

image

  1. If you select "volumetric histogram with grouping", you can get the following result.

image

Stacked bar charts

This time we will have to add another column. Since the two columns will look exactly the same as in the case of grouping.

image

The third column must be in the form of numbers, not text, so that the program can add the data normally.

  1. Select the table, click on the "Histograms" button and select something with accumulation.

image

  1. As a result of this, you will see the following.

image

As you can see, in this histogram, the scale along the y-axis is displayed as a percentage. This method of construction is good because you can see comparative information.

  1. The statistics for each day will show how sales and costs relate to each other (in the case of an example). If you are not comfortable working with percentages, and want the data to be presented in absolute terms, then in this case you need to choose a different type of histogram.
Also Read:   How To Turn Off Compatibility Mode In Excel

image

Data analysis package

This feature is not available by default. In order to insert it on the panel, you must do the following steps.

  1. Click on the "File" menu item.

image

  1. Click on "Settings".

image

  1. Next, go to "Add-ons".

image

  1. Make sure Excel Add-ins is selected in Manage. After that, click on the "Go to ..." button.

image

  1. Check the box next to "Analysis Package" and click on the "OK" button.

image

  1. Go to the main panel on the "Data" tab. A new Data Analysis button will appear on the right side of the ribbon.

image

Now let's look at the process of creating a chart for this table. To do this, you must perform the following steps.

  1. Click on the button you just added. Select "Histogram" and click on "OK".

image

  1. After that, you will see the following window.

image

  1. In order to specify the "Input interval", simply select the table. The data will be filled in automatically.

image

  1. Now check the box next to "Graph output" and click on the "OK" button.

image

  1. As a result of this, you will get such a "Histogram" with the analysis of values.

image

In this case, the x and y axes are selected automatically.

Conditional Formatting

A beautiful analysis of the entered data can be done right inside the table.

  1. To do this, select it, go to the "Home" tab and click on "Conditional Formatting".

image

  1. Select "Histograms" from the menu that appears. After that, a large list of different options will appear. You can try to overlay any colors you like. To do this, just hover over one of the proposed templates.

image

  1. As a result, get a beautiful table in which the data is represented by a gradient fill.

image

In relative units (shading), it is much easier to analyze information and thereby determine which cell has the maximum or minimum value.

Additional distribution ranks

There are other types of statistical data processing. These include:

  • Pareto;
  • Frequency polygon;
  • Cumulates, etc.
See also:   Subtotals in Excel

Most of them can be created with ready-made templates. For example, to create a "Pareto Chart" you need to do the following.

  1. Select table.
  2. Go to the "Insert" tab.
  3. Click on the "Insert Statistical Chart" icon.
  4. Select the desired workpiece.

image

How to draw a histogram

As a rule, most users do not like the standard appearance of the created objects. It's very easy to change it.

  1. When you select a chart, a new Design tab appears in the menu.

image

Thanks to her, you can do anything. In addition, editing is possible through the context menu.

image

By right-clicking on an empty area of ​​the diagram, you can:

  • copy or cut;
  • change type;
  • select other data;
  • move it;
  • rotate the volumetric figure.

Let's consider some options.

copying

By clicking on the corresponding menu item, the entire contents of the histogram will be in the clipboard. After that, you can paste it into Word. It is worth noting that you can do the same using the keyboard shortcut [knopka]Ctrl[/knopka]+[knopka]C[/knopka]. To paste, use the combination [knopka]Ctrl[/knopka]+[knopka]V[/knopka].

image

Looks very nice.

moving

To get started, click on "Move Chart" in the context menu.

image

After that, a window will appear in which you can specify the purpose of the selected object.

image

If you choose the first option, it will be moved to a new sheet.

Rotation

For these manipulations, you need to select the following item.

image

As a result, an additional panel will appear on the right side of the screen, in which you can “play around” with two axes.

image

In this way, you can give even more volumetric effect.

image

We sign the object

If you click on the name, a panel for working with text will be displayed on the right. Moreover, it will be possible to edit.

image

Optionally, you can add effects such as:

  • shadow;

image

  • glow;

image

  • smoothing;

image

  • volume.

image

You can combine all of these individual attributes. But do not overdo it, otherwise it will turn out to be a nightmare.

How to combine a bar chart and a graph in excel

To combine different types of objects, it is necessary to use non-standard sets of diagrams.

  1. For this example, let's create another table with more data.
See also:   Error in Excel when sending a command to the application: we solve the problem

image

  1. Now select all the rows, go to the "Insert" tab, click on the "Histograms" icon and select the last option.

image

  1. In the window that appears, go to "Combined".

image

  1. You will then be able to specify the chart type for each series.

image

  1. It is necessary to specify "Histogram with grouping" everywhere, and for the "Total" series - the type "Line". In this case, you need to put a tick in the column "Auxiliary axis".

image

  1. After clicking on the "OK" button, we will get a new chart that combines a histogram and a graph.

image

Please note that an additional scale has appeared on the right along the Y axis, intended only for the line, that is, for the “Total” row. The left scale is for everything else.

Chart online

For those who cannot build a histogram correctly, online services come to the rescue. For example, the site OnlineCharts.

image

By clicking on the "Create Your Diagram" button, you will see a huge number of different settings, thanks to which you can draw whatever you want.

image

The result is easy to download to your computer.

Conclusion

In this article, we took a step-by-step look at how to create different types of charts using various tools. Don't be afraid to experiment. You can always delete your result.

It should be noted that there are different versions of Excel. For example, Excel 2010, 2013 and 2022 are very similar in this regard. The products of 2003 and 2007 are not so relevant nowadays and the difference between them is colossal.

Video instruction

For those who still have some questions, it is recommended to watch the video, in which everything is explained in detail with additional comments.

...