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
hi
i have one doubt
a
2244
2244
3444
3444
b
avc
cbd
is it possible to replace avc in 2244.. i cant use find and replace process because there are lot of data need to be changed. so kindly give a reply with working results.
Thanks. Helped a java developer to copy paste data from excel to SQL.
I have a worksheet that is a part number application guide, with 1000+ listings. Some parts are single entries, some are repeated many times over for different applications. I have a separate list of specifications for these parts (sizes etc) that I want to add in against each and every original listing. Is there a way I can bulk enter information into new columns against the original part numbers, or do I have to do it part by part?
Hi
is there a way i can copy a value in a column starting at row 5 until row 3000. i don't want to to do it manually selecting the cells, as it's a long to select and i have multiple columns where i need to replace/insert the value. i am hoping to do it in an easy way, i know how to do it manually by selecting each cell.
How to use in excel Numerical Text Colour data to Mailmerge in word
=(L1)
Please see the above. Basically is there a way to "fill in the blanks" from other columns? Basically I have url information in Column B, Link Display info in Column L. It's more like mail merge. I tried the combining cell info methods, but it obviously is not meant to do what I am intending. Any help would be much appreciated. Thanks in advance
Best one ever. When you want to add the same value to a cell in a column just left click on the cell to highlight it, then double (left) click on the right-hand corner and the cells below all receive the same information.
basically i want to select formula cells in a row and paste in the same corresponding cells in another row
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.
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 )
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.....................
PLS HELP ALEX
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 ,
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?
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
How to Copy single data
a
a
a
a
b
b
b
f
f
g
g
g
g
Like this
a
b
f
g
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.
T H A N K Y O U!!!!!!
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.
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?