A motivational speaker gives her tips on business success

Debbie Mayo-Smith: Top five Excel tricks for your database

Photo / Thinkstock
Photo / Thinkstock

I was Microsoft's guest this past week at the Tech Ed Conference which heralded the release of Windows 8 and Office 365. The conference was geared for the technical field. The guys and gals who run IT departments and will build the new apps for the Windows phones and tablets. Not mere mortals and users like me.

Not much was said about The Office features, as attention was focused on the new Windows 8 and Office 365, the latter being their Cloud based software.

While the demos were on prototype tablets, it will be marvellous to have the ability to work with your Office documents anywhere. The editing is as rich as if you were working straight in Word, PowerPoint or Excel. I can't wait to be able to kiss Google doc's goodbye so I can do REAL work while overseas.

See my previous column To tablet or not overseas.

One of the new Excel features demonstrated seems to have overcome a problem of not knowing what functions can help you manipulate names, emails and addresses. If it works well it will be a huge innovation helping immensely.

In the interim, I thought I might give you my top 5 Excel functions for working with data. This can save a massive amount of time. By the way, to move information from one software program to another, simply save the information as a CSV (comma separated variable) or a TAB (text) file. Then open it in Excel.

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: Inconsistencies

You want to do a mail merge, but you have Ave, Av, Avenue.........
Solution: Find and Replace (Edit menu > Replace)

Enter Ave for find, and type in Avenue for replace and then select replace all. Do it again with Av to Avenue and so on.

Take a peek behind the fx button - looking at four functions that are particularly wonderful when working with text. I'll also detail how functions work.

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.
Solution: (fx) Concatenate

This will merge separate Columns together. So if you have Mr & Mrs, Tom, Jones in three 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.

Written by international speaker and bestselling author Debbie Mayo-Smith. For more tips, over 500 how-to articles visit Debbie's article webpage.

Get the news delivered straight to your inbox

Receive the day’s news, sport and entertainment in our daily email newsletter

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

1200 characters left

By and large our readers' comments are respectful and courteous. We're sure you'll fit in well.
View commenting guidelines.

Sort by
  • Oldest

© Copyright 2017, NZME. Publishing Limited

Assembled by: (static) on production apcf03 at 25 May 2017 01:23:55 Processing Time: 594ms