Conditional Formatting is a powerful feature in Microsoft Excel. I use it regularly while working on spreadsheets jam-packed with data. It allows you to change the appearance of a cell based on its value or another cell's value. You create certain conditions, and when those conditions are met, Excel applies the formatting that you choose.

Here’s a quick trick for how you can use Conditional Formatting to find duplicate values in a list of cells:

1. Highlight the column of cells that have the duplicate values
2. Click on the Conditional Formatting on the tool bar
3. Click Highlight Cells Rules
4. Click on Duplicate Values
5. In the Duplicate Values box make sure the first drop down box is marked Duplicate
6. Then in the second drop down choose the format color and style you want – click ok

Looking for more quick Excel tips?

• 5 Useful Excel Keyboard Shortcuts
• 3 More Tips to Make Excel 2010 Work For You
• 3 Excel Tips to Help Media Planners Organize Contacts & Ad Info

It’s been a few months since I’ve shared tips for using Microsoft Excel 2010, but now I’m back with my favorite keyboard shortcuts. Not only will these tricks save you time but they’ll save you from using the mouse too.  

Here they are:

1. Selecting Rows and Columns: 

• To Select an entire row of data to copy or move – Press “Shift” then “Space”
• To Select an entire column of data to move or copy – Press “Ctrl” then “Space”

2. Highlight many rows of data:

• Highlight the first row of data in the range you want to select
• On your keyboard, hold down Shift/Ctrl/Down Arrow and it will then highlight the entire data range for copying or cutting

3. Copy & Paste:

• To Copy – Press “Ctrl” then “C”
• To Paste – Press “Ctrl” then “V”

4. Move from top of worksheet to the bottom without scrolling:

• To move to the first cell in a worksheet –Press “Ctrl” then “Home”
• To move to the last cell in a worksheet - Press “Ctrl” then “End”

5. Saving & Printing:

• To save the file – Press “Ctrl” then “S”
• To print file – Press “Ctrl” then “P”

What Excel keyboard shortcut do you use most often?

Good time management skills mean making your tools and data work for you, not the other way around. In today’s fast-paced media world, there is no time to waste; but there are many days when the data does not want to cooperate and we seem to have to make many manual adjustments. Last month I covered some basic Excel tips, and heard from several planners that they were helpful.

Here are some additional tips that might help you tame all that data in your media plans and other spreadsheets.

Turning All Caps into Proper Text – when you have cells with (“TEXT”) in all capital letters and want to change the case to proper (“Text”):

1. Click on a blank cell next to the cell you want to change
2. Use formula =proper(click on cell you want to change, i.e. A2)
3. Click enter and the reformatted text should appear in the cell 
4. Copy formula down the column if more text needs to be changed

Adding a Header or Footer to Spreadsheet – When you need a title or date at the top or bottom of a worksheet:

1. Click on the “Insert” tab on the toolbar
2. Click on “Header & Footer” tab on the main toolbar
3. On the left of the new toolbar you can either click “Header” or Footer” tabs (these are now two separate buttons)
4. Each tab dropdown will give you pre-established names i.e. Page 1 or date for convenience
5. You can choose from the dropdown or type custom text directly on the header/footer pane
6. When you are finished, click off of the header or footer back into spreadsheet cells
7. To return to “Normal” view, click “View” on the toolbar and click “Normal”
8. The header/footer will be displayed when you print the spreadsheet

Custom Sorting Fields by Font Color & Additional Values – When text within fields are highlighted by color:

1. Highlight the entire dataset that you want to sort. including header row
2. Click on the “Home” tab on the toolbar
3. Click on “Sort & Filter” on toolbar
4. Click “Custom Sort”
5. Click on “Sort By” in first column and choose by the column title you want to sort on
6. Click on “Sort On” in the middle column and choose “Font Color”
7. Under the “Order” column on the far right, click on the arrow for font color and choose the color you are looking to sort on, then click "Ok"
8. At the top of the Sort box click “Copy Level” and another sort option level will appear below
9. Click on “Sort By” in first column and choose by the column title you want to sort on
10. Click on “Sort On” in the middle column and choose “Font Color”
11. Under the “Order” column on the far right, click on the arrow for font color and choose the next color you are looking to sort on, then click "Ok"
12. Your font colors should now be sorted from the top in the order you established

These tips have helped me time and time again to organize my data in a timely manner. I'd love to hear how these tips have helped you, or if there are any other special tricks you've learned to deal with large datasets in Excel.

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!