Share via

Help with a Scatter Plot - Centering x axis & changing data markers colors

Anonymous
2024-10-10T13:36:35+00:00

Hello, I am working on a scatter plot for a project timeline in excel and so far I have created this:

I am looking to make a few more adjustments to the chart but need help. The following adjustments are:

  1. Centering the labels on the x axis (dates). I want the dates to be centered between the gridlines.
  2. Is it possible to change the colors of the data markers based on conditions or formulas. Changes = status and status will change, is there a way to do automatic changes (i.e. linked to something) rather than manually changing the data markers every time status changes.

Below is the data below used for the chart... Ref # (Y Axis) is white-d out on the side as I do not want to see it.

Data Label (Data Label on the Data Marker) Ref # (Y Axis) Target Date (X Axis - at the top) Status (Color of Data Marker)
A1 1 1/10/2025
A2 1.1 1/10/2025
A3 1.2 1/24/2025
A4 1.3 11/22/2024 Upcoming (on track)
A5 1.4 11/29/2024 Upcoming (off track)
A6 1.5 3/28/2025
A7 1.6 3/7/2025
A8 1.7 1/10/2025
A9 1.8 1/31/2025
A10 1.9 2/14/2025
A11 2 12/20/2024
A12 2.1 11/8/2024 Upcoming (on track)
A13 2.2 11/22/2024 Upcoming (on track)
A14 2.3 12/13/2024
A15 2.4 12/20/2024
R-R1 3 1/31/2025
R-R2 3.1 10/4/2024 Upcoming (off track)
R-R3 3.2 10/4/2024 Upcoming (off track)
R-R4 3.3 11/1/2024
R-R5 3.4 11/15/2024
R-R6 3.5 10/11/2024 Achieved
R-R7 3.6 9/23/2024 Achieved
Microsoft 365 and Office | Excel | For business | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

5 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. HansV 462.6K Reputation points
    2024-10-10T16:23:04+00:00

    In the workbook at Chart.xlsm, the status is entered on the sheet itself for simplicity.

    The colors will be updated automatically when Sheet1 is activated.

    You will have to allow macros when you open the workbook.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2024-10-10T16:05:54+00:00

    There are multiple tabs within the excel file so on this particular tab- the status are linked to a different tab using xlookup. So, on the tab in which the chart is on- yes they will automatically update. However, within the file (the starting point) is manual changes.

    Was this answer helpful?

    0 comments
  3. HansV 462.6K Reputation points
    2024-10-10T15:44:25+00:00

    Thanks. Will the status be edited manually, or is it the result of a formula, or something else?

    Was this answer helpful?

    0 comments
  4. Anonymous
    2024-10-10T15:03:09+00:00

    Hi, that is an error on my part, the legend of the status is:

    Achieved = Green

    Upcoming (off track) = Brown

    Upcoming (on track) = yellow

    Milestone = gray

    Red = delayed

    Was this answer helpful?

    0 comments
  5. HansV 462.6K Reputation points
    2024-10-10T14:53:22+00:00

    Your screenshot shows different colors for data points with the same status. For example A4, A12 and A13 are all 'Upcoming (on track)" but A4 is brown while A12 and A13 are yellow.

    On the other hand, A4 and A5 have different statuses but they appear to have the same color.

    Can you explain?

    Was this answer helpful?

    0 comments