So this week I've been out in San Francisco at Dreamforce '07, and I've learned some incredible things about Salesforce.com -- better ways to extend what we're already doing and some cool new features do to hit this winter. I've also met some amazing people. One such person is Ezra Kenigsburg, a consultant and Salesforce.com Data Architect out of Chicago working in the Professional Services group.
Turns out Ezra is a bit of an Excel geek, too. Actually we met at a breakfast discussion of analytics, and he was mentioning that he was "...a bit of an Excel geek..." so I jumped in and said, "Oh, really? Well, I am THE Excel_Geek." We got along just fine from then.
Once we were finished chatting about Salesforce stuff, we jumped on this blog, so that he could see what I was all about. The last post on "Filling in the blanks..." was up, and a second or two after he read it, he showed me a far better, simpler way of doing was I had done.
Ever heard of "Edit --> Go To... --> Special"? Well, it's a pretty cool, little known feature. What you can do is select a large range, like we were dealing with in "Filling in the blanks..." and go to Edit --> Go To... --> Special, and select "Blanks". This will select only those cells within the range that are blank. (There are many other cool options, too, like selecting only formulas, comments, conditional formatting, data validation, etc. Oh the possibilities! Anyway...) Once you've got only the blank cell selected, if you click up into the Formula Bar and type in a formula you want -- in our "Filling in the blanks..." case, we wanted to average the four cells immediate left -- and instead of hitting <Enter> you'll hit <Ctrl> + <Enter>, which will copy that formula, adjusted for relative ranges, into all selected blank cells. Wow. The simplicity. Wanna make 'em all red now, simply Format --> Cells... --> etc.
Thank you, Ezra. Truly a pleasure to meet you.
Wednesday, September 19, 2007
I found another Excel geek at Dreamforce
Subscribe to:
Post Comments (Atom)
1 comment:
A pleasure meeting you too, Eric!
Two random thoughts on reading the original "Filling in the blanks..." post:
1) I'm a fan of the radical reduction of IF tests. Any time I see an IF test that returns either a True or False, I think, "Is that IF necessary?"
Frinstance, in your code, you have
If Left(CEL_REF.Formula, 1) = "=" Then
ISFORMULA = True
Else
ISFORMULA = False
End If
This can be refactored into a single line as
ISFORMULA = (Left(CEL_REF.Formula, 1) = "=")
It takes a little getting used to, but code typed using the latter style (a) runs faster and (b) has more geek cred :-)
This principle also applies to the IF spreadsheet function. For example, if you wanted to test whether a certain cell equals five, you might type
=IF(A1=5, True, False)
That formula works exactly the same as
=A1=5
but the latter formula will run faster, which is handy if you have thousands of rows.
2) Excel users love love love to draw attention to specific rows or cells by highlighting or coloring them. While I have no problem with colored rows/cells per se, it can be hard to find all the colored rows/cells in a big XLS. Whenever I'm asked to color specific rows/cells in a big doc, I also create a new column and populate the appropriate rows with a value ("Error row", "outlier", whatever) so that the dataset can be easily sorted later.
Happy data-crunching!
Post a Comment