Fill blank cells with linear values in Excel – (4 Efficient Methods)

In everyday use of Excel, we often encounter data columns that contain blank cells. These blanks may represent data that has not yet been entered or repeated values intentionally omitted for a cleaner layout. In certain analysis tasks, filling these blank cells with linear trend values can help present data trends more clearly and prepare the dataset for subsequent charts or calculations. This comprehensive guide will walk you through several optimized methods to accomplish this efficiently.

Fill blank cells with linear values in Excel

• By using Fill Series feature
• By using Kutools for Excel
• By using formula
• By using VBA code

Fill blank cells with linear values by Fill Series feature

Excel’s Fill Series tool is a built-in way to automatically complete sequences, and it's perfect for filling linear values when blanks occur between two known numbers.

1. Select the range from A2 to A8, and then click "Home" > "Fill" > "Series", see screenshot:
   
2. In the "Series" dialog, just click OK—Excel will fill the first block of blank cells with linear values. See screenshot:
   
3. To fill additional blocks of blank cells, select the next range (such as A9:A14) and repeat the steps above to apply linear interpolation.
                     

Fill blank cells with linear values by Kutools for Excel

If you frequently work with large or complex datasets, Kutools for Excel’s “Fill Blank Cells” feature provides a simple and efficient way to fill blanks with linear values. Additionally, this tool allows you to fill blanks based on the direction of adjacent cells (up, down, left, or right), or even fill them with a fixed value, offering great flexibility.

After installing Kutools for Excel, please do with the following steps:

1. Select the data range that you want to fill linear values. Then, click "Kutools" > "Insert" > "Fill Blank Cells", see screenshot:
   
2. In the "Fill Blank Cells" dialog box, check the "Linear values" option and fill order (From left to right or From top to bottom) based on your data. See screenshot:
   
3. Then, click OK Button, Kutools will automatically fill in the blanks based on a linear trend between the known values. See screenshot:
   

Fill blank cells with linear values by a formula

For those who prefer formula-based solutions or want more control over their calculations, using formulas is a reliable and dynamic method.

Suppose your data is in column A (A2:A8), and you want to fill blanks based on a linear interpolation.

1. Enter or copy the following formula in cell A3, (In the above formula, A2 is the start cell, and A8 is the next data cell.) see screenshot:

=A2+($A$8-$A$2)/(ROW($A$8)-ROW($A$2))

2. Drag the fill handle down from A3 to the row just above the next known value (in this example, A7). Once filled, you'll see the blank cells are now populated with interpolated values that follow a straight-line trend between the two known data points.

Fill blank cells with linear values by VBA code

If you frequently need to fill blank cells with linear values, a VBA macro can save time by automating the task.

1. Select the data list you want to fill blank cells with linear values.
2. Click "Alt+F11" to open the "Microsoft Visual Basic for Applications" window.
3. Click "Insert" > "Module", and paste the following VBA code into the Module window.

   Sub LinearFillBlanks()
   'Updateby Extendoffice
       Dim rng As Range
       Dim cell As Range
       Dim startCell As Range
       Dim endCell As Range
       Dim i As Long, countBlank As Long
       Dim startVal As Double, endVal As Double, stepVal As Double
       Set rng = Selection
       For i = 1 To rng.Rows.Count
           If Not IsEmpty(rng.Cells(i, 1).Value) Then
               Set startCell = rng.Cells(i, 1)
               startVal = startCell.Value
               countBlank = 0
               Do While i + countBlank + 1 <= rng.Rows.Count And IsEmpty(rng.Cells(i + countBlank + 1, 1))
                   countBlank = countBlank + 1
               Loop
               If i + countBlank + 1 <= rng.Rows.Count Then
                   Set endCell = rng.Cells(i + countBlank + 1, 1)
                   endVal = endCell.Value
                   stepVal = (endVal - startVal) / (countBlank + 1)
                   For j = 1 To countBlank
                       rng.Cells(i + j, 1).Value = startVal + stepVal * j
                   Next j
               End If
           End If
       Next i
   End Sub
   

4. Then, press F5 key to run this code, all blank cells are filled with linear values. See screenshot:
   

Conclusion

Filling blank cells with linear values in Excel can greatly enhance the accuracy and readability of your data, especially when dealing with trends or numerical patterns. Whether you prefer using Excel’s built-in Fill Series feature, the powerful Kutools for Excel add-in, a flexible formula-based approach, or an automated VBA solution, each method has its own advantages depending on your data size, complexity, and workflow needs.

Choose the method that best fits your scenario:

• Use Fill Series for quick manual interpolation
• Try Kutools for fast, one-click results across large ranges
• Apply formulas for precision and control
• Or automate with VBA for repetitive tasks

By mastering these techniques, you can ensure your Excel datasets are complete and analysis-ready while maintaining data integrity. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.

Related articles:

• Fill blank cells with 0 or other specific value
• Fill blank cells with value above in Excel