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 365 - 2007.

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

Table of contents

167 comments

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

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

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

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

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

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

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

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

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

  9. 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!

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

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

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

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

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

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

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

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

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

  19. Is it posible to enter data in "one" cell multiple time and that changed data to saves every time in another cell?
    In other words :
    1234 writen in A1 shows in C1, another input data 2345 A1 shows in C2 etc.

  20. Sir,
    Ihave 3 sheet in excel and in which all column and rows are contain formula and protected now i want to insert a row in 1st sheet and same will appear next two sheet with same formula and data how is it 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 :)