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".
With any one of the cells in your data range highlighted, select
Table
from theInsert
tab of the command ribbon and click OK on the dialog box that pops up.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 theTable Tools
tab.)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 theInsert
dropdown menu on theHome
tab of 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.
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.)