Comments on: How to enter the same data (formula) into all selected cells at a time

In this quick tip you will learn about 2 fast ways to enter the same formula or text into several Excel cells at a time. It is very useful if you need to insert the formula to all cells in a column or fill all blank cells in a table with the same value (e.g. "N/A"). Both tricks work in Microsoft Excel 2013, 2010, 2007 and lower. Continue reading →

Comments page 5. Total comments: 167

  1. That was very helpful.
    I am currently facing a problem where I have to add a row within rows. It's not a table. I have to copy down all the formulas from the above row to the newly created row. In the above row some cells have the formula and some has values without formulas. I just need to copy down the formulas.

    I select the entire row via shift+space. I press f5 to open a Go TO window, click on special and go to formula. Now i have those cells highlight which have formulas. I copy them and paste them in the newly created row but it does not work.

    Could you please help me with that.

  2. GW-564 GW-566 GW-564 GW-565 GW-564 GW-565

    how to remove double value in one row (i need GW-564 Value in one time )

  3. Hi,

    I am trying to Insert 10 rows(That contain information) in between 1200 lines that each have information across cells. Right now I have a Blank row in between each and having to click each blank row and insert copied cells and shift the rest of the cells down.Example below:

    Row 1(has information across cells)
    A..........................
    B................................
    C.....................................
    D..............................
    E.......................
    F....................
    G......................
    H..........................
    I..............................
    J....................................
    Row 2(Has information across cells)
    A.................
    B..............
    C.....................
    D......................
    E..........................
    F...............................
    G.............................
    H...............................
    I...............................
    J..............................
    Row 3.....................

  4. PLS HELP ALEX

  5. Hi
    actually i wanted to know is their any formula to type big word in two or three characters in excel fo eg. i wanted to type state bank of India again and again so can i assign a short word SBI to enter State bank of India ,

  6. I have an area code in one column and the rest of the number in a second column. I need both area code and number in the same column. How do I combine or merge the 2 columns?

  7. Hi Alex,
    I need your help
    i have a 4 column table that goes for maybe 60 rows. In the first row i have company names.

    what i want to do is (at the same worksheet in an empty area below this datas) to type a company name (just the first word) and i want from excel to give me the whole row which contains this company name

    is it possible? it would be very helpfull.
    thanks

  8. How to Copy single data

    a
    a
    a
    a
    b
    b
    b
    f
    f
    g
    g
    g
    g

    Like this
    a
    b
    f
    g

  9. I want to assign a name for every 100 lines of work to 10 different individuals, I am assigning work so how can that be done.

  10. T H A N K Y O U!!!!!!

  11. Hi Alex,
    I have one problem in excel where I want to paste different values selected times in next column or sheet. for example I want to paste "ABC" 3 times and "CDE" 17 times and the numbers are in next column.

    SR# # of copy
    ABC 3
    CDE 17
    THD 43
    HNU 22

    I can not use control+enter since the data is huge.

    Thank you very much in advance.

  12. I have an excel project with 4 worksheets, what I am trying to accomplish is sort of confusing for me to explain.

    My 4th worksheet is a combined list of data that is found on sheets 1 - 3.
    Sheets 1 - 3 contains data that is shared between one or both other sheets.

    The top row is blank on all 4 sheets with a filter for sorting.

    What I want to do is designate a series of rows with 3 columns each (4 rows by 3 columns) where if I enter data into "Row 1 Cell 10" the info is copied to the next blank row in column A on Worksheet 1.

    If I can get this done, I can easily make it work for the other sheets.

    I imagine this would need to be a type of context entry. Where the data would be cleared once I hit enter or something. Otherwise, it would only work once?

  13. Alexander,

    I am trying to create an inventory list in Microsoft Excel and I have all the numbers in column A, how do I put a letter/s in front of them (so I will know which vendor they are from) without having to manually do it? Thanks in advance for your help.

    1. Hi Shan,

      It is not very convenient to fulfill this task in Excel. Please add a Helper column, enter the following formula there:
      ="AB"&$A2

      Where "AB" is letter(s) you want to add, $A2 is the first cell with data in your column.

      Select the entire Helper column, press Ctrl + C, go to the 1st cell in column A, press Shift + F10 and then V to place the new variant over the old one.
      Then you can delete the Helper column.

      1. I tried adding a helper column to do this and it either deletes all my original content or it puts a letter v in the place of the first cell.

        The steps I took were first to move everything to column B and then put into column A the formula above, only I changed it to ="AB"&$B2.

        Then I selected column A, pressed Ctrl + C, went into the 1st column of column B, pressed Shift + F10 and then V and so maybe this is where I am messing up. I've tried pressing them all together and then separately or I am just not creating the helper column correctly any further suggestions?

  14. Hi Alexander

    I am trying to put the same words in 2 different boxes on 2 different spreadsheets. How do you do this?

  15. Hi -

    I have a huge set of data, and I'd like to insert data points into specific cells. For example, I'd like to enter iPhone into ever 7th cell.

    Please advise.

    1. Please clarify whether you want to change the content of each 7th cell or insert text before each 7th cell.

  16. Hi. Is there a way I can copy data from a column into a column with several cell spaces between them?

    Source Column:
    A1
    A2
    A3
    A4
    A5

    Cells I want to fill in:
    A1
    _
    _
    _
    _
    A6
    _
    _
    _
    _
    A11
    _
    _
    _
    _
    A16
    _
    _
    _
    _
    A21
    _
    _
    _
    _
    A26

    Thank you in advance =)

    1. Hello Leslie,

      Please insert the following formula into the Destination column and copy it down:
      =IF(MOD(ROW(),5)=1,INDIRECT("A"&ROW()),"-")

      Where "A" is the name of the original column.

      If in the sixth row you want to get the value from cell A2, not from A6, then use this formula:
      =IF(MOD(ROW(),5)=1,INDIRECT("A"&INT((ROW()+5)/5)),"-")

      To replace the formula with the values, please select the entire column, press Ctrl + C, then Shift + F10 and then V.

  17. Hi Alexander,

    I've created a excel sheet where we made it one floor plane (table).In which I put the numbering on the cell in floor plane like (1,2,3),somewhere down I've written the name of the cell.
    suppose If I am clicking the particuler number so it has to show the name of that cell which I've written down.can u help me on that.

    Thanks

  18. i want to insert all data in a format from internet dtaa base, how at a time i can paste those things , and if there will be same number in rows , how i will find those ......plz i want solution my dear

  19. Hi Alexander,

    I've created a spreadsheet for statistical purposes but have issues when trying to copy formulas from one sheet to another.

    I'm looking at data ranges for different elements with the Mean, Range and Standard Deviation. For each entry I'm using a formula like: =STDEV('XRF Edited'!H2:H14, then for the next element =STDEV('XRF Edited'!I2:I14. The only variable for that is changing is the column and preceding letter. I've tried the trusted drag and drop to copy the formulas but this will only copy whats been done.

    Are they any quick methods for copying these formulas, which take into consideration only the column letter changing each time? I've looked at the absolute and relative cell references but neither seem to fix the issue.

    Any advice or guidance you could offer would be greatly appreciated!!

    Best wishes

    Steve

  20. I have a list of 5,000 incoming students. Column A lists their student ID, which consists of three letters and four numbers (ex. kal0009). To make that into the student's email address, I need to add @auburn.edu to each one. Is there a way to do that (add the same text to columns that already have text) without doing it manually? I'm wondering if I could make Column B all @auburn.edu and then merge the two columns. Is that even possible, or is there another way? Thanks bunches!

    1. HELP....I am trying to also add a column to over 3,000, I want the column to all say the exact same thing. How were you able to add @auburn.edu to everyone? Did you have to manually do it?

  21. Hi, could you help me with a problem?

    I have in column 'A' 3000 entries. Some are repeated values like i350, i560,e900 etc which code for a specific name i.e. i350=apple, for example.

    So it looks like this:

    A B
    i350 Apple
    E900
    g560
    y700
    i350
    i350

    Column B is empty, So what I'm Doing at the moment is; using ctrl+f to find 'i350' (of which there will be 300ish scattered throughout column A) then pasting 'apple' into column B beside it. This is very time consuming and inefficient and is hurting my wrists a lot because I have 15000 to do. I would love to be able to just find say 'i350' and use some sort of function button to paste 'apple' in column B right beside all the i350s in column A. IS this possible?

    I could put them all alphabetically and just drag down copy and paste when they are all in a row but I need the values in column A to stay in the order they are in, so i can't just paste them in and undo it twice to get back to the same order either.

    Any help would be most appreciated!

    1. Hello Ronan,

      Here is the fastest way:
      - Create a lookup table, where column A contains the codes (i350, etc.) and column B – the words (Apple, etc.).
      - Download and install Merge Tables Wizard:
      https://www.ablebits.com/excel-lookup-tables/index.php

      - Select the main table, run the add-in, select the Lookup table.
      - Compare 2 tables by columns A.
      - Choose this action: Update column B in the main table with the values in column B from the Lookup table.
      - 15,000 entries with get updated in about 10-40 sec.
      If the words get changed in the future or if you add new code-words, simply update the Lookup Table and run the add-in again.

      1. Thanks a million alexander for your speedy and wonderful reply :)

  22. Any way to make me analyze or view only the column and rows that I have filled rest will be deleted.

    1. Hello Akshay,

      Sorry, I do not quite understand your task. Can you, please, explain in more detail?

  23. Hi

    Here is my Q.. i have an excel like this

    column A

    A
    -
    -
    -
    B
    -
    -
    -
    C

    is there any way I can fill the - with A and B respectively in one shot?

    after updating
    Column1
    A
    A
    A
    B
    B
    B
    C

  24. To copy/paste a couple of lines into multiple worksheets, without overwriting any data, I use the 'copy' & 'Insert Copied cells' option.
    The option is removed when I tab to the next worksheet so I have to use copy again. Is there any way to keep using the 'Insert Copied cells' option more than once without having to copy again?
    Thanks

    1. As far as I know, it is not possible to change this behavior of Excel.

  25. I have a massive dataset derived from genetic screening, and I need to isolate parts of it based on specific values. This is because I have received separate workbooks that I need to piece together. I know it is possible to use the "advanced filter" option with the formula ="=entry" to filter out the entries that I do not want, however I have so many entries to screen for that editing them one at a time will take me months. Is there a way to edit a column to include this formula without having to retype each entry, so that I can use it for advance filtering??

      1. kindly help me in it

        if column A of Book1 is having numbers 1 to 1oo (like 1 2 3 4 5 etc...)
        i want to put this data automatically in another excel sheet called book2 but as mentioned below
        A1 of book1 comes A1 of book2 (want three cell gap in every new data)
        A2 of book1 comes A5 of book2
        A3 of book1 comes A8 of book2 and etc...

      2. Hello Alexander

        Have you had a chance to look at my problem yet?

  26. Merged cells are of different size

  27. Hi,
    If i use CTRL+Enter the same data will be copied to the selected cells. I want merged cells to be auto numbered. Do you have any tips for that.

    1. Hello Mary Victoria,

      We do not have any tips for that, I am really sorry.

  28. please respond to the enquiry above "If I use CTRL+Enter it will erase the content of the cell. I need it to be done so the original content remains and the next content goes in front (to the left) of the original content of each selected cell."

    i am trying to add '0' to a list of telephone numbers in multiple rows in a column.

    Thanks

    1. Hi Louis,

      Sorry, I have not written the article yet. Anyway, this is no simple solution.

      You can create a helper column, e.g. column F, supposing that column E contains telephone numbers.

      To add zeros to all tel numbers, enter the following formula in cell F2:
      ="0"&E2

      If you need to add "0" only to some of the numbers that contain fewer than the specified number of characters, 8 characters in my example (including digits, dashes and any special symbols), use this formula:
      =if(len(E2)<8, "0"&E2, E2)

      Then copy the values from the Helper column to your main column in this way: select the entire column F, press Ctrl+C, select column D, press Shift+F10 and then V (this is a shortcut for Paste special - Values).

      After that remove the helper column.
      If you are looking for something different, you can send me a sample of your data at alexander.frolov@ablebits.com and I will try to help.

      1. Thank you so much :) this saved lot of time

      2. very simple and easy formula thanks dear

  29. thanks

  30. Mehnaz, David, thank you for your comments. I'm going to cover this task in my next tip this week. I will add a link here as soon as I publish it.

    1. i need a help to bring many excel sheet value one by one like if 01.10.2016 has columns namely(name ,process ,work item-done) then the next 02.10.2016 in that excel sheet values to another sheet without change in the source data using formula in excel itself if i not clear let me know i will be more specif about my require

  31. If I use CTRL+Enter it will erase the content of the cell. I need it to be done so the original content remains and the next content goes in front (to the left) of the original content of each selected cell.

    1. something like this:
      - if your main cell is: C4
      - then in the "cell C5", you can write this formula: =C4
      - and then copy the "cell C5" into all the below cells
      - the formula will change to something like this but they all show the C4's content:
      =C5
      =C6
      =C7
      ...

      1. @Milad ~ Oh my God, dude thank you SO MUCH! I wanted to solve this simple ass problem for SO long. I just want it to duplicate the damn number in the corresponding horizontal cells and NOBODY could give me an answer to this, haha! Thanks again! =D

    2. Hi David and jawwad
      a very simple way is to use the above cell content then you can easily copy the formula in all the belove cells, then they all get the same value of the first cell and whenever you change the first cell's value they all will change at the same time. TADA! ;)

    3. Yes i also having same problem pls anybody share the solution

  32. Very helpful page how we add /insert 2-3 more numbers in already
    Written numbs for e g 3215436232 is already in colum we want
    To add 92 before 3215436232 in whole coloum.

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