Excel Tables - How do I tell Excel to NOT apply a formula to all rows in the table?

Posted on 2012-09-14
Last Modified: 2012-11-18
I love Excel tables. I've been using them more-and-more as of late.

Excel Tables
Let's say I've created a simple table that includes cells A1:E5 and I create a formula in cell E2. Excel, in an effort to be smart, automatically transfers the formula in E2 to cells E3 - E5. Most the time, that's what I want. Sometimes, however, I want the formulas in column E to vary. Is there a way to tell Excel to not automatically transfer the formula in E2 to cells E3 - E5?
Question by:jdana
    LVL 39

    Accepted Solution

    Undo (Ctrl+Z) after the formula has been expanded.

    LVL 23

    Expert Comment

    If you click on the table, a new menu for Table Design comes up.. you can choose to Convert to range... then the formulas won't copy along...
    LVL 19

    Expert Comment

    by:Ken Butters
    this can also be controled with autocorrect options... but it will ALWAYS apply...

    go to file / options / Proofing

    click on auto correct options button

    go to autoformat as you type tab

    uncheck "Fill formulas in tables to create calculated columns"

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
    In case Office 2010 has not been deployed in your environment, this article may be quite useful. In our office, we wanted a way to deploy Microsoft Office Professional Plus 2010 through an automated batch file via logon script. This article is docum…
    The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now