It’s not difficult to see the importance of lists and data sets in the life of a marketer: Marketing revolves around the lead database; People are willing to spend hundreds of dollars on mailing lists; And where would we be without our to-do lists? They keep our heads on our shoulders and deliverables in our outbox.
Oftentimes, these lists come from multiple sources, and it’s hard to maintain consistency across our databases. Other times, lists need to follow a certain template for reports or uploads. When you’re downloading data for weekly reports or uploading contact lists to your CRM on a regular basis, file prep can eat up a lot of your valuable time. Clean up your databases faster and with less effort by following these 3 Excel shortcuts for marketers.
#1. Record a Macro
When you use the Record Macro feature, Excel writes code for every action you take, creating a macro that can recreate all the recorded steps with a click of a button. Macros are a great way to save time on repetitive, predictable tasks such as spreadsheet standardization.
Marketing solutions like VerticalResponse, MailChimp, and Marketo each have their own way of ordering and formatting exported data. They label columns differently than you do in your reports, for example. They may also include unnecessary fields. By recording a macro for each program, you can export your files, open them, run your macro, and watch as Excel instantly formats the exported data to match your company’s reporting style.
To create a macro:
- View Tab>Macros>Record Macros>
- Name your macro. Make sure to save in “Personal Macro Workbook” to be able to use it across workbooks. Hit “Ok”
- Format your document. To keep the code clean, try to do it in as few steps as possible.
- Click the square stop recording button at the bottom left-hand corner of the screen
To use a macro:
- View Tab>Macros>View Macros
- Click on your macro name
#2. Split Cell Information
Some exports or mailing lists provide contact names in full in one column when you need them separated out into first and last names. If your downloaded spreadsheet doesn’t list names the way you want them to, please, PLEASE don’t be the technologically impaired person that painstakingly retypes each entry! You have two options to separate information in a cell: the Text to Column Wizard or a formula.
The Text to Column Wizard works best for lists with consistent entries. In the case of contact names, the entries would follow a pattern like “Last, First,” or “First Last.” Use formulas on lists that include names with multiple parts—i.e. suffixes, honorifics, middle names, etc.
Formulas require a certain degree of Excel savviness, so if the Text to Column Wizard isn’t cutting it, and you can’t write a formula yourself, check out this page for help with splitting names with multiple parts.
Other uses for the Text to Column Wizard include isolating domains from a list of email addresses or links and separating city/state from street address for location filtering.
Steps:
- Create new destination columns for separated text.
- Go to the Data Tab in the ribbon and select Text to Columns
- Make sure Delimited data is selected and hit “Next.”
- Choose your delimiter.
- Modify your formatting or destination if necessary. Hit “Finish.”
- Fix your header information and voila!
#3. Make it Pretty
Forms build lists, and people fill out forms…if only people were perfect, maybe our databases would be, too. Guard against those infamous CAPS LOCK enthusiasts, and use a formula to make your data set consistent and attractive. This is especially important if you’re planning to use your list for email marketing and you use tokens that grab from a database. Email recipients don’t want to open with “Hi DAVE KOLWALSKI, let me yell at you!”
Excel’s PROPER function returns the text in a cell with only the first letter capitalized. If, for whatever reason, you want your data to be in all lower- or –upper case, use the LOWER and UPPER functions. Use TRIM to get rid of extra spaces in front, between, or after words in a cell.
To use these functions, simply insert a new column next to the one you’re trying to modify, and type in =FUNCTION(cell reference) in the cell beside the first entry and autofill the rest of the column. If you were using the PROPER function, this would look something like:
We hope these 3 Excel shortcuts for marketers save you time and sweat. Don’t have a lead database to work with? Check out our services page to see what we offer in terms of lead generation and email marketing.
There are no comments