Share via

Excel table: Type formula only in one row rather than whole column

Anonymous
2023-06-15T07:01:22+00:00

Dear community,

I have a large table so that calculation can take some time. Thus, I would like to have some cell calculations only for one cell rather than all cells in the column. Is there a way to achieve this within the Excel table?

I only found this solution, which is not ideal as it would apply to all columns:

Turn calculated columns on or off

  1. On the File tab, click Options.
  2. Click Proofing.
  3. Under AutoCorrect options, click AutoCorrect Options.
  4. Click the AutoFormat As You Type tab.
  5. Under Automatically as you work, select or clear the Fill formulas in tables to create calculated columns check box to turn this option on or off.

Stop creating calculated columns automatically

After entering the first formula in a table column, click the AutoCorrect Options button that is displayed, and then click Stop Automatically Creating Calculated Columns.

Many thanks!

Microsoft 365 and Office | Excel | For business | MacOS

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

  1. Anonymous
    2023-06-16T19:10:53+00:00

    Try this:

    1. Enter the formula (it will fill)
    2. Press Ctrl+Z (Undo)

    The Undo undoes the autofill but not the initial formula entry.

    9 people found this answer helpful.
    0 comments

5 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2024-01-17T15:23:45+00:00

    Uncheck Fill formulas in tables...

    1 person found this answer helpful.
    0 comments
  2. Anonymous
    2024-01-17T14:51:03+00:00

    Thank you for that solution, but what if I don't want a particular column to have an automatic formula at all, so every time I add a new line it will only have the formatting, but not the formula?

    Also, sometimes I change a formula in a cell in a certain row and the entire column changes to that formula.

    I just want some columns to not have auto formulas at all. Can this be done without deleting the formula, and also making sure that if I do set a formula on a cell then it only applies to that cell and not be pasted automatically over the entire column?

    1 person found this answer helpful.
    0 comments
  3. Jim G 134K Reputation points MVP Volunteer Moderator
    2023-06-16T18:33:44+00:00

    Hi

    The autofill for formulas in Excel tables is a feature of Excel Tables, As you discovered, the settings to turn this on and off are all or nothing.

    An option for you would be to convert the Excel Table object to an Excel Range object by clicking Convert to Range on Table tab of the Ribbon. Autofill of formulas does not occur in range objects. Instead you have to use the Fill Down command whenever you want to fill a column with a formula.

    You do lose all the nice automatic formatting when converting to range object.

    1 person found this answer helpful.
    0 comments
  4. Anonymous
    2023-06-15T07:23:45+00:00

    After the formula auto apply to entire column, you may still delete the formula from other rows.

    0 comments