Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

6 Answers 6

up vote 3 down vote accepted

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

share|improve this answer
    
+1, yes, Excel tables are much more than just formatting shortcuts and provide very useful ways of referencing the data within a table, with column names being an extremely useful method. Allowing multiple columns to have the same name would 'break' this feature. –  Mike Fitzpatrick Feb 9 '11 at 8:05

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.

share|improve this answer

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!

share|improve this answer

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!
share|improve this answer
  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
share|improve this answer

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).

share|improve this answer
    
You are just rephrasing the almost three years old reply of Laura. –  pabouk Feb 3 at 21:11

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.