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. Continue reading
Comments on: How to enter the same data (formula) into all selected cells at a time
by Alexander Frolov, updated on
Comments page 3. Total comments: 167
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?
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.
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.
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
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.
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.
Seems like Alexander sucks! He still hasn't answered the question. What a loser!!
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
HAI SIR
I HAVE A DOUBT IN EXEL..
IS THERE ANY WAY TO COPY AND PASTE THE SEVERAL CELL AT A AME TYM
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!
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?
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?
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
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.
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
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
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
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
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.
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.
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?
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!!!!!!