Five things I love about Excel...

When I tell people that we are an internet marketing business, they usually imagine that we have a suite of hi-tech software to help us with this. However, in reality most of the data we work with comes down to good old-fashioned brain power and Microsoft Excel! In our business, we use Excel all the time. In fact, I would go so far as to say it’s the tool we use most often, with the exception of email and the web.

Before I started the business, three years ago, I would already have said that I was ‘good with Excel’. However, since then I’ve learned so many new ways to use it that I now realise I’ve still only really scratched the surface. Here are five ways that Excel has helped me to run my business – hopefully some of them may help you too.

Find & replace tricks

Have you ever been in the situation where you needed to do something like:

Merging two columns into one (eg you have separate columns in a spreadsheet for First name & Surname but now need to put them into one column)?

Doing the opposite of the above – turning one column into two?

Adding something to every cell – for example, we recently had a list of phrases where we needed to add ‘Colchester’ to the end of each one?

There are lots of tricks you can do by saving your data in a text file from Excel. Let’s take the example of turning one column into two. Imagine you have a column where you have peoples’ names such as ‘Fred Bloggs’, ‘Freda Jones’ and so on, and you want to separate out the first names from the surnames. What you can do is the following:

Copy your column of data into a new worksheet

Save that worksheet as a .txt (tab delimited) file

Close it, then open it up in Excel again

When you get asked whether or not the file is delimited, select ‘yes’ and then on the next screen of the wizard, untick the tab box and instead tick the ‘Other’ box and put a space in the box beside it. Click finish.

You should now have a worksheet with separate columns for first name and surname!

You can do lots of variations of this trick to perform various different functions. It has saved me a lot of time over the last few years.

Pivot tables

Until a year ago I had never set up a pivot table. Now I don’t know how I would live without them. What is a pivot table? It allows you take a sheet of data and ‘turn it round’ – look at it in a different format. It can be really useful for summarising data. For example, I produce monthly reports for my clients on their pay-per-click marketing data. By using a pivot table I can easily take a sheet full of data and turn it into a nice summary for the client showing the total number of clicks from each advert and so on.

I don’t have space here to give step-by-step instructions on setting up pivot tables, but I do urge you to give them a try, if you haven’t already. Just put your cursor anywhere within a sheet of data, click on Data > Pivot table and pivot chart report, and follow the wizard through to get an idea of what it is all about.

The VLOOKUP function

Again, this is something I have discovered in the last year or so and don’t know how I ever managed without it.

I use it in cases where I need to combine two sets of data in some way. For example, if I have one sheet with search engine position data for a set of phrases, and another sheet where I have search engine traffic data for that same set of phrases, and I want to combine the two.

The VLOOKUP function will match the two sets of phrases against each other and pull data from one sheet into the other.

Again, I don’t have space here to tell you about it in detail, but do read up about it if this sounds like the kind of thing you might find useful. I use it almost on a daily basis.

The Format Painter

Stupidly, I didn’t know about this until about four years ago. If you want to copy the formatting from one part of a sheet to another part, so for example you want to use the same fonts in one column as you have done in another, select the area where you like the formatting and click on the paintbrush icon on the toolbar, then select the area that you want to reformat. Hey presto!

Mail merge

I’m pretty sure that most people reading this will know what mail merge is. However, are you using it as much as you could be? I save myself a lot of time each year by keeping all my addresses for business Christmas cards (and personal ones, actually) in a spreadsheet. I then update the list once a year and mail merge it into Word to generate labels for all my cards. There are so many things you can do using Excel sheets and mail merge – don’t forget it is there!

Helen Mitchell runs Ascendancy Internet Marketing, a Shropshire based internet marketing consultancy. Although addicted to Excel, she does not provide Excel advice as a service – so if you need any help with Excel or any other program, try searching the Rural Marketplace for details of fellow WiRE members who could help you.

www.ascendancyinternetmarketing.com
Helen@ascendancyinternetmarketing.com

Please note that this article is not written by WiRE but by a third party company. Whilst WiRE have made every effort to ensure that the information and details are accurate, we are unable to guarantee that they completely and WiRE are therefore unable to accept liability for any loss you may suffer as a result of omission or inaccuracy.