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

  1. Hello, I have to add one line (5 cells A-E) into many Excel files (150+).
    Is there a way to insert this line "at the end"/after the last entry in the A-column in all of these files simultaneously?
    Thanks and best
    Alexander

  2. Good day, I had an column for address info E3:E7 I need that info to display in different rows example: b16, b27 etc what will the formula be for this?

    1. Hello!
      I hope you have studied the recommendations in the above tutorial. You can also use the "=" operator to refer to a cell. Describe in detail what problem you have, and I will try to help you

  3. Hi,
    Can someone guide me in this.

    I have my required data in cell A5:A10, and i want to repeat the same data as many times as mentioned in cell A1.How can i do that.

    Example... Suppose I have 3 written in A1 cell, then my data should be data should be copied and pasted 3 times.

    1. Hello Raj!
      Automatically 3 times to copy and paste data using VBA. But even in this case, you must indicate where exactly the data should be inserted. The program cannot know where exactly you want to insert data 3 times.

  4. Hi All,

    I need help on below task

    Column 1 Column 2

    204 A
    204 A
    204
    204
    204 A
    205 B
    205
    205 B
    205 B
    206
    206
    206 C
    206 C
    206
    207 D

    I want to fill all blank cell with slimier data like all against 204 need "A" and all against 205 need "B" and so on

    1. Hello!
      If I understand your task correctly, please try the following formula:

      =MID("ABCDEFGHIJKLMNOPQRSTUVWXYZ",A1-203,1)

      I hope this will help, otherwise please do not hesitate to contact me anytime.

  5. Please i need help.
    i need to copy line 1 to line 2, line 3 to line 4 and so on. i have 2,000 lines. is there a short cut or formula to do it. thanks
    1 I.1.001
    2
    3 I.2.001
    4
    5 I.3.001
    6
    7 I.3.002
    8
    9 I.4.001
    10
    11 I.4.002
    12
    13 I.5.002
    14
    15 I.6.001
    16
    17 I.7.001
    18
    19 I.7.002
    20

  6. how to make a data enter in one cell and in order (number) form?
    eg.
    1. baywalk
    2. pavement
    3. porch

  7. hi,
    Is there a formula that I enter the entries in one general ledger and the particular entries be also added in another ledger.
    like in a general ledger I make many entries of cash coming in and out but I want all the entries against Karachi knitting be automatically posted to a special Karachi knitting ledger...?

    1. Hi. I wanted to know if this can be done too. Have you found a way to do this?

  8. i have a excel sheet containing following data i want to all column repeat as per no. of quantity and Amount should be divided by no. of quantity.

    Sr. No. Item Name Item Code Qty Amount
    1 Chair 110002 35 35000
    2 Table 110003 500 500000
    3 Computer 500010 45 1500000

  9. Thanks for the awesome help/info. As much as I know about Adobe InDesign, Photoshop and Illustrator, I know very little about Excel.

    All the best!

    Scott

  10. I do not want to reprogram the op system by entering the code RTf&86hhk//%"/I2873298j&/%¤w80+ in three different crypt modes in the register editor, divide that with my email adress and run it through my coffee brewer mixed with a copper penny and put it all in my shoe, then spit three times over my left shoulder while sharing the results of all this with everyone in else the whole world who happens to be on line and then finally plant a magic bean outside my bedroom window and slowly watch as it grows into a beautiful magic horse with wings in order to make this work.

    All I want to do is to copy the content of one Excel cell and paste it into two (or more) other cells of my choice, and seriously; how hard should it be?

    1. Mark the cell/value
    2. Copy
    3. Mark the cells you want to paste to
    4. Paste

    Something like that maybe?
    Well forget it Jake, it's Microsoft...

  11. hii , i need to add a date in all cells of column C , and column c already contains some data which is not identical in all cells and i dont want to delete the data which is already in there.

  12. Thanks

  13. Is there any one to help me with some excel formulas

  14. Hello sir,
    I Have a Question to you
    There is data in C1= Aman, C2= Roshan, C3= 10litter, C9= 1 Bike
    i want that all data gose in other colaum A1 which is "Aman,Roshan,10litter, 1Bike " Is it Possible

    1. Aman:
      I would use the CONCATENATE function like this:
      In A1 enter =CONCATENATE(C1,",",C2,",",C3,",",C9)

  15. Thank you so much.

  16. CAN YOU PLS HELP ME TO CREATE A FORMULA TO DISPLAY THE FINAL STATUS OF MY LOG

    A1 B1 C1 D1

    C B A A

    I WANT THE LATEST VALUE A1, B1 OR C1 TO BE REFLECT IN D1 OR WHATEVER IS AVAILABLE IN THE TO MAKE AUTOMATIC UPDATE.

    THANK YOU

  17. Hi All,

    I need help on below task

    Column 1 Column 2

    204 A
    204 A
    204
    204
    204 A
    205 B
    205
    205 B
    205 B
    206
    206
    206 C
    206 C
    206
    207 D

    I want to fill all blank cell with slimier data like all against 204 need "A" and all against 205 need "B" and so on

    Thank in Advance

  18. is there a way to enter same data in 2 different cells that are not in the same column? I have a service contract with guest counts that i would like the data to auto populate on the 2nd cell to minimize errors or forgetting to change the data. Also this data is associated with a formula to calculate total $ amount per guest. example below: I would like to have D20 to auto fill when data is input on I8. J20 have a formula =Sum(I8*H20).

    I'm trying to screen shot the data however it is not allowing me to do so. Please email me thanks

  19. Trivendra:
    To concatenate a number to another number and retain the number format so that you can work with the final number as a number and not text, concatenate the cells like this:
    Where 91 is in A1 and 8889347606 is in B1 enter in C1 =Value(A1&B1)
    Be sure A1, B1 and C1 are formatted as Number or General.

  20. Hi, please solve my quiry
    if we have a list of numbers in excel as below and i would like to add 91 before every number in excel in a huge data then how can i add 91 before every number in one time.

    8889347606
    7042210599
    8527975359
    7992397638
    7053961291
    9015341276
    7004744844
    7255025669
    8979771294
    9140655234

    1. Hi, please solve my quiry
      if we have a list of numbers in excel as below and i would like to add 91 before every number in excel in a huge data then how can i add 91 before every number in one time.

      8889347606
      7042210599
      8527975359
      7992397638
      7053961291
      9015341276
      7004744844
      7255025669
      8979771294
      9140655234

  21. Please does anyone has report card template for primary and high schools? I've tried all these procedures I saw above without any suitable result. I need to fill in single formula for 50 empty columns, but it failed to go through, help me with simplest procedure.

  22. Against one part no. I hav 4 rows & 8 coloumn. In first 4 rows i hav entered formulas.

    Now i have to copy these 4 rows containg all col. Against all 1200 part nos.

    Please tell how its possible in short cut

    Thanks in advance

  23. I have a data as
    a b c d

    I want same in next sheet as under (as link via drudging way)
    a
    b
    c
    d

    1. Dear Zahoor,

      Please copy your data & Special paste as transform.

  24. my question is i have a column which data havelike this
    a
    b
    c
    d
    and i want result like this
    a
    a
    a
    b
    b
    b
    c
    c
    c

  25. excel have

    name
    1.abcd
    2.bbcd
    3.cccd

    it need change into

    name
    1.abcd
    name
    2.bbcd
    name
    3. cccd

    how can i applied each raws in excel in betweeneach names

    1. Hello,

      Please try to solve your task with the “Create Cards” tool which is a part of our Ultimate Suite for Excel. You can download and install its fully functional 14-day trial version using this direct link.
      After installation you’ll find Create Cards in the Transform section under the Ablebits Tools tab in the Excel Ribbon. To get the result you need, please run the add-in and choose the following options in the add-in dialog box:

      1. Set 1 in the “Number of columns” field;
      2. Uncheck the “By empty row” box;
      3. Check the “Add header” box;
      Then press the Create button. Go to the resulting worksheet and select all the data in the sheet. Run the add-in again and select the following options:
      4. Set 1 in the “Number of columns” field;
      5. Uncheck the “By empty row” and “Add header” boxes;
      6. Press “Create”.

      Hope this will help you.

  26. Hi,

    I have a data which i want to paste it for multiple times, Data includes with 250 employee payroll Date, Emp Name, Department, No of Days worked - the entire data should be pasted for no of days in a month & excluding date column. Is there any specific formula to come out of this please help.

  27. Hi,

    Is there any way to have the number of person in every 30 minute interval just by referring to the staff schedule? To explain further, I have a date of staff work schedule :

    00:30-09:30
    01:30-10:30
    02:00-11:00
    02:00-11:00
    11:00-20:00
    12:00-21:00
    10:30-19:30
    12:00-21:00
    17:00-02:00

    and so on...

    I need to get the number of staff per 30 minute interval,

    00:00
    00:30
    01:00
    01:30
    02:00
    02:30
    03:00
    03:30
    04:00
    04:30
    05:00
    05:30
    06:00
    06:30
    07:00
    07:30
    08:00
    08:30
    09:00
    09:30
    10:00
    10:30
    11:00
    11:30
    12:00
    12:30
    13:00
    13:30
    14:00
    14:30
    15:00
    15:30
    16:00
    16:30
    17:00
    17:30
    18:00
    18:30
    19:00
    19:30
    20:00
    20:30
    21:00
    21:30
    22:00
    22:30
    23:00
    23:30

    Can you please help?

  28. 310399.166 -79X215-11-1 A001 1.0 EN 50306-2 300V M 1.0mm² (5303931) YE B1 A

    310399.166 -XK65-X342/188 A001 1.0 EN 50306-2 300V M 1.0mm² (5303931) YE B1 A

    I wanna to cut this cell -XK65-X342/188 and paste in the first row like this way

    (310399.166 -79X215-11-1 -XK65-X342/188 A001 1.0 EN 50306-2 300V M 1.0mm² (5303931) YE B1 A)

      1. step 1: select the data
        step 2: go to data then text to columns
        step 3: go to fixed length &cut as required

  29. hey,
    I have excel sheet each 2 rows have the same data unless one cell, I wanna cut this cell and paste it in the first rows .
    unforunatlly I can't upload an example.
    thanks

  30. I am working on a database and want to put back to back same dates in column A. Example
    Monday, August 21, 2017
    Monday, August 21, 2017

    For the entire year. What is the best way to accomplish this?

    1. I figured it out v-lookup on a separate sheet with two calendar years in back to back rolls.

  31. hi, im creating a template that contains form that needs to be answered and then the next sheet will prepare the print page.

    everything goes smoothly, including name address and contact informations. when i input them on the template page and it will appear on the print page.
    i use the =REPT('input page'!J9,1) (sample) formula

    the only problem is the date: every time i input the date, it will not appear exactly as it is formatted. (by the day i use Excel for Windows Date Picker

  32. I have a data sheet which contains multiple counts of data which were entered as numeric values e.g. A1 has a numeric value of 230 which must each be entered individually 230 times before the next cell A2 with a numeric of 30 times.
    that datasheet has 197 rows filled with data amounting to 2058 numeric values.
    How can I duplicate each one to their numeric values without using those complex excel formulae?

  33. I have a worksheet of approx 2000 lines just for one distribution center. I have to take the data and split into two rows one dc to show 70% and the other 30 however the data stays the same just the percent changes . Is it a pivot table.

  34. I have a worksheet of approx 2000 just for one distribution center. I have to take the data and split into two rows one dc 70% and the other 30 however the data stays the same just the percent stays . Is it a pivot table.

  35. Hi, I have an excel worksheet we use for daily retail business paperwork. It has 3 sheets with Daily Toatals, monthly Totals and Yearly Totals. I have to input data in Daily first, then copy and paste it into Monthly and yearly updates by simple formulas. I don't have extensive knowledge of excel, let alone Visual Basic. I would like for the Monthly sheet to update itself as it recognize the date on the daily sheet. Searched it a lot online but couldn't find anything other than some VB examples which went straight above my head. Can you help me. I appreciate that

  36. I have a huge dataset and I am doing content-mapping. I want to know if I can insert a particular data in all the columns which has the same last word in its left column.

  37. Hi Alex,

    I have a data in multiple Columns, Eg - A coloumn I have data like 54% in B column data like 60%, How can I add the both values in single colums 54%,60% in D column.

    Please help me on this.

  38. Seems like Alexander sucks! He still hasn't answered the question. What a loser!!

  39. Hi,

    I am stuck in a problem where I have 36 records and another set of 227 records. I need to paste one each of 36 against 227 records i.e. my total rows will be 227*36 = 8172 rows. I want to know a faster way to get the data sets ready

    Below Example shows how the records will be visible:

    Column1 Column2

    Mumbai 123
    Mumbai 234
    Mumbai 345
    Mumbai 456
    Chennai 123
    Chennai 234 and so on

  40. HAI SIR
    I HAVE A DOUBT IN EXEL..

    IS THERE ANY WAY TO COPY AND PASTE THE SEVERAL CELL AT A AME TYM

  41. I came to this post while looking for a way to copy a range of cells with formula to another range of the same size.
    My solution was to Replace = with xx= and then copy the text freely from place to place. Then when ready with corrections (just needed to change the name of the sheet references) I did the opposite transformation and replaced the xx= with = and voila!

    I like your site - had come to it many times in search of tricks for faster work with Excel.
    Thank you for your wonderful work!
    Hope my little tip can enrich your content!

  42. Apple computer, using Mac2011 Excel.
    I have a column of dates, more than 4,000 cells, that I need to insert just the month and year.
    With just the month and year I can create a pivot table to compare September 2015 with September 2016.
    Is there an easy quick way to insert the month and year into these cells so the pivot table just sees or uses the month and year, not the actual day of the month?

  43. I just need a simple formula (value in one column subtracted from the value in another column) formula is really easy (=D2-E2) however your instructions didn't work. I entered the formula but Excel did not automatically fill the whole column. What is missing?

  44. sir i want to enter the data of the poultry farm in excel and i want to know how can i put the data Eg for column A 7001 and this will same for ten rows and then 7002 for ten rows in the same column and so on to 8000. i need to know any formula or any shortcut my which i can enter the same data in a column for each ten rows

    plz plz plz reply

  45. Ok - so I'm trying to add the same date to a column of data in Excel 2016 on a mac.

    I've tried the fill handle and the control+enter methods and both end up adding one year to the following cell.

    8/8/2016
    8/8/2017
    8/8/2018....

    For 300 cells.

    I tried changing the format of the cells to 'text'. Didn't help.

    Any thoughts?

    Thanks.

  46. How we set the Last number having unique customer id in new column.

    Customer Id Transaction Id Set New Id (Last)
    S0K3DFD4356FGGHSDGHGD4566 A3453501
    S0K3DFD4356FGGHSDGHGD4566 A3453502
    S0K3DFD4356FGGHSDGHGD4566 A3458501
    S0K3DFD4356FGGHSDGHGD4566 A3453602
    S0K3DFD4356FGGHSDGHGD4568 A3453505
    S0K3DFD4356FGGHSDGHGD4568 A3453505
    S0K3DFD4356FGGHSDGHGD4568 A3451801
    S0K3DFD4356FGDESDGHGD4565 A3453702
    S0K3DFD4356FGDESDGHGD4565 A3453104
    S0K3DFD4356FGDESDGHGD4565 A3453111
    S0K3DFD4356FGDESDGHGD4565 A3453187

  47. I have a single column of 1000 rows containing numbers like given below

    120
    62
    32
    48
    56
    45
    29
    22
    ..
    up to 1000 rows

    I want excel to sort this column in 4 rows as under
    120 62 32 48
    56 45 29 22

    Please Help

  48. My concern is, in excel how do I copy simultaneously a data on a cell to another cell or cells of the same sheet or maybe on another sheet/s?

    Ex.on Sheet1: Range A1 = Rob

    I need "Rob" to appear simultaneously in Sheet1 on range of D10, or to appear simultaneously in Sheet2 on range H5, or to anywhere on desired sheet/s. How to do these?

    Sincerely,

    Rob

  49. Hi ,

    I have one excel data sheet i need a drop down list,for 50 No. of cell, where in if i type one alphabet the supporting words should appear in the list which are linked to it,and also it should allow to type something if it is not in the list, my list is will be common, any change shall be updated

    Pls do help to write formula, i tried with drop down list,but to write for 50 different cells it is time consuming
    Regards
    KK

  50. Dear all,
    could you please assist for me, i am using for two different name excel files, now i will do enter the data to 1st file means parallel automatically upload 2st file. (expected some columns only). How if can any possible.

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