?
Solved

Excel 2003: Change colour of all data points in a scatter plot

Posted on 2010-08-17
5
Medium Priority
?
1,139 Views
Last Modified: 2012-05-10
I have a scatter plot based on data series. I would like to change the colour of one data point in each of the data series and at present, in order to do this i must click each point manually to edit its colour. Is there a way of editing a group of data points all at the same time. Perhaps VBA code is required? I have attached an example. What i would like to do is make all the datapoints for Lab A (see attached file) salmon-coloured. Is there a quick way of doing this rather than going through one by one changing their colour?
test-colour-data-point.xls
0
Comment
Question by:LucasHamlyn
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 50
ID: 33461400
Hello,

Your chart is a line chart. You can achieve what you want by adding new data series and add it to the chart. Change its chart type to XY chart and set the X values to the range in column A, the Y values to the range in column C. Then format the markers.

See attached.

cheers, teylyn
Copy-of-test-colour-data-point.xls
0
 

Author Comment

by:LucasHamlyn
ID: 33461559
Teylyn: awesome but once i have added a new data series, how do i change it's chart type to XY chart without changing all the other data series at the same time?
0
 

Author Comment

by:LucasHamlyn
ID: 33461795
Its the right solution I just can't replicate what you have done thats all.
0
 
LVL 50

Accepted Solution

by:
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 2000 total points
ID: 33462323
Here's what I've done:

- select cells C6:C25
- copy the cells
- click the chart
- click Edit - Paste Special
- tick Add cells as "new series", Values (Y) in "columns", all other selections unticked - OK
- You'll now see a new line in your line chart and the chart looks screwed up towards the right
- select the new line, right-click and select "Chart Type"
- Select XY Scatter (without lines)
- Click Chart menu - Source Data - Series tab - scroll down to the last series (should be Series 26)
- Select the last Series and edit the X and Y values.
- click in the X values field, delete what's in there, then select A6:A25 in your data table (the cells with all 1 values)
- click in the Y values field and confirm that the Y values are C6:C25
- now the new series will overlay your current data and will have different data point markers
- select the new series in the chart and format it
- - marker = custom
- - style = diamond shape
- - foreground = dark blue
- - background = salmon
- - size = 12 pts

That's all I've done.

For a chart like this, it would make much more sense to chart the data in a REAL XY chart and chart it by columns. You are using a line chart (just formatted the line to not appear), charting by row.

For what you want to highlight, a real XY chart by column may be more suitable, especially if you want to emphasize a specific data series ("Label A").  

You can move the "all 1" values from column A to any other area of the workbook, even to a different sheet, if you want. If you want to chart your data in a true XY chart, you need a column of position numbers for the X position for each "Label" or column of data, so, one column of 1's, one column with all 2, one column with all 3, etc. These are the X values for an XY chart, making sure that the horizontal spread of your data points is lined up. The Y values can be in a different table (Your main data source).

This way, you'll probably have less data series (one per column, instead of one per row). Then, the only thing you'll need to sort out is how to create the correct "Label A" text to appear on the X axis instead of the numbers 1, 2, 3, etc. But that's a different topic (the solution is easy).

cheers, teylyn
0
 

Author Closing Comment

by:LucasHamlyn
ID: 33470342
I like the food for thought at the end of your answer.
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question