3 Excel tips to help media planners organize contacts & ad info

I have worked at SRDS for over 13 years, and data has always been a big  part of what I do here. Learning how to quickly manipulate spreadsheets and databases has made my life a lot easier, and while I know that there are all kinds of skill sets when it comes to using different data tools, I wanted to share some of the things that have made my transition from sales support to marketing analyst flow a little smoother. 

When I was in support it was very important for me to organize different pieces of client information in a spreadsheet format.  I spent many hours in the Excel help section and never found an easy way to do many of the simplest things I needed to do.

Since then, I’ve spoken to many young media planners who have told me that having to organize all the contact/advertising info while putting together a media plan can be cumbersome. 

Here are a few Excel 2010 tips that might help with that:

Freezing Panes – when you have a lengthy spreadsheet and you need to scroll down to see more records but still see the top row for column reference:

1. Click underneath the column header row in cell A2
2. Click the “view” tab on the toolbar
3. Click “freeze panes,” then “freeze top row”

Custom Sorting fields by Color – When fields are highlighted by color:

1. Highlight the entire column that you want to sort
2. Click on “home” tab on the tool bar
3. Click on “sort & filter” on tool bar
4. Click “custom sort”
5. Click on “sort on” in middle column and choose by “cell color”
6. Under “Order” Column on the far right, Click on arrow for cell color and choose the color you are looking to sort on and then click ok
7. You will now have all those color coded cells at the top of the spreadsheet.

Removing Duplicate Values in a Column:

1. Select the column that you have duplicate cells in
2. Click on the “data” tab in the tool bar
3. Click on “remove duplicates”
4. In the box down below you will see the columns you highlighted, you need to put a “check mark” in the column you want to DeDup
5. At the top, Check the “my data has headers” box if your spreadsheet has headers
6. Click ok – You will get a box that says how many duplicate values will be removed

These tips have helped me organize data spreadsheets in a timely manner so that I can be more efficient in my job.  I hope they make you more effective in putting together your media plans. More fun data tips to come!