Solved

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

Posted on 2010-08-17
5
1,086 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:Ingeborg Hawighorst
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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
location range 4 22
Hiding column macro 10 28
AutoFilter on a list of items.  Totalling each item in the list. 14 12
Excel - find text within text? 1 21
How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

914 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

21 Experts available now in Live!

Get 1:1 Help Now