Debbie Mayo-Smith 's Opinion

A motivational speaker gives her tips on business success

Debbie Mayo-Smith: Wow! I didn't know Excel could do that!

13 comments
Be in to win one of two copies of Debbie’s Make Your Database Your Goldmine. Just email Debbie at nzhcontest@successis.co.nz with the word 'contest' in the subject line. Winners will be announced in two weeks.
Photo / Thinkstock
Photo / Thinkstock

Before I share three utterly marvellous time saving tips, I'd like to share a story with you. It's about the genesis.

Can you recall a time when a few words were life changing for you? "Will you marry me"; "it's a boy". "The job is yours".

I can distinctly remember seven words my husband said to me that literally changed my life.

Come back with me to July 17th, 2001. Thursday night. 10pm. Our six children were in their bedrooms fast asleep. I'm in the living room. On my laptop. Working with an Excel spreadsheet.

My husband Steve walks in and peers over my shoulder for a moment. He asked "what on earth are you doing Debbie?" "Oh, honey," I moaned. I want to send out my first email newsletter tomorrow and do a personalised email merge with my database. But everyone's first and last names are in one column, so I'm splitting them apart."

(Have you ever done that copy paste delete, delete dance? Copying the names into two columns, then deleting the last name from the first column and then the first name from the last column? Over and over and over and over again?)

Steve's seven words were: "Don't do that! Use text to columns" "Text to columns? What's that? I never heard of it" I replied. Steve then had me hand him my laptop and he proceeded to show me a function in Excel that with 3 clicks of the mouse split the 2890 names (I had left to do) apart. Instantly. Three clicks instead of at least two, three hours.

You're thinking two things now. Cool. But life changing? It was to me because:

First, emphasized how little I, we know our software. The hidden diamonds beneath the ribbons. The swipes on our phones we don't know to do. Wonderful things waiting for us to discover and save us time.

Second it started my quest for the modern Holy Grail. Time. And saving it. From that moment on I promised myself that when anything repetitious in nature had to be done on the computer, I would first stop. Think. Question my new best friends Mr G (Google of course) and Microsoft 'Help'. I knew now that for most things we do around software there is a quicker, smarter, better way.

There. Hope you like that story. Now on with the wow bit for you.

One of the most useful ways of using Excel is as a list of names and addresses. Your database. However as this list grows over time lots of problems can arise:

1. You add on details collected from the internet (online signups for your newsletter or requests for quotes for example).

2. Different people interact with it and have different habits - such as using all upper case in places or putting abbreviations, not adding on prefixes for phone numbers, mixing landline and mobile numbers in one column.

3. Duplicates can occur

4. Mistakes such as comma's in email addresses instead of periods in places

If you need to use this list for emailing or mailing - you have to clean it - or look bad! Just as I didn't want to send an email newsletter out Dear Tom Cruise, Dear Samantha Smith -or Dear reader, it has to be fixed first.

Here are a few lovely, lovely tricks.

Wow! I didn't know that.


1. OneDrive (free online MS Office) has a splendid version of Excel online that you can share- thus allowing people anywhere, anytime to enter their own contact details. All they need is the link. Sign up here.

2. Online Survey with OneDrive also has a stunning online survey/form capacity. You can create a form, a survey, a RSVP reply and send the link. What you get is a secure database of the responses all set and done for you. Easily downloadable.

3. Text to columns - in the data menu 2003; data ribbon 2007-13 will split apart information into separate columns based on what you tell it: space, comma, dash.

4. Concatenate - will then put things from multiple columns back into one column. For example your last names like van der Geen.

5. Proper - turns text into proper tense - capital for the first letter.

6. Text Filter - you can search within columns for specific text using the 'contains, does not contain' parameter.

7. Export to Outlook - bet you didn't know you can not only import your Excel database into Outlook but you can also do the opposite - Export your Outlook contacts into Excel.

Written by Debbie Mayo-Smith, One of New Zealand's most in-demand speakers, trainers and bestselling authors. Debbie works with companies that want more effective staff. For more tips and business ideas sign up for her free monthly newsletter.

Debbie Mayo-Smith

A motivational speaker gives her tips on business success

Debbie is one of the most in-demand speakers in Australasia; in the top 7pc of speakers globally and well-loved for her practical, plain talking technology quick tips. A best-selling author of sixteen books, Debbie has sharpened the activity of over 1 million individuals around the world through her presentations, training, newsletters, books and videos.

Read more by Debbie Mayo-Smith

Have your say

We aim to have healthy debate. But we won't publish comments that abuse others. View commenting guidelines.

1200 characters left

Sort by
  • Oldest

© Copyright 2014, APN New Zealand Limited

Assembled by: (static) on production apcf01 at 25 Nov 2014 06:42:27 Processing Time: 329ms