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
Is there any one to help me with some excel formulas
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
Aman:
I would use the CONCATENATE function like this:
In A1 enter =CONCATENATE(C1,",",C2,",",C3,",",C9)
Thank you so much.
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
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
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
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.
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
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
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.
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
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
Dear Zahoor,
Please copy your data & Special paste as transform.
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
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
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.
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.
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?
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)
Hey, Samir,
I believe you may want to check out a couple of our tools: Extract Text and Add Text.
step 1: select the data
step 2: go to data then text to columns
step 3: go to fixed length &cut as required
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
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?
I figured it out v-lookup on a separate sheet with two calendar years in back to back rolls.
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
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?