IF you have a range of data and you want to highlight, say, the top 10 values by giving them a unique fill color or font color, then follow these steps:

1. In the Home tab, Styles group click Conditional formatting.
2. Point to Top/Bottom Rules and click Top 10 Items.



3. The Top 10 Items window will open.



4. Specify the number of items to format (left) and the formatting options from the drop-down list on the right. If you are not satisfied with what is on the list, you can click Custom Format at the bottom and then set up your own.
5. Your data will look like this.

If you have a cell, or column, that contains some text and you want to distribute that text into the adjacent columns, then :

1.       Select the cell, range of cells within the same column or the entire column that you want to distribute its data.

2.       On the Data tab, Data Tools group, click Text to Columns. This will start the Convert Text to Columns Wizard .

3.       In the first step of the wizard specify your data type then click Next :

a.       “Delimited” means data fields are separated by special characters like commas. E.g ” Shakespeare,William “.

b.      “Fixed” means fields are aligned in equal size columns separated by spaces. E.g ” Shakespeare William “.

4.       In the second step of the wizard you specify the field delimiters, if you choose “Delimited” in the previous step, or specify the field widths, if you choose “Fixed” in the previous step. Click Next when you are done.

5.       The last step lets you specify the data format of each column, if it is different from the default set by Excel. Click Finish when you are done.

6.       The data will be distributed into a number of columns depending on the number of separators or spaces in the original column.

You can highlight duplicate values in a data range by using conditional formatting. Follow these steps:

1. Select your data range.
2. In the Home tab, Styles group click Conditional formatting.
3. Point to Highlight Cells Rules.
4. In the sub-menu to the right click Duplicate Values.



5. The Duplicate Values window will open. Click OK.
6. Any duplicate values in the range will be highlighted with the default color. You can change the color before clicking OK.

Applies to: Excel 2007, Excel 2010

To clear conditional formatting from a range of cells follow these steps:

1. Select the cells for which you want to clear conditional formatting.
2. On the Home tab, Styles group, click Conditional formatting.
3. Click Clear Rules.
4. From the menu to the right Click Clear Rules from Selected Cells.

To clear conditional formatting from the whole worksheet (no selection is required):

1. On the Home tab, Styles group, click Conditional formatting.
2. Click Clear Rules.
3. From the menu to the right Click Clear Rules from Entire Sheet.