• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course โ€“ Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member
  • Login

Excel Fill Value Down Rows based on Criteria

You are here: Home / Excel / Excel Fill Value Down Rows based on Criteria
Excel Fill Down IF
June 2, 2015 by Mynda Treacy

A few weeks ago one of our members, Kylie, sent me this question:

 
โ€œHow can I fill column E with the text โ€˜CURRENTโ€™, if the Position Title in column D matches the first instance of โ€˜CURRENTโ€™ in column E?โ€

See example data below; Kylie wants to fill the cells bordered in orange:

Excel Fill Down blank cells IF

Note: I had to desensitise Kylieโ€™s data and the first names that popped into my mind were inspired by the French Open. I hope Federer likes his new position as Business Customer Rep, or is that Bus Customer Repโ€ฆeither way itโ€™s going to be a big change for him ๐Ÿ™‚

Download the Workbook

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.
Please enter a valid email address.

Download the Excel Workbook. Note: This is a .xlsx file please ensure your browser doesn't change the file extension on download.

Watch the Video

Fill Down based on Criteria

Unfortunately There's no IF option on the Fill tool but it's easy enough with a combination of Go To Special and an IF formula:

  1. Select the cells in column E from the first row of your data to the bottom of your data e.g. E4:E4000, but donโ€™t select the whole of column E, you just want the rows containing data.
  2. Press CTRL+G to open the Go To dialog box > click the โ€˜Specialโ€™ button at the bottom:
  3. Go To Dialog box

  4. Select โ€˜Blanksโ€™ and click OK

Go To Special Dialog box

This will have selected all blank cells in your data range in column E

  1. In the formula bar enter this formula (assuming the first cell selected by Go To Special is cell E6, if not adjust cell references accordingly):
    =IF(D6=D5,E5,"")

Excel Fill Down blank cells IF formula

Letโ€™s translate the formula into English:

IF the Position Title in D6 is the same as the Position Title in D5, then insert the Current Flag from E5, otherwise leave the cell blank.
  1. Press CTRL+ENTER to enter the formula in the selected empty cells
  2. Excel Fill Down blank cells result

  3. Copy column E and Paste as Values to get rid of the formulas.

Note: if it was possible for Kylie's data to have the Position Title repeated for different employees you could make the formula an IF(AND... like so:

=IF(AND(D3=D2, A3=A2),E2,"")

This would also ensure the CURRENT flag was only copied down to the relevant employee.

Excel Fill Down IF
Mynda Treacy

Microsoft MVP logo

AUTHOR Mynda Treacy Co-Founder / Owner at My Online Training Hub

CIMA qualified Accountant with over 25 years experience in roles such as Global IT Financial Controller for investment banking firms Barclays Capital and NatWest Markets.

Mynda has been awarded Microsoft MVP status every year since 2014 for her expertise and contributions to educating people about Microsoft Excel.

Mynda teaches several courses here at MOTH including Excel Expert, Excel Dashboards, Power BI, Power Query and Power Pivot.

More Excel Posts

8 Excel in-built AI Tools

Built-in Excel AI Tools

Clean, analyze and visualize data with these Official Excel AI tools by Microsoft. Including formula writing, PivotTables, Charts and more.
excel date and time

Excel Date and Time

Everything you need to know about Excel date and time. Includes comprehensive workbook with every date and time function, plus PDF version.
10 mistakes to avoid when using excel formatting

10 Mistakes to Avoid With Excel Formatting

These are the Excel formatting habits that drive me crazy and what you should do instead to ensure you use formatting effectively.
linear regression

Excel Linear Regression

Excel linear regression is easy with the built-in tools. Use charts to plot linear regression or use the Data Analysis Toolpak.
speed up slow excel files

How to Improve Excel Performance

How to improve Excel performance and the various causes of slow Excel files so you can speed up Excel and avoid problems in future.
Securely Share Excel Files

Securely Share Excel Files

Securely share Excel files stored locally, on OneDrive or SharePoint. Prevent editing or downloading, specify who can open and edit the file.
excel check boxes

Interactive Excel Check Boxes

Excel check boxes are interactive elements you can link to formulas, charts, conditional formatting and more.
tips for working in multiple excel files

Hacks for Working in Multiple Excel Files

Awesome tips for navigating, arranging and working in multiple Excel files. Guaranteed to streamline your workflow and increase productivity.
chatgpt for excel

ChatGPT for Excel

Using ChatGPT for Excel can be hit and miss. Learn the best uses for ChatGPT to make your Excel life easier and what to avoid using it for.
excel templates

Where to Find Free Excel Templates

Where to find free Excel templates and how to create your own Excel templates. Using templates saves time and effort.


Category: Excel
Previous Post:pivot chartsPivot Charts
Next Post:Customize Default Excel WorkbookExcel custom blank workbook

Reader Interactions

Comments

  1. Adrian

    November 15, 2022 at 1:11 am

    Hello
    I want Excel to recognise a delivery profile given as a number and to automatically use this number to populate cells beyond a given date with labour hours. For example, I have running horizontally 1 – 420 representing 420 months (35 years). There will be 120 products delivered commencing month 1 at a rate of 8 per month (15 month delivery program). The products will be subject to various maintenance interventions (e.g. every 4 years ‘A’ type exam, every 7 years ‘B’ type exam, every 10 years C’ type exam). Each exam requires a different amount of man hours to complete. On separate horizontal rows I would like Excel to recognise 1) When the exam type commences (and its multiples of), 2) to show in the respective horizontal row for the exam type the man hours for each month accounting for the delivery profile (e.g. ‘A’ type exam commences start of Year 4, and for 15 months thereafter man hours are shown for each month. Thank you

    Reply
    • Mynda Treacy

      November 15, 2022 at 2:55 pm

      Hi Adrian, please post your question on our Excel forum where you can also upload a sample file and we can help you further.

      Reply
  2. charles Bolton

    July 13, 2022 at 5:58 am

    I want to create small subset of my main humongous data base using two zip codes. I want all the columns of data in the main dataset in the smaller subset files. is there an equivalent of a “Select IF (VAR=”94110”) Then Write to (FILE) command and have it execute for every time the condition is true?
    The Excel commands, I have looked at require me to define a range.

    Reply
    • Philip Treacy

      July 13, 2022 at 1:34 pm

      Hi Charles,

      You could use Power Query to extract the data you want and save that into a table/tables in a file/files.

      Without your data it’s difficult to give you a more complete answer. You cold start a topic on our forum and supply a sample file there, and we could give yo a better answer.

      Regards

      Phil

      Reply
  3. SRINIVASU AKKINENI

    December 23, 2020 at 7:28 pm

    Can we achieve the same using query editor?

    Reply
    • Mynda Treacy

      December 23, 2020 at 7:37 pm

      Yes. Please post your question and sample Excel file on our forum where we can help you further and our answers can also help others: https://www.myonlinetraininghub.com/excel-forum

      Reply
    • Ahmed Hossain

      May 29, 2021 at 5:46 am

      Hello,
      If values are changed in column “C” the formulas does not work.
      How it is linked to formula? there is no reference in formulas from column “C”
      would you please explain.

      thanks

      Reply
      • Mynda Treacy

        May 29, 2021 at 8:47 am

        Hi Ahmed, change the formula to reference column C instead of D. If you’re still stuck please post your question on our Excel forum where you can also upload a sample file and we can help you further.

        Reply
  4. Tony

    September 27, 2019 at 12:14 am

    Saved me 5 hours, literally. Thanks!

    Reply
    • Mynda Treacy

      September 27, 2019 at 8:32 am

      Wow, that’s huge! Glad we could help ๐Ÿ™‚

      Reply
  5. Ang

    April 20, 2018 at 8:39 am

    Hi Mynda, Would this formula be able to be re-worked to count the number of rows it fills with “CURRENT” (e.g. 139) and fill to a certain number (e.g.160)?

    Reply
    • Catalin Bombea

      April 21, 2018 at 4:20 pm

      Hi Angelique,
      Can you post sample data file on our forum? It will be easier to see what you’re after. (create a new topic after sign-up)
      Catalin

      Reply
  6. maggie

    June 16, 2015 at 3:09 pm

    just great

    Reply
    • Mynda Treacy

      June 16, 2015 at 3:16 pm

      ๐Ÿ™‚ Thanks, Maggie!

      Reply
  7. Purushottam

    June 4, 2015 at 5:32 pm

    Nice to check it on youTube. Thanks for sharing the knowledge.

    Reply
    • Mynda Treacy

      June 4, 2015 at 7:12 pm

      You’re welcome, Purushottam ๐Ÿ™‚

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Current ye@r *

Leave this field empty

Sidebar

More results...

Popular Content

  • 10 Common Excel Mistakes to Avoid
  • Top Excel Functions for Data Analysts
  • Secrets to Building Excel Dashboards in Less Than 15 Minutes
  • Pro Excel Formula Writing Tips
  • Hidden Excel Double-Click Shortcuts
  • Top 10 Intermediate Excel Functions
  • 5 Pro Excel Dashboard Design Tips
  • 5 Excel SUM Function Tricks
  • 239 Excel Keyboard Shortcuts

100 Excel Tips and Tricks eBook

Download Free Tips & Tricks

239 Excel Keyboard Shortcuts

Download Free PDF

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel Office Scripts
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook

Sign up to our newsletter and join over 400,000
others who learn Excel and Power BI with us.

 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate
  • Sponsor Our Newsletter

Support

  • Contact
  • Forum
  • Helpdesk – For Technical Issues

Copyright © 2023 ยท My Online Training Hub ยท All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.