Pivot Table Help - Counting multiple items in a cell individually
Hello, I need some pivot table help. I’m using Excel 2010 and have scoured google for an answer without success. I currently have a table that looks like this (sample): Office Client ID Requested Funds Central X8345987 Mileage Parking Northern X435786 Parking Western U92345987 Mileage Car Insurance Central G3405695 Parking Mileage Car Insurance I have the office, client ID, and funds requested. In the funds requested column I used ALT + Enter to list multiple items in the one cell. The problem is, when I create a pivot table, this is what I get: Count of Requested Funds Column Labels Row Labels Mileage Parking Parking Mileage Car Insurance Parking Mileage Car Insurance Grand Total Central 1 1 2 Northern 1 1 Western 1 1 Grand Total 1 1 1 1 4 What I want is excel to separate the list items, and count them individually. For example, instead of providing a count of 1 for mileage and car insurance together, I want a count for mileage AND a separate count for car insurance. I want my pivot table to look like this: Office Mileage Parking Car Insurance Central 2 2 1 Northern 0 1 0 Western 1 0 1 Can anyone help me do this? Or is it even possible with a pivot table? Thanks!
Pivot Table new feature "Expand to month/week"
Hi I'm just noticed some new features related to Excel Pivot tables that are accessible from tghe contextual menu of a PVT (right clik). I understood the use of most off them but I'm not able to apply the "Expand to month/week". who know about that feature and can give me a example or an orientation. FYI : I'm currently using the 1801 version on Excel 2016 built 9001.2138
Pivot Table StDev calculates different value then the STDEV formula
Hi, I'm using Excel 2013 15.0.5153.1000 32-Bit and a pivot table to calculate StDev and compare quickly to the StDev calculated by the excel formulas. I noticed that sometimes the Pivot Table StDev function gives a different value compared to the StDev calculated by the excel formula. When i found this i compared the StDev of the pivot table with every existing stdev formula from excel to see if i can get a match with one of the 6 formulas. In the attached file you can see this comparison with both StdDev and StdDevp function of the pivot table. 12 times out of 50 the StDev doesn't match. The error is very small, only the last few decimals are different. Column J contains the values StdDev and StdDevp calculated by the pivot table, in columns K:P you can see the values calculated by the excel formulas. On rows 8, 10, 15, 16, 24 and 26 you can see that the StDev value from the pivot table doesn't match with any value calculated by the excel formulas. (Same thing for the StdDevp only the row numbers are different). I'm curious if anybody knows the reason for this. Did anybody met this issue before? I can't find anything on the internet regarding this.Grouping Date/Time Data in a Pivot Table not working
I am trying to follow the instructions as outlined here to group by hours: https://www.extendoffice.com/documents/excel/4822-excel-pivot-table-group-by-hour.html The issue is that when I right click and choose group, I don't see the date/time format although it shows in my Pivot table. How can I convert the data properly? Thanks in advance for the assistance.
Named Range, Pivot Table failure
Hello, I've come across a problem that I've not seen before and I can't find a fix anywhere. My source data is 31 columns wide and is bound under a Dynamic Named Range using the OFFSET, COUNTA formula. When I test the range on the source it's fine and goes from A1 to AE3483 without any problems; however, when I apply the range to a pivot table, it stops at column AB. As a consequence, data in the final columns does not appear in the list for my pivot table and I cannot use it. (For this task I must use a Dynamic Range as the source data is constantly being updated and I want to be able to easily refresh my pivot tables without having to manually adjust the ranges - because there are many) Can anyone offer some advice into why the pivot table isn't counting across all columns? Here is an example of the formula being used: =OFFSET('Dash Source'!$A$1:$A$500000,0,0,COUNTA('Dash Source'!$A$1:$A$500000),COUNTA('Dash Source'!1:1))
Cannot set default Pivot Formatting
Hi, I am trying to set my default Pivot Table formatting as can be seen in the support article here: https://support.office.com/en-gb/article/Set-PivotTable-default-layout-options-efd8569c-f07a-43c1-9db2-4f2912a0f94e I have the same issue as can be seen here: https://techcommunity.microsoft.com/t5/Excel/quot-Data-quot-not-an-option-under-excel-gt-file-gt-options/td-p/98447 Whereby I don't have 'Data' under the options. I have data set in the 'Customize Ribbon' and am running version 1710 build 8625.2127.
