Change Header names in DataTable (Structured References) - Excel 2007+

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-08-14
I have a large data table in Excel 2010. If I add a new column, it defaults the name of that column to "Column#". Is there any way to change the name?

So for example if I put a formula in cell A4 referencing cell D4 (which is part of a data table), the reference will become "=[@Column3]" for example. I want to change that to "=[@mycolname]"

Any easy way to do this? I'm comfortable with VBA but was hoping for a non VBA solution

Question by:m4trix
  • 2
  • 2
LVL 17

Accepted Solution

andrewssd3 earned 2000 total points
ID: 36920639
You can simply overtype the column headings and Excel updates any structured references to the new name.  Is this what you're looking for? - it seems too easy!

Author Comment

ID: 36920836
Oh, I have the column headers hidden, so that would explain why I didn't realize that... Lol

I think the only way to change them without showing the column headers is through VBA though...
LVL 17

Expert Comment

ID: 36920864
Yes - that's not difficult to do, but I'd need to know what your requirement was - for example how would you trigger the rename, what column, what new name, etc

Author Comment

ID: 36921140
It's static - a one time thing. I'm pretty proficient in VBA so it was no big deal. For anyone searching behind this, I simply created a loop that went something like this:

ActiveSheet.ListObjects(1).ListColumns(x).Name = "new_col_name"

where x is the column # you wish to rename

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a serious pitfall that can happen when deleting shapes using VBA.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question