All Questions
Tagged with microsoft-excel-2016 pivot-table
56
questions
1
vote
1
answer
79
views
Excel Pivot Table showing same count as the first column
I'm a beginner at Excel and am currently practicing pivot tables.
The data that I'm dealing with is this table:
In the ROW Labels, I have added the field "Quiz and Assignment Mark Range" ...
- 11
0
votes
0
answers
71
views
Creating a technical excel data sheet to display efficient data
I am creating a table in excel that holds milestone achievement data with their respective amount. This table has multiple milestones that have only one amount, meaning that a set of milestone ...
2
votes
0
answers
116
views
Change PivotChart source from one PivotTable (external data source) to another PivotTable (table/range)
I have a PivotChart in Excel 2016 that is currently based on PivotTable-A.
PivotTables A and B are identical, other than the fact that they have different data sources:
A's data source is an ...
- 157
2
votes
0
answers
731
views
Change PivotTable source from external data source connection to table/range
In Excel 2016, I have several PivotTables (and corresponding PivotCharts) that use external data sources. The data sources were created via Microsoft Query (an Excel mechanism) and have complex SQL ...
- 157
1
vote
1
answer
141
views
Force bar chart to show missing years within 10-year range (system year + 9)
I have a table in Excel 2016:
YEAR_ AMOUNT
---------- ----------
2024 100
2025 200
2025 300
2026 400
2027 500
2028 ...
- 157
0
votes
1
answer
30
views
EXCEL: What type of dataset am I working with overall? How to manipulate pivot tables to answer complex relationships between fields?
I am beginning to delve into data analysis via excel and I would like to learn more about the terminology in terms of some data I'm playing with.
I've created a sample data set for mock invoice ...
- 21
0
votes
1
answer
440
views
Reuse an existing external datasource connection for PivotTable (without creating a duplicate connection)
In Excel 2016, I can create a connection to an Oracle database (via ODBC) using Microsoft Query or the Connection Wizard.
Creating a connection using one of those methods works as expected. The ...
- 157
0
votes
1
answer
3k
views
Excel Pivot Charts - Creating different charts (with different grouping) from the same data
I would like to create (at least) two pivot charts sourced from the same data that display the data in different groupings; one will be grouped by years, the other grouped by months. I am able to ...
0
votes
3
answers
11k
views
Pivot Table: Expand/collapse all rows using keyboard shortcut
In an Excel 2016 pivot table:
Is there a keyboard shortcut we can use to expand or collapse all rows at a given level? For example, CTRL + click the plus sign to expand all rows?
I'm aware that it's ...
- 157
1
vote
1
answer
5k
views
Pivot Table: Sort row by hidden field
I have a table in Excel 2016 (source: Microsoft Query/ODBC):
Raw data:
Construction Type Location Sort
Reconstruction Main St - Red St to Blue St 1
...
- 157
0
votes
1
answer
198
views
Excel Pivot display multiple columns
I had a Salary data month wise now i want to calculate the difference between last 2 months salary field wise
below is the sample data
Required Sample output of single employee.
I have tried using ...
- 103
0
votes
1
answer
654
views
Pivot table column name and horizontal bar in Pivot Chart
My pivot table has a problem.
As you can see,
the first column has a heading of "Row Labels" (in cell A10).
I want it to say "Months".
Also, I'd like to have a horizontal line ...
0
votes
0
answers
4k
views
How to remove a specific field value from Pivot Table
My pivot table is sourcing data from a dynamic table where the number of rows can increase or decrease based on certain conditions.
As such, the range for my data source is something like $A:$M so ...
0
votes
1
answer
1k
views
How to remove blanks from drop down in Excel Pivot Table
I'm trying to create a pivot table that represents a parent-child assembly list. It works nearly perfectly except for one issue. Anywhere there is a blank, a full row with the very little info it has ...
- 101
0
votes
2
answers
14k
views
Division calculation within pivot table values in excel
Please see example below. The row headings (redacted) are defect types of a product. The column headings are the name of the products (redacted) and just below are the total sales of that product. ...
- 15
0
votes
1
answer
1k
views
Cannot Reference Pivot Table headers due to header formatting
I am writing a SUMPRODUCT function that references headers in a pivot table for my report on a separate sheet. The header that is giving me an issue is the year, i.e. 2020, 2021, etc. I have tried ...
0
votes
1
answer
68
views
Grouping non-date data in a usable way for Excel Pivot Tables
I've got a large list of various department labels and associated data, and am wondering how best to consolidate this down to something simpler for reporting.
An excerpt of the data is in the below ...
- 815
0
votes
1
answer
1k
views
Grouping dates by week in an Excel pivot table which has distinct count
My raw data looks like this: raw_data
I want to group the dates by week, showing the number of distinct shows viewed by each viewer every week.
The data should look like this. ExpectedOutput
I've ...
0
votes
1
answer
327
views
How to highlight the rows relating to the "Count Of" in a pivot table?
I have a table with values, and next to it a pivot table showing count of certain fields (same sheet).
I want to be able to click the field or count value on the pivot table (either number or field ...
0
votes
1
answer
55
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 ...
- 151
0
votes
1
answer
1k
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
2
answers
90
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 ...
1
vote
1
answer
254
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 ...
- 169
1
vote
2
answers
1k
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 ...
- 177
0
votes
0
answers
51
views
Excel Pivot Table impossible situation
I have a table that has a list of customers and some attributes, in the form of
Customer | Purchased Amount | State | etc.
So far, so good. The issue is that our customers answered a poll and we ...
1
vote
2
answers
6k
views
Create new table without any formatting
I have an issue with creating table from file that I download from server/database. It's an older .xls file. In that file there is a list with 67 columns and arbitrary number of rows (it depends on ...
- 499
0
votes
0
answers
321
views
Get Date Filter on PivotTable row
I'm creating PivotTables in Excel 2016. The first Row field is a Date. A few months ago I managed to apply a Date Filter to this row field, as shown below. But I am now unable to reproduce this: No ...
- 1,715
0
votes
0
answers
27
views
Excel 2016 Pivot Table Column rename
I have a pivot table with total dollars in the value, year in the columns (2017 and 2018), rows by product. I add the totaldollars field again, and change it to difference from prior year from the ...
0
votes
0
answers
270
views
How do I drill down by value in a pivot table?
I have a pivot table that sums a boolean column (so 1 = yes, 0 = no). I want to be able to double-click in a cell and get just those rows that have a value of 1 for the column. Instead, when I double-...
4
votes
9
answers
91k
views
Excel chart formatting lost when Refresh All or individual Right Click on Data > Refresh
I have 4 pivot charts that rely on data that is refreshed from a connection.
When I click refresh all I lose all the formatting I set up (colours/borders/line and bar and 2nd axis choices)
I have ...
- 97
1
vote
0
answers
434
views
How to plot timeline chart with start and end date columns
I have a table like this:
| Resource | % | Start | End |
|-------------|-----|------------|------------|
| John | 0.1 | 10/1/2018 | 11/15/2018 |
| Paul | 0.2 | 8/20/...
- 2,089
4
votes
10
answers
78k
views
How to rectify Excel error "We couldn't get the data from table in the workbook..."
I am dealing with the following problem using MS Excel 2016:
The Excel file contains a huge data table and several pivot tables in separate sheets.
The current Excel file was saved under a new file ...
- 41
1
vote
2
answers
5k
views
Using a scroll bar to control pivot table filters
I have created a bar chart using a pivot table. The table has two filters in it to choose with row I am pulling data from. Currently, I can use a drop down menu to change these filters.
But, I would ...
- 13
0
votes
1
answer
676
views
How to sort duplicate rows of values into unique rows and multiple columns
Say I have a sample table like this:
And I want to make it like this:
Basically, it is making values in column A unique by moving all duplicate values of each unique row into the next column over. ...
- 119
-1
votes
1
answer
103
views
Excel; How do I import CSV table as rows seperated by table number?
So I have a MASSIVE data set, in its unprocessed format it is a CSV file and contains two column tables that are stacked on top of one another. in the picture below the delimited version of the file ...
1
vote
1
answer
26k
views
Excel chart with multiple series based on pivot tables
I'm starting to learn more about Excel and it's charting capabilities and I have a question to which I can't seem to find the answer. I have three worksheets, each with a single column that contains ...
- 165
2
votes
0
answers
5k
views
How to change the format of a date in a Pivot Table?
I'm having an issue in my pivot table (Excel 2016).
My source data contains actual dates (generated with EOMONTH function), in a standard YYYY-MM-DD format:
However, my pivot table is showing a ...
- 1,020
0
votes
1
answer
2k
views
Unable to add CubeField/PivotField as a Data Field in VBA
I'm working on a very long Macro for my job and I am just about finished, but this is the last part and no matter what I do, I can't seem to figure out the fix. In this PivotTable, I need to have "...
- 11
1
vote
0
answers
369
views
Calculate the difference between two field counts as a part of the pivot table
This issue is driving me mad after a couple hours and I cannot find a solution for it. This solution is close but I don't have a third parameter like year to key on.
Here is the data I have in the ...
- 505
1
vote
1
answer
2k
views
Pivot table grouping by week - can't get it to start on Monday
In Excel 2016, I'm trying to set up a pivot table which has a Date-format column and group it by weeks.
Ungrouped, the data looks like this:
I'm then trying to group it using the following settings:
...
- 153
1
vote
0
answers
177
views
Student voting system in Excel: Combination Slicer and Histogram
I am trying to create an interactive chart with an Excel workbook (2016) to display the results of student voting. My workbook has several columns, but the two relevant here are "student name" and "...
- 11
2
votes
1
answer
1k
views
Filter PivotTable on date range with blank rows showing
I have a table of data that looks like this:
Created Severity Tickets Submitted
11/17/17 High 1
11/28/17 High 1
12/3/17 High 1
12/4/17 High 1
12/27/17 High 1
...
- 115
0
votes
1
answer
55
views
How to creat this Pivot table correctly in Excel 2016
I wanted to do a pivot table as shown below.
Clearly it is not correct, as Business is the most popular subject and it shouldnt have any "<0 or (blank)".
I am assuming that the problem here is ...
- 494
1
vote
1
answer
847
views
Reference Cell in "GetPivotData" Function
My function is working correctly when I manually enter the "CustomerAccoutNumber":
=GETPIVOTDATA("[Measures].[Sum of SalePrice]",
[2017Sales.xlsx]GMBlitzOct!$A$6,"[2017Sales].[CustomerAccountNumber]...
0
votes
1
answer
623
views
Slicer only corresponds to table not my chart - Mac Excel 16
I have inserted a pivot table based on the data range I want to use. From there, I have selected the data in the table and inserted a chart. I then insert a slicer, but when I select the category it ...
- 45
1
vote
1
answer
824
views
(Joining 3 tables) Pivot Table using Excel 2016
I think this is a many-to-many relationship and Excel currently doesn't support this using Pivot Tables. Nonetheless, I wanted to see if there could be an alternative that could be achieved while ...
- 111
6
votes
2
answers
44k
views
Not able to sort columns
I have a pivot table and I would like to sort some columns. But the "sort" function is not working at all. When I click "sort from A to Z" nothing happens. I am just able to sort the first column of ...
- 61
0
votes
2
answers
3k
views
PivotTable Change Data Source broken in latest version (1706)?
Create a sheet "2015":
Create a sheet "pivot" und sum the amount:
Create a sheet "2016" with different data
Try to select the data from "2016" for the pivot table and hit the OK button and nothing ...
- 101
1
vote
0
answers
62
views
Excel:can I simplify creation of following pivot table and chart somehow?
I have following table that lists all projects: ProjectId, DateCompleted.
I want to create a table/chart that shows a total of projects completed each year/month.
I used =TEXT(DateCompleted,"MMM") ...
- 11
0
votes
1
answer
21k
views
Disconnect link between copied charts in Excel
I created a Pivot Chart in Excel and added a filter.
I would like to create copies of the chart with different filter selected in each one.
But it seems there is a link between them, and when I change ...
- 719