Thursday, March 16, 2017

Fill blank cells in Excel with value in above cell

When we setup data in Excel, we generally leave blank if the same value repeating multiple times or we fetch data from other sources, data have some blank cells which should have values in the above cell. See below snapshot:


Raw Data

Now, we have to fill blank with the values in above cell e.g., Anu Gupta should be in Range(A2:A7), Delhi should be in Range(B2:B4) and so on.


For doing this, first of all we have to select all blank cells. We will do this step-by-step.


Step1. Select the range where you want to fill the data

Select the Range


Step2. Press F5 or Ctrl+G or Click on Home > Find & Select > Go to Special

Go to Special


Step3. You will see this pop-up window. Click on Special or Press Alt+S



Step4. You will this pop-up window, select Blank and click OK



Step5. Your data will look like this. Put this formula =AboveCell reference (e.g. =B2) and Press Ctrl+Enter


Your output is ready, now you can copy and paste value entire the range.

-----------------------------------------------------------------------------------------------------------------------------------------------

Download the file and practice on it : AutofillValues.xlsx

Thanks for learning, will come soon with new post.

Please give your valuable feedback in comments, if its helpful.

No comments:

Post a Comment