Highlight Alternating Rows or Columns

by with No Comment beginnerformattingtips & tricks

This post will show you how to create the effect of highlighting alternating rows, which sometimes makes it easier to read a data set.

In order to accomplish this, first select all the cells in the worksheet and then select New Rule under conditional formatting.

For the new rule you will need to select the last option to use a formula. The formula we will need to use is as follows:
=MOD(ROW(A1),2)=1
The MOD function calculates the remainder after division and has two arguments, the number to be divided into, and by what number. 
Using ROW(A1) means the reference will change depending on the location of the cell, meaning it will encompass every row in the selection. Using 2 as the divisor will help identify if the row is as odd number or even. If you’d rather highlight columns than rows, use the COLUMN function.
The formula will be true if the remainder is 1, meaning the row is an odd number (e.g. highlighting will start on the first row). If you want highlighting to start from row 2, just change the 1 to a 0, since even rows will have no remainder.
Once the formula is set, click on Format to determine the appearance of the alternating rows.
I select a light blue colour for the alternating rows
After I press OK this is the result of my conditional formatting:
If you don’t want to apply the formatting to all the cells or want to change the range, under conditional formatting select manage rules
There you will see a field where the formatting Applies to. Here you can change the range you want the formatting to apply to.

Extracting Unique Values

by with No Comment beginnerunique

For Excel 2007 and newer:

Select the data you want to extract unique values from, and under the Data tab, click on Remove Duplicates.

If the column you select is alongside other data, it will remove those cells as well. If that’s not what you want, I’d recommend copying the column over somewhere else so it is by itself, and then click on the Remove Duplicates button.

For Excel 2003 and older:

Run an Advanced Filter on the column that you want to extract duplicates from, leave the criteria Blank, select Copy to Another Location, and select Unique Records Only.

Note, select the advanced filter option when you are in the sheet you want to extract the values to. For example, if your data is on sheet 1 and you want to extract the duplicates to sheet 2, you need to select advanced filter while you are on sheet 2, otherwise there will be an error.