How to Create Personalized Invitations Using Word and Excel

If you have ever had to hand write a whole bunch of invitations then I am sure you have had the thought “There has to be an easier way”. Well, if you have Microsoft Office then you are in luck!

Here is how you can create and print your own personalized birthday party invitations in Word from a list of your friends compiled in Excel.

First, create your friends list in Microsoft Excel. It does not need to be anything fancy, just a straight list of names, but make sure you split the first and last names so you can address people by their first name if you want to add a personal note.

OK, the characters in Futurama aren’t really my friends, but you get the idea, heh.

Start creating a new file in Word and select the template you wish to use. Word will download any templates you select that you haven’t used before.

The template I selected allows you to print a folding invitation, so part of it is upside down. This feels a bit weird to edit but you can trust that it will come out ok on the printer!

Now go to the “Mailings” ribbon, press “Start Mail Merge”, and choose “Step by step mail merge wizard”

The mail merge side bar will open on step 1 of 6. Choose the type “Letters”, then click the link “Next” at the bottom

Now hit the radio button “Use the current document” and, again, click “Next”.

We want to “Use an existing list”, the list we created earlier in Excel, so browse for the file where you saved it on your machine and select it.

When you have browsed and found your Excel spreadsheet containing the names of your friends, choose the sheet from excel (sheet1), and click OK to load up the names into the Word “Mail Merge Recipients”.

If you look at the screen grab below, this is how Word sees our friends list. We could remove some of them if we don’t want to invite them to our party, by un-checking the box next to their name. Click OK when you are happy with the guest list.

To add a greeting line right before the invitation text, put your cursor above the text, and click “Greeting line” in the panel on the right of the screen.

You can customize the greeting line with various options and you get a preview before you commit yourself.

Before you complete your invites, you need to do some additional text modifications. There is some place holder text you do not want to publish (I don’t know who this “Stephanie” lady is, and it’s not 2004 anymore!). Click Next when you are done.

At this point you will see what the final product will look like. We can browse through the different invitations for each friend. If everything is all right, click “Next” again.

All that is left now is the final step, which is to print all the invitations!

Obviously we used an example that will be applicable to nearly everybody, but the same principle applies to all mail merges, from sales letters to other kinds of invitation.

Do you use Word mail merge? Please share your experiences in the comments …

Want Microsoft Excel Articles for Your Site?

Over the last few months I have been guest posting like a maniac, but it is time to offer it up again, so if you would like some free content, check this out now!

Yoav and I want to write free articles about Excel for your website or blog.

All we ask is that we can link back to our site(s) in the “about the author” attribution box. Seem like a good deal?

If so, simply fill out this short form and we will be in touch via email if we have something for you. (If you have multiple sites, please fill out the form for each one).

Click here to add your details and be on your way to getting some free, expert content for your site!

We have already written guest posts for several happy site owners. In the past we have written about all kinds of topics but now we really want to focus on Cogniview’s core expertise which is making your Microsoft Excel experience happier, easier, more efficient.

Cogniview’s expert developers have already supplied me with some absolutely awesome Excel solutions and code to share with you, I just need somewhere to write it up!

While we can’t guarantee that we will write something for everyone, we will think very hard to see if we can come up with something brilliant for you, so don’t delay, fill the form out now!

PDF2XL is 4 Years Old!

Dear Friends,

This month Cogniview is celebrating the 4th Birthday of PDF2XL – our flagship PDF to Excel conversion product.

It’s not that we always had PDF2XL. You see, Cogniview started as an Enterprise software company that aimed to sell its Enterprise-Level software products to IT departments in large organizations.

We spent a lot of money on flamboyant marketing campaigns, well-dressed sales people and a variety of other time/money wasters.

After 4 years of struggles that resulted in a huge hole in our bank account, we woke up and realized we had to survive. So we turned to our customers who said: Make us a decent PDF to Excel converter – and that was how PDF2XL was born.

Since then, PDF2XL licenses were bought by more than 15,000 companies in a variety of industries all over the globe.

We are thankful to our customers, our employees, our partners and our blog readers who have contributed their time, energy and resources to help Cogniview achieve so many successes.

So, let the party begin!

And what’s a party without party gifts?

First a special Excel Productivity Guide that can help you save even more time when working with Excel.

Click here to Download the Excel Productivity Guide

PLUS…

We would be overjoyed if you would consider leaving PDF2XL a birthday greeting as a comment on this Blog post. The best 10 greetings will entitle their authors with a $15 Amazon Gift Card.

The best part is that the best greeting out of the top 10 will also win a Flip Mino camera!

Amazon Gift Card Camera

So, we invite you to share your thoughts with us by leaving a comment on this post.

And once again – thank you!

Yoav Ezer, CEO
Cogniview Systems 2002

Excel Productivity Video

This is the first video from Cogniview’s Excel productivity training.

In my opinion, the approach outlined in this video is the cornerstone of productivity (Whether you are an Excel user or not). I’d love to hear your thoughts in the comments.

Part I

Part II

Excel Data Mining: Measuring Customer Support Costs

This entry is part 3 of 3 in the series Excel Customer Data Mining

In this series we have already looked at Recency, Frequency and Monetary value as metrics for data mining and ranking your customers in Excel. RFM will tell you who the most rewarding customers are, but it will not tell you who is most-likely to be a tough customer, nor will it tell you how expensive those customers are.

To work out these additional factors, you need to record more data, and that is what I will reveal in this article.

Customer acquisition costs are familiar to any business but many companies do not track individual customers support costs and instead see customer service as an aggregated expense. This is a mistake because certain customers could be costing you more money than they bring in, meaning gaining more of these customers would actually hurt your business rather than help it. Another reason why “make it up in volume” is often a bad approach!

Depending on your systems you might be able to record support incidents either by counting “tickets”, or you might even be able to record time spent. Again, just like with the customer value, you want to use recency and frequency, although in this case high recency and frequency are “bad”!

What would you use instead of monetary value or margin? Well, in some cases you can place a cost on the total support a customer required. It’s not just a factor of time, although that is a cost worth recording, but also there may be additional expenses incurred such as travel, postage, returns, waste, custom work or additional purchases. A customer who demands you turn up at their offices hundreds of miles away with a brand new custom widget is going to be more of a financial burden than one who sends one email and receives a stock answer.

Along with customer ID, you will want to record the type of customer or the product/service the support was against. If customers have multiple products then do the exercise against customer initially but also run through another process for product or service.

Often, but not always, you will find the customers with the best RFM scores are also those who cost you least in customer support. The top 20% really are your best customers overall. Over and over the customers who pay you least are also the ones who cause you the most bother. Of course there is always the high-roller exception who is just demanding because, well, they can.

A friend around the millenium had a software product with service levels. Together we turned the business from a net loss into a hugely profitable company by first systemizing customer service, and then by removing the bottom rung of the offering entirely. We found the cheap product attracted customers who were both more likely to circumvent the copy protection, but also generated the bulk of the distractions in the form of groundless complaints, returns, support problems and bad PR.

You do not need fancy systems to keep this information. Using Excel you can record your customer service data very easily. Just make sure you record at least:

You might well have suggestions or requirements for additional data, but essentially you want to know what the problem was, the cure, how long you spent solving and who for.

Once you have your data you can see if there is a way to make these problems go away, perhaps the issue is with documentation or customer expectations rather than product quality. If the problems can’t go away entirely, you can then work on making your customer service as easy as possible, with stock responses, procedures, and other systems. Of course failing all that, you are left with dropping the product, customer (or type of customer) or raising prices.

Bottom line, without data you would only be guessing. So long as you have actionable information then you can actually make some decisions. If you are not recording support information, you had better get started!

How to Use Excel to Identify Your Best Customers

This entry is part 2 of 3 in the series Excel Customer Data Mining

In the previous article I showed how you can measure visitor value and engagement using Excel. Now I would like to show you how you can identify your best, most profitable customers, also using Excel.

Now immediately you might think it is obvious who your most profitable customer is. Isn’t it the one who spends the most?

No!

First, someone could buy your biggest ticket item and then go away never to return. Are they a better customer than one who buys a smaller item each month? This is why we need to look at the data!

We also need to remind ourselves that revenue is not the same as profit. We will get into working out profitable from unprofitable customers and/or product later.

Customers can often talk up their future spending plans which can turn a sales persons head. Ideally what we want to discover who has shown good customer evidence through their actions and an evidence-based prediction of propensity to buy in future.

For simplicity sake in these examples I will be using revenue because I don’t want to distract you from the valuable knowledge that you can gain by getting involved in just getting hold of some of this data. If you have profit data then wherever you see revenue or spend, substitute that with profit or margin.

Discover Your RFM

The technique I am showing you here is called RFM Analysis. RFM stands for

Each of these measures is an important indicator of how good a customer they are, but together you get a potent scoring metric that can tell you a great deal about where (or who) your profit is coming from.

Set Up Your Customer List

If you use a fully-featured ecommerce or CRM system then this information might be readily available to export or in reports. Otherwise you need to talk to your tame techie or do some inputting. For this example I set up a spreadsheet to simulate the output from a shopping cart system.

I then copied the cells and pasted into a new sheet using “Paste Special” as I only wanted the values, not the formulas, just in case it recalculated as I worked with the data.

This gave me 100 “customers” with associated purchase activity.

Now we need to “score” them!

Essentially scoring your customers is as easy as sorting and adding some additional numbers:

  1. Sort by “Last Purchase” descending.
  2. Add a column marked “R” and give the top 20% a score of 5, the next 4, and so on. (Do not use a formula, just copy and paste).
  3. Copy the column of scores so you can paste in the next steps.
  4. Add columns for “F” and “M”.
  5. Sort by “Purchases” descending, score in “F” (paste the scores), then sort by “Total Spend” descending and place the “M” scores.
  6. Now when you sort the spreadsheet back to ordered by “Customer” you will see each customer has the appropriate values for Recency, Frequency and Monetary Value. All we need to do is add those numbers together to get their RFM scores.
  7. Create a new column marked RFM and add up their R+F+M using something like the formula =E2+F2+G2 and paste that in for each customer row.
  8. Now you should be able to sort by the RFM column descending to get the people with the highest score at the top.

Take a look at the top of your results.

Those at the top have spent a lot, recently and often. Your best customers.

Now look at the bottom.

These folks might have spent a lot, but a year ago and not bought anything since, or perhaps made one small purchase more recently. As you can see further up, small purchases made often can make a brilliant customer, and we would want to attract more of them if we can service them without too much hassle and cost.

Using these Results

If you do nothing else, it is a good idea to split the list up into Quintiles/Fifths, the top 20% are your Gold Customers, the next Silver, then Bronze. The next 20-40% of your customers need looking at to learn where you have gone wrong.

Already we have data that can provide us insights.

We have identified two types of customer that just looking at revenue wouldn’t have revealed. High ticket customers who go away, and new, low ticket customers. Those latter type might turn into brilliant customers but it is too early to tell, but they will not grow into top customers without our attention. The former type of customer likely went away because of either poor customer service or because of some other bad experience – ie, we dropped the ball.

If we only looked at $ we would have thought the former were fine and might have considered latter not worthy of much attention compared to the high-rollers!

In the next part of this series we will look at two additional metrics that can tell you a great deal about how profitable these customers really are, Engagement and Support.

Introduction to Using Excel to Calculate Your Website Visitor Value Metrics

This entry is part 1 of 3 in the series Excel Customer Data Mining

Do you know how much each visitor to your website is worth? If you buy advertising, do you know how much money you can spend and still break even? Do you know the long term value of your visitors?

We all want as many visitors as possible to our websites, it almost seems like crazy talk to imply otherwise, but in actual fact for a business website, some visitors are more valuable than others, and some visitors could be even costing you money.

Knowing your average visitor value is therefore essential, and breaking visitor value down further could be a very profitable thing to do.

Even more important is if you are paying for this traffic. You want every spend on your web marketing to work as hard as possible, both to know how much you can invest, and also to trim the fat.

There are custom and commercial tools out there, but in fact, you can do a lot with plain old Microsoft Excel!

Using Excel for Basic Visitor Value

First you need to know how many visitors you get each month, and how much money your website makes.

Do you sell products or services? Maybe you show advertising?

Take your monthly visitors and sales total and plug these numbers into the spreadsheet. In the final column the revenue is divided by the visitors to give your visitor value.

So in this example, 30,000 visitors bringing in a revenue of $10,000 provides an average visitor value of $0.33.

How is this useful? Well, with this in mind, if you are currently paying $0.25 per click in Google Adwords then you can up it and still break even.

Drilling Down Visitor Value

This is not really fair though. As I say in the introduction, not every traffic source is created equal.

Say, for example, you notify your audience about a product in three ways:

  1. Blog Feed
  2. Email
  3. Twitter

In the example shown, Email generates the most revenue, with feed coming second, and Twitter looking pathetic at only $20, BUT, look at the per-visitor value!

This is because the Twitter message converted at a much higher rate, that is a higher percentage of Twitter visitors bought. We will look at conversions in a moment. For now just know the message and channel can have a profound impact on visitor value, even a negative effect, as well as the total revenue where email clearly wins in this case.

Responsives Versus Subscribers

The obvious conclusion you would draw from the above example is “wow, I had better grow my Twitter followers”, but WAIT!

Yes, there is an indication that those Twitter clickers are worth $6.67 on average, but it does not tell you how much your Twitter followers are worth! At this point you just know how much your responsive followers are worth from one test.

To know how responsive your various fans are, we can check the Click Through Rate, or CTR.

CTR is calculated by setting the cell format as percentage using the % button, then taking the visitors and dividing it by the total messages sent (if you have 300 followers then one tweet is sent to 300 people, but if you send it twice then the number of messages sent doubles).

In my case here the formula is =(C7/B7) (where / means divide).

These results can give you additional insights. You can see Feed subscribers are incredibly responsive – a full half clicked through, were as email and twitter followers were not in the same league.

You can not take the results from just one sample too seriously. You need to measure repeatedly for a start. Secondly, if you have 10 followers in total and 3 clicked through, then your click and conversion rates seem high, but we could be dealing with too small numbers to know if your results are statistically significant.

Statistical relevance is too much math for my meager brain, but there are spreadsheets available that will help you do the calculations.

What you do know is that if you can get more of your followers, subscribers or readers to respond, then you will increase their value to you, and while some sources are less responsive than others, their propensity to buy can be very different … that is where conversion rates come in.

Measuring Conversions

Using a tool such as Google Analytics you can automatically measure conversions using “Goals”, but you can do some broad calculations using Excel of course.

Taking the example given above, we can add another couple of columns to reveal a better picture of what went on with that promotion.

Conversions are calculated much like CTR, but CR is percentage of visitors who buy, therefore Sales divided by Visitors presented as percentage format.

So in our example you can see that while this particular sites Feed readers are highly responsive, they are clickers not buyers, whereas this site owner has a small amount of raving fans who were primed to buy following on Twitter.

Why might this be? Well, Twitter is often used for conversation. There might have been many messages leading up to the offer being made preparing those who were interested that something special was going on sale, or perhaps an existing product heavily discounted. The Twitter and email recipients might have been hovering over their keyboard ready, while the blog readers while still interested left it too late and clicked over after the offer had gone away!

Going for Gold

So now we know that not all visitors are equal, but what about customers? Earlier I said that some visitors or customers could in fact be costing you money, how do we know? And what about the long term value?

In the next part of this series I will show you how to split your customers into groups for increased profitability and so you know who to lavish your super special customer service on! We will later also work out who are the best customers long term, and who you might want to stop buying from you.

Subscribe so you don’t miss anything!

Got Microsoft Excel Questions?

Our Excel cheat sheet seems to have been a hit with readers so far. If you haven’t grabbed it yet, you can still download it for free.

So many people told me that they struggle and fight with Excel I thought I would offer you the opportunity to decide what should go in the next one! I will do my hardest to get answers for all the best or most popular questions.

Just add your suggestion, question, idea or problem in the comments.

Also if you particularly like a suggestion anyone else makes, just point it out in the comments too!

We have Excel experts on hand to supply me with the answers, so get asking your Excel questions in the comments :)

Free Microsoft Excel Cheat Sheet to Download and Print

I don’t use Excel very often so when I do I have to remember or research all the keyboard shortcuts and functions that I normally use, even the most basic ones.

Rather than keep writing and losing post-it stickies I thought I would put this cheat sheet together, and because I figured I wouldn’t be alone in this, I thought I would share it with you too.

This cheat sheet shows the major keyboard shortcuts and a list of useful formula functions. Stick it up near your monitor so you will never need to Google that keyboard shortcut again!

Download the Free Excel Cheat Sheet

Excel Design Tips – Make Your Excel Spreadsheets Look Good

This entry is part 1 of 2 in the series Excel Design Tips

Excel Design

Excel is a powerful numbers tool, no question. Most of the time when we talk about spreadsheets we are focusing on the mathematical side, rather than how the results look.

Thing is, spreadsheets are a tool for creating content to be read also. In fact, I would go as far as to say a spreadsheet is an influential tool of persuasion, when used correctly.

On top of that, many business rely on Excel for their business forms, such as invoices, expenses, time sheets and all kinds of paperwork.

It is possible to not only have your spreadsheets make you more productive, but also look good.

In this series I want to show you how you can beautify your spreadsheets without requiring any real design skills.

Formatting Reports With Spreadsheets

Although the features available in the latest software surpass anything that came before, this use of spreadsheets is not a new thing, can you remember the old Lotus 123 and Supercalc days?

Even back then, spreadsheets were used to make data more presentable, more professional, and less, well, like it had come straight off a music-ruled dotmatrix report, which was often the main alternative.

Of course back then a lot of the data was retyped, causing inevitable transcription errors. Now Excel can read in data exported from numerous systems natively, and also understand common open formats, even down to interrogating live databases directly through ODBC.

So this is not just about spreadsheets you create form scratch, Excels presentation abilities can help you with presenting any kind of data that Excel can import.

Things have come a long way. In the past you were pretty much restricted to having lines, boxes, bold and italic. If you were lucky. Now Excel has features only previously found in desk top publishing packages.

Excel’s graphic features are both a blessing and a curse.

Yes, you have a huge amount of freedom and functionality to create the look you want, but the downside is a lot of people over use these features and make their spreadsheets look like a Nascar, and reports appear to be a page from a comic book.

Let’s focus on some basics!

10 Tips for Avoiding Excel Formatting Horrors

Excel Design Tip 1: Less is More

When working with the formatting and design features of Excel please do not treat it like a kid in a candy store!

Restraint is important if you want to provide the best possible result.

Excel Design Tip 2: Comic Sans has no place in business

Sometimes I wish I could build a time machine so I could go back to whichever lunatic was presiding over the meeting when Microsoft decided to bundle Comic Sans with their operating system and give them a sound thrashing.

Ahem.

If there is one thing that will reduce the professionalism of your spreadsheet it is the inclusion of any kind of comic font, but in particular Comic Sans. Just don’t.

Excel Design Tip 3: WordArt can kill your design

WordArt is a nice and flexible feature that has been an addition of Office for years. Many people are comfortable with it. But does it ever look cheesy! Just look at the header image of this article – geeky, no?

You know where we agreed restraint was important a few paragraphs ago? That goes one hundred fold for when using WordArt. In fact, if in doubt, leave it out!

Excel Design Tip 4: Clipart Clichés

Photography or illustration can bring a page to life and stop it being a sea of text and numbers, but if the clipart looks over-used, out of place, hackneyed or just plain BAD, then you are only doing damage. I would recommend instead of the default clipart to look to outside sources such as Istockphoto, but there is a similar risk in using those also!

Excel Design Tip 5:  Careful with colors

Color is essential in aiding clarity of your data, providing of course you are going to view the spreadsheet on screen or your printer can output color. What often happens though is people get carried away and the end Invoice in excelresult is a rainbow mess of clashing, garish, neon. Excel is not the best at allowing color flexibility, so make the best of a bad set of colors by selecting muted tones that go well together, such as grays and blues.

Excel Design Tip 6: Templates are your friend

Rather than starting from scratch, especially if you are design challenged like me, consider trying one of the many freely available pre-built templates out there. You can find everything from invoices to timesheets. There is a great list in our Excel Wizardry article.

Excel Design Tip 7: Use grid lines selectively

Just because you are working in a spreadsheet does not mean you have to show your grid lines. In many cases it is easier to only add the lines that aid legibility, and switch off all othes. In Windows you can find the option to turn off gridlines in the view tab, or in older versions tools, options. In Mac, look in the preferences.

Excel Design Tip 8: Background images can lift or distract

A tasteful background image can work well. Unfortunately all too often the images used are busy, detailed and when combined with columns of numbers, just make your eyes water. Go for something plain and delicate, such as a subtle graduated tint or a slight drop shadow..

Excel background images

Excel Design Tip 9: Align your text

Another legibility tip is for you to not just accept Excels defaults when working with your text. For example did you know you can format your cells and select to align your text at the top and to wrap around?

Excel Design Tip 10: Use appropriate font sizes

Your basic font needs to be large enough to be legible without an electron microscope, plain enough to read (step away from the “handwriting” font!), and there should be a clear visual hierarchy. What do I mean by that? While most people in their wordprocessor will use headlines and subheads, for some reason fewer people think to do this in a spreadsheet, but breaking up a report in this way can really help get your point across. So if you are using 10-12 point body text you might make your subhead 14-16pt and your main headline 18pt.

Summary

Yes, much of this will be seemingly obvious, but if you go into any Excel using business I guarantee at least one and probably more of these excel formatting crimes are being committed right now.


Got Excel design tips? Please share in the comments, and look out for part 2 of this series!

Next Page →