Workbooks and Worksheets

Worksheet The worksheet is the basic sheet where you enter your data, and you will do your calculations.

The worksheet consists of 256 columns and over 16,000 rows. You can add worksheets as needed. You can scroll through the worksheets, using the arrows in the lower left corner, or by using a labeled tabs at the bottom of the screen.

Each worksheet has a name on its sheet tab at the bottom left of the workbook window: Sheet1, Sheet2, and Sheet3. You view a worksheet by clicking its sheet tab.

Rename the sheet tabs to make the information on each sheet easier to identify.

Add additional worksheets if you need more than three. Worksheet tabs are a series of virtual tabs that allow you to move through your worksheets. You can rename the tabs by right clicking on them, and then selecting rename.

Excel Data Types: numbers and text + formulas

• Text — Alphanumeric data, such as titles, names, and addresses; these can be sorted or be filtered (limited) to screen results. time and date are also considered text.
• Numbers — Numeric values that can have computations run on them
• Formulas — Expressions that compute numeric results; these can be symbols or alphanumeric

Keyboard ShortCuts

Microsoft has tried to create a uniform interface for all of its programs. Excel is no exception. If you have used any of the programs in Office, most of Excel's interface will seem very familiar.

Many of the shortcuts from Word will work in Excel:

Columns and Rows

Worksheets are divided into columns, rows, and cells. That's the grid you see when you open up a workbook.

Columns go from top to bottom on the worksheet, vertically. Rows go from left to right on the worksheet, horizontally. A cell is the space where one column and one row meet.

Each column has an alphabetical heading at the top. The first 26 columns have the letters from A through Z.

Each row also has a heading. Row headings are numbers, from 1 through 65,536.

The alphabetical headings on the columns and the numerical headings on the rows tell you where you are in a worksheet when you click a cell. The headings combine to form the cell address, also called the cell reference. You'll learn more about this in the next section. There are 16,777,216 cells to work in on each worksheet.

Cells

Cell A Cell is the intersection of a row and column. Each cell has an address that consists of the column letter and row number (A1, B1, C2, I24 and so on). You enter data and formulas in the cells.

Inserting Columns and Cells

To insert a single column, click any cell in the column immediately to the right of where you want the new column to go. Then on the Insert menu, click Columns.

To insert a single row, click any cell in the row immediately below where you want the new row to go. Then on the Insert menu, click Rows.

Formulas

Formula bar When you enter information into a cell, it appears in the Formula bar.

You do math in Excel by typing simple formulas into cells. Excel formulas always begin with an equal sign (=). Here's the formula typed into cell C6 to add 12.99 and 16.99:

=12.99+16.99

The plus sign (+) is a math operator that tells Excel to add the values.

To do more than add, you would use other math operators as you type formulas into worksheet cells.

You would start each formula with an equal sign and use a minus sign (-) to subtract, an asterisk (*) to multiply, and a forward slash (/) to divide. You can more than one math operator in a single formula.

AutoSum

To add up the total of expenses for January, you wouldn't have to type all those values again. Instead you could use a prewritten formula, called a function.

You could get the January total by selecting cell B7, then clicking AutoSum Button on the Standard toolbar. This enters the SUM function, which adds up all the values in a range of cells.

Pressing ENTER displays the SUM function result 95.94 in cell B7. The formula =SUM(B3:B6) appears in the formula bar whenever cell B7 is selected.

B3:B6 is the information, called the argument, that tells the SUM function what to add. By using a cell reference (B3:B6) instead of the values in those cells, Excel can automatically update results if values change later on. The colon (:) in B3:B6 indicates a cell range in column B, rows 3 through 6. The parentheses are required to separate the argument from the function.

The autosum button has a dropdown box which offers quick access to some of the most commonly used formulas.

• Sum = adds
• Average = caclulates an average
• Count = tallies the occurances
• Max = the largest number in the range
• Min = the smallest number in the range

AutoFill

Once you have created a formula, you can copy the formula to adjacent cells.

You would select cell B7, which contains the January formula, then position the mouse pointer over the lower-right corner of the cell until the black cross (+) appears. Next, drag the fill handle over cell C7. When the fill handle is released, the February total 126.93 appears in cell C7. The formula =SUM(C3:C6) is visible in the formula bar near the top of the worksheet whenever cell C7 is selected.

After the formula is copied, the Auto Fill Options Button image appears to give you some formatting options. In this case you wouldn't need to do anything with the button options. The button disappears when you next make an entry in any cell.

Note: You can drag the fill handle to copy formulas only into cells that are next to each other, either horizontally or vertically.

Error Alerts

Sometimes Excel can't calculate a formula because the formula contains an error. If that happens, you'll see an error value instead of a result in a cell. Here are three common error values:

##### The column is not wide enough to display the contents of this cell. Increase column width, shrink the contents to fit the column, or apply a different number format.

#REF! A cell reference is not valid. Cells may have been deleted or pasted over.

#NAME? You may have misspelled a function name or used a name that Excel does not recognize. You should know that cells with error values such as #NAME? may display a color triangle.

If you click the cell, an error button appears to give you some error correction options. How to use that button is not covered in this course.

Sorting Data

You can sort any column in a list, just selecting the column and clicking the sort button. By default, the data is sorting in ascending order.

To sort the contents of more than one column at once, you would click Sort on the Data menu.

The Sort dialog box will open.

Choose which columns to sort, and how to sort them.

Filtering Data

Filtering selects just the data you need and hides all the rest. So you see only what you want to see, and you see it at a click. Filtering doesn't change your data in any way. As soon as you remove the filter, all your data reappears, exactly the same as it was before.

Click any cell in the data you want to filter. On the Data menu, point to Filter, and then click AutoFilter. AutoFilter arrows appear to the right of each column heading.

When you click an AutoFilter arrow, a list is displayed. The list contains each of the items in the column, in alphabetical or numeric order, so that you can quickly find the item you want.

When you click on the desired value, Excel hides all the rows on the worksheet except for those that contain that text in this column.

After you filter one column, if you want to focus on even more specific information, you can filter again on another column, and then again on another column, and so on. You can click the arrow next to any heading in any column to apply a filter.

Note You can filter columns in any order you choose. The filters are applied progressively, in the order you apply them. Each filter limits the data to which you can apply the next filter.

Custom Filters

When you filter by choosing from the list next to an AutoFilter arrow, you hide everything except your single choice. If you want to see more than one selection in a column, you can create custom filters.

You can also use custom AutoFilters to find items not available in the list next to the AutoFilter arrow, such as:

• Values within a range, such as all numbers between two particular numbers.
• Values outside a range, such as all dates earlier or later than a particular pair of dates.
• Values equal to or not equal to another value.
• Specific text that is part of other text.

To create a custom filter, click an AutoFilter arrow and then click (Custom…) to open the Custom AutoFilter dialog box.

Charts

To create a simple chart, select the data that you want to chart, as well as the column and row labels; click the Chart Wizard Button on the toolbar to open the Chart Wizard.

When the wizard opens, the Column chart type is selected. You could easily select another chart type. Next you would click the Finish button at the bottom of the wizard.

Any changes that you make to the worksheet data are instantly shown in the chart.

The wizard places this chart as an object on the worksheet, along with the data, as shown in the picture. A chart can also be placed on a separate sheet in a workbook

Chart Titles

Add descriptive titles to your chart

The Titles tab in Step 3 of the wizard has boxes for three titles for this chart: one for the chart, at the top, and one for each of the chart axes, vertical and horizontal.

After the titles have been entered, they appear in the preview on this tab.

You can add a title for the chart by typing in the Chart title box.