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
Excel offers several ways to fill blank cells with interpolated linear values. You can use the built-in Fill Series feature, Kutools for Excel, formulas, or VBA. The right method depends largely on whether you are filling one small gap or many blank sections, and whether you want to use formulas or code.
Compare methods for filling blank cells with linear values
All four methods can generate linear values between known numbers, but their workflows differ considerably. Fill Series and formulas are useful built-in approaches for individual sections, VBA provides code-based automation, while Kutools for Excel can process the selected range through a dedicated Fill Blank Cells tool without formulas or macros.
| Method | Multiple blank sections | Setup | Best for |
|---|---|---|---|
| Fill Series | Repeat the operation for each blank section | Select each block and apply Fill Series | Quick built-in interpolation for one or a few blank sections |
| Kutools for Excel | Processes blank cells throughout the selected range in one operation | Select the range, choose Fill Blank Cells > Linear values, and specify the fill order | Quickly filling multiple blanks or sections without formulas or VBA |
| Formula | Formula references must be adjusted for each section | Build an interpolation formula and fill it through each blank block | Formula-based calculations where you want control over the interpolation logic |
| VBA | Can automate multiple blank sections in the selected list | Insert and run a VBA macro | Repeatable code-based automation for users comfortable with VBA |
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.
- Select the range from A2 to A8, and then click "Home" > "Fill" > "Series", see screenshot:
- In the "Series" dialog, just click OK—Excel will fill the first block of blank cells with linear values. See screenshot:
- 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 need to fill several blank sections or work with larger ranges, Kutools for Excel’s Fill Blank Cells feature provides a more direct solution. Instead of selecting and processing each blank block separately, you can select the target range, choose Linear values, and let Kutools fill the blanks in one operation. The same tool can also fill blanks based on adjacent values or with a fixed value when needed.
After installing Kutools for Excel, please do with the following steps:
- Select the data range that you want to fill linear values. Then, click Kutools > Insert > Fill Blank Cells, see screenshot:
- In the Fill Blank Cells dialog box, check the Linear values option and choose the fill order—From left to right or From top to bottom—according to the layout of your data. See screenshot:
- Click OK. Kutools automatically fills the blank cells in the selected range with linear values between the known data points. 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.
- This method only works between two known values. If your column contains multiple blocks of blank cells, you’ll need to repeat this process for each block, adjusting the references in the formula accordingly.
- Make sure your data is numeric, as this method is intended for numerical linear interpolation.
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.
- Select the data list you want to fill blank cells with linear values.
- Click Alt + F11 to open the Microsoft Visual Basic for Applications window.
- 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 - Then, press the F5 key to run this code. All blank cells in the selected list 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. The best method depends mainly on how many blank sections you need to process and whether you prefer built-in tools, formulas, or automation.
- Use Fill Series for a quick built-in solution when you only need to interpolate one or a few sections.
- Use Kutools for Excel when you want to process a larger selected range or multiple blank sections quickly without formulas or VBA.
- Use formulas when you want direct control over the interpolation calculation and do not mind adjusting references for different sections.
- Use VBA when you need a reusable code-based solution and are comfortable working with macros.
For routine work involving multiple blank areas, Kutools for Excel can significantly shorten the process by handling the selected range through one Fill Blank Cells dialog instead of repeating Fill Series operations or rebuilding formulas for each section. If you're interested in exploring more Excel tips and tricks, our website offers thousands of tutorials to help you master Excel.
Related articles:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!
All Kutools add-ins. One installer
Kutools for Office suite bundles add-ins for Excel, Word, Outlook & PowerPoint plus Office Tab Pro, which is ideal for teams working across Office apps.
- All-in-one suite — Excel, Word, Outlook & PowerPoint add-ins + Office Tab Pro
- One installer, one license — set up in minutes (MSI-ready)
- Works better together — streamlined productivity across Office apps
- 30-day full-featured trial — no registration, no credit card
- Best value — save vs buying individual add-in