Pasting a Formula into a new column or worksheet, but so that it doesn't change

Hello - When I copy and paste a formula into a new column, Excel
assumes that I wanted to use references to different columns.

That is, each time that I want to copy and paste a formula into a new
column, or sheet, I have to either first make it an absolute
reference---or I have to paste it into Word, and then paste it into
Excel to keep it as the same formula.

Does anyone know if there is an easier way to paste a formula so that
the reference doesn't change, and the formula thus stays the same?

Thanks!!
0
2/4/2008 10:56:39 PM
excel 39879 articles. 2 followers. Follow

5 Replies
573 Views

Similar Articles

[PageSpeed] 2

One way is to copy it *from the formula bar*!

Click in the cell containing the formula.
Select the *entire* formula in the *formula bar*.
Right click in that selection and choose "Copy".
NOW ... hit <Enter>.

Navigate to the new location.
Right click in the new cell and choose "Paste".

And you're done!
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:2eb159b2-c819-446a-aa39-201adbf98809@q39g2000hsf.googlegroups.com...
> Hello - When I copy and paste a formula into a new column, Excel
> assumes that I wanted to use references to different columns.
>
> That is, each time that I want to copy and paste a formula into a new
> column, or sheet, I have to either first make it an absolute
> reference---or I have to paste it into Word, and then paste it into
> Excel to keep it as the same formula.
>
> Does anyone know if there is an easier way to paste a formula so that
> the reference doesn't change, and the formula thus stays the same?
>
> Thanks!! 


0
ragdyer1 (4060)
2/4/2008 11:35:45 PM
On Feb 4, 5:35=A0pm, "RagDyer" <ragd...@cutoutmsn.com> wrote:
> One way is to copy it *from the formula bar*!
>
> Click in the cell containing the formula.
> Select the *entire* formula in the *formula bar*.
> Right click in that selection and choose "Copy".
> NOW ... hit <Enter>.
>
> Navigate to the new location.
> Right click in the new cell and choose "Paste".
>
> And you're done!
> --
> HTH,
>
> RD
>
> --------------------------------------------------------------------------=
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------=
-"Dave K" <fred.sher...@gmail.com> wrote in message
>
> news:2eb159b2-c819-446a-aa39-201adbf98809@q39g2000hsf.googlegroups.com...
>
>
>
> > Hello - When I copy and paste a formula into a new column, Excel
> > assumes that I wanted to use references to different columns.
>
> > That is, each time that I want to copy and paste a formula into a new
> > column, or sheet, I have to either first make it an absolute
> > reference---or I have to paste it into Word, and then paste it into
> > Excel to keep it as the same formula.
>
> > Does anyone know if there is an easier way to paste a formula so that
> > the reference doesn't change, and the formula thus stays the same?
>
> > Thanks!!- Hide quoted text -
>
> - Show quoted text -

Thanks!
0
2/5/2008 1:09:08 AM
Another way is to put an apostrophe in front of the equals sign in the
first formula. This turns it to text, so it can be copied anywhere
without change. Then just remove the apostrophe from both cells.

Hope this helps.

Pete

On Feb 5, 1:09=A0am, Dave K <fred.sher...@gmail.com> wrote:
> On Feb 4, 5:35=A0pm, "RagDyer" <ragd...@cutoutmsn.com> wrote:
>
>
>
>
>
> > One way is to copy it *from the formula bar*!
>
> > Click in the cell containing the formula.
> > Select the *entire* formula in the *formula bar*.
> > Right click in that selection and choose "Copy".
> > NOW ... hit <Enter>.
>
> > Navigate to the new location.
> > Right click in the new cell and choose "Paste".
>
> > And you're done!
> > --
> > HTH,
>
> > RD
>
> > ------------------------------------------------------------------------=
---
> > Please keep all correspondence within the NewsGroup, so all may benefit =
!
> > ------------------------------------------------------------------------=
---=AD"Dave K" <fred.sher...@gmail.com> wrote in message
>
> >news:2eb159b2-c819-446a-aa39-201adbf98809@q39g2000hsf.googlegroups.com...=

>
> > > Hello - When I copy and paste a formula into a new column, Excel
> > > assumes that I wanted to use references to different columns.
>
> > > That is, each time that I want to copy and paste a formula into a new
> > > column, or sheet, I have to either first make it an absolute
> > > reference---or I have to paste it into Word, and then paste it into
> > > Excel to keep it as the same formula.
>
> > > Does anyone know if there is an easier way to paste a formula so that
> > > the reference doesn't change, and the formula thus stays the same?
>
> > > Thanks!!- Hide quoted text -
>
> > - Show quoted text -
>
> Thanks!- Hide quoted text -
>
> - Show quoted text -

0
pashurst (2576)
2/5/2008 1:36:13 AM
Appreciate the feed-back.
-- 
Regards,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:e3b2459b-09fe-4a9c-bb48-ae00803dea41@d70g2000hsb.googlegroups.com...
On Feb 4, 5:35 pm, "RagDyer" <ragd...@cutoutmsn.com> wrote:
> One way is to copy it *from the formula bar*!
>
> Click in the cell containing the formula.
> Select the *entire* formula in the *formula bar*.
> Right click in that selection and choose "Copy".
> NOW ... hit <Enter>.
>
> Navigate to the new location.
> Right click in the new cell and choose "Paste".
>
> And you're done!
> --
> HTH,
>
> RD
>
> ---------------------------------------------------------------------------
> Please keep all correspondence within the NewsGroup, so all may benefit !
> ---------------------------------------------------------------------------"Dave 
> K" <fred.sher...@gmail.com> wrote in message
>
> news:2eb159b2-c819-446a-aa39-201adbf98809@q39g2000hsf.googlegroups.com...
>
>
>
> > Hello - When I copy and paste a formula into a new column, Excel
> > assumes that I wanted to use references to different columns.
>
> > That is, each time that I want to copy and paste a formula into a new
> > column, or sheet, I have to either first make it an absolute
> > reference---or I have to paste it into Word, and then paste it into
> > Excel to keep it as the same formula.
>
> > Does anyone know if there is an easier way to paste a formula so that
> > the reference doesn't change, and the formula thus stays the same?
>
> > Thanks!!- Hide quoted text -
>
> - Show quoted text -

Thanks! 


0
ragdyer1 (4060)
2/5/2008 2:05:59 AM
You can also copy the formula from the cell. Just select the cell with the 
formula, press F2 to enter edit mode, press Ctrl+Shift+Home to select the 
formula, press Ctrl+C to copy the formula, then press Escape. Select the 
cell where you want the formula, press Ctrl+V to paste the formula.

Tyro


"Dave K" <fred.sheriff@gmail.com> wrote in message 
news:2eb159b2-c819-446a-aa39-201adbf98809@q39g2000hsf.googlegroups.com...
> Hello - When I copy and paste a formula into a new column, Excel
> assumes that I wanted to use references to different columns.
>
> That is, each time that I want to copy and paste a formula into a new
> column, or sheet, I have to either first make it an absolute
> reference---or I have to paste it into Word, and then paste it into
> Excel to keep it as the same formula.
>
> Does anyone know if there is an easier way to paste a formula so that
> the reference doesn't change, and the formula thus stays the same?
>
> Thanks!! 


0
Tyro (331)
2/5/2008 4:35:33 AM
Reply:

Similar Artilces:

Formula: What am I Doing Wrong
4 A 3 A 2 B 1 A 4 $6.00 3 $6.00 2 $2.00 1 $6.00 In the first example I want to know how many instances of A there are in column B multiplied by the number in Column A. Answer would be 8. Formula: sumproduct((a1:a4)*(b1:b4="A")) But if I want to find out how many instances of $6.00 there are instead of A, I'm stumped. I get either 0, or 48. Must be something simple. It is better to use =SUMIF(B:B,"A",A:A) than sumproduct((a1:a4)*(b1:b4="A")) I assume you mean =COUTIF(A:A,6) -- HTH Bob (there's no email, no snai...

Paste special treats cells as a picture
I just got a new computer, and a newer version of excel, which is great, but whenever I try to copy from one file to another, my paste special function treats the copied cells as a picture. Instead of the normal paste special popup, where it asks me how I want the data to come out, I get a different popup that shows the source as a "Microsoft Office Excel Worksheet Object." It asks me what format I want the picture to come out as, and gives me a list of different file formats. If I try to just paste a link, it puts a picture of the other file on the new one. I talked to so...

Improperly Saved Changes on an Attachment
Is there any way to retrieve changes made to an attachment that were manually saved, but not "saved as" and placed in a folder on my computer? I use Vista's Windows Mail to recieve my mail. The file is probably located in a folder somewhere in C:\Users\(youraccount)\AppData\Local\Microsoft\Windows\Temporary Internet Files\ "\AppData" is normally hidden "\Temporary Internet Files" is normally a hidden protected Operating System file You'll have to remove the checkmark (if they're hidden) in Windows Explorer: Organize - Folder & Sea...

coping formula
I am trying to copy a formula down a column. When I copy the formula down I want the cell to be blank until i fill out the cells to give me my balance. My three columns are Debit / credit / balance. My formual reads : f7(balance column)+e8(credit column)-d8 (debit column) when i copy the formula down form my balance column it gives me the balance in every cell. I would like for it to be blank until i fill in my debits and credits. I hope you understand the way i wrote this thanks much -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.aspx/excel-new/200803/1 You ...

locking formulas
Is there a way I can lock my formulas so no one can change them when i send my spreadsheet out? I have about 50 cells with formulas in them in 4 or 5 columns and I need them all locked. Thank you for any suggestions. -- speary ------------------------------------------------------------------------ speary's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=24959 View this thread: http://www.excelforum.com/showthread.php?threadid=389055 Select all cells that DO NOT require to be locked, go to Format>Cells>Protection and remove the check mark beside "L...

Copy and paste formula; Value pastes instead
Excel 2002 I've tried starting several worksheets, checking formats, pasting special (formula only). For some reason the formula actually copies OK by looking at the formula bar, but the value in the cell is identical to the value of the copied cell.Incorrect. Tried even the simplest formulas with no joy. BK ...

Is there an Excel formula to round a date to the end of the month
User enters a specific date, I'd like to round that entry to the end of the month With a date in A1: =DATE(YEAR(A1),MONTH(A1)+1,0) step into the next month and then back one day. -- Gary''s Student - gsnu201003 "Siralec" wrote: > User enters a specific date, I'd like to round that entry to the end of the > month Another way... This requires the Analysis ToolPak add-in be installed for Excel versions prior to Excel 2007. A1 = some date =EOMONTH(A1,0) Format as Date -- Biff Microsoft Excel MVP "Siralec" &...

Clipboard pasting
I copy something to the clipboard using Publisher 2007 and the computer tells me it is in the clipboard; however, when I go to paste that information into another document, Publisher acts like it is not there. I then need to close the document I wish to paste it into, reopen it, and then, like magic, there is the information in the clipboard and I am able to paste it. UGH!!!!!! -- Ginger Christenson Open the Office Clipboard in Publisher. Edit, Office Clipboard. What program are you pasting to? If it is an Office program, open the Office Clipboard in that program too. -- Mary Sauer ...

formula to get sheet name
hi, can a formula retreive a worksheet name or does it need to be macro based? thanks, N.S. Hi Nigel, This formula that takes care of the possible situation whereby you have only one sheet in the workbook and its name is the same as the workbook's. Note: CELL("filename") will only work if the file has been saved at least once and if the file is opened in a different language system, the argument "filename" will need to be changed manually to the corresponding word (e.g. in Spanish "nombrearchivo"): =LOOKUP(REPT("z",255),SUBSTITUTE(MID(CE...

Giving a name to a row or column
I have a spreadsheet that has a bunch of rows of different length. I am doing a match to find a column I want - which is between my starting column and an ending column that is big enough to fit my longest row. But periodically I insert a new column in the front, making my longest row longer. I can solve this problem by making my end row huge. Maybe make it zz (what's the largest column possible?). But this offends my sensibilities a bit. What would be better is to somehow assign a variable name for my column that would be moved right when I add a new column. I s...

copy formula only
When I copy a formula down the column the value gets repeated all the way down too . How can I only paste the formula so as the cells stay blank until related cells are filled in to make the calculations. Thanks Steve if(relatedcell="","",relatedcellformula) -- Don Guillett SalesAid Software donaldb@281.com "Steve Fletcher" <steve.fletcher1@blueyonder.co.uk> wrote in message news:Oqetb.63$Mw1.40@news-binary.blueyonder.co.uk... > When I copy a formula down the column the value gets repeated all the way > down too . How can I only paste the formul...

Formula returned in vlookup function
I've used vlookup for a while now in the office 2003. Just converted to office 2007. Now when I do a vlookup I get the formula displayed in my current cell instead of a returned value. I can't figure our what I'm doing wrong. Can someone help? Thanks bmc You've probably got a tick in the wrong place: Try Office Button (top left hand corner) Excel Options (bottom line) Click on advanced Scroll down to Display options for this worksheet and untick 'Show formulas in cells instead of their calculated results Click OK Hope this helps "87vette" wrote...

Microsoft Excel Viewer 2003 won't open Microsoft Excel Worksheet
Today a company e-mailed me a quotation with the extension: xls I don't have Excel and eventually found Excel Viewer 2003. This took 45 mins to download, and then didn't work. The Excel File Graphic had appeared atop the quotation file - but when clicked on Excel Viewer 2003 opened and just said: cannot open this file type. Any ideas? Would appreciate solution. Running XP SP2 on a Dimension 8250.. Thanks Maybe the sender could forward you a .pdf version of the file. Or you could specify the type of file you can read (MSWord (.doc), .txt). Then the sender could convert the fi...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

"Paste Special" with the right click mouse
I would like to have the option of "Paste Special" with the right click mouse in all microsoft office suite. Thanks, ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/office/community/en-us/default.mspx?mid=de847738-df56-4...

Text data
I am entering text data into a spreadsheet and I need to know how to calculate the number of times particular words/phrases appear in each column. I am very new to excel, so any help would be appreciated! =COUNTIF(A1:A1000,"word") replace A1:A1000 with your range and word with the word you want to count -- Regards, Peo Sjoblom "excelbeginner" <excelbeginner@discussions.microsoft.com> wrote in message news:FE55CF40-F3B6-42F3-918F-D200690758F6@microsoft.com... >I am entering text data into a spreadsheet and I need to know how to > calculate the nu...

Pasting formulas advances cell reference
Hi When i paste special from one cell into another, it advances the cell reference and i just want an exact match i.e. A1 formula ='Feb 2010'!C18 but when i paste it into another cell it pastes it as ='Feb 2010'!D18 The C changes to D, how do i stop this happening? Thanks for any help in advance Derek Use absolute referencing. Below are the different reference styles. A1 Relative referencing. Both column and row will change if you copy or drag the formula. $A1 The column reference is fixed and will not change A$1 The row reference is fixed and wil...

Stacked Columns and TWO axis
Does any one know how to add a second Y axis to a stacked column chart? When one charts revenue and profit, and the profit is just a small percentage of the revenue, the impact of the profit distribution by category is lost because the scale on the Y axis measuring the revenue. Any help is greatly appreciated. [This followup was posted to microsoft.public.excel.charting with an email copy to Ed Pereira. Please use the newsgroup for further discussion.] Plot both series on both axes. How? Plot the series as normally so that both are on the primary axis. Then, select the 2 ranges ...

New Documents opening protect
When every I open a new word document it is open with the protection on. I am using Word 2007 and would like to turn this off. Any suggestions On Tue, 24 Nov 2009 09:21:02 -0800, Mike <Mike@discussions.microsoft.com> wrote: >When every I open a new word document it is open with the protection on. I am >using Word 2007 and would like to turn this off. Any suggestions Your Normal.dotm template has protection. This is a special case of the problem described at http://word.mvps.org/FAQs/AppErrors/BlankDocNotBlank.htm, and the solution is the same (see Method 3). -- ...

Formula Bar
Have cells custom formatted for seconds :ss but in the formala bar shows and invalid date and time, is there a way to make the formula bar show the correct value of the cell i.e. typed in cell - 55 for 55 seconds but the formula bar showing content as: 1900-02-24 12:00:00 AM for me to get it to display as :55 have to enter it as :55 but then can't use it for a calculation if format it as mm:ss enter 55 shows as 00:00 in the cell but in the formula bar shows content as: 1900-02-24 12:00:00 AM need cells to display a seconds :55 but will need to use the cells for oth...

New update: synchronize with iCal doesn't work.
Finally it possible to synchronize Entourage with Apple Address Book and iCal. Address Book works, but the data from iCal do not appear in Entourage. Is someone else sucessful in this? -- To mail me: look closely to my address In order for your iCal entries to show up in Entourage, they have to be in the "Entourage" calendar in iCal. Entourage can only sync to one calendar, and that's iCal's new "Entourage" calendar; try assigning one of your entries in iCal to it, and you should see it show up in Entourage as well. On 16.03.06 4:48, in article 1hca2ko.19epc6u1...

Copy/Paste loop
I have a form on one sheet in excel, on the next sheet i have mad different coloums with different headings. I want the data from th form to be copyed to each heading e.g. name in form is copyed to unde the coloum with title name. So each time a user fills in there details he/she clicks submit an there data is copyed to the next page, like a small database. Is there a macro that can do this as i keep getting different error each time. I want this to continue in a loop Please help!!:confused -- Message posted from http://www.ExcelForum.com This might get you started: Option Explicit Opti...

ActiveSync does not sync new mail without intervention
I have a SBS2003 with Exchange 2003 SP2. The computer is behind a Watchguard firewall. ISA is not running. OWA & OMA both work fine via https from either mobile devices or computers. From Windows Mobile 5 works fine if in cradle (I'm assuming that activesync notes & tasks syncing keeps outlook open) but if remote Initial sync - works fine Send mail - works fine read mail messages - works fine sync'd message deleted - works fine new mail - only works if mail already read Activesync shows that it has synchronized all items without error but new messages do not sync! If a ...

Cancel a combo box change with VBA
Hi, I have a combo box from which I'd like to cancel the change depending on the answer selected from a msgbox. It goes something like that... The user changes the combo box value. The system asks the user to confirm he wants to change the value. The user says No. The combo box is left untouched. There's more to it but I simplified it so that it's easier to explain. Can anybody point me in the right direction as to how I should proceed to accomplish this with VBA? Thanks in advance! Assuming it's a bound combo box, put code like the following in its BeforeUpdate event:...

Curly Brackets In Formulas
I'm trying to copy formulas from one spreadsheet to another similar to the one below, but the curly brackets disappear, rendering the formulas inoperable (in the original document, they compare values in one range of cells against another, count the cells that match as 1, and adds them up). Adding the curly brackets manually just converts the cells to text. How do I make these formulas work in the new document? I'm using Excel 2000. TIA. ~Charie G. {=SUM(COUNTIF(B5:F5,$B$2:$F$2))} Hi Charlie the {} indicate that the formula is an array formula which is entered in a cell using a c...