# Questions tagged [pivot-table]

A Pivot Table is a table of data that can pivot or rotate around a specific point in that data, whether it be a data type or category.

643
questions

**0**

votes

**2**answers

22 views

### How to index date values into columns in Excel

I have the following table in Excel 2016:
ID Type1 Type2 Type3
1 01.03.2018 null 01.02.2019
2 01.04.2018 01.05.2018 null
3 null ...

**-1**

votes

**0**answers

23 views

### Excel doesn't print all active sheets to PDF

When I select sheets in a macro-free workbook (no code involed!) that each may or may not span multiple printed pages, select print to PDF (using Microsoft Print to PDF, selecting "Print Active Sheets"...

**0**

votes

**0**answers

15 views

### How to analyse correlations in Ms Excel

Price change % is my column of interest. I would like to measure whether or not there are correlations between Price change % and any of the other columns. What visualisations would be best for this?
...

**0**

votes

**1**answer

49 views

### MS Excel. How to filter a pivot table based on input range?

I know that I can go to Row Label filter –> Value Filters –> Greater Than etc. However, I'd like to have two input fields, From and To and I'd like for the pivot table to only display values within ...

**1**

vote

**2**answers

35 views

### MS Excel. How to get around pivoting data that has blank placeholder columns?

I've been asked to produce a dashboard based on a data set that looks something like the screenshot below. The dates are the column headings and there are blank and placeholder columns in between each ...

**0**

votes

**0**answers

31 views

### MS Excel. How to analyse dates when they are heading names?

I've been given a data set that consists of hundreds of Stores and almost a hundred dates, below is a small sample. The issue is that dates (mmm-yy) are actually heading names. The reason I see this ...

**0**

votes

**0**answers

16 views

### MS Excel. Slicer alternative when dealing with hundreds of values

I'd normally use a slicer but in this instance I have almost 300 options and a slicer won't be very user friendly.
Can someone please suggest an alternative?

**0**

votes

**0**answers

21 views

### MS Excel. Does it make sense to represent data in such a way?

I have a data set that looks something like this. At the moment, data is sorted by region, and at the end of each section there is a row for Region averages for all the months.
However, I'd like to ...

**0**

votes

**1**answer

23 views

### Excel Pivot Table - show additional info about rows

I have a large table/data set (30k rows and growing). It lists individual actions taken on a wide range of documents.
Each document resides in a site and in a library within that site. I need to ...

**0**

votes

**1**answer

31 views

### Can I use conditional formatting in an Excel PivotTable to format cells based on specific fields?

I have the results of numerous tests run on several sub-samples. Many sub-samples make up a sample. Between 5 and 10 samples make up a lot, and I have five lots I'm trying to analyze.
Each one of ...

**0**

votes

**0**answers

25 views

### Excel PivotTable: How do you calculate the difference between a two columns in a multileveled PivotTable?

So say I have a pretty simple PivotTable, with reach record having Month, Type, Lodging and Lodg. Tax.
My records are uniquely organized by Date and by Type (with Types being "Estimate" and "Real").
...

**0**

votes

**0**answers

14 views

### Excel PivotTable: Collapsing/grouping a multilevel column

Hopefully this is pretty straightforward.
I have a table with Date, various expenses, and Type. Type only has two possible values: "Estimate" and "Real".
In my PivotTable, it is organized as:
...

**0**

votes

**1**answer

32 views

### Pivot Table Average summary returns error

I'm creating a very simple analysis of attendance at my membership organisation meetings. We have a meeting register in excel with 3 columns:
MeetingDate
Person
Category (member or visitor)
So I ...

**0**

votes

**0**answers

7 views

### Adding to source data with multiple pivot tables

I have made multiple pivot charts from one.
How do I get them all to update when I am adding a new month's information to the source file? On a scale from 1-10, I am a 5 at Excel.

**0**

votes

**0**answers

10 views

### How to hide Copy option in Excel pivot which have connection to SSAS

I Have pivot table excel, which have connection to SSAS sql server,
for security reason, i want hide COPY option in that pivot excel,
so user just can see and analysis data, but not COPY.
can you ...

**0**

votes

**2**answers

158 views

### How can I combine a column with a row to generate new columns?

I have data stored as an excel file taken from the U.S. Energy Information Administration. The below is a snippet of that data. I want to transfer this to a GIS-usable format. As such, I want to ...

**0**

votes

**2**answers

37 views

### Microsoft Excel 2016 - Pivot Table is Grouping Dates into only the Month of the Year

Our office recently upgraded to MS Office 2016 and I am used to doing all of this stuff in Python and SQL. Several people still prefer an old excel report that I need to update once a month. I am ...

**0**

votes

**1**answer

32 views

### How to make a DAX measure NOT to re-calculate on aggregated levels?

I have a DAX measure which works perfectly on the level 0 POV in the Pivot Table but I was wondering if there is a way to make it aggregate like a regular number when filtering to top level POV (e.g. ...

**1**

vote

**1**answer

29 views

### Replace Static Table Data w/ External Source and Update Pivot Automatically (Excel)

st tab is a table with data manually pasted from a file
nd tab is a pivot based on the 1st tab
Is it possible to replace the 1. st tab with an external data source (load-in the file without pasting ...

**0**

votes

**0**answers

8 views

### How to Get Drill Down Sheets to Display Only The Matching Values Their PivotTables Sum Up?

I find PivotTable drill-down sheets are displaying excess data, and I want to see only the appropriate records that match the cells that I've double-clicked.
My Situation
I've got an Excel ...

**0**

votes

**0**answers

22 views

### Excel 2016 change pivot table analysis services data source erases the pivot table

We are upgrading an MS Analysis Services cube from SQL Server 2008 to 2012 (I know, I know...wish we moving to the latest-and-greatest!). We have 100+ fairly complex spreadsheets that read from the ...

**0**

votes

**0**answers

16 views

### Show the latest value using pivot table in Excel - part 2

For reference I have a similar problem to the follow thread, with a few alterations, making this solution not work as well as I would want.
Show the latest value using pivot table in Excel
The ...

**0**

votes

**0**answers

16 views

### Excel Pivot Table: insert new rows, some cells have no formula

I have a pivot table. There are formulae in Columns Q to Z. After inserting rows at row 170 (table size:A4:Z180), I found that the newly inserted cells in Columns Q, R, and T have no formula while ...

**0**

votes

**1**answer

25 views

### How to prevent pivot table from resizing after refreshing data?

I have slicers and pivot tables on a single sheet and would like to preserve the pivot table size after refreshing the data. Any ideas as to how I can achieve this?

**0**

votes

**1**answer

51 views

### How to get Slicer to multi select instead of multi DEselect?

Adjusting the multi select setting on my slicer makes it deselect items. However, I'd like to select multiple filters instead. How would I go about doing this?

**0**

votes

**0**answers

13 views

### How to preserve pivot chart formatting? [duplicate]

Every time I make changes to my pivot table, the chart formatting reverts back to its original appearance. How do I preserve my chart formatting?

**0**

votes

**0**answers

9 views

### Slicer updating just one of the Pivot tables within same tab

I Have two pivot tables and a pivot chart in one tab. I've also created a series of slicers, all of which are updating the pivot chart and one of the pivot tables. However, one of the pivot tables isn'...

**0**

votes

**2**answers

31 views

### How can I apply conditional formatting to rows that get added to a pivot table when it is refreshed?

I have pivot table in an Excel workbook that summarises information from a table in the same workbook. I have applied Conditional Formatting (data bars) to the pivot table. I have added Year & ...

**0**

votes

**0**answers

8 views

### No underlying data for Excel Pivot Table value

On every other row value in a pivot table I'm able to double click the value to see the underlying data that is summed to produce the value, except for 1 row where double clicking the values shows a ...

**-1**

votes

**1**answer

19 views

### Pivot Table - data summed in two columns with different criteria

I have a pivot table that summarizes Sales per Customer. The data set has month indicator for each transaction.
I would like to get the pivot table to have two columns - one for 12 months of 2018; ...

**0**

votes

**1**answer

44 views

### Calculated field based on sum and count of other field

I have a dataset like this:
Segment Organisation name nr of visitors
A {name} 3
A {name} 7
B {name} 20
...

**0**

votes

**0**answers

14 views

### Excel Pivot table - row based toggle

I have a data set which I'm summarising with Excel. One of the items in the raw data is a note or comment; something like.
+-----------+-----------+-----------------+---------------------------------...

**1**

vote

**1**answer

161 views

### MS Excel - How to model my data in order to produce a chart broken down into product and month (year on year)?

I have one single date column in my raw data. The dates range across 2018 and 2018. I have multiple products and each row represents a sale transaction. There are multiple transactions per product.
...

**0**

votes

**0**answers

13 views

### Excel Camera/Linked Picture of dynamic Pivot - picture size is not adjusting

Tried a few things - I need a linked picture to the columns in a dynamically filtered pivot table. I only need to show the first 3 column labels - where the image is longer or shorted based on pivot, ...

**0**

votes

**1**answer

19 views

### Excel Olap PivotTable Fields Collapse by default

When I connect to an OLAP data source in Excel and pivot, there are a list of dimensions and measure groups shown. These are expanded by default. Like below:
There is an option to "collapse all" ...

**0**

votes

**0**answers

17 views

### Group a numeric input as a date in a PivotTable

Is it possible to group a numeric as a date in a PivotTable?
I am trying to do date grouping and have stumbled on a problem. When a PivotTable understands a variable (column) as a numeric, it can be ...

**0**

votes

**1**answer

11 views

### Excel Pivot Table - Display values of multiple fields as percentage of parent total in one step

I am attempting to display values as percentage of parent total for multiple columns in one click. This doesn't seem to be possible. I have to go column by column and make the selection. Is there any ...

**0**

votes

**0**answers

53 views

### How to freeze columns and rows in Pivot table in Excel when using Filters

Hello I have a pivot table in Excel, I am using some filters to filter on the data. I would like my columns and rows to be fixed in place, even when the filter is applied. In this case only the values ...

**0**

votes

**0**answers

26 views

### How can I show multiple values for a row? (Excel Pivot Table)

As far as I am aware, the values in pivot table can only be shown as a sum, average, count etc. but not as a list of the raw values?
What I am looking for is to list all the times ran for each ...

**1**

vote

**1**answer

124 views

### Excel Pivot Table recognizing blank cells (“”) in column as text not numbers

I'm having an issue with Excel Pivot Table recognizing a column as text instead of numbers.
Background:
I have a Main Table (Main_Tbl) with a column ("Net Widgets") that calculates the variance ...

**0**

votes

**2**answers

40 views

### Excel Pivot Table: Combining Data from Multiple Columns

I'm trying to create a pivot table that summarizes Resource and Number of Days required per project phase. Each resource can appear across multiple phases for different durations. However, due to the ...

**0**

votes

**0**answers

3 views

### Pivot table with alphanumeric header on Excel on Mac OS

I am using Excel 2016 on Mac OS and have data in this format. I would like to create a pivot table with the postcode data in the first column as a collapsible heading. It is my understanding that ...

**0**

votes

**0**answers

22 views

### How to calculate values as percentage of values in other column in pivot table?

I'd like to get a percentage of the number of "TRUE" values in one column (z) compared to the number of "TRUE" values in another column (y) using a pivot table (used for filtering).
How do I do this ...

**1**

vote

**1**answer

21 views

### Remove column names from formulae (Convert into regular cell coordinates without sheet names)

I have a defined table of several rows and columns containing data and formulae. Some of the columns' cells' formulae are:
=[@[Weight]]*1000
Where 'Weight' is the name of a column heading.
I want ...

**0**

votes

**0**answers

20 views

### PowerPivot get the last value recorded for a date

I have a PowerPivot query where I have 3 columns: ID, Date and a Value. Every day is possibly recorded multiple values each on new row. so it looks like:
ID Date Value
1 1/1/2019 654594
...

**0**

votes

**0**answers

23 views

### Excel Pivot Table Averages not Accurate

I am trying to average the amount of time (Column Q) if there is a 1 in column AA by day.
If I use formulas to review the data (table on the left) I get an accurate average min / LC. If I use a ...

**0**

votes

**0**answers

14 views

### How to show total when field is expanded in pivot table?

How can I add a subtotal to an expanded field in a pivot table? (It shows fine when collapsed).
In the image below, I'd like to see "8" (6+2) in the highlighted cell (B2).
Image of what I'd like to ...

**0**

votes

**0**answers

26 views

### Excel pivot table - combining data from three columns. Power query?

My income is divided into three portions - cash, bank transaction, and USD. Let's say they are in columns A to C respectively. The total income is:
total income = column A + column B + column C * ...

**0**

votes

**1**answer

17 views

### Pivot Table does not show all dates (last ones)

I got a problem with my pivot table. In my sales table it doesnt show me the last dates of my sales time period. I guess its because there have been no sales in the last two days (values = 0). For ...

**1**

vote

**1**answer

76 views

### Excel stacked columns chart target line

I have searched but unfortunatelly did not find any answer to my issue.
I have the following table:
_ | A | B | C |
-----------------------------
1 | feb | yes/no | target |
2 | ...