I am having a problem with Excel Pivot Tables using 'old' field names for new data that has replaced older data. i.e. i will type over data in a column but on the drop down menu's the old 'names' still appear but the new ones to not'
I have had an extensive search and am familiar with (and use) the 'clear old items' macro. Whilst this macro does work it is not helping me as the items in my drop downs do not need to be cleared but their names changed to my new data. The only way i can seem to get round this is to delete and re-create the pivot table from strach - very time consuming.
Using Pivot Table, I added a field to the "Values" field in order to give me a sum of the numbers. The title automatically changes to "Total", I am needing it to show "Total Invoice to OOM Delta". Is there a way to do this? I tried the Active Field option, but that's not working for some reason.
I have excel 2007. My data sheet has columns for the sales for May 2009 and the sales for May 2008. Next to it is a column for the % growth between the 2 periods. When I put all this info into a pivot table and add the formula as below to the pivot table it shows the correct values for all the individual customers but not for the Grand Total line. Here it just shows a zero.
Can you also help so that the names on the data side of the Pivot table is the same as on your original sheet where it is reading the info from, and doesn't say "Sum of May 2009, but only says "May 2009".
Please show how you would work out the % Growth between the two different columns, as I tried it, and on the individual cell the data is correct but on the grand total line it shows it as zero and does not apply the formula.
In building my pivot table my data that I want to show in the column area is showing up as rows stacked on top of each other. In the column section I'm trying to show Total Budgeted Amount next to Total Actual Amount but on the layout it's showing the two stacked on top of each other is there some kind of hidden key that I'm missing?
1. I am trying to record a macro where I select a Pivot Table. But in the recorded macro "Pivot Table name" is not recorded neither the Pivot Field Property only the Range name is recorded. But on other systems (workstations) these details get recorded. Does this have something to do with excel settings?
2. I uploaded a macro enabled excel file on my company's "sharepoint" the drop down boxes present in the file get populated via a macro in "ThisWorkbook" page but sometimes these drop down boxes don't show any values. What can be the reason for this? Can't share the file because of data security policy of my organization.
why the Group Field option is not available in my Pivot Table. Other Pivot Tables in the same document, using data formatted the same way will let me format the data (the original data is in MM/DD/YY [h]:mm format). I have floored my data so that I can group by hour of the day, but it's not letting me do that.
I have two fields in pivot table, "HATA TOPLAMI" and "SIRA NO". "Hata toplamı" is sum of items, and "Sıra no" is count of items. İ want to divide these two fields and add it to the pivot table, like "HATA TOPLAMI" / "SIRA NO". I have tried calculated field function from pivot table. İ wrote "="HATA TOPLAMI" / "SIRA NO"". But the summary is false. I want to divide Sum of "Hata toplamı" and count of "Sıra no" but instead it divides with sum of "sıra no". Is there a way to do this?
I have a problem in expanding/collapsing fields in pivot table.
I have source table with GROUP, SUBGROUP, ACCOUNT and AMOUNT. In pivot table I've put GROUP, SUBGROUP and ACCOUNT in Row labels respectivly, and AMOUNT in values. There are same names in SUBGROUP for different names in GROUP. (for example: groups are Production costs and Distribution costs and in each of them there are subgroups Personnel costs and Other costs).
The question is: Is it possible to expand only field Other costs in group Distribution costs while field Other costs in group Production costs stays collapsed?
Ihave a pivot table that takes data from a table, groups the duplicate items and adds their values up - so it shows just one entry for each item. I now want to add a calculated field in there so that I can divide one of the fields into another.
I have attached a simple test file. The data tab has two columns in the pivot range. The third column is _not_ in the pivot range, but is there to show the result I'm trying to get with the pivot.
The idea is to count the number of people whose entry dates are greater than 2/1/2009. My third data column gives a correct result, but a similar formula in the calculated field of the pivot table gives a completely wrong result (a date), where my goal is to generate a zero, a one, or a sum of zeros and ones.
It could be a formatting issue, but that would not explain why all the pivot results have the _same_ wrong answer.
I'm new to PTs and calculated fields, and expect I've made a naive mistake that an expert could easily spot.
I have a pivot table with 2 row filters (dept and name) and then three columns - Year 1, Year 2. I need to add a calculated field inbetween Year 1 and 2 to show the variance in dollars from ( Year1-Year2) as well as an additonal a column that shows the percentage change between the tw o years . I know I can copy this over and add formuals in Excel but I need to retain the features of the pivot tables due to the 2 row filters?
I want to show the field name (i.e Surname) before the value is shown in the Pivot Table. for Example if the Field is Surname and the value is 'Smith', I want to show - Surname Smith
I've created a Pivot Table with 30+ fields. I've recorded the following macro to add the first field. I need modifying the code so that it looks for and adds every field automatically. It'd save a lot of time.
Code: Sub PTAdd() ' ' PTAdd Macro ' ' With ActiveSheet.PivotTables("PivotTable3").PivotFields("Assigned To") .Orientation = xlRowField .Position = 1 End With End Sub
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?
I have a pivot table that picks up the month an invoice was generated. Since I have several invoices for a few months in 2005, I would like to sum all the 2005 invoices into one column. I have the impression that I can insert a field within a Pivot table to sum all the amounts related to 2005.
I know in Pivot tbale we could add calculated field into the data ,but I fail to put the added in the "Page","Row" or the "Column". Eg. in the sheet of data,I have a column " DATE",but no column for year.Is it possible to add year in the "page" without adding anything into the "data" sheet?
I have 5 pivot tables which feeds data from a single worksheet. The data from this sheet change every month. Meaning, old data out (deleted) and new one's inn.
The problem I have is the old data I used in the Row Label (such as Customer Name) are still showing in all the pivot tables even though the record is no longer there.
I have two fields where users enter a security identifier (cell P1) and the declaration date (cell P2) and i'd like to be able to pull all the records from the master table (A1:K10) where the security id matches (column H) and the request date (column C) is <= the declaration date (i've attached a sample file for your reference). I tried using a pivot table but it doesn't let me filter for "less than or equal to" values for the request date.
I have a problem that I'm sure requires the most elementary fix. I have exhausted google on this. All I need is a drop down list for each value field. See picture attached.
I have a table with two columns: 'Serial' and 'Read Date'. The table represents all those serials that failed to read at a certain time and date. For example:
Serial Read Date 4028543 10am Dec 16 4028544 10am Dec 16 4028566 10am Dec 16
[Code].....
I know you can move them manually but I have 73 reads so far and that's a lot of manually moving columns about.
I'd like to get Excel to use the original order of the Read date entries.
Is there a way to modify Pivot Table so that "Sum of, Count of", etc is not shown. For example, have Table (& Pivot Chart) show "West Region" instead of "Sum of West Region"
I am trying to display text in the value field within a pivot table. I do not want a count or any number to display. What I am basically trying to create is a weekly schedule for nurses, with the dates on the top and times along the side, with "call type" and client as the text data to display with the value field.
I have attached an excel file with all the data on Sheet 1 and my attempt at a pivot table on Sheet 2. Perhaps there is some way to write a macro to display the data in a similar fashion, without using a pivot table?
Is there any way how you can change value in page field in pivot table using formulas? I know this can be done with couple of lines of VBA, but I need to create VBA-free excel file.
VBA code to remove "sum of" from a pivot table. It works on a regular pivot table. However, when applying this code to a pivot table created utilizing PowerPivot, the code does not work. Here is the code:
Sub ChangePTName() Dim pt As PivotTable, pf As PivotField, ws As Worksheet, i As Long Application.ScreenUpdating = False Set ws = ActiveSheet For i = 1 To ws.PivotTables.Count Set pt = ws.PivotTables(i) pt.ManualUpdate = True For Each pf In pt.DataFields
I have a PT with multiple data fields that I want to Rank individually and then give those ranks a weight percentage. Once I have that weighted rank, then I can sort PT items based on the rank.
Is there a way to do this inside the PT?
I.E.
Items are PNs
Data fields are NUMBER OF TRANSACTIONS, QTY SOLD, DOLLARS SOLD
I want to rank each field and then assign a weight to it. Then I can sort PT based on the RANK data field.
I have and excel sheet that pulls data from an external source (ServiceNow) via MS Query.
From the exported data, I have the following:
Date opened (MM/DD/YYYY HH:MM:SS AM/PM) Date closed (MM/DD/YYYY HH:MM:SS AM/PM) calendar_stc (this exports in seconds, such as 136) (this equals date closed minus date opened for each record) assignment group
From this export, I'm trying to make a pivot table to show the average calendar_stc per assignment group, per month, per year; but in hours, not seconds.
I thought this was an easy process, create pivot to group row data by years then by months. Then add in assignment group to to the Rows as well. From there, I added to the values section of the pivot the calendar_stc. The default, of course, is to sum each row. But I need the average so I switched it to average. Now it is showing the correct average calendar_stc in seconds per group. To make it hours, I figured I could add in a calculated field. To do that I added the formula of =calendar_stc/60/60 for the calculated field. When I hit okay, it's giving me the SUM in hours for each row. Modifying the field settings to average does not change the numbers. So I went back into the formula and modified =average(calendar_stc/60/60). And that is not working either.
Here is an example to show the math with my formula.
