Excel – Creating a “grouped” bar chart from a table in Excel

chartsmicrosoft excelmicrosoft-excel-2016

Firstly, I have almost no experience with Excel charts. 🙂

I'm trying to create a fairly simple chart from a fairly simple table. But I'm having more problems than I thought I would have and spending too much time on it.

So, this is the table:
enter image description here

And this is a sketch of what I want to have:
enter image description here

So… X-axis has labels from the top row, Y-axis has values from row 4 and bars are grouped according to labels in row 3. Also, bars are colored and there's a legend on the side linking a color to a specific sub-label.

How do I do it? 🙂

And a bonus question… Let's say I want to add a new "week" every now and then to the right of this table (expand it with more data). Can I do it so that the chart automatically adjusts and includes the new data? Or will I have to manually edit it each time?

If all of this has already been answered, sorry, but I have no idea what to search for.

Best Answer

Excel charts work by plotting rows and columns of data, not just a big long row. So arrange your data like this:

Data layout optimized for plotting

Select this range of data, and on the Insert ribbon tab, click Table. It won't insert anything, but it will convert your ordinary range of data into a special data structure known as a Table. Nothing to be scared of, Tables are pretty powerful. The dialog will ask if your range has headers, which it does (Week, A, B, C, Total).

The Table now has special formatting, with a colorful header row and alternating bands of color. It's a little overformatted, but you can select it and choose a less (or more!) formatted style.

Now select the table, or a cell within the table, and insert a column chart.

Table and Chart

If you don't want the total (it might overwhelm the rest of the data, simply select and delete the total columns in the chart, or select only the first four columns of the table before selecting the chart.

Now comes the magic of Tables. If you have a formula somewhere that relies on the whole column in a table, then if you add or remove rows in the table, the formula will update without any effort on your part. These formulas include the Series formulas in the chart. So add a row to the table, and the chart will automatically include the new row of data.  

Expanded Table and Chart