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.
455
questions
0
votes
1
answer
7
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 ...
- 818
0
votes
0
answers
7
views
How can I make a dropdown list based on VLOOKUP or alternatives in google sheets? [closed]
I have 2 excel sheets: one with a list of questions and one with the questionnaire, see screenshots.
There will be other questions added, removed or their order changed, so I need to account for this....
0
votes
0
answers
3
views
How to repeat a set of columns forever in Google Sheets? [migrated]
I'm designing a workout spreadsheet in Google Sheets that has an A workout and a B workout that alternate every time.
Rather than having two sheets, A and B, that I have to switch between, I'd rather ...
- 145
-1
votes
1
answer
25
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
...
0
votes
0
answers
14
views
How to put multiple hyperlinks inside a Google Sheets cell [migrated]
I tried multiple option, all returned me an error.
When I want to put one link in a cell this works fine :
=HYPERLINK("mylink.com";"link1")
Here is what I tried for multiple links (...
- 101
0
votes
0
answers
28
views
Sumifs with index match not working with month validation
I am trying to use the SUMIF function along with with INDEX and MATCH to return data from the "Raw Data" sheet using month validation, but it is not working.
Need the data on this sheet
Raw ...
- 1
-1
votes
1
answer
28
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 ...
0
votes
0
answers
10
views
Google Sheets - smart filtering [migrated]
So I need to do something which might be a standard feature in spreadsheets, but I have no idea how.
I have a landing page that processes T-shirt purchases by families. Each row represents an order, ...
- 101
1
vote
1
answer
34
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:...
- 113
0
votes
0
answers
3
views
How to copy/paste conditional formatting in Google Sheets (without including the original cells) [migrated]
When I copy and "paste the conditional formatting only" option from one column to another (e.g. A1:A5 to B1:B5) the result is that for B1:B5 the conditional format range includes A1:A5.
How ...
0
votes
1
answer
23
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
32
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 --> ...
- 201
0
votes
1
answer
52
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 ...
- 4,337
0
votes
1
answer
37
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
0
answers
25
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
1
vote
1
answer
67
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
37
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:
- 611
0
votes
1
answer
46
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 ...
- 1,010
0
votes
1
answer
46
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 ...
- 203
0
votes
1
answer
40
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 ...
- 133
8
votes
4
answers
3k
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 ...
- 225
0
votes
0
answers
42
views
How can I check all the return values from FILTER against an IF condition?
I'm trying to organize a data set that is aggregated by submissions to a Google form onto a spreadsheet. The data contains data collected from individuals at different times and organized according to ...
- 13
1
vote
2
answers
149
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 ...
- 13
1
vote
1
answer
38
views
How to group by multiple excel columns into one list with a header
So I have a dataset in Google Sheets (Excel) like the following in two columns
Movie1, Jack
Movie1, John
Movie1, Henry
Movie1, Hanson
Movie2, Bill
Movie2, Benny
Movie3, Gary
Movie4, Robert
I want to ...
- 113
-1
votes
1
answer
92
views
Excel: How to count how many times a consecutive text value appears
I'm making a work schedule and I have to pay attention to a specific rule that, no worker should be scheduled to work more than 6 days in a row.
I got this spreadsheet where I make those schedules but ...
0
votes
1
answer
36
views
Formatting a column to a type can I have header text that is not that field type and will not cause issues?
If I have a column of data in Google Sheets or Excel can I have a header column that is not that type?
For example, I need to have a column of data of type time. But I want to have a header text for ...
- 2,024
-1
votes
3
answers
479
views
Excel: How To Transpose Every Random amount of N Rows From One Column To Multiple Columns?
I have a set of multiple choices questions with ABCD as answers, all answers are stacked on a column, but not every question has the same amount of answers.
Instead of transposing every nth value, my ...
0
votes
0
answers
25
views
Excel vs Sheets: use case of rand() as autonomous trigger for sheet calculations?
I recently read about a fairly brilliant and novel use of the rand() function in order to produce (functionally speaking) real-time calculation on G Sheets independent of the sheet actually being ...
- 941
1
vote
1
answer
201
views
Is there a formula for calculating the difference in minutes or hours between two times?
Is there a formula for Is there a formula for calculating the difference in minutes or hours between two times in two columns?
In my spreadsheet I have the following two columns called start time and ...
- 2,024
0
votes
1
answer
65
views
How do I multiply a group of cells by a certain number, based on if they have a value in them or not
I want to have a cell (the K column, for allowance) multiply the number of days in it's row (the C-I columns, Sunday - Sat) only if they have a value, by 100.
For example, if Monday, Tues, Wed all ...
- 1
0
votes
1
answer
21
views
How to get Project Wise Summary numbers from one Google Sheet to another?
I am trying to create a Project accounting Google sheet.
In one sheet I have all the expenses, revenues for the projects. (see below)
and the other sheet will have list of projects with Summary of ...
- 323
0
votes
0
answers
11
views
Hi need a formula when i type a value in COL C it looks up that value in COL E,F,G and then displays the value with the appropriate color [closed]
enter code here
Hi need a formula when i type a value in COL C it looks up that value in COL E,F,G and then displays the value with the appropriate color
0
votes
1
answer
6
views
SUMIF and OR function doesn't work
I tried this function in Google sheets: =SUMIF($D$3:D; OR(""; ">"&TODAY()); $B$3:B)
Why is the result 0?
It shouldn't be :/
- 13
0
votes
1
answer
110
views
Search across cells and return value from different cell
I need to reference a cell based on drop-down values from four different columns.
Essentially I am trying to compile a summary list of data from different tabs. Each tab is laid out the same however ...
- 3
-1
votes
1
answer
50
views
How to use GSheet ArrayFormula function to Excel
I've this list in GSheet
| A | 8,6 |
| B | 9,6 |
| C | 7,5 |
| D | 6,7 |
| E | 9,5 |
| F | 5,6 |
I can score the average with this formula
=ArrayFormula(AVERAGE(IFNA(vlookup(trim(split(E2,...
- 236
0
votes
2
answers
39
views
Excel formula to return abcd from a cell containing [ab]{xy}[cd]{zw}
An excel file contains some cells with text in this format: [ab]{xy}[cd]{zw}, where there can be between 0 and 10 [whatever]{whatever2} sequences.
I would like a formula to get all the text inside the ...
- 171
1
vote
2
answers
59
views
How can I have a total row appear at the bottom of a table of a variable size?
I have a table with a variable number of rows. I'd like for there to be total row beneath it.
The rows are being generated by a function producing an array. So my current solution is to have enough ...
- 11
0
votes
0
answers
74
views
Create chart in excel that will compare different sessions on the same timeline
I have three devices that report battery percentage to my server every 15 minutes. The exact time that the devices report can vary. I took a sample of the three devices over a period of about 30 hours ...
- 1
0
votes
0
answers
16
views
How to add the currently opened website as an Edge app
I have a Google Sheet and I intend to open it as an Edge app.
I open the Sheets lists:
https://docs.google.com/spreadsheets/
I navigate to the sheet:
I use the 3 dots and then Apps->Add website ...
- 715
1
vote
1
answer
97
views
Function to calculate length of a string in Google Spreadsheets when the string contains certain UTF-8 characters
This function returns 6 and it's expected to return 4:
=LEN("🥎A🥎B")
What function should I use to get the "real char count" expecting that one symbol in the UTF-8 icon map is 1 ...
- 493
1
vote
2
answers
21
views
Sequence Generation formula in excel for an “exploder” function
I am wondering if there is a way to create the following type of sequence in excel:
1 1
2 1
2 2
3 1
3 2
3 3
4 1
4 2
4 3
4 4
5 1
5 2
5 3
5 4
5 5
I would ...
- 13
0
votes
1
answer
52
views
Looking for values based on some conditions
I have a sheet that has the attendance reports submitted by instructors through a Google forms for different courses in different weeks.
I need to generate an attendance sheet for each course ...
- 5
0
votes
1
answer
30
views
Excel/Google Sheets: What is the best way to continue this formula so it will include cells yet to be filled in?
I'm manually calculating the variance and standard deviation of a set of potential investment returns, each with their own probability of occurrence.
Assuming I'd like to expand this list and not have ...
0
votes
0
answers
22
views
Data Source to Excel Date
I've been trying to import data from excel to google sheet. The data I got was in the format of dd/mm/yyyy, which makes sense as we use it in where I live; however, once I tried to copy paste the data ...
0
votes
1
answer
45
views
How to split rows of 1 column into other columns?
How to transform this :
Color Number Fuit
Yellow
1
Banana
Red
2
Apple
Green
3
Grape
...into this :
Color Number Fuit
Yellow 1 Banana
Red 2 Apple
Green 3 Grape
My ...
0
votes
0
answers
24
views
Sum of multiple if values
I am trying to sum the total number of shifts worked.
D=1 ... a full shift
D3 & D7=.5... 1/2 shifts
=countif(A7:A22,"D")
=value(IF(COUNTIF(A7:A22,"d3"),".5","&...
- 1
0
votes
0
answers
11
views
How to use results in a cell in another formula? Sum multiple results from previous formula
Not sure if thats how I should say it but I am trying to sum/average cells which has their individual ifs formula and it always end up having a different result. anyone has suggestions?
this might be ...
0
votes
0
answers
28
views
How to preserve newlines embedded in cells when exporting google sheets
The default Google sheets text exporter gets quite confused when it encounters embedded newlines. I might lose the preceding cells if the cell is not in the first column for example.
What is the way ...
- 3,413
0
votes
2
answers
78
views
How to get "winner" based on row MAX
I'm trying to get the header value based on the highest value per row.
For example, given a sheet like:
A B C D
-------
1 2 3 4
3 3 1 2
I'd like to do reports on the winners of the row. For example ...
- 285
0
votes
1
answer
61
views
How do i create a Dynamic Dropdown Data Validation List based on pre selected criteria?
I seldom use Google Sheets or Excel, but now that I'm working on a card game, I have since thrusted myself into formulas and data validation.
I'm currently running into a logic issue.
I would like to ...
