EXCEL tutorial group and outline


Télécharger EXCEL tutorial group and outline

Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours



★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

Télécharger aussi :


EXCEL MANUAL

Course Outline

Subject Title:     BUSCOM 122     (Business Computer Software Operation II - Spreadsheet)

Units: __6__                                                   Lec: 3 Hrs/Week                                  Lab: 3 Hrs/Week

Course Description:       This subject covers data organization and report generation using the so-called “Electronic Spreadsheet”. The subject covers how to use the software, keyboard familiarization worksheet commands, computations using predefines functions and user prepared equations, creating, displaying and printing of graphs and database management.

Course Requirements:   The student must pass the required classroom recitation, quizzes, periodic          examination, and laboratory.

Class Standing                      - 20%

Quiz                          - 25 %

Periodic Exam                      - 25%

Laboratory             - 30%

Total - 100% (Passing Grade is 75%)

General Objectives:       Upon completion of the subject, students can perform fundamental operations such as creating, modifying, enhancing and producing

spreadsheets, use spreadsheet applications to solve business problems, create pie, bar, 3D and line graphs, use analytical functions, automating spreadsheets with macros, analyzing data with data tables, database functions and graphics, exploring in Advanced What-if alternatives and importing/exporting files.

Specific Objectives:                                     At the end of the course, students will be able to:

1.    define Microsoft Excel;

2.    search for help by typing a question in the help window;

3.    explain the excel environment;

4.    use shortcut keys;

5.    define workbook and worksheets;

6.    create a new workbook;

7.    set the default working folder in the Open and Save As dialog boxes;

8.    open a workbook;

9.    open several workbook at once;

a workbook as read-only;

a workbook automatically when you start Excel;

a back up copy of a workbook;

or hide the list of recently used workbooks on the File menu;

the file properties to locate files;

properties to make a workbook read-only;

and preview files;

17.create and use workbook templates;

workbook;

19.close workbook;

a workbook;

21.insert a new worksheet;

and scroll through a worksheet;

23.select sheets in a workbook;

24.display more or fewer tabs;

or copy sheets;

a sheet;

27.display a hidden sheet;

28.delete sheets from a workbook;

29.rename a sheet;

a row or column;

31.create links to connect data and files;

32.change the defaults for new workbook and worksheets;

33.change what you see in the screen;

34.arrange workbook windows;

35.enter data in worksheet cells;

36.cancel or undo entry;

37.select and move around in a workbook;

cell contents;

39.clear or delete cells, rows, or columns;

and replace data;

41.insert cells and data;

or copy cell data;

only values, formulas, comments or cell formats;

44.switch rows of cells to columns or columns to rows;

45.collect and past multiple items

46.check spelling and correcting typing errors;

formats from one cell or range to another;

48.change the font size, font, color, or other text format;

49.shrink the font size to show all data in a cell;

50.change the text color;

selected text or numbers bold, italic or underlined;

52.apply and remove borders to cells;

53.shade and remove cells with solid colors;

and remove background patterns to an entire sheet;

55.change, add or remove conditional format;

56.change column width and row height;

57.merge cells to span several columns or rows;

58.split a merged cell into separate cells;

59.center, align, indent or rotate data in a cell;

60.format numbers, dates and time;

61.increase or decrease the number of decimal places shown;

numbers as a postal code, social security number or phone number;

numbers as fractions or percentages;

or hide the thousands separator in a number;

or remove a currency symbol

66.display zeroes and hide values;

print options;

68.change the print area, layout and page breaks;

69.change the headers and footers;

70.print the active sheets, a selected range or an entire workbook;

71.create an on-screen or printed form;

72.create forms for data entry;

buttons, check boxes and other controls;

74.create a form template that copies worksheet data to a database;

75.create formulas and auditing workbooks; 76.create a chart;

77.change and add text and data in a chart;

78.change the display, view or placement of a chart;

79.change the type of a chart;

80.format data markers, labels and other chart items; and create drawings and importing pictures.

INTRODUCTION

Ø   Microsoft Excel (full name Microsoft Office Excel) is a spreadsheetapplication written and distributed by Microsoftfor Microsoft Windowsand Mac OS X. It features calculation, graphing tools, and a macro programming language called VBA (VisualBasic for Applications)

Ø   Microsoft Excel has the basic features of all spreadsheets, using a grid of cells arranged in numbered rows and letter-named columns to organize data manipulations like arithmetic operations. It has a battery of supplied functions to answer statistical, engineering and financial needs. (Wikipedia)

Ø   a software program that allows the easy analysis and manipulation of data using tables and formulas.

Ø   a spreadsheet program that can perform numerical calculations and bookkeeping tasks.

Ø   Because all sorts of calculations can be made in the Excel spreadsheet, it is much more flexible than a paper spreadsheet.

Excel is a computerized spreadsheet, which is an important business tool that helps you report and analyze information.

A sample Excel worksheet

 

Using the Help System

 Excel components

The Excel window has some basic components, such as an Active cell, Column headings, a Formula bar, a Name box, the mouse pointer, Row headings, Sheet tabs, a Task Pane, Tab scrolling buttons and Toolbars.

   

MS EXCEL Shortcut Keys

The following lists contain CTRL combination shortcut keys, function keys, and some other common shortcut keys, along with descriptions of their functionality.

•    CTRL+A                 Selects the entire worksheet.

•    CTRL+B                 Applies or removes bold formatting.

•    CTRL+C                 Copies the selected cells.

•    CTRL+D                Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.

•    CTRL+F Displays the Find dialog box.

•    CTRL+G and F5 Displays the Go To dialog box.

•    CTRL+H                Displays the Find and Replace dialog box.

•    CTRL+I Applies or removes italic formatting.

•    CTRL+N                Creates a new, blank file.

•    CTRL+O                Displays the Open dialog box to open or find a file.

•    CTRL+P                 Displays the Print dialog box.

•    CTRL+R                 Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.

•    CTRL+S Saves the active file with its current file name, location, and file format.

•    CTRL+U                Applies or removes underlining.

•    CTRL+V                 Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you cut or copied an object, text, or cell contents.

•    CTRL+W               Closes the selected workbook window.

•    CTRL+X                 Cuts the selected cells.

•    CTRL+Y Repeats the last command or action, if possible.

•    CTRL+Z Uses the Undo command to reverse the last command or to delete the last entry you typed.

•    F1           Displays the Help task pane.

•    F4           Repeats the last command or action, if possible.

•    F6           Switches to the next pane in a worksheet that has been split (Window menu, Split command).

•    F7           Displays the Spelling dialog box to check spelling in the active worksheet or selected range.

•    F12        Displays the Save As dialog box.

•    ARROW KEYS     Move one cell up, down, left, or right in a worksheet.

•    BACKSPACE Deletes one character to the left in the Formula Bar. Also clears the content of the active cell.

•    DELETE                 Removes the cell contents (data and formulas) from selected cells without affecting cell formats or comments.

•    ENTER   Completes a cell entry from the cell or the Formula Bar, and selects the cell below (by default).

•    ESC        Cancels an entry in the cell or Formula Bar.

•    HOME   Moves to the beginning of a row in a worksheet.

•    PAGE DOWN      Moves one screen down in a worksheet.

•    PAGE UP              Moves one screen up in a worksheet.

•    SPACEBAR In a dialog box, performs the action for the selected button, or selects or clears a check box.

•    TAB        Moves one cell to the right in a worksheet.

Workbooks and worksheets

When you start Excel, you open a file called a workbook. Each new workbook comes with three worksheets, like pages in a document. You enter data into the worksheets.

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. 

1.    The first workbook you open is called Book1 in the title bar at the top of the window until you save it with your own title.

2.    Sheet tabs are at the bottom of the workbook window.

•    It’s a good idea to rename the sheet tabs to make the information on each sheet easier to identify.

•    You can add additional worksheets if you need more than three. Or if you don’t need as many as three, you can delete one or two (but you don’t have to).

 

You may be wondering how to create a new workbook if you’ve already started Excel. Here’s how: On the File menu, click New. In the New Workbook task pane, click Blank workbook.

Renaming Worksheets

Step 1 - Launch Excel - Automatically a workbook opens. An Excel workbook is made of several sheets. Unless you have changed the default setting you will have three sheets.

 

Step 2 - Changing the name of the worksheets - Each sheet is a full spreadsheet. Having a workbook with multiple sheets allows you to easily establish relationships from one sheet to another.

The name of the sheet may be changed. Double-click the name of the sheet in the tab at the bottom, and type the new name.

 

Step 3 - Inserting additional worksheets - If you need additional worksheets, go to the Insert menu and select Worksheet . It will be placed before the selected sheet.

You may also insert a worksheet by right-clicking on the name of the sheet in the tab and selecting Insert.

 

As you can see from the graphic above, renaming the worksheet can also be accomplished by right-clicking the tab containing sheet names.

Columns, rows, and cells

Columns, rows, and cells: That’s what worksheets are made of, and 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 place where one column and one row meet.

Columns and rows have headings:

1.    Each column has an alphabetical heading at the top.

2.    Each row has a numeric heading.

The first 26 columns have the letters from A through Z. Each worksheet contains 256 columns in all, so after Z the letters begin again in pairs, AA through AZ, as the picture

shows.

Row headings go from 1 through 65,536.

Cells are where the data goes

Cells are where you get down to business and enter data in a worksheet.

 The active cell is outlined in black.

For example, if you select a cell in column C on row 5:

1.    Column C is highlighted.

2.    Row 5 is highlighted.

3.    The active cell is shown in the Name Box in the upper-left corner of the worksheet.

The selected cell has a black outline and is known as C5, which is the cell reference.

You can see the cell reference of the active cell by looking in theName Box in the upper-left corner.

 

Cells - Rectangles in a spreadsheet are called a cell. Cells are designated by the column and row in which it is located. By default, the top left cell, A1, is highlighted.

Types of Cell Entries

•    Constant – an entry that does not change

? Can be a numeric value or descriptive text

•    Function – a predefined computational task

•    Formula – a combination of numeric constants, cell references, arithmetic operators, and functions

? Always begins with an equal sign

Try the following ways to move from cell to cell:

•    Hit the Return/Enter key to move down to the row below

•    Hold down the Shift key, then hit the Return/Enter key to move up to the row above

•    Hit the Tab key to move over to the column to the right

•    Hold down the Shift key, hit the Tab key to move back to the column to the left

•    Move the cursor to any cell and click there

•    Use the arrow keys to move up, down, left, or right.

Recognizing cursor styles - There are four common cursor styles used in Excel.

 

Click and drag to highlight multiple cells with this cursor, or click in a cell to select the single cell

   

Click and drag with this cursor to fill cell contents into cells below or to the right.

         
 

Click and drag the contents of the selected cell to any other cell.

   

Click to place the cursor into the Formula bar so that you can edit an equation or function.

Identify cell ranges

•    A group of worksheet cells is known as a cell range, or range.

•    Working with ranges in a worksheet makes working with the data easier.

•    Ranges can be adjacent or nonadjacent.

Ø   An adjacent range is a single, rectangular block of cells

Ø   Select an adjacent range by clicking on a cell and dragging to an opposite corner of a rectangle of cells

Ø   A nonadjacent range is comprised of two or more adjacent ranges that are not contiguous to each other

To select a nonadjacent range, begin by selecting an adjacent range, then press and hold down the Ctrl key as you select other adjacent ranges

Using Cell Ranges

•    Range – a rectangular group of cells

Ø   May be a single cell or the entire worksheet

Ø   May consist of a row (or part of a row), a column (or part of a column) or multiple rows and/or columns

•    To select a range:

Ø   Click left mouse button at the beginning of the range

Ø   Hold left mouse button as you drag the mouse

Ø   Release left mouse button at the end of the range

Adjacent and nonadjacent ranges

 

Entering data - Move to the cell where you want to enter data and enter words or numbers. If data is already in the cell it will be replaced without having to cut or delete the previous data.

Entering and Editing Data

Use these steps to easily enter and edit data in cells. Learn to identify the various cursors in MS Excel with this cursor identification Chart.

Step 1 - Cell data - Things that can be entered into a cell:

•    numbers

•    words

•    equations, formulas or functions

•    fill color

•    images (although they are actually on top of a cell, not in it)

Step 2 - Entering data - Move to the cell where you want to enter data and enter words or numbers. If data is already in the cell it will be replaced without you having to cut or delete the previous data.

Navigation keystrokes

 

*Use the Alt+Enter key combination to enter text on multiple lines within the same cell.

Using the Standard Toolbar with an Excel Worksheet The Standard toolbar

 

This entire toolbar could become a floating window by double-clicking on the control bar at the far left end of this toolbar. That gives the following window, which can be placed anywhere on the screen:

 

This toolbar can be restored to its original position by clicking in the gray bar at the top and dragging it back to the top of the screen. Push the top of the window up to the bottom of the menu bar.

Function of commonly used buttons

 

Creates a new blank document based on the default template

 

Opens or finds a file

 

Saves the active file with its current file name, location and file format

 

Prints the active file - for more print options go to the File menu and select Print

 

Print preview - Shows how the document will look when you print it.

 

Spelling, grammar and writing style checker

 

Cut - Removes the selection from the document and places it on the clipboard

 

Copy - Copies the selected item(s) to the clipboard

 

Paste - Places the content of the clipboard at the insertion point

 

Format painter - Copies the format from a selected object or text and applies to other objects or text

 

Undo - Reverses the last command, use pull-down menu to undo several steps

 

Redo - Reverses the action of the Undo button, use the pulldown menu to redo several steps

 

Auto Sum - Adds numbers automatically, and suggests the range of numbers to be added

 

Sort Ascending - Sorts selected items from the beginning of the alphabet, the lowest number or the earliest date

 

Sort Descending - Sorts selected items from the end of the alphabet, the highest number or the latest date

 

Chart Wizard - Guides you through the steps for creating an embedded chart (graph)

 

Displays or hides the Drawing toolbar

 

Zoom - Enlarge or reduce the display of the active document

Excel - Using the Formatting Toolbar The Formatting toolbar

 

This entire toolbar could become a floating window by double-clicking on the control bar at the far left end of this toolbar. That gives the following window, which can be placed anywhere on the screen:

 

This toolbar can be restored to its original position by clicking in the gray bar at the top and dragging it back to the top of the screen. Push the top of the window up to the bottom of the menu bar.

Function of commonly used buttons

 

Changes the font of the selected text

 

Changes the size of selected text and numbers

 

Makes selected text and numbers bold

 

Makes selected text and numbers italic

 

Underlines selected text and numbers



 

Aligns to the left with a ragged right margin

 

Centers the selected text

 

Aligns to the right with a ragged left margin

 

Merge and Center - Merges two or more selected cells and centers the entry

 

Currency Style - Formats selected text to display currency style

 

Percent Style - Formats selected cells to display percent

 

Comma Style - Formats selected cells to display commas in large numbers

 

Increase Decimal - Increases the number of decimals displayed after the decimal point

 

Decrease Decimal - Decreases the number of decimals displayed after the decimal point

 

Decreases the indent to the previous tab stop

 

Indents the selected paragraph to the next tab stop

 

Adds or removes a border around selected text or objects

 

Marks text so that it is highlighted and stands out

 

Formats the selected text with the color you click

   

The Drawing toolbar

 

This entire toolbar could become a floating window by double-clicking on the control bar at the far left end of this toolbar. That gives the following window, which can be placed anywhere on the screen:

 

This toolbar can be restored to its original position by clicking in the gray bar at the top and dragging it back to the top of the screen. Push the top of the window up to the bottom of the menu bar.

Function of commonly used buttons

 

A pull down menu with several drawing options

 

Changes the pointer to a selection arrow

 

Rotates the selected object to any degree

 

A pull down menu with several libraries of shapes

 

Draws a line where you click and drag. Hold the Shift key down to make the line straight

 

Inserts a line with an arrowhead where you click and drag

 

Draws a rectangle where you click and drag. Hold down Shift to draw a square

 

Draws an oval where you click and drag. Hold down Shift to draw a circle

 

Draws a text box where you click and drag

 

Create text effects with Word Art

 

Add, modify, or remove fill color from a selected object

 

Add, modify, or remove line color

 

Formats the selected text with the color you click

 

Changes the thickness of lines

 

Selects dash style for dashed lines

 

Select arrow style; placement and shape of arrowhead

 

A pull down menu offers shadow choices

 

Add 3-d effects to rectangles or ovals

The most commonly used items from the File, Edit and View menus will be explained.

File menu

New - Opens a new document. If you use the keyboard combination indicated on the right a blank document opens immediately. Selecting the New menu item with your cursor gives the opportunity to open a large number of types of documents. Open - Opens a previously saved document.

Close - Closes the active document but does not quit the application.

Save - Saves the active document with its current file name, location and format.

Save As - Saves by opening a window which gives the opportunity to change the file name, location or format.

Page Setup - Sets margins, paper size, orientation and other layout options. Grid lines don't show up when you print? Go to the sheet tab in this window Print Preview - Shows how the file will look when you print it.

Print - Prints the active file, also gives the opportunity to change print options

Exit - Closes Microsoft Excel

Edit menu

Undo - The actual entry of this item will depend on what you did last. In my example I had typed, so that was displayed. This selection can be repeated several times.

Redo - After an action has been undone, it can be reinstated in the document.

Cut - Removes the selection from the active

document and places it on the clipboard. Copy - Copies the selection to the clipboard, the cell from which information is copied remains highlighted

Paste - Inserts the contents of the clipboard at the insertion point (cursor) or whatever is selected.

Paste Special - Ten choices are available when making this selection; examples include pasting formulas, vqalues, and comments.

Fill - Fill contents of a selected cell Up, Down, Left or Right

Clear - Deletes the selected object or text, but does not place it on the clipboard. Four choices are available; All, Format, Contents or Comments

Delete - This menu entry can be used to delete antire rows or columns.

Find - Searches for specified text in the active document

Replace - Searches for and replaces specified text and formatting.

View menu

Normal - The default document view for most word processing tasks.

Page Break Preview - Before printing, make sure the page breaks appear where you want them Toolbars - Displays or hides toolbars. The right pointing arrow indicates a list of toolbars. To add one slide down to the name of the toolbar and click to select.

Formula Bar - Remove or display the bar which displays cell address and data entered into the active cell

Header and Footer - Adds or changes the text that is displayed at the top or bottom of every page of the document

Comments - Hidden comment give further information about cell contents. Comments are displayed when you move the cursor into the cell, however this command causes all comments on a page to be displayed. Full Screen - Hides most screen elements so you can see more of your document

Zoom - Controls how large, or small, the current document appears on the screen.

Using Insert, Format and Font Menu items in Microsoft Excel

Insert menu

Cells - Use this command to insert a cell. A pop-up window allows you to move existing data down or to the right. You can also insert rows or columns with this window.

Rows - Inserts a new row in the spreadsheet, above the row that contains the active cell.

Columns - Inserts a new column in the spreadsheet, to the left of the column that contains the active cell.

Worksheet - By default, an Excel workbook is made up of three worksheets. You may insert as many additional sheets as you require. Sheets are inserted in front of the current worksheet.

Chart - This adds a chart of the selected data, or of the entire worksheet if you have no data selected.

Page Break - Inserts page breaks above and to the left of the active cell. To avoid adding a page break to the left, make sure a cell in Column A is selected before inserting the break.

Function - Opens the Paste Function window allowing the selection of a specific equation to go in the active cell.

Comment- Have something to say about the contents of a cell? Add a small note with the appearance of a Post-It note.

Picture - Insert pictures from clip art or a file. You can also insert auto shapes, word art, or a chart.

Object - Insert an object such as clip art, word art, an equation or much more.

Hyperlink - An interesting use of hyperlinks is to place a link to any document stored on your computer. You can later open that document by clicking on the link. If you want to see an example of an Excel worksheet using, download a copy and open it with Excel.

Format menu

Cells - Format the way a number is displayed, alignment of data in the cell(s), font (size, color, style, etc.), borders and colors for the selected cells, and you may also lock the contents of a cell here.

Row - Specify a row height, choose auto-fit, and hide or un-hide the selected row.

Column - Specify a column width, choose auto-fit, hide or un-hide the selected column, or choose the standard width for a column.

Sheet - Here you can rename the sheet if sheet 1 is not descriptive enough (and it's not), you can hide or un-hide a sheet, or you can tile an image in the background of the entire sheet.

Auto Format- There are sixteen pre-designed formats to change the look of your spreadsheet. You may apply the format to the entire sheet or only to selected cells.

Style - Opens a Style window which will lead to the Format Cells window if you wish to Modify the format.

Tools menu

Spelling - Check spelling in your document AutoCorrect - Define, or turn off automatic corrections. If you tend to make a particular keystroke error often, and it is not in the dictionary, you can add it here.

Share Workbook - Choose this option if you want several users to work on the data in the same workbook simultaneously. Make it available on your network and anyone with access can make changes.

Protection - You can password protect a

single sheet of a workbook, or the entire workbook. One obvious application would be to protect a worksheet which you are using as your grade book.

Customize - Select/Deselect toolbars to be displayed with your workbook, or add buttons to existing toolbars.

Options - If you do not like something Excel does automatically, or if you know it should be doing something automatically and it is not, this is where you turn those features on or off. One thing I use a lot here is removing gridlines from a worksheet so I can draw a graphic organizer.

Format data - Once information has been entered into a cell, you might want to change something about the way the information is displayed. To do that, make sure the cell you want to format is selected and go to the Format menu. Select Cells The number I entered repeats infinitely, but I only need 2 decimal places.

 

You can bring up the Format Cells dialog box by right-clicking on the cell you want to format.  From the following pop-up box select Format Cells

 

Using formatting buttons - There are several toolbar buttons which allow one-click formatting. To illustrate I entered the same number in four cells, with the formatting I plan to use for each cell.

 

I selected the Increase Decimal button for cell C1

The Decrease Decimal button was used to format cell D1 These four actions produced the following result:

 

As you can see, the Percent button simply multiples by 100 and attaches the % sign. Care must be exercised in using this button properly.

Other formatting options - The Format toolbar includes several formatting options which can be applied to information entered into a cell.

 

Editing entered data - If incorrect information has been entered into a cell, one easy way to edit that is to click on the cell and enter the correct information. You do not need to highlight or delete. Typing replaces whatever is in the cell.

If you do not want to replace the data, but simply need to correct some part of it, move your cursor into the Formula bar.

 

When your cursor turns into an I-Beam, click and edit within the formula bar.

Page setup and printing

Excel allows you to print your document as well as to add additional page layout options to your presentation. You can add a header or footer to a page, change the margins as well as several other options.

Page setup

The options of the page layout exist to improve the presentation of your document on paper. You can change the margins of sheets, add the headers, the footers and several other option.

 

•    From the File menu, select the Page setup option.

•    Under the Page tab, in the section Orientation, Excel asks you for the orientation of the pages to print.

•    In the scaling sections, you can manually change the size of your spreadsheet by reducing or increasing it. This is very practical when all the columns of you're worksheet should be on one page only. You can also ask Excel to find automatically the best size to enter your document on to X pages in widths and Y pages in height. At any time, you can look at the printout before printing by pressing the review button. You can also change the type of paper (letter size, legal, newspaper ) as well as the quality of the printing.

Margin

Under the Margins tab, you can determine the margins, the width between the border of the page and your text, for the file as well as those for the header and the footer of the page. You can also choose to center horizontally and vertically your worksheet on the page. You can also determine the place for the header and the footer for the worksheet your about to print. The outline in the middle of the window gives you an idea of the effect of these choices on the paper.

Header and Footer

Under the Header/Footer tab, you can determine what will be in the header and the footer of each of the pages of the printout. If you don't want a header or a footer, select the first option, "none", from the list of the predetermined options.

         •              Personalize the header and footer.

It's sometimes useful to add a header or a footer to a document. It helps to identify the document and what it's supposed to represent. For example, a header with the title "Monthly revenues and expenses for May 2006" says it all.

The next exercise consists in writing the name of the document, your name, as well as the date and the hour of the printout.

Printing Headers and Footers

 

•    Under the Header/Footer tab, press the Custom Header button.

•    The options to personalize the header will appear as above. They are the same options as for the page footer. In the middle of the window, there is a series of buttons. They are the options most often used. The section at the bottom is separated into three boxes. The left box will contain the text that will be written on the left side of the page. The box in the middle will contain the text that will be in the middle of the header and so on.

Print a workbook

•    To Print a worksheet, you can use:

Ø   A menu

Ø   The Print button on the standard toolbar

Ø   The Ctrl-P keystroke to initiate a printout of the worksheet

•    Excel uses the same basic methods for printing as other Windows and Microsoft Office applications.

The Print dialog box

 

The Printing Zone

•    Besides allowing you to print your entire spreadsheet, Excel allows you to print a part of your worksheets. It is however necessary to determine in advance the printing zone that you need. There are several ways to carry out this task.

•    From the File menu, select the Printing zone and Define options. Make a range of cells with the area that you need to print.

OR

From the File menu, select the Page Setup option.

Select the Sheet tab.

Click in the Print area box.

Select the range of cells that you need to print.

While pressing on the Ctrl key, you can select several zones to print at the same time. However, every zone will be printed on a different page.

•    The window offers you several options. In the Printer section, you can choose the type of printer on which your document will be printed.

•    In the Print range section, you have the options to print your entire document or only some pages of you're file. This is very practical when you need to reprint a few pages after a correction.

•    In the Print what section, Excel offers you to print the block that you selected first, to print the worksheet where the cursor is located or to print all the worksheets of your file that contains a number, text or a formula.

In the Copies section you can choose the number of copies that Excel will print.

The Print Preview Command

Using an Excel worksheet -Automatic features

Learn to use these automatic features of Microsoft Excel such as the common cursor styles, auto sum, and auto fill.

Step 1 - Review cursor styles - There are four common cursor styles used in Excel. If you wish to use the automatic features of Excel, you should become familiar with each style.

 

Click and drag to highlight multiple cells with this cursor, or click in a cell to select the single cell

Click and drag with this cursor to fill cell contents into cells below or to the

right.

Click and drag the contents of the selected cell to any other cell.

Click to place the cursor into the Formula bar so that you can edit an

equation or function.

Step 2 - Using Auto fill - If there is a pattern in the data you are going to enter, Excel can detect the pattern and fill it in for you. In Cell B1 I typed Group 1.

 

Place the cursor on the bottom right of cell B1 and a black plus sign appears. Click and drag to the right to cell E1. Excel detects the pattern and fills in the other groups.

The image below illustrates some other patterns, and a problem with this procedure:

In row 3 the procedure did not work because there are too many possibilities. In cell B6 I typed the 2, highlighted both cells B4 and C4, clicked and dragged to the right. Now that Excel knew the pattern it could fill in the cells.

Note : If you are using some of the early versions of Excel, this procedure works in two directions only. You may fill to the right or down . Auto Fill will not fill to the left or up when using earlier versions of Excel. However, Excel XP and 2003 will fill in any direction; right , left , up , or down .

Step 3. Using Auto Sum - Excel allows you to quickly find the total of a column or row of numbers.

Step 1 - Select the cell Step 3 - When you verify

below your column of    Step 2 - Select the            that the range of numbers is numbers (or to the right      Auto Sum button        proper, depress return/enter of your row of numbers). from your Standard         and the sum is displayed. toolbar

Step 4. Problems Using Auto Sum - Excel will automatically do what it is set to do. In this case, the program finds all adjacent numbers in a column, or row, and includes them


in the range.

Step 1 - If there is a gap in the data, Excel will highlight only numbers not separated by an empty cell.

 

Step 2 - Place your cursor in the highlighted equation and click to edit. In the example above I would change A4:A5 to A2:A5

 

You may also click into the equation in the formula bar above the worksheet, and make changes there.


Step 5. Using Merge and Center - For giving a clean design look to your worksheet, consider using Excel's Merge and Center feature. This is a two step process:

1.    Highlight a range of cells

2.    Select the Merge and Center button

3.    If you have data in only one cell, that data will be in the center of one long cell.

 

If you attempt to Merge and Center with data in more than one cell, you will wipe out data in all but the upper-leftmost cell. Don't worry, Excel will warn you!

 

FORMULAS

•     Formulas are entries that have an equation that calculates the value to display. We DO NOT type in the numbers we are looking for; we type in the equation. This equation will be updated upon the change or entry of any data that is referenced in the equation.

Order of Precedence Rules

 

Arithmetic Operators

 

BASIC FORMULAS IN EXCEL

When we are entering formulas into a spreadsheet we want to make as many references as possible to existing data. If we can reference that information we don't have to type it in again. AND more importantly if that OTHER information changes, we DO-NOT have to change the equations.

If you work for 23 hours and make $5.36 an hour, how much do you make? We can set up this situation using

•    three labels

•    two constants

•    one equation

Let's look at this equation in B4:

•    = B1 * B2

•    = 23 * 5.36

Both of these equations will produce the same answers, but one is much more useful than the other.

DO YOU KNOW which is BEST and WHY? It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

CHANGE IN FORMULA

If we referenced the actual cells (instead of typing the data into the equation) we could update the entire spreadsheet by just typing in the NEW Hours worked. And -- you're done!

Let's look at the new spreadsheet: hours have been changed to 34

•    wage is the same

•    total pay would now be = 34 * 5.36

•    but would still be = B1 * B2

If we had typed in ( = 23 * 5.36 ) the first time and just changed the hours worked, our equation in B4 would still be ( = 23 * 5.36 ) INSTEAD we typed in references to the data that we wanted to use in the equation.



We typed in ( = B1 * B2 ). These are the locations of the data that we want to use in our equation.

It is BEST if we can Reference as much data as possible as opposed to typing data into equations.

BASIC MATH FUNCTIONS

•     Spreadsheets have many Math functions built into them. Of the most basic operations are the standard multiply, divide, add and subtract. These operations follow the order of operations (just like algebra). Let's look at some examples. For these following examples let's consider the following data: A1 (column A, row 1) = 5

Adding Up

You've seen how to add up in Excel. You either used individual cells to add up, like

this:

= A1 + A2 + A3 + A4

Or you have used a range of cells with the Sum function. Like this:

= Sum(A1:A4)

Either way you get the same answer. But you can combine the two to add up. For example, if you wanted to add up cells A1 to A4 and cell A10, you'd do it like this: = Sum(A1:A4) + A10

You can also use the Sum function alone. Like this:

= Sum(A1:A4, A10)

Here we have entered a range of cells in the brackets - A1:A4. But after that, we added a comma then the final cell we wanted to add up. If we also wanted to add cells A12 and A14 to our sum, we just add a comma then the cell reference. Like this:

= Sum(A1:A4, A10, A12, A14)

Multiplying

You saw how to multiply two numbers together. Just use the asterisk symbol with the cell references. Like this:

= A1 * A2

But what if you wanted to multiply a long range of values together? You might want to multiply all the numbers in the cells A1 to A10. Surely you don't have do this = A1 * A2 * A3 * A4 * A5, etc?

You'd be right - you don't have to enter all the cell references. You can do the same as in the Sum Function, just use A1:A5. But instead of using the word Sum you use the word Product. Like this:

=Product(A1:A5)

If you look back at Exercise 2, the number 3 was in the cells A1 to E1. We then added them up. If you wanted to multiply them all instead, use Product. To further illustrate what Product does, this is what we wanted to multiply:

= 3 * 3 * 3 * 3 * 3

So 3 times 3 = 9. Multiply the 9 by the third 3 to give 27. Multiply the 27 by the fourth 3 to give 81. Multiply the 81 by the fifth 3 to give 243. And that's what Product does: Multiplies a range of cells together. Just like the Sum function, you can add other cells after a comma. Like this:

= Product(A1:E1, A3)

In the above function, our answer of 243 will be multiplied by whatever is in cell A3. Try using Product yourself with these exercises:

Subtraction

To subtract one value from another, you just use the minus sign in between your cell references. Like this:

= A1 - A2

Below is an image from a spreadsheet showing a subtraction formula:

Cell A3 is where the answer is displayed, and where we entered the formula.

If you want to subtract more than two cells you can do it like this:

= A1 - B1 - C1

Subtraction is fairly straightforward in spreadsheets, and shouldn't cause you too many problems.

Division

If you want to divide one number by another the symbol to use is this one:  /

That's the forward slash, and can be found just to the right of the full stop on your keyboard. You use it like this: = A1 / C1

SPECIFIC FORMULAS OR FUNCTIONS SUM FUNCTION

•    Probably the most popular function in any spreadsheet is the SUM function. The Sum function takes all of the values in each of the specified cells and totals their values. The syntax is:

=SUM(first value, second value, etc)

•    In the first and second spots you can enter any of the following (constant, cell, range of cells). Blank cells will return a value of zero to be added to the total.

Text cells can not be added to a number and will produce an error.

We will look at several different specific examples that show how the typical function can be used! Notice that in A4 there is a TEXT entry. This has NO numeric value and can not be included in a total.

 

AVERAGE FUNCTION

•    There are many functions built into many spreadsheets. One of the first ones that we are going to discuss is the Average function. The average function finds the average of the specified data. (Simplifies adding all of the indicated cells together and dividing by the total number of cells.) The syntax is as follows.

•    =Average (first value, second value, etc.)

•    Text fields and blank entries are not included in the calculations of the Average Function.

 

Max Function

•    Max (which stand for Maximum). This will return the largest (max) value in the selected range of cells.

•    Blank entries are not included in the calculations of the Max Function.

•    Text entries are not included in the calculations of the Max Function.

 

Minimum Function

•    Min (which stands for minimum). This will return the smallest (Min) value in the selected range of cells.

•    Blank entries are not included in the calculations of the Min Function.

•    Text entries are not included in the calculations of the Min Function.

 

Count Function

•    This will return the number of entries (actually counts each cell that contains number data) in the selected range of cells.

•    Blank entries are not counted.

•    Text entries are NOT counted.

 

CountA Function

•    This will return the number of entries (actually counts each cell that contains number data OR text data) in the selected range of cells.

•    Blank entries are not Counted.

•    Text entries ARE Counted.

 

IF Function

•    The IF function will check the logical condition of a statement and return one value if true and a different value if false. The syntax is

•    =IF (condition, value-if-true, value-if-false)

•    value returned may be either a number or text

•    if value returned is text, it must be in quotes

 

AND

Checks whether all arguments are TRUE, and returns TRUE if all arguments are true

Syntax: =AND(logical1, logical2…)

Example: =AND(A4=5, A5=5). If A4 and A5 has a value of 5, it will return TRUE OR

Checks whether any of the arguments are TRUE, and returns TRUE or FALSE. It will only return FALSE if all arguments are false

Syntax: =OR(logical1, logical2…)

Example: =OR(A4=5, A5=5). Will return TRUE if either A4 or A5 has a value of 5

  IF

Checks whether a condition is met, and returns one value if TRUE, and another value if FALSE

Syntax: =IF(argument, value if True, value if False)

Example: =IF(A4=5, “Yes”, “No”). Will return the word Yes if A4 has a value of 5. Otherwise, it will return No.

Function Wizard

•    In Excel there is a help tool for functions called the Function Wizard.

•    There are two ways to get the function wizard. If you look at the Standard Toolbar, the function wizard icon looks like the icon on the right.

•    The other way to get to the function wizard is to go to the Menu INSERT -- down to FUNCTION.

•    Either way you get there, at this point Excel will list all of the functions available. Upon choosing the function, Excel will prompt you for the information it needs to complete the function. Mini descriptions are available for each of the cells. It is often necessary for you to understand the functions in order to be able to figure out these descriptions.

•    It is faster to type the basic function in from the keyboard as opposed to going through the steps of this tool.

Using Functions in Excel

Step 1 - Enter data - One of the principal reasons for using a spreadsheet is to perform calculations. To illustrate, we will begin by adding a column of numbers. This is from a class survey about the favorite ice cream flavor of a class. Caution : If you plan to ask Excel to add a column of numbers, make sure that they are numbers. If cell B2 contained "6 students" rather than just the number 6, Excel would read the entry as a word, not a number.

 

Step 2 - Placing a function - In the example above the total of the column of numbers would naturally go in cell B10. Before placing a function (a built in equation) make sure the selected cell is where you want the function to go.

From the Insert menu select function

In the Paste Function pop-up window, select the Math & Trig category and scroll to the Sum function

 

When you choose OK, the dialog box below appears. There appears to be a lot of information to deal with, but it is basically just a summary of what you asked Excel to do.

 

The edit bar at the top names the function ( Sum ) then lists the mathematical function to be performed ( =SUM(B2:B9) ). Excel selects the range numbers immediately above or to the left of the function. If there is a gap in the intended range you must enter the proper range in the edit bar. Example: In the data listed with Step 1, if cell B4 was empty, Excel's suggested equation would have been incorrect ( =SUM(B5:B9) ).

Choose OK. The function is placed in the cell and the sum is displayed.

 

What is in cell B10? The number 24 is displayed there. If the worksheet is printed the number 24 will be on the printed copy. However, if cell B10 is copied and pasted into another cell something altogether different appears. If you said that the information in cell B10 was "the sum of the range of numbers from B2 to B9." you are almost correct. To explain that almost we will paste the equation into cell C10.

 

The actual information contained in cell B10 is "find the sum of the range of numbers that are in the eight cells above this cell." Since there are no numbers in those cells, the displayed sum is zero.

Step 3. Using AutoSum - Remember that Excel allows you to quickly find the total of a column or row of numbers. If you forgot that from the previous page, go there to review .

Step 4. Using another function - Excel has many built-in equations (functions). One more will be used as an illustration, and to whet your appetite to discover more on your own.

Once you know how to write your own functions (next topic), Excel can be used as a substitute for a calculator. I am going to prepare a worksheet which will automatically remind me what the date is as I use my "calculator."

 

Cell C2 is where a Date & Time function will be placed. From the Insert menu select Function . Choose Date & Time then select the Today function.

 

Whenever you insert a function, a dialog box pops up to explain the chosen function.

 

No further action is required, select OK and today's date is displayed. Tomorrow when I open this workbook, a new date will be in cell C3.

 

Step 5. Explore - Look through the Paste Function pop up window later to discover what equations are built into Excel.

Using an Excel worksheet - Calculating Percent and Using Absolute Cell Reference

Step 1 - Review percent - Before showing how to calculate percent with Excel, let's review how to calculate percent.

A number divided by a second number and multiplied by 100 expresses what percent the first number is of the second number. If you do not multiply by 100 you have the decimal equivalent of percent.

Step 2 - Writing a percent equation for only two numbers- Solve the following: 2 is what percent of 8?

Now that you are sure you remember the process for calculating percent, use an Excel worksheet to perform the calculations.

 

Step 3. Writing a percent equation for a column of numbers accompanied by a sum. - Data from the ice cream survey will be used to illustrate how to calculate percentage.

 

Task: Determine what percent 6 is of 24 by putting the equation into cell C2 of a worksheet similar to the one above.

Step 4. Auto Fill and problems associated with it - You probably remember a discussion of Auto Fill on a . That is a convenient way to place information in several cells at the same time. That might sound like a very good way to fill the equation into cells C3 through C9. For instructive purposes we will do that now to see the problem it causes.

 

Oops! Something wrong there. The problem was caused by the way the equation was written. The equation B2/B10 says, "take the first cell in this equation and divide it by the cell 8 spaces below." The reason that none of the other equations work is that there is nothing in the cell 8 spaces below any of the cells from B3 to B10. We must find a way of telling Excel to use call B10 to divide by for each of the other 8 equations.

Step 5. Absolute Cell Reference - You tell Excel to use one specific cell, and never move to another relative location in the calculations by using "absolute cell reference." To specify the cell, place a dollar sign before the column letter and before the row number. Thus, $B$10 says always use cell B10. Lets go back to the worksheet and re-write the equation in C2.

 

Notice the answer has not changed. If we were writing only this one equation, we wasted time using absolute cell reference. The real benefit of this equation will be seen when you fill down into cells C3 through C10.

Step 6. Fill the equation down into the cells. As soon as you fill this equation with an absolute cell reference down into cells C3 through C10, the percentages are instantly calculated. Only one more task remains.

 

Step 7. Format the cells - Unless you need five decimal places, I suggest formatting cells C2 through C10, the highlighted range above, so that one decimal place is displayed.

Right-click on the highlighted range of cells, and select Format Cells

In the category list select Number, and in the Decimal places: box use the down arrow to choose 1.

 

Click OK, your column of data has a nice uniform appearance.

 

CHARTS

Why use a chart?

Ø   To simplify the analysis of a mass of data.

Ø   To be able to compare the data.

Ø   To quickly analyze the trends in data series.

Ø   To analyze proportions among different data series.

Create a chart

This exercise consists in creating a "3D Column chart" on a new worksheet.

•    Enter the following data in the appropriate cells.

 

•    Select the data by using a block (continuous or non-continuous), for the exercise, from A1 to D4.

•    The selection of data is very important. Avoid selecting empty lines or columns otherwise you will have empty spots in your chart. Use the instructions in the operations pageto select only the blocks of cellsthat you need. Make sure that the range of cells you have selected represents data that is valuable to the chart. Don't take single cells scattered everywhere on your worksheet.

•    Generally, the first line or row selected from the range of cells will be used by the chart for the description of the X axis. The content of the first column from the range of cells is be used as the description for the legend of the chart. Keep in mind that Excel for the X axis are numbers.

•    From the Insert menu, select the Chart option.

OR

Use Excel's chart assistant by pressing the button.

Answer the questions that you will be shown in the next windows.

•    The first stage consists in choosing a chart from the 14 categories that are represented in the left column. In the right part of the screen, there are subcategories to represent the same data in a different ways.

•    These subcategories are alike, but will give a different representation from the same data. The first line shows the data in two dimensions (2D). The second line shows these same data but in three dimensions (3D). Further more, the first column shows the series of data side by side.

•    The second column shows the data in cumulative mode (one on top of the other). The last column shows the proportion of each of the series. Notice that each of the bars is of the same height, only the proportion of each series changes.

•    Before continuing, you may preview the chart this allows you to chose the best chart that represents the data. Press the button "Press and Hold to View Sample" to preview the chart. The section with the subcategories of chart will be replaced by a representation of the chart. You can try different types of charts before going any further and preview them.

•    The Data range tab is there to make sure that you choose the right area of cells for the chart. If there is an error, you can always press the button at the end of the box to re-select the cells that you need for the chart. You can determine the range of the data are in columns or in lines. This means that every line or every column represent a data or an item of the chart. For the purpose of this exercise, make sure that the

•    range of the series are in rows     and not in columns. That means    that every row from the range of    cells you selected will be a data series.

•    You also have a preview of the final chart before having finished it! You can experiment and see that will be the final result by changing  the presentation of the series of data.

•    Under the tab Series, you may change, add or delete the range of data. In the bottom to the left of

the window, you have the name of each of the series. In the right-hand side, the "Name" box allows you to change the name of a series. It's that name that will appear in the legend of the chart. You can select the content of a cell of one of the worksheets of the file or you can write the text of your choice.

•    The "Values" box is an area of cells that contain the numbers you want to see in the chart for that data series. You can change the area at any time.

•    The "Category (X) axis labels" box indicates the description that will be shown on the X axis of the chart. It's still possible to you to change it. You just need to press the button at the end of the box and select the cells that you wish for the X axis. You can also write the content. You need to place a semi-colon (;) between the text For example, orders could come from "In store";"Catalogue";"Internet".

•    Make your selection and press the Next button.

•    For the third step, there are several tabs. Each describes a characteristic of the chart.

•    Click on the Titles tab.

•    The tab of the titles serves for writing the text that will appear to the main title of the chart as well as for the descriptions of each of the axes of the chart. For this exercise:

In the Chart title box, write: Revenues by Categories. In the Category (X) axis box, write: Years. In the Series (Y) axis  box, write: Categories. In the Value (Z) axis box, write (M$).

•    Click on the Axes tab.

•    The Axes tab gives you the choice to show or to hide the data of the various axes of the chart. For the moment, leave all axis visible.

•    Click on the Gridlines tab.

•    Gridlines help you compare items that are not close to each other. You compare them to the gridlines to see a trend upward or downward. Under this tab, you may show or hide the gridlines of the chart. For the purpose of this exercise, select the same options as the image. Just activate the major gridlines for each axis.

•    Click on the Legend tab.

•    This tab gives you the choice to show or hide the legend of the chart. It contains the names of each series of your chart with a color representation beside it. You can even decide on the position of the legend.

•    Select the Bottom position.

•    Click on the Data Labels tab.

•    Under the Data Labels you can show the labels and values of each of the elements of the series. You can show the value, the percentage, both or even the description of the X axis (series name). The big problem with this option is that it crowds the chart with too much information making it more difficult to read. One option is to activate the label you want but move them on the side of the bar instead of over the top of each bar. It gives more information without blocking the view on the trends or the proportions of the chart.

•    For this exercise, do not activate any options.

Click on the Data Labels tab.

•    This is a recent addition to Excel. It's possible not only to show a chart but also the numbers themselves in a table below the chart. Select the Show the data table option to have a preview of the result. However, for the needs of the exercise, don't show the data table.

•    For this exercise, do not activate the Data table.

Press the Next button.

•    The chart assistant will ask you a last question. Do you want this chart in a worksheet that has numbers or on a new chart sheet? You can also give a name to this new working sheet.

•    For this exercise, select a new chart sheet that will be called Chart1.

•    Press the Finish button.

•    Excel will show you the finished chart with the options you selected.


 



2