6 surefire ways to optimize Excel stakeholder management

Let’s look at making your contact database searchable, filterable and readable, for optimal Excel stakeholder management. We’ll look at some examples and the most frequent difficulties PR pro’s experience. At the end of this post you can download a ready-made Excel template for you to manage your contacts.

How to make the most of Excel for stakeholder lists:

• One sheet for all contacts
• One row for each contact
• Split up data across different columns
• Combine fixed with flexible categories
• Make it searchable and filterable
• Keep it readable

We’ll tell you up front, Excel isn’t ideal for managing contacts. It’s meant to do calculations and make pretty graphs. But there are a few things you can do to make it a lot easier and efficient to manage your contact list in Excel.

As the founders of a CRM tool for PR teams, we’ve been doing this for a long time and we’ve helped hundreds of our clients with the optimization of their Excel contact lists. Excel contact lists are oftentimes a mess, but they needn’t be.

Let’s dig in:

Although it’s tempting to scatter your contacts across multiple sheets, this is not a good idea. It will make it a lot harder to filter out certain contacts and you won’t be able to see if you’ve got duplicate contacts in your list. Putting your contacts in one sheet seems like a big pile of inconveniently organised information but if you do it right, it’s definitely the way to go. For instance, many agencies use separate Excel sheets for each client and on top of that, often everyone has individual variations of those sheets on their individual computers. So what happens when a single contact has to be edited? That’s right, everyone has to go over the separate client sheets and their individual sheets to effect the change.

Try to keep your data readable by other systems (for instance when importing your list into MailChimp or Prezly). The only way to do that is to keep all information that belongs to one contact on the same row. This is probably a no brainer but don’t be tempted to scatter data across rows. This will make your list unmanageable.

Don’t do this:

or this:

Instead, do this:

The more you split up your data the easier it is to apply filters to it. So think hard about what information is relevant to split up:

• Full name: always handy to split this up in first and last name. Not for filtering reasons, but what if you’d like to automatically bring the first name into a mailer?
• Address: At some point you’ll want to filter on city for that one local outreach campaign. Having this data separate will save you a lot of time in the future.
• Multiple e-mail addresses and phone numbers: You want to know which are the primary address and phone number and which are the secondary.

First, split up the name column into two separate columns: first name and last name.

Next up, the address. It’s useful to split this column up into three columns: street, city and postal/zip code.

Finally, e-mail addresses and phone numbers should be split up in different columns, such that it is clear which contact information is primary. The easiest way to do this is by numbering each column that contains e-mail addresses or phone numbers.

The more data you have the better, so make sure you can describe your contacts as detailed as possible. If you only allow an exhaustive list of categories to assign to your contacts, you will lose data. Working with tags, for example (which are categories that are part of an unlimited list and that you can assign to contacts), will allow you to flexibly grow your categories. Of course you need to try and keep things as consistent as possible.

Other data should only have a limited amount of possible options. Take periodicity and media type, for example. It’s a good idea to keep those terms fixed and keep that list as short as possible. For instance: you don’t want to use “yearly” and “annually” in the same list.

To assign your contacts to different categories, your best option is to create a “tags” column. In that column, you can fill in categories for the different contacts, so you can filter them later.

To further improve searchability and filterability, you can create columns that define the function of your contacts, the periodicity and type of medium your contacts work for, the name of the medium your contacts work for and the languages they speak.

After setting up your Excel sheet, it’s time to make it searchable and filterable. You can do this by clicking the magic button: 

To make your sheets searchable and filterable, you need only click the above filter button. Once you click that button, you will notice arrows appearing in every top cell of your columns:

When you click that arrow, a menu with a range of filtering options will appear. You can sort your contacts based on the values in the column you select, you can filter them by various conditions or certain values. Notice that Excel has made a list of filterable values based on the information that you entered in the cells for that column. In the case below, Excel has listed all periodicity categories present in the “periodicity” column.

All you need to do now is check the conditions you wish to apply. In this case, say we want all contacts that work for a medium that publishes stories on a monthly basis. We uncheck all categories except for the “monthly” category and click OK.

Now we’re only left with the contacts that work for a monthly medium. Notice that a green funnel symbol has appeared in the top column cell, signaling that a filter is currently active:

You can narrow your search even more by applying a filter in another column. Say, for example, that we want all bloggers that publish monthly. We just do the same as we did with the periodicity column: we click the arrow in the top cell of the “function” column, uncheck all categories except for “blogger” and click OK. Now we’re only left with weekly bloggers. Notice that both columns will now be showing the green funnel symbol:

To remove the filters and display all contacts again, simply click the funnel symbol, click “select all” in the menu and click OK.

At some point someone else is going to be using your contact list. Keeping column names clear and logical is essential. It needs to be easy to read and understand. This also means you need to try and keep it as compact as possible. We know we’ve just talked about splitting up your data across columns, but it’s easy to overdo this and make your spreadsheet really hard to read and understand. Take, for example, contacts that speak more than one language. From a data perspective it would initially make sense to split all languages up in columns. However, this will make your sheet unreadable. You might start out with only three languages, like English, French and Dutch, but it is possible you will need to add a new language to that list. So in this case, it’s better to keep this restricted to a single column named “languages”.

In this Excel template you can start managing your contacts. It contains two sheets: one empty sheet with all the category columns you need in which you can place your contacts, and another in which you can see an example of what the contact file should look like (using fake data).

Excel can only take you so far. How do you track all your email engagements? How would you add more than one tag to a contact and still have a searchable database? Or how would you keep individual campaign statistics up to date? Prezly is a CRM tool for PR professionals, designed to just that: build, maintain and understand your contact database.