Creating Clustered Column Charts

This video shows how to make a clustered column chart in Excel relying on Excel's own ability to recommend displaying the data in that form. Despite the video's age, Excel's recommendation engine appears unchanged.

However, if you create a table containing the numbers from 1 to 10, Excel will suggest a line or area chart to display the data. In those circumstances, you will need to select the chart type manually. The fastest method is to press the keys ALT+F1 so Excel gives you a default chart, then to click Change Chart Type on the Chart Design tab if you're not happy with Excel's default selection. You can navigate to the Column charts on the All Charts tab to find the chart you want.

Excel may not always understand the chart you want to create. In the video, we have years as well as rent and staff. Years can look like just another number to Excel, so it may recommend years be displayed as a third colored column alongside rent and staff. In the event Excel is not giving the option of a chart with just rent and staff columns, it is simplest to create the clustered column chart without selecting the labels you want on the x-axis at all. Excel will start numbering 1, 2, 3, 4, 5 on the x-axis. When you want to add your year labels to the x-axis, you can click Select Data from the ribbon, then Edit under 'Horizontal (Category) Axis Labels' before selecting the range including the year labels.

If you can't get the chart you want having selected all the data you need, it is often easiest to create the chart you want from a subset before adding back in the other rows and columns of data you require.