How to draw this chart using excell data?

Question

i am drawing population over census..on x axis i have 1951,1961,1971,....1921 i have the values i also have got corrosponding Y axis population for these. Now i also have yearly values of population from 2006 through 2021..i want to plot these(yearly values 2006 onwards) on same x axis with same scale..in other words i want to sow them on my main graph..i want todo this on a line chart. how can i do this ..?

Answer 1

The key fact is to use an XY or scatter chart, and NOT a line chart. A line chart just uses the x axis data as labels, so it doesn't take the actual x data value into account.

Get the data in columns, then highlight it all, then Insert, Chart, and follow the wizard.

Not sure why you need to calculate intermediate values (the other answer given), as the chart will draw straight lines between each data point if you select the appropriate option, but maybe I misunderstood...

Answer 2

You are going to have to create population values for every year in between the census years.  These values will be a straight line inbetween the census points.

Example:

1950 : 152,271,417

1960 : 180,671,158

You will have to create the values for 1951, 1952, 1953, etc. using this formula:

(180,671,158 - 152,271,417) / 10 = 2,839,974.1

Add this number to 1950 to get 1951, then add it to 1951 to get 1952, etc.

This way there will be a straight line inbetween the 1950 and 1960 points.  Repeat, for every decade.

It's a lot of work, but then you can add the yearly values from 2006 to 2021, and the X-Y scatter plot will plot correctly.

.