The main challenge in most Excel charting questions is getting your data arranged so that Excel can create the chart you want. In your case you want:
- horizontal axis to show date (not time-an important distinction)
- vertical axis to show salary values
- each line (series) to show a single individual's salary
First, make sure your dates are actually dates in Excel.
Next rearrange your data so that each line (series) is in a single column (or row). The easiest way to accomplish this is to create a Pivot Table. Change your data into a Table Insert > Table
Then, use that Table as a source for your Pivot Table, Insert > Pivot Table, with
- Columns = Name
- Rows = Salary Change Date
- Values = Sum of Salary
From here, you can create a Pivot Chart (a special chart tied to Pivot Tables), and it will look something like this:
There are two major problems: the lack of line between Lucy's data points and the inconsistent spacing of the date points.
To correct the gap, you'll need to adjust the chart's Hidden and Empty Cells setting, found in the Select Data Source dialog box. It defaults to Gaps and you need to change it to Connect data points with line.
The other problem is more challenging. In a Line Chart, Excel displays the Horizontal Axis as a categorical axis-meaning that each entry is given the same weight. So even though there's 8 months between your first and second dates (2020-05-05 and 2021-02-01), and 5 months between your second and third dates (2021-02-01 and 2021-07-01), there's the same distance along the horizontal axis-1 tick each.
To correct this, you need to pad your data with empty date values (to the Month level, if that's close enough, or to the Date level if it's critical). The resulting Table and Pivot Table might look like this:
And the Chart would look like this:
A better option is to use an XY/Scatter Chart (because both Axis values are displayed as Continuous data, which is better for dates in your context). Unfortunately, Pivot Charts cannot use an XY/Scatter Chart type. You can still use your Pivot Table as your data source, you just need to do it manually.
- Select a blank cell
Insert > Charts > Scatter > Scatter with Straight Lines and Markers- For each data series, manually set:
- Series Name = Pivot Table Column Label
- Series X = Pivot Table dates
- Series Y = Pivot Table sum of salary values for applicable column
- Adjust
Hidden and Empty Cells settings as above
And your chart should look like this:
It's nearly identical to the Pivot Chart, without the need for additional padded values and the resulting Blank series.
