You’re a Marketing Professional – “Excel” Like One

Knowing how to click the green icon and open Excel is one thing. But sorting, tagging and synthesizing data to deliver real insight that affects strategy – well that’s something else entirely.

Excel offers tons of tools and functions a mile long for pros that take the time to learn the software. My motto is: If you think Excel can do it, it can. But before you can go full data sorcerer, there are a few building blocks that will make you smarter, better, faster, stronger.

Use Tables

Any data that will (1) have columns added or deleted, (2) be sorted and/or (3) be kept long-term, immediately create a table. It’s two clicks. Highlight the data, and create a table. This will make your life easier in two ways:

1. Easy data manipulation through sorting and filtering tools
2. Your rows will always stay true through sorting and filtering

Two is especially important. You have to tell Excel that all your rows of data need to stay consistent. Tables are a signal that all those relationships are important.

Tip: If your data already has a header row, bold the header values. This indicates to Excel that these values should be column headers. Otherwise you might end up with new row titles for Column1, Column2, etc. and your actual header will be included in the data set.

Renaming cells (less commonly used tool)

For templates and other sheets that are often reused, you can identify a name for a specific cell. Instead of “B4” you can refer to the cell as “total.” The same naming can be used for columns and tables.

To name cells:

• Highlight the cell, column or table you would like to rename
• To the left of the function toolbar, you will see a value (likely the cell location – i.e. B4)
• Rename the value

Tagging

Tagging is the most efficient method for categorizing data. Some people use color coding or bolding or character strings like “REMEMBER THIS” to tag or call attention to specific information. The problem with these methods is there is no standard way to interpret the notes as the data is used in other tools and Excel features like pivot tables.

A common method, like creating new tags for each element of categorization, will keep your whole team on the same page and make the data easy to work with. At True, we use a common practice when we create any new report that will be saved long-term:

• Insert 2 columns at the beginning of the worksheet
• Fill in headers if you know how you are tagging data
• Use Tag1 and Tag2 if you don’t yet know the designation of the column head

Note: If your tag columns are in a table you can quickly sort by any value in the table to quickly tag a whole subset of data.

Cleaning data

No need to sugarcoat it. You have to spend time cleaning up your data. Extra characters, misspellings, duplicate data, unnecessary information and other junk are always an issue. But the following three tools can save you a lot of time.

• Remove duplicates
• In the “data” tab, find the “remove duplicates” button
• Choose a column or columns in which Excel should look for duplicates
• Complete process
• Tip: Removing duplicates one column at a time makes it easy to manage the process
• Find and replace
• Use the dropdown next to the search box in the top right of the screen
• Choose the replace options
• Replace text with new text in an effort to create consistency
• Replace text with no text to simply delete

• Text to columns – delete columns
• Often there is a single character that separates important info from info you would like to remove (i.e. a column of image names with different file extensions - .png .jpg. gif). This feature allows you to quickly separate info you want to delete
• Create new columns for as many fields as you intend to split the data
• In the “data” tab, click “Text to Columns”
• In the first box choose the “delimited” option and click next
• Choose the character Excel will use to identify column widths. Use “other” for other characters
• Click next and review your new columns structure
• Complete the process
• Delete the columns containing information you don’t need

Bonus Tip: Keyboard shortcuts

If you spend any time in Excel, these keyboard shortcuts will save you loads of save.

• Command + Shift + [arrow key] – highlight everything from point of orientation to the last cell containing a value in that direction
• Command + [arrow key] – go to the last column in the series with a value, or skip to the new column with a value
• Command + A – Select everything in the table or all cells in the series with a value
• Function + Delete – Clear values in all highlighted cells

Author: Tyler Norris [Google+]

‍