I use cross-tabs quite a bit and there is one thing I find frustrating. It is when you have a perfectly laid out cross-tab and realize that the row or column field needs to be changed. Changing a row or column field tends to reset the formatting of the cross-tab cells and often changes the size as well.

One time when I was trying to change a cross-tab row from one formula to another formula I realized that I could avoid losing my formatting if I just copied the contents of each formula into the contents of the other, reversing the formulas. Once I had reversed the contents, I renamed the formulas so that the names matched the contents as before. The cross-tab didn’t see either of these changes as a field change and so the formatting wasn’t affected.

So, now whenever I create a more complex cross-tab, I created dedicated formulas for the row and column fields. I usually name them Xtab Row 1, Xtab Row 2, etc. That way I can change the contents to any field or calculation,   without having to worry about the cross-tab losing any formatting.

This is similar to the technique I called ‘feeder’ formulas in a prior article.