Clustered column excel как построить

Обновлено: 02.07.2024

The clustered column chart is one of the most commonly used chart types in Excel.

In this chart, the column bars related to different series are located near one other, but they are not stacked.

On the other hand, stacked charts are used when we need to show the ratio between a total and its parts.

It shows the different series as a part of the same single column bar, where the entire bar is used as a total.

A clustered column chart vs a stacked column chart in Excel .

The difficulty may appear when we need to combine these two chart types, as Excel doesn’t give us any default, built-in tools for that.

There are several different methods to tackle this, so let’s discuss the relatively easy (and less confusing) method: where there are several secondary chart axes using the same dynamic Min and Max values.

Let’s walk through the entire process from the beginning…

PREPARING THE SOURCE DATA

Inserting the simple Clustered Column Chart

The source data representing Quarterly Revenue by Division, including Revenue from new apps per Division

The sample data for this example was previously prepared to demonstrate the following idea:

For the purpose of the data visualization, we need to show this data as several clustered columns (one column for each Division) for several Quarters (i.e. three columns for every Quarter), which are stacked and show the Revenue Share of the new apps within a single Division.

Pro Tip: This technique is based on a little trick for how to use both primary and secondary Y-axes and let them have the same Min and Max bounds in a dynamic way, without needing to change them manually.

The aim is to build a Combined Clustered Stacked Column chart like this one.

NOTE: Keep in mind that we need to arrange the source data in the following way:

Grouping the data in this way is required for the next stage of combining the charts.

Step 1

Let’s insert a Clustered Column Chart.

To do that we need to select the entire source Range (range A4:E10 in the example), including the Headings.

After that, Go To:

INSERT tab on the ribbon > section Charts > Insert a Clustered Column Chart

Select the entire source Range and Insert a new Clustered Column chart

Pro Tip: Since a Clustered Column chart is a default Excel chart type (at least until you set another chart type as a default type), you can select a source data range and press ALT + F1 keys on your keyboard. This combination allows you to insert a default chart object by pressing the hotkeys only.

Step 2

If we take a look at the screenshot above, there is one thing that we may notice from the beginning: the Quarters are set as chart Series (that’s why they are shown inside the chart Legend) instead of the axis X as we need.

Within this new dialogue window, we need to click on Switch Row/Column in the middle of the window.

We need to change the source data for the chart Series to use Quarters as X-axis values

Pro Tip: Once you right-click on the Chart Area you can confirm the command S e lect Data by pressing “E” on your keyboard (take a look at every single option in the context menu – as you may notice, each character is underlined in a command keyword – these are so-called “lazy shortcuts” that are widely used by Excel experts to speed-up the whole range of repetitive actions during a daily work).

In our case, pressing the “E” key confirms S e lect Data … command; pressing the “F” key would confirm F ont… command and so on.

In addition, to the mentioned shortcuts, you can find some commands on the Excel Ribbon under DESIGN and FORMAT contextual tabs that appear once we selected a Chart Area.

Step 3

However, our aim is to stack every “new apps” column with the appropriate “total” column, i.e. we need to create a Stacked Column chart.

The first step to do that is to Select a Chart Area, right-click on it and Select Change Chart Type command in the context menu.

Once the Change Chart Type dialogue window appears, we need to select Combo chart type and for each series that has “- new apps” keyword in the name, we apply the Clustered Column chart type and check ON the secondary axis.

Creating the initial combined chart

Pro Tip: Once you have right-clicked on the Chart Area you can confirm the command Select Data by pressing “Y” key on your keyboard (this is another “lazy shortcut”)

If we look closer, we may notice that the primary axis Y (on the left side of the Chart Area) and the secondary axis Y (on the right side of the Chart Area) are different, i.e. they use different numeric values for the Min and Max bounds.

We can change them manually if we make double-click on the Axis Y area (the appropriate Format Axis panel appears on the right side of the screen).

However, this is not an optimal approach because the source data may change next Quarter (or even Month), i.e. the Min and Max bounds may have values other than the current 2500 and 1200.

The primary and secondary Y axes are not equal right now.

Step 4

Step 5

How do we push this Max value to be used as a Max value for the secondary Y Axis?

Well, we use another small trick in Excel.

The secret is in the fact that we need to add this value as a new Series in the chart and check ON the secondary axis for this Series later.

So, let’s Select the Chart Area, right-click on it, click on the Select Data command and then click on Add on the left side of the dialogue window that appears.

Adding a new Series with one data point to the combined chart

In the next dialogue window, let’s set cell F4 as a name for the Series, and cell F5 as a data range for the new Series.

Adding a new Series with one data point to the combined chart

Step 6

Once we click OK in the Select Data Source dialogue window during the previous step, Excel inserts the additional series (and the additional column for this series).

That’s why our other columns have been shifted too.

To fix that, we need to convert this new Series to some other chart type (such as a Linear graph).

Let’s select the Chart Area, right-click on it and select the Change Chart Type command again.

In the dialogue window that appears, we need to find a new Series in the list and apply another chart type (e.g. Linear chart) to it.

In addition, make sure to check ON the appropriate “Secondary Axis” checkbox for this Series.

C onverting the new Series into another chart type (e.g. Linear chart)

In the case, if the secondary values inside a source data range are bigger than the primary values, we need to perform this trick twice – once for the secondary axis and once for the primary axis.

In other words, we are creating two new series, which are both based on the same Max value.

While we convert them to another type chart, we check ON “Secondary Axis” checkbox for one series, and check OFF the same checkbox for another series.

It will be related to the primary axis in this case.

Step 7

Let’s make some visual improvements to the chart.

First, we can move the Legend to the top.

To do that, Select the Legend Area , make double-click it and select Top legend position in the Format Legend panel that appears in the right side of the screen.

Also, we can delete the extra entry in the Legend (“Inv. Value” element in our example) as no one needs to see it.

Just select that value(s) and press the DELETE key on your keyboard.

Moving the Legend to the Top and deleting all extra unnecessary entries

Also, we recommend deleting all the entries for the secondary elements (“new apps” elements in the example) as have less Legend Entries improves readability and clearness.

Some visual improvements

Step 8

Let’s select every single primary column one by one, right-click on it and select Add Data Labels > Add Data Labels in the context menu.

Repeat for all secondary columns.

Adding the data labels for both primary and secondary columns

Pro tip: Feel free to play a little with the Series Overlap and Gap Width properties for the primary and secondary columns to enlarge/reduce the clearance between the two nearest columns and to make all values be located inside the columns.

Make sure that you apply the same changes for both primary and secondary columns to prevent the column shifting for one category only.

In our example, we used the following parameters – Gap Width: 70% and Series Overlap: -10% for both categories.

In addition, let’s delete both primary and secondary Y axes from the Chart Area.

We can delete the gridlines too.

Finally, let’s move the position of the data labels to “Inside Base” for all the secondary columns to improve the overall readability.

To do that, select the data values for all three secondary columns one by one (i.e. we need to repeat this action three times), double-click on any data label, go to Label Options on the Format Data Labels panel that appears, and set the position as Inside Base.

Also, we can embolden the primary Data labels and the X Axis titles.

Just select the appropriate element and click on the Bold button on the HOME tab of the Ribbon (or press the CTRL + B hotkey on the keyboard).

Moving the data labels for the secondary columns to the Inside Base position

Step 9

Let’s add the chart title.

Just select the Chart Title area and type the title of your chart.

Editing the Chart Title

Pro tip: We can make the Chart Title dynamic and linked to a specific cell.

In this case, every time we want to change or edit the chart title, the only thing we need to do is to update the related cell value.

To do that, select the Chart Title area and click inside the formula bar (the section where we usually enter a formula in the cell).

Type the following formula:

= cellAddress

where “cellAddress” is the name of the cell with the chart title value (this is cell A3 in our example).

Just type the equal sign (“=”) and click on the cell with the chart title value.

Don’t be confused by the screenshot above – typically a cell address may include a Worksheet’s name where this cell is located.

So, the full cell address frequently looks like: ‘worksheetName!cellAddress

In addition, let’s add the additional text box inside the Chart Area that will show a text definition for the secondary Data Labels.

To do that, let’s select the Chart Area, then Go To:

INSERT tab on the Excel Ribbon > Text Section > Text Box

(alternatively: INSERT tab on the Excel Ribbon > Illustrations Section > Text Box)

I nserting the TextBox with the additional note

Once the TextBox has been inserted, let’s type “New Apps” and format it.

They are locked together, and the TextBox will be moved or edited in parallel with the chart.

Otherwise, both objects would be existing separately from each other and if we were to move or edit the Chart object, the TextBox object would be kept untouched.

Диаграмма - это наиболее важный инструмент в Excel для отображения отчетов и панелей мониторинга. Преимуществом диаграммы может быть обобщение всей деятельности и / или активности на фигуре.

Это графический объект, используемый для представления данных в вашей электронной таблице Excel, в Excel есть много типов диаграмм. Но кластерный график часто использует в Excel.

Кластерная столбчатая диаграмма является частью диаграммы в Excel, она помогает нам отображать более одного ряда данных в кластеризованном вертикальном столбце, кластеризованная столбцовая диаграмма очень похожа на гистограмму, за исключением того, что кластеризованная столбчатая диаграмма позволяет группировать столбцы рядом друг с другом. сравнение сторон,

Вертикальные столбцы отображаются в группе по категориям, мы также можем сравнить несколько рядов с помощью столбчатой диаграммы кластера, каждый ряд данных отображается на одинаковых метках оси.

Шаги для создания кластеризованной столбчатой диаграммы в Excel

Для этого нам нужно выбрать весь исходный диапазон, включая заголовки.

После этого перейдите к:

Вкладка «Вставка» на ленте> Диаграммы сечений>> щелкните «Больше столбчатой диаграммы»> «Вставить кластерную диаграмму»

Кроме того, мы можем использовать короткую клавишу, во-первых, нам нужно выбрать все данные и затем нажать короткую клавишу (Alt + F1), чтобы создать диаграмму на том же листе, или нажать только F11, чтобы создать диаграмму в отдельном новый лист.

Когда мы нажимаем на столбец диаграммы больше, мы получаем новое окно, как указано ниже. Мы можем выбрать формат, а затем нажать кнопку ОК.

Как сделать кластерную диаграмму столбца в Excel?

Clustered Column Chart в Excel очень проста и удобна в использовании. Давайте разберемся с работой Clustered Column Chart с некоторыми примерами.

Вы можете скачать этот шаблон Excel с кластерными столбцами здесь - Шаблон Excel с кластерными столбцами

Кластерная диаграмма столбцов в Excel, пример № 1

Существует обобщение данных, это обобщение отчета о производительности компании, предположим, что некоторые сотрудники отдела продаж находятся в другой зоне расположения, и у них есть цель для продажи продукта. Все поданные как цель, количество заказов, Цель, Стоимость заказа, Достигнутый%, Полученный платеж, Скидка% приведены в сводке, Теперь мы можем увидеть их в таблице, как указано ниже. Поэтому мы хотим отобразить отчет с помощью столбчатой диаграммы кластера.

Прежде всего, выберите диапазон. Нажмите «Вставить ленту»> «Нажмите на столбчатую диаграмму»> «Больше столбчатой диаграммы».

Выберите кластеризованную столбчатую диаграмму> Нажмите Ok.

Также мы можем использовать сочетание клавиш (alt + F11).

Эта визуализация по умолчанию используется Excel, если мы хотим что-то изменить, так что Excel позволяет изменять данные и все, что мы хотим.

Если мы видим с правой стороны, что есть знак опции «+», то с помощью знака «+» мы можем отобразить различные элементы диаграммы, которые нам нужны.

Когда мы используем график и выбираем, тогда отображаются еще две вкладки. «Дизайн» и «Формат» как лента,

Мы можем отформатировать диаграмму и цветовую разметку диаграммы с помощью вкладки «Дизайн ленты».

Обе ленты очень полезны для диаграммы. Например: если мы быстро изменим макет, просто перейдите на вкладку «Дизайн», нажмите «Быстрый макет» и измените макет диаграммы.

Если мы хотим изменить цвет, то мы можем использовать ярлык для изменения стиля и цвета чата, просто нажмите на диаграмму, и есть выделенная опция, как показано ниже упоминания.

Нажмите на стиль, который вы хотите.

Диаграмма также дает возможность отфильтровать столбец, который нам не нужен, аналогично просто нажмите на диаграмму, с правой стороны, мы можем увидеть знак фильтра, как показано ниже упомянутых изображений.

Теперь мы можем нажать на столбец, который нам нужно показать на графике.

Опять же, мы также можем изменить диапазон данных. Щелкните правой кнопкой мыши на графике и выберите опцию данных, затем мы также можем изменить данные.

Мы можем изменить ось, по горизонтали или вертикали, просто щелкнув по строке / столбцу переключателя. Мы также можем добавить или удалить и редактировать файл.

Мы также можем отформатировать область диаграммы, просто щелкнув правой кнопкой мыши на диаграмме и выбрав опцию формата области диаграммы.

Затем мы получаем новое окно с правой стороны, как показано ниже.

Просто нажмите на опцию диаграммы, тогда мы сможем использовать все опции форматирования диаграммы, такие как название диаграммы, ось, легенда, область графика, серия и по вертикали.

Затем нажмите на опцию, которую мы хотим отформатировать.

На следующей вкладке мы также форматируем текст.

Нам нужно знать о некоторых более важных вещах, таких как добавление меток данных, отображение линии тренда в чате.

Если мы хотим отобразить метки данных в процентах, просто щелкните правой кнопкой мыши столбец и выберите «Добавить метки данных».

Метка данных добавляется на график.

Также мы можем показать метки данных с описанием. С помощью данных выносок. Щелкните правой кнопкой мыши на метке столбца данных столбца, нажмите на кнопку добавления меток данных и выберите пункт Добавить выноски данных. Картина показывает ниже упомянутый.

Данные выноски добавляются на график.

Иногда нам нужен анализ трендов также на графике. Поэтому щелкните правой кнопкой мыши на метках данных и добавьте линию тренда.

Линия тренда добавлена на график.

Мы также можем отформатировать линию тренда.

Если мы хотим отформатировать линии сетки, щелкните правой кнопкой мыши линии сетки и выберите формат линий сетки.

Щелкните правой кнопкой мыши на Gridlines и выберите Add Miner Gridlines.

Сетка майнера добавлена к нему.

Щелкните правой кнопкой мыши линии сетки и выберите Ось формата.

Наконец, кластерная диаграмма столбцов выглядит так, как показано ниже.

Плюсы кластерной столбчатой диаграммы в Excel

Поскольку диаграмма Clustered Column является типом диаграммы Excel по умолчанию, по крайней мере, пока вы не установите другой тип диаграммы в качестве типа по умолчанию
Это может понять любой человек и будет выглядеть презентабельно, кто не намного лучше знаком с графиком.
Это помогает нам обобщать огромные данные в визуализации, поэтому мы можем легко понять отчет.

Недостатки кластеризованной столбчатой диаграммы в Excel

Это визуально сложно, когда мы добавляем больше категорий или серий.
В один раз сложнее сравнивать отдельные серии по категориям.

Вещи, которые нужно помнить

Все столбцы и метки должны быть заполнены различными цветами, чтобы легко выделить данные на нашем графике.
Используйте понятные названия диаграмм, названия осей. Таким образом, по названию можно понять отчет.
Если данные относятся к этой диаграмме, используйте эту диаграмму, в противном случае данные относятся к разным категориям, мы можем выбрать другой тип диаграммы, который будет представлен.
Нет необходимости использовать дополнительное форматирование, чтобы каждый мог ясно видеть и анализировать все точки.

Пользовательская комбинированная диаграмма

Вы можете создать комбинированную диаграмму, настроенную вами.

Шаг 2. На вкладке ВСТАВКА в группе Диаграммы щелкните значок комбинированной диаграммы на ленте.

What is the Clustered Column Chart?

The clustered column chart is a column chart that visualizes the magnitude of data using vertical bars. It is majorly used to show multiple variables of data in one chart. It is easy to compare two variables using the Clustered Column Chart.

How a Clustered Column Chart Different From Column Chart?
Although the clustered column chart is a column chart, it is slightly different from a simple column chart. A column chart is used to represent one variable against one other variable. For example, total sales in each month of the year. While Clustered Chart is used visualize multiple variables of data. For example, total sales of two or more cities in each month of the year.

When to Use Clustered Chart?
Use Clustered Column Chart when you need to compare multiple variables. Try to keep only a few variables. Too many variables will make clustered chart too dense and it will not be easy to make sense out of it.
How To Create a Clustered Chart in Excel?

It’s easy to create a clustered chart in excel. First prepare your data. Here I have prepared sales data of two cities by month.

Now I want to compare both cities sales in each month. And the best way to compare data is using clustered chart column chart or bar chart. Follow these step to plot clustered chart in excel.

1. Select relevant data. Here I have selected month, Delhi, and Mumbai column. Excluded total because I don’t want it to be in my chart.
2. Go to Insert tab? Charts? Column Or Bar Chart? Clustered Column Chart.
The two cities are shown in two different colours. But still, we can’t clearly see the month grouping. It looks to crowded. Let’s make some space among the months so it easy to differentiate the months. We need to group the columns by month.

How to Insert space between groups of columns?
To make space between groups of columns in chart, follow these steps.
1. 1. Right click on chart and click on select data.
  1. 1. Select Data Source dialog will open. In Legend Entries, click on the Add button.
    1. 1. Edit Series Dialog will open. Leave the Series name blank. In Series values: type 0 in curly brackets. Hit the OK button.
      1. Now you can see that each month is grouped and separated.
      To make it even better, reduce the gap width and Series Overlap to 0%. To do so..
      1. Right Click on any column and click on Format Data Series. In Excel 2016, on the right side of screen, you’ll see formatting options. In 2010 and older versions, you’ll see a dialog box for same.
      2. Reduce the gap width and Series Overlap to 0%.
      Rename the chart title to “Sales”.
      Add Data Labels to Clustered Chart
      
      Well, it’s not required since you can visually see the difference but still, If you want to see the magnitude of each column then you should add data labels to the chart. To add data labels to the clustered chart, follow these steps:
      1. Click on the chart and go to design tab.
      2. In the left most corner, you can see Add Chart Element options. Click on it.
      3. Click on Data Labels. Select which over format you like. I choose outside end.
      Your clustered column chart is ready. To beautify this clustered chart, you can use formatting option or just use pre-designed chart style provided in Excel 2016. Choose the one you like.
      
      Finally, you are ready to rock the presentation with your self-explanatory clustered charts.
      Include Total Column Clustered Charge Creatively
      
      In the above chart, we don’t have a total column. Because it is foolish to compare variables to their sum. But you may want to compare total sales of different months. In that case, this kind of clustered chart will be a good option.
      
      Isn’t it beautiful and creative? We can compare each city's data in separate months and totals of months too in one single chart. To make this kind of chart click here.
      
      So yeah guys, this is how you plot a cluster column chart in excel. In the same way, you can plot a clustered bar chart. The only difference would be the orientations of the bar. Here the bars are vertical and called columns. In the bar chart, they are horizontal and called clustered bar chart.
      
      If you have any query regarding charts in excel or anything related to excel and VBA, feel free to ask me in the comments section below.
      
      Related Article:
      
      Popular Articles :
      
      50 Excel Shortcut to Increase Your Productivity : Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
      
      How to use the VLOOKUP Function in Excel : This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
      
      How to use the COUNTIF function in Excel : Count values with conditions using this amazing function. You don't need to filter your data to count specific values. Countif function is essential to prepare your dashboard.
      
      How to use the SUMIF Function in Excel : This is another dashboard essential function. This helps you sum up values on specific conditions.
      
      Читайте также: