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 2. Total comments: 157

  1. When I try to use the autofill option to do copy the data but not the formatting, I can't get the right menu to come up. Even the graphic is wrong. Instead of being little blue squares, it shows what looks like a folder with a lightning bolt going through it. The menu offers options such as data bars, color, icon set. Nowhere does it give me an option to copy formatting but not data, or data but not formatting, etc. I've checked the Advanced Options, but the Enable Cell Drag and Drop option is checked, so that's not the problem. Help?

  2. why my 10 a turns into 10:00 AM
    can someone help? it is driving me crazy

  3. I want to auto fill down a series of numbers like 1789-19, 1790-19, 1791-19. When I auto fill the last number it just goes like 1791-20, 1791-21 and so on, but I want 1792-19 etc. Can I fill it so it creates a series where the number after the - does not change?

  4. Auto Fill Options Missing

    Kindly assist, when I select data on my worksheet, the autofill icon is missing. I have gone to Options -> Advanced and made sure the tick in the checkbox Enable fill handle and cell drag-and-drop is selected. However I still have no auto fill icon. I am using Office 365.

  5. Hi, when I start writing any word starting with Al it give directly the word Alternative, how I can remove it?

  6. ="JB01-"&ROW(C1)&"/TB1-"&ROW(C1)
    You can use row function for serial no 1,2,3..... rest should be as text.
    Kindly check... and past it as text

  7. Hi
    I have to autofill two seperate series.Is it possible?
    Example JB01-1/TB01-1
    Next Line JB01-2/TB01-2
    So I need to fill both the series..Kindly reply.

  8. Hello, I am interested in whether an autofile with functions can be created in the excel, that is, I have an IF function in which the cells in the other worksheet are located, and I have to make it so that every next line contains the cell number increased by 65.

    Example:
    =IF(marko!A2="","",marko!A2)
    =IF(marko!A67="","",marko!A67)
    =IF(marko!A132="","",marko!A132)
    ....

  9. Hi

    I am trying to use the weekly planner where it has to summarise in the Monthly plans in excel. Some what i received templates from various sources. But not exactly.

    When i select first week i should be able to enter the details(description) of it and for 2nd, 3rd, 4th week the dates should change at the same time the data which i updated for the first week should not appear there. so that i can plan/update for every week.

    In the Monthly sheet when i select particular month then the summary of all 4 weeks should appear there.

    Thanks you
    Babu

  10. Hi

    I trying to use the weekly planner where its summarise in Monthly plans in the excel. Some what i received template from various sources. But not exactly.

    When i select first week i should be able to enter the details(description) of it and for 2nd, 3rd, 4th week the dates should change at the same time the data which i updated for the first week should not appear there. so that i can plan for every week. In the Monthly sheet when i select particular month then the summary of all 4 weeks should appear.

    Thanks.

  11. Brhlwwe,,,
    I need ur help very soon. Can u help me???

    I click mouse on a cell, and there will be write yes,, wherever i click, will be yes word.

    i used macro, but its too late, cause, here need keyboard key.. but i want, when i press just mouse right button, and will be right just yes.. is it possible??

    plzzz help me... very soon if u can.

  12. Using Excel 2013. I have a spreadsheet that I copy and reuse with new, imported data which contains between 50,000 and 200,000 rows of time-evolution data. I perform simple calculations on the data, such as multiply by 1000 or divide by 60. In an adjacent column, I enter the pertinent calculation, select the cell, and double-click on the auto-fill square. The auto-fill will correctly fill all 200k cells in the column. Other data, same spreadsheet a few columns over has a different calculation performed on it. In this column, the auto-fill procedure will only work to about 50k of the 72k values and I have to manually drag the auto-fill square down to complete the procedure. Scrolling down to the bottom of the incomplete column, selecting a cell, and double-clicking on the auto-fill square does nothing. I have to manually scroll down to complete the action.

    Strange thing is that if I start with a new, empty spreadsheet and import the same data, auto-fill works fine. But, if I save it, delete the data and enter new data, the auto--fill is broken again. I don't want to do this because the graph formatting is very complex and time-consuming and I don't want to "reinvent the wheel" with every new set of data.

    Any thoughts? Thanks!

  13. How we insert date of excell which update every day automatically

    1. Gulmajeed:
      The TODAY() function will show the current date.
      Format the cell that contains the TODAY() function as Date. Right click on the cell and select Format Cells then choose the Date option from the list and choose the specific format you want to use.

  14. Autofill is working in all cells when I drag down with the thin crosshair. The only exception is checkboxes which I can't drag down to autofill. My options in advanced are selected accordingly so that is not the issue. Your assistance will be highly appreciated.

  15. Hello, I'm wondering if you could help me. I'm wanted to fill in a number in a ID column and then a Name associated with that ID number populate to the cell next to it. How can I go about doing this?

    GROWER NUMBER GROWER NAME
    98805 Turkey Trott

  16. I have changed computers and am now on windows 10, on my old windows 7
    I was able to start entering a name in a cell and if had been used before it will auto fill, but with windows 10 it does not and I cant find anywhere or any one to solve this problem as it is time consuming having to retype every name.

    Can you help me.

    Regards Colin

  17. Hi Dear.
    i want you help me how to set auto fill up the colors in excel 2010.
    Exsample:
    when i input the number in colunm B and the color auto fill in colunm C without seeing any datas just only the color. I wish you can help with this.
    Colunm B (number) and Colunm C (auto fill color)

  18. I want to add series number in column from 1 to 100 with formula, Could you please help me to share formula.

  19. Hello-How some spacified data from master excel sheet(not fixed) also auto exist into each separate respective particular or multi excel files without the efforts of picking up copy paste posting from the master sheet.Imtiyaz

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

  21. 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

  22. 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.

  23. 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.

  24. 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?

  25. 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,

  26. 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.

  27. 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.

  28. 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.

  29. thank you very much.
    very useful to me

  30. 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.

  31. 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

  32. 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

  33. 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?

  34. 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.

  35. 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

  36. 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

  37. 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

  38. 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

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

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

  41. 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"?

  42. 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.

  43. 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.

  44. 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.

  45. 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

  46. 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

  47. 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.

  48. 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

  49. 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.

  50. 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.

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 :)