Set Your Cells Apart: How To Use Conditional Formatting

Imagine your boss comes in to your office one day and gives you a giant spreadsheet that goes on for pages and pages and pages. He wants you to highlight every single cell that contains word "NO" and says it is a top priority. There are two ways you can do this. If you prefer to do things the tedious and time-consuming way, you could go through every single sheet and cell in the entire workbook and highlight each and every cell. OR if you like to be quick and efficient, you can use Excel's powerful Conditional Formatting tool. If that way sounds appealing to you, read on! We will explain how to make this daunting and formidable task as easy as 1, 2, 3!

1. Select the cells where you want the formatting applied. On the Home tab, click the Conditional Formatting icon and select "New Rule."

2.Select the option "Format only cells that contain". In the first drop down menu, select Specific Text (or Cell Value, etc). In the third input box, type "No".

3. Select the custom format you want by clicking the Format... button. We selected the font color to be red but you can format it anyway you like. Hit OK.

And there you have it! What could have taken you hours and hours now literally takes you minutes! 

There are many other great ways to format conditionally. Be adventurous and play around with some of the other options and you will be impressing your boss and getting that promotion you deserve in no time! 

Extreme Filters & Sorting

This week's tip is one that many of you may already know a bit about. However, we want to take your knowledge one step further and show you some neat functions.

1) Sort by Color: Let's say your boss goes through a list and highlights all the people he wants to meet with in the next week. What is the best way to sort through a list of over 500 employees? Easy, just filter it by color! First create the column filters.

Next, click on the column filter arrow, find the Filter by Color option, and select the color you wish to filter. If your boss selected multiple colors, you can also select the Sort by Color option on that same drop down menu.

2) Custom Sort: Most of your friends probably only know how to use the simple Sort function. However, that tool is very limited. The Custom Sort tool is much more powerful. You will find it on the Home ribbon - Editing - Sort & Filter - Custom Sort

To get started, highlight the table you would like to sort. Make sure you include the headings. Next, select Custom Sort on the Home ribbon. A box like this will appear.

The Sort By drop down will allow you to select which heading or column you want to sort. You can then select what you want to Sort On (usually just Values) and the Order you want it. For this example, We want to sort by ID Number from Smallest to Largest.

You can even add multiple sorting criteria. You do this by clicking on the Add Level button. Now I can first have it sort by ID Number from Smallest to Largest and within that, sort by Last Name from A to Z. That is pretty neat, huh?!

Now you can sort and filter like no one at work can! This is sure to save you time, organize your data more efficiently, and, most importantly, impress your co-workers. Stay tuned for next week's tip!