Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

Pivot table fields not showing

I have a spreadsheet and I have added new columns of data. When I create a new pivot table and the new columns are selected they do not show in the field list. Is there any way to fix this? Thanks for your help.


Post your answer or comment

comments powered by Disqus
Hi,
I have some pivot tables linked back to a SQL server via ODBC and they work fine. Recently some new fields have been added to the SQL tables but a refresh of the pivot tables does not display the new fields - ie they are not in the pivot table Field list display.However if i create a new pivot table then the new fields display. Is there anyway of refreshing my existing pivot tables to display the additional new fields in the Pivot table field list??? thanks

Hi All,

I have a spreadsheet that contains sales by sales rep. Its very simple, I
have the sales rep name and the total sales for the year, which is a simple
sumif from a data list.
But what I want is to create a macro where you select the sales rep ID and
click a button and a pivot table opens in a new workbook showing the sales by
period (months). I'm almost there the only problem I can not figure out is
how to set the pivot table field to show only the information for the
selected sales rep. I have tried recording a macro to see how to do it but
all I get is this

Sub Macro1()

With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee")
.PivotItems("Ian Smith").Visible = False
.PivotItems("John Smith").Visible = False
.PivotItems("Nat Li").Visible = False
.PivotItems("Vivien Soul").Visible = False
End With

End Sub

With the one I have picked not in the list hence the property would be
visible=true.
Is there any way I can get the macro to say hide all except the chosen one,
as the list of sales reps constanty changes.

All help is greatly appreciated

Naz

--

_______________________
Naz,
London

Hi,

Is it possible to delete Pivot Table fields? Not the ones taken directly from the Excel sheet table (that feeds data into the pivot table), but the secondary fields that were created, e.g. from grouping?

Thank you.

LY

Can I predetermine what order my pivot table fields results show up as
besides ascending or descending?
Thanks!
--
Jodie Gardner

I am using pivot tables which were created by another (no longer contactable) and have found two fields in the field list which are not part of the source data i.e. neither are column headings but are used in the pivot table.

My questions are:

1. How are these field created?
2. How can I examine these fields for formulae (as I have found one of them returns an incorrect value)?

Field1 return a numerical value which is the difference between two columns
Field2 shows this as a % (but incorrectly)

I have a pivot table that provides sales data by customer sorted by salesman and division. The report is designed so that sales data is sorted by a 4 digit salesmen/territory code. For example, the James company had 6,959,577 in budgeted sales. If I wanted to move that customer to another salesmens territory, I would change the 4 digit code for that customer on my data tab.

My problem: I have new sales/territory codes. For example D1 Smith was 1301-1600. Now I need to add 1900-2600. I have added the new codes to the data tab. When I calculate, the new codes are on seperate lines and not under the desired saleman. I do not understand how to add the new codes so that they will be under the respective salesmans territory.

My second issue: Some of my data is not showing up. I can drill down on totals and see it but it will not show up on my report.

A simplified version of my report is attached.

Hello

Can you help me with the message shown 'The pivot table field name is not valid'.

the field names appear normal to me, with no spaces.

It is very strange I have not encountered somthing like this in pivot tables.

Regards

kin

I apologise in advise for not being able to show the file (maybe someone can help me with that?), but I need help with a pivot table issue.

I have individuals that I track sales numbers for weekly.
Those weeks roll into fiscal months, months to quarters and "(All)" would be the entire year's information.
Each individual is part of a team.
The sales numbers are tracked by percentage, i.e., if a person's target is $1,000 for the week, and their actual sales come in at $1,500, they have attained 150% for the week.
In my data sheet, I enter the sales in Column T, the target in Column U, and the attainment % in Column V, which has a formula to calculate T and U.
In the pivot table, if I select a particular Week number from the Page Area, I can see what the attainment is for the week for each individual--no problem. But when I select Month from the Page Area, it totals all the data for the weeks in that month, which is okay for the sales and targets, but I don't want the % to be totalled--I want the actual % for the month of sales v. targets.
Is there an option in the Pivot Table Fields that I can use to calculate this?

Thanks for any assistance that can be provided.

I use both excel 2007 and excel 2003 with xp professional. when I do pivot
tables in exce 2007,the pivot table fields list is empty of field names.
There is nothing to drag to different areas of pivot table. I have tried
anything I could think of. However, that same .xls file works ok in excel
2003!

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...22120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

No question here, just some procedures for the archive.

Search criteria: fill listbox with items from a pivottable field, get
pivot table field values into a listbox, populate listbox with items
from pivottable, listbox values from pivottable, get listbox values
from pivot table.

The three procedures below demonstrate how to fill a listbox with
items from a pivot table on the active worksheet, modify the pivot
table with a selection in
the listbox and then "reset" the modified field in the pivot table
with all values being shown. The target field in the pivottable is a
field call DEPT that will populate a listbox named ListBox1 on the
active sheet.

1. The first procedure, SetupListBox1, populates ListBox1
2. The second procedure, ListBoxSelectionChangesPT, modifies
(shows/hides) DEPT items in the pivottable based on the selection
highlighted in ListBox1 (note with this code, it seems that you can
only select one item at a time in the listbox, I tried adjusting
ListBox1> Properties>Behavior> MultiSelect: 0 - frmMultiSelectSingle,
but this modification conflicted with the code in the procedure. You
may have better luck or insight.
3. The third procedure, PivotShowItemAllVisible, shows all the
previously hidden items in the first field of the pivot table (in this
case, DEPT).

Please note that I gathered these procedures from previous postings in
the newsgroup and modified the existing code just slightly for clarity
and run-time accuracy.

Sub SetupListBox1()
'Clears then populates a listbox named LISTBOX1 on active sheet
'With identified values from .PivotFields("TargetFieldNameHere")

Dim PF As PivotField
Dim I As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
.Clear
For I = 1 To PF.PivotItems.Count
.AddItem PF.PivotItems(I)
Next
End With
End Sub

Sub ListBoxSelectionChangesPT()
'Note for this procedure to work it seems that your
'ListBox1 Properties>Behavior>MultiSelect must be set
'to 0 - frmMultiSelectSingle. So it seems you can only
'select one item in the listbox to update to the pivot table.

Dim PF As PivotField
Dim I As Integer
Dim iVis As Integer
Set PF = ActiveSheet.PivotTables(1).PivotFields("DEPT")
With ActiveSheet.ListBox1
For I = 1 To PF.PivotItems.Count
If .Selected(I) Then
PF.PivotItems(I).Visible = True
iVis = iVis + 1
End If
Next
If iVis = 0 Then
MsgBox "Must have at least one DEPT visible"
Exit Sub
End If
For I = 1 To PF.PivotItems.Count
If Not .Selected(I) Then PF.PivotItems(I).Visible = False
Next
End With
End Sub

Sub PivotShowItemAllVisible()
'Shows all items in the FIRST FIELD in all pivot tables
'on the active sheet.
'For version 2000 -- show all items in field
'sort is set to Manual to prevent errors, e.g.
'unable to set Visible Property of PivotItem class

Dim pt As PivotTable
Dim PF As PivotField
Dim pi As PivotItem
Application.ScreenUpdating = False
Application.DisplayAlerts = False
On Error Resume Next
For Each pt In ActiveSheet.PivotTables
For Each PF In pt.VisibleFields
For Each pi In PF.PivotItems
If pi.Visible True Then
pi.Visible = True
End If
Next pi
Next PF
Next pt
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

I am having trouble creating a pivot table in 2007 and cannot figure out why. I am trying to create the simplest of tables but the data I am selecting is not showing up in the pivot table field list.

I used pivot tables quite frequently in 2003 so I am not new to the application itself - is there something I'm missing?

Thanks for your help.

given:
Excel 2003 Pivot table with a SQL Server OLAP cube (access via 'Microsoft
OLEDB Provider for OLAP Services' works fine)

problem:
Items (rows or columns) of the excel Pivot table are not shown as long as
the items (rows or columns) contain no data ! Typically this applies if
detailed items are opened within the Pivot table.
Excel Pivot tables allow to check 'Show items with no data' in the 'Field
Settings ...' dialog (window 'Pivot Table Field') to display the fields/items
(rows or columns) even if they contain no data. However Excel Pivot tables
with an underlying OLAP cube do NOT allow this setting (see
http://support.microsoft.com/kb/2347...2120121120120).

Question:
Is there a possibility (workaround), to display/show items with no data,
i.e. emty rows or columns in an Excel Pivot table even if the Excel pivot
table relies on an OLAP cube ?
Setting 'for emty cells, show ...' in 'Table Options' didn't help. Single
empty cells in rows or columns were shown correct, but empty rows or columns
that are completely empty are still not shown.

All hints welcome. Thank you for your help !
Timmo

When I select the data into Pivot Table (Excel 2007), pivot table field list is not appearing althogh pivot table is appeearing. What is the mistake (disable or enable) that I did?

Hi,

This one always seems to get me.

I have a pivot table setup like this -

I have 5 fields in my rows, and one field in my data section.

It is a simple employee pivot table -

The 5 rows are -

Department, Employee Name, Start Date, Term Date, Level and the data field is Salary.

I would like to sort the pivot table, so it shows -

The Department in Ascending order, than the Employee Names in Ascending Order, and then the salary (Data Field) in Descending order.

For some reason I can get it done.

Any help would be much appreciated.

Thanks,

Hi all,

I have a pivot table question and not sure if it's possible. Here is what i want to do.

I have a list of products and I want to show Orders and Forecast. For example I have 20 weeks of data, and week 1 -10 they are actual orders, but for weeks 11 - 20, it's forecast. I know I can just hide the actual columns, but what I really want is a pivot table that will show me only the actual orders from week 1 - 10 and then weeks 11- 20 show me the forecast.

Thanks in advanced.

Hello Everyone -

I have a request and I don't know if this is possible.

I have a list that I wish to use for a Pivot Table in my workbook. The list tracks occurances of the types of cases my company has and includes the following information:

Date (listed using the mm/dd/yyyy format)

Person Working the Case

The Case ID number

The type of Case

The Account number associated with the Case

I want to the pivot table to list each type of case shown in the list (this makes up the rows of the pivot table) and then show how many of each type of case we have in any given month (I would like a column for each month) that displays as such MONTH YEAR (i.e. April 2007).

The problem, of course, is that the pivot table wants to list every single date listed. This is not practical for my needs.

I need a function of sorts that will parse through all of the dates listed and give a total count of each type of case in the format I listed above (MONTH YEAR).

So, in other words...

I need to go from a bunch of dates that are shown in mm/dd/yyyy format like so: 7/15/2007, 7/1/2007, 6/24/2007, 8/01/2007, 8/01/2007, 8/13/2007 to calculate and total in the Pivot Table and read like so:

June 2007 - 1
July 2007 - 2
August 2007 - 3

Any and all help is greatly appreciated!

Hi all. I have never had a use for a pivot tabl before (not a sensible one, anyway) and now I need to produce one approximately 2 weeks ago.

I have the table up and running, no problem, but I would like to see the fields on the left hand side (in this case expenditure area) in a particular order. I have tried sorting the underlying data, but that doesn't appear to make a difference. What I don't understand is that Excel seems to put them in a random order - at the moment it isn't even alphabetical.

Is there a way to tell it that I want to see Salaries at the top, Rent underneath etc...?

Thanks.

Pivot table fields can be edited and renamed by mistake quite easily. Where
this has been done, I am having trouble re-writing the correct values to the
pivot table, despite the fact that the database containing the data, still
shows the correct data. I have tried refreshing the data, have looked at all
the pivot table options and tried changing the field settings without success.

I am using Microsoft Excel 2003 SP1

Pivot tables in Excel 2003 are all fine, except - I typed a date incorrectly
in the database and now the Pivot table retains this mistake when you click
the Field Name drop-down. The date was corrected, and I have refreshed all
the data to the database and have tried removing the date field from the
layout and adding it back again. Nothing works! Though no data is returned if
this erroneous date is chosen, I would like it not to appear at all as a
choice under this "Week_Ending" pivot table field. Any ideas or help will be
much appreaciated!
Thank you. Annie

I am trying to make the default of my pivot table to NOT show all values in
the data fields initially. By default it selects all the data listings right
now.
Thanks!

why? o why? o why?

I cannot add a Pivot Table to my excel sheet, because I keep getting the message "Pivot Table Field Name Invalid". It has an automatic name, not my fault (thanks MS-B.S.). If I select another sheet which contains only one list of information I can create one, but how useless is that, when I want the real power of this feature unleashed on the whole database (sheet). What is wrong with this function of Excel and how do I work around this cumbersome issue that MS has caused?

thanks in advance,
John

I'm MS-Excel 2003 pivot tables which connect to an OLAP database. I am trying to set my subtotals in the pivot table to "AVERAGE". Currently, they're defaulting to the "SUM" setting.

I right click on the cell (I do this for both the cell and the cell heading...same result) and select "FIELD SETTINGS". This brings up the Pivot table field window. The only options that I have that are not ghosted out are "AUTOMATIC" and "NONE".

I understand that I need to tick the CUSTOM radio button under the Subtotal heading, but i am unable to do so, as it's ghosted. Consequently the Sum/Count/Average/Max/Min... options are also unavailable.

This is the case with all of my spreadsheets that connect to this OLAP database.

Is there a global setting that can be reset on Excel? Or is this a setting in our OLAP cubes? Or is there something else?

Scott

i have base data in the format of mmddyyyy hhmmss that I'm putting into a
pivot table. How can I format the Pivot Table fields to show mmdd so only
one entry shows for a given day? 'formatcellmmdd' does not work


No luck finding an answer? You could always try Google.