February 21, 2018

Excel tricks to make your life easier

I am a HUGE fan of Excel.  Spreadsheets are a wonderful thing that allow you to bring information to life in various forms – charts, graphs, mail merges… If you are fighting the use of spreadsheet software, I’m here to tell you that it really is not as scary as you may think.  Sure, going in, it doesn’t look pretty – that just means that you haven’t put Excel to work FOR you.

There are several tricks that help make using Excel easy.  The problem is, most people don’t know what they don’t know.

I have compiled some key tips that, once learned, will help make any spreadsheet task much easier. Here are just a few tips to get us started.

Separating names

Many times, people insert full names of individuals into the spreadsheet.  This is problematic when using in mail merges as no one really wants to say “Good afternoon, John Doe” – you want to say, “Good afternoon, John.”  Follow  these quick steps to personalize your communications further by creating the ability to drop the last name (and other information later).

How to separate the name column into First Name and Last Name, or taking a column stating Name: John Doe and making it two columns, one saying John and another saying Doe.

  1. Insert column after Name column (preferably two columns to be safe)
  2. Highlight column needing to be separated
  3. Go to Data, Text to Column

a.    Delimited, Next
b.    Select both Tab and Comma, Next
c.    Select either General or Text, Finish

And, there you go!

To change ALL CAP names to Title Case

When working with a form that other people have access to (such as your customer), you may come across the people who like to talk in ALL CAPS.  This can be aggravating as (again) when working with mail merges, you end up screaming at your customer in your greeting.  Follow the steps below to provide consistency throughout your writing, without the messy ALL CAPS.

  1. Insert a column in which to fill the corrected text.
  2. In the new, empty column, enter the formula =proper(a2) and click ENTER.
    NOTE: does not have to be a2, but rather whichever cell is appropriate.
  3. Click the cell you just created (in this case, b2) and drag the bottom-right part of the cell down the column to copy the formula.

All your “CLIENTS” should now be your more adaptable “clients.”

De-Duplicate (De-Dupe)

I haven’t heard of a database yet that people don’t fight with.  One common annoyance is duplicates.  There is an answer to this problem (if you have the ability to export into Excel).

  1. Click on the Email column as this will be the most specific (or whichever is most appropriate for your needs).
  2. Go to Data, Filter, Advanced Filter
  3. Check “Unique Records Only”
  4. Click OK
    NOTE: This HIDES the row, it does not delete it. 

No more duplicates!

I hope you find these tips useful in your upcoming projects.

0 Flares Twitter 0 Facebook 0 Filament.io 0 Flares ×
Phone: (740) 215-5321 | Email: Info@Trudden.com Copyright 2014 Trudden Marketing Solutions. All rights reserved.