Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to highlight a cell so I know which point on the chart it is?
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. |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to highlight a cell so I know which point on the chart it is?
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 "Windson" wrote: 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. |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to highlight a cell so I know which point on the chart it
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 "HEK" wrote: 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 "Windson" wrote: 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. |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to highlight a cell so I know which point on the chart it
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(A100,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 "Windson" wrote: 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 "HEK" wrote: 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 "Windson" wrote: 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. |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
how to highlight a cell so I know which point on the chart it
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 "HEK" wrote: 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(A100,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 "Windson" wrote: 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 "HEK" wrote: 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 "Windson" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with this conditional IF statement | Excel Discussion (Misc queries) | |||
How do I print comments on a chart? | Charts and Charting in Excel | |||
Display a digit before or after the decimal point in another cell | Excel Worksheet Functions | |||
up to 7 functions? | Excel Worksheet Functions | |||
Display cell value on a chart. | Charts and Charting in Excel |