by 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):
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.
Press F5 to bring up the "Go To" dialog and click on the Special… button in that dialog.
Select the "Blanks" radio button in the "Go To special" window and click OK.
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.
- 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.
- 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").
- Name the newly added column.
- 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)
- Press Enter. Voila! Excel automatically fills all blank cells down your column with the same 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.
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:
- Select all the blank cells in a column.
- Press F2 to edit the last selected cell and type some data: it can be text, a number, or a formula (e.g. "_unknown_")
- Press Ctrl+Enter instead of Enter. All the selected cells will be filled with the data that you typed.
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.
167 comments
Thankyou for explaining the method ....filling same number in multiple cells is what i was looking for and got it in your website ..great going
Hi, i was wondering if you can help. I have a list of website links, lets say they all begin with this "www.onlinegames.com/play/" and after "play" its the name of the game (the full link might look like this : www. onlinegames.com/play/random-game-1 , www. onlinegames.com/play/new-random-game-1, www. onlinegames.com/play/random-game-2 etc.)
I don't initialy have them on the spreadsheet and need to introduce them BUT, I do have the column with the game name (random-game-1, new-random-game-1,random-game-2 etc. ) So the spreadsheet is like this Col A = Full Marketing Name, Col B = random-game-name, Col C = Website links
If I copy the text "www. onlinegames.com/play/" in a random cell outside these columns, how can I use that in a formula that automatically copies "www.onlinegames.com/play/" + then adds "random-game-name" from Col B?
Hello!
Here is the article that may be helpful to you: How to select random sample in Excel.
Is there any way to add "text", for multiple cells, like I want to add colons and comma before and after the text,
Hi,
Is there anyway that i can get one cell (B17) to paste into another cell (G17) when clicking a cell (A1)?
Thank you
Hi!
Excel functions only work on the current cell. Your task can be solved with a VBA macro.
Looking to be able to put same data in two cells using either cell as the data entry cell.
example
A5 is a numbered cell that will change on occasion. In another part of the sheet cell Q5 represents the same data as cell A5. I want to be able to change that data in either cell.
Today A5 and Q5 would be the number 10, but tomorrow I need them to be changed to 12 but I don't want to have to go to each cell to make the change because the two cells will always be equal to each other. I'd also like to be able to make the change in either location and have the other cell change automatically too.
Hi!
Have you tried the ways described in this blog post? If they don’t work for you, then please describe your task in more detail, I’ll try to suggest a solution.
in Q5, type = and click on A5. Whatever you type into A5 will be duplicated into Q5.
O, the kind soul!
I have been looking for that simple advice for hours and here it is!!!
Thank you very much!!!
Hi, can anyone tell me what command I can use to copy data of one cell to an entire single row. Basically, I want to achieve the below steps with command.
.....................................
Move or copy cells
Select the cells or range of cells that you want to move or copy.
Point to the border of the cell or range that you selected.
When the pointer becomes a Hand pointer , do one of the following:
To
Do this
Move cells
Drag the cells to another location.
Copy cells
Hold down OPTION and drag the cells to another location.
Hi everyone,
I have a spreadsheet with date columns. How do I input the same date in the column (i.e. C2:C20) without the date automatically increasing numerically every cell
Thanks
I have a row of data with different number values and I just to simply add 5 in each cell. Example I have 96 in A1 and 10 in A2 and want to add 5 so they will become 101 and 15. How can I do that without typing or calculating manually? Please help!
Hello!
How to paste and add/subtract/multiply/divide at a time - use Paste special.
I hope it’ll be helpful.
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
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?
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
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.
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.
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
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.
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
how to make a data enter in one cell and in order (number) form?
eg.
1. baywalk
2. pavement
3. porch
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...?
Hi. I wanted to know if this can be done too. Have you found a way to do this?
Hello!
To automatically get part of the data from the main table to another workbook, try Pivot Tables.
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
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
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...
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.
Thanks