Home » » Excel : Fill the Blank Cells with Data from Rows above/Below or with Columns

Excel : Fill the Blank Cells with Data from Rows above/Below or with Columns




Objective is to fill all the blank cells in a column with the Data from the above cell. Below images can explain it in a better way:


To solve this problem there can be many ways, hardest and very time consuming being copying and pasting data manually specially when you're dealing with large data. We can write 'n' number of formula's to solve this, but I am going to tell a very simple and quick method of doing it.
Here we go,
  • First select all the columns containing your data
  • Press Ctrl+G or F5 this will open Go To dialogue box



Press Special... and a new dialogue box will pop out



This will select all the blank cell in your data and Type "=" and then hit up arrow for example A4: =A3



Press Cltr+Enter to input that formula into all currently selected blank cells.
And we are done in a flash.





Alternatively::
  • So you can enter text, numbers, formulas pointing left/right etc - and by varying whether they are absolute (eg =$A$3) or relative (eg =A3) you can be quite flexible with your output.
  • function, and for variety sake : Control H, find and replace, leave find field null, enter integer into replace (1). Insert column, if (A2=1, A1, A2), fill formula down to last record. Copy column B, paste values onto column A

0 comments:

Post a Comment

Like us on Facebook
Follow us on Twitter
Recommend us on Google Plus