Excel: Stop Auto Numbering at Duplicate Column Header

Updated 4 November 2026 · Standard formula, shown below

Excel does not automatically number duplicate column headers, but it does automatically rename them by appending a number (e.g., Sales, Sales1, Sales2) when you use a table or a query that requires unique field names. To prevent this auto-numbering, you can either rename the duplicate headers manually before creating the table, or use a formula that references the header cell without triggering Excel’s unique-name enforcement. This article explains why Excel adds those numbers, how to stop it, and what to do when you need duplicate headers in a worksheet.

Why Excel Adds Numbers to Duplicate Headers

Excel’s auto-numbering behavior appears in two main contexts: Excel Tables (created with Insert > Table or Ctrl+T) and Power Query (Get & Transform). Both require that every column header be unique because they use header names as field identifiers in formulas, structured references, and data transformations. When you type a header that already exists in the same table, Excel automatically appends 1, 2, etc., to the new one. For example, if you have a column named Amount and you type Amount in the next column, Excel changes it to Amount1. This is not a bug; it is a deliberate design to ensure that each column can be referenced unambiguously.

The same rule applies when you load data into Power Query: if your source data has duplicate column names, Power Query renames them by adding a suffix (e.g., Name, Name_1, Name_2). This happens because the query engine uses column names as keys in M code, and duplicates would cause errors.

How to Prevent Auto-Numbering in an Excel Table

If you are working with an Excel Table and want to keep duplicate headers (for example, two columns both named Q1), you have a few options:

Rename the header before creating the table: If you type the headers first and then convert the range to a table, Excel will still rename duplicates. So this does not work directly.
Use a normal range instead of a table: If you do not need table features (like structured references or automatic formatting), simply keep your data as a plain range. Excel will not enforce unique headers in a normal range; you can have as many duplicate headers as you want.
Use a formula that references the header cell: If you need table features but want to display a duplicate header, you can hide the actual header row and create a custom header row above the table. For example, put a formula in A1 that references A2 (the table header), and then hide row 2. This way, the visible header can be duplicated, while the table’s internal header remains unique.
Use a naming convention that avoids duplicates: Instead of fighting the auto-rename, you can intentionally name the columns Q1a and Q1b or Q1_1 and Q1_2 so that they are unique but still convey the same meaning.

If you already have a table with auto-numbered headers (e.g., Sales and Sales1) and you want to remove the number, you can simply select the header cell and delete the number. Excel will not re-add it unless you create another duplicate. However, if you then try to use structured references, you must use the exact header name (including the number) in your formulas.

How to Stop Power Query from Renaming Duplicate Columns

When you import data into Power Query, duplicate column names are automatically suffixed with _1, _2, etc. To prevent this, you have two main approaches:

Rename the columns in the source before loading: If you are importing from a database or a text file, you can edit the source query to give each column a unique name. For example, in SQL you can use SELECT col1 AS "Name", col2 AS "Name_1".
Rename the columns manually in Power Query: After the data is loaded, you can select the duplicated columns and rename them to whatever you like. Power Query will not re-add suffixes unless you refresh the query from the source and the source still has duplicates.

If you want to keep the duplicate names in the final output (for example, when you load the query to a worksheet), you can load the query as a connection only and then use a formula to bring the data into a normal range. That way, the final worksheet can have duplicate headers, but the query itself uses unique names internally.

Common Mistakes and Misconceptions

A frequent misconception is that Excel’s auto-numbering is a bug or a setting that can be turned off. In reality, it is a core part of how Excel Tables and Power Query work. There is no built-in option to disable it. Trying to turn it off will not work; instead, you need to work around it by using normal ranges or by renaming columns before they enter the table/query.

Another common mistake is to assume that the auto-numbering is permanent. If you rename a header to remove the number, Excel will not add it back unless you create another duplicate. However, if you refresh a Power Query, the suffixes will reappear because the refresh re-imports the source data and re-applies the renaming logic.

Also, be careful when using formulas that reference table headers. If you have a column named Sales and another named Sales1, a structured reference like Table1[Sales] will refer to the first column, and Table1[Sales1] will refer to the second. If you delete the number from the second header, the structured reference will break and show a #REF! error.

When You Actually Need Duplicate Headers

There are legitimate reasons to want duplicate headers, such as when you have two columns that both represent the same metric (e.g., two Score columns from different sources) or when you are preparing a report where the header row is purely visual. In these cases, the best solution is to avoid Excel Tables altogether and use a normal range. You can still apply formatting, filters, and even formulas, but you lose structured references and the automatic expansion of the table range.

If you must use a table but want to display duplicate headers, the hidden-header trick works well:

Create a table with unique headers (e.g., Score1 and Score2).
Insert a new row above the table (if your table starts at row 1, you will need to move it down).
In the new row, enter a formula that references the table header cell, e.g., =A2 for the first column, =B2 for the second.
Format the new row to look like a header.
Hide the original header row (row 2) by right-clicking and selecting Hide.

Now the visible headers can be identical, while the table internally uses unique names.

Alternatives to Auto-Numbered Headers

If you are using Power Query and want to avoid the _1 suffixes, you can use the Transform > Rename Columns feature to give each column a meaningful unique name. For example, if you have two columns named Price, you could rename them to Price_Retail and Price_Wholesale. This is often better than leaving them as Price and Price_1 because it makes the data self-documenting.

Another alternative is to use Excel’s Power Pivot or Data Model, which also requires unique column names but gives you more control over the naming. You can rename columns in the diagram view without affecting the source data.

Frequently Asked Questions

Can I turn off Excel’s auto-numbering for duplicate headers?

No, there is no setting to disable it. Excel Tables and Power Query require unique column names, so the auto-renaming is enforced. You can work around it by using normal ranges or by renaming columns before they enter the table/query.

Why does Excel add a number to my second column with the same name?

Excel adds a number to ensure that every column header in a table is unique. This is necessary for structured references and for Power Query to function correctly. Without unique names, formulas like Table1[Column1] would be ambiguous.

How do I remove the number from a duplicate header in an Excel table?

Simply click on the header cell and delete the number. Excel will not re-add it unless you type the exact same header again. However, if you use structured references, you must update them to match the new header name.

Can I have two columns with the same header in a normal Excel range?

Yes, in a normal range (not a table), you can have duplicate headers. Excel does not enforce uniqueness there. This is the easiest way to keep duplicate headers if you do not need table features.

Why does Power Query add _1 to duplicate column names?

Power Query adds _1, _2, etc., to duplicate column names because the M language requires unique column names for each step. This is similar to Excel Tables but happens during data transformation. You can rename them manually in the query editor.

Will refreshing a Power Query remove my manual column renames?

If you rename columns manually in Power Query, those renames are part of the query steps and will persist after a refresh. However, if the source still has duplicate names, the initial auto-rename will happen again, but your manual rename step will override it.

Method: the standard formula for this calculation, rounded as each result states. See how the calculators work or report an error.