When we set up an excel file in Microsoft, we always get a file like this screenshot. It’s pretty common that you always leave so many blank cells in one row as it’s exactly the same as the cell above while others rows are fully filled. You try to fill all these “unnecessary and repeated” blank cell with same value but hate to do the “copy-paste” thing.
So here comes the question: do we need to fill the blank cells with value and how to reach our goal rapidly. For the first question, the answer is absolutely YES. Your form will look cleaner and more organized if there are no repeated value.
Nevertheless, leaving blank cells will bring you some trouble when you are needy for sorting, and filtering your data. In this case, you need to fill blank cells with value. In this article, you will learn several tricks to solve this problem. All my tricks are shown below. Let’s see how I do this.
How to fill blank cells with value above in Excel?
Draging directly
If you only need to fill no more than 20 blank cells, I can show you a rapid trick to achive quick cocy & paste. As you can see, in the right corner of each cells, there is a tiny quare. Just drag this tiny cells to the blank cells where you want to fill in.
Using formula to fill blank cells with value above
If you need to fill more than 20 or even coutless blank cells with value, what you need is just a simple formula.
Please follow the procedure:
Step 1: Select the range that you need to fill.
Step 2: Click Home – Find&Select – Go To Special. A diagog box will comes out after step 2. Choose Blanks and click OK.
Step 3: As it shown above, every blank cell is selected and all you need to do next is put a formula “=A2” into A3 (Caution: be cautious about the numberer, A2 means A3 will get value from A2)
Step 4: Last step. Press “Ctrl+ Enter“. Microsoft will apply the formula automaticlly. You do not need to type every value to all these “annoying” blank cells.
Done! 🙂
How to replace values in Excel?
It happens sometimes you want to change some value and you hate to change every cell individually, “Find&Replace” might be the application you are looking for.
Step 1: click “Replace” and you will see the below.
Step 2: replace the orignial value with the new one. Pretty easy.
How to insert a new row below the value?
Step 1: put the cursor down below the selected value. (Bob, A5), click right button and choose insert.
Step 2: choose “Entire row”
Learn More about How to fill blank cells with value left/right in Excel?
Don’t want to use formula? OK, I can still help you get what you want just by downloading a alternative office app named WPS Office. Just like Microsoft Office, WPS Office is also a office app that contains applications like Word, Excel, PPT and PDF but more flexible, convenient and handy. It also compatible with Mocrosoft Office files and WPS will only make things easier.
Importance: it is totally FREE!
Click and download now~
