# Questions tagged [google-spreadsheets]

Google Spreadsheets is a part of the Google Docs web app suite. Questions about Google Spreadsheets are off topic and should be asked on webapps.stackexchange.com, unless they specifically involve your computer or browser.

478
questions

0
votes

0
answers

5
views

### How to return values horizontally in Google Sheets [migrated]

Say I have a column like this:
User1 Item1
User1 Item2
User1 Item3
User1 Item4
User1 Item5
User2 Item1
User3 Item1
How can I return them like this in a filter/lookup formula:
User1 ...

0
votes

1
answer

41
views

### How can I keep multiple rows of data while only displaying one row, but the others show up when moused over or selected?

Basically, we have a rickhouse full of barrels. Each rick/slot is 3 levels/rows high. We have 180 ricks/slots, or 540 levels/rows. Each row is 10 barrels deep. So each barrel has a rick number, a ...

0
votes

2
answers

86
views

### How do i remove numbers before text in excel

I want to remove numbers before questions in the column C
the column name is ***QUESTIONS***

0
votes

1
answer

26
views

### Leave cell blank until data is entered when multiplying

I have very little experience with spreadsheets and computers in general compared to, I assume, all of you. So, if you could spell out the answer to the below question as clearly as possible, it ...

0
votes

0
answers

5
views

### Growth between 3 cells in Google Sheets [migrated]

I am creating a data wall to compare student achievement across 3 time periods Beginning of the Year(H2), Middle of the Year (I2) and End of the Year(J2) and having the difference recorded in K2. I ...

4
votes

1
answer

143
views

### Consolidate Data from Multiple Columns into One Column

I have a dataset that looks something like this:
Customer
Col1
Col2
Col3
Bob
111
2221
Michael
112
Anna
113
2223
Rachel
114
2224
334
Person5
115
I'd like to put all data into one column, ...

0
votes

2
answers

29
views

### formula to count in how many cells first digit is greater than second in last pair

Let's say I have row of cells A1:A10 with values like "46 64 76", "63 60" etc. Two or three pairs of digits separated by spaces. I need to count in how many cells in last pair ...

1
vote

1
answer

50
views

### SUM of a range of durations in an XLSX-Formatted sheet always returns 0 or errors

Excel document
I have the following XLSX-formatted spreadsheet in Google Sheets
I'm trying to set a formula on cell R5 (colored in red) so that it adds up all the times in the range L4:N42 but ...

0
votes

2
answers

84
views

### Get first and last values for a range

Say I have a range, A1:A27, and that range has a mix of empty and non empty values, I would like two functions: one to get the first non-empty value in the range, and one to get the Last non-empty ...

0
votes

0
answers

26
views

### Importing a range of formulas from csv or txt file starts with ' in excel and sheets [duplicate]

I am generating some image URLs with a python script and converting them to a formula. I have tried saving it to both a CSV and a TXT file as others suggested, but both lead to the same problem:
After ...

0
votes

1
answer

86
views

### How to get value using row and column search

I made a budget spreadsheet I'd like to use and have plugged in some numbers to ensure I'm doing everything correctly. I have two sheets: 1. for the budget i.e. I'd like to spend max $1000 for rent ...

0
votes

0
answers

106
views

### Sync Google Sheet with Microsoft Form Responses

I have a Microsoft form and the responses are recorded in an Excel file. I want to sync this Excel file with a Google sheet to do some calculations there. Sometimes, some formulas break the connection ...

0
votes

1
answer

68
views

### How can I get an aggregated count of rows grouped by two values and enforcing uniqueness on a third in Excel?

I have a Google Sheets spreadsheet which is connected to an external integration that automatically populates rows with new 'events' contained within one of the sheets. I want to create a second sheet ...

-1
votes

1
answer

52
views

### Alpha-sorting multiple rows, empty cells for missing values

Say I have multiple columns:
A | B | C…
Bill | Bill | John
Adam | Fran | Nick
| Adam2| Adam
I’d like to alpha sort the rows in each column, and also add empty values in a cell if the cell ...

0
votes

1
answer

173
views

### Using Array Formula with TEXTJOIN to separate with commas all non-blank fields in each row

I am trying to create an array formula function that will pull the values of all fields in each row (between columns F and V) and separate them by ',' when not blank (from within only the row). The ...

1
vote

0
answers

18
views

### What's the best way to draft emails in Gmail that use variables from Sheets? [closed]

I'm want to draft emails in Gmail that incorporate variables from Sheets. Is there a free tool that does this, or am I better off writing a script?
Crucially, I need to look over the draft emails ...

-1
votes

2
answers

115
views

### Difficult Cumulative Income Tax Calculation

I've been trying to solve this for days. I can't seem to figure out how to create a one size fits all formula for this cumulative tax.
I'm trying to achieve the following:
I can't seem to find a one ...

0
votes

2
answers

94
views

### How to shorten Excel OR function for many conditions?

As shown in the data, I want to determine whether a student passed (failing if s/he gets less than 40% in any subject). I can do this using many OR's inside IF.
My formula: =if(or(b3/b$2<0.4,c3/c$...

1
vote

1
answer

46
views

### How to use countif to find specific formulas

I'm using Google Sheet. I have a bunch of =char(10003) to show check marks in some cells.
I want to use =countif to count them all.
I'm stuck at what to write for the criterion parameter.
How can I ...

0
votes

1
answer

20
views

### Clear direct formatting does not work, how do I treat the cell with a date in it as date and no as text

A cell that contains a date, 02/30/2020, is treated as text by my formula datedif from another cell whereas other cells with similar dates are treated as dates. Deleting the content of the cell does ...

0
votes

1
answer

58
views

### SUMIF or VLOOKUP for comma-joined / array

See toy example (Google Spreadsheets). Given column D, what formula do I need to enter in column E so that it gets the sum of the comma-separated list of values present in A? You can assume all items ...

0
votes

1
answer

35
views

### GDrive calculates matrix multiplications differently in GSheets and . xlsx. Does anyone know why this is?

I've been using Google Forms and Google Sheets to gather and analyze data for some time. I use array formulas and calculate matrix multiplications. For back-up reasons, I had to export the Sheet as ....

0
votes

1
answer

45
views

### How can I copy value only if another column says "Completed"?

How can I copy value to another tab from column "price" in which I type price only if column "status" says "Completed"?
I have Appointments tab in which I type prices of ...

1
vote

1
answer

125
views

### replace comma separated values in a sheet uses SORT formula to get sorted results from another sheet

I have a sheet called "Form Responses 1" connected with a Google Form, and I made another sheet/tab and used this formula:
=SORT('Form Responses 1'!A2:M, 4, TRUE)
to sort the responses with ...

0
votes

3
answers

112
views

### Return First Column Header of Instance of String

I have a collection of speakers who have spoken various dates.
Date,1/2/2023,1/2/2022,1/2/2021,1/2/2020,1/2/2019,1/2/2018
1st-Speaker,Jim,John,Fred,Sam,Scott,Riley
2nd-Speaker,...

0
votes

1
answer

2k
views

### MAX function not working on date with format dd/mm/yyyy in Google Sheets (Excel)

I'm using Google sheets and the MAX() function works only on dates with format mm/dd/yyyy. How to get the MAX() of date with format dd/mm/yyyy?|

-1
votes

1
answer

49
views

### how to split one string into multiple rows (by every eight chracter)? [duplicate]

i have some data below, could there be anyway to split this all string data into multiple rows, each row with 8 characters. Appreciates ur help. ^^"
Raw data:
...

0
votes

1
answer

519
views

### Google Sheets - Making a chart where certain series do not have data for all X-axis values

I am making a chart for research in musicology which requires me to graph the duration and tempo of notes based on the score, i.e. musical values like a quarter note.
Using a recording of a string ...

0
votes

0
answers

355
views

### Calculus operations in Excel and Sheets

I am having issues with integration in Google Sheets and Microsoft Excel. I have installed the necessary packages to compute integrals and double integrals, including Calculus Functions for Google ...

3
votes

1
answer

203
views

### Text transformation of regex capture group using PROPER is ignored by REGEXREPLACE

I am trying to change the case of part of a text string (sub-string) in Google Sheets.
I've tried and failed to apply the PROPER function to a regex capture group inside REGEXREPLACE.
The PROPER ...

0
votes

1
answer

52
views

### Find stdev of values in col B where col A matches criteria

I have a sheet where column A is dates and column B is values. I want to find the stdev of values in B where the date in A is within the last 30 days.
I am able to do sums with a sumifs which works on ...

2
votes

1
answer

647
views

### How to set a whole row equal to a value, using ARRAYFORMULA() ? (in Google Sheets)

I would like to set the contents of all cells in a particular row to have the same value, sort of like having a banner that runs across the entire width of the spreadsheet. I'm working in Google ...

0
votes

0
answers

45
views

### How to verify calculations when Excel and Google Sheet give imprecise results [duplicate]

This question is similar to In Excel, when I enter 22222.09482 then I see 22222.0948199999 number in the formula bar but it isn't the same. It's about how to verify with precision.
A simple ...

0
votes

2
answers

44
views

### How to determine cell value based on comparing other cells to find the fastest and slowest durations?

I have a set of columns in a Google Sheet which are calculating the average duration between two times. I would like to calculate a score in the cells marked with an X based on the calculated averages....

0
votes

1
answer

14
views

### How to dynamically return in each row the number of occurences of a string found in every column?

I have data between H2:L where H is static but L can grow to a new column.
How to use something dynamic like an arrayformula to count the number of occurrences of certain strings inside every column ...

-1
votes

1
answer

38
views

### Convert tally table to a table

I have a table in Google sheets which approximately looks like this:
Name
Red
Yellow
Green
Blue
Mark
1
John
1
Steven
1
Bill
1
I want to shorten it into this kind of table:
Name
Color
Mark
...

-2
votes

1
answer

690
views

### Excel / Sheets: How do I filter addresses based on a list of zip codes?

I'm planning to mail some postcards to potential business contacts.
I've got a spreadsheet of 2,500+ addresses with columns including Name, Street Address, City, State, zip code, etc. that cover the ...

1
vote

1
answer

235
views

### reference range in different sheet with Indirect column and row

I'd like to reference a range in a separate worksheet using indirect relative row and column notation, something like this:
=INDIRECT("'dataset'!R[0]C1:R[0]C4", FALSE)
instead of
=dataset!a1:...

0
votes

1
answer

2k
views

### Len and substitute to return 0 if cell is blank?

I'm using BYROW to pull a string of dates from one sheet and make a list of those dates in a single cell on another sheet. Now I'm trying to use LEN and SUBSTITUTE to count the number of dates in that ...

2
votes

1
answer

460
views

### Remove third-party Google Sheet Add-On and UI Menu

I installed Numerous.ai third-party Add-On for Google Sheets (CAREFUL - DO NOT INSTALL IT). I didn't like it, so I then deleted it from Add-Ons (Extensions --> Add-Ons --> Manage Add-Ons --> ...

0
votes

1
answer

172
views

### Replace duplicates in-place (Excel or Google Sheets)

Is there in-built functionality to remove duplicates in-place in Excel (365) or Google Sheets across several columns? For example, consider this set of data:
The goal is to select the entire data ...

0
votes

1
answer

2k
views

### Find next cell within column that is a number, starting from reference cell in another sheet

I think this sounds simple but I'm having a bit of trouble figuring it out.
I have 2 sheets, the Sample Sheet attached and an Input Sheet.
In the Input Sheet, I input one of the green shaded values (...

0
votes

1
answer

101
views

### Combining sheets with different number of colums and rows

How can I combine two sheets with different number of rows and columns in Excel, Libre Office or Google Sheets? Example:
Sheet 1:
Name
Age
Phone
Mike
35
197 158 348
Dave
38
394 953 429
Sheet 2:
...

1
vote

1
answer

1k
views

### Drag and a drop a range from one sheet to another sheet of the same workbook or spreadsheet in Excel/Google Sheet

I would like to move a range of cells that contains many formulas (some using absolute reference and some others using relative reference). If I just do Cut and Paste then the relative formulas will ...

1
vote

1
answer

227
views

### Calculate the difference in days between dates between rows in Google Spreadsheets?

I have a date column. What formula do I use in Google Sheets to calculate the difference in days between rows?
I tried =DATEDIF(A2,A3,"D") but get the following:

0
votes

1
answer

963
views

### Performing T-test on subset of values in a Google Sheets range

I have a set of sports scores in Google Sheets. I have various subsets of scores for which I calculate the averages and standard deviations. I want to perform T-tests to determine if a particular ...

0
votes

1
answer

470
views

### Google sheets is able to access protected sheets and workbook. How to properly protect them?

I was making a quiz template on Excel with all the correct answers on a separate sheet so it can be compared and then graded, again in another sheet on the same file/workbook. The two other sheets are ...

0
votes

1
answer

121
views

### Bulk edit a column and add formulas

I have a column which contains values like
2015
2000
1950
I want to replace this column with something like
=YEAR(TODAY())-2015
=YEAR(TODAY())-2000
=YEAR(TODAY())-1950
How do I do it, if I have ...

8
votes

4
answers

9k
views

### Formula to see if a series of numbers is increasing in value?

I have a spread sheet with thousands of sets of numbers. That is too say, there are ten columns and each column has a number - and there are thousands of rows of these numbers.
What I want to do is ...

1
vote

2
answers

459
views

### Using XLOOKUP or FILTER to aggragate data into a single cell

I have a data set that is compiled by various people filling out a form to populate a spreadsheet on Google Sheet. Here's what the data set might look like:
A data set listing out name, collected ...