Below is my problem and attached is the workaround I did by creating 3 separate graphs and trying to stack them on top of each other but as you can see, the formatting isnt perfectly aligned. I have spent hours on this and would really love any help you can provide!!
This is my data | Year | CMG | CSA (not AJ) | CSA (AJ) | I-IMG (not AJ) | I-IMG (AJ) | |------|-----|--------------|----------|----------------|-----------| | 2017 | 53% | 10% | 5% | 29% | 3% | | 2018 | 57% | 9% | 6% | 25% | 3% | | 2019 | 43% | 12% | 8% | 32% | 5% | | 2020 | 45% | 11% | 7% | 33% | 4% | | 2021 | 42% | 9% | 6% | 37% | 5% | | 2022 | 43% | 8% | 7% | 37% | 4% | I want to have the Y axis be %, the X axis be year and for each year on the X axis I want 3 columns, the first column will be clustered and made up of CMG, the second column will be stacked and made up of CSA (not AJ) and CSA (AJ), the third column will be stacked and be made up of I-IMG (not AJ) and I-IMG (AJ)
I tried the following:
Certainly! Here's how you can create the desired chart in Excel based on your data:
Select Your Data:
Insert a Clustered Column Chart:
Select the entire data range. Go to the "Insert" tab in the Excel ribbon. Click on "Column Chart" in the Charts group. Choose "Clustered Column." Adjust the Chart Type for Stacking:
Click on the chart to select it. Go to the "Chart Design" tab in the ribbon. Click on "Select Data" to open the "Select Data Source" dialog box. Add Series for Stacking:
Click on the "Add" button. Enter a name for the series (e.g., "CSA"). For the series values, select the data in columns C and D (CSA (not AJ) and CSA (AJ)). Click "OK" to close the dialog. Add Another Series for Stacking:
Click on the "Add" button again. Enter a name for the series (e.g., "I-IMG"). For the series values, select the data in columns E and F (I-IMG (not AJ) and I-IMG (AJ)). Click "OK" to close the dialog. Format the Chart:
Right-click on the chart elements to format them. Customize the colors, legends, and other formatting options as needed. Adjust the Y-axis to show percentages. Now, you should have a chart with each year on the X-axis, and for each year, three columns: CMG (clustered), CSA (stacked), and I-IMG (stacked). Adjust the formatting to meet your preferences. If you encounter any issues or have further questions, feel free to ask!
But kept receiving this error: The reference is not valid. References for titles, values, sizes or data labels must ba single cell, row or column
I then followed the advice of this youtube video: https://www.youtube.com/watch?v=CpR8QvC6pAA&ab_channel=KarinaAdcock
But as you can see in the attached, it really isnt very polished.