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:
- Click underneath the column header row in cell A2
- Click the “view” tab on the toolbar
- Click “freeze panes,” then “freeze top row”
Custom Sorting fields by Color – When fields are highlighted by color:
- Highlight the entire column that you want to sort
- Click on “home” tab on the tool bar
- Click on “sort & filter” on tool bar
- Click “custom sort”
- Click on “sort on” in middle column and choose by “cell color”
- 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
- You will now have all those color coded cells at the top of the spreadsheet.
Removing Duplicate Values in a Column:
- Select the column that you have duplicate cells in
- Click on the “data” tab in the tool bar
- Click on “remove duplicates”
- 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
- At the top, Check the “my data has headers” box if your spreadsheet has headers
- 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!