Free Microsoft Excel 2013 Quick Reference

pivot table not showing field list

Excel 2003

For some reason, the pivot table functionality no longer works. When the
"show field list" button is toggled on, the field list isn't shown. When I
toggle this button off, the headers on the actual pivot table disappear.

this has worked previously.

I tried to repair and full uninstall MS Office 2003 already.

thank you

Excel 2003

For some reason, the pivot table functionality no longer works. When the
"show field list" button is toggled on, the field list isn't shown. When I
toggle this button off, the headers on the actual pivot table disappear.

this has worked previously.

I tried to repair and full uninstall MS Office 2003 already.

thank you

Dear Friends,

I am using MS-office 2007. When I created the Pivot Table, I don't know why the field list is not appearing. I reinstalled entire application also. Even I tried with the pivot tables which are created earlier also. But of no use.

Any sol. for show field list in VBA macro. so that I can activate it.

Thanks in advance.



i have a pivot table that shows the customer names. when i select the customer drop down box i can all the customer i want to see. but in the table itself there are some customer not showing. this is the first time this has happened

any idea please



I don't know how I done it. But I have managed to totally goober up my pivot table settings at a global level.

Start with a data worksheet and do Data | PivotTable and Pivot Chart Report... and just click the Finish button and you get a skeleton with the grey "Drop Column Fields Here", "Drop Data Items Here", "...Row...", "...Page..." and blue highlights around each. I don't know when -- but it must be recently, perhaps even this morning... I did something that turned off this behavior. Now I don't see the grey messages. Now I don't see the blue outline. Normally, if that were the case, one simple clicks on the Show Field List buttons and voilá. But neither on existing pivots in workbooks that have always been well-behaved and new pivots too, I cannot get the Show Field List buttons to work! Neither the default feller on the PT toolbar, nor the same button on the popup menu that you get when right-click the PT. Even more amusing? The button(s) are not disabled. If I click somewhere off the PT, then yes, the buttons disable. Click back on the PT and the button on the PT toolbar "enables". They just don't do anything.

Troubleshooting failures so far...

Under Tools | Options... the View tab: Show All on Object is selected. ~ on Edit tab the Allow cell drag and drop is checked.

I'm trying to peruse the object browser for properties that might impact this behavior. So far I've looked at [*]Workbook.ShowPivotTableFieldList (I had high hopes for that one)[*]PivotTable.EnableFieldList[*]PivotTable.EnableFieldDialog[*]PivotField.DragToColumn[*]PivotField.DragToData[*]PivotField.DragToRow[/list]The following code return TRUE's across the board... Code:
Sub CheckFieldList()

    Dim pvtTable As PivotTable, pfX As PivotField, strDragProps, strEnableds

    Set pvtTable = ActiveSheet.PivotTables(1)

    ' Determine if field list can be displayed.
    With pvtTable
        strEnableds = "Field List: " & vbTab & .EnableFieldList & vbCr & _
                      "Field Dialog:" & vbTab & .EnableFieldDialog
    End With
    MsgBox strEnableds, vbInformation, pvtTable.Name
    On Error GoTo ErrorHandler
    For Each pfX In pvtTable.PivotFields
        With pfX
            strDragProps = strDragProps & .Name & vbTab & _
                                        "Drag2Col: " & .DragToColumn & vbTab & _
                                        "Drag2Data: " & .DragToData & vbTab & _
                                        "Drag2Row: " & .DragToRow & vbCr

        End With
    Next pfX
    MsgBox strDragProps, vbInformation, pvtTable.Name & " - Field Drag Properties"

    Exit Sub
    strDragProps = strDragProps & pfX.Name & " «errors» " & vbCr
    Resume Next
End Sub

Sub UseShowPivotTableFieldList()

    Dim wkbOne As Workbook

    Set wkbOne = Application.ActiveWorkbook

    'Determine PivotTable field list setting.
    If wkbOne.ShowPivotTableFieldList = True Then
        MsgBox "The PivotTable field list can be viewed."
        MsgBox "The PivotTable field list cannot be viewed."
    End If

End Sub
Other failed tests:[*]Shutting down Excel[*]Rebooting[*]Toggle Events Off/On[*]Going into Table Options... for a PT and unchecking all options and then one-by-one re-checking them[*]Immediate Window: ActiveSheet.PivotTables(1).EnableWizard = true/false[/list]Code that builds PT's still works okay. But the only way I can now build a pivot interactively is to the wizard and click the layout button and drag fields inside the dialog box in the wizard.

Searched here and w/ Google and ain't seen anyone else w/ this prob so far.

So, I'm plum stumped. If'n anybody kin figure out what the heck I done, I'll be much obliged!

When I press the "Show Field List" button on the pivot table toolbar, nothing
happens. I am only able to add fields by going into the pivot table wizard
and pressing the layout button. Any thoughts on how to fix this or what I am
doing wrong?
Thanks, Flyer27

I have a macro that with the help from Excel Forum I was able to create a pivot table and add a column to show the difference between the 2 columns in the pivot table. The problem I am now having is that when I run the program in the full worksheet, the pivot table created does not show any row fields. However, if I select from the filter, I see all of the items listed but no data. If I run the debugger, then the pivot table gets created normally without any issues. Does any body have any ideas why this is happening?



Someone asked this question recently but didn't appear to get a solution, so
I'll ask again...

The pivot table show field list dialog box/window does not appear -- either
by selecting the button on the toolbar or by right-clicking in the PT. The
only way to change the PT layout is to start the wizard and then select the
layout button.

Does anyone know how to get the field list to reappear?

Your help is greatly appreciated!


When I press the "Show Field List" button on the pivot table toolbar, nothing
happens. I am only able to add fields by going into the pivot table wizard
and pressing the layout button. Any thoughts on how to fix this or what I am
doing wrong?
Thanks, Flyer27

Dear Microsoft,
Please help me solve the problem that happen to me when use Pivot Table
(Excel 2007).
I have a Pivot Table like this:.

AAA Infor. Count Sum
BBB HHH 23 256,962,305
NNN 23 256,837,275
Others 3 18,151,200
CCC HHH 206 3,255,545,500
NNN 52 902,285,045
Others 147 2,145,626,800
DDD HHH 22 497,000,000
NNN 6 126,000,000
Grand Total 482 7,458,408,125

In data field, when I make right-click on, it shows details form the result.

I would like to hide the fnction "show details" when right-click on data
field, how could I do this?

Please pay your attention that, I've already check out "Enable show detail"
in Pivot Table OptionsData tab. But, this makes Pivot Table not show detail
when right0click on data field. It still permit other users can enable this
function again.

So I'd like to protect this function by hiding this function. I don't know
how to do this. Please help me with this.

Thanks & regards,

Hi all,
I am creating a pivot table from region, which some of the rows are blank.
Is there any possibility that the pivot table not show the blank in its dropdown combobox?

Hello-my pivot table is not capturing all the data from my export file. Currently everything is checked but only showing 12 out of the 131 "items". please help.

I saved, closed, reopened.
Sorted, saved, reopened.


Hi all,

Has anyone experience a problem on a pivot table where the field list will not show no matter what you do?
for some reason I've manage to corrupt excel in such a away that when ever I log on to my PC it will not show a pivot field list for any excel file with pivot tables. (I have to open the pivot wizard to alter any pivot table layout)
not only that tho ....
everytime I create a pivot chart - the screen flickers like christmas lights on "LSD"...

I haven't change anything within VBA or toolbar settings ..... it was working fine one minute and now its not. (no virus/trojans either) everything else fine except my pivot tables

maybe I've come across a excel "bug"???

The only way I can fix this is to delete my profile from the PC and sign on again as a new starter - but this don't make sense at I can use the files at any other PC and all works ok.

a bit strange this one...


I am using a pivot table to get a list of unique values in a range of data (this was the best way I could think of). When I do this manually the rows appear straight away but when I do this as a macro it will not show the values unless I put something into the data section. Is there a way of making it show the rows if I do not use the data section?

This is part of the very messy code I have cobbled together so far. hopefully there is something I can just add to this to make it show the list.

Sub Setup()
Application.ScreenUpdating = False

Dim BenType As String
Dim rng As Range

Dim wks As Worksheet
Dim wbk As Workbook
Dim rnn As String
Set wbk = ActiveWorkbook
Set wks = Worksheets("All Data")
                    'Sets range ofdata to be used
                    Set rng = wks.Range("a1").CurrentRegion 'assuming that is where the top of block is
                    'Valids the Selected Data
                    If rng.Rows.Count < 2 Then
                        MsgBox "Pivot on one row of data does not make sense"
                        Exit Sub
                        'Sets the ranges of data and gives it a Name
                        rnn = "pvtsource"
                        rng.Name = rnn
                    End If
                    'Add a new sheet, as you can't put a chart of the current sheet as the
                    'pivottables datasource breaks over the web in IE.
'                    Sheets.Add
                    With ActiveSheet
                        'Name the Sheet
'                           .Name = "Charts"
                        'Start a Pivot Table Wizard
                            .PivotTableWizard SourceType:=xlDatabase, SourceData:= _
                                rnn, TableDestination:=Worksheets("Allowances &
Benefits").Range("B2"), TableName:="PivotTable1"
'ActiveSheet.Range("A65536").End(xlUp).Offset(5, 0), TableName:="PivotTable1"

    With ActiveSheet.PivotTables("PivotTable1")
        .InGridDropZones = True
        .RowAxisLayout xlTabularRow
        .ColumnGrand = False
        .RowGrand = False
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Benefit Name")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable1").PivotFields("Benefit Code")
        .Orientation = xlRowField
        .Position = 2
    End With
End With
Thanks in advance for any help.



Small but important problem:

I have a workbook that creates and manipulates pivot tables with various field settings. One of my fields is the "Queue" field- and I only display data in specific Queues. Therefore, the current code I have sets every Queue to false visibility. Then, another code sets only the Queue's I want to include to true visibility.

The problem is, sometimes the data I pull from does not contain all of the Queues- so my macro will crash because it tries to set a Queue's visibility to false- when that Queue does not exist in the data.

If there were some way of setting ALL the "Queue" fields to false visibility- without specificing each Queue- then the code would work no matter what. It would change all Queues to false, and then show the ones I want (The Queue's I need to be visible are always present in the data). Ideas anyone? Attached is the code.

I have one workbook with two worksheets. I am trying to figure out how I can make sheet 2,"which is a Pivot Table" not show dates under column titled, "Contact Due Date" which is being pulled from Sheet 1. I am going to use this column as a Query where dates will be picked but I do not want the dates to show on the Pivot Table column. Any ideas?

O/S Windows 2000
MS Excel 2000
RAM 768Mb
Spreadsheet size 44.8Mb.

I am attempting to use "Multiple Consolidation Ranges" option to analyse two excel lists held on seperate worksheets within the same workbook.

These tables have identical fieldnames.

There are 54K records in the first sheet and 23K records in the second.

Pivot tables on either List work, though these are understandably slow.

Selection of the Excel lists is OK, but when I attempt to set the layout I first get an out of memory error then Excel crashes.

This crash occurs before I get to the layout wizard dialogue box.

I don't think that this is a column field or row field issue, but I would be happy to be corrected on this.

Can anyone point to a possible solution?

If all else fails then I will use seperate pivot tables for each Excel list!

Thanks for reading this.


I have a pivot table which show plus and minus values in one of the fields. How can I filter the table to only show negative values?

My pivot table appears as follows:

................................................Sales Area
Al................Operating Profit.........-100
Al................Count of Stores.........10
Rick.............Operating Profit.........250
Rick.............Count of Stores.........11
Sally............Operating Profit.........-150
Sally............Count of Stores.........5
Bob..............Operating Profit.........60
Bob..............Count of Stores.........8

I want to only show Managers with a negative Operating Profit.

My first thought was to create a Calculated Field in the pivot table, however the field can't be placed anywhere other than in the data section so I can't filter by it.

Any ideas?



I created a pivot table which is pulling data from an external data source. Prior to creating the pivot table, I omitted the excel grid lines by using the "fill color" white on the entire sheet so it appears as a clean all-white sheet. However, once the pivot table fields are placed in the cells and are manipulated by either dragged down and pulled up to analyze, the grid lines re-appear evertime the fields are moved. Anyway to keep the entire sheet with a pivot table not showing the grid lines ? Any help is appreciated.



I'm having a problem creating a calculated field in an excel 2007
pivot. Here is the scenario I'm dealing with.

* The data this pivot table pulls from has daily sales for a given
* I've set up formulas to recognize if this sales data is from "This
Week" or the "Previous Week".
* I have a pivot table that shows me Sales for "This Week" and
"Previous Week".

I'd like to create a calculated field that will show me the difference
in sales from "This WeeK" and "Previous Week" but the only option I
have in creating a calculated field is to select the original "Sales"

Is there a way to create a calculated field that would show the
difference for Sales for one week to the next? I've run into this
often and usually throw up a formula outside of the pivot table that
can simply do the calculation but I'd rather be able to keep this
calculation within the table.

Any help would be appreciated!



Hi all,

I have attached the spreadsheet and highlighted the issue in yellow.

I have a pivot table that in my <Table Input> tab that is querying all data in my <Tracker> tab - it has:

Value: Incident Count
Row Label: Remedy Status
Column Label: Severity
Current Week: Filter

I want to also distinguish incidents by their 'Incident Category' (column V in tracker) but when I add it to the pivot table none of the Incident Categories appear.

(It should be showing each incident by category for my current week filter)

Can anyone help?


can I get a pivot table to show only data with the date field during
working hours e.g. 9am-5pm mon-fri excluding holidays



Have a table of sales people,

Want to set rewards program for top sales people based on n# of sales that exceed $X.

so for the source range that feeds the pivot table, I have a vba decision derived decision column title "Huge Sale" that is applied to each sale (does this sale exceed $x, if true then put "yes" in decision column, else "no").

Now, what I am trying to figure out, on my pivot table, I want it only to show salespeople who have had at least n# of "Huge Sales". Is there a way I can set a filter on my pivot table to show only row groups where Count of Huge Sales exceeds n?

If I can do that, user inputs what a huge sale is ($ threshold value) and how many sales before reward (# threshold). And the table would just spit out the sales people to reward.

Any ideas?

Current Row labels are:
Huge Sale (show "yes" only)

Count of huge sale


I am trying to pivot table an uneven flat list that looks something
like below . .

Rep Q2 Rep Q1 Rep Sales
id1 300 id3 210 id3 3432
id2 240 id1 190 id2 2390
id4 235 id3 177 id1 1920
id3 144 id2 168 id4 1700
id4 130 id5 900
id6 545
id7 455

I need a pivot table that appropriately shows

Rep -> Sum(Q1, Q2)
Rep -> Sales
Rep -> Sales/Sum(Q1,Q2)

Whenever I try to pivot table the above list,
it lumps the Q2 & Q1 numbers by row, i.e
it ignores the Rep id key for. adds 300 (id1-q2)
+ 210 (id3-q1) = 510, when instead it should
add 300 (id1-q2) + 190 (id1-q1)= 490.



Like the title says, pivot table is not updating . Yes, I know... I already checked the source data, and hit pivot table refresh, but it doesn't update.

The excel is hooked up with mysql database, using ODBC connection. I am pulling in raw data into a table, and then into a Pivot Table that is based of that table (for reasons, I am not pulling raw data directly into Pivot Table). Now, when I hit refresh, it pulls in the raw data, but even when I hit Pivot Table refresh, it does not give me correct numbers. Funny thing is, if I click at a number in pivot table, and it gives me the detail behind that number, that is correct. For example, real number should be 80K... but Pivot Table is showing 30K. Now, if I double click on that 30K, opening up another sheet that gives the numbers behind that 30K, the sum of those numbers is 80K.... but how come it is not showing 80K in the Pivot Table .

Have been trying everything... created another Pivot Table of the same data, and it was fine (but I dont want to do it again and again.. its for different users)....

Anyone has any idea whats going on? Will really appreciate any help.

Thanks .

Sorted out the problem guys.... .