# Tag Info

154

When you're focused on the cell press enter to get into edit mode: Windows/Linux/Chrome OS: Ctrl + Enter Mac: Command/option(Alt) + Enter

135

Up to ten rows of each sheet can be designated as the column header. This is called freezing rows. Every sheet has a horizontal guideline which separates header rows from the rest of the sheet. This guideline has a gray handle. The gray handle initially appears above row “1” on the left hand side of the sheet. To set the number of rows in the column header, ...

109

By using the QUERY function you can achieve your goal. Formula =QUERY(Data!A1:B6;"select A, sum(B), count(B) group by A") Screenshot Using other functions, makes it achievable as well. add this formula in A2: =UNIQUE(Data!A2:A) add the following formulas in B2 and C2 and copy down to B4 and C4 respectivey =SUM(FILTER(Data!\$B\$2:B;Data!\$A\$2:A=A2)) =...

92

If you're using the "new" Google Spreadsheets, in the "Edit" tab menu, under "Paste special", the last menu voice is "Paste transposed": it does exactly what you need in a single task.

81

Right-click the selection you want to have the date picker show up for (i.e. single cell, entire row, entire column) and then open data validation. Set Criteria: Date is a valid date and click Save. Now just double-click the cell!

74

From https://support.google.com/docs/answer/6325535?hl=en : Open a spreadsheet in Google Sheets. Paste the data you want to split into columns. In the bottom right corner of your data, click the Paste icon. Click Split text to columns. Your data will split into different columns. To change the delimiter, in the separator box, click PS: There is no option ...

73

That was way too convoluted, there's a MUCH simpler way to do it, I've added another field "quick and easy checkboxes", a quick and easy way of doing it is to: Right click cell, select "Data Validation" Under Criteria, select "List of Items" In the "List of items" field, paste this: ☐,☑ Save. You should now have a drop down list of both of the above options....

65

Google has added a new number format called Duration. Set your start and end fields to Format -> Number -> Time and your calculation field to Format -> Number -> Duration Once you have done that you can subtract the fields to get the difference as noted by Stefano Palazzo in his answer.

59

When you switch to a different sheet in Google Spreadsheets, pay attention to the URL in your browser's address bar. At the end of the URL you should see something like: #gid=0 This number changes when you switch sheets, and specifies which sheet to display. Copy the entire URL and create a hyperlink to it with this formula: =hyperlink("https://docs....

58

If your weights are in row 2 from B to L, and the data you want to average is in rows 3 and higher, say, then you can use sumproduct as follows: =sumproduct(B\$2:L\$2, C3:L3)/sum(B\$2:L\$2)

57

You can import data from another spreadsheet using the importrange() function: =importrange(spreadsheet-key, range) spreadsheet-key is part of the url of the spreadsheet that contains the data you want to import. It looks like "0AsaQpHJE_LShdFhSRWZBWWMxem1pZGxhWG1XZzlic0E". range defines the cells you want to import. In your example, you would look for ...

52

=COUNTIF(\$A:\$A,"="&A1) < 2 If you put this in as a Custom Formula for the Data Validation rule for Column A, Column A will reject all duplicates.

52

By adding a \$ in front of the column & row you can lock that specific area up when copying. Example: Totais!\$C\$30 would lock down when copying anywhere else in the document. Totais!\$C30 would lock down moving column to column. Totais!C\$30 would lock down when moving down rows.

52

Please try: =countif(G:G,6) if you want to find the number 6 in ColumnG:G and/or =countif(A1:B7,"d") if you want to find d in the first seven rows of the first two columns. By find I really mean count the number of instances in the chosen range. Ref: Documentation for countif

51

Yes, If your time fields are properly formatted (click Format → Number → Time) you can just add and subtract times: =C2-B2 or 21:58:00 - 20:44:00 = 1:14:00 This will give you the time delta as HH:MM:SS. And if you want to calculate the number of minutes, you can use the Hour(), Minute() and Second() functions on that field: =(Hour(D2) * 60) + Minute(...

50

Assuming names are in A1:A8, amounts in B1:B8, first we need unique name list in D1: =UNIQUE(A1:A8) Then use conditional sum in E1: =SUMIF(A\$1:A\$8,D1,B\$1:B\$8) Please note \$ prefix before cell number. This will keep the same ranges when we copy cell contents down (select cells E1:E8 and press Ctrl+D).

49

Go to Tools → Script Editor and add this code: function sheetName() { return SpreadsheetApp.getActiveSpreadsheet().getActiveSheet().getName(); } In a cell where you want the sheet name add: =sheetName() There was an issue with accessing sheet names via API functions shown below—where it only returned the first sheet name. This was fixed in March 2015....

49

You can use REGEXMATCH: =IF(REGEXMATCH(A1, "sites"), 1, 0) To explain, REGEXMATCH returns true if and only if the argument is a substring of your string.

48

Just realised FILTER should be comma separated: =SUM(FILTER(A1:A5,B1:B5="Yes"))

46

When I double click on the cell itself, and then hit the insert button, the overwrite will toggle off for not just that cell but also for the entire application.

45

If there's a difference between 50 and 050, what you have are strings (i.e. text). Changing the cell format to Plain Text via Format>Numbers>Plain Text will keep leading zeros.

43

Use the following formula to obtain the query you want. Formula =FILTER(A:A;(A:A="Combat Medic")+(A:A="Universal")) Explained In the reference it is explained by Ahab that + and * can be used as boolean expressions, as OR and AND. The solution with the IFERROR (see revision history) is of no use in this case, unless you want to find a particular string: ...

43

Yes, you can create a custom function for that. Click Tools → Script Editor. Select Create blank project. In the script editor window, paste the following code: function encode(value) { return encodeURIComponent(value); } Save the script (give it a name if prompted), and return to your spreadsheet. Now, you may use the function as =encode. If ...

42

41

It is also possible to use Google Apps Scripts to achieve automatic in-place sorting of the data. This may be more difficult to achieve and more error-prone (I'd still go for William Jackson's solution, +1 BTW), but I thought it was interesting enough to show. I have a sheet that looks like this: I added a new script, using these steps: in the menu, go ...

41

All date/time values in spreadsheets are internally handled as floating point values. To add 3 hours to a date/time just add (3/24) to the original date/time. =F3+(3/24) This also works in MS Excel.

39

If you prefix the data in the cell with a single quote—'—it should prevent any default formatting.

39

While it's not directly possible in the current version with its own keystroke, there is another very simple way to accomplish this. First, be located in the row on which you want to insert a row above or below. Then do the following (keys are on Mac, so translate as necessary for your OS): Ctrl + Option + I - invokes the Insert menu Then hit the R key ...

38

Google Sheets supports the following keyboard shortcuts: Ctrl + Shift + : is the keystroke to insert time. Ctrl + ; is the keystroke to insert date. Ctrl + Alt + Shift + : is the keystroke to insert date and time. See Keyboard accelerators, mnemonics, and shortcuts for more details.

38

Finally my original answer is completely obsolete. Google updated features with Alternating Colors features in "Format" menu item. There you can easily select a default scheme and customize as you like: Be sure to remove your old manual formatting, as this doesn't automatically replace/override. === OBSOLETE BELOW THIS LINE === Google added this feature ...

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