Is there a formula to auto fill down text?

Occasional Contributor

I have Client names in Column A. In column B, I have various projects that take up multiple cells below. The client name is only on the line of the first project but then is blank until it gets to the next client. 




Is there a way to auto fill down Client A until it gets to Client B, Client B until Client C, etc. without having to do this one client at a time?  I have 500 + clients I need to do this for.




15 Replies


That could be as



Select column A.

Press Ctrl+G to activate the Go To dialog.

Click Special...

Select Blanks, then click OK.

Enter the formula =A1 and confirm it with Ctrl+Enter to populate all selected cells.

@Hans Vogelaar 


Each of the steps seem to lead me in the right direction until I get to the last step of enter '=A1 and press enter.'  All of the blank cells are highlighted but the data is not filling.


I do have column headings in Row 5 so my client data starts in A6.  Rows 1 - 4 include a title/description of the report I am trying to create.


You should press Ctrl+Enter, as I wrote in my previous reply.

@Hans Vogelaar 

I get this error message



I went ahead an pressed ok, but all the blanks were filled in with a '0'.


Did you enter =A2 by accident? =A1 should work:



Left: before entering formula, right:after entering formula and pressing Ctrl+Enter. 

@Hans Vogelaar 


I did have =A1 in the cell.


My first client entered is in A6.  Would that impact the result I am getting?


I keep getting the error message and when I say ok, the blank cells are filled in with '0's still.


Thanks for your help!  I'm determined to get it figured out!


Hi @KimLabenz365 


If you'd like an automated solution my toolbar has a macro to do this automatically


Fill Down.gif


It's free to download here







My Linkedin Articles...



If cell A1 is blank, the formula cannot fill in a name. You should select a range from the first non-blank cell down.

After the blanks have been selected, look at the address box on the left hand side of the formula bar.

You'll see the address of the active cell.

The formula should refer to the cell above that. So if the active cell is A7, the formula should be



@KimLabenz365 You can also apply an IF function that can fill in the gaps as a helper column. Once you have the final result, just replace the original column as a text of the solution -- see example attached.



best response confirmed by KimLabenz365 (Occasional Contributor)

@Hans Vogelaar 

So I selected the range and did as you suggested.  All of the correct blank cells were highlighted.  I used the formula =A6.


Now, instead of '0' appearing in all the blanks, 'A6' is what appears.  


I tried the IF function in the helper column, as suggested by adversi.  The same thing happened and 'A6' is what appeared as a result.


Are there any settings I need to adjust for the cells?




Make sure that the number format is not set to Text and that 'Show Formulas' on the Formulas tab of the ribbon is not highlighted.

@Hans Vogelaar 


IT WORKED!!!  Thanks so much for sticking with me to help me get this figured out!


The issue was the number format was changing to text so I set it to general prior to going through the steps.