Questions tagged [google-sheets]

For questions about Google's web application to create and edit spreadsheets on a web browser or app. Don't use it for the Google Drive Excel files editor. Please consider adding a demo spreadsheet to your question; this will greatly increase the chance that you get a satisfactory answer.

1,944 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
16 votes
3 answers
10k views

Statically positioned chart (ignore scroll)

I have a Google Sheets document with four columns of data. I have a chart on the right side. When you scroll down to see all the data, the chart scrolls up off the screen. Is there a way to make it ...
user avatar
5 votes
2 answers
450 views

Unable to operate script in my work computer and home computer, on other computers it works fine

I have a Google Apps Script project that I have used for the past few months (successfully) and everything has worked just fine. A few weeks ago, I started getting the "Unable to load files" error ...
user avatar
  • 216
5 votes
3 answers
4k views

Copy range from specific values to top of another sheet

I want to copy everything from the range "Start" to "End" to the top row of another sheet (Sheet2). Sheet1: The data between "Start" to "End" can be any number of rows. This is my current code. It ...
user avatar
  • 151
4 votes
1 answer
5k views

Calculate Percentage with QUERY on Google Sheets

I have a Google Sheets database like this: Name | Value | Answer A | 100 | Y B | 200 | N C | 10 | Y A | 100 | N A | 1000 | Y B |...
user avatar
4 votes
1 answer
10k views

How can I duplicate a row in Google Sheets while retaining all of the formulas and format?

I need help with Google Sheets. We are using a sheet as a working file and new rows will be added by different users. Formatting and formulas are included in various columns. How do I ensure the new ...
user avatar
  • 41
4 votes
1 answer
5k views

How do you paste without format by default in Google Sheets?

In Google Sheets you can hit Ctrl+Shift+V to paste without the formatting, but, is there a way to change it so that you can use Ctrl+V? Either by changing the Keystroke or forcing the rows or columns ...
user avatar
  • 223
4 votes
2 answers
2k views

Jump to bottom of spreadsheet, blank (non-formula) row

I had a tall spreadsheet that I need to add data at every time it opens. Answers like Go to Bottom Cell have been helpful, but my quirk is that the first column contains formulas that return blank in ...
user avatar
  • 504
4 votes
1 answer
7k views

Populate a Google slide automatically via Google spreadsheet

Is there any way to populate a Google slide automatically with some other workbook like Google spreadsheet. (e.g. like by giving range reference in a Google slide). If someone knows the other way to ...
user avatar
4 votes
1 answer
1k views

How can I create a Google Spreadsheets whose name and cells auto-complete with cells completed on another sheet?

I'm using Google Spreadsheets to manage projects, and I'm not familiar with scripts or macros. What is: 1) I have a Gantt spreadsheet which lists all the ongoing and foreseen project as follows (by ...
user avatar
  • 41
3 votes
1 answer
346 views

How do I stop Google Sheets from opening new tabs?

In Google Sheets, I have two completely separate spreadsheet files. In Spreadsheet A, there is a cell with a link to open Spreadsheet B, and vice versa. When I click on the link, the other ...
user avatar
3 votes
2 answers
598 views

Conditional Formatting in Google Sheets

The shorter question to spare you from reading more than you need to I have a few different formulas for use in conditional formatting with custom formula in Google sheets. They are all roughly 1100 ...
user avatar
3 votes
1 answer
150 views

How to automatically convert all Microsoft Office file attachments in Gmail to Google Docs?

My Gmail has thousands of emails. Each email will have several attachments as Microsoft Office files (.doc, .docx, .xls, .xlsx, .ppt, .pptx). Currently, I want to automatically convert all Microsoft ...
user avatar
  • 31
3 votes
3 answers
618 views

How to remove duplicate words in a cell only if longer than a certain number of characters?

I need to remove a duplicate text in a cell but only if it is longer than 4 characters. I have this formula that only works for any types of duplicate words: =join(" ",unique(transpose(split(A1,", ")...
user avatar
3 votes
3 answers
394 views

Conditional formatting for columns, date & time specific?

I have uploaded 4.000+ Venmo donation data sets into Google Sheets for sorting, but the date and time is preset and looks like this: 2018-11-01T20:16:52 I have tried multiple approaches to sorting ...
user avatar
3 votes
1 answer
2k views

Reference Cells in Sorted Filter View

I have a Google Sheet that looks like this: That Google Sheet has some Google Script that looks like Google This: function myFunction() { var ss = SpreadsheetApp.getActiveSheet(); var rng = ss....
user avatar
3 votes
1 answer
131 views

Selecting a single column from all rows for chart range

I want to specify a certain column from all rows (omitting the first few) to be the range for my chart. Currently I input this to specify the range: L6:L (note: this automatically changes to L6:L20 ...
user avatar
  • 131
3 votes
1 answer
2k views

Simultaneously sort multiple ranges on a single sheet in varying orders

I am trying to find a way using a script if possible (or formula) that will allow me to reduce the number of operations that I have perform to sort data in a particular google spreadsheet. Currently, ...
user avatar
  • 31
3 votes
1 answer
4k views

Can I precisely position charts in Google Sheets?

I am using Google Sheets to create a "Monthly Report" spreadsheet for a product, and am attempting to make an attractive, compelling experience for the consumers of the report. In this spreadsheet, I ...
user avatar
3 votes
2 answers
1k views

How to Modify Matched Cells Across Multiple Sheets with Google Script

My question is a fairly complex one, but I'll do my best to clarify exactly what I'd like to do and this requires some background: I have a Google Spreadsheet used for user administration in my ...
user avatar
2 votes
1 answer
39 views

I wish to make my Google sheet slightly easier for me to use

Can anyone teach me how to autofill the "Selling At" column E and "SC Used Per Transaction" when I key in the "Denomination Sold" Column? So for example, when I key in &...
user avatar
2 votes
0 answers
28 views

If there anyway i can check if the cell contains the result of a arrayformula()?

For example i have this in A1 : =arrayformula(B1:B100) If in A10 i accidentally enter a value (which will overlap with arrayformula result) , it will produce error in the A1 because of existing value....
user avatar
  • 195
2 votes
0 answers
53 views

How do I specify a consistent interval for a date-range axis in a Google Sheet Chart

I have a Google Sheet with a set of deposits and withdrawls and the running balance. I have added a chart to the sheet, so I can visualize what the balance has been over time and when deposits or ...
user avatar
2 votes
1 answer
125 views

Drag to fill/autocomplete dates only increases year, not day

Please see screenshot below. When I dragged on the blue square in highlighted cell, I only get the result below. I have set correct region in sheet settings. I have set the correct date format in the ...
user avatar
  • 21
2 votes
1 answer
56 views

Check if a string appears within another cell, and return the longer of the 2

I am having a hard time thinking of an formula that can help me pull the data needed in this example: In the C1 cell, I would like to make a formula that does this: Check if any cells in the A ...
user avatar
  • 21
2 votes
1 answer
50 views

Replace first value in a given range (column)

How would I look up the first value < 0 in this range and replace it with 0? If the value <0 does not exist, I'd like the column to remain intact. I've been struggling with this for a day now ...
user avatar
2 votes
1 answer
23 views

How do I write a formula while referencing another sheet in Google Sheets?

So I want to do a CountA on Column A and Column B in Sheet Two and divide Column A by Column B in Sheet One to get the percentage totals. Can I write all that in one line? =SUM(Sheet!((COUNTA(A2:A516)/...
user avatar
2 votes
1 answer
31 views

Getting the slope of the 10 cells above current

I'd like a formula for Google Sheets, but I'm having trouble figuring it out. Say I'm on B74, I want the equivalent of =SLOPE(B36:B63,A36:A63). However, I want something that will work even when I add ...
user avatar
  • 21
2 votes
1 answer
41 views

Query date time

I am writing a function to extract date, where my Col1 consists of date and time and my Col2 consists of date only. =query({Sheet4!A1:Q},”select Col1, Col2 where Col1 = date ‘”&TEXT(today(),”yyyy-...
user avatar
  • 21
2 votes
0 answers
367 views

Sheets - display ISO8601 string as localized date

In Google Sheets, is there a way to have a cell display its value as a localized date (and time) but actually still have and accept its value as an ISO8601 date string? The use case is that I have ...
user avatar
  • 121
2 votes
1 answer
38 views

How do I input a current balance in a cell from a running balance that will be updated frequently?

I know I'm not accurately explaining that but here's a sample spreadsheet of what I mean:
user avatar
2 votes
1 answer
478 views

Is there a way to get multiple share links from Google Drive in order?

I have 100s of files on my Google Drive where the files are sorted by name. All I want is the share link of each file in the order they appear in Google Drive. I tried selecting all these files inside ...
user avatar
2 votes
0 answers
18 views

Customize point style in scatter chart [Google Sheets]

Is there a way to add new point shapes (import new symbols) in Google Sheets charts? Also, can I customize there color fills, or remove the infill color?
user avatar
2 votes
1 answer
468 views

Average the last N values in a Sheets column using Arrayformula

Experts, I need your help... I'm looking to create a formula (not necessarily an arrayformula), that will automatically calculate an average. This sounds easy, but surprisingly, it isn't. Let me ...
user avatar
  • 21
2 votes
1 answer
52 views

Howto sync offline sheet from Sheets app

I did some offline work on a google sheet file on my mobile using Sheets app. I actually did not realize that I am offline, as I never set this option, at least not on purpose. After I connected to ...
user avatar
  • 121
2 votes
2 answers
734 views

Group by in query Google Sheet select different columns

Want to get the results described on the screenshot. How can I achieve it. QUERY: =QUERY(A2:F12,"SELECT A, MAX(B), MIN(B) group by A label A 'G V1', MAX(B) 'LAST V2', MIN(B) 'FIRST V2'") Want to ...
user avatar
  • 71
2 votes
0 answers
369 views

Why do Google Sheets filter ranges sometimes auto-expand to include new data, and sometimes don't?

When I create a filter in Google sheets, I want the range to automatically expand as I add/populate new rows. Sometimes, this works fine: When I have a column using “Checkbox” data validation (...
user avatar
2 votes
2 answers
168 views

How do I increase google sheets cell number by adding 20 in a formular

I would like to use "+" or "- " in referencing a cell eg =i(10+23) = i33 . So that I don't have to increment the cell reference manually . Any help would be great ?
user avatar
2 votes
1 answer
37 views

Sums with Same Value - Follow-up Question

This is a follow-up to these questions and information. Thank you Jacob and Kowser for the formulae you shared many years ago! The information you shared is almost exactly what I need to simplify a ...
user avatar
  • 21
2 votes
0 answers
229 views

How to Group Set of Rows & Skip One Every Time

I'm currently working in a Google Sheet that has about 12,000 rows. Because of the layout, I want to group every 5 rows, then skip one, then group the next 5. Right now, I have as script that is ...
user avatar
2 votes
0 answers
149 views

Create a pivot table with a column for each assignee, containing rows of task descriptions

I have a two-column table with the first column being "task description" and second column being "assignee". This is something I'm unable to influence. Crude representation: I'd like to be able to ...
user avatar
2 votes
1 answer
84 views

How do I get the sum of the values of cells adjacent to (left of) a given key?

For example, given the following table, I'd like to return the sum of all cells directly to the left of all cells that contain the string 'joe' (6.0). | A | B | 1 | 2.0 | joe | 2 |...
user avatar
2 votes
1 answer
91 views

How to make a cell autopopulate the date dependent on another cell

I'm trying to make a recruitment sheet for an online community I help run. What I'm currently attempting to do is that when we record a person has joined the Discord to column B, it'll automatically ...
user avatar
  • 43
2 votes
0 answers
48 views

How to use COUNTIF and UNIQUE

I have a Google Sheet with two columns. One containing a list with order numbers - the order numbers can be listed several times/duplicates. The other column consists of the a free text with the name ...
user avatar
  • 21
2 votes
2 answers
2k views

Import button greyed out in Google Sheets menu for unknown reason

I'm trying to import a .csv file into my Google Sheet like I've done myriad times. However, occasionally, the Import button in the menu is greyed out (see screenshot). Reloading the document doesn't ...
user avatar
2 votes
0 answers
673 views

Google Sheets: Filter Row if it does not contains HYPERLINK

I have a sheet which has a column where most cells contain a hyperlink, I am looking for a way to find cells which do not contain a hyperlink and delete those rows. Filter by condition -> Text ...
user avatar
2 votes
1 answer
1k views

Auto sorting by multiple columns in Google Sheets

I have a data-set which I would like to have sorted with first column 6 and then column 7. From this post: How can I make some data on a Google Sheets auto-sorting? I have gotten ideas about making a ...
user avatar
2 votes
1 answer
53 views

Filter function with multiple variables google sheets

I want to be able to use a filter function for people in NAME whose P DATE is within a specified range. However, if they have a Y under VALIDATED, I want the filter to also include names with the T ...
user avatar
  • 21
2 votes
0 answers
1k views

How to check if there is any Data Validation error in the whole spreadsheet?

Is there a way to check in a Google Sheets spreadsheet, if there is at least one cell violating a Data Validation rule? If such functionality is not built-into the app, how can I write a Google Apps ...
user avatar
2 votes
0 answers
95 views

Custom number formats in Google Sheets: Issue displaying negative numbers

Context: Looking to display large numbers used in financial reporting & analysis in an abbreviated easier-to-read format. I currently have the GS custom number format as follows: [>=999950]#,...
user avatar
  • 121
2 votes
1 answer
620 views

*UPDATE* Hide/unhide certain columns on some conditions

UPDATE: I FIGURED IT OUT!!!! YEY!!!! Im trying to have certain columns in my Google Sheet hide or unhide, depending on some conditions. I realized that this is difficult. But I've managed (I think) ...
user avatar

1
2 3 4 5
39