+ Reply to Thread
Results 1 to 3 of 3

fill a range with formula, hundreds of rows down

  1. #1
    Registered User
    Join Date
    04-05-2020
    Location
    PA
    MS-Off Ver
    O365
    Posts
    5

    fill a range with formula, hundreds of rows down

    Hi. I'd like to apply the =LOWER() function to a range of values in order to convert them from mixed case to all lower case. The values exist in column A. My thinking is to create empty column B, and apply the =LOWER(A) in column B. This works easily in cell B1 but I'd like to avoid manually 'dragging' the formula downward to bottom of the range of source data in column A. Is there any way to automate applying the formula in column B instead of dragging the formula in column B downward all the way to the bottom? (maybe based on the existence of values in in Column A?) Thank you in advance.

  2. #2
    Forum Moderator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    Pro 2019
    Posts
    16,170

    Re: fill a range with formula, hundreds of rows down

    You can convert your data range into an Excel Table. Then it will apply that formula to every row of the table, and automatically extend it if you add more rows.

    I'm not sure I understand your aversion to manually filling it down. Put your formula in B1. Hit CTRL-END to go to the last cell. Use the left arrow to position to column B. Hit CTRL-SHIFT-HOME to select columns A and B. While continuing to hold SHIFT, use the right arrow to remove column A from the selection. Hit CTRL-d to fill the formula down.
    Making the world a better place one fret at a time | | |會 |會 |會 |會 | |:| | |會 |會
    Please read the rules
    If someone helped you, click on the star icon at the bottom of their post
    If your problem is solved, go to Thread Tools and select Mark This Thread Solved
    Don't attach a screenshot--just attach your Excel file! It's easier and will let us experiment with your data, formulas, and code.

  3. #3
    Registered User
    Join Date
    04-05-2020
    Location
    PA
    MS-Off Ver
    O365
    Posts
    5

    Re: fill a range with formula, hundreds of rows down

    Thank you for your advice. This worked great!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 14
    Last Post: 03-22-2020, 07:24 PM
  2. Need help adding 00 and inserting it in hundreds of rows
    By excelstone in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 05-24-2016, 06:20 PM
  3. Need help adding 00 and inserting it in hundreds of rows
    By excelstone in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-24-2016, 05:36 PM
  4. Creating a macro to generate a list for hundreds of rows
    By kcgojnur in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 11-22-2015, 11:48 AM
  5. how to apply a formula to hundreds of rows
    By Malkey in forum Excel General
    Replies: 11
    Last Post: 06-10-2012, 10:35 AM
  6. [SOLVED] Remove hundreds of empty rows
    By drews in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 11-22-2011, 04:29 PM
  7. Hide Hundreds of Rows
    By mavsman4457 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 04-14-2008, 12:53 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1