Results 1 to 2 of 2

Thread: Excel Chart - Customize X-Axis Data Labels

  1. #1

    Thread Starter
    PowerPoster stanav's Avatar
    Join Date
    Jul 2006
    Location
    Providence, RI - USA
    Posts
    9,290

    Excel Chart - Customize X-Axis Data Labels

    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.

    Name:  bubble-chart-data.png
Views: 120
Size:  20.3 KB

    Name:  bubble-chart-graph.jpg
Views: 120
Size:  11.0 KB
    Let us have faith that right makes might, and in that faith, let us, to the end, dare to do our duty as we understand it.
    - Abraham Lincoln -

  2. #2
    Registered User
    Join Date
    Jun 2024
    Posts
    1

    Re: Excel Chart - Customize X-Axis Data Labels

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width