Two colored line chart
Have you ever wondered how you could present a line chart with two colors representing the actual for the past and the projected for the future. Where the line chart needs to be a single line and represented in two colors similar the chart below.
Though it does look a bit had to do, the above chart is something that can be done with ease. All you need to understand how a line chat works, and then take few steps with re-arranging the data. Once the data is arranged then all you need to do is inset a line chart and it will work like a charm.
In order to explain the steps, lets take 12 months of data. In my data set I have assumed that the chart is representing monthly sales data. In my data, I have assumed that the data for Jan to May are what I have as actual data, and data beyond May is what is being projected.
I have also taken time to highlight the data which I have assumed to be what is being projected.
Once you do have a set of data, then the fist step would be to create an additional data table with keeping the original data in tack (This will help with re-working if you do make mistakes).
As I said the first step is to create a data area say in column E. The table I created is as per the following.
The first step is to place the months under the heading months. As the sales data in the chart are only for the year 2021, I am only typing the name of the month in short form.
The second step is to copy the actual sales data under the heading actual.
Third step is to copy the projected sales under the projected sales heading.
Once these steps have been done, insert a line chat in the to the work sheet. Now follow the steps given below and define each data ranges. In order to do so, select the empty chart area, and then select the Design Tab, and select select data option.
Once you have selected the select data the following dialog box will appear.
The fist step would be to introduce the fist data set to the chart. In this chart the first data set would be the actual sales data. To do so, select the “Add” button which is indicated in the left of the dialog box “Legend Entries (Series)”, and select the fist data set.
Select the “Series name:” click on the up arrow, and then select the heading from the data. In my data this would be cell “F2”. Now, select the “Series values:” and click on the up arrow and then select the first data range. In my data set this would be “F3:F14”. Once you have selected the data, your dialog box would look like the one in the left, and your chart will start indicating a line too.
Now, follow the same steps and introduce the second data set. In this instance in my data the dialog box with selected data would be similar to the dialog box on the left.
Once both the data have been entered. You would notice the the “X” axis has numbers 1 to 12 instead of the months. To introduce the months, select the “Edit” button in “Select data dialog box” .
In the dialog box that appear, click on the up arrow, and select the data range with the month names. In my data, this would be “E3:E14”. Now click “OK” button. Now once you have done these you will have a chart similar to the one below.
The chart is not right as of now, as you would notice, there is a gap in the two line. between May and June. The reason being a line chart is a chart where a line is drawn connecting two data sets, and in the second data set there is no data point informing excel to do so between May and June. To resolve this issue, simply copy the same data point from actual sale to projected for the month of June and your chart would work like a charm.
Now all you need to do is introduce the legends, “Y” axis data label and Heading to the chart. To do so click on the chart, and click on the “+” sign.
Tick the Axis Titles, Chart Titles and legends from the popped menu and click the “+” sign again to close the popup menu.
Now you can edit each of the Axis Titles and the Chart Title and you are done.
Since the “X” axis in this chart is obvious I took the liberty of deleting the X Axis Title. Now the chart should like the one given below.
Happy Charting. :)