50 questions
-2
votes
1
answer
60
views
How do I pivot distinct values in Excel (preferably PivotTable fields editor)? [closed]
I have a table layout with different number of text values for each id column. Now, I'd like to pivot them, so that each id gets a single row and each text value is put into a different column of the ...
- 3,262
1
vote
0
answers
29
views
How to use Interop.Excel to create multi pivot chart in C#?
foreach (var item in itemlist.Items)
{
if (item.PivotChartConfig != null)
{
var describedPivotChart = item;
var chartConfig = describedPivotChart.PivotChartConfig;
...
- 11
0
votes
1
answer
31
views
Is there an option to remove dimension name in front of column name
I'm using import data from the Analysis Services to excel . Is there an option to not show the dimension in front of the column name on my tabular table?
I will try search in properties of dimension ...
1
vote
2
answers
92
views
How to create Excel Pivot Table from a table with hierarchical columns?
In Excel spreadsheet, I have a table with columns: "Epic" and "Stories" and "Tasks". There is another column to enter hours for each task:
| Code | Epic | Stories | Task |...
- 2,326
0
votes
1
answer
277
views
How do I make a list of cells that contain text from a table and avoid blanks? Index/Match/Lookup options (no VBA)
I have a table in excel set up like so:
Procedure
Visit 1
Visit 2
Visit 3
Visit 4
Arm
X
Z
X
Brain
X
Y
X
Chest
Z
Z
Eye
X
Y
X
Z
I want to get be able to select information like so that I can filter to ...
- 49
1
vote
2
answers
590
views
How to convert Google Spreadsheets pivot table to Excel?
I have a Google Spreadsheet with the following data:
I have copied the data to Excel (office 365):
You can access the data here: https://docs.google.com/spreadsheets/d/...
- 77
1
vote
2
answers
233
views
Summary pivot table from matrix data
I have an excel file that contains a table formatted in this way (the example here below is just notional):
Table 1
Column A
Column B
Column C
Group A
Option 12
Option 33
Group B
Option 11
Option 15
...
- 83
0
votes
3
answers
159
views
In excel pivotal table count unique values
An Excel table contains information when a component of a service is down. So service [column A] (MyService) has a number of components [column B] (component-1, component-2, component-3). Each row ...
- 7,723
0
votes
1
answer
3k
views
Where can I find Pivot table option tab in google sheets
I am using Google sheets for learning Pivot table and cannot find the tab PivotTable Options.
Any help will be greatly appreciated.
- 1,455
0
votes
0
answers
960
views
How do I combine Excel pivot table Rows?
Say I have a basic table of data with 1 question asked twice (once before, once after to compare responses).
Question 1: Before
Question 1: After
WRONG
CORRECT
CORRECT
CORRECT
WRONG
CORRECT
I do a ...
- 395
1
vote
0
answers
2k
views
Transferring Pivot Tables & Charts without Source Data
I need to send the excel workbook tabs from "Combined" to "PPA HQ" that contain pivot tables and graphs, without the source data. Is it possible to do that?
I would like to keep ...
- 11
0
votes
1
answer
17k
views
XLookup on Pivot Table using pivot column headers/names instead of worksheet column letters
I am using xLookup to pull some data out of a pivot table. Currently my formulas look like this:
=XLOOKUP($A2,'RecT'!$A$5:$A$30,'RecT'!$C$5:$C$30,0)
But in my pivot table, column A is called Grade and ...
- 1,030
-1
votes
1
answer
1k
views
Excel not sorting numbers correctly on pivot table
It's as the title says, my excel pivot table is not sorting numbers corretly.
When I create the pivot table from my dataset, the goal is to have the data sorted by year, week number, and the Keys ...
- 61
0
votes
0
answers
194
views
Pivot table in excel contain duplicate data (not sure if it MS Excel Bug)
In Excel, I'm getting duplicate values one with a check and one with unchecked when I create a pivot table.(see the attached excel screen)
Please help me with this.
0
votes
1
answer
78
views
Unexpected Excel Pivot behaviour with SQL Server Tabular model (v2)
We use Excel from Office 365 with a SQL Server 2019 Tabular model
I have a "strange" behaviour of Excel Pivot regarding the level at which calculations are requested from DAX.
I create 3 ...
- 3,805
0
votes
0
answers
66
views
Unexpected Excel Pivot behaviour with SQL Server Tabular model
We use Excel from Office 365 with a SQL Server 2019 Tabular model
I have a "strange" behaviour of Excel Pivot regarding the level at which calculations are requested from DAX.
We have a ...
- 3,805
1
vote
1
answer
1k
views
Can I get the name of a single Field in the "Values" quadrant of PivotTable?
Preface:
I am trying to build a macro that relies on no absolute references.
Problem:
Part of my macro needs the name of the field in the "Values" quadrant of the active PivotTable.
The ...
- 131
0
votes
1
answer
158
views
Error When Trying to Filter Pivot Table With Between Dates Using vb.net
I'm automating a number of reports using a vb.net winforms application. I have one report where I need to update the filter on a Date Field to between 2 dates (Monday & Sunday or the week in ...
- 61
2
votes
1
answer
3k
views
How to solve comerror: PivotFields method of PivotTable class failed?
I'm trying to refresh a pivot table in excel and update existing filters using python's win32com.
However, I'm hitting the error of:
com_error: (-214735267, 'Exception occured.', (0, 'Microsoft Excel',...
- 154
0
votes
0
answers
56
views
Simply count values using Pivot Tables in Excel
Sorry to have maybe simple question, but I just learning pivot tables and some advanced functions in the Excel.
Let's imagine we having a table with Movies and Votes followed by User1 and User2 and ...
- 633
-1
votes
1
answer
157
views
Show intersection values across excel file
I have table with around 8000 rows .
This is just example of that :
I need to show the intersection values across the column Co. For example , I need to show a result like this :
To describe the ...
- 17
0
votes
1
answer
263
views
Pivot measure based on the pivot row label grouping
There is a table as shown in the pic
Is there a way to add a measure to the pivot created from the above data such that different error percentages can be used to different row label groups as shown ...
- 21
0
votes
2
answers
6k
views
How to filter one column in Excel Pivot table
I am trying to create a tunnel chart using data from Pivot table. However, I can't find a way to set a condition for one column ("Response Received Column). I want the table to count 5 people, ...
- 33
1
vote
0
answers
2k
views
openpyxl - update xslx sheet and preserve pivot table
I am working on an xlsx with two sheets: the first one containing raw data and the second one containing a pivot table. I cannot manage to modify the data-sheet without getting the pivot one in plain ...
- 11
2
votes
0
answers
161
views
MDM Cube browsing Fast vs Excel pivot being too slow
I have a MDM cube with fact having just 84K records. Simple STAR schema structure. All regular relationships and no calculations or KPIs.
While trying to browse the cube by pulling 15-20 attributes ...
- 21
1
vote
1
answer
37
views
Register Event for automatically generated Worksheet in Excel
When the user doubleclick on a cell in a Pivot-Table, Excel generates a new Worksheet with the Details. That is perfect. I am searching for a possibility to register a VBA-Event in this new Worksheet. ...
- 6,339
1
vote
0
answers
3k
views
Excel Pivot Table - Multiple headers with subheaders
Is it possible to create a pivot table with multiple headers, and each header has multiple subheaders? I've been trying to figure out but couldn't find a way on how to do it right.
This is my current ...
- 431
1
vote
1
answer
596
views
Default date aggregation for Excel
What is the default behavior of adding a date, time, or datetime into an Excel pivot row/column? I have seen it sometimes add it as the "raw value", sometimes it will add it as a Year > Query > Value, ...
- 112k
0
votes
0
answers
651
views
Is there any way we can read Data from created Pivot Table using java code?
I am creating pivot table using poi and save it into another sheet but after that i want to read the data of recently created pivot table but somehow i could't get it
For creating Pivot i am using:
...
0
votes
0
answers
23
views
multi tier pivot to handle tiers differently (SUM,MAX,SUM)
In my ODBC Excel File i have a pivot set up with 3 tiers:
Bin,StockCode,Date.
And NoOfDays as the value (always 1 per day/stockcode/bin combination)
the 1st and 3rd tier should be summing totals and ...
- 138
0
votes
1
answer
46
views
Display Company if Company Sells Two Specific Products
I want to know which companies sell apples and oranges.
My data set is as follows
Name Product
CompA Apple
CompB Banana
CompA Orange
CompB Apple
CompA Taco
And I'd like the resulting pivot ...
- 1,293
-3
votes
1
answer
301
views
Excel Pivot Table - how to arrange fields to sum a field while ignoring duplicate rows from another unrelated field
I hope someone can help me figure out what I'm doing wrong here - maybe it's my lack of Pivot table knowledge or even how the data is being modelled.
I am creating a report from an MS Access ...
- 3
0
votes
1
answer
4k
views
Excel VBA Macro Create Pivot Table with Dynamic Data Range: Source Data Range Error
I am new to VBA coding and trying to create an Excel macro which will create a pivot table based on a dynamic data range. I am using Excel 2016.
The number of columns in the data will remain the same ...
- 43
0
votes
2
answers
2k
views
In an excel pivot table, how do calculate percentages within a row of items within that row?
I am not trying to calculate a percentage as a percentage of the grand total. Rather I am trying to figure out what percentage of items within a specific row (or grouped category) meet a certain ...
- 731
1
vote
1
answer
3k
views
Apply a fixed value dynamically to a calculated field in a pivot table
I have the following Excel spreadsheet:
In Column C you can see the sales from the products in Column B. In Column A you can find the corresponding brand to each of the products in Column B.
Based on ...
- 5,565
2
votes
0
answers
499
views
C# Refreshing Pivotcache or RefreshTable is not refreshing the excel pivot table
I have a sheet that I update in C#. There is a pivot table using this sheet which needs to be refreshed. I am trying to refresh this Pivot table using VS 2015 and C#. I am doing the below and it doesn'...
- 161
0
votes
0
answers
2k
views
Exclude an option from Pivot table filter in Excel
I am having a pivot table and there are some filter on that pivot table.
What we did is the data source of the pivot table is populated by java program and upon opening the excel file, the pivot table ...
- 1,325
0
votes
0
answers
41
views
What is the incorrect pivot table logic?
In an excel pivot table, I am looking to count all items created after the exit date for projects. None of the formulas have worked in the calculated field section.Several attempts listed below.
...
- 263
0
votes
2
answers
79
views
How to automate excel task
This is an example excel data I am using. I have to lookup address age and preferred location with 7-8 different file every morning.
This is an example of the date which I receive to fill up.
| ...
0
votes
1
answer
3k
views
How to filter a Cubeset with a filter expression to calculate ytd Values
I want to filter a simple cubeset to calculate Year-To-Date Values in a Excel Pivot Datamodell
There are ways I tried:
with a From - To expression - this works but depends on the existens of the ...
- 138
1
vote
2
answers
34k
views
Excel Pivot Table IF Statement
What I'm trying to do is, I have on the sheet of multiple dates (report dates) in column A and a transition date column in column E. From B - D I have numbers in each column corresponding with actual ...
- 517
0
votes
1
answer
4k
views
How can I get the ListObject object of a PivotTable?
This question was originally posted as part of an answer by Hugh Seagraves on a related question. He "wanted to refer to a list object (a table) on one worksheet that a pivot table on a different ...
- 1,696
0
votes
1
answer
7k
views
Calculating the running average in a pivot table
I have a pivot table that has the following structure:
Row fields are month (number 1 through 12) and values (revenue, sqft, rev_per_sqft which is a calculated field). I also show the Running Total in ...
- 2,045
0
votes
2
answers
136
views
Filter 0-values in PivotTable
I have the following Excel spreadsheet:
A B C
Product Sent Quantity Returned Quantity
1 Product A 500 0
2 ...
- 5,565
1
vote
3
answers
13k
views
Sorting an Excel Pivot Table by a calculated field
I have the following Excel spreadsheet:
A B C D
1 Product Category Sale Margin
2 Product A Apparel 500 ...
- 5,565
0
votes
1
answer
180
views
How to create calculated column in excel pivot table?
I have 3 columns in my excel sheet - ID, Version, Material No.
for ex -
and i want the pivot table to be like -
How can i create the third calculated column because it involves two aggregation ...
1
vote
1
answer
76
views
5 tables in 1 pivot table relationship
I have 5 tables (each table has a Date column)
I want to make a relationship between all the dates of all the tables in the pivot table to do some data analysis.
But unfortunately, the relationship ...
- 13
0
votes
0
answers
162
views
How can I create a Macro in excel that will always create a pivot table using raw data?
I am trying to automate some pieces of my reports for work. I want to have a macro that will take all the raw data in a worksheet and create/design the same pivot table every time.
I have been able ...
- 51
1
vote
1
answer
460
views
Issues using mdx generated from PivotTable
I recently used excel to connect to analysis services and generate a mdx code that was used to query the cube. The code is shown below. The code was also verified using sql profiler. However when I go ...
- 125
0
votes
2
answers
43
views
Excel pivot condition cell
how can i set condition inside pivot sum data to refer label row ?
I need to sum data only if row label is equal to specific value.
I'm trying to use standard IF but it doesn't work.
Thanks.
- 7,020