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 2016, 2013, 2010, 2007 and lower.
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: 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.
You may also be interested in:
123 Responses to "How to enter the same data (formula) into all selected cells at a time"
Very helpful page how we add /insert 2-3 more numbers in already
Written numbs for e g 3215436232 is already in colum we want
To add 92 before 3215436232 in whole coloum.
If I use CTRL+Enter it will erase the content of the cell. I need it to be done so the original content remains and the next content goes in front (to the left) of the original content of each selected cell.
Yes i also having same problem pls anybody share the solution
Hi David and jawwad
a very simple way is to use the above cell content then you can easily copy the formula in all the belove cells, then they all get the same value of the first cell and whenever you change the first cell's value they all will change at the same time. TADA! ;)
something like this:
- if your main cell is: C4
- then in the "cell C5", you can write this formula: =C4
- and then copy the "cell C5" into all the below cells
- the formula will change to something like this but they all show the C4's content:
=C5
=C6
=C7
...
@Milad ~ Oh my God, dude thank you SO MUCH! I wanted to solve this simple ass problem for SO long. I just want it to duplicate the damn number in the corresponding horizontal cells and NOBODY could give me an answer to this, haha! Thanks again! =D
Mehnaz, David, thank you for your comments. I'm going to cover this task in my next tip this week. I will add a link here as soon as I publish it.
i need a help to bring many excel sheet value one by one like if 01.10.2016 has columns namely(name ,process ,work item-done) then the next 02.10.2016 in that excel sheet values to another sheet without change in the source data using formula in excel itself if i not clear let me know i will be more specif about my require
thanks
please respond to the enquiry above "If I use CTRL+Enter it will erase the content of the cell. I need it to be done so the original content remains and the next content goes in front (to the left) of the original content of each selected cell."
i am trying to add '0' to a list of telephone numbers in multiple rows in a column.
Thanks
Hi Louis,
Sorry, I have not written the article yet. Anyway, this is no simple solution.
You can create a helper column, e.g. column F, supposing that column E contains telephone numbers.
To add zeros to all tel numbers, enter the following formula in cell F2:
="0"&E2
If you need to add "0" only to some of the numbers that contain fewer than the specified number of characters, 8 characters in my example (including digits, dashes and any special symbols), use this formula:
=if(len(E2)<8, "0"&E2, E2)
Then copy the values from the Helper column to your main column in this way: select the entire column F, press Ctrl+C, select column D, press Shift+F10 and then V (this is a shortcut for Paste special - Values).
After that remove the helper column.
If you are looking for something different, you can send me a sample of your data at alexander.frolov@ablebits.com and I will try to help.
very simple and easy formula thanks dear
Hi,
If i use CTRL+Enter the same data will be copied to the selected cells. I want merged cells to be auto numbered. Do you have any tips for that.
Hello Mary Victoria,
We do not have any tips for that, I am really sorry.
Merged cells are of different size
I have a massive dataset derived from genetic screening, and I need to isolate parts of it based on specific values. This is because I have received separate workbooks that I need to piece together. I know it is possible to use the "advanced filter" option with the formula ="=entry" to filter out the entries that I do not want, however I have so many entries to screen for that editing them one at a time will take me months. Is there a way to edit a column to include this formula without having to retype each entry, so that I can use it for advance filtering??
Hi Vince,
Please see "The fastest way to insert a formula into the entire column" section
https://www.ablebits.com/office-addins-blog/2013/09/26/insert-same-data-multiple-cells-excel/#insert-formula-entire-column
If it is not what you are looking for, then please send me a sample book to alex@ablebits.com. I will have a look and try to figure out a way.
Hello Alexander
Have you had a chance to look at my problem yet?
kindly help me in it
if column A of Book1 is having numbers 1 to 1oo (like 1 2 3 4 5 etc...)
i want to put this data automatically in another excel sheet called book2 but as mentioned below
A1 of book1 comes A1 of book2 (want three cell gap in every new data)
A2 of book1 comes A5 of book2
A3 of book1 comes A8 of book2 and etc...
To copy/paste a couple of lines into multiple worksheets, without overwriting any data, I use the 'copy' & 'Insert Copied cells' option.
The option is removed when I tab to the next worksheet so I have to use copy again. Is there any way to keep using the 'Insert Copied cells' option more than once without having to copy again?
Thanks
As far as I know, it is not possible to change this behavior of Excel.
Hi
Here is my Q.. i have an excel like this
column A
A
-
-
-
B
-
-
-
C
is there any way I can fill the - with A and B respectively in one shot?
after updating
Column1
A
A
A
B
B
B
C
Hello kishor,
Yes, it is possible.
- Select all the cells you want to replace.
- Press Ctrl+F.
- In the "Find what" field, enter "-".
- Press the "Find All" button.
- Select the first result in the list.
- Press Ctrl+A to select all cells.
- Press the Close button.
Then see how to fill the selected cells with values above:
https://www.ablebits.com/office-addins-blog/2014/05/02/fill-blanks-excel/#fill-blanks-formula
Any way to make me analyze or view only the column and rows that I have filled rest will be deleted.
Hello Akshay,
Sorry, I do not quite understand your task. Can you, please, explain in more detail?
Hi, could you help me with a problem?
I have in column 'A' 3000 entries. Some are repeated values like i350, i560,e900 etc which code for a specific name i.e. i350=apple, for example.
So it looks like this:
A B
i350 Apple
E900
g560
y700
i350
i350
Column B is empty, So what I'm Doing at the moment is; using ctrl+f to find 'i350' (of which there will be 300ish scattered throughout column A) then pasting 'apple' into column B beside it. This is very time consuming and inefficient and is hurting my wrists a lot because I have 15000 to do. I would love to be able to just find say 'i350' and use some sort of function button to paste 'apple' in column B right beside all the i350s in column A. IS this possible?
I could put them all alphabetically and just drag down copy and paste when they are all in a row but I need the values in column A to stay in the order they are in, so i can't just paste them in and undo it twice to get back to the same order either.
Any help would be most appreciated!
Hello Ronan,
Here is the fastest way:
- Create a lookup table, where column A contains the codes (i350, etc.) and column B – the words (Apple, etc.).
- Download and install Merge Tables Wizard:
https://www.ablebits.com/excel-lookup-tables/index.php
- Select the main table, run the add-in, select the Lookup table.
- Compare 2 tables by columns A.
- Choose this action: Update column B in the main table with the values in column B from the Lookup table.
- 15,000 entries with get updated in about 10-40 sec.
If the words get changed in the future or if you add new code-words, simply update the Lookup Table and run the add-in again.
Thanks a million alexander for your speedy and wonderful reply :)
I have a list of 5,000 incoming students. Column A lists their student ID, which consists of three letters and four numbers (ex. kal0009). To make that into the student's email address, I need to add @auburn.edu to each one. Is there a way to do that (add the same text to columns that already have text) without doing it manually? I'm wondering if I could make Column B all @auburn.edu and then merge the two columns. Is that even possible, or is there another way? Thanks bunches!
Hello Kate,
Yes, your approach will work. For more detail, please see the following post about merging columns in Excel:
https://www.ablebits.com/office-addins-blog/2013/10/13/merge-columns-excel-without-losing-data/
I cannot suggest a faster way, sorry.
HELP....I am trying to also add a column to over 3,000, I want the column to all say the exact same thing. How were you able to add @auburn.edu to everyone? Did you have to manually do it?
Hi Alexander,
I've created a spreadsheet for statistical purposes but have issues when trying to copy formulas from one sheet to another.
I'm looking at data ranges for different elements with the Mean, Range and Standard Deviation. For each entry I'm using a formula like: =STDEV('XRF Edited'!H2:H14, then for the next element =STDEV('XRF Edited'!I2:I14. The only variable for that is changing is the column and preceding letter. I've tried the trusted drag and drop to copy the formulas but this will only copy whats been done.
Are they any quick methods for copying these formulas, which take into consideration only the column letter changing each time? I've looked at the absolute and relative cell references but neither seem to fix the issue.
Any advice or guidance you could offer would be greatly appreciated!!
Best wishes
Steve
i want to insert all data in a format from internet dtaa base, how at a time i can paste those things , and if there will be same number in rows , how i will find those ......plz i want solution my dear
Hi Alexander,
I've created a excel sheet where we made it one floor plane (table).In which I put the numbering on the cell in floor plane like (1,2,3),somewhere down I've written the name of the cell.
suppose If I am clicking the particuler number so it has to show the name of that cell which I've written down.can u help me on that.
Thanks
Hi. Is there a way I can copy data from a column into a column with several cell spaces between them?
Source Column:
A1
A2
A3
A4
A5
Cells I want to fill in:
A1
_
_
_
_
A6
_
_
_
_
A11
_
_
_
_
A16
_
_
_
_
A21
_
_
_
_
A26
Thank you in advance =)
Hello Leslie,
Please insert the following formula into the Destination column and copy it down:
=IF(MOD(ROW(),5)=1,INDIRECT("A"&ROW()),"-")
Where "A" is the name of the original column.
If in the sixth row you want to get the value from cell A2, not from A6, then use this formula:
=IF(MOD(ROW(),5)=1,INDIRECT("A"&INT((ROW()+5)/5)),"-")
To replace the formula with the values, please select the entire column, press Ctrl + C, then Shift + F10 and then V.
Hi -
I have a huge set of data, and I'd like to insert data points into specific cells. For example, I'd like to enter iPhone into ever 7th cell.
Please advise.
Please clarify whether you want to change the content of each 7th cell or insert text before each 7th cell.
Hi Alexander
I am trying to put the same words in 2 different boxes on 2 different spreadsheets. How do you do this?
Alexander,
I am trying to create an inventory list in Microsoft Excel and I have all the numbers in column A, how do I put a letter/s in front of them (so I will know which vendor they are from) without having to manually do it? Thanks in advance for your help.
Hi Shan,
It is not very convenient to fulfill this task in Excel. Please add a Helper column, enter the following formula there:
="AB"&$A2
Where "AB" is letter(s) you want to add, $A2 is the first cell with data in your column.
Select the entire Helper column, press Ctrl + C, go to the 1st cell in column A, press Shift + F10 and then V to place the new variant over the old one.
Then you can delete the Helper column.
I tried adding a helper column to do this and it either deletes all my original content or it puts a letter v in the place of the first cell.
The steps I took were first to move everything to column B and then put into column A the formula above, only I changed it to ="AB"&$B2.
Then I selected column A, pressed Ctrl + C, went into the 1st column of column B, pressed Shift + F10 and then V and so maybe this is where I am messing up. I've tried pressing them all together and then separately or I am just not creating the helper column correctly any further suggestions?
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?
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.
T H A N K Y O U!!!!!!
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.
How to Copy single data
a
a
a
a
b
b
b
f
f
g
g
g
g
Like this
a
b
f
g
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
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
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 ,
PLS HELP ALEX
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.....................
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 )
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.
basically i want to select formula cells in a row and paste in the same corresponding cells in another row
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.
=(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
How to use in excel Numerical Text Colour data to Mailmerge in word
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.
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?
Thanks. Helped a java developer to copy paste data from excel to SQL.
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.
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?
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.
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.
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
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
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
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
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.
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
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?
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 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!
HAI SIR
I HAVE A DOUBT IN EXEL..
IS THERE ANY WAY TO COPY AND PASTE THE SEVERAL CELL AT A AME TYM
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
Seems like Alexander sucks! He still hasn't answered the question. What a loser!!
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.
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, 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 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.
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 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?
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 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.
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
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. They are designed for cases like this and they can be found in the Cell Cleaner add-in.
You can download and install its fully functional trial version by clicking here.
step 1: select the data
step 2: go to data then text to columns
step 3: go to fixed length &cut as required
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?
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.
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.
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
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
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
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.
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
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.
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
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
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
Thank you so much.
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)
Is there any one to help me with some excel formulas
Thanks
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.
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...
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 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
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...?
how to make a data enter in one cell and in order (number) form?
eg.
1. baywalk
2. pavement
3. porch
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
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.
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.
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
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