Views > Excel charts > How to create a line chart with multiple series in excel

It is often useful to display the time series of multiple data sets in a single line chart. Each data set is encoded in the graph using a separate line.

This works fine as long as there is a small number of data sets. The more lines we add to the graph the more cluttered the graph becomes.

But sometimes showing many lines in one graph is exactly what we need to solve the problem at hand. And the solution to the clutter problem is to calm down all the lines—by displaying them with thin grey lines—while highlighting the most relevant data series with a thick dark line for comparison.

Have a look at the chart below. It is showing the annual average unemployment rate for 51 states and 34 years (from 1990 till 2023). New York state stands out more clearly than the remaining 50 states. A darker color and a thicker line helps with the contrast from the sea of thin light grey lines. The thin grey lines representing the other 50 states give more context. This kind of graphs enables us to see the forest while at the same time focusing on a single tree (NY in this case).

The chart above takes few seconds to understand because someone (me :) ) had spent some effort to declutter it and to make it clear and clean. Contrast that with the chart below which is simply created by highlighting the data in Excel and then clicking on the default line graph.

Of course, the graph above can be transformed and decluttered. But here is where troubles start. Moving from the above raw graph to the cleaner version requires the cumbersome process of manually changing the color of each data series (51 lines each representing a US State in this case)—one by one. The same should be done to the thickness of each line. To make things worse, when a different dataset—with more or less data—is used, some of the formatting done to the chart is lost and has to be redone again. Not efficient. Not fun.

To solve this problem we can take advantage of the new Dynamic Arrays features of Excel (Only available to Microsoft 365 subscribers). Dynamic Arrays enable us to unpivot the data and transform it into one single data series. Once the transformation is done, we can use a scatterplot instead of the standard Excel line chart to represent the data. The moment we have all states merged into a single data series, it becomes easy to change the formatting.

Lets start.

The original data is shown in the table below (range B2:AJ53) where the years are along the columns and the states are along the rows.

1. Before we unpivot the data we need to add an #N/A at the end of each row. Without that #N/A, Excel will connect the end of one data series with the beginning of the subsequent data series. By forcing an #N/A, Excel charts will show #N/A as empty cells (gaps) instead of connecting the lines. Therefore, enter the following formulas in the respective sells:
A56 --> = COUNTA(B3:B53)

B56 --> = SEQUENCE(A56,1,1,0) + NA()

The above will generate an array (51x1) of #N/As.

2. Next we need to stack the above array to the end of the original data set. To do that add the following formula in the respective cell:

C56 --> = HSTACK(C3:AJ53,B56#)

The data series become like this where an #N/A is added to the end of each row:

3. Next we need to unpivot the data and create the setup for the scatterplot. For that we need to generate 35 * 51 = 1,785 pairs of x and y. To do that enter the following formulas in the respective cells:

A109 --> = COUNT(C2:AJ2) + 1
A110 --> = A109 * A56
B109 --> = SEQUENCE(A110,1,1,1)
C109 --> = IF(MOD(B109#,A109) = 0,A109,MOD(B109#,A109))
D109 --> = IF(C109# = A109,NA(),C109# - 1)
E109 --> = TOCOL(C56#)

The outcome is 1,785 pairs of x and y that makes up one data series for the scatterplot as per the below screenshot:

4. Select the range D108:E1893 and then on the Insert tab, in the Charts group, click the Insert Scatter (X,Y) or Bubble Chart icon and then select the Scatter with Straight Lines icon. The chart should look like this:

5. Lets do some cleaning. Delete the Chart Title.
6. Delete the Horizontal (Value) Axis Major Gridlines.
7. Turn off the border of the Chart Area by setting the Shape Outline to No Outline.
8. Change the chart font to Trebuchet MS, its size to 9 and color to Black.
9. Set the Shape Outline of the Vertical (Value) Axis to No Outline.
10. Set the Shape Outline of the Vertical (Value) Axis Major Gridlines to Dashes > Dash.
11. Set the following settings for the Horizontal (Value) Axis: set the Labels > Labels Position to None, The Tick Marks > Major type to Outside. Set the Axis Options > Bounds > Maximum to 33 and the Axis Options > Units > Major to 1. Set its Line > Solid Line > Colour to black.
12. In the chart, select the data Series "y" data series and set its Line > Solid Line > Colour to light grey and its Width to 1 pt.
The chart should look like this:

Next, we need to add a series for the selected state (i.e. NY) and a series for the horizontal axis labels.
13. On the worksheet select cell G108. Then on the Data tab, in the Data Tools group click the Data Validation icon and then select the Data Validation option.
14. In the Data Validation dialog box, on the Settings tab, select List from the Allow: dropdown list.
15. Then refer to the range B3:B53 in the Source: edit box as per the screenshot below:

18. Right-click the chart and then, on the shortcut menu, click Select Data.
19. In the Select Data Source dialog box, click the Add button under Legend Entries (Series) to add a new data series.
20. In the Edit Series dialog box, refer to cell G108 in the Series name edit box. Next, edit the Series X values to include cells D109:D142 and the Series Y values to include cells G109:G142 as per the screen shot below:

21. Click the Ok button to close the Edit Series dialog box.
22. Repeat steps 19-20 to add the series Year for the horizontal axis by referring to cell H108 in the Series name edit box, cells D109:D142 in the Series X values and cells H109:H142 in the Series Y values.
23. Click the Ok button twice in a row to close the Edit Series dialog box and the Select Data Source dialog box. The chart should look like this:

16. Click the Ok button to close the Data Validation dialog box. And then select NY from the drop down list just created in cell G108.
17. Next, type the following formulas in the respective cells:
G109 --> = TRANSPOSE(INDEX(C3:AJ53,MATCH(G108,B3:B53,0),))
H109 --> = SEQUENCE(A109 - 1,1,0,0)

The worksheet should look like this:

24. In the chart select the Series "Year" data series. Next, click the Charts Elements button located on the upper-right corner of the chart. Then, in the expanded menu, check the Data Labels option.
25. In the chart, right-click the Series "Year" Data Labels and then, on the shortcut menu, click Format Data Labels.
26. In the Format Data Labels pane set the Label Position to Below.
27. Under Label Contains, click the Value From Cells option. Next, in the Data Label Range dialog box refer to the range C2:AJ2 in the Select Data Label Range edit box. Click the Ok button to close the Data Label Range dialog box.
28. Under Label Contains, uncheck the Y Value and the Show Leader Lines options.
29. In the chart, select the Series "Year" data series and then, in the Format Data Series pane set the Line to No line.
30. Adjust the size of the Plot Area by dragging its bottom edge a bit to the top to avoid the data labels overlapping with the horizontal axis. Adjust the size of the chart so that the axis labels are not overlapping and the chart should look like this:

31. Finaly, format the Series "NY" data series by changing its line color and thickness.
32. Add a marker and data labels for the first and last data points.33. Add a title and data source to the graph and the chart should look like this:

The chart is dynamic. Select another state from the dropdown list in cell G108—say MI—the chart should look like this:

CREATE AWSOME CHARTS

Thank you for reading, and I hope you found this useful. If you have any questions, find me on X (Twitter) and ask me anything.