I use Google Spreadsheets for a few things (and other doc types for a few others), but I usually do hard-core editing in Excel. Â Export from the web, edit in Excel (or more likely Calc), reimport into Docs. Â Hardly elegant, but when I'm going to be cranking a workbook for two or three hours it's worth it compared to editing online.
I was getting set up to do just that this evening, and it occurred to me that the online experience probably doens't suck nearly as much in Chrome. Â So I went and grabbed it, set it up with the latest dev build (instructions), and fired 'er up.
Much to my delight, it's actually quite good. Â They also added a multi-line editor for cell values that you can resize as needed, which makes editing huge formulas way easier. Â No more copying out of the Excel bar into TextPad to edit a formula and then copy it back in. Â Still a few issues with keyboard navigation, but they seem to be bound to the fact that "type in the cell" and "edit the cell" are expressed in very similar ways, so you can't tell which mode you're in without thinking pretty hard. Â I'm trying to figure out what's different with Excel (where I don't recall having this problem), but I can't put my finger on it.
Spreadsheet's filter function is also pretty slick. Â Excel has roughly equivalent functionality in it's DB functions, but filter is way slick. Â You don't have to define a database range, it just figures it out, and you don't need explicit criteria cell pairs, you express the filter clauses directly in the formula. Â It took me a few minutes of struggling to figure out what the syntax really meant by the examples in the docs and how it applied to my problems, but it quickly became obvious once I willed the nasty Excel way out of my brain.
In any case, I think I'm done with the roundtripping via Excel. Â With a kick-ass JS engine behind it, the online version is close enough to a real spreadsheet app. Â Recomputing complex formulas is still slower than I could like, but that's about my only remaining complaint. Â And no, I didn't do any actual comparative profiling, but again, I don't recall seeing significant lag with Excel when I've done complicated stuff on there.
I'll have to pick you brain about this. The editing in Google Excel is S.L.O.W. I don't want to spend the time so I've been doing the roundtripping as well.
See you soon!
All you need to do is install the Chrome browser, point it at http://docs.google.com/ and go. I added a link to it in the post above (forgot the first time).
how do you filter in google spreadsheet?
Here's a simple example:
=sum(filter(F:F,A:A="23″,B:B="barney"))
That results in a single number that is computed by summing all the cells in column F where the cell in column A of the same row is "23″, and the cell in column B of the same row is "barney". If you omit the sum() call, you'll get an array of cells that will be shown starting at the cell you put the formula in and moving downwards until they run out (e.g. if the filter matches 4 cells, the values will display in the cell you put the formula in, along with the three cells below it). You can also create multi-column results by changing the first (selection) parameter.
I have a spreadsheet of projects assigned to people on my team. Column A is the persons name. There are hundreds of rows in the spreadsheet. How do I setup a filter on column A to just show me the projects for "Tina" ?
The example a couple comments up only pulls a single column ("F") and filters on two columns ("A" and "B"). So in your case you'll need something like this:
=filter(B:Z,A:A="Tina")
The first range ("B:Z") is the columns you want to select. You don't need 'A' because that'll always be 'Tina'. You'll need to change the 'Z' to whatever your last column is. The second range ("A:A") is what you want to filter on. The net result is a filter which says "I want columns B to Z from any row which has 'Tina' in column A".
That all makes sense Barney… But where am I actually placing the formula? In A1 or do I select the column? Sorry if these sound like stupid questions, but I've been on Google for 2 days.
tina
You're not filtering the rows in-place, you're creating a new listing of rows by filtering the "real" rows. Look at cell E2 in this spreadsheet:
https://spreadsheets.google.com/ccc?key=0AnVO3WmYAWTzdGl0UDdXQ3pGV3hmYXhfaklpdG5rU2c&hl=en
Excellent ! Thanks Barney ! One more thing, If I wanted to have a tab with everyone's projects listed, and then individual tabs, like Tina, Barney, and Chris – how would I do that? Can I reference a different sheet in my filter?