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.

Filter by
Sorted by
Tagged with
0
votes
2answers
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
0answers
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
0answers
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
1answer
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
2answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
2answers
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
2answers
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
1answer
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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
0answers
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
0answers
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
2answers
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
0answers
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
1answer
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
1answer
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
0answers
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
0answers
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
1answer
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
2answers
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
0answers
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
0answers
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
1answer
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
0answers
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
0answers
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
0answers
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
0answers
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
1answer
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
1answer
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 | ...