Formatting Pivot Chart Axes

[nicruzer]

I'm attempting to show the day value of a date on an axis of a pivot.

I have formatted the day in both the source table and in the pivot table; however, when I create the chart based on the pivot table, I get the original date showing in the x-axis. I want "Mon, Tue...." to show up instead.

My goal is to analyze our revenue grouped by day of week. In other words, based on our revenue, we will be able to tell which days are our busiest.

Any ideas?

[Asala42]

Hey,

Go to your X-Axis properties and hit the number tab. Then select a custom format and type in the custom format: ddd (or) dddd

Adam

[nicruzer]

Please forgive my ignorance.

I cannot find the "x-axis" properties tab anywhere in Excel 2000.

Where will I find this?

[Asala42]

no problem-o,

Double click on your chart. Double click on the X-Axis (that should kick up a "Format Axis" window). You should then be able to see the "number" tab where you can select various formats.

Adam

[eliW]

Add A column of DAY near the column of DATE and put in the formula: =WEEKDAY(A2,1)and copy down to last row.
DO your PivotTable on DAY and SUM Revenue
Eli

[ This Message was edited by: eliW on 2002-05-20 12:14 ]

[nicruzer]

The only tabs I see in the resulting dialog box are "Patterns, Scale, Font, Alignment," in that order.

I pulled up the same formatting box on the y-axis only to find the number tab included in that menu. Could it be that Excel automatically drops the number tab on my chart because the field in the orginal table is calculated and formatted?

[Asala42]

Hmm..

You're in the right place, given the tabs you read off. You're just missing the number tab for some reason (I can't seem to get rid of it to duplicate what you're seeing).

What kind of chart is this? What version of Excel? You might want to open a blank file, toss some junk data in and see if you can reach those options.

Anyone have any idea why the Number tab on a format axis chart might not be there?

Adam

[nicruzer]

I am using Excel 2000, and my chart is based on a pivot table.

Why wouldn't the original number formatting carry through all the way to the chart from the raw data?

[Asala42]

Well,

I'm getting the same results but only with a chart based on a pivot table - weird that this appears to be normal excel behavior.

Sorry I couldn't help further.
Adam

[nicruzer]

I found a workaround to this problem.

First, I took a look at the Microsoft Knowledge base to see if any articles address this issue. None of the articles I found directly address this; however, I did find an article that clued me in to the ultimate "solution." The reference number is Q214021.

Second, I deleted the old chart and pivot table on which the chart was based. I recreated the pivot table/chart, and in the process of creating it, I set certain options in the "options" dialog of the 3rd step in the process. Because I didn't take a scientific approach to the solution, I couldn't say which one solved my issue. Nevertheless, the options I checked were "Merge Labels," "Preserve Formatting," and "Set Print Titles." My guess is that "Preserve Formatting" is the only one that affected the ultimate result.

Finally, I came across one more interesting little detail that could have affected the final chart. While setting my fields for the pivot table, I double-clicked on the "DayOfWeek" field which I wanted formatted. I knew this would pull up the formatting options dialog box for the field. That dialog box had a "Number" button where I could set that custom formatting. I mention this because this option did not appear on my first round through this pivot chart creation!

Thanks for all of your help!