?
Solved

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

Posted on 2010-08-17
5
Medium Priority
?
1,175 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
  • 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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

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.
Usually, rounding is performed by some power of 10 - to thousands, hundreds, tens, or integer - or to one, two, or more decimals. But rounding can also be done to a power of two, say, 16 or 64, or 1/32 or 1/1024, even for extreme values.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

590 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