Excel Column Chart issue

Question

Hi all,

I'm trying to create a chart on excel from some data we are provided with weekly.

I've attached an extract from a spreadsheet - this is the data I need to create the charts from.

I've also attached an example of a chart I have been able to make, but it's not quite right. I need it to be formatted like that, but separate the bars for peak into one colour, daytime into another, and off peak into another. I can do this manually but we are looking to do this manually for each region from column B7 to B22, so we need something that will be quick to produce.

The chart included is for Redruth, so I highlighted all 12 months, down to the percentage line for redruth. I found this graph under 'all charts' but once created I couldn't find a quick way to format the colours how we need them.

Does anyone have any ideas?

For reference, i have been able to do it by reformatting the data before creating the chart, but again this is time consuming as the data will always be provided this way.

Thank you.

Jo

Answer 1

Well, we can do that in Excel, but it's a bit complex because of your bad data layout.

Alright, I've created a sample file to reproduce the issue:

You can write a macro to color each column in the chart ... or use a Pivot chart.

The first step is to transform the data into a usable form, I used Power Query for that:

YFYI, here is the side how to do that in PQ:

Un-pivoting With Subcategories in Power QueryThe Excelguru Blog

Now we can create a Pivot table / Pivot chart from this data and use a slicer to filter the data from each item we want to show:

Here's the file:

https://www.dropbox.com/s/lqfmodu5cq8khte/f2444680-b00d-48c3-a199-baffe14aed9c.xlsx?dl=1

Andreas.

Answer 2

Dear Jo,

Greetings!

Thank you for posting in Microsoft Community. We are happy to help you.

Please correct me if I am wrong, you want to know how to change the individual Chart bar color to your preference. I can see in the screen shot you provided, that bars are on blue color and you want to change it.

1. Kindly click the chart you want to change.
2. In the upper right corner, next to the chart, click Chart Styles.
3. Click Color and pick the color scheme you want.

Article: Change the color or style of a chart in Office (microsoft.com)

Hope the suggestion can help you.

We look forward to your response. Thanks for your cooperation. Stay safe and healthy always.

Sincerely,

Zee | Microsoft Community Moderator

***Note: In the event that you're unable to reply to this thread, please ensure that your Email address is verified in the Community Website by clicking on Your Account Name > "My Profile" > "Edit Profile" > Add your Email Address > tick "Receive email notifications" checkbox > click on "Save".***