pivot tables

We have volunteers who won't spend the money to get Excel. But our training comes from Excel things. For pivot tables, Calc does not have a Sigma Values to set up. What is the work around? Thank you. T2t

I have a spreadsheet that updates based on a user-entered date range. I'm running into two problems. 1. There are some values I need to exclude from the pivot tables. For example, the number of rows of data will change depending on the date range. I have index match formulas where I set the...

Good Afternoon everyone, I’m currently struggling with an excel feature. Whenever a pivot table is created and the pivot table fields open, there is an option under the fields column called ‘more tables’! I’m wondering how can I add that again. I updated my Excel to the most recent (2016) and I...

Work Day values in the Pivot Table do not match the work day values in the worksheet. Some entries in worksheet show less than a full day (e.g: 0.75 of a day vs. 1 day). I tried to use the cell format function the Pivot Table, but that did not work. How do I get Pivot Table to show values < 1...

Hi All, I'm looking for a way (if possible) to allow my filters in a pivot table be based off of an "if" statement or something similar. The data that i'm pulling from is a little complicated to write out... but here goes nothing. I work at a hotel and every night there is a snap shot of what...

Hello, I'm trying to automatically set the current year and next year in a pivot table using VBA. This is what I have currently Dim CurrentYear as Variant Dim NextYear as Variant CurrentYear = Year(Now()) NextYear = Year (Now())+1 With ActiveSheet.PivotTables("PivotTable1").PivotFields("YR")...

I use Power Query to massage data from a number of sources. It works great. There are cases where to create the right analysis that I use pivot tables. When I create the pivot table, I select External sources and select the appropriate Query as input. Everything so far works great. The issue...

Thanks in advance for anyone's suggestions. I will give feedback on any suggested solutions. Why am I getting a "Compile error: Invalid Qualifier" on "xlDataField" within the line: 'Add item to the Data Field to either Count or Sum. In this Case Sum With...

My goal is to highlight the cell when I have a filter active in the pivot table row & column labels. I have found the linked document below which shows me how to accomplish what I'm looking for in regards auto filters. What I would like to do is perform this same action in a way that functions...

Running 2016 Pro Plus. Have a large (about 110,000) row table that has also been loaded into the Data Model (no relationships established). Creating several Pivot Tables from the Data Model. Assumed that a separate PT cache was established for each PT. Created Slicers for each PT (using the...

Good morning! I have created a pivot table (round of applause for me please!) I have added additional columns to the raw data but the data is not showing in the pivot after refreshing. I believe this is because the initial area of the pivot doesn't include my last column. Could you tell me...

I am trying to graph the frequency of a series of cap rates but want to also show the average age of properties that have that cap rate. For example, the chart will show there are 3 properties with a 6.5% cap rate, 6 properties with a 6.75% cap rate etc. but I also want to see the average age...

I am using Excel 2016 and I have a userform (ComReport) that when clicked the user has two combo boxes to choose what they want to filter, Year & Quarter. Unfortunately I have not been able to get the combo boxes to fill. I have searched online and tried different tweaks of pretty much the same...

I have 3 calculated items in a pivot table - each just sums 3 or 4 other items from the field. Some of the items are summed in multiple calculated items. For example: 'Subtotal1' = 'Item 1' + 'Item 2' + 'Item 3' and 'Subtotal2' = 'Item 3' + 'Item 4' + 'Item 5' Twice now the formulas seem to...

Before I begin: I'm not a novice by any means, but I find myself lacking in this particular area of expertise. And because of that, I don't know exactly how to succinctly ask this question in Google or on this message board. There's a good chance it's been answered - or that the answer is...

I’m getting back into VBA after several years away. Refreshing my memory is harder than I thought. I’m creating multiple pivot tables from the same data sheet (“SOW Detail”), and inserting them one after another on “Sheet2”, with 3 empty rows between them. The macro recorder is not capturing...

I do a fair amount of Pivot Table Work and was trying to compare Pivot Table Add-in tools. I was looking at Contextures Pivot Power Premium https://www.contextures.com/xlPivotPremAddIn.html Excel Campus PivotPal https://www.excelcampus.com/pivotpal/ There are things I like about both, but...

I have a macro-enabled Excel file with many tabs on it. Four of the tabs contain Pivot tables that pull their data from one other tab. I have VB code that exports the five tabs to a specific location as an xlsx file. I make sure that the data tab is added first, then the four pivot tabs are...

Hi, I wonder if there is a way to filter/slice a pivot table in the exact same way that a timeline-slicer works, but with manually entering dates instead of marking them on the timeline. I want it to function as a slicer in a dashboard-worksheet. Like the "Date Filter -> Between" but as a...

On a pivot table I have categories of inventory and each one has a quantity on the sum. I want to highlight the quantity if it exceeds a limit but the limits are different. So if the sum of catalog codes for the category 'core balloon devices' exceeds 10, i want to be highlighted. But if the...