Excel 2007 :: Pivot Table - How To Display And Use A Field
Jan 26, 2012
I have a table of data which I am analysing in a Pivot Table. For the majority of the data, the Pivot works very well: however I have a small issue, but it subsequently means the Pivot is useless.
Within the table array that I am referring to, there is a column of data of "Days per employee for a given period". The rows of data within the table array relate to every absence entry per employee, but this final column of data always contains the same figure (although can differ from employee to employee).
When I put the data into the Pivot, I can summarise the absence(s) as a simple sum. However, this final column of data should not be summed, since it is already the sum figure.
This figure though needs to be part of the Pivot, since I need to report on the percentage of absence days per type over the given period. So, the simple representation of =absence day(s)/worked days does not work... well I cannot get it to work. Additionally I have tried features like % of, but nothing.
I believe that I am finally getting the hang of pivot tables and VBA ... pretty nice. Now for my latest frustration - calculated fields.
I have a pivot table created which compares two years of data. The problem seems to be that this data is from the same data field (PINSAL) even though it shows in two columns (year 2007 and year 2008).
1 - I need to subtract the 2007 figure (column C) from the 2008 figure (column D) in a calculated field called DollarVariance
2 - I need to divide DollarVariance into the 2007 figure to create a calculated field called PercentVariance
This seems easy to do if I had two different variables used to create the 2007 and the 2008 data but it is the same datafield. Can I use column letter? Can I use the column name assigned by the pivot routine (12 - 2007 and 12 - 2008)?
I have a set of sales data and need to create a run rate which is simply = Total Sales/Selling Day
Selling day changes most days.
I put the calculation into my pivot data but it's summing up, (instead on 12 I get 720) so I changed this to Average so I get the right figure in the Pivot but when I then use this field, it doesn't use the Average amount, it uses the summed figure.
I'm having an issue getting the correct results from a calculated field in a pivot table in Excel 2007.
In column A I have "Business Name", in B I have a sum of the amount of lines a customer has, and C is a MAX of the number of employees the customer has at their location.
In one example I have a business with sum 50 lines and max 30 employees but when I try to make the calculated field 50/30 (should equal 166%) i get 9.2% instead.
The reason appears to be because there are 18 types of lines the customer has and 18 x max 30 = 540 and 50/540 is 9.2%. In the attached example it's all the same business location so the total number of employees (30) is the same for each row and each row in the raw data is a set of lines with similar features.
I'm using Excel 2007 and am having an issue with grouping/ungrouping fields in pivot tables.
I have 2 separate pivot tables, both from the same named data source, but summarizing different data selections. Both tables include the date field, I am trying to produce both a daily and a monthly table, but whenever I change the grouping/ungrouping of the date field setting on one table, the other table changes to the same grouping.
Is there anyway to have one table with an ungrouped date field and one table with the grouped to month date field?
I have data sheet that has a field minutes. I a pivot table I have the Average Minutes per category. How it the pivot table can I show the Average minutes as Days, Hrs, Minutes? I am working in Excel 2007.
I have a pivot table with multiple row fields and multiple column fields. One of the column fields is a Date and I need some VBA that will auto-sort the columns into ascending order by the Date column field.
E.g., if the first four column labels are "2-Jun-2010, 13-May-2009, 16-May-2013, 17-May-2012" then i want the sort to arrange them as "13-May-2009, 2-Jun-2010, 17-May-2012, 16-May-2013".
Note: This is the left to right order of the columns i'm talking about, not the top to bottom order of the rows, or the data in the rows but specifically the column labels.
I've tried googling a solution and I can find a variety of code that deals with sorting the data in the rows in all sorts of ways, but nothing on how to order the columns.
i'm using excel 07, and the source data for the pivot table has the Date field formatted as custom "dd-mmm-yyyy". This can be changed if necessary.
I am trying to have duplicates and their corresponding data appear in my pivot table. Assume I have a list of over 1000 stores. Let's say store 101 appears twice in my data file.
When I create my pivot, the 101 only appears once and the data is consolidated. Is there a way to ungroup/display duplicates?
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?
I have a Pivot table showing costs of several types of items.
The issue is, the cost of an item is not in U$, it is in amounts of the unit value (example: Unit value of an item (cell A1) = U$500, and Item Cost (cell B1) = 2, the total cost is U$1000).
So instead of showing just the Item Cost (U$500) in the pivot table, I need to show 500*2 (A1*B1) which would be U$1000.
Can this be done in Excel 2010?
I don't want to create a new column with that multiplication, because I'm doing a monthly cost for the items.
I've created a pivot table and I'd like to index through each "value" in the page field and then copy the results to another sheet, one sheet per field returned.
I can't figure out if it's possible to index through the list though. Is it possible?
Any way to create a calculated field in an Excel 2010 pivot table that will find all the Transaction Types (Report Filter) with "transportation" in them and make the field Quantity 0 and leave all other quantities the same? I do not want the quantity of transportation added in twice and may not have the flexibility of adding a column to the raw data.
I used the formula below in a calculated field and it does not match the values using the added column to the data file.
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?
I tried multiple variations of searches for this, but can't seem to get this to work. I've been asked to perform some analysis on the number of direct reports for each manager in our company as well as the number of total reports. I have been able to calculate this with no problem. My difficulty comes with the request to be able to display this information in a particular way with Excel. (this is using Excel 2010)
What the VP would like to be able to do is to drill down on each manager to see all of their direct reports, and then, for any of those direct reports that are managers, be able to further drill down into those people's direct reports. The difficulty I am coming to is that, depending on the employee, there can be as many as 9 layers of management when I work this all the way up to the Executive Vice President Level.
My data is currently laid out such that my first 5 columns are identifiers such as employee id, employee name, job title and organization information. After that I have columns for the managerial hierarchy. For example first column of this (let's call it column H) is the EVP, the 2nd column (column I) is the Senior VP, 3rd column (column J) has the various reports to the senior VP, 4th column (column K) is the managers that report to those in column J, and so on, up to 9 layers.
For example, one of the employees in my finance area would have name of the EVP in column H, the SVP in column I, the director in column J, and their manager in column L. The remaining columns that could house hierarchy data are blank for them because of where they are in the hierarchy.
When I set up the pivot table, I placed each field of the managerial hierarchy in the row area and then the employee field at the end. The problem I encounter is that with all of the blank cells in the data due to the varying levels of the hierarchy, I get a lot of (blank) fields in the pivot table for any layer below where the person fits in (ie, for the example above, the EVP, SVP, and manager name come in find, but then it is (blank) on down to the employee name.) If I deselect (blank) in the field settings, it eliminates all of the rows that don;t go as far down in the hierarchy, i.e. I deselect (blank) at level 5, any employee that is 4 or less levels from the EVP does not appear.
I also tried doing the same as above but including the employee name in my hierarchy (so for the example above, the employee name would be placed in column M). This worked a bit, but then after every employee was the series of (blank) drill down options.
The ideal solution that I've been asked to come up with would be to have the data display as it does when I've set the pivot table up with the employee name in the appropriate hierarchy level, but not have the drill down (blank) show for those that don't have layers below them.
I have a pivot table in Office 2007. I want to filter the last column such that the values in the data area are greater than a certain number. But all those filter options are grayed out. The only option available (and working) is to select the top n entries. So clearly a reference into the data field to filter a column works, but why not by value?
1. Age (up to 20, 21-30, 31-40) 2. Gender (male, female) 3. Location (London, Paris, Berlin)
I want to see Age and Gender as column labels and Location as row labels. However, I don't want Gender as a sub-set of Age - I want Age Labels followed immediately by Gender labels ie:
Upto20 / 21-30 / 31-40 / Male / Female
I don't want: 21-30 Male / 21-30 Female / 21-30 Total / etc etc..
In Excel 2003 I created a macro that copied data from a table, pasted into a new sheet, created a pivot table, copied and pasted that data next to pivot table, sorted, copied and pasted into chart data. I've used this macro in Excel 2007 with no problems.
Now I'm trying to create a similar macro but it won't complete the pivot table. My Pivot Table Field List should have the Row Label and Values populated but when I run it all that is populated is the Values field.
Is there something in 2007 that will not allow me to create a macro using a Pivot Table or am I doing something wrong? The code is below if that helps ...
I have a pivot table which has a report filter. The report filter can have anywhere from 20 to 350 values. The user will be able to select multiple values from the listing to produce the table as needed. I would like to be able to display what values were selected in the report filter so that the user can see this information once the pivot table is rendered. Is there any way to display this information either above below or along side of the table itself?
I am using Excel 2011 for Mac, but I also have access to Excel 2011 for Windows. Either way will work. I have been able to get quite a bit of what works on Windows to work with the Mac.
I have a pivot table that summarizes jobs that need to be completed on a specified date. I need to put that information on a calendar, but I would like to concatenate all information from that date so I can do a vlookup from the calendar. My other problem is that the number of cells change for each date, so I cannot specify exact cells, (some only have 1 entry, but others may have 4 or more). Can I specify a range of cells based on the result of the pivot table?
I have attached the pivot table, calendar and the data sheet with the vlookup info. Am I using the proper calendar or method to find this info?I have struggled with this for over a week now.
I have created a pivot table using a family name in row labels. The names appear in alphabetical order unless the person's name is the same as a month or day of the week. The result is that I have Mr Sun and Mr May at the top of my list, rather than listed alphabetically. Sorting the list does not solve the problem. It switches Mr Sun and Mr May, but does not included them in the main list. Is there a setting I can use that will stop Excel 2007 thinking that these words are something that they are not?
I have a pivottable that has a calculated field returning a percentage of two other feilds. I have pivotcharted the result and now want to include a median of the calculated feild results on the same chart.
I want to use a pivot chart as it'll accomodate changes to the data range and different page fields.
I'm using Excel 2007. My pivot table seems to be limiting me to 256 columns in the Values/Data area. In researching below I believe that I should be able to have 16,000 columns in my Pivot Table.
[URL] The "Big Grid" and Increased Limits in Excel 2007
PivotTables Maximum rows displayed in a PivotTable report is 1 million. Maximum columns displayed in a PivotTable report is 16,000. Maximum number of unique items within a single Pivot field is 1 million. Maximum number of fields visible in the Fields list is 16,000.
I have a stripped down data source for debugging purposes. I only have 4 rows of data for test purposes. The dates are formatted as dates.
When I create the pivot table the dates become my column values. When I select the first date in the pivot table the Group By Field menu option is grayed out. I tried setting a tabular format but didn't work.
