1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Creating a chart with different data point formats

Discussion in 'Discuss Data Visualizations and Charting' started by SDV, May 17, 2017.

  1. SDV

    SDV New Member

    Messages:
    16
    Hi everyone!

    You have given me incredible solutions to my previous question, and I hope you can once again help me out here!

    1. I want to create a chart that looks like the attached PDF file. I used scatter charts, and added labels as text boxes for the x-axis. So far, so good.

    2. I have differentiated the data points (colour, shape, outline) based on performance, functional and leadership ratings, and added + or - to show change from the previous year.

    3. I changed the data points one by one to get them the way I wanted, but now I have to do it for hundreds of data points.


    Q1. Is there any way to set automatic (or dynamic) chart settings where the data points will get formatted depending on the data used?
    Q2. Alternatively, is it possible to use formulas to change data points?


    Thank you!

    SDV

    Attached Files:

  2. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,318
    It can be done
    You will need 192 Helper columns for each series
    that is 3 x 4 x 4 x 2 x 2

    Set them up and populate them as appropriate

    I will try and get to this in the next day or so, unless somebody else jumps in!

    Yes it will handle hundreds of data points per each series
  3. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,318
    Here is a start
    upload_2017-5-17_23-13-19.png
    So you will have to manually format 191 more series
    Then add your data
    then enjoy

    See attached file for the layout

    Attached Files:

    Thomas Kuriakose likes this.
  4. SDV

    SDV New Member

    Messages:
    16
    Hi Hui,

    Do you mean I have to manually format 192 data points? When I add data to any of the cells, many more data points are added to the chart - I don't need 192 data points on the chart, just 1 point to represent 1 value.

    I found a VBA that comes close to what I'm looking for:
    http://www.clear-lines.com/blog/post/Excel-ScatterPlot-with-labels-colors-and-markers.aspx

    I am trying to edit that to make it run on my data, with more formatting options.

    Thanks
    SDV
  5. Hui

    Hui Excel Ninja Staff Member

    Messages:
    10,318
    You have 192 combinations of data
    3 x 4 x 4 x 2 x 2 = 192

    So you need a series for each combination
    You have to format each series once
    Each series can have as many points as you want

    You could write a macro to do it for you if you want

Share This Page