Excel Intermediate - Day 1 - Note Sheet

Instructor: Robin Wood - robin 'at' wctrain dot net

Supporting Files:

hyperlink.xls and hyperlinkwithformulas.xls

Formatting Cells

Select the cell range that is to be formatted. Right click the selected cells and click Format Cells.

Changing the Text Orientation

Click the alignment table and adjust the Orientation Degrees to the desired rotation.
Indenting Text

Select the range of cells and click the Indent icon .

Copying Cells

Select the range and click the copy icon . Click the cell that you want the range to be copied to and press the enter key on your keyboard.

Inserting Rows

Select the row number that you want to insert a blank row above and right click the row;select Insert. This can also be done with Columns. Select multiple rows or columns to insert the same number of rows or columns.

To insert multiple rows, select any cell and select Insert... (notice the three ... (ellipse). From the menu, select how you want the surrounding cells to be shifted.

Percent Formats

You can type and value followed by the percent % sign to format a cell with percents. I have found that once you use this format in a column it carries to the cells below. To remove the percent format just select the cells and click the comma or currency style .

Freezing Titles

Freezing Titles will allow you to freeze row or column headings so that the headings will always be visible. Select the cell just below and to the right of the headings that you want to "lock" or freeze and click Window > Freeze Panes.

Inserting the Current Date

Type =now()

Absolute Cell Referencing

When you copy a formula the cell addresses in the copied formula will change automatically. There are times that you want to keep a cell reference constant. When you want to use absolute referencing you apply $ signs around the column letter and/or row number. The shortcut key for making a cell absolute is F4.

The IF statement

Example: =if(b4>=$b$21,$b$20,0)

Starts with - =if(the test >= value if true, value if false)

>= greater than or equal to

Revealing Cell Formulas

Press CTRL+~ (to the left of the number 1). This will reveal all the underlying formulas in a worksheet.