Excel 2007 notes-start to ms excel, Lecture notes of Information and Communications Technology (ICT)

Download Excel 2007 notes-start to ms excel and more Lecture notes Information and Communications Technology (ICT) in PDF only on Docsity!

SPREADSHEET

Introduction to Ms Excel

COMPUTER DEPARTMENT

TABLES OF CONTENTS

CHAPTER 1. CREATING AND NAVIGATING WORKSHEETS

 Creating a Basic Worksheet

 Editing Data

 Navigating in Excel

 Saving Files

 Opening Files

CHAPTER 2. ADDING INFORMATION TO WORKSHEETS

 A Quick Ways to Add Data

 Adding Different Types of Data

CHAPTER 3. MOVING DATA AROUND A WORKSHEET

 Selecting Cells

 Moving Cells Around

 Adding and Moving Columns or Rows

CHAPTER 4.MANAGING WORKSHEETS AND WORKBOOKS

 Worksheets and Workbooks

 Find and Replace

CHAPTER 5. FORMATTING WORKSHEETS

 Formatting Cell Values

 Formatting Cell Appearance

CHAPTER 7. BUILDING BASIC FORMULAS

 Creating a Basic Formula

 Copying Formulas

CHAPTER 8. CHARTS AND GRAPHICS

 Creating Basic Charts

Looking at Interface of ms excel so as to be familiar with Features of ms excel

ELEMENTS OF MS EXCEL

Key terms in ms excel

Workbook Is an file of ms excel that can hold many worksheet Worksheet Is a grid of columns and row where entering data and formula Cells Is the intersection of columns and rows Active Cell Is the selected cell surrounded by border Sheet tabs It displays names of work sheet or sheet Formula bar Displays contents of a cell Name box Displays name of a cell Columns Is vertically arrangement of data, in ms excel column denotes by letter example Column A, Column B Rows Is horizontal arrangement of data, in ms excel rows denote by number

The Excel 2000 Standard and Formatting toolbars includes buttons for formatting data and cells.

The Name box indicates which cell is selected.

The Formula box shows the data in the cell.

Column

Row

Use these buttons to keep track of worksheets in a workbook.

Selected cell

Scroll bar

Click the Sheet buttons to move from one worksheet to another./sheet tabs

example Row 1

CELL

Is the intersection of columns and rows

We name cell according to the location of the cell in worksheet

Example: A cell which found at Column A and Row 1 is cell A

MS EXCEL INTERFACE 2007

Ms Excel features and Elements 1

Cell

Entering Simple Numeric and Text Values

An entry that includes only numerals 0 through 9 and certain special characters, such as + - E e ( ). , $ % and /, is a NUMERIC VALUE****. An entry that includes almost any other character is a TEXT VALUE.

The following table lists some examples of numeric and text values.

Numeric Values Text Values 123 Sales 123.456 B- $1.98 Eleven 1% 123 Main Street 1.23E+12 No. 324

EDITING DATA

Every time you start typing in a cell, Excel erases any existing content in that cell. (You can also quickly remove the contents of a cell by just moving to it and pressing Delete.)

To Edit Cell Data

1. Move to the cell you want to edit.

Use the mouse or the arrow keys to get to the correct cell

2. Put the cell in edit mode by pressing F2 or Double Click A Cell

3. Complete your edit.

NAVIGATING IN EXCEL

(Moving from one cell to another)

There are a number of ways to move around in a workbook. Moving from one cell to another in Excel is quick and easy. The ways to move from cell to cell include clicking a cell or using the Go To command, the scroll bars, the arrow keys, or the HOME, END, PAGE UP , and PAGE DOWN keys.

 Use the arrow keys on the keyboard. Keystrokes move you one cell at a time in any direction.  Click the cell with the mouse. A mouse click jumps you directly to the cell you've clicked.  To select any cell, click it. For example, click cell A1.  To move one cell to the right, press TAB , or to move one cell to the left, press SHIFT + TAB.  To move one cell down, right, up, or left, use the arrow keys.  To move to the uppermost-left cell, A1 ; press CTRL + HOME.  To move to any cell, on the Edit Menu, click Go To and then type any cell number (for example, J18 ).  To move down in the worksheet, press PAGE DOWN.  To move up in the worksheet, press PAGE UP.

 To move to the first column of the worksheet, press HOME.

SAVING FILES

Step to save Workbook:

Save As. This choice allows you to save your spreadsheet file with a new name

Select Office Button > Save or Save As This time, simply choose Save Select My Documents as the location to save This is the default location to save This is the best choice to save all of your files as it is easy to back up this folder You can also make folders within the My Documents folder for better organization

Shortcut Keys for Moving Around a Worksheet

Key Combination Result

(or Tab) (^) Moves one cell to the right.

(or Shift+Tab) Moves one cell to the left.

Moves one cell up.

(or Enter) Moves one cell down.

Page Up

Moves up one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows up (unless you are already at the top of the worksheet).

Page Down Moves down one screen. Thus, if the grid shows 10 cells at a time, this key moves to a cell in the same column, 10 rows down.

Home Moves to the first cell (column A) of the current row.

Ctrl+Home Moves to the first cell in the top row, which is A1.

Ctrl+End (or End, Home)

Moves to the last column of the last occupied row. This cell is at the bottom-right edge of your data.

To create a custom list, follow these steps:

1. Choose Office button Excel Options.

The familiar Excel Options window appears.

2. Choose the Popular section, and then click Edit Custom Lists.

Here, you can take a gander at Excel's predefined lists, and add your own

3. In the "Custom lists" box on the left side of the dialog box, select NEW LIST
4. in the "List entries" box on the right side of the dialog box, type in your list
5. Click Add to store your list.
6. Click OK to close the Custom Lists dialog box, and OK again to close the Excel Options window.

AutoFill

AutoFill is a quirky yet useful feature that lets you create a whole column or row of values based on just

one or two cells that Excel can extrapolate into a series

Bottom line : AutoFill is a great tool for generating simple lists

To use AutoFill, follow these steps:

1. Fill in a couple of cells in a row or column to start off the series
2. Select the cells you've entered so far. Then click (and hold) the small black square at the bottom- right corner of the selected box.
3. Drag the border down (if you're filling a column of items) or to the right (if you're filling a row of items).

CHAPTER 3. MOVING DATA AROUND A WORKSHEET SELECTING CELLS

First things first: before you can make any changes to an existing worksheet, you need to select the cells you want to modify

Making Continuous Range Selections Simplest of all is selecting a continuous range of cells. A continuous range is a block of cells that has the shape of a rectangle

Step to Select Continuous Range

Click the top-left cell you want to select. Then drag to the right (to select more columns) or down (to select more rows)

As you go, Excel highlights the selected cells in blue. Once you've highlighted all the cells you want, release the mouse button

3. Move to the new location in the spreadsheet where you want to paste the cells.
4. Paste the data by selecting Home Clipboard Paste (or press Ctrl+V or Enter on the keyboard).

NB: The cut-and-paste and copy-and-paste operations let you move data from one cell (or group of cells) to another

Adding and Moving Columns or Rows

Inserting Columns

To insert a new column, follow these steps:

1. Select the column immediately to the right of where you want to place the new column.

That means that if you want to insert a new, blank column between columns A and B, start by selecting the existing column B. Remember, you select a column by clicking the column header.

2. Choose Home Cells Insert Insert Sheet Columns.

Inserting Rows

Inserting rows is just as easy as inserting new columns. Just follow these steps:

1. Select the row that's immediately below where you want to place the new row.

That means that if you want to insert a new, blank row between rows 6 and 7, start by selecting the existing row 7. Remember, you select a row by clicking the row number header.

2. Choose Home Cells Insert Insert Sheet Rows.

Excel inserts a new row, and all the rows beneath it are automatically moved down one row

Deleting Columns and Rows

Delete Column

If you select a column by clicking the column header, you can either clear all the cells (by pressing the Delete key), or remove the column by choosing Home Cells Delete.

Delete Row

Step: Select entire row then Home Cells Delete or Right Click Select Delete

Adjusting Column width& Row height

The most straight forward way to create a worksheet is to design it has a table with heading for each column

First type Worksheet Title, Column and Row Headings

Manual Adjusting Column width

To adjust column so as to make text fit in a column

Step to adjust column

To manually resize a column, position the mouse on short line that separate a column header from its right neighbor

Then Click and drag left or right until the small box display desire width or

Automatically resizing Column width

On ribbon, click Home in cell Section, Format and click Auto fit Column width

Or

Double click it and it will Auto fit

Manual Adjusting row width

To adjust Row Height do the following

Select the row dividers and drag up and down to size the row Height

Automatically resizing Row Height

On ribbon, click Home in cell Section, Format and click Auto fit Row Height

Worksheet Title

Column headings

To search data in Worksheet

1. Move to the cell where you want the search to begin.
2. Choose Home Editing Find & Select Find, or press Ctrl+F.

The "Find and Replace" window appears, with the Find tab selected.

3. In the "Find what" combo box, enter the word, phrase, or number you're looking for.
4. Click Find Next.

CHAPTER 5. FORMATTING WORKSHEETS

There are really two fundamental aspects of formatting in any worksheet:

 Cell appearance. Cell appearance includes cosmetic details like color, typeface, alignment, and borders. When most people think of formatting, they think of cell appearance first.  Cell values. Cell value formatting controls the way Excel displays numbers, dates, and times. For numbers, this includes details like whether to use scientific notation, the number of decimal places displayed, and the use of currency symbols, percent signs, and commas. With dates, cell value formatting determines what parts of the date are shown in the cell, and in what order.

Formatting Cell Values

To Format Cell value (Data hold in Cells)

To format a cell's value, follow these steps:

1. Select the cells you want to format.

You can apply formatting to individual cells, or to a collection of cells

2. Select Format Cells, or just right-click the selection, and choose Format Cells.

The Fraction format displays your number as a fraction instead of a number with decimal places

Scientific

The Scientific format displays numbers using scientific notation, which is ideal when you need to handle numbers that range widely in size (like 0.0003 and 300) in the same column

Text

Formatting Dates and Times

To format dates and times, in the Format Cells dialog box

Choose Date or Time from the column on the left and then choose the format from the list on the right

Custom Formats

There are some situations when you want to format numbers in a specialized way that Excel just doesn't

expect.

Creating a custom format

Here's the easiest way to apply a custom format:

Select the cells you want to format

Select Format Cells, or just right-click the selection, and choose Format Cells

1. Choose a format that's similar to the format you want to use.

For example, if you want to apply a custom date format, begin by selecting the Date number format and choosing the appropriate style. If you want to apply a custom currency format, begin by selecting the Currency number format and specifying the appropriate options (like the number of decimal places).

2. At the bottom of the Category list, click Custom.
3. Enter your custom string.

Type your custom string into the box below the Type label

Example type "0521-"

1. Click OK to commit your changes

Formatting Cell Appearance

Formatting cell values is important since it helps maintain consistency among your numbers. But to really make your spreadsheet readable, you're probably going to want to enlist some of Excel's tools for controlling things like alignment , color , and borders and shading.

To format the appearance of a cell, first select the single cell or group of cells that you want to work with,

and then choose Format Cells from the menu, or just right-click the selection and choose Format Cells. The Format Cells dialog box that appears is the place where you adjust your settings.

Alignment and Orientation

Excel lets you control the position of content between a cell's left and right borders, offers the following choices

 General. General is the standard type of alignment; it aligns cells to the right if they hold numbers or dates and to the left if they hold text  Left (Indent). Left indicates that Excel should always line up content with the left edge of the cell  Center. Center indicates that Excel should always center content between the left and right edges of the cell.  Fill. The Fill setting copies content multiple times across the width of the cell, which is almost never what you want.  Justify. This is the same as Left if the cell content fits on a single line

Look below Diagrams