Three Favorite Excel Tricks of a Hopeless Nerd

My passion for Excel probably classifies me in the “extreme” nerd category. I love the precision and the efficiency. From a complex table to the simplest grocery list, I proudly use Excel 85.714% of my days (that’s equivalent to 6 days per week).

Below, I’ve listed three of my all-time favorite Excel tricks. If you haven’t yet tried these gems, give it a go!

VLOOKUP

Early in my career, I was a sales support analyst for a candy broker. We dealt with thousands of SKUs from Hershey, Mars, Nestle and dozens of lesser-known candy brands. Each of the candy SKUs had an item number, UPC code, and countless other codes, dimensions, etc.

Before I discovered VLOOKUP, I would spend hours keying in data on various customer forms. Naturally, every customer had its own product selection as well as spreadsheet format. So copy/paste became inefficient and individually keying info like 12-digit UPC codes was an invitation for errors. Then I discovered VLOOKUP.

Basically, the VLOOKUP function “looks up” some value in one column and then returns a corresponding value from a different column within the same row. So back to my candy example, if I needed to fill a customer form with Item Number and UPC in columns A and B, but Hershey provided the information with Item Number in column F and UPC in column P, all I need is a simple VLOOKUP formula which references the Hershey spreadsheet. Keep in mind, the real advantage of VLOOKUP occurs when you have numerous rows of data and you need only create the formula once and then copy/paste it to all rows within your column of data. Also, it doesn’t matter what order the SKUs are listed in my customer form; if it doesn’t match the Hershey spreadsheet, it’s OK!

Click for more details on VLOOKUP

CONCATENATE

Have you ever needed to join the contents of two cells? For example, your spreadsheet contains one column containing a customer’s first name (‘John’) and a separate column containing the customer’s last name (‘Smith’). Using the CONCATENATE function, it’s extremely easy to add a new column that joins the contents to show ‘John Smith’ (or ‘Smith, John’ or any other variation). Again, like above, you’ll want to copy/paste the formula to all remaining rows. Piece of cake!

Click for more details on CONCATENATE

PASTE SPECIAL

If you’re like me and you do a lot of data manipulation with formulas, you’ll want to learn the PASTE SPECIAL command. Let’s assume that you used the ‘concatenate’ formula above to join the contents of column A (first name) and column B (last name) into a new column C (so for example, ‘John Smith’).   Once you have your new column and no longer need columns A and B, you’ve got to be careful what you do next. Do not delete columns A and B until you convert the contents of column C. Even though column C displays ‘John Smith’, what it really contains is a formula: [=CONCATENATE(A1,B1)].   Deleting either column A or B now would return a “#REF!” error. Here’s an easy solution: Select the entire column C and do a “copy” (command+C on a mac or control+C on a PC). Now, while the column is still selected, choose Edit > Paste Special and select “Values” from the menu, then hit “enter” to finish. Now you have converted the formula to the value “John Smith” and it’s perfectly safe to delete columns A and B.

Click for more details on PASTE SPECIAL

I hope these tips are useful for you! If you’re still struggling, we’re just a phone call away.


At inTrust Marketing, we want to make you, the small business owner, look better than ever! Contact us for help with all your business & marketing needs. info@intrustmarketing.com

Leave a Reply

Your email address will not be published. Required fields are marked *