Wednesday, September 15, 2010

A Few Excel Basics...

Written by James Bland
Group Marketing Manager
The Sundial Group, Highgate House

I’m always wary of sentences that start with “I’m no expert…but”, but then that’s because I rarely use them. However, I’m going to make an exception and share a few pointers on Excel that make my life as a marketing manager easier. When I was studying for my CIM (Chartered Institute of Marketing) qualifications, a few of my classmates, all experienced marketing professionals, recoiled in horror at the sight of a number, fled for coffee when shown a spreadsheet and I’m pretty sure one lady fainted when talked through a simple DCF analysis, so I’m fairly confident that a good number of marketers won’t know their way around Excel. None of the below is any big secret, and Googling most Excel questions will give you plenty of really well-constructed guides, but the ones I list below have helped me out of many a hole in the past!

So, I’m no expert, but…

We’re Better Together

Let’s say you have a mailing list where First Names and Last Names are stored in different columns (A and B) but you really want one column to have people’s full names in. The formula that you type into column C is…

=CONCATENATE(A1,” “,B1) – where A1 represents whatever is in that cell, likewise B1, and whatever is inbetween the speech marks is inserted inbetween. You could put any text or number you want in there, but I wanted a space so that’s what I typed.

Parting is Such Sweet Sorrow

Maybe though, you want to reverse that procedure, because cell A1 contains “Mr James Bland”, but what you want to do is address a letter “Dear James”. Slightly different process this one – first you have to make sure that the next three cells (B1, C1, D1) are empty, because otherwise you could overwrite something important. Then, highlight Cell A1 and go to “Data”, “Text to Columns”. For this example, you want to choose the “Delimited” option, and then choose “Space” as your delimiter. By doing this, you tell Excel that a space marks where the cell should be split. Then hit finish and the job is done. If you’re doing a big list, be careful with the number of specific words though. “Mr James Edward Bland” will spread into column E1, again potentially overwriting something important. For lists containing names like these, best make yourself plenty of empty columns and then go back over the list using CONCATENATE to re-attach the two bits of someone’s surname.

Don’t Lose Your Head

A word of caution here, Excel saves a Cell’s formula, not necessarily its text content. If you had CONCATENATE-d two bits of a name together and then wanted to delete the old individual columns, doing so will empty your combined column do, as Excel will be referencing empty cells. To get round this, save your file as type “*.csv” (comma separated variable), close the spreadsheet, and re-open the CSV file. The contents of the cell will be ‘banked’ and you can delete at will. A word of warning though, if you have made pretty patterns using bold type or coloured backgrounds, that information is not saved in a *.csv file, so it will have disappeared when you reopen the file.

Say What You Say…

And, of course, a personalised letter is so much more powerful than a generic one. But when I say “personalised”, please don’t fall into the trap of thinking that just putting my first name, or company name, into the middle of a sentence is going to cut it. People are generally savvy enough to see right through that gimmick. Thankfully, using Excel and Word to mail merge, you can customise whole paragraphs of text to fit what you need to say. There’s nothing particularly difficult about this either – all you need to do is put a longer string of text into your spreadsheet in a designated column for “Comments” or “Extras”, and then merge this field into your letter like it were a postcode. You can then add a line or two of customisation really quickly into Excel, and the mail merge process will guarantee it gets attached to the right letter! Pioneering schoolteachers have been using this technique for years when formulating school reports for classes of thirty kids or so. Much better than just noting “Could do better” each time.

No comments:

Post a Comment