Comments on: How to use AutoFill in Excel - all fill handle options

This post looks at the AutoFill Excel feature. You will learn how to fill down series of numbers, dates and other data, create and use custom lists in Excel. This article also lets you make sure you know everything about the fill handle, cause you may be surprised how powerful this tiny option is. Continue reading →

Comments page 3. Total comments: 158

  1. hi Maria
    thank u so much for ur helpful material...

  2. SUPPOSE I WANTS TO TYPE A IN A1, B IN A2, C IN A3 AND SO ON. I WANTS EXCEL TO AUTOMATIC FIX SERIAL NO BEFORE A TO Z. MEANS IF I TYPE A IN A1 IT AUTO FILL 1. A, SAME IN A2 IS SHOULD BE 2.B N SO ON

  3. Is there a way to have an excel sheet anticipate a value of a cell based on a list? For example, I am trying to do a large sheet of sending suggestions to a group. Often these suggestions are the same for the recipients and it would come from only a list of suggestions, nothing extra. I have used the data validation, but using the drop down is tedious. I want something where if I want to suggest to "go to the movie", as soon as I type "Go to..." it will want to fill in the rest of the value. I know if the value is listed above in the sheet, it will try to finish, but is it possible to have that list input in the background to do that in advance. It would cut down a lot more time in excel.

  4. Is there a way to get it to flash fill two boxes in the same row that are related? In my case, individuals have a code associate with them-- the codes need to be in a separate box, but it would be simpler for me to have it fill in box the name and code box when I input my data.

  5. when using vdb, how do i get it to increment the start and stop period by dragging the auto fill corner to the next cells. currently it just copies the data in the cell or cells. i have tried to select multiple cells and it isnt recognizing a pattern, any ideas?

  6. Hi,

    I want to ask you if is it possible to change the value of 50 cells set in the autoComplete option. I am facing with a large table and for me it will be useful to look up for more than 500 cells, of course without blanks in the respective column. I've tried with excel 2003, 2007 and 2010 but I have the same result (after "n" groups of 50 cells, I must type "n" characters in the next cell to return the value written in a cell above (I don't know if this works for more than 7-8 characters).

    Thanks a lot,

  7. Is there a way to set the default options of autofill to "copy cells"?
    Because the current default is "fill series" which I do not need for now.

  8. Hello,

    I am trying to create an auto fill list that will replicate slot designations for warehousing ie.

    CGA1
    CGA1
    CGA2
    CGA2
    CGA3
    CGA3
    CGA4
    CGA4
    etc.

    Thank You,
    Bruce

    1. What is your response to Bruce's query above? I have a similar situation whereby I need to number the detail lines of an a record and the pattern in a column is ,1,2,2,3,3,4,4,5,5,6,6,7,7 etc.

  9. I am not able to push a button twice in a row. How do I turn off this feature so that I can type words like letters, cooler or even hit the delete button back to back.

  10. thank you very much.
    very useful to me

  11. I would like to know if it is possible with a 2 column sheet, the first column has a date and I'd like the column to the right to auto populate exactly a year from that date. This is for calibrating gauges, so I'd like it to be something like Date calibrated Date due
    8/1/2016 8/1/2017

    where the date due is auto populated.
    Thank you.

    1. Hi Tina,

      You can use this formula:

      =DATE(YEAR(A2)+1, MONTH(A2), DAY(A2))

      Where A2 is the Date calibrated.

  12. Hi, I would like to know whether it is possible do the following in excel,

    changing the values of particular items in wherever it is repeating by changing the source data.
    Same as Invoice preparation, by auto filling the values from source file

  13. Do you know why the option "Enable fill handle and cell drag-and-drop" from the Advanced option doesn't stay active. Almost everyday I need to go back there and tick the box again and again. Annoying. Thanks for your help

  14. Hi there,

    I am looking to use some sort of autofill feature that operates conditionally. I have a list of employees, each coded with a different colour based on department (done automatically by way of vlookup). I fill in hours (if applicable) on that spreadsheet for all employees. I then have a separate sheet where I want to have ONLY employees who logged hours listed, and grouped by department (fill formatting mentioned above). I want it to fill in that second sheet automatically based on the data I entered for the first. What would be the best way to go about doing this?

  15. I am using MS Office 2010.Excel-2010 have no FLASH FILL option.I shall be grateful to you if you can suggest how flash fill can be done in excel-2010.

  16. Hi Stephanie,

    Having worked through a number of your samples here I've picked up a few tips that I wasn't aware of, however I was wondering if you could help me with a problem I'm running into. I'm not sure if it is related to this topic (I may have used the wrong search criteria to find a solution - if I did I still learned something new :-) and will try to find it elsewhere).
    I have a spreadsheet with a number of formulas in it, most of which are date related and are spread across a number of columns (which are not beside each other). I was wondering if there was a way to have these formula automatically appear on a new line each time a new record is added or is this always going to be a manual process.

    For example
    COLUMN L =IF(ISBLANK(K3),"N/A",+K3+70)
    COLUMN M =IF(ISBLANK(K3),"N/A",L3-TODAY())
    COLUMN N =IF(ISBLANK(K3),"N/A",+L3+42)
    COLUMN U =IF(ISBLANK(T3),"N/A",EDATE(S3,T3))
    COLUMN V =IF(U3="N/A","N/A",U3+42)
    COLUMN Y =IF(ISBLANK(X3),"N/A",X3+42)
    COLUMN AA =IF(ISBLANK(Z3),"N/A",Z3+42)

    Any help on this issue would be greatly appreciated

    Regards,
    Dan

  17. Hi,
    Im using Excel 2013. I need to enter months horizontally. Next to every date of the month I require my work day to be inserted. its a 4days on 2 days off rotation. is their any way I can auto fill that or a quicker way to do it? as follows..

    Shifts pattern (
    Early
    Early
    Day
    Day
    OFF
    OFF
    Late
    Late
    Mid
    Mid
    Off
    oFF)

    It should display as follows in Excel...
    Date shift date Shift
    02/05 L 02/06 E
    03/05 L 03/06 E
    04/05 M 04/06 D
    05/05 M 05/06 D
    06/05 OFF 06/06 OFF
    07/05 OFF 07/06 OFF
    ....

    Thank you
    Dulan

  18. Hi,
    I am using excel-2013 , i need to enter a particular value in front of particular name. the names are jumbled up and are repeated again and again. how can i enter the same particular value in front of same particular name every time even when the names are jumbled and repeated

    Thanks
    Mayank Agarwal

  19. Hi,
    I am working in an excel spreadsheet entering invoices. Earlier today when I was entering if i typed the apartment number "101 or 236 or etc" it would auto populate the Letter that corresponded with that unit (like A, B or C) to show that it is a 1, 2 or 3 bedroom. But for some reason it has stopped working. Can you please let me know how to fix and also explain how to do it so I can utilize this feature in another spreadsheet.

    Thanks,
    Stephanie

  20. I want auto fill number as
    001-2016
    002-2016
    003-2016

  21. Hye
    H r u?
    I am facing problem to run the flash fill in excel, Can you please help me.
    Thanks.

  22. What I want from drag fill is Series

    ex)
    1
    2
    3
    4

    However What I am getting is
    1
    1
    1
    1

    I have tried dragging 1 and 2 together,
    then I still get
    1
    1
    1
    1

    How do you fix this?

    I have excel 2013

    1. Hello Logan,

      Hard to suggest what could be the problem.

      I have tested successfully in Excel 2013.

      What mean "1 and 2 together"?

  23. Hi, I'm trying to Autofill a column of alphanumeric numbers. There's only one column with the numeric portion in the middle. such as NP1A, NP2A, NP3A, NP4A etc, with the numeric portion increasing by one every time. It just repeats it what I've done every time. It works if I take the A off the end, but I need it there. Please help.

  24. Hi Amanda,

    Please enter teh data in the following way:
    PG01-01 PG01-02 PG01-03
    PG01-02 PG01-03 PG01-04

    Then Excel will understand the logic and fill the series correctly.

  25. Hi, I am trying to Autofill a column of alphanumeric numbers. Column 1 is PG01-01, column 2 PG01-02, column 3 PG01-03 etc. When I select all 3 the cells and then click on the fill handle to fill the rest of the column, instead of continuing the series, it just repeats PG01-01, 02 and 03. Please help as I am getting really frustrated.

  26. Hi,
    i am generating a table with the formular below the "B" value is increasing by 5 that is B473,B478,B483,B488... when i use auto fill it only populates the table increasing the values by '1' i.e. B474, B475 ...instead of '5'. please how do i go about it. note that the table is picking values from another sheet called the value counters. Thanks in anticipation of your help
    ="SI "&'Value of Counters'!B473
    ="SI "&'Value of Counters'!B478
    ="SI "&'Value of Counters'!B483
    ="SI "&'Value of Counters'!B488

  27. If i want to generate a timemtable of a class in university. I have teachers names, subjects to be handle but i want the excel automatically fill the cells for a week of 6 working days each day having 6 hours of different subjets

  28. Nikki asked above:
    I am trying to use auto fill, however, I need to add a space. For instance,
    1402301 ---- 14 02301
    1402302 ---- 14 02302

    [Krishn] I can suggest a work around. Add some character in place of space which does not exist in the excel. E.g.
    14-!@-02301
    14-!@-02302
    14-!@-02303
    and then perform auto fill. It will give values like...
    14-!@-02304
    14-!@-02305
    14-!@-02306 and so on. Later perform Find & Replace. Find "-!@-" and replace with a space.

  29. Hi,

    This may be tough to get across in text but I have a summary sheet which reads data from other tabs with in the worksheet.

    When trying to use the fill handle, how do i autofill to allow for an increase tabs increments? My sheet tabs are labelled 1.01, 1.02, 1.03, 1.04, 1.05 and so on...

    Currently when i drag the cell it only makes a copy of the selected cells data.

    '1.01'!$A$2
    '1.01'!$A$2
    '1.01'!$A$2
    '1.01'!$A$2
    '1.01'!$A$2

    I would like to see the following on my summary sheet, which picks up data from cell A2 on sheet tabs 1.01 through to 1.05.

    '1.01'!$A$2
    '1.02'!$A$2
    '1.03'!$A$2
    '1.04'!$A$2
    '1.05'!$A$2

    I have tried flash filling and tried to create a custom list without any success.

    Thanks

  30. HI I'm trying to use the quick analysis tool but it is not appearing on the bottom right corner after selecting a table. Also i am not getting an autofill option. I am using a macbook and really dont know how to get around this

    1. Hello,

      I'm really sorry, we cannot help you with this issue. Please contact Micrososft Support Service.

  31. HI I'm trying to use the quick analysis tool but it is not appearing on the bottom right corner after selecting a table. Also i am not getting an autofill option. I am using a macbook and really dont know how to get around this.

    1. Hello,

      I'm really sorry, we cannot help you with this issue. Please contact Micrososft Support Service.

  32. Hi
    I have some data which is in the format of Data filter number of names is there i am wasting lot of time to choose the correct name (every name have a unique number) from the list. I want to know that any facility to get in simple way to get, i mean is there any shortcut to get the name when i am entering the number on the column? Please inform me.
    Thank you

  33. Hi Maria,

    A very informative post. I have been using Excel for some time now and was thinking if the Excel supports the following Auto Fill feature or not.

    Let us say I have some data in A2 (10), B2 (20) and I have used a formula in C2 to multiply A2 and B2 (10*20). Is it possible that when I enter new data in A3 and B3, the formula of C2 automatically gets filled in C3 and multiplies A3 and B3 dataset. I understand that I can do it by extending the cell or by double clicking, but I am looking for auto-population of data in C column as soon as I enter the data in A & B columns.

    It will be a great time saver if the user can see the formulae in action as the data gets entered without the user intervention.

    1. Hi Vipul,

      I'm really sorry, looks like this is not possible with the standard Excel options.

  34. Found out it was because I was in filter mode. Sorry.

  35. thank you.

  36. Whatever I try I cannot get excel to fill a series in a column. Whether the fields are formatted as text or number, whether I press Ctrl or not, it only copies the first cell. I've tried restarting excel, but to no avail. I'm using excel 2010. In excel 2003 I never had this problem.

  37. Hi,
    I'm having an autofill error that I'm hoping you can help me with.

    I have a very long nested IF statement formula that I am trying to apply to every row in my spreadsheet (hence, the autofill).

    My spreadsheet is for salary analysis. Basically, I am saying this:
    if Grade=3 AND New Salary is less than or equal to 35149, then Salary Range=Min
    if Grade=3 AND New Salary is between 35150 and 40554, then Salary Range=25%
    if Grade=3 AND New Salary is between 40555 and 51363, then Salary Range=Mid
    if Grade=3 AND New Salary is equal to or greater than 51364, then Salary Range=Max

    (The grade level and the salary amount being the logical test, and the Salary Range being the true value. If the first statement is false, then it will evaluate the second, and so on.)

    There are 8 different possible grades, with four salary range options for each grade, so a 32-statement formula. The grade possibilities are 3, 4, 5, 6, 7, A, B, C.

    When I enter the formula and drag it through my spreadsheet, it works just fine through all the numbered grades (3-7). When the formula hits the lettered-grades (A-C), it suddenly stops working. However, if I copy/paste the formula again on the first lettered-grade row, and drag it the rest of the way, it works just fine. So to me, it seems the autofill capability is having a hard time getting the transition from numbered-grades in the logical test statement, to lettered-grades in the logical test statement. Again, when I drag the formula through the grades separately, it works just fine. So the formula is correct... Hoping you guys can help?

    Thanks!

  38. Hi, hope you can help, I'm using excel for inventory purposes and my data is input with a hand scanner. The scanner gives me comma-delimited data which i have to sort using "text to columns". Regardless of what i have tried , including varieties of autofill, i cannot get excel to automate the text to columns feature, so i have to keep manually doing it. Is it possible to get excel to automatically do text to columns as i'm scanning the data in ?just repeat it on every row as it's entered? I'd be very grateful for any assistance, thank you, Fran

  39. I am trying to use auto fill, however, I need to add a space. For instance,
    1402301 ---- 14 02301
    1402302 ---- 14 02302

    1. Hello Nikki,

      Regrettably, we don’t know the way how to do this.

  40. Hi!
    I would like some help, please!
    I have a list with "code", "name", "adress", "contact", etc...
    In the same excel document (in another sheet) i want to create a biger spread sheet and i would like to have a collumn where i write the code, and the rest of the collumns autofill with the corresponding name, adress and contact information (of the previous spread sheet).
    Is there an "easy" way to do it?
    Thanks

  41. Hi Maria
    I Want excell sheet like software
    example
    i want enter some value specific cell like in A1 suppose 1234
    this value automatic fill in specific date on daily basis
    is it possible???

    Regards
    zulfi

  42. Are you able to help with a formula as the auto fill is not quite what I need. Im trying to split over 700 addresses into unit number, street number, street name and street type. Some address have for example 1/3 Burke and Will Road and then other might have 3 Will Rise Road.

    1. Hello, Carmen,

      For us to be able to help you better, please send a small sample table with your data in Excel and include the result you expect to get to support@ablebits.com

  43. excellent help rendered to me. thankyou.

  44. wonderful I cant imagine. The best help I received when I had requested to solve my problem regarding excel dragging of cells down word and so.

  45. wonderful I cant imagine

  46. Hi,
    I am using excel 2010, i am trying to put together a formula for when i populate a number in column b, column d populates with the name of the item in b. Is there any way to do this and how?
    Thanks,

    1. Hello, Leeroy,

      Please copy down column D в the following formula: =B1

      This should solve your task.

  47. Hi All,

    I am trying to create a spreadsheet to show sales of particular items. What I want to achieve is to automatically bring up the cost of an item if I select it from a drop down list. So for example, if I select Honey in column B, I want column C to automatically show £1.99, and something else in column D. Is this doable?

    Thank you!

  48. Hi, i was wondering if there was a specific "formula" for the autofill handle. I want to create a table to print a list of data (ie. months) if specific conditions are met (a1""), so that the second column will conduct a mathematical formula from another table.
    Is this possible?

    Thx.

    1. Hello, Andy,

      For us to be able to help you better, please send a small sample table with your data in Excel and include the result you expect to get to support@ablebits.com

  49. I am using excel 2010 and trying to use the flash fill function. Neither of the ways you listed above are working for me. When I right clock and drag my menu does not have flash fill at all; it is not even a faded option that's unclickable. It just is not there. When I try the other way, my options are only to copy cells, fill formatting only, or fill without formatting. This also makes my first two cells, the ones that I used to set the pattern, repeat instead of following the pattern. Help??

  50. I want to enter a date in column A1 and have it auto-fill the day of the week in B1 and so forth down the sheet. Is that possible. Thanks so much. Rick

Post a comment



Thank you for your comment!
When posting a question, please be very clear and concise. This will help us provide a quick and relevant solution to
your query. We cannot guarantee that we will answer every question, but we'll do our best :)