+ Reply to Thread
Results 1 to 6 of 6

How to stop adding automatic reference to cell when I click on it after I wrote a formula

  1. #1
    Registered User
    Join Date
    05-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    18

    How to stop adding automatic reference to cell when I click on it after I wrote a formula

    When I'm writing a formula in a cell then click on another cell it adds a reference to that cell. How do I stop this from happening?
    For example--when I have copied the contents of a cell, and I am trying to simply paste it into another cell, it will instead try to add the contents of that cell to the first cell (=$A$9 or something like that will appear in the second cell while $A$9 is added to the formula in the first cell) and I will have to press Esc, which sometimes works (other times it goes back to selecting the first cell), and then paste it.
    When I google this most of the results are about the unrelated question of keeping absolute values when you paste into another cell. This is what I am talking about: how-to-get-excel-out-of-select-cell-mode superuser
    Last edited by reagr; 05-03-2021 at 04:54 AM.

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop adding automatic reference to cell when I click on it after I wrote a form

    Hi, not much to go on with what you're saying, this formula you're entering is this just in a worksheet or inside a listobject (Excel table)?
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Guru
    Join Date
    08-05-2004
    Location
    NJ
    MS-Off Ver
    365
    Posts
    13,578

    Re: How to stop adding automatic reference to cell when I click on it after I wrote a form

    Not sure why you would click on another cell in the middle of writing a formula (I cannot think of any reason you'd be doing this on any regular basis) but that's basically how Excel works to make it easier to reference other cells within a formula. If you click on another cell, it automatically assumes that you want that cell reference as part of your formula. You'd need to wait until you are done with your formula before pasting that data into another cell.

    You could prevent this from happening by starting your formula with the single quote mark ' Then Excel thinks you are typing text and not a formula and will simply change focus to the new cell when you click on it. Then remove the ' when you are finished typing the formula to convert it back to a formula from text.
    ChemistB
    My 2?

    substitute commas with semi-colons if your region settings requires
    Don't forget to mark threads as "Solved" (Edit First post>Advanced>Change Prefix)
    If I helped, Don't forget to add to my reputation (click on the little star at bottom of this post)

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Registered User
    Join Date
    05-03-2021
    Location
    US
    MS-Off Ver
    2016
    Posts
    18

    Re: How to stop adding automatic reference to cell when I click on it after I wrote a form

    "Not sure why you would click on another cell in the middle of writing a formula (I cannot think of any reason you'd be doing this on any regular basis)"

    This occurs whenever I copy and paste within a document, which is a regular activity.

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: How to stop adding automatic reference to cell when I click on it after I wrote a form

    Like @ChemistB is saying, when you're in an actual formula every click in (another) cell Excel 'thinks' you're editing a formula.

  6. #6
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,400

    Re: How to stop adding automatic reference to cell when I click on it after I wrote a form

    If you are copying all or part of a formula from within the cell, then click on ENTER to leave the cell BEFORE paste.

    So, instead of COPY PASTE, you need to do COPPY ENTER PASTE.

    Or CTRL+C - ENTER - CTRL+V.

    Just realised that this thread is over a year old, but this may help someone.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 08-24-2020, 10:37 AM
  2. [SOLVED] Stop Automatic Cell formatting to a date.
    By Noobulon in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-12-2017, 07:01 AM
  3. Replies: 5
    Last Post: 01-15-2016, 06:16 PM
  4. Click Button to start/stop a Stop Watch
    By Aeneren in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-14-2016, 01:25 PM
  5. [SOLVED] VBA Codes who insert the automatic worksheet when click a cell and all datas of its
    By PRADEEPB270 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 10-14-2013, 03:32 AM
  6. STOP automatic cell formatting
    By justinb2 in forum Excel General
    Replies: 6
    Last Post: 08-02-2011, 12:14 AM
  7. Replies: 1
    Last Post: 03-14-2006, 03:10 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1