Dynamic highlight data point on Excel chart
If a chart with multiple series and a lot of data plotted on it, which will be difficult to read or find only relevant data in one series you use. Here this tutorial will show a method to dynamically highlight the data points of active series in Excel as below screenshot shown.
1. Select the data range and click Insert > Insert Line Or Area Chart > Line with Markers, then a line chart has been inserted.
2. Then in a blank cell, type one of the series name, supposing in Cell F1 type KTE, then in F2, type this formula:
In this formula, B2:D5 is the data range excluding column headers and row headers; E2:E2 is the next blank cell to the formula; F1 is the cell that you type the series name, B1:D1 is the range that the series name in the data table.
3. Then drag the auto fill handle down or right to extract all relevant data of this series.
4. Select the formula cells, F2:F5, press Ctrl + C to copy them, then select the chart and press Ctrl + V to paste, now, notice which series changes the color.
5. Then right click at the new series, select Format Data Series in the popped context menu.
6. In the Format Data Series pane, under Fill & Line tab, check No line option in Line section.
7. Go to the Marker section,
1) In Marker Options group, check Built-in option, and choose the circle mark in Type drop-down list, then enlarge the Size to 15, then in Fill group, check No fill.
2) Go to Border group, check Solid line option, and select one highlighting color you want, then enlarge the Width to 1, and select one Dash line you need.
8. Then go back to the chart, remove the name of new series.
Now when you change the series name in F1, the highlighting chart points will be changed automatically.
|Quickly Insert Bubble/Speedometer/Bell Curve Chart |
|The Bubble chart, speedometer chart and bell curve chart is usually used for analyze data in Excel, but the creation of all of them is complex in Excel. Here, Kutools for Excel's Charts group can help you quickly create the Bubble chart, Speedometer chart or Bell Curve chart. |
Click for 30 free trial.