Excel Basics - Day 1 - Note Sheet

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

Setting up Excel the First Time

Click View > Toolbars > Customize

Uncheck Standard and Formatting toolbars share one row

Setup Default File Locations

By default, Excel automatically saves files in My Documents. You can change this default file location by clicking Tools > Options > General. In the Default file location box, highlight the current contents and type the drive letter/path to your user directory.

Turning On and Off Toolbars

Click View > Toolbars

From this menu you can turn on and off all of the Excel toolbars. The Standard and Formatting toolbars should always be turned on. The Standard and Formatting toolbars contain all of the frequently used Excel functions. A check next to the toolbar name indicates that the toolbar is turned on.

Excel Workspace

Column and Row Headings - The Cell Address

All columns are labeled with letters; the letters A thru IV are used. Rows are labeled with numbers and there are 65,536 rows in an Excel spreadsheet. Where a Column and a row intersect is called a cell. Cells are addressed with their Column name and Row number. In the image above, cell G6 is an example of a cell address. This is important because Excel uses these cell addresses to perform mathematical calculations. When you select more than one cell it's called a range of cells. The range is identified by the first cell selected a colon (:) and the last cell selected. A range may look like this G6:J6.

Menu Bars

Standard Toolbar

If you are familiar with Microsoft Word you will see some similarities in Excel. The Menu bar and toolbars have many of the same options. From the Standard toolbar you can create new documents, open existing documents, save documents, email files, print, print preview, spell check, cut, copy, and paste text, copy formatting, use undo and redo, and create hyperlinks.

The Standard toolbar contains some icons that you may not be familiar with:

	AutoSum - Used to quickly add a row or column of numbers
	A wizard like feature that allows you to perform thousands of mathematical calculations
	Sort a column in Ascending order
	Sort a column in Descending order
	Create Pie, Bar, Column, Line, Radar, Doughnut and other charts with an easy to use wizard interface

Formatting Toolbar

The Formatting toolbar allows the user to format the worksheet. This includes changing the font face and size, bolding, italicizing, and underlining text, and changing the text alignment. You must select the cell or cells that you want to format prior to using these tools.

	Merge and Center - This tool allows you to merge two or more cells together and centers the contents of the resulting cell
	Apply currency style to the contents of a cell (dollar sign and two decimal places).
	Apply percent formatting to the contents of a cell
	By default Excel does not add commas to numbers, this tool will apply a comma style to selected cells
	Increase decimal places (increases the number of digits after a decimal)
	Decrease decimal places (decreases the number of digits after a decimal)

Common Shortcut Keys You Should Know

CTRL+N		Creates a new document
CTRL+O		Opens an existing document
CTRL+S		Saves the current document
CTRL+P		Prints the current document
CTRL+Z		Undo!!
CTRL+C		Copies the current selection
CTRL+X		Cuts or moves the current selection
CTRL+V		Pastes either copied or cut text into a new location
F7		Spell check

A special note about Undo: Undo can be your best friend. In addition to using the shortcut, CTRL+Z or the Undo icon , you can also click the Undo arrow to see a list of your most recent changes to the worksheet. Instead of pressing CTRL+Z repeatedly, you can select the items that you want to undo from the list.

For additional common shortcut keys, click here.

Shortcut Keys for Moving in an Excel Worksheet

Entering Data

Select the cell and type the desired number or text. Numbers should include the appropriate decimal places, but do not need to include dollar signs. Press the Enter key to move to the cell below or the Tab key to move to the cell to the right. I typically enter data down a column rather than entering numbers from left to right.

Changing Column Widths and Row Heights

The vertical lines between each column heading can be used to resize the column. To resize a column or row, move your mouse pointer over the line between the two column headings, hold down your mouse button and drag to the left or right.

You can automatically resize the column or row to "fit" the contents of the widest cell by double clicking the vertical line.

Using AutoSum

Click in the cell that you want the total to appear in and click the AutoSum tool . Marching ants will appear around the numbers that Excel plans to add. If the selection is correct, click the AutoSum tool again to complete the calculation. If the selection is incorrect, highlight the cells that you want to add and click the AutoSum tool to complete the calculation. You can also select multiple cells and click the AutoSum tool once to total multiple columns or rows at the same time.

Viewing Formulas Using the Formula Bar

You can quickly view the formula of any cell by selecting the cell and looking at the formula bar. The formula bar displays the contents of a cell. The contents may be text, a value (numbers), or a formula. You can also edit the contents of a cell in the formula bar. All Excel formulas must begin with the equal symbol (=). In the example image, the SUM function is used. The function name is followed by an open parenthesis. Within the parenthesis is the range of cells that you want to perform the mathematical calculation on. In this example we are adding the range B3 thru B6. The colon (:) is used to indicate thru. Finally, a closing parenthesis is used to conclude the formula.

Using the Fill Handle

The fill handle can be used to copy formulas and create series. The fill handle is located in the lower right hand corner of a selected cell (see image to the right). If you want to copy a formula, select the cell that contains the formula and move your mouse over the fill handle, your pointer will turn into a bold cross. Hold down down your left mouse button on the fill handle and drag the handle across the cells that should contain the same formula. In the image to the right, drag the fill handle to the right to column E to copy the total to the Campus, Telephone, and Web columns.

The fill handle can also be used to create a series. You may want to create monthly column headings. Type the first two months, i.e. January and February in adjacent cells and select both cells. Drag the fill handle across the columns you wish to fill. As you drag you should notice a bubble help that displays the months in the series. You can use this same technique to create a series of years, numbers, letters, and other commonly used heading titles.

Using Paste Function

The Paste Function tool can be used to create more complicated formulas. Click the Paste Function tool and the Paste Function dialog box will appear.

Select the Function category and the Function name and click OK. A wizard will appear. In this example, the Average function is being calculated; so we must select the range of cells we want to average.

Click the icon to the right of number 1 (indicated with a red square in the image above). The Average function box will "roll up" and you can select the cells that you want to average.

Click the "roll down" icon (indicated with the red square in the image below) and click OK to complete the function.

Merge and Center

You may want to center heading across multiple columns. Select the cells you want the title to appear across and click the Merge and Center icon .

Using Auto Format

You can quickly format your worksheet using AutoFormat. Select the range you would like to format and click Format > AutoFormat.

Select the desired format from the list. Note: The Options button will allow you to select the formatting options you would like to apply. Click OK to apply the format.

Creating a Chart

Select the range of cells that you want to chart. Click the Chart Wizard icon .

Note: When creating charts you select the individual figures OR the totals, do not select both. In the sample above I have included everything but the totals.

Select the Chart type, chart sub-type, and click Next. Click Next again. Type the Chart title and click Next. Select As Object In Sheet 1 and click Finish. Note: Charts will be covered in more detail in Day 2 of the Excel Basics Course.

Saving the Worksheet

Click the Save icon or click File > Save. Select the folder that you want to save the file in, give the file a name in the File name box and click Save.

Printing

Click the Print icon or click file print. More advanced printing options will be covered in Day 2 of the Excel Basics Course.