Fill Blank Cells in a Range

DigDB

Add-in Tools for Excel Elites
Who else wants to do a 10-hour Excel analysis in 15 minutes? (more ...)

Features | Testimonial | Download ^{free 15-day trial} | Purchase | FAQ | Contact

Excel's Fill function allows you to fill a range from top to bottom or left to right or vice versa, but it overwrites everything within the range. What if I only want to fill the blank cells? As the blanks are often scattered within a range, using Excel you will have to find and fill each blank blocks manually. DigDB allows you to do this easily. You have three ways to fill:

Fill blanks by base values in the range - Similar to Excel's Fill Up, Down, Left, Right, except DigDB will do this on every block of blanks cells in your range.
Fill blanks by linear series (interpolate) - Similar to Excel's AutoFill with linear interpolation, i.e. the cells in-between will be filled by values of the linear series from the starting cell to the ending cell. DigDB will find each blank block, and fill the linear series either from top to bottom in each column, or from left to right in each row. This works on all number, date, and time values.
Fill blanks by user-input value(s) - You can enter one or multiple values, and only the blank cells in the selected range will be set to your values. Multiple values will simply rotate evenly.

Practice file - fill-blanks-demo.xls (16k)

Fill blanks by base values in the range

Select a range, invoke 'DigDB->Selected Area->Fill Blanks by->Base Value->...', you have 4 options, Down, Up, Left, Right,

Fill Down - in each blank block, copy each column's top neighboring cell's value to the blank cells below; in case there is no top neighboring cell, use 0.

Fill Up - in each blank block, copy each column's bottom neighboring cell's value to the blank cells above; in case there is no bottom neighboring cell, use 0.

Fill Right - in each blank block, copy each rows's left-most neighboring cell's value to the blank cells to the right; in case there is no left-most neighboring cell, use 0.

Fill Left - in each blank block, copy each row's right-most neighboring cell's value to the blank cells to the left; in case there is no right-most neighboring cell, use 0.

Fill blanks by linear series (interpolate) - works on numbers, dates, time values

Select a range, invoke 'DigDB->Selected Area->Fill Blanks by->Linear Series->...', you have 2 options, Top to Bottom, or Left to Right

Linear Series from Top to Bottom - in each column of each blank block, interpolate a linear series between the top and bottom non-blank neighboring cells; use 0 in case of missing a non-blank cell.

Linear Series from Left to Right - in each row of each blank block, interpolate a linear series between the left-most and the right-most non-blank neighboring cells; use 0 in case of missing a non-blank cell.

Fill blanks by user-input values

Select a range, invoke 'DigDB->Selected Area->Fill Blanks by->Values...'
Enter one or multiple values. Use 'Add >>' to add more values.
DigDB will rotate your inputs to fill out the blanks.

Need more?

Browse the complete list of DigDB's powerful features listed on the front page.
If you see errors or things not working, check out our error reference page.

Related keywords: Advanced excel tips, excel questions tools, Excel reporting applications, Excel add-ins add-in, Excel shareware, Excel plugin plug-ins plugins, Excel tricks question, Excel utility utilities, Excel add-on add-ons, Excel tip tips, Power excel technique, Excel manipluation, Excel guru, Excel trick, Excel toolkit, Excel app power user

Microsoft and the Office logo are trademarks or registered trademarks of
Microsoft Corporation in the United States and/or other countries.