Solved

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

Posted on 2010-08-17
5
1,077 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

Expert Comment

by:teylyn
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:
teylyn earned 500 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

746 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now