Tag Info

162

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

120

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)) =...

102

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.

91

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 ...

87

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!

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....

69

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.

68

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.

64

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

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)

58

=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.

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 ...

54

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(...

54

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).

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.

50

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

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

48

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.

46

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 ...

46

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.

46

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 ...

45

You can use Pivot Tables to group data in Google Spreadsheets.

45

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: ...

44

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.

44

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 ...

41

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

40

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.

39

Use the following short cuts. Shotcuts Ctrl + Shift + PageDown ==> Move to next sheet (Option + Down arrow on a Mac) Ctrl + Shift + PageUp ==> Move to previous sheet (Option + Up arrow on a Mac) Screenshot Reference Keyboard Accelerators

38

Open Spreadsheet Click on View from the menu Click on Freeze rows from the dropdown menu Choose how many rows you want to freeze. Frozen row(s) will stay at the top of the sheet when viewing other portions of it.

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