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

Posted on 2012-09-14
Medium Priority
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

nutsch earned 1380 total points
ID: 38399419
Undo (Ctrl+Z) after the formula has been expanded.

LVL 23

Expert Comment

ID: 38399629
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
ID: 38400308
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

ID: 38610353

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
With its various features, Office 365 can not only help you with your day-to-day business tasks, it can also do wonders for your marketing campaign.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

840 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