As a part of content marketing, you may often need to work with a lot of data.
These could be numbers (such as page views, conversions, subscribers) or text (URLs, titles, keywords).
It always helps to have a couple of Excel tips up your sleeve.
Here are my top 7 Excel tips that will help you work with data in Excel.
Excel Tip #1 Combine Text Using CONCATENATE Function
You can easily combine text in Excel using the CONCATENATE function.
Suppose you have a dataset as shown below and you want to create URLS that contain the campaign source, campaign medium, and campaign name:
You can easily create the complete URLs using the following formula:
This would combine all the three parts of the URL.
BONUS TIP: You can also use the ampersand (&) character to join text in Excel. So in this case, instead of the CONCATENATE function, you can use =A2&B2&C2
Excel Tip #2 Use to Text to Column to Separate Text
Text to Column feature comes in handy when you want to separate text based on a pattern.
For example, suppose you have a dataset of email ids as shown below and you want to separate the user name and the domain name. The pattern here is that these two are separated by the ‘@’ character.
The pattern here is that these two are separated by the ‘@’ character.
Here are the steps to quickly split the username and the domain name:
- Select the cells (A1:A6 in this example).
- Go to Data –> Data Tools –> Text to Column.
- In the Convert Text to Columns Wizard, make the following changes:
- Step 1: Make sure delimited is selected.
- Step 2: Select Other and enter @ as the delimiter.
- Step 3: Specify the destination cell.
- Click Finish.
This would instantly split the email ids into username and domain name.
Excel Tip #3 Quickly Insert Current Date and Time
You can easily enter the current date or time in an Excel cell using keyboard shortcuts.
I use these on a daily basis when I have to mark some tasks as completed on my to-do list and also keep a track of when these were done.
To enter current date
- In PC: Control + :
- In Mac: ^ + :
To enter current time:
- In PC: Control + Shift + :
- In Mac: ⌃ + ⇧ + :
Excel Tip #4 Highlight Cells with Duplicate Content
If you work with data, you’re likely to face the trouble to handling duplicate data points.
Here is a quick way to highlight cells that contain duplicates:
- Select the cells in which you want to highlight duplicates.
- Go to Home –> Conditional Formatting –> Highlight Cell Rules –> Duplicate Values.
- In the Duplicate Values dialog box, specify the color and click on OK.
This would instantly highlight all the cells that have a duplicate in the list.
Excel Tip #5 Drill Down Using Excel Filters
If you have a huge dataset, you can easily drill down and filter the list using Excel Filter feature.
Suppose you have a dataset as shown below:
You can use Excel filters to drill down and see only the subscribers from the US.
Here is how to do this:
- Select any cells in the data and go to Data –> Sort & Filter –> Filter. This will apply a filter icon at the top row of the data set.
- Open the filter drop down for countries and deselect all and select US.
- Click OK.
This would instantly hide all the other results and only keep the results od the US visible.
Excel Tip #6 Insert a Comment into a Cell
As marketers, we sometimes have to juggle so many things at once that things are bound to be forgotten.
A good practice is to insert comments in cells. It will be helpful when you revisit the data next, or share it with someone else.
To insert a comment, right-click on a cell and select Insert Comment.
Now you can write the note in the yellow box, and when done press Escape.
Bonus Tip: The fastest way to insert a comment is to use the keyboard shortcut: Shift + F2
Excel Tip #7 Quickly Sort Data for Better Analysis
If you work with a lot of data, it’s a good practice to keep it sorted.
The sorting could be based on value, date, or the alphabets.
Suppose you have a dataset as shown below and you want to sort the countries from A to Z:
To do this:
- Select the data in column B (don’t select the header).
- Go to Data –> Sort & Filter –> A to Z icon.
Excel is smart enough to detect that there are more columns of data in it and would ask you to expand the selection. Make sure ‘expand the selection’ option is selected (which is by default) and click OK.
This would sort the entire data set based on countries.
These are my top 7 Excel tips for bloggers and marketers.
Do you have some Excel tips that you use on a daily basis to save time and be more productive? Would love to hear from you in the comments section.
Author Bio: This is a post by Sumit Bansal. Sumit Bansal is a spreadsheet geek and loves to share cool Excel tips through his blog. When he is not busy changing the world with his blogs, he can be find wasting time on Netflix.