how to highlight a cell so I know which point on the chart it is?

[Guest]

Hi, I am curious to see if anybody could create a visual relationship between
a point on the chart and the data cell. Let's say if we have 1000 points on
the chart, when I point to one of them--it will show the values--but I still
have no idea where the point actually comes from. Or on the other hand, when
I try to hightlight some cells using formats, the best thing is to see the
points on the chart also being highlighted in some way.

Thanks for any hints there.

 

[Guest]

Windson:
I am not sure whether I can help you here. If you select a data point and
you move it Excel pops up a Goal Seek with the cell reference of the data
point.
Another way to visualize is to copy yr data column (as values). The copied
values you can conditionally format (>Format/Conditional Format...) in
comparison to the data series (if(A<>B, "make cell B yellow","") for
instance). Now if you move a point by hand the cell that changes turns
yellow, triggered by the conditional format. Write down the cell reference,
and restore by Ctrl-Z to move the data point back in place.
HTH,
Henk

 

[Guest]

HEK:

Thanks. Both ways defenitely help to find the reference cell of the data
point. And can you hightlight data points on a chart by highlighting the
reference cells?

Windson

 

[Guest]

Windson:
Sorry to be late with my answer - I was on vacation.
I believe this desire can be fulfilled, yes.

In the table you can simply use conditional formatting, to give the cell an
outstanding colour, a bold border or any formatting effect you want to apply.
Simply select from the menu Format/Conditional Formatting...

In the graph: create two new data series from yr original data set, both
with IF statements with complementary conditions. Example: say you want to
highlight values >100. Yr original data series in A, set up the two series
in B and C. B as IF(A>100,A,NA()) and C IF(A<=100,NA(),A). Don't plot A (I
assume XY scatter chart), but B and C instead. The NA() values are ignored,
and the two series complement each other. If u hv only one value >100, this
will appear as different colour. You can change the marker. Of course this
principle can be adopted to other ways (e.g., a circle around an data point t
highlight, or an arrow pointing to a particular point.)
HTH,
Henk

 

[Guest]

Henk,
Thanks for the idea. I tried similar ways before. The only problem is you
have to use different series (or columns) of source data. So if you have ten
differnet groups to highlight in distinctive ways it's a little
time-consuming. What I am seeking for is a more automatic way using a single
column as Y values. Probably I need macro to do so, but I am not good at it
now.

Thanks again and hope you enjoy your vacation.

Windson