Share your favorite formulas, functions, tips, and tricks in Excel or Access

Last week I wrote a post about how I love books and recently used our e-reference library to find the CONCATENATE function in Excel. This week, though, I want to confess that I’ve learned most of my favorite tips and tricks in Excel and Access from friends and fellow co-workers. It’s amazing how few of the features and functions we all use in these programs. Whenever I hear about someone using a function or formula I’ve never used, I can’t wait to learn it.

I’ll share a couple of my newly learned tricks with you here. I invite you to write comments to this post and tell us how you use a particular function. If you can, show us an example.

CLEAN

This function made me finally appreciate VLOOKUP. Almost everyone I know who works in spreadsheets uses VLOOKUP frequently. I’ve never liked it, though, until last week. I was talking with my new friend, Preston, at O’Reilly about combining information from two worksheets. I told him that I always import each worksheet into Access and create a query linking the two tables by their unique identity ID, such as our book ISBN code. He asked me why I didn’t just use VLOOKUP. I explained that whenever I tried to create that function, I often found that my cell types never matched. I would highlight a column and attempt to format the cells as either number or text. No matter. My formats never seemed to “take.” How annoying. Preston explained that solving the format problem was as simple as creating a new column and using the CLEAN function. Voila! I was converted.

So simple, and it makes creating VLOOKUP functions so painless.

Cross tab query

Another new lesson from Preston. I wanted to create a chart that showed how many books we published on SharePoint technologies to each audience: developer, IT pro, and office worker. I wanted it to look like this:

Here’s how easy it was to create the table once he showed me how to use the Total cell and Crosstab cell. Notice that I used the field ISBNCode to count how many books were published per topic and audience.

I know that a lot of you reading this blog are pretty sophisticated developers. I’m sure that you could offer up some cool tips and tricks. Bring em on!