Excel – How to get “Calculated Columns” to work in excel


Using Excel 2010, I want a single formula to apply to all cells in a column.

I see documentation for a feature called "Calculated Columns". That is exactly what I want, but when I follow the instructions I only end up creating a formulate in a single cell – not the whole column.

I don't want to use "Fill" or "Copy" because the number or rows in the sheet changes periodically and maintaining the formulas would be very tedious.

Best Answer

You can create a calculated column when you set up a data range as an Excel Table (which used to be called a List).

The first step is to convert your data range to a "table".

The data range in its original form.

With any one of the cells in your data range highlighted, select Table from the Insert tab of the command ribbon and click OK on the dialog box that pops up.

Selecting Table from the Insert tab

Create Table dialog box

Your data range will be automatically formatted, with filter dropdowns at the top of each column. (If you're like me and don't really care for the default formatting that Excel uses, you can change it by selecting Design on the Table Tools tab.)

Newly created table with formatting

Now you need to insert a new column for your calculation. To insert the column, put the cursor in one of the cells immediately to the right of where you want the new column to be and then choose Insert Table Columns to the Left from the Insert dropdown menu on the Home tab of the ribbon.

Selecting Insert Table from the ribbon

Enter your formula in the normal way into one of the cells of the new column. (It's probably a good idea to enter a descriptive header for the column at this point.)

If you use point-and-click to build the formula from neighboring cells, the result will look a little strange--Excel shows the cell references in the internal format that it uses. If you just type the formula in, it will look the same as usual.

Immediately after you press the Enter key to confirm the formula, Excel will copy it down to the bottom of the column.

Table with Calculated Column

The Calculated Column is now in place. If you add rows at the bottom of the table, the column formula will automatically be copied down. If you paste over the existing data with a new set of data with more rows, the calculated column will again adjust automatically. (Be careful not to paste over the formulas in the column, because you will lose them just the same as if the table was a regular data range.)

Related Question