3 more tips to make Excel 2010 work for you

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.



Search article