Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

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.
doc average data in noncontiguous ranges

1. Select the data range and click Insert > Insert Line Or Area Chart > Line with Markers, then a line chart has been inserted.
doc average data in noncontiguous ranges 1

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.
doc average data in noncontiguous ranges 2

3. Then drag the auto fill handle down or right to extract all relevant data of this series.
doc dynamic highlight data point in chart 9

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.
doc average data in noncontiguous ranges 3

5. Then right click at the new series, select Format Data Series in the popped context menu.
doc average data in noncontiguous ranges 4

6. In the Format Data Series pane, under Fill & Line tab, check No line option in Line section.
doc average data in noncontiguous ranges 5

=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.
doc average data in noncontiguous ranges 6 doc average data in noncontiguous ranges 7 

8. Then go back to the chart, remove the name of new series.
doc average data in noncontiguous ranges 8

Now when you change the series name in F1, the highlighting chart points will be changed automatically.

Click to download sample file

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.
doc file convert

Other Operations (Articles) Related To Chart



  • 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...
kte tab 201905
  • 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!
officetab bottom
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.