Add or Insert a Column
Adding a column means two unrelated things, and the search results mix them together freely. One is structural: opening a new empty column in the sheet, which pushes everything to its right and is done from the column headings. The other is arithmetic: totalling the numbers that are already in a column, which is =SUM(B2:B200) and changes no structure whatever. Close to a fifth of the demand arriving on this page is the second one, so it is worth being certain which you want before following any set of instructions — the two have no steps in common at all.
The formula
a new empty column in the sheet from the column heading; the blank one lands on your LEFT
the total of a column of numbers =SUM(B2:B200), or Alt+= in the cell beneath them
a new field on an Excel table type a heading in the cell just right of the table
the same text on every row a helper column of ="Mr " &A2, then Paste Special > Values
the same text shown but not stored a custom format of @" kg"A worked example
Someone asks for "a column added" to a sheet of monthly figures. There are two entirely different deliverables behind that sentence.
Ask whether the result should be a new blank field, or a number.
A new field means selecting a heading and inserting, which shifts every column to its right and re-points the formulas that referenced them. A number means one =SUM() in an empty cell and no structural change at all. Doing the first when the second was wanted quietly moves every column in the workbook that anything else refers to, which is a much larger event than it looks.
Which one do I need?
| If you want to… | Use |
|---|---|
| A new empty column somewhere in the sheet | Work from the column heading, and expect the blank one to appear on the left-hand side of your selection |
| The total of the numbers already in a column | That is =SUM(B2:B200) or Alt+=, and it inserts nothing |
| A new field on the end of an Excel table | Type a heading in the cell immediately right of the table and it extends itself, formatting and formulas included |
| The same text added to every value in a column | A helper column of ="Mr " &A2 filled down, then Paste Special > Values back over the original |
| A unit or prefix shown without changing the data | A custom format of @" kg" shows it while the stored values stay numeric |
| Two existing columns combined into one | A formula or Flash Fill, not an insert |
| Rows rather than columns | The same moves rotated: select row numbers, right-click, Insert |
| Excel refuses to insert anything at all | Something occupies the last column, XFD, and the sheet has nowhere left to shift |
Frequently asked questions
Does "add a column" mean inserting one or totalling one?
Both, which is why searching the phrase returns two unrelated sets of instructions. Inserting is structural — it opens a blank column and pushes the rest of the sheet right. Totalling is a formula that leaves the layout alone. If the answer you are reading talks about column headings and right-clicking, it is the first; if it mentions SUM or AutoSum, it is the second. Neither is a step in the other.
How do I add a column to an Excel table?
Type a heading in the cell immediately to the right of the table and press Enter — the table absorbs the new column with its banding and filter buttons already applied. Tabbing from the last cell of the last row does the same for rows. Any formula you then enter in the new column fills down the whole table automatically, and editing one cell of it breaks that calculated column into ordinary formulas.
How do I put the same text into every cell of a column?
For values that must really change, put ="Mr " &A2 in an empty column and fill it down, then copy that column and paste it back over the original as values before deleting the helper. For something that only needs to be seen — a unit such as kg — a custom format of @" kg" entered under Ctrl+1 shows it on every cell while the underlying values stay numeric and go on calculating.
How many columns can a sheet have?
16,384, running A to XFD, in every version since Excel 2007; the old .xls format stopped at 256. Inserting starts failing as soon as anything at all occupies column XFD, including stray formatting applied across a whole row, and Excel reports it as a possible loss of data rather than as a limit being reached. Ctrl+End shows where the used range genuinely ends, usually far to the right of the visible data.
New guides and tools, once a month
DE + EN · double opt-in · no spam