Questions tagged [pivot-table]

For questions about Pivot tables in Google Sheets or any other web spreadsheets supporting Pivot tables.

Filter by
Sorted by
Tagged with
0
votes
1answer
10 views

How can I create a line chart in Gsheet if I have a pivot table with 2 rows?

I want to plot a line graph, it should have 4 series as mentioned below Car 2019 Scooter 2019 Car 2020 Scooter 2020
0
votes
1answer
37 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 ...
0
votes
1answer
18 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
1answer
50 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 ...
0
votes
0answers
60 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 ...
0
votes
0answers
25 views

Pivot table - Show "values as rows" on left and "rows" on right

I want to show the "values as rows" on the left side of my pivot table on "rows" on the right. In other words, I want my pivot table rows to be grouped by value rows instead of ...
1
vote
0answers
39 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 ...
1
vote
0answers
74 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 ...
0
votes
0answers
25 views

Guideline for converting a pivot table to a query in Google Sheets?

I'm running into limitations as to what a pivot table can do. Is there a ready guideline for converting a pivot table to a query? Alternately a set of examples might do.
0
votes
1answer
146 views

Google Sheets pivot table won't show totals for more than one Row item

Edit: 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. https://docs....
2
votes
1answer
36 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 ...
0
votes
1answer
51 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/...
0
votes
1answer
51 views

Convert table into list

I need to convert a table into a list so it can be used as a database Into this:
1
vote
0answers
166 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
0answers
109 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
2answers
92 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 ...
0
votes
1answer
4k 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
vote
1answer
59 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
2answers
137 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" ...
3
votes
1answer
1k 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 ...
2
votes
2answers
1k 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 ...
4
votes
3answers
4k 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 ...
2
votes
1answer
2k 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 ...
0
votes
1answer
2k 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 "...
1
vote
1answer
518 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 ...
0
votes
1answer
65 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
0answers
1k views

Calculated field in google sheet pivot table

I encounter a problem when trying to make a simple formula in calculated field. I can't reference a column with a space on its name. For example, if my column is "Column A" : formula : "='Column A'" ...
0
votes
2answers
36 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 ...
1
vote
2answers
5k 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 ...
1
vote
1answer
483 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, ...
0
votes
1answer
2k 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 ...
1
vote
1answer
51 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.
0
votes
1answer
38 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 ...
0
votes
0answers
917 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. ...
0
votes
1answer
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
1answer
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 ...
57
votes
8answers
180k 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-...
10
votes
4answers
32k views

How can I group a pivot table into weeks or months?

In Microsoft Excel, in a pivot table, you can right click on the dates - while within the pivot table, on a date and select group (from the drop down menu) - then you can choose different groupings, ...