Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table in Excel 2007 that I want to summarise, in a similar fashion to a Pivot Table, but for various reasons I can't use a pivot table.

I like the "Format as table" features of sort and filter buttons, automatic formatting etc, so have used that to create a simple table:

         A            B            C                    N 
 1 |           | 01/01/2010 | 01/02/2010 |  ...  | 01/12/2010 |
 2 | CategoryA |     15     |     545    |       |    634     |
 3 | CategoryB |     32     |     332    |       |    231     |
 4 | CategoryC |      5     |     234    |       |    644     |
   |    ...    |            |            |       |            |
27 | CategoryZ |      2     |     123    |       |     64     |

The numbers are retrieved from a "back-end" pivot table using GETPIVOTDATA().

All that works fine.

Now, the problem is that I can't seem to use formulas for my column headings in these new "smart" tables - they are converted to text or just broken.

For example if in B1 I put NOW(), I don't get the date, I get 00/01/1900.

Is there any way of getting a formula to work in the auto tables? Or do I have to use standard tables and manually alternate-colour my rows etc?

share|improve this question
I think its static text only for the headers. You could use VB to overwrite the headers though. – James Jan 11 '11 at 16:17

It is only static text for headers because they are named fields and therefore can be referenced. If they were dynamic, it would wreak havoc with formulas using them as references.

share|improve this answer

I am using Excel 2010, but this may work in 2007. If you turn off the Header Row in the Table Design, you should be able use a formula in the row above your table data.

share|improve this answer

If you use Table, Convert to Range it should keep the colouration of alternate rows but still allow formulae in the column headings.

share|improve this answer

Your Answer


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.