Free Microsoft Excel 2013 Quick Reference

I cannot drag a Pivot Table field into the page area

When I try to drag a particular field into the Page area, Excel tells me that
"the field you are moving cannot be placed into that Pivot Table area". The
data in the column concerned is text, so I can't understand it's not allowing
me to place it there. I've looked at the field settings and there doens't
appear to be anyhthing untoward there. The message box is next to useless at
explaining why I can't drag it into the page area (but I can drag it to the
Row or Column area)- does anyone have an explanation?

Post your answer or comment

comments powered by Disqus
When I try to drag a particular field into the Page area, Excel tells me that
"the field you are moving cannot be placed into that Pivot Table area". The
data in the column concerned is text, so I can't understand it's not allowing
me to place it there. I've looked at the field settings and there doens't
appear to be anyhthing untoward there. The message box is next to useless at
explaining why I can't drag it into the page area (but I can drag it to the
Row or Column area)- does anyone have an explanation?

I am creating a pivot table from an access database containing 896426
records. The issue I am running into is that on one of the fields the pivot
table is only pulling in one of two values, but the access table does contain
the second value. When I click on the arrow associated with that field it
does not contain the second value either. Is there somewhere else in the
settings this value could have been lost?

I created a pivot table and in the data area I have 4 months of sales date
Jan, Mar, Jun, and Dec.. When I pull down the down arrow for this data I see
all 4 months and I see box for each month and a box for all. When I look at
Jan, and Mar and not all I loose the other months. What is wrong with the

"Debra Dalgleish" wrote:

> AFAIK, the Data source option is only enabled for OLAP cubes. For pivot
> tables built from Excel data, the items are listed in Ascending order
> when the pivot table is created. If you set sort to Manual, you can drag
> the items to any position in the list.
> You could also create a custom list, and base the sort order on that.
> In a cell on a blank worksheet, type your list, then select it
> Choose Tools>Options
> Select the Custom Lists tab
> Click the Import button, click OK
> When you create a new pivot table, items should be in the order of the
> custom list.
> To sort the items in an existing pivot table, select the field
> button, and choose Data>Sort
> Click the Options button, and choose your custom list.
> Mike wrote:
> > Hi.. thanks for the response. The ADVANCED option under
> > FIELD SETTINGS gives AUTOSORT options of:
> >
> > Manual
> > Ascending
> > Descending
> > Data Source Order
> >
> > It's set at Manual, but the data is in Ascending order
> > even though the data in the original sheet is not sorted.
> > And, the "data source order" option is greyed out.
> >
> > I still cannot get the data in the same order as the
> > original sheet. Any reason why the Data Source Order
> > would be greyed out?
> >
> > Thanks again. Mike
> >
> >>-----Original Message-----
> >>If you right-click on a field button, choose Field
> >
> > Settings, and click
> >
> >>Advanced, that field should appear in the field list for
> >
> > sort order.
> >
> >>Mike wrote:
> >>
> >>>I cannot get some data fields in pivot tables to
> >>
> > appear
> >
> >>>in the "data source order". This option under advanced
> >>
> > is
> >
> >>>greyed out for some reason. So everything gets sorted.
> >>>Any suggestions?
> >>
> >>
> >>--
> >>Debra Dalgleish
> >>Excel FAQ, Tips & Book List
> >>
> >>
> >>.
> >>
> >
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List

My data has various dates in column A, which I group into months using the formula "=DATE(YEAR(A2),MONTH(A2),1)" in column B. Then I format column B to show the results in the format mmm-yy. So far so good.

I then use a pivot table to count the number of entries for each month and I have formatted the date column the same way. But every time I refresh the data (which I need to do frequently as data floods in from external sources), the format of the pivot table reverts to dd/mm/yyyy and I have to go and manually reformat it again.

I've checked all the options I can think of, but cannot find any way to keep the format as I have set it. Does anybody know of a way?

I'm using Excel 2003 on Windows 2000 Professional.

Thnaks in advance for help.

Hi there

I'd like to know if its possible to sum the column field in a pivot table
and display the result as a calculated field.

Sum of Total Region
Item Alberta Total(Alberta) Ontario
Binder 1279.36 4973.44 5762.63
Desk 825 4973.44 875
Pen 151.24 4973.44 539.73
Pen Set 4973.44 2421.39
Pencil 231.12 4973.44 1540.32
Grand Total 2486.72 4973.44 11139.07

Thank you for your help in this regard.


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,


I want to reference the contents of a pivot table 'Field drop-down list' as
a header on a spreadsheet.

Ideally i want to say select weeks 6,7,8,9,10 and have to boxes at the top
of the sheet saying 'Start week: 6' and 'End week: 10'

Any ideas




I've produced a pivot table to count the number of patients waiting in
various groupings. As excel nicely does, it's supressed repeated values when
there are more than one sub-categories within a main category...


Sub heading Weeks Wait No. of Patients
Colonoscopy 0 25
1 20
2 15
3 6

Is there a way I can tell the pivot table to show "colonoscopy" on every row
rather than supress the repeated value? I can't seem to find an obvious way
of doing it.

Any help would be great.



How do I go about calculating deltas in a pivot table? It doesn't seem that a calculated field will work.

Basically, this is what I am trying to do. I have created a pivot table to track the growth of my email subscribers. Now, I just want to include a column that will automatically calculate the delta (or email subscribers) from previous day.

Is this possible?

I have created a pivot table that counts the number of occurences of numeric
values between 4 and 16.

The pivot table contains the following

Value Count
9 2
10 1
11 1
12 7
13 4
14 1
15 1
16 3

There were no records that had the values 4, 5, 6, 7 or 8.

I have created a pivot/column chart to display the counts (y-axis) of each
value (x-axis). Because there are no counts for 4, 5, 6, 7 and 8 the scale
for the x-axis is 9 to 16.

How do I create a chart with a scale from 4 to 16?

(I've also noticed that if there are only records for 4, 5, 6, 7, 9, 10, 11,
12, 13, 14, 15 and 16 that the chart x-axis will not include an 8.)

I cannot get a pivot table macro through the macro recorder to work. I receive the error:

"Runtime error 1004, Unable to get the pivotfields proberty of the pivot table class"

The code is below, any suggestions?

Sub Pivot()
' Pivot Macro
' Macro recorded 8/7/2008 by e427032
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Item Summary'!C1:C27").CreatePivotTable TableDestination:="", TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Vendor Name")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Current Month"), "Count of Current Month", xlCount
ActiveSheet.PivotTables("PivotTable1").PivotFields("Count of Current Month"). _
Function = xlSum
Selection.Style = "Currency"
Selection.Style = "Currency"
End Sub

I am using a pivot table to count the date where an error has occurred. It counts the dates but the problem is that the date has a time attached to it therefore a single day may have several rows. I have formatted the table so it only sees the date but it is not working and does the same thing. i am also exporting this data to minitab if that helps any bit. I am using Excel 2003 and windows XP

Hi all,

First time posting,
I'm working on a projet for work to determine how many of each component is needed and when to order it.

I've created a pivot table to summarize the data and I'm wondering how to use a vlookup or hlookup to find data associated with a component number. As you know a pivot table puts the component number (reference) in the upper left then 2 columns to the right is the first datum point. Then sequentially lists them below so they are 2 columns over and 1,2,3,4 and 5 rows below the reference cell (component number). I attempted using offsets in conjunction with v/h lookups but I had no success... Anyone know how to use these funcitons together or know of another work around?

Any help would be much appreciated.

What we would like to do, if possible, is apply conditional formatting to a
pivot table field that stays with the field regardless of where the field
gets moved to on the pivot table as users move fields around while using the
pivot table for analysis. In addition, we'd like to find out if it is
possible to apply any formatting to a pivot table field where the formatting
is relative to the pivot table field and not to the cell that the pivot table
is in at the time the formatting is applied. Thanks in advance.

Does anyone know how I can create a pivot table to resemble the look and feel of a query table?

I have a colum where the amount of time required to make a particular part is
listed in seconds (this is the result of a formula subratcting the start time
from the end time). I have a pivot table to add the total amount of each
type of part time and the total time required to make the parts. I can not
get the seconds and minutes to add up to realistic numbers.

How can I add minutes and seconds and get a usable number?

i have a table from a survey with columns of favourite fish and age group,for example

Favourite Fish	Age
Other	50+
Other	50+
Salmon	26-35
Other	26-35
Trout	46-50
Cod	26-35
Cod	50+
Trout	26-35
Other	36-45
Trout	18-25
i have created a pivot table to show the count of favourite fish in each age group

Count of Favourite
Fish	Column Labels							
         Cod 	Other	Salmon	Seabass	Swordfish  Trout	Grand Total
18-25	116	280	84	21	24	84		609
26-35	151	392	95	31	29	133		831
36-45	128	390	99	37	35	100		789
46-50	47	142	35	14	13	49		300
50+	374	1067	280	77	73	332		2203								
Grand	816	2271	593	180	174	698		4732
But i only want to display the overall favourite fish in each age group. is there a way to display the max value from each column in the grand total column instead of the grand total?

Thanks in advance for your replies.

Hi All,

I am creating a Pivot table based on the following Columns of Data, Customer Name, AgencyName, Year, Spend.

When I create the PT I am using Year as the column set, and CustomerName and AgencyName (in that order) as the rowset. The data in the middle is the SUM of SPEND.

In cases where there is more than one agency associated with a customer I get:

Customer Name, Agency Name, Spend
Garry Customer Travel
Travel Agency 1 $1000
Travel Agency 2 $2000
Travel Agency 3 $3000
Garry Travel Total $6000

OK what I want to do is get rid of the row "Garry Travel Total". Is there a way to do this with the Pivot Table or would I be better writing a macro to delete all line with Total in the last 5 chars of each row?


Hi everyone,

I found an excellent macro this morning that allows the user to filter a pivot table based on the value found in a specific cell. This cell essentially acts as a search bar, allowing the user to type in what they are looking for rather than select it from a drop-down list.

The macro works perfectly for my purposes except in one regard: I can no longer perform a "show all" filter. If I leave the "search bar" cell blank, the pivot table shows nothing. I'm sure that there is an easy fix for this but I'm still learning the basics of how to write and use macros. How can I change the code so that when I leave the cell blank, the pivot table shows all? The search bar cell is D2.

Thanks for your time!

"The field you are moving cannot be placed to that pivot table area."
The usual helpful message.

This error happens when dropping a time stamp field into the Totals area.
This was working perfectly fine for weeks: it gave me min/max on call start times, counts and averages. The time field on the PowerPivot sheet does come in as
12/30/1899 11:39:38 AM
And I changed the format just to Time.
From the Pivot and I can select the times and I can dump it in Rows fields which I don't need.
But I assume Powerpivot still doesn't like this somehow.
How can I fix this?
I did not have any calculated fields for this so not sure what happened.

I'm putting some calculated fields into a pivot table but I can't seem to enter any more than two. It lets me walk through all the steps but then the field doesn't show up on the table. Any thoughts on what I'm doing wrong?

Thanks so much in advance.



I have created a system where users input data from a report into a spreadsheet, and after input it is sent off to various other workbooks depending on what was entered. I.e. The user is inputting test data on a location in a region. The regions make up the workbooks, and each location is a worksheet (While I can think of better ways to organise the data changing this is not an option here).

When the macro encounters a location in a region that hasn't been entered before it adds a new sheet based off the name of the location in the region's workbook, and creates a new named range for that sheet.

I'm running a pivot table in the central workbook, and I want to add the new named range to the page field when I create a new sheet. This is what I don't know how to do. EDIT: To clarify, the page field is choosing between which worksheet(via named range) is being shown currently.

Any help is appreciated

- Bockit.

I have a pivot table Field, with 3 listed items, referencing data from 3 separate worksheets - named 2004, 2005, 2006. ( i renamed them from the original item1, item2 and item3, to 2004, 2005 and 2006. I have now redefined the range in worksheet 2006 by using the Pivot table wizard and going one step back. I have now got 4 listed items in my Field - 2004, 2005, 2006 and item4. I cannot rename item4 to 2006 unless I delete 2006. But do you think it's letting me do that???? So how do I remove unwanted items in a Pivot table field list? Help please!!!!

Many thanks!


Hello All,

I have a form on one page that has a drop down list which the rowsource needs to be a selection from a field on a pivot table on a different sheet.

I think the best method would be to write the code on the form.activate section to put the pivot table field items into an array? then make that array the rowsource of the drop down list.

I think the method is good but I am having problems creating the array of the pivot items.

Any suggestions? am I heading in the right direction?

Allan Psaila

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