Excel-Fill values down a column based on criteria

Here's an Excel conundrum. How can we fill column F with the text "Current" if the Deal entry in column E exactly matches the first instance of "Current" in column F?” In the illustration below, we need to only fill the cells indicated with the red borders.

Only fill the cells bordered in red

Fill Down based on Criteria

Unfortunately there is no conditional option on the Fill tool but it should be possible using a combination of Go To Special to find the blank cells in column F and an IF formula to make the entry.

1. Select the cells in column F from the first row of your data to the bottom of your data, you just want the rows containing data.


2. Click the Find & Select control in the Editing group on the extreme right of the Home tab and choose Go To Special from the drop down menu.


3. Click Blanks and then OK. This will select all the blank cells in your data range in column F.





Find the blank cells





In the formula bar enter the following formula (assuming the first cell selected by Go To Special is cell F2, if not adjust the cell references as required): =IF(E3=E2,F2,"") Translating the formula into plain English, "IF the text in E3 is the same as the text in E3, then enter the current text from F2, otherwise enter blank text into the cell."




Press CTRL+ENTER to enter the formula into the selected empty cells.



Entering formulas into the blank cells




Copy column F and Paste as Values to get rid of the formulas and replace with text if necessary.