A common problem is to make a chart from a list like this one. You want separate series for each item in one column (e.g., the cities in the first column), but your list is different every time, and you have to spend an hour assigning data to each series in the chart.
This is a job for VBA. It’s possible to write a simple loop that reads the first column of the range, grouping rows together by item. The VBA procedure is listed at the end of this post. Select a chart, run the procedure. A dialog pops up, asking for the data range.
The code puts each group of rows into its own series, based on the label in the first column of the range, and labels each point of the series with this label (which is also used as the series name). The chart is shown below:
Sub PopulateChartFromTable() Dim cht As Chart Dim rng As Range Dim sPrompt As String Dim iSrs As Long Dim srs As Series Dim iRow As Long Dim iRowStart As Long Dim iRowEnd As Long Dim sSeries As String If ActiveChart Is Nothing Then MsgBox "Select a chart and try again.", vbExclamation GoTo ExitSub End If sPrompt = "Select a three-column range with your data." sPrompt = sPrompt & vbNewLine & " Column 1: Series title" sPrompt = sPrompt & vbNewLine & " Column 2: X values" sPrompt = sPrompt & vbNewLine & " Column 3: Y values" sPrompt = sPrompt & vbNewLine & "Avoid blank cells" On Error Resume Next Set rng = Application.InputBox(Prompt:=sPrompt, Type:=8) On Error GoTo 0 If rng Is Nothing Then GoTo ExitSub Set cht = ActiveChart Do If cht.SeriesCollection.Count = 0 Then Exit Do cht.SeriesCollection(1).Delete Loop sSeries = "" iSrs = 0 For iRow = 1 To rng.Rows.Count + 1 If rng.Cells(iRow, 1).Value <> sSeries Or iRow > rng.Rows.Count Then If iSrs > 0 Then iRowEnd = iRow - 1 Set srs = cht.SeriesCollection.NewSeries With srs .Values = rng.Cells(iRowStart, 3).Resize(iRowEnd + 1 - iRowStart) .XValues = rng.Cells(iRowStart, 2).Resize(iRowEnd + 1 - iRowStart) .Name = rng.Cells(iRowStart, 1).Value .ApplyDataLabels ShowSeriesName:=True, _ ShowCategoryName:=False, ShowValue:=False End With End If iRowStart = iRow sSeries = rng.Cells(iRow, 1).Value iSrs = iSrs + 1 End If Next ExitSub: Application.ScreenUpdating = True End Sub
For a formula-based solution to this issue, see Split Data Range into Multiple Chart Series without VBA.