Question description:
This user has given permission to use the problem statement for this
blog.
The below data is in one column. How do I get the blank cells to Auto Fill from what is in the cell directly above it ?
ABO & Rh Blood Type
ABO & Rh Blood Type
ABO/Rh Blood Type
Varicella Zoster Antibody IgM
Varicella Zoster Antibody IgG
Varicella Zoster Antibody IgG
Varicella Zoster Antibody IgG
HbA1c
Solved by K. L. in 42 mins
This is the chat thread from the real Excelchat help session. It contains no private user
information.
Excelchat Expert
01/03/2018 - 06:39
Welcome to excel got it pro.
Excelchat Expert
01/03/2018 - 06:40
I'm here to help you today.
Excelchat Expert
01/03/2018 - 06:42
You can not use a formula to auto fill in the same column. It can be done using a helper column and IF formula.
Excelchat Expert
01/03/2018 - 06:45
Hello, you there?
User
01/03/2018 - 06:45
I remember doing this years ago by highlighting the column and then do a series of menu commands
Excelchat Expert
01/03/2018 - 06:47
Well, yes you can also do that.
Excelchat Expert
01/03/2018 - 06:47
This is by selecting the column, then clicking on the GoTo menu item.
Excelchat Expert
01/03/2018 - 06:49
To do it, follow the steps- let me know if you have confusion or fetch problem along the way. ..
Excelchat Expert
01/03/2018 - 06:49
1. Select the range that contains blank cells you need to fill
Excelchat Expert
01/03/2018 - 06:49
2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.
Excelchat Expert
01/03/2018 - 06:50
3. Click OK, and all of the blank cells have been selected. Then input the formula “=A2 ” into active cell A3 without changing the selection. ( I'm assuming A2 is the first cell having data and A3 is blank)
Excelchat Expert
01/03/2018 - 06:51
4. Press Ctrl + Enter, Excel will copy the respective formula to all blank cells
Excelchat Expert
01/03/2018 - 06:51
Does it make sense?
User
01/03/2018 - 06:54
In my case, I have all my data in Column F and the first cell with missing data is F50
Excelchat Expert
01/03/2018 - 06:55
okay, then select column F and in step 3, write F49.
Excelchat Expert
01/03/2018 - 06:57
still having problem?
User
01/03/2018 - 06:57
So in cell F50 am I typing =F49
Excelchat Expert
01/03/2018 - 06:58
and?
Excelchat Expert
01/03/2018 - 07:01
let me know if you are not able to do it yet.
User
01/03/2018 - 07:02
It fixed the one cell but not any others down 2000 rows
Excelchat Expert
01/03/2018 - 07:03
It seems that you might have made a mistake in following the steps. Here they are once again.
Excelchat Expert
01/03/2018 - 07:03
1. Select the range that contains blank cells you need to fill
Excelchat Expert
01/03/2018 - 07:03
2. Click Home > Find & Select > Go To Special…, and a Go To Special dialog box will appear, then check Blanks option.
Excelchat Expert
01/03/2018 - 07:04
3. Click OK, and all of the blank cells have been selected. Then input the formula F49 into active cell without changing the selection.
Excelchat Expert
01/03/2018 - 07:04
Now this is tricky, you might want to click on the sheet to type in the formula, don't do that. Just write from the keyboard F49.
Excelchat Expert
01/03/2018 - 07:05
=F49
Excelchat Expert
01/03/2018 - 07:06
This is very tricky, it is very common to miss the trick here, that why the help of an additional column and IF formula is easier.
User
01/03/2018 - 07:06
Okay, let me experiment with it and try to get it to work. Thanks for the help
Excelchat Expert
01/03/2018 - 07:07
Do you want me to stay while you give it a try?
User
01/03/2018 - 07:07
Okay I will try one more time
Excelchat Expert
01/03/2018 - 07:08
If not, then you can close the session. Otherwise I'm stuck here and won't be able to help others.
Excelchat Expert
01/03/2018 - 07:08
Sure.
Excelchat Expert
01/03/2018 - 07:12
any luck yet?
User
01/03/2018 - 07:13
Do I key F49 into the first blank cell or =F49 ? Then, do I hit the enter button before Control + ?
Excelchat Expert
01/03/2018 - 07:13
=F49, then control + Enter.
User
01/03/2018 - 07:16
Do I need to hold Down Control and + at the same time before I hit enter ?
Excelchat Expert
01/03/2018 - 07:17
Yup, that's the idea.
Excelchat Expert
01/03/2018 - 07:17
oh no, not control and +
Excelchat Expert
01/03/2018 - 07:17
control + enter means hold control and then press enter before releasing control.
Excelchat Expert
01/03/2018 - 07:18
in other word, press control and enter at the same time.
Excelchat Expert
01/03/2018 - 07:18
is it clear?
User
01/03/2018 - 07:21
I got it to work. Horay !!!
Excelchat Expert
01/03/2018 - 07:21
Congratulations
Excelchat Expert
01/03/2018 - 07:21
You have made it and I'm not a terribly teacher. :)
User
01/03/2018 - 07:21
Thanks very much
Excelchat Expert
01/03/2018 - 07:21
My pleasure, a 5 star would mean a lot.
Excelchat Expert
01/03/2018 - 07:22
To rate me, please click on end session button and after that you will be asked to rate.
Excelchat Expert
01/03/2018 - 07:22
Anything else?
This is the output file from the real Excelchat help session:
This is an example of the expert help you can get. It contains no private user
information.
