0

How can I plot 2 datasets with different dates on x-axis? Whenever I try to plot them it shows both at the same time although actually they are at different times, for example

enter image description here

1
  • Make sure your dates are actually dates and note text that looks like a date. you can test with the following formula and you need a true result. '=ISNUMBER(B3)' where B3 is a cell with one of your dates. – Forward Ed Sep 9 '20 at 13:50
1

It' recommended to use Scatter with Straight Lines to create the X-axis, and add the data labels.

I found there are some duplicate data in Group 2, so I use "Remove Duplicates" to remove them.

To plot 2 datasets with different dates on X axis, I add a column X with "0" and column Y to count the gap of days, and one day is the Units on X-axis. enter image description here

Let us say the start date is "01-Mar-18", to get the Y values for Group 1, I use the formula =J2-$J$2 in L2 and pull down to get other values, for Group 2, the formula in L8 is =J8-$J$2+365*2+1, and pull down. You may also set different Units with dates on the X axis according to your needs.

Then I add 4 series of Scatter with Straight Lines and Markers. enter image description here

enter image description here enter image description here

enter image description here enter image description here

Delete the X-axis, add data labels for these 4 series.

Select one data Label of A-axis 1, enter = and click the corresponding date cell, press Enter. Then the label would show the previous date.

enter image description here

Adjust the position of the label, and set the color for them, I get the following chart at last.

enter image description here

1

Unlike other other graphing programs, excel is not designed to work with multiple X-axis series.

You have two choices. you can swap your X and Y axis around so that your date goes up the Y axis, and then you can have each series with its own Y axis to plot against. I do not recommend as its not an intuitive visualization to me.

So we can cheat a bit, rearrange your data and make it look reasonable.

First I started by eliminating the duplicate data entries where price and date were the same. These can be kept in if you need them to be but it was just as easy to pull them out. In the sample data at the end this is column B and C.

The next step was to convert the dates so they were all the same year while maintaining month and date. I did this via the following formula placed in E3 and copied down.

=DATE(2020,MONTH(B3),DAY(B3))

After getting the "NEW" dates, I placed the values from group 1 in the next column (F) and the values from group 2 in the column after that (G).

Now for things to lineup, you need to sort by date. However, if we sort on formula dates the formulas will just update after the sort and you will be looking at screwed up date order. To avoid this, I copied and pasted values into columns I:K. I then selected I3:K14 and sorted by column I oldest to newest. And to complete the picture I put the original year in at the top of the column as a name for the series.

The next step is to select I2:K14 and insert and X/Y scatter plot. If you just one the dots pit the one with out the lines and you are off to the races. Just read the part about formatting the axis. If you want line, select the X/Y scatter with lines. Now when you do this it will initially look bad as their will be gaps in your graph.

GAP GRAPH

To fix this, right click on the graph and left click on "Select Data..."

Select Data

In the bottom left of the window that come up left click on the box that says "Hidden and Empty Cells".

HIDDEN AND EMPTY CELLS

By default, empty cells are shown as gaps. Change the radio button and select "Connect data points with line" instead

Connect points

When you get back to your graph it should now look like the following in terms of the plotted points:

Connected lines

Now your X-Axis format is not matching the one in the image, but its an easy enough fix. Left click on one of the number in the axis. When you do the a dashed box should show up around all the numbers and on the right side of the spreadsheet a menu should appear that look like this:

Format Axis

There are other ways to get to formatting the X-Axis and you are free to navigate there however you like. I selected Format axis and my menu adjusted to what is needed. You want that menu on the right to eventually look like this:

Format Axis Menu

Down at the bottom of that menu left click on that triangle beside "NUMBER" to expand that submenu to look like the following:

Number Submenu

In the very bottom box "Format Code" enter mm/dd or dd/mm or dd-mmm or mmm-d or some variation of that. Note you may need to change the lettering to meet the needs of your regional formatting. The above should work just find for most of North America. Then hit the add button and the graph and rest of the fields should update them selves.

Final format

Example Data

Example

Now a little caveat. If you are working with a leap year, and namely if you have data on FEBRUARY 29th, make sure that the year you choose as the common year for all dates is the year with the leap year. Otherwise you may wind up with February 29th's data showing up March 1st's data or an error.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.