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:
=INDEX($B$2:$D$5,ROWS($E$2:E2),MATCH($F$1,$B$1:$D$1,0))
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.
=AVERAGE(AverageR)
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. |
 |
The Best Office Productivity Tools
Kutools for Excel Solves Most of Your Problems, and Increases Your Productivity by 80%
- Super Formula Bar (easily edit multiple lines of text and formula); Reading Layout (easily read and edit large numbers of cells); Paste to Filtered Range...
- Merge Cells/Rows/Columns and Keeping Data; Split Cells Content; Combine Duplicate Rows and Sum/Average... Prevent Duplicate Cells; Compare Ranges...
- Select Duplicate or Unique Rows; Select Blank Rows (all cells are empty); Super Find and Fuzzy Find in Many Workbooks; Random Select...
- Exact Copy Multiple Cells without changing formula reference; Auto Create References to Multiple Sheets; Insert Bullets, Check Boxes and more...
- Favorite and Quickly Insert Formulas, Ranges, Charts and Pictures; Encrypt Cells with password; Create Mailing List and send emails...
- Extract Text, Add Text, Remove by Position, Remove Space; Create and Print Paging Subtotals; Convert Between Cells Content and Comments...
- Super Filter (save and apply filter schemes to other sheets); Advanced Sort by month/week/day, frequency and more; Special Filter by bold, italic...
- Combine Workbooks and WorkSheets; Merge Tables based on key columns; Split Data into Multiple Sheets; Batch Convert xls, xlsx and PDF...
- Pivot Table Grouping by week number, day of week and more... Show Unlocked, Locked Cells by different colors; Highlight Cells That Have Formula/Name...
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
