Sep
26

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.

Automate your daily tasks in Excel

Ultimate Suite for Microsoft Excel - a complete set of 20+ smart tools that will automate most of repetitive and error-prone tasks you perform daily:

  • Match and merge data from two worksheets.
  • Remove duplicates in one Excel table or between two lists.
  • Consolidate multiple workbooks into one.
  • Merge cells, columns and rows, combine data from duplicate rows into one.
  • And more.

Special offer only for our blog readers and only until next Friday!

Click to get a license of Ultimate Suite and save $45 off the regular price!

Download Now!
Free 20-day trial version, 32.14 MB

Knowing these simple techniques will save you a lot of time that you can spend on more enjoyable things.

Select all the cells where you want to enter the same data

Here are the quickest ways you can select cells:

Select the entire column

  • If your data are in a full-fledged Excel table, just click on any cell in the column and press Ctrl+Space.
  • If you have a simple range (when you click on any cell with the data, you don't see "Table Tools" tab in the top-right corner of the Excel ribbon):
    You'll see Table tools only if your data are formatted as Excel table

    Note: Unfortunately, simply pressing Ctrl+Space will select all the cells in the column, i.e. from C1 to C1048576, even if only cells C1-C100 contain data.

    Put the cursor to the first cell in the column (or the second one if your Table has headers), then press Shift+Ctrl+End to go to the end of your table, hold Shift and press the Left key repeatedly until only the needed column gets selected.

    This is the fastest way to select all the cells in the column, especially if the column contains several blank cells between the data.

Select the entire row

  • If your data are in a full-fledged Excel table, just click on any cell in the row and press Shift+Space.
  • If you have a simple range, click on the last cell in the row, then press Ctrl+Home.

Select several cells

Hold Ctrl and left-click on all cells that you want to fill with data.

Select the whole table

Click on any cell in your table and then press Ctrl+A.

Select all cells in a worksheet

Press Ctrl+A, then Ctrl+A again.

Select blank cells within a certain area (row, column, table)

Select the area you need (see below), e.g. the whole column.
Select the column that contains blank cells

Press F5 to bring up the "Go To" dialog and click on the Special… button in that dialog.
Click the Special button in the Go To dialog

Select the "Blanks" radio button in the "Go To special" window and click OK.
Select the Blanks radio button in the 'Go To special' window

You will get back to Excel and see that only blank cells in the selected area are highlighted. That's right; it is faster to select 3 blank cells by clicking on them with your mouse cursor. But what if you have more than 300 blanks randomly distributed between 10000 cells :)?

The fastest way to insert a formula into the entire column

You have a large table and you want to add a new column with some formula. For example, you get a list of links (new backlinks to www.ablebits.com :) ) and you want to extract the domain names from these links for further work.
Extract a part of a value using formulas in the next column

1. Convert your range to an Excel table. Select any cell within your data range and press Ctrl+T to bring the "Create Table" dialog (the shortcut is Ctrl+L in Excel 2003). If your data have column titles, make sure the checkbox "My table has headers" is selected. Usually Excel recognizes your table headers automatically, if not, check this checkbox manually.
Make sure your table headers are identified

2. Insert a new column into your table. It is a lot easier to add a new column to a table than to a simple data range. Just click on any cell in the column next to where you want to add a new one and choose Insert > Table Column to the Right ( or "Table Column to the Left").
It is easy to insert a table column

3. Name the newly added column.

4. Enter your formula in the first cell of the new column. I'll use the following formula for extracting domain names in my example: =MID(c2,FIND(":",c2,"4")+3,FIND("/",c2,9)-FIND(":",c2,"4")-3)
Enter your formula into the first cell

5. Press Enter. Voila! Excel automatically fills all blank cells down your column with the same formula
Excel automatically fills in the column cells with your formula

If you want to switch back from a table to a simple range for some reason (I don't know any :) ), select any cell in your table, then press the "Convert to range" button on the Design tab.
Convert your table back to range

You can apply this tip only if all cells in the column are blank, so the best way is to add new columns. The next tip is universal.

Insert the same data into multiple cells using Ctrl+Enter

Select the cells that you want to fill with the same data in your Excel worksheet. Please see the tips above for quick data selection.

Say, we have a table with a list of our customers (this is a fake list, of course :) ). There is a column listing the websites from which our customers come. We want to fill the blank cells with "_unknown_" to make filtering easier in the future:
We want to enter Unknown into blank cells

1. Select all the blank cells in a column.
Quickly select all blanks in a column

2. Press F2 to edit the last selected cell and type some data: it can be text, a number, or a formula (e.g. "_unknown_")
Enter the data into the last selected cell

3. Press Ctrl+Enter instead of Enter. All the selected cells will be filled with the data that you typed.
Have all cells filled in with the entered value

If you know more tricks that speed up data input, please share them in the comments. I'll be happy to add them with your authorship to this article.

25 Responses to "How to enter the same data (formula) into all selected cells at a time"

  1. Mehnaz says:

    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.

  2. David says:

    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.

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

  4. Louis says:

    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

    • Alexander says:

      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.

  5. Mary Victoria says:

    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.

  6. Mary Victoria says:

    Merged cells are of different size

  7. Vince says:

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

  8. John says:

    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

  9. kishor says:

    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

  10. Akshay says:

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

  11. Ronan says:

    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!

    • Alexander says:

      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:
      http://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.

  12. Kate says:

    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!

  13. Steve says:

    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

  14. pratap says:

    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

Post a comment



Ultimate Suite for Excel Professionals
 
 
The best spent money on software I've ever spent!
Patrick Raugh
Ablebits is a fantastic product - easy to use and so efficient.
Debra Celmer
Excel is at its best now
Annie C.
I don't know how to thank you enough for your Excel add-ins
Jennifer Morningstar
Anybody who experiences it, is bound to love it!
Kumar Nepa
It's the best $100 we've ever spent!
Mike Cavanagh
I love the program, and I can't imagine using Excel without it!
Robert Madsen
One word… WOW!
Dave Brown
Love the products!
David Johnston
It is like having an expert at my shoulder helping me…
Linda Shakespeare
Your software really helps make my job easier
Jeannie C.
Thanks for a terrific product that is worth every single cent!
Dianne Young
I love your product
Brad Gibson
Awesome!!!
Sheila Blanchard