Questions tagged [pivot-table]
For questions about Pivot tables in Google Sheets or any other web spreadsheets supporting Pivot tables.
51 questions
1
vote
1
answer
65
views
Two Different Answers for the Same Simple Formula in Google Sheets
I have a spreadsheet with a very simple formula: =C4/D4
Sometimes, this very simple formula gives the wrong value.
Cell C4 contains the value 390062 formatted as a number with thousands separator
...
- 13
0
votes
1
answer
470
views
Wrong grand total when using Calculated Field
I have a simple pivot table, but I'm using Calculated Field values.
The raw data used for the pivot table is like a transaction log, as shown below:
DATE
STOCK
QTY
PRICE
TOTAL
03/2023
AAPL
1
100
100
...
- 53
0
votes
1
answer
2k
views
Google Sheets Pivot Table - Show totals for only one column in grand total
I have a pivot table that has 1 Row (ATIVO), 0 Column and 4 Values columns (Quantidade, Preço Un. Atual, Preço Médio and Total).
But I only need the grand total (shown as Total geral) from the last ...
- 53
1
vote
1
answer
2k
views
How can I automatically refresh a pivot table in Excel 365 (web)?
I found https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2#ID0EBBF=Web, which says:
However, I don't see the PivotTable Tools on the ribbon, even ...
- 19.2k
0
votes
2
answers
2k
views
How to filter a calculated field in Google Sheets?
Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is" is based on choosing a relevant field, then using the syntax to filter it. But ...
- 1,270
0
votes
1
answer
2k
views
How do I use a custom formula to filter dates to a specific year in a pivot table?
I have a sheet of expenses with the date in the first column, in the form YYYY-MM-DD. The column name is "date". I know that I can use the "is between" value filter to select dates ...
- 437
0
votes
0
answers
1k
views
How to sort pivot columns in Looker Studio in Viewer mode?
If it's a table, all one has to do is click the column's title or right click and choose Sort By:
[
How can the same be achieved to pivot tables?
- 1,270
0
votes
0
answers
80
views
Link row in a pivot table back to the source record (in an adjacent Google sheet)
I have two (Google) sheets; the first contains the raw data (records/rows) and the latter a pivot table derived from those records. I want to use the pivot table to index the critical fields and then ...
1
vote
1
answer
285
views
Freeze the values of a slicer when I leave the sheet
I have multiple pivot tables and each of them is linked to one or two slicers. They adjust when I filter by values or conditions but when I want to go to another sheet of my document.
The problem is ...
- 11
1
vote
1
answer
713
views
custom formula in pivot table google sheets
I'm trying to filter on a value in a call in a pivot table.
In my "config" tab I have the following data:
In my source data tab I have the following date:
And in my pivot table I have two ...
- 149
1
vote
0
answers
196
views
Slicer problems
I have constructed a large pivot table and attempted to put some slicers on. Sheets will not
let me choose the specific column I wish to slice on,
when I have a slicer like "Name" the ...
- 11
1
vote
2
answers
60
views
Converting columns into rows with different values
I have a CSV file with 16 columns.
The first 10 (A-J) are contact information, the last 6 are different phone numbers, sometimes for the same contact.
My objective is to create duplicates of the first ...
- 11
0
votes
0
answers
393
views
Need help with Sheets Pivot Table Coding
I am trying to code a script to generate a pivot table based on input from drop down menus in Sheets, but I am new to Google Apps Script and having issues understanding the syntax. I am trying to ...
0
votes
1
answer
1k
views
How to get value of adjacent cell in Google Sheets?
I am trying to build a simple connected-tables multi-sheet in Google Sheets, for tracking expenses.
My tables are as follows, and connected by data-validation.
In the column name, I state where I want ...
- 101
0
votes
1
answer
1k
views
Google Sheets - Pivot Table: how to use sum(values) in filter custom formula
In our attendance spreadsheet present is 1 and absent is 0. In a pivot table we show the attendance per person: sum(attendance). We only want to show people who attended sessions, so we want to filter ...
- 1
1
vote
3
answers
5k
views
How do I create a pivot table group in Google Sheets when the ungrouped values produce too many columns? Right click on the columns is not available
I have a Google spreadsheet for expenses: one column for the YYYY-MM-DD date of the expense, one column for the category, and one column for the amount. This sheet has thousands of rows. The goal is a ...
- 437
0
votes
0
answers
2k
views
Google Sheets - How to display all rows and columns of pivot table?
I am trying to display all rows and columns including those that do not have any data in them in my pivot table. Reason being that I want to see all my entries and keep the table size constant. ...
0
votes
1
answer
479
views
Return a list if 2 criteria match in Google Sheets
In an effort to organize staff training: So far I've made the pivot table, but I'd like for the pivot table to extract who needs which training, as a list in one cell. I tried using =JOIN, but I have ...
- 1
0
votes
1
answer
278
views
Keeping manually entered data side-by-side with pivot table data — row misalignment issue
I have a Pivot Table (columns 1 and 2), and I also have manually inserted data (columns 3 and 4):
But when more data gets added to the pivot table, the manually entered columns won't follow "...
0
votes
1
answer
770
views
Using VLOOKUP with pivot table - OR - another way to aggregate data
My sample is here:
https://docs.google.com/spreadsheets/d/1j9pRECnAKtKtvAfhMvLv93-u6_kwIrFZg3RY3jSkpkU/edit?usp=sharing
I am trying to aggregate the last date that each "familyID" was ...
- 3
0
votes
0
answers
784
views
How to make Pivot Table repeat row labels *without* expanding all columns?
I am making a spreadsheet for doing some analytics and determining win, lose, or draw percentages. The pivot table is all fine, except that Column A is a date column, and if I choose to not have all ...
- 1,266
1
vote
0
answers
80
views
Is there a way to transpose rows with an varying number of columns and replicate cells based on number of columns?
I have a Google Form featuring an name, date, and checkbox field capable of generating multiple choices, anywhere from 1 to 50. I'm looking to have this form filled monthly by hundreds of users.
When ...
- 11
1
vote
0
answers
372
views
How to make Google sheets refresh date filtered pivot tables when a day passes?
Google Sheets has a nice advantage over Excel - their pivot table automatically refresh, while Excel's pivot tables rely on clicking a manual UI refresh button (or - if the right pivot setting is ...
- 1,270
1
vote
1
answer
2k
views
Google Sheets pivot table won't show totals for more than one Row item
Here's a link to the original sheet read only. The Experiment tab is the one in question. If you want to play, you need to make your own copy, then share back in your answer.
I've got Totals ...
2
votes
1
answer
47
views
A Google Sheet with variables for products
I manage a small business and use Google Sheets to store customers data. I want to filter and analyze that data at the end of the month for which I use pivot tables. Now, the problem is my product has ...
- 23
0
votes
1
answer
596
views
Referencing a calculated value in another calculated value
Here's the general structure of my spreadsheet:
Start End Count Duration Hours Count/Hour
09:00 10:00 10 01:00 1.00 10
Duration = End - Start
Hours = TIMEVALUE(Duration) * 24
Count/...
- 101
1
vote
1
answer
429
views
Convert table into list
I need to convert a table into a list so it can be used as a database
Into this:
- 11
1
vote
0
answers
556
views
Find Difference from Previous Consecutive Row in Google Query Pivot Table
Say I have source data like the below:
OriginDate|Name|IncidentDate| where Origin Date is constant and the earliest date of my imported data set. I've formatted the dates to numbers so I can perform ...
0
votes
0
answers
318
views
How can I SUMIF a row, but have the formula automatically applied to all new rows below it in Google Sheets?
I've made an inventory tracking spreadsheet that gets raw data from Google Forms. Forms inputs data into Sheets in a less than ideal way, but there's nothing I can do about that, so I'm stuck post-...
0
votes
2
answers
616
views
Google Sheets Pivot Table take first string
I have a spreadsheet of many-to-many matches between people. My data looks something like this:
+-----+-------+-----+-------+
| id1 | name1 | id2 | name2 |
+-----+-------+-----+-------+
| 123 | John ...
- 103
1
vote
1
answer
14k
views
How to use external data range for a Google sheet pivot?
I'd like to use another spreadsheet's data as the data range for a pivot table.
In order to import data from another spreadsheet, one can use the impotrange function.
But can it be imported directly ...
- 1,270
1
vote
1
answer
235
views
Count query based on multiple conditions
I have a spreadsheet that I use for my World of Warcraft guild, and I've hit a bit of a wall while trying to add new functionality to it.
For reference, when our guild goes through dungeons, we ...
0
votes
2
answers
365
views
Google sheets - "Anchor" formula to pivot table data which keeps moving
Ive got a Pivot table in a Google Sheet which summarises some numerical data by groups.
Ive attached a screenshot of the pivot table below.
I want to be able to sum the values of the "grand total" ...
- 7,069
4
votes
1
answer
2k
views
Dynamically reference data from multiple tabs and sort it
I've got a spreadsheet with multiple tabs of data. The data contains 2 columns, A = Categories, B = Quantity.
I've created a copy of the spreadsheet HERE (please use file > copy) if you would like ...
- 7,069
2
votes
2
answers
3k
views
Summarise data in a pivot table, but exclude certain data if it has a certain value in a certain column
I've got some data in a Google Drive spreadsheet that I want to run a pivot table on. There are 3 columns Element, Cost, Omit as per screenshot below
I want to summarise the cost by element, but ...
- 7,069
4
votes
3
answers
5k
views
Syntax of field "Google Sheets / Pivot table editor / Filters / Filter by condition / Custom formula is"
In Google Sheets, if I create a pivot table, there is a Pivot table editor I can use to configure the pivot table.
The lowest section of the interface of the editor is Filters (which allows me to add ...
- 151
2
votes
1
answer
4k
views
Create table showing difference between values in two pivot tables
Our bookstore has two separate documents in Google Sheets (one containing purchase data and one with sales data). Each document contains a sheet with a pivot table that sums up total purchases/sales ...
- 53
0
votes
1
answer
3k
views
Pivot Table Filters - Custom Formula
In my pivot table, I want to only display the entries in the column "Which Event" if the name contains "Baseball" or "Softball".
I can very easily do part of this by adding a filter for the field "...
- 105
1
vote
2
answers
2k
views
Is there a way to create a calculated field using results of another calculated field?
Is it possible to use the results of a calculated field as part of a new calculated field? I know further calculation could be done outside the pivot table but I was hoping there was a way to somehow ...
1
vote
1
answer
212
views
Can I navigate from a pivot table back to the original database?
Once I am "in" a pivot sheet, is there an easy way to click on a row or cell in the Pivot table and navigate directly back to the related data in the original database (Google Sheet), from which the ...
0
votes
2
answers
54
views
How to count duplicate names as one value in Sheets?
I have a large dataset in Sheets and I'm grouping together the data in a pivot table and have a hard time figuring out how to avoid counting every instance of a name instead of counting the name as ...
2
votes
2
answers
8k
views
Filtering Pivot table by multiple values
I have a pivot table below that I am trying to filter dynamically based on a separate list and am trying to figure out how to do this by referencing the relevant field. In the below example I have ...
- 23
1
vote
1
answer
801
views
Custom Script in Pivot Table Value Formula = Error Loading Data forever
I am very new to the Google Sheets platform, but I am not new to programming, and am comfortable creating and managing javascript code, and reading API documentation.
I am working on a side project, ...
user205834
0
votes
1
answer
3k
views
How to calculate weighted average of averages in Google Sheets?
I have product review data. The reviews are out of 5 stars and I know how many there are (ie quantity of reviews). I'm trying to calculate a weighted average of the reviews because I'm trying to ...
- 289
1
vote
1
answer
109
views
Can you create a pivot table in the same sheet as the data its referencing?
Is it possible to have a pivot table on the same sheet as the data it's referencing in a Google Sheet?
At the moment if I create a pivot table it always creates a new sheet.
- 7,069
1
vote
1
answer
137
views
Aggregate data with Sheets pivot table or graph
Please have a look at this google sheet here.
It has a pivot table with 3 columns:
A period (for example: 2016/01), a category and a number. I would like to do a chart that looks like this:
For ...
- 131
0
votes
0
answers
1k
views
Google Chart (for pivot table) legend labels display wrong
In my Google Sheets spreadsheet, I have a pivot table, and a column chart connected to it.
But the legend of the chart is displaying not just the headings, but also the two cells below the headings. ...
- 101
0
votes
1
answer
2k
views
Can I eliminate redundent totals in google pivot tables?
Given the general difficulty of doing much with google pivot table, I expect the answer to be "No".
In the snippet below I would like to keep lines with the totals in green shading and not see the ...
1
vote
1
answer
4k
views
How can I format a pivot table with staggered elements to make narrower?
Right now a typical pivot table looks like this:
This takes up too much room horizontally. I want to format it so that it is narrower and taller, by moving nested blocks down and left.
How can I ...
65
votes
8
answers
203k
views
How to group data by month in Google Sheets
I have a Google Sheet which contains various metrics for each day. Each day has it's own row:
day | cost | impressions | clicks | conversions
2014-02-01 | $45 | 3,540 | 80 | 5
2014-...
- 823