Entering Text



Microsoft Excel, Level I

[pic]

Margie Johnson and Brad Parnell

Technology Staff Development

Metropolitan Nashville Public Schools

Table of Contents

Excel Basics 3

Introduction to Excel 3

The Excel Environment 3

The Toolbars…………………………………………………………………………………..4

Entering Data and Navigating in a Worksheet 5

Entering and Correcting Data 5

Entering Data 5

Replacing Cell Contents 5

Changing an Entry As You Type 5

Navigation and Movement Techniques 6

Using the Mouse 6

Using Keyboard Shortcuts 6

Saving a File 7

Saving As a Template 7

Moving and Copying Data 8

Inserting and Deleting Rows 8

Moving, Copying, and the Fill Series Feature 8

Moving Data 8

Copying Data 8

Using the Fill Series Feature 9

Using Formulas 9

Using AutoSum………………………………………………………………………...............10

Using Absolute Reference…………………………………………………………………….11

Formatting a Worksheet……………………………………………………………………….11

Merge and Center………………………………………………………………………………11

Printing a Worksheet 12

Prior to Printing 12

Spell Checker 12

Print Preview 12

Page Setup 13

Additional Print Options 14

Introduction to Charts…………………………………………………………………….…….15

Excel Basics

Introduction to Excel

Excel is a powerful program that can be used in many ways. Typically, it is used to create spreadsheets because it has hundreds of built-in mathematical formulas to do the calculations for you. Many people also use it as a database to help organize information, such as name, address, etc…

The Excel Environment

[pic]

A spreadsheet is divided into cells that make up columns and rows. Excel contains 256 columns and over 65,000 rows. Each column heading is assigned a letter, and each row heading a number.

NOTE: A cell is named by its COLUMN and ROW location. For example, A1.

The Toolbars

If there are two toolbars sharing one row—ex. the Standard Toolbar and the Formatting Toolbar.

1. You can put them on separate rows. Click View—Toolbars—Customize. The Customize Dialog Box appears.

2. Check the Checkbox in front of the option Show Standard and Formatting toolbars on two rows. Click OK.

[pic]

The Standard Toolbar

[pic]

The Formatting Toolbar

[pic]

Entering Data and Navigating in a Worksheet

Now that we know the environment, we can get started creating a spreadsheet. When creating a worksheet it is recommended that you start with the text. Enter the numbers, then the formulas. You may want to leave the formatting until the end.

You will notice that when you enter text, Excel aligns to the left. When you enter numbers, Excel aligns to the right. This is the default but can be changed by simply using the alignment buttons on the formatting toolbar.

Entering and Correcting Data

Entering Data

1. Select the cell where you want to put data. You know it is selected because it has dark black lines around it.

[pic]

2. Type the data. Press .

Replacing Cell Contents

1. To affect it, you must select it. Use the White Cross to select the cell that you need to put different data.

[pic]

2. Once it is selected, begin typing and all the contents in the cell will automatically be erased.

Changing an Entry as You Type

Sometimes you will only want to change part of your text or make a correction as you type.

1. When you click on a cell the contents appear in the Formula Bar, which is at the top of the worksheet.

[pic]

2. Use your cursor and click where you want to start editing. A blinking cursor appears to show you were your editing is taking place.

3. To delete characters, you have two options-- key or key. The Backspace deletes one space back. The Delete key deletes the character in front of the blinking cursor.

Navigation and Movement Techniques

Being able to move around efficiently within a worksheet is important, too. Here are some tips.

Using the Mouse

Of course, you can navigate using the mouse. Don’t forget the scroll bars on the bottom and side of the worksheet.

Using Keyboard Shortcuts

|Keyboard Shortcut |What It Does or Where It Goes |

|TAB |Moves Right in a Row |

|SHIFT TAB |Moves Left in a Row |

|ENTER |Moves Down in a Column |

|SHIFT ENTER |Moves Up in a Column |

|←↑→↓ |Moves in Direction of Arrow |

|HOME |Moves to Beginning of Row |

|CTRL + HOME |Moves to Beginning of Worksheet (A1) |

|CTRL + ←↑→↓ |Moves Direction of Arrow to Ends |

|PAGE UP |Moves to Previous Screen Up |

| |in Same Sheet |

|PAGE DOWN |Moves to Next Screen Down |

| |in Same Sheet |

|CTRL + PAGE UP |Moves to Previous Worksheet |

|CTRL + PAGE DOWN |Moves to Next Worksheet |

Selecting Cells

You will find it necessary to select certain cells in order to make editing or formatting changes. By selecting a range of cells, you can make a number active at one time so that you can apply formatting to many cells at once.

Click and Drag Method

Simply click in the cell you want to start in and hold and drag across to the cell where you want to stop.

Shift Key Method

The shift key allows you to select multiple cells at once that are touching. Select the cell where you want to begin- hold down the shift key- then select the cell where you want to range to end. This will select all cells in that range.

Control Key Method

The Ctrl key allows you to select multiple cells that are not touching.

Select the first cell- hold the control key- then cell as many cells as needed.

Adjusting Column and Row Width/Height

When you start to type a long word into a cell, you notice that it over hangs into the next column. You can adjust the width several ways:

1. When you are in between the column headings, your arrow becomes[pic]. A two headed arrow always means “re-size”. Simply click-and-drag to the desired width

2. When you have the [pic] symbol, you can also double-click which automatically adjust the column to fit the longest word in the column.

3. If you would like to make all the column’s the same width, go to the menu bar, Format(column(width.

* You can adjust the rows using the same features.

Saving a File

It is recommended that you periodically save your files while you are working to avoid unneeded frustration if you accidentally lose power or your computer freezes.

There are several ways to save a file:

1. File—Save or Save As.

2. Click the Save Icon [pic] on the Standard Toolbar.

3. Use your keyboard and press CTRL + S.

When saving a document, remember where you Save It.

Saving As a Template

Sometimes you will find yourself using the same file layout repeatedly. To ensure that you don’t accidentally save over your original, you make it a Template.

1. Create the layout that you plan to use repeatedly.

2. Click File—Save As. The Save As Dialog Box appears.

3. By File Type, click the drop-down arrow and select Template. Give it a File Name and click OK.

Inserting and Deleting Rows/Columns

While there are a couple of ways to insert and delete rows, this way is the quickest and easiest.

1. To affect it, select it. Select the row below where you want to insert a row by clicking on the row number on the left side of the spreadsheet window.

[pic]

2. Right-click on the row number to get the shortcut menu. Choose Insert

3. Follow the same steps for delete, but choose Delete.

4. Columns work the same way (they are inserted to the left of the column

selected)

Moving, Copying, and the Fill Series Feature

Before we start the next section, let’s take some time to discuss the cursor in Excel. A slight change in cursor means a new operation.

|Cursor |Function |

|[pic] |Selects |

|“white cross” | |

|[pic] |Moves |

|“four-headed arrow” | |

|[pic] |It is located in the lower right corner of a selected box. |

|“auto-fill handle” |Fill Series Feature |

Moving Data

1. Select the data you want to move.

2. Go to the sides of the selected cells until your cursor changes to the “four-headed arrow”. Click and drag the cells to the desired location.

You can also use cut and paste to perform a move, especially if it is across workbooks.

Copying Data

1. Select the data you want to copy.

2. Choose a copy command:

a. Right-click—Copy, or Click Edit—Copy, or Click the Copy Icon

b. Get the “four-headed arrow” on the edge of the selected cells. Press CTRL while you click and drag the cell to the desired location.

You can also copy and paste across worksheets and workbooks.

Using the Fill Series Feature

Have you ever gone down the row numbering 1, 2, 3, etc…? No more….the fill series feature is here to save the day.

1. Enter the first and second number of the range in adjacent columns or rows.

[pic]

2. Select the cells. Use the “auto-fill handle” and click and drag to fill in the other numbers.

[pic]

You can also use this feature with the days of the week and months.

This feature “recognizes patterns”, which is especially great when you are working with formulas. You only have to do one formula, and then use your auto-fill handle to do the rest of the work!

Using Formulas

Excel’s power demonstrates itself when formulas are used. No more crunching numbers because the computer does it for you.

o The key to creating formulas in Excel is the = sign. The equal sign tells the computer that you are creating a formula.

o Use cell references to create formulas, so calculations are automatically updated if the cell value changes.

o Another key to creating formulas is to go back to you basic math class concept—the Order of Operations. Do you remember Please Excuse My Dear Aunt Sally? You must follow these rules when entering a formula for it to work correctly.

|Operator |Performs |

|+ (plus sign) |Addition |

|- (minus sign) |Subtraction |

|* (asterisk) |Multiplication |

|/ (slash) |Division |

|( ) (parentheses) |Controls the order of mathematical operations; calculations |

| |within the parentheses are performed first. |

|% (percent) |Converts a number into a percentage; for example, when you type |

| |10%, excel reads the value as .10 |

|^ (caret) |Exponentiation; for example, when you type 2^3, Excel reads the |

| |value as 2*2*2 |

Example:

If I wanted to find the total sales for Jan-March

1. I would select the cell where I wanted my answer to go

2. start with an equals sign

3. Click in the cell that contains Jan. sales, then a plus sign, then Feb. sales, then a plus sign, then March sales, then enter.

* remember if you use cell references the formula will be updated if you make changes in the future.

Using AutoSum

This is a powerful tool, which is used often so it is made easily accessible to us on the standard toolbar.[pic]

1. All you need to do is get the cell selected where you want the formula to be.

2. Click on the AutoSum button and Excel, being the smart program that it is, will draw dancing lines around what it thinks you want to add.

3. If it is correct all you have to do is press enter.

4. If it is not correct, you must adjust it by simply clicking and dragging over what it is you do want summed.

In Microsoft XP you will see a down arrow beside the AutoSum button. [pic]

This will display a menu which allows you to Average as well, which works exactly the same way as AutoSum. You can also quickly display the count of numbers, Max, and Min.

Using AutoCalculate

The AutoCalculate feature performs a simple calculation on a selected range of cells without making you supply a formula. The result of the calculation appears on the status bar. AutoCalculate results are temporary and are not placed in the worksheet.

The AutoCalculate functions are accessed by right-clicking on the status bar once you have selected your range.

[pic]

Using Absolute References

Cell references are used to create formulas, so that any data changes will automatically update the formula data. One thing to note about cell references is the formula changes to correspond with the next row or column of information. For example, =a1 + a2 changed to =b1 + b2 if you copied the formula to row b(a relative reference). Sometimes you will have a formula where one cell needs to be used repeatedly and not change as it moves location. You can create a formula with an absolute reference. An absolute reference is a cell that is always used in the formula. You do not want it to change when the formula is copied to another location.

1. Type in a formula as usual. Remember, the equal sign (=).

2. Select the cell reference that needs to be the absolute reference.

3. Press the F4 key. You will see $ signs appear around the row and column references. It is now an absolute reference. Of course, you can type in the dollar signs if you want.

[pic]

Formatting a Worksheet

Once you get your data completely entered into the spreadsheet, you can jazz it up with the formatting features of Excel.

The easiest way to format is using the Formatting Toolbar. First, select the cells you want to format, and then click the icon for the formatting that you want.

You can also use Format in the Menu Bar.

Merge and Center

This feature works great especially for titles or specific subject areas of your spreadsheet. If for example you have the title “Waverly Belmont Teachers” and beneath it you have two columns, one for name and the other for position. You either have to type your title in one of the two columns.

Click and drag over the cells where you would like the title centered, then click the merge and center button [pic] on the formatting tool bar. Much Better!!

Printing a Worksheet

Prior to Printing

To save some trees, there are several features that you can use prior to printing the workbook to ensure that you don’t have to continually reprint it.

Spell Checker

Microsoft Excel has a built-in spell checker. Of course, it is a limited dictionary. More technical terms related to specific jobs may not be in the dictionary, so do not be alarmed if it finds a misspelled word that is spelled correctly.

1. Finish your workbook.

2. Click the Spelling Icon. The entire workbook will be checked.

[pic]

3. You have several options.

[pic]

Print Preview

This feature allows you preview what the paper will look like exactly before you print it. How many times have you printed something and thought, oh I thought it was all on one page, or I thought the margins were smaller than that!

1. Click the Print Preview Icon.

[pic]

2. You will enter Print Preview View. Notice that the menu and toolbars change.

[pic]

3. To get back to Normal View, click Close.

Page Setup

Page Setup gives you some more options for controlling exactly how the page is printed. To get to Page Setup, click File—Page Setup.

Page Tab

[pic]

Margins Tab

[pic]

Header/Footer Tab

[pic]

Sheet Tab

[pic]

Make sure you click OK after you have made changes. If you don’t want to make changes, then click Cancel.

Additional Print Options

By clicking File—Print, you have a few more options than printing the entire document, such as only printing the current worksheet or multiple copies.

[pic]

Introduction to Charts

A graphic display of your worksheet will add visual appeal and make numerical information easier to understand. Excel makes it easy to transfer information from a spreadsheet to a chart.

The Chart Wizard in Excel takes you through the creation process. Simply select the data that you wish to chart, including the row and column labels. The Wizard takes you through four basic steps, in which you will make choices about your chart appearance.

First, select your information you want to chart.

[pic]

Second, go to the Standard toolbar and select the Chart Wizard button

[pic]

Third, walk through the four steps making choices about chart appearance.

1. The first step asks for your chart type, along with sub-types. Then click Next.

[pic]

2. The second step ask what do you want to chart(which you have already done) unless you want to change your mind and also would you like the series in rows or columns. Just switch back and forth to see which you prefer.

[pic]

3. The third step is full of options. Be sure to select each tab to make your changes. Look in the preview box to see those changes.

[pic]

4. The last step asks if you would like the chart on the same sheet or if you want it on its own. When printing, sometimes it is nice to have the spreadsheet info and the chart on the same page to easily refer to.

[pic]

* To create a “super-quick” chart use the F11 key once you have selected your information.

-----------------------

Chart Wizard

Sort Ascending

Spelling and Grammar

Status Bar

Tab Scrolling Buttons

Scroll Bars

Worksheet Tabs

Cell

Formula Bar

Toolbars

Title Bar

Menu Bar

Worksheet

Task Pane

Format Painter

Zoom

Redo

Copy

Print

Email

Open

Sort Descending

Drawing

AutoSum

Insert Hyperlink

Undo

Permissions (Unrestricted Access)

Paste

Cut

Print Preview

Save

New

Office Assistant

ShowxxxHide¶

Research

Increase Decimal

Currency Style

Decrease Indent

Outside Border

Align Right

Font Color

Comma Style

1234569GXœ®¯ÆÇÈäåæôäôäÖÃäô·«Ÿ·–‡}‡j\Bj2[?]?j?[pic]h©˜h¸%>*[pic]B*[?]U[pic]mHnHphÿu[pic]h©˜hôkâ0JmHnHu[pic]$jh©˜hB60JU[pic]mHnHu[pic]h©˜hB65?^J[?]jh©˜hB65?U[pic]^J[?]hB65?CJ0aJ0hÿ]CJ0OJQJaJ0h=c›CJ0OJQJaJ0hB6CJ0OJQJaJ0%jhn5hB6CJ`OJQJU[pic]aJ`jhB6U[pic]mHnHu[pic]jhB6CJ`OJQJU[pic]aJ`hB6CJItalic

Align Left

Font Size

Merge and Center

Decrease Indent

Increase Indent

Highlight

Center

Underline

Bold

Font

Percentage Style

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download