Excel Tips & Tricks

I’m a foodie at heart, but a digital marketer & strategist by day.

Microsoft Excel is a tool that no one in business can be without.

excel

Excel is a powerful tool, and what I do barely scratches the surface. I remember taking several computer classes in Junior High (on those beautiful green Macintoshes), and in High School (Windows 98 and 2000 baby!); now that I’m pushing 30 (ugh), I fully expect that everyone younger than me should be a pro at this program and at computers in general—I mean, kids start using tablets by the age of 1 now, right?

Still, I’m shocked at the amount of people I come across who still don’t know how to use Excel’s basic functions. I cringe when I see someone use their iphone to calculate a sum and then t-y-p-e that number into a cell. Why do all that extra work when Excel does it in a snap (and also greatly minimizes your margin of error)?

So here are a few basic tips and tricks for you, my fair reader, in the hopes that I’ve shaved off at least 1 hour of manual labor for you:

1. Quick Sum

=SUM([letter][number]:[letter][number])
=AVERAGE([letter][number]:[letter][number])

That’s it.

2. Unique Entries and Data Sort

I manage a lot of contests and promotions and my team handles several exports of data on a regular basis. Duplicate data is useless when you’re building an email list, so we constantly gather unique entries when determining contest winners (most of our programs stipulate one entry per person), and newsletter imports.

A quick way to gather unique entries is to select a column and then go to
Data → Filter → Advanced Filter

Here you ensure your column is in the List range, then check ‘Unique records only.’ You can either copy those names to another location, or just have it filter the list, in-place.

Typically I filter by email address, since our parameters require a unique email address per entry. Sure, if you wanted to be really crafty you could enter several different email addresses in the hopes you’ve increased your chance of winning…but not so fast. A quick data sort of your columns (highlight your first row and then go to Data → Filter → auto-filter and handy-dandy drop-down menus will appear) and you can check to see if your random winner has any other duplicate names around.

3. Pivot Table

These are perhaps one of the most complex concepts in Excel to grasp, but they’re quite easy to set up and make a world of difference! A pivot table is a great way to pluck out the important data you need. In my case, I needed to report the sum of impressions and clicks by title (what website?) and size (728×90 or 300×250?). After painstakingly using a data sort to attempt this feat in a more manual way, my husband showed me the pivot table.

Select all of the data in your spreadsheet, then go to Data → Pivot Table.

I like to create a new sheet for my pivot table. Then, you “build” your table by dragging and dropping the elements of your data in there. It will default to a ‘count’ of the data, but you can easily change that to ‘sum’ by double-clicking on the tile.

I realize this is the tip of the iceberg when it comes to Excel. There are so many formulas (like COUNTIF) that I also find helpful, but more often than not I need to Google it and find step-by-step instructions when attempting to use it. Don’t be afraid to Google things–if you think “There must be a better way!” when working with data in Excel, there probably is. Luckily the man uses Excel quite a bit at his job too, so we’re able to swap notes and share our tips and tricks.

Good luck, and I hope this helps!