Excel Chart - Customize X-Axis Data Labels

[stanav]

Hi all,
I have this Bubble Chart in Excel using the data from columns B (Y-values), C (X-values) and D (bubble size) in a single series. The different bubble colors represent the different people and not the series.
Everything looks OK the way I want it to except that I can't seem to format the X-axis labels to show the quarter values (data in column D) instead of the date in "m-yyyy" as it is now (which is the closest I could get as the quarters )
I hope someone can help me with some ideas to achieve my goal.
Thanks for reading!
Stanav.

[charlesfriedo]

Hi,

This should update your chart to show quarters on the X-axis instead of the date format.

Assuming your data looks like this:
```
B (Y-values) | C (Dates) | D (Bubble Size) | E (Quarters)
----------------------------------------------------------
10 | 01/01/2024 | 5 | Q1-2024
20 | 04/01/2024 | 8 | Q2-2024

1. Add a New Column for Quarters:
Create a new column (let's say column E) where you calculate the quarter from your dates in column C.
Use a formula like `=ROUNDUP(MONTH(C2)/3,0)` to get the quarter number. Adjust the formula to match your specific needs (e.g., include the year for unique quarters like "Q1-2024").

In E2, enter: `=CONCATENATE("Q", ROUNDUP(MONTH(C2)/3, 0), "-", YEAR(C2))`

2. Modify the Bubble Chart:
Right-click on your chart and select "Select Data".
Edit the X-values to use the new quarter values from column E instead of the original dates. (e.g., `Sheet1!$E$2:$E$5`).

3. Format the X-axis:
Right-click on the X-axis, select "Format Axis".
Change the axis labels to use the new quarter format if needed. Ensure the axis is displaying the values as text, not dates.

If this still not solved, You can try using ready-made bubble chart from any plug-in such as Chartio, ChartExpo or Google Sheets Charts where you can edit axis values from front end.

Best,
Charles