Debbie Mayo-Smith international inspirational motivational how-to speaker technology, time management, improving business performance
Motivational Speakers, Sales, Marketing, Time Management, Productivity, Technology, Tips

Database Magic - Six Tips To Save You Time and Money

Have you ever asked a staff member to get you a partial or segmented list from your database? It's handed to you a few hours later.

However do you really know what they had to do to get that list ready for you? You could be appalled!!!

Problems. Problems.
A good database is one of the most valuable assets in a business. But managing it and using it cleverly is a huge task as you know. It's problematic trying to keep information current and correct. There are four big problems that you're likely to have, and probably not aware of.

  1. If you have website data entry by clients and prospects that goes straight into a database you use - this data will always be messy with incorrect spellings, inconsistency (AUS, Australia , AUSTRALIA , bigpond,com).

  2. You need to use or split information in a way not supported by your database.

  3. You need additional information that is not stored in the database - such as non-client or Contact information from email programs.

  4. No matter where you put your data to bed at night (such as in Access, ACT, ProPlanner, a proprietary database) most staff will work with it in Excel. However your biggest problem of all is that they don't know many software functions and therefore work very inefficiently, wasting a tremendous amounts of time. And time is money.

Wasted time you say?

How can this be? Let's go back to asking a staff member for a list. Let's say you want to do a personalised letter to Superannuation clients greeting them by first name. However your database information is stored in full names. How are they separating those first and last names out of one column for you?

If you lined up 300 staff members from a sample of financial services companies, you'd find 295 of those staff members would be manually separating the information - one by one by one. Copying the names into another column, then deleting the first name out of one column, the second name out of the other. The sad thing is there's a function in Excel called Text To Columns that will separate the information out with a push of a button.  If you have a list of any length - think of the fabulous amount of time wasted needlessly.

It's not their fault.
I have a favourite saying "you don't know what you don't know - until someone shows you".

Database Magic
There are more 'hidden' jewels like that in Excel. They're hidden behind the "fx" button (functions), however you'd have to be an utter rocket scientist to understand the Excel description of them, then to take the concept and translate it to words, names 'facts' instead of figures. Having said that you still may be thinking "Excel, what's she talking about? We keep our database in Access, File Maker, our own software. Who uses Excel?"

It doesn't matter where you store your information - that's almost irrelevant. There is nothing better than Excel to either quickly, easily and stunningly fix database information or to create customised mailing /email lists in split second time.

To move information from one software program to another (such as MYOB, ACT, Goldmine, Proprietary databases), simply save the information as a CSV (comma separated variable) or a TAB (text) file. Then open it in Excel.

Additionally you might not know that it's incredibly easy to export information from your 'contacts / address book' in your email program to Excel. There's a wizard that walks you through it completely. Just go to the File menu and select Import and Export

Six Magical Database Solutions
A little creative thinking and knowledge of a few crucial functions can make a database maestro of any one. Let me part with a few of my favourites taken from the book Superb Tips and Tricks For Managing Your Customer Information (

Problem One: First and last name together in one column.
You want to personalise and not send Dear John Smith.

Solution: (Data menu> Text to Columns)
This function has a 3 step wizard that separates out information from within one column. It asks you what to look for (blank space, a comma) and then asks if you want any special formatting. That's it! Just be sure to add a lot of extra blank columns for the information to go to - some people have last names like van de Whitten!

Problem Two: 5 city roadshow
You want to do a personalised invitation, but don't want your recipients to read through a long list of times, venues and cities.

Solution: Find and Replace (Edit menu > Replace)
In Excel copy the recipients city two extra columns. Then for the first column, do a Find and Replace finding the city and replacing it with the venue. For the second column do the same replacing the city with the correct event starting date and time. You'll end up with a city, venue, time mailing list that can be merged to individual customised personalised invitations.

The following functions are hidden behind the "fx" button (functions),

Problem Three: Mixed upper and lower case.
This could be from the way you store your names or from accumulating data from website entry. You don't want to send out letters or emails that say Dear JOHN or Dear ROSE do you?

Solution: (fx) Proper

A veritable gem. Proper will change the first letter of each word to upper case, the rest to lower case. So debbie mayo-smith becomes Debbie Mayo-Smith

Additionally - there are the functions Upper which will turn the information into all Upper case and Lower which turns it to all lower case.

Problem Four: Information in many columns that need to be put together in one
For example you have a list where the first and last names are separate, but your database requires them to be together. Or postcode and State need to be together.

Solution:(fx) Concatenate
This will merge separate Columns together. So if you have P O Box 123 , Sydney , NSW, 2000 in four different columns, Concatenate will put them together into one column.

Problem Five: extra blank spaces got into your database
From incorrect data entry or some other way

Solution: (fx) Trim
Removes all extra spaces in a column except for ones that should be there in-between words.

Problem Six: duplicates in your database
If you've merged several lists together, you might find duplicates. Weeding them out is ardous if the list is long.

Solution: (fx) If statement
This is where creative thinking comes in. Sort, for example by email address. Then using the If statement function, you can have Excel look to see if one row of a column (let's say C2 is the same as the row after it (C3). You tell the function to say duplicate if they're equal; false or Not Duplicate) if they're not Drag the function down the entire column (or double click quickly). Voila. Now you know what's duplicate.

Two Important Notes:

1. Here's how all the functions work:

1. Insert an empty Column to the right of the one you want changed. (Highlight the Column>Right click>select Insert).

2. Double click on the little fx.

3. Type in Proper, Upper etc or select it if it's showing.

4. As shown below, a box opens called Function Arguments. What you're meant to do is just show Excel what Columns you'd like to work from.

2. Caveat - they're still "functions" you need to turn them into 'text'. It's SIMPLE:

1. Highlight the new column with your corrected data in it.

2. Select Copy

3. Edit >Paste Special

4. Select Paste Value

5. Hit okay (You  paste special right over the original content)

Debbie Mayo-Smith (BSc Hons Econ) is an International Motivational Business Speaker and Managing Director of SuccessIS! ( and a leading specialist in easy practical ways to improve business profitability, personal productivity and Internet marketing. Debbie lives in NZ and travels the world speaking, writing and training. By the way, if you'd like to get lots of neat tricks like this, plus marketing and business development tips, why not enrol for our free newsletter?


This article is copyright to Debbie Mayo-Smith & SuccessIS. You may use it for your newsletter, website or as an article. It can be reproduced - but in its entirety and with inclusion of Debbie Mayo-Smith as the author and the weblink

This article is copyright to Debbie Mayo-Smith & SuccessIS. You may use it for your newsletter, website or as an article. It can be reproduced - but in its entirety and with inclusion of Debbie Mayo-Smith as the author and the weblink


Free Quick Tips Newsletter
You'll love this succinct monthly tip newsletter focused on improving your business results and productivity.

View and sign up now!!!

Andrew Gardner; Investors Edge

"Get on her email list mate! She sends brilliant tips every month. It’s the one newsletter I love to get!"