Skip to main content
3 votes
Accepted

Select full column in Google Sheets by [@column]

I recommend that you use named ranges. For instance: 1.) Select all of Column A by clicking on the "A" at the top of that column. 2.) Select Data > Named ranges. 3.) Enter a descriptive ...
Erik Tyler's user avatar
  • 5,048
3 votes

Excel to Google Sheets Script

Just like Microsoft's VBScript, Google has its own language - Apps Script. It lets you automate sheets with a Javascript-like syntax, should be easy if you know Javascript. Tutorial and documentation ...
David's user avatar
  • 76
3 votes
Accepted

Does importrange not work on Google Sheets embedded XLSX files?

The importrange() function can only read data from Google Sheets and therefore cannot read data from Microsoft Excel files, even though you can view and edit such files with Google Sheets. I do not ...
doubleunary's user avatar
  • 14.9k
2 votes

How convert VBA to Google Sheet script (to lock columns based on data)

Short answer The script should be wrote by and end-user/developer. Explanation Google Sheets doesn't include a way to automatically convert VBA code to Google Apps Scripts. By the other hand not ...
Rubén - Volunteer Moderator -'s user avatar
2 votes
Accepted

Exporting date fields from Cognito Forms to Excel on Mac transforms date by 4 years

Seems your Excel is set to the 1904 date system (more likely for Mac users than PC users). This is an Excel option. Dates in Excel are index numbers that by convention start either in 1900 or 1904. ...
pnuts's user avatar
  • 17.7k
2 votes
Accepted

How do I calculate a sum based on a column number which is the value of another cell?

This is a job for the INDIRECT function. For the sum in A8, use =SUM(INDIRECT("R2C" & B8 & ":R5C" & B8; FALSE)) Likewise, for the sum in A9, use =SUM(INDIRECT("R2C" & B9 & ":R5C"...
Vidar S. Ramdal's user avatar
2 votes

Rich text formatting in a Google Sheets cell

Rich-Text formatting is not an OPT in/out / turn on/off feature. It's always turned on. To use it: Put the cell in edit mode Type the text Select the text part to apply bold, italics, color Apply the ...
Rubén - Volunteer Moderator -'s user avatar
2 votes

In Google Sheets or Excel, how can I write a formula to SUM() a range based on the criteria that none of the respective cells are blank?

Here's a shorter option: =IF(COUNTA(B2:F2) < 5, "", SUM(B2:F2)) The 5 is the number of values you are looking for. (The number of days of the week you have listed)
Phil Anderson's user avatar
2 votes

How to organize and add multiple tags to a list?

It's not an easy task to do in a spreadsheet. But one simple solution could be to make a new column that makes the tags filterable, using an expression like =CONCATENATE("|", REGEXREPLACE(B2; " *, ...
Jesper's user avatar
  • 306
2 votes
Accepted

Keeping referenced data organized after sorting

You should not blindly trust that each name will continue to be at the same row number on every sheet. Use a vlookup() or filter() instead. Try this: =sum( iferror( filter(Earned!B$2:ZZ, Earned!A$2:A =...
doubleunary's user avatar
  • 14.9k
2 votes

What's the best way to upload a million row spreadsheet as Google spreadsheet?

Google Sheets has hard-and-fast limitations. One of them is that no spreadsheet may have more than five million cells total among all sheets/tabs. If you are trying to upload 1M rows, and those rows ...
Erik Tyler's user avatar
  • 5,048
2 votes

How can I conditionally format any empty cell from column C to L if the respective cell in column B not empty

The solution is to apply conditional formatting to the C2:L range with the custom formula: =and($B2<>"",C2:L2="")
Daniele's user avatar
  • 2,428
1 vote

Automatically Color Series In Chart

The following answer was developed piecemeal and many elements could have been combined to create a more efficient process. However, each of the functions will run standalone and this may assist the ...
Tedinoz's user avatar
  • 5,741
1 vote
Accepted

Check if any cells in one column fall between cells in different columns in Google Sheets

Try this formula: =isbetween(A3:A6,B2:B5,C2:C5) you could conditional format column A (A3:A1000) with custom formula: =and(A3<>"",isbetween(A3,B2,C2))
Daniele's user avatar
  • 2,428
1 vote

How do I put the if function inside the lookup? - Daily Routine sheet

I'd use Index and Filter (Filter () creates a list of upcoming tasks, Index () takes only the first of those) =index(Filter(A2:A,timevalue(B2:B )>=timevalue(now())),1,1) to get something like this:...
Daniele's user avatar
  • 2,428
1 vote

A way to "lock" and "unlock" a Google Sheet's contents

An editor of a spreadsheet cannot modify sheets or ranges that have been protected from edits. However, if they are the owner of the spreadsheet, they can always edit any cell directly. The exception ...
doubleunary's user avatar
  • 14.9k
1 vote

Google Sheets Formula Not Working in Excel

Turns out Excel does not like A5:A. A needs to be defined. A5:A1000 will work.
plet9090's user avatar
1 vote

How can I highlight the source cells in a formula (e.g. =SHEETA!$E$568 )

Try this script. To access it, either put it in a custom menu or create a shortcut. function jumpto() { var ss = SpreadsheetApp.getActiveSpreadsheet() var ui = SpreadsheetApp.getUi(); // ...
Tedinoz's user avatar
  • 5,741
1 vote
Accepted

Transferring an Excel document to G Sheets, how do I fix the slightly broken auto-borders implementation?

With @Rubén's insight into how Google Sheets was inheriting border-applying conditional formatting rules from Excel (without a way to access or edit those rules), I was able to figure out a different ...
gills's user avatar
  • 192
1 vote

How do I get data from the 'Statement' sheet to automatically sort into the 'Expenses' sheet?

Check this sheet (Make a copy for full access) Note the Helper Pivot tab, which is referenced by formulas in Expenses tab. You can hide that tab in the final spreadsheet. The formula =iferror(...
GoranK's user avatar
  • 171
1 vote
Accepted

Sheets or Excel - sum values in column a for each unique date and time in col b

If your times in Col A are hour-minute as shown (i.e., no seconds): =QUERY(A:B, "Select A, SUM(B) WHERE A Is Not Null GROUP BY A LABEL SUM(B) ''") This assumes your values start in A1:B1 (i....
Erik Tyler's user avatar
  • 5,048
1 vote

How to move “answers” rows into under column “questions”?

Please use the following FLATTEN formula =FLATTEN(A2:C4) OR ={"Questions";FLATTEN(A2:C4)}
marikamitsos's user avatar
  • 12.9k
1 vote
Accepted

How to get maximum value from rows, only from specific columns

You need following formulas: MAXIFS and MINIFS. =MAXIFS(A2:G2,$A1:$G1,"bar") =MINIFS(A2:G2,$A1:$G1,"bar")
Oleg_S's user avatar
  • 791
1 vote

I wrongly set the date of 2000 cells. I want to add 20 years of those cells. How?

Try using the EDATE() formula. Since it adds months you'll need 240. Something like this: =ARRAYFORMULA(EDATE(A2:A2000,240)) Then paste values. Alternatively, you could use this formula: =...
Oleg_S's user avatar
  • 791
1 vote

IF Statement works in Excel but not G Sheet?

The correct formula is: =IF(G15=TRUE,1,0) That is, drop the quotes around TRUE.
1 vote
Accepted

Creating Hyperlinks of Google Drive Items in Alphabetic Order

An option is to output the file name as well as the HYPERLINK() function. This should allow you to: sort the output, lookup the file name and return hyperlink (or vice versa, according to your ...
Tedinoz's user avatar
  • 5,741
1 vote

Why do Google Sheets and Excel give different exponential regressions?

From https://developers.google.com/chart/interactive/docs/gallery/trendlines#exponential-trendlines Note: Unlike linear trendlines, there are several different ways to compute exponential ...
Rubén - Volunteer Moderator -'s user avatar
1 vote

Format Google Analytics Date

You need to use TEXT, instead of TO_TEXT: =TEXT('90diaspessoas'!A16,"dd-mm-yyyy" ) As long as underlying date number is correct, This will work. OR Set formatting in '90diaspessoas'!A16 ...
TheMaster's user avatar
  • 3,342
1 vote

Excel spreadsheet into body of Gmail message without losing formatting

Short answer Apply "real" border lines. Explanation The "default borderlines" in Excel and other spreadsheets application are named grid lines. They are a visual aid, not real ...
Rubén - Volunteer Moderator -'s user avatar

Only top scored, non community-wiki answers of a minimum length are eligible