Make excel fill inn missing data linearly

Question

Hi i have a dataset with some missing values. i have posted an example picture below. Excel is outputting the blue graph. Is it possible to make excel draw the red line between the two known points? i could probably fill the missing point with the average value of the two known values, but i wonder if it is possible to do it in a more efficient way as my dataset consist of several of theese holes.

Answer 1

Choose select data on the chart design tab and select the button what to do with hidden and empty cells. Select ot connect data points with a line. No need for a FORECAST formula.

The above picture was taken on a Mac, but it's similar on a PC.

Answer 2

Hi Lerka

I'm AnnaThomas and I'd be happy to help you with your question. In this Forum, we are Microsoft consumers just like yourself.

Yes, it's possible to make Excel fill in missing data linearly by using the linear interpolation function. Here's how you can do it:

Select the column that contains the missing data.

Go to the "Home" tab and click on "Find & Select" in the "Editing" group. Then select "Go To Special".

In the "Go To Special" dialog box, select "Blanks" and click "OK". This will select all the blank cells in the column.

Right-click on one of the selected cells and choose "Format Cells".

In the "Format Cells" dialog box, select the "Number" tab and choose "Number" as the category. Then click "OK".

In the first empty cell, enter the formula "=FORECAST. LINEAR(A2,A$1:A$7,B$1:B$7)" (assuming your data starts in cell A1 and ends in cell B7). This will interpolate the missing value based on the values before and after it.

Press "Enter" to apply the formula. The missing cell should now be filled with a linearly interpolated value.

Double-click the small green square at the bottom-right of the cell to fill the formula down to all the other empty cells in the column.

Your graph should now display a linear line connecting the two known data points.

I hope this helps ;-), let me know if this is contrary to what you need, I would still be helpful to answer more of your questions.

Best Regards,

AnnaThomas

Give back to the community. Help the next person with this problem by indicating whether this answer solved your problem. Click Yes or No at the bottom.

Answer 3

Thank you! quick and easy. perfect!

Answer 4

One follow up question. do you have an easy way to mark the "generated line" i want something like this:

Answer 5

To display a shaded column would be difficult. I'm not saying impossible, but I can't think of an easy way right now. Much easier would be to revert to your initial question where you wanted a different colour for the "missing" line segment.

Add another series with he formula shown in the picture. It creates a line with only two points. One on each side of the gap. With the same setting as used earlier you can connect the gap between these two points with a line. Note that the "Gap" line formula requires B12 not to be empty. Therefore, I just put an X there. Perhaps not very elegant, but it works.