XL abc's (redirected from Excel abc's)

XL abc's

This tutorial is for newcomers to Microsoft Excel (examples are Excel 2000) and will provide the essentials for working with Excel worksheets. Keep this on screen while you work with Excel, and press Alt-Tab to jump back and forth between both applications. If your screen is large enough, you can size both windows so they appear on screen at the same time. For more information on Windows basics, see Win abc's.

Also, if you don't understand a term used in any of these explanations, just look it up. Everything is in the Encyclopedia.

All the individual subjects in this tutorial are replicated under their own topic names with an XL prefix. You can always go back and review a topic without having to search for it in this entry. For example, if you want to review how to delete rows or columns, look up "XL Delete row/column."

The key things you need to know to work with Excel are:

1. LABELS - Descriptive text entered into a cell as a heading, page, row or column heading.

2. VALUES - The numeric data within a cell.

3. FORMULAS - The algorithms that state how data in the cells will be calculated. Functions are predefined formulas you can use for a variety of common calculations.

Excel 2000 Users
All the screen shots in this tutorial come from Excel 97 unless otherwise noted. There are only the slightest differences between Excel 97 and Excel 2000 for the examples provided in this tutorial. In Excel 2000, there may be more icons on the toolbar or more options in a menu, but the essential functions you are learning here are the same.

Spreadsheets, Worksheets and Workbooks
Spreadsheet and worksheet are generic terms for a document created in an application that calculates rows and columns of numbers. Excel creates three worksheets at a time, the collection of which is called a "workbook." When you save an Excel worksheet, you are actually saving three worksheets as a workbook file with an .XLS file extension (for more on file extensions see extension). You can always add more worksheets to a workbook (see XL Adding worksheets).

Create a Workbook
To create an Excel workbook, launch Excel by clicking the Start button on the Windows Taskbar. Select Programs and then select Microsoft Excel from the submenu. Excel opens to a blank workbook, which has preset columns and rows. A new Excel workbook is named Book1 until you rename it.

One Workbook - Three Worksheets

The three worksheets are identified at the bottom of the screen as tabs that can be clicked to switch between them. Much of the time, people just use the first worksheet and ignore the rest. You can also add more worksheets and give them names (see XL Rename worksheets and XL Adding worksheets).

Create Another Workbook
To create another workbook, select the File menu and New (File/New). Click OK to create the default workbook.

Book1, Book2, etc.

Excel names your workbooks Book1, Book2 and so on. You rename them when you save them the first time.

Button/Keyboard Tip!

Click this button on the Excel toolbar to create a new workbook. Or, press Ctrl-N on the keyboard.

Save, Close and Exit
Saving your spreadsheet transfers everything on screen to the hard disk so you can retrieve it later. To save a workbook, select File/Save. The first time you save a workbook, you rename it by typing over the Book1 name (see below). Closing your workbook does the same thing as Save but also removes the workbook from the screen. When you are finished with the workbook, select File/Close.

Save As
The Save As function is very useful for making copies of the same workbook with different names or saving your workbook with the same name in different folders. The difference between Save As and Save is that Save As prompts you for a file name, whereas Save just writes the contents of your screen to the same file.

Quitting Excel
To quit Excel, select File/Exit. If you made changes without saving, Excel will prompt you to save the changes.

All File Management from the File Menu

Save, Save As, Close and Exit are all options in the File menu.

Button/Keyboard Tip!

Click this button on the Excel toolbar to save your document. Or, press Ctrl-S on the keyboard.

The Default Folder
Excel defaults to saving your new workbook in the My Documents folder. You can override that by selecting another folder. If you are unfamiliar with file and folder organization, see Win Folder organization.

When you save your workbook for the first time, rename Book1, Book2, etc. by simply typing over the temporary name.

Open a Workbook
To open an Excel workbook, select File/Open. Select the folder the workbook was saved in, and double click on the workbook to open it. You can open multiple workbooks by highlighting them and clicking Open.

Quick Tip!

If the workbook was one of the last 15 documents opened, click the Start menu in the Taskbar, choose Documents and select the workbook by name.

Button/Keyboard Tip!

Click this button on the Excel toolbar to open a workbook. Or, press Ctrl-O on the keyboard.

Rows, Columns and Cells
Worksheets are made up of cells organized into rows and columns. The height and width of rows and columns can be made larger or smaller (see XL Insert row/column).

Cells are the building blocks of workbooks. A cell can hold labels, values or formulas. At any given time, only one cell is active. The active cell has a bold border and its row and column headers will also display a bold number or letter (in the following example, A1 is the active cell).

To enter descriptive text (labels) and numbers (values), select a cell and start typing. When you're done, press Enter, and the active cell moves to the one below.

Labels and Values
Labels are descriptive text entered into a cell as a page, column or row heading. Values are quantities and money amounts used in calculations. Values can also be dates and times as well as numeric data. Labels and values do not change unless you purposefully change them by typing new data on top of the old data.

Labels
If the label starts with a letter, just type it into the cell. If it starts with a numeric digit, type an apostrophe first. The apostrophe tells Excel that the numeric data that follows is not a value, but a descriptive label. In the following examples, "Grades for Semester" is just typed into the cell, but the 115 for Model # must be typed with an apostrophe first ('115) to define it as a label and not a number that can be calculated.

Values
Values are simply numeric data that will be used in calculations, but they can also be dates and times. Values are constant. They do not change unless you purposefully type a new value into the cell. In the examples above, note that the numbers under Grade and Price are values. By the way, you don't have to enter the dollar signs. You can set the cell format to do that automatically.

Formatting Labels and Values
To change the alignment of a cell, select the cell or cells and click the desired alignment button on the toolbar.

Formatting numbers
There are several ways to format numbers in Excel. To format the number with a dollar sign, a percent sign, commas only, or to increase or decrease the number of digits after the decimal point, select the cell or cells you want to change and click any of the following buttons on the Toolbar. You can also change numeric layout by selecting Cells/Number from the Format menu (see below).

Fractions, Phone Numbers, Zip Codes, Etc.
Excel offers numerous formats beyond dollar signs and commas. To learn the options, select Cells from the Format menu and review each category in the Numbers tab and click the other tabs for special effects. Zip code, phone number and social security number formats are in the Special category under the Numbers tab.

To apply any of these styles, highlight the cell or cells first and then select Format/Cells and the appropriate section.

Formulas and Functions
Formulas and functions are the heart of a spreadsheet. They provide the "ripple through" effect that occurs when you make a change to any of the numeric values in the cells and all the formulas are recalculated automatically. A formula is an algorithm, or mathematical expression, that says "add this to that" or "sum this column and multiply by 5." A function is a predefined formula that saves steps. For example, Sum is a function that adds all the numbers in contiguous cells. Following are the common math operators used in formulas:

+ Addition
- Subtraction
* Multiplication
/ Division
% Percent
( and ) Groups items together

Creating and Editing Formulas
Formulas are created by selecting cells and pressing the appropriate math keys on the keyboard or function buttons. You create a sequence of events such as "this cell minus that cell divided by that cell."

After formulas have been created, you can edit them manually. When you select a cell that contains a formula, the formula is visible in the Formula Bar, where it is available for editing. In the following example, the contents of B2 are subtracted from B1. An equal sign is always the first character of a formula.

Adding (Summing) Columns and Rows
To add a column or row of numbers, select the empty cell under the column or on the right side of the row, click the SUM button on the toolbar, then press Enter. The SUM function will add a column or a row of numbers.

As soon as you press the SUM button, the formula for that column is displayed as follows:

Pressing Enter hides the formula, and the results are displayed as follows:

Subtracting Numbers
To subtract one number from another, do the following steps. An example is provided below:

1. Select the cell that will hold the result.
2. Press equal key.
3. Select the cell you want to subtract from.
4. Press subtract key (- key).
5. Select the cell to be subtracted.
6. Press Enter key.

Multiplying Numbers
To multiply one number with another, do the following steps. An example is provided below:

1. Select the cell that will hold the result.
2. Press equal key
3. Select the cell you want to multiply.
4. Press asterisk key (* key).
5. Select the cell with the multiplier.
6. Press Enter key.

Dividing Numbers
To divide one number with another, do the following steps. An example is provided below:

1. Select the cell that will hold the result.
2. Press equal key.
3. Select the cell containing the numerator.
4. Press divide key.
5. Select the cell with the denominator.
6. Press Enter key.

Compound Operations and Parentheses
Very often, there is a need to combine two or more mathematical operations in the same formula. When you do that, you often need to use parentheses to group the calculations in the order you want them to be executed. Excel uses standard algebraic rules to perform operations in the following order: (1) exponentiation, (2) multiplication and division and (3) addition and subtraction. However, Excel will always perform operations grouped in parentheses first.

The following example diagrams the steps in developing a compound operation. It calculates gross pay from hours worked in a week which includes regular pay, overtime at time and a half and double time. The formula being built is hourly rate times all the hours worked, but the hours worked are grouped in parentheses so they are treated as a unit and added together. At the end is another example that shows what you would get if you do not use parentheses. The formula in this example is:

gross pay = hourly rate X hours worked

hours worked is computed by

(regular hours +

1.5 X overtime hours +

2 X double time hours)

You Need the Parentheses
If you hadn't used parentheses in the formula, you would have gotten the wrong results. With parentheses correctly used, the multiplications and additions within the parentheses were computed first. Then the rate was multiplied by that result in the following order:

1. 1.5 x 18 hours = 27 hours
2. 2 x 4 hours = 8 hours
3. 40 + 27 + 8 = 75 hours
4. $15.23 x 75 hours = $1142.25

Without parentheses, the dollar payment for the first 40 hours is incorrectly added to the hours worked as follows:

1. $15.23 x 40 hours = $609.20
2. 1.5 x 18 hours = 27 hours
3. 2 x 4 hours = 8 hours
4. $609.20 + 27 + 8 = $644.20

End of Microsoft Excel ABC's