"Format as Table" Increments Column Names

Question

I love using the formatting styles for tables in Excel 2007, but in my data I'm using the same column name for multiple columns.

When I format my table using the pre-defined styles, it automatically adds an incremental number to each subsequent column name which I don't want. Is there any way to stop this from happening? If I attempt to manually rename the column back to the original name, it automatically appends the incremented number.

Answer 1

Think of them a bit like database tables. The column names need to be unique.

See http://msdn.microsoft.com/en-us/library/bb833052(v=office.11).aspx

Answer 2

When inside a table object, the labels become more akin to field names rather than text lables and are treated as objects themselves. Therefore, can't have two named the same within the same object. Kind of like having two files in the same location with the same name - can't be done.That's the "why.

The poorman's work around is to deselect the header row check in the Table Style Options group of the Design Table Tools contextual tab. Then go and manually label the cells. The first time you sort, you must do so on the first data row. Thereafter, it works as you would expect. However, filtering is a different issue. If you need to filter, it will recreate the Col1, Col2, Col3 structure in a separate row underneath your labels. After you have filtered though, you can turn off the header row once again.

Answer 3

Another solution: Pick a table style with a white or black colored headder. After Excel creates the table and adds in the (unwanted) numbers go through the labels and highlight only the number. Change the color of the number to match the background. The number is still technicaly there but you no longer see it!

Answer 4

Another feasible option:

1. select the region to format as table, and Insert > Table
2. Make sure "My table has headers" is unchecked
3. Excel adds a line with headers
4. Go to Table Tools > Design and uncheck under "Table style options" the "Header Row" option
5. The unwanted header row disappears!

Answer 5

1. Click any cell inside the table
2. Select the Data tab and dis-select the filter button
3. Select the Design tab and from the Tools group click on Convert to range button

Answer 6

I know this isn't the correct way of doing ot, but I couldn't figure out any other way. I just changed the colour of the number that shows up in the duplicate column to make it match with the cell background (i.e. made the 2 in "DAY NUMBER 2" white so its not visible).