Excel tips and tricks

Conditional Formatting

Conditional Formatting can help find duplicate values in an Excel file. To use Conditional Formatting to locate duplicate values in cells in Excel:

1. Open the Excel document.
2. Select the cells to be checked for duplicates. To select an entire column, click on the column name (A, B, C, etc.).
3. Click on the Home tab at the top of the screen.
4. In the Styles section, click Conditional Formatting.
5. Choose Highlight Cells Rules.
6. Choose Duplicate Values...
7. Click OK.
8. All cells that contain duplicate values will now appear with a light red background and red text.

Fill Handle

The Fill handle in Excel has the ability to fill blank cells with a value that is already entered into the Excel file. If the same value is to be entered for each row of a column, the Fill handle can make data entry faster. To use the Fill handle in Excel:

1. Open the Excel document.
2. Determine what cells need to be filled.

Note: The Fill handle can only fill cells adjacent to the existing data. If the cells that need to be filled are not touching the cell that contains the data, copying and pasting the information may be a better option.

3. Select the cell that contains the data that will be filled.

Note: The Fill handle will attempt to predict what will be filled. If the cell contains a numerical value, the Fill handle will incrementally increase the value in each filled cell. If the numerical value that is being filled should not change, enter the data to be filled in two cells, then select both cells during this step.

4. Hover the cursor over the darkened square in the lower right of the selected cell. The cursor should become a cross hair.
5. Click and drag the cursor over the cells to be filled.

Filtering

Filtering information in Excel will allow for easier viewing of the data, as the information can be sorted according to each column heading. To filter data in Excel:

1. Open the Excel document.
2. Select cell A1.
3. Click the Data tab at the top of the screen.
4. In the Sort & Filter section, click Filter.
5. Each column heading will now become a drop down menu. Click on the arrow on the right side of the cell to sort the information.

Finding blank cells

Each import file has columns that cannot contain blank values. These columns will be indicated by red column headings in the import file. To find blank cells that may be causing import errors:

1. Open the Excel document.
2. Select the cells to be checked for blank values. To select an entire column, click on the column name (A, B, C, etc.).
3. Click the Home tab at the top of the screen.
4. In the Editing section, click on Find & Select.
5. Choose Go To Special...
6. Choose Blanks.
7. Click OK.
8. All blank cells will now be selected.

Find and Replace

The Find and Replace feature in Excel can locate and replace specified values in the Excel document with another value. Find and Replace can be used in an instance where a value is repeated on an Excel sheet, but all values need to be replaced with another value. To use the Find and Replace feature:

1. Open the Excel document.
2. Click on the Home tab at the top of the screen.
3. In the Editing section, click on Find & Select.
4. Choose Replace...
5. In the Find what field, enter the value to be replaced.
6. In the Replace with filed, enter the value to replace the existing value with.
7. Click Replace All to change all found values, or Replace to replace each value one at a time.