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.
I am currently working with a pivot table someone else created. The data set has dates only, in the correct format. The slicer has the dates categorized by month and excludes the dates in which there was no data.
Here is my problem: I added a few new months of data. Everything is calculating just fine in the Pivot table. However, my slicer where I should be able to pick the month now has these months listed as all their individual dates. I want to put the December dates under "December" and so on. The data that was present before I loaded anything additional still has this feature.
How do I group these dates, to be able to them as months? By the way, the grouping function is grayed out.
I have a database of roughly 9000 rows. My first sheet is this data in a raw format (Masterdata!). Each row is a fire-rescue response call and therfore has a time stamp. The raw data uses different formats to provide the time stamp. My second sheet is my filtered data (Filtereddata!). In order to make these time stamps uniform I have a formula that looks at the Masterdata! timestamp column and if it is in a numeric format changes it to a text date and if it is a text date perserves it.
=IF(ISNUMBER(Masterdata!K:K),TEXT(Masterdata!K:K,"dd/mm/yyyy hh:mm"),Masterdata!K:K)
This gives me the "timestamp" which has both the incident start time and the date. I have =left and =right formulas breaking apart the date and the time. Therefore I have two columns on my Filtereddata! for the date of the incident and the time the call came in. What I would like to do is run some pivot tables and group incidents by month. I am unable to group them once I run the pivot table. I get a "Unable to group these items" (or something like this) message. I have assured multiple times that the date column is in DATE format. However, when I change the date to long date or short date I see no changes in the column so I am assuming there is something happening that I'm missing. The formula populating the date column on Filtereddata! is as such:
=LEFT(frfiltereddata[[#This Row],[Timecode]], FIND(" ", frfiltereddata[[#This Row],[Timecode]], 1))
One cool feature of 2010 is ability to (right click on dates) and Group them into Quarters.
How to change the Quarters to be in line with e.g. Financial Year, as opposed to fiscal years? i.e. have a start date of say June?
I have date fields as column labels in a pivot table. When I try to group them I'm only given the option to group by integers and not by months, years etc.
I've had a look at the format settings of the column and they are formatted as date fields.
I prepared a Pivot Table and then i add some data in it but now all data is not showed by Pivot Table.
I do all necessary things like refresh and all but unable to increase a range.
How to increase range for Pivot Table in 2007.
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?
View 9 Replies View RelatedI cannot remember how/if I can ALWAYS exclude blank results from my pivot table.
I'm hoping i don't need to use a macro, it should be in the settings for the pivot table, I hope...
I use Excel2007.
In my data, I have:
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..
Is there a way to specify this in a pivot table?
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 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?
View 4 Replies View RelatedI 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 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.
using xl2007
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 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 am using excel 2007.
In Excel 2007 is it possible to filter the "Values" in a Pivot Table?
I am tracking the Gross weight of shipping containers. My table sums the weight of all items in a container by container number. So my rows are 9 digit container numbers and my data values is a Sum of part weights. I want to be able to filter out containers above a certain weight.
for example:
Row Labels Sum of Gross Wt. - Lbs
10003150588929
10003153258700
10003155984958
1000315651530
10003156549761
I know I can simply copy and paste into a new tab and sort it there, but I'd like to be able to do it internal to the pivot table if that is possible.
Using Excel 2007 I have a pivot table that counts that number of incidents based on month and year. To get the month and year I group the date field as months and years.
My problem is if I want to filter specfic months in say year 2012 it also takes out the month in 2013. I though it used to give you the option of year and date in the filter but mines are 2 seperate filters.
I am using Excel 2007 and my version of visual basic is 6.5.
I am baffled by the behavior of this code to manipulate one of my pivot tables. I am trying to set all but one of the pivot items in one of the pivot fields to not visible. Because there is a large number of items, I wish to suspend all automatic updates until all items are properly set to visible or non visible.
------------------------------------------------------
Sub SwitchBoards()
Dim BoardNew As String
Sheets("Board Parameters").Select
' Make sure we get the right value.
[Code].....
The MsgBox returns with: "Manual update is set to False" right after the instruction to set it to True!!
I am trying to create multiple pivot tables from the same pivotcache using VBA. The data range is approximately 270,000 records with 100 columns. When I run the macro, I get "Run-time error '-2147352567 (80020009)' Method 'Add' of object 'PivotCaches' failed". Is there a size limit on the data? If there is less than 65,000 records, it works great; if I use a wizard, it works with no problems with the full dataset. If I bring the data in as an Access table, it can work with some tweaking of the code, but the size of the file is huge because each pivot is taking its own snapshot of the data. I also can only create 3 tables before it runs out of memory, so I have to stop, save the file, and open it back up to create 3 more pivots.
Dim WSD As Worksheet
Dim PTCache As PivotCache
Dim PT As PivotTable
Dim PRange As Range
Dim FinalRow As Long
[Code] .........
The last line is where I am getting the error. I am running XP, Excel 2007.
As I said, if I import an Access table, I can use the code:
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table_Pivot_test.accdb[#All]", Version:=xlPivotTableVersion12). _
CreatePivotTable TableDestination:="", TableName:=txtPivotTableName, DefaultVersion:=xlPivotTableVersion12
And loop, but the final file size with 14 pivots can be 500MB! I have tried both "Create" and "Add", but they both give me errors.
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 looking for a way to extract all of the unique items in a Pivot Table. For example, I have a Pivot Table that has category A items and category B items. There may be several B items to one A. For example:
A0
B1
B2
B3A1
B3
B4
B5
Now, what I need is a list that looks like this:
A0 - B1
A0 - B2
A0 - B3
A1 - B3
A1 - B4
A1 - B5
Formatted so that those are two columns. However, in Excel 2007, I cannot set the option to have it repeat the A series items in the Pivot table. Is there some easier way to do this?
Excel 2007
No empty rows
No empty cells
No calculations in cells
Field names look fine - no punctuation no merged cells
If I select one column - any single column I can generate a pivot table but not with multiple columns
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.
So what I need is Total Sales/X
X = Cell Reference
I am using Excel 2007. I have a population that I used to create a pivot table. I am currently double clicking on the value cells to create worksheets of only particular "row label" categories. I am then copying the "row label" information into the newly generated work sheet name tab. This works fine when I only have a few "row label" categories to do but it is tedious if there are many categories.
Is there any way to automate the creation of work sheets for all row label values and also naming each work sheet tab with it's respective row label information. Here are images of the pibot table and the type of work sheet I would like for wall row label values.
I have a pivot table with dates along the x-axis. The data is spread out over 5 years and the spacing between sampling events is not even, therefore, the spacing of my x-axis dates is not even. I want the x-axis to look similar to a normal graph where there is equal space between each month, even if I don't have data for each month. I do not want to group the data to accomplish this. If I click the "show items with no data" it only shows one extra day, not all days or even all months. I have excel 2010.
View 2 Replies View RelatedExcel 2007 crashing when trying to up date a data source within a pivot table?
View 3 Replies View RelatedI'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 attached an example. CalcFieldProblem.xlsx
Scenario: I have a worksheet full of ~360 tasks. Tasks are broken down into phases, have owners, and have status'. I leverage a pivot table to easily sort my data by owner, or by status. When reviewing the pivot table with others we want to update the raw data. This entails changing worksheets and scrolling to the task in question.
I'm looking for an easier way to update the raw / original data.
Options I've tried to pursue but have come up empty:
Option 1: Excel modification? Is there a setting in Excel 2007 that would allow me to change a value in my pivot table and have it propagate to the original data set (another Excel worksheet)? So far the answer seems to be "no". Is this possible?
Option 2: Hyperlink from Pivot Table to Raw data field.
- I cannot create hyperlinks within PivotTable data cells.
- I have used the "hyperlink()" function OUTSIDE of my pivot table, along w/ a "match()" on a key field in my pivot table to create hyperlinks back to my raw data... But this is prone to breaking.
- Is there a way to enable hyperlinks from within a Pivot Table? I have seen other requests from individuals that had a pivot table with actual hyperlinks (e.g. [URL]...) and they could not 'activate' them.
Option 3: Tool Tip VBA Form w/ Link?
- If I can't create hyperlinks directly in the table, then how about a function so then when i click (mouse over?) a pivot table cell a form appears with a hyperlink back to the source data?
Option 2 and 3 Assume that there's an way to get at the underlying location of the raw data (e.g. "Pivot Table Worksheet!E10" comes from "RawData!C4". I've dug through some VBA documentation at Microsoft.com but came up empty. I know this link must exist behind the scenes - In Excel 2007 when you mouse-over a pivot table cell Excel displays: "Row - Phase - Owner - Status", which is directly out of the raw data table/worksheet. [[This was an incorrect assumption... When i looked at the pivot table tool-tip closer I realize it was just walking through my data :'( row.
What is the VBA Code to revel the source cell? If i can get at that data, (and hyperlinking can be turned on w/in the pivot table...) then I can easily write a script to walk through the pivot table and create links back to the raw data... Not a perfect solution - but it takes away the need to search through the source data for the row I want.
Is there a way to freeze your pivot table format after clicking for a ' Refresh'. I'm getting tired reformatting my column headers to wrap or in the middle etc. I'm working in Excel 2007.
View 6 Replies View Related