Excel courses

EXCEL tutorial complete with practical examples


Télécharger EXCEL tutorial complete with practical examples

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

Télécharger aussi :


 

S.No

Topic

Page

Excel Tutorial 1

GETTING STARTED WITH EXCEL

01

Introducing Excel

07

02

Exploring Excel

08

03

Navigating a Worksheet

09

04

Planning a Workbook

09

05

Entering Text, Numbers, and Dates in Cells

10

06

Entering Multiple Lines of Text within a Cell

10

07

Changing Column Width and Row Height

10

08

Inserting a Column or Row

10

09

Deleting and Clearing a Row or Column

11

10

Working with Cells and Cell Ranges

11

11

Selecting Cell Ranges

11

12

Moving or Copying a Cell or Range

12

13

Inserting and Deleting a Cell Range

13

14

Entering a Formula

13

15

Copying and Pasting Formulas

14

16

Introducing Functions

15

17

Entering a Function

15

18

Entering Functions with AutoSum

15

19

Inserting and Deleting a Worksheet

16

20

Renaming a Worksheet

16

21

Moving and Copying a Worksheet

16

22

Editing Your Work

16

23

Using Find and Replace

17

24

Using the Spelling Checker

17

25

Changing Worksheet Views

17

26

Working with Portrait and Landscape Orientation

18

27

Printing the Workbook

18

28

Viewing and Printing Worksheet Formulas

18

Excel Tutorial 2

FORMATTING A WORKBOOK

29

Formatting Workbooks

20

30

Formatting Text

20

31

Working with Color

21

32

Formatting Text Selections

21

33

Setting a Background Image

21

34

Formatting Data

21

35

Formatting Dates and Times

22

36

Aligning Cell Content

22

37

Indenting Cell Content

23

38

Merging Cellst

23

39

Rotating Cell Content

23

S.No

Topic

Page

40

Adding Cell Borders

24

41

Working with the 

Format Cells Dialog Box

24

42

Copying Formats  with the Format Painter

25

43

Copying Formats with the 

Paste Options Button

25

44

Copying Formats with Paste Special

25

45

Applying Styles

26

46

Working with Themes

26

47

Applying a Table Style  to an Existing Table

26

48

Selecting Table Style Options

27

49

Introducing Conditional Formats

27

50

Adding Data Bars

28

51

Hiding Worksheet Data

28

52

Changing the Page Orientation  to Landscape

28

53

Defining the Print Area

28

54

Inserting Page Breaks

28

55

Setting and Removing Page Breaks

28

56

Adding Print Titles

29

57

Adding Headers and Footers

30

Excel Tutorial 3

WORKING WITH FORMULAS AND FUNCTIONS

58

Using Relative References

31

59

Using Absolute References

31

60

Using Mixed References

32

61

Entering Relative, Absolute, and Mixed References

32

62

Understanding Function Syntax

32

63

Inserting a Function

33

64

Typing a Function

34

65

Working with AutoFill

35

66

Using the AutoFill Options Button

35

67

Filling a Series

35

68

Creating a Series with AutoFill

36

69

Working with Logical Functions

36

70

Working with Date Functions

37

71

Working with Financial Functions

37

72

Using the PMT Function to Determine a Monthly Loan Payment

38

 

Excel Tutorial 4

WORKING WITH CHARTS AND GRAPHICS

73

Creating Charts

39

74

Selecting a Data Source

40

75

Selecting a Chart Type

40

76

Moving and Resizing Charts

41

77

Selecting Chart Elements

41

78

Choosing a Chart Style and Layout

42

79

Working with the Chart Title and Legend

42

80

Formatting a Pie Chart

43

81

Setting the Pie Slice Colors

43

82

Working with 3D Options

44

83

Creating a Column Chart

44

84

Formatting Column Chart Elements

45

85

Formatting the Chart Axes

45

86

Formatting Chart Columns

46

87

Creating a Line Chart

46

88

Formatting Date Labels

47

89

Setting Label Units

48

90

Overlaying a Legend

48

91

Adding a Data Series to an Existing Chart

48

92

Creating a Combination Chart

49

93

Inserting a Shape

50

94

Aligning and Grouping Shapes

50

Excel Tutorial 1

Getting Started with Excel Objectives

•   Understand the use of spreadsheets and Excel

•   Learn the parts of the Excel window

•   Scroll through a worksheet and navigate between worksheets

•   Create and save a workbook file

•   Enter text, numbers, and dates into a worksheet

•   Resize, insert, and remove columns and rows

•   Select and move cell ranges

•   Insert formulas and functions

•   Insert, delete, move, and rename worksheets

•   Work with editing tools

•   Preview and print a workbook

Introducing Excel

•   Microsoft Office Excel 2007 (or Excel) is a computer program used to enter, analyze, and present quantitative data

•   A spreadsheet is a collection of text and numbers laid out in a rectangular grid.

 Often used in business for budgeting, inventory management, and decision making

•   What-if analysis lets you change one or more values in a spreadsheet and then assess the effect those changes have on the calculated values

Introducing Excel

Exploring Excel

Exploring Excel

 

Description of the Excel window elements

Feature

Description

Office Button

A button that provides access to work book-level features and program settings

Quick Access

Toolbar

A collection of buttons that provide one-click access to commonly used commands, such as Save, Undo and Repeat

Title bar

A bar that displays the name of the active workbook and the Excel program name

Ribbon

The main set of commands organized by task into tabs and groups

Column headings

The letters that appear along the top of the worksheet window to identify the different columns in the worksheet

Workbook window

A window that displays an Excel workbook

Vertical scroll bar

A scroll bar used to scroll vertically through the workbook window

Horizontal scroll bar

A scroll bar used to scroll horizontally through the workbook window

Zoom controls

Controls for magnifying and shrinking the content displayed in the active workbook window

View shortcuts

Buttons used to change how the worksheet content is displayed – Normal,

Page Layout, or Page Brea Preview view

Sheet tabs

Tabs that display the names of the worksheets in the workbook

Sheet tab scrolling buttons

Buttons to scroll the list of sheet tabs in the workbook

Row headings

The numbers that appear along the left of the worksheet window to identify the different rows in the worksheet

Select All button

A button used to select all of the cells in the active worksheet

Active Cell

The cell currently selected in the active worksheet

Name box

A box that displays the cell reference of the active cell

Formula bar

A bar that displays the value or formula entered in the active cell

Navigating a Worksheet

• Excel provides several ways to navigate a worksheet

 

Excel navigation keys

Press

To move the active cell

?,?,?,?

Up, down, left or right one cell

Home

To column A of the current row

Ctrl+Home

To cell A1

Ctrl+End

To the last cell in the worksheet that contains data

Enter

Down on row or to the start of the next row of data

Shift+Enter

Up one row

Tab

One column to the right

Shift+Tab

One column to the left

Page Up, Page Down

Up or down the screen

Ctrl+Page Up, Ctrl+Page Down

To the previous or next sheet in the workbook

Planning a Workbook

• Before you begin to enter data into a workbook, you should develop a plan

 Planning analysis sheet

Planning Analysis Sheet

Planning Analysis Sheet

        Auther :             Amanda Dunn

        Date :                  01/02/2010

What problems do I want to solve?

•   I need to have contact information for each RipCity Digital customer.

•   I need to track how many DVDs I create for my customers.

•   I need to record how much I charge my customers for my service.

•   I need to determine how much revenue RipCity Digital is generating.

What data do I need?

•   Each customer’s name and contact information

•   The date each customer order was placed

•   The number of DVDs created for each customer

•   The cost of creating each DVD What calculations do I need to enter?

•   The total change for each order

•   The total number of DVDs I create for all orders

•   The total revenue generated from all orders What  from should may solutions take?

•   The customer orders should be placed in a grid with each now containing  data on a different on a different customer 

•   Information about each customer should be placed in separate columns.

•   The last column should contain the total charge for each customer.

•   The last now should contain the total number of DVDs created and the total revenue from all customer orders.

Entering Text, Numbers, and Dates in Cells

•   The formula bar displays the content of the active cell

•   Text data is a combination of letters, numbers, and some symbols

•   Number data is any numerical value that can be used in a mathematical calculation • Date and time data are commonly recognized formats for date and time values

Entering Multiple Lines of Text within a Cell

•   Click the cell in which you want to enter the text

•   Type the first line of text

•   For each additional line of text, press the Alt+Enter keys (that is, hold down the Alt key as you press the Enter key), and then type the text

Changing Column Width and Row Height

•   A pixel is a single point on a computer monitor or printout



•   The default column width is 8.38 standard-sized characters

•   Row heights are expressed in points or pixels, where a point is 1?72 of an inch

•   Autofitting eliminates any empty space by matching the column to the width of its longest cell entry or the row to the height of its tallest cell entry

Changing the Column Width and Row Height

•   Drag the right border of the column heading left to decrease the column width or right to increase the column width

•   Drag the bottom border of the row heading up to decrease the row height or down to increase the row height

Or

•   Double-click the right border of a column heading or the bottom border of a row heading to AutoFit the column or row to the cell contents (or select one or more columns or rows, click the

Home tab on the Ribbon, click the Format button in the Cells group, and then click AutoFit

Column Width or AutoFit Row Height)

Or

•   Select one or more columns or rows

•   Click the Home tab on the Ribbon, click the Format button in the Cells group, and then click Column Width or Row Height

•   Enter the column width or row height you want, and then click the OK button

Inserting a Column or Row

•   Select the column(s) or row(s) where you want to insert the new column(s) or row(s); Excel will insert the same number of columns or rows as you select

•   In the Cells group on the Home tab, click the Insert button (or right-click a column or row heading or selected column and row headings, and then click Insert on the shortcut menu)

Inserting a Column or Row

Deleting and Clearing a Row or Column

•   Clearing data from a worksheet removes the data but leaves the blank cells

•   Deleting data from the worksheet removes both the data and the cells

Working with Cells and Cell Ranges

•   A group of cells is called a cell range or range 

•   An adjacent range is a single rectangular block of cells

•   A nonadjacent range consists of two or more distinct adjacent ranges

•   A range reference indicates the location and size of a cell range

Selecting Cell Ranges

To select an adjacent range:

•   Click the cell in the upper-left corner of the adjacent range, drag the pointer to the cell in the lower-right corner of the adjacent range, and then release the mouse button or

•   Click the cell in the upper-left corner of the adjacent range, press the Shift key as you click the cell in the lower-right corner of the adjacent range, and then release the Shift key To select a nonadjacent range of cells:

•   Select a cell or an adjacent range, press the Ctrl key as you select each additional cell or adjacent range, and then release the Ctrl key To select all the cells in a worksheet:

•   Click the Select All button located at the intersection of the row and column headings (or press the Ctrl+A keys)

Selecting Cell Ranges

Moving or Copying a Cell or Range

•   Select the cell or range you want to move or copy

•   Move the mouse pointer over the border of the selection until the pointer changes shape

•   To move the range, click the border and drag the selection to a new location (or, to copy the range, hold down the Ctrl key and drag the selection to a new location) Or

•   Select the cell or range you want to move or copy

•   In the Clipboard group on the Home tab, click the Cut button or the Copy button (or right-click the selection, and then click Cut or Copy on the shortcut menu)

•   Select the cell or upper-left cell of the range where you want to move or copy the content

•   In the Clipboard group, click the Paste button (or right-click the selection, and then click Paste on the shortcut menu)

Moving or Copying a Cell or Range

Inserting and Deleting a Cell Range

Inserting or Deleting a Cell Range

•   Select a range that matches the range you want to insert or delete

•   In the Cells group on the Home tab, click the Insert button or the Delete button or

•   Select the range that matches the range you want to insert or delete

•   In the Cells group, click the Insert button arrow and then click the Insert Cells button or click the Delete button arrow and then click the Delete Cells command (or right-click the selected range, and then click Insert or Delete on the shortcut menu)

•   Click the option button for the direction in which you want to shift the cells, columns, or rows

•   Click the OK button

Entering a Formula

•   A formula is an expression that returns a value

•   A formula is written using operators that combine different values, returning a single value that is then displayed in the cell

 The most commonly used operators are arithmetic operators 

•   The order of precedence is a set of predefined rules used to determine the sequence in which operators are applied in a calculation

Entering a Formula

   

Arithmetic operators

Operation

Arithmetic

Operator

Example

Description

Addition

+

=10+A1

=B1+B2+B3

Adds 10 to the value in cell A1

Adds the values in cells B1, B2 and B3

Subtraction

-

=C9+B2

=1-D2

Subtracts the value in cell B2 from the value in cell C9

Subtracts the value in cell D2 from 1

Multiplication

*

=C9*B9

=E5*0.06

Multiplies the values in cells C9 and B9

Multiplies the value in cell E5 by 0.06

Division

/

C9/B9

=D15/12

Divides the value in cell C9 by the value in cell B9

Divides the value in cell D15 by 12

Exponentiation

^

=B5^3 =3^B5

Raises the value of cell B5 to the third power

Raises 3 to the value in cell B5

Entering a Formula

 

Order of precedence rules

 

Formula (A1=50, B1=10, C1=5)

Order of Precedence Rule

Result

=A1+B1*C1

Multiplication before addition

100

=(A1+B1)*C1

Expression inside parentheses executed before expression outside

300

=A1/B1-C1

Division before subtraction

0

=A1/(B1=C1)

Expression inside parentheses executed before expression outside

10

=A1/B1*C1

Two operators at same precedence level, leftmost operator evaluated first

25

=A1/(B1*C1)

Expression inside parentheses executed before expression outside

1

Entering a Formula

•   Click the cell in which you want the formula results to appear

•   Type = and an expression that calculates a value using cell references and arithmetic operators Press the Enter key or press the Tab key to complete the formula

Entering a Formula

Copying and Pasting Formulas

• With formulas, however, Excel adjusts the formula’s cell references to reflect the new location of the formula in the worksheet


Introducing Functions

A function is a named operation that returns a value

• For example, to add the values in the range A1:A10, you could enter the following long formula: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10

Or, you could use the SUM function to accomplish the same thing:

=SUM(A1:A10)

Entering a Function

Entering Functions with AutoSum

• The AutoSum button quickly inserts Excel functions that summarize all the values in a column or row using a single statistic

 Sum of the values in the column or row

 Average value in the column or row

 Total count of numeric values in the column or row

 Minimum value in the column or row

 Maximum value in the column or row

Entering Functions with AutoSum

Inserting and Deleting a Worksheet

To insert a new worksheet into the workbook, right-click a sheet tab, click Insert on the shortcut menu, select a sheet type, and then click the OK button

• You can delete a worksheet from a workbook in two ways:

 You can right-click the sheet tab of the worksheet you want to delete, and then click Delete on the shortcut menu

 You can also click the Delete button arrow in the Cells group on the Home tab, and then click

Delete Sheet

Renaming a Worksheet

•   To rename a worksheet, you double-click the sheet tab to select the sheet name, type a new name for the sheet, and then press the Enter key

•   Sheet names cannot exceed 31 characters in length, including blank spaces

•   The width of the sheet tab adjusts to the length of the name you enter

Moving and Copying a Worksheet

•   You can change the placement of the worksheets in a workbook

•   To reposition a worksheet, you click and drag the sheet tab to a new location relative to other worksheets in the workbook

•   To copy a worksheet, just press the Ctrl key as you drag and drop the sheet tab

Editing Your Work

•   To edit the cell contents, you can work in editing mode 

•   You can enter editing mode in several ways:  double-clicking the cell

 selecting the cell and pressing the F2 key

 selecting the cell and clicking anywhere within the formula bar

Editing Your Work

Using Find and Replace

You can use the Find command to locate numbers and text in the workbook and the Replace command to overwrite them

Using the Spelling Checker

• The spelling checker verifies the words in the active worksheet against the program’s dictionary

 

Changing Worksheet Views

• You can view a worksheet in three ways: 

 Normal view simply shows the contents of the worksheet

 Page Layout view shows how the worksheet will appear on the page or pages sent to the printer

 Page Break Preview displays the location of the different page breaks within the worksheet

Changing Worksheet Views


Changing Worksheet Views

Working with Portrait and Landscape Orientation

•   In portrait orientation, the page is taller than it is wide

•   In landscape orientation, the page is wider than it is tall

•   By default, Excel displays pages in portrait orientation

Working with Portrait and Landscape Orientation

• To change the page orientation:

 Click the Page Layout tab on the Ribbon

 In the Page Setup group, click the Orientation button, and then click Landscape

 The page orientation switches to landscape

Printing the Workbook

•   You can print the contents of your workbook by using the Print command on the Office Button

•   The Print command provides three options:

 You can open the Print dialog box from which you can specify the printer settings, including which printer to use, which worksheets to include in the printout, and the number of copies to print

 You can perform a Quick Print using the print options currently set in the Print dialog box

 Finally, you can preview the workbook before you send it to the printer

Viewing and Printing Worksheet Formulas

•   You can view the formulas in a workbook by switching to formula view, a view of the workbook contents that displays formulas instead of the resulting values

•   To change the worksheet to formula view, press the Ctrl+` keys

•   Scaling a printout reduces the width and the height of the printout to fit the number of pages you specify by shrinking the text size as needed

Viewing and Printing Worksheet Formulas

Viewing and Printing Worksheet Formulas

Excel Tutorial 2

Formatting a Workbook Objectives

•   Format text, numbers, and dates

•   Change font colors and fill colors

•   Merge a range into a single cell

•   Apply a built-in cell style

•   Select a different theme

•   Apply a built-in table style

•   Add conditional formats to tables with highlight rules and data bars

•   Hide worksheet rows

•   Insert print titles, set print areas, and insert page breaks

•   Enter headers and footers

Formatting Workbooks

•   Formatting is the process of changing a workbook’s appearance by defining the fonts, styles, colors, and decorative features

•   A theme is a collection of formatting that specifies the fonts, colors, and graphical effects used throughout the workbook

•   As you work, Live Preview shows the effects of formatting options on the workbook’s appearance before you apply them

Formatting Text

•   The appearance of text is determined by its typeface, which is the specific design used for the characters

Font 

•   Serif fonts

•   Sans serif fonts

•   Theme font

•   Non-theme font

–  Font Style

–  Font Size

                                      •    Measured in points

 

Working with Color

•   Theme colors are the 12 colors that belong to the workbook’s theme

•   Standard and custom colors

•   Apply a color by selecting a cell or range of cells, clicking the Font Color or Fill Color button arrow, and then selecting an appropriate color

 

Formatting Text Selections

• The Mini toolbar appears when you select text and contains buttons for commonly used text formats

 

Setting a Background Image

•   You can use a picture or image as the background for all the cells in a worksheet

•   Click the Page Layout tab on the Ribbon

•   Click the Background button

•   Locate the background, and then click the Insert button

Formatting Data

•   By default, values appear in the General number format, which, for the most part, displays numbers exactly as you enter them

•   The Number group on the Home tab has buttons for formatting the appearance of numbers

•   Comma style button

•   Decrease Decimal button

•   Percent Style button

•   Increase Decimal button

•   Accounting Number Format button

Formatting Data

Formatting Dates and Times

•    Although dates and times in Excel appear as text, they are actually numbers that measure the interval between the specified date and time and January 1, 1900 at 12:00 a.m.

Aligning Cell Content

•   In addition to left and right alignments, you can change the vertical and horizontal alignments of cell content to make a worksheet more readable

•   Alignment buttons are located on the Home tab

Alignment buttons

Buttons

Description

Aligns the cell content with the cell’s top edge

Vertically centers the cell content within the cell

Aligns the cell content with the cell’s bottom edge

Aligns the cell content with the cell’s left edge

Horizontally centers the cell content within the cell

Aligns the cell content with the cell’s right edge

Decreases the size of the indentation used in the cell

Increases the size of the indentation used in the cell

Rotates the cell content to an angle within the cell

Forces the cell text to wrap within the cell borders

Merges the selected cells into a single cell

Indenting Cell Content

•    You increase the indentation by roughly one character each time you click the Increase Indent button in the Alignment group on the Home tab

Merging Cells

• One way to align text over several columns or rows is to merge, or combine, several cells into one cell

Rotating Cell Content

•   To save space or to provide visual interest to a worksheet, you can rotate the cell contents so that they appear at any angle or orientation

•   Select the range

•   In the Alignment group, click the Orientation button and choose a proper rotation

Rotating Cell Content

Adding Cell Borders

•    You can add borders to the left, top, right, or bottom of a cell or range, around an entire cell, or around the outside edges of a range using the Border button arrow 

Working with the  Format Cells Dialog Box

        •   The Format Cells dialog box has six tabs, each focusing on a different set of formatting options

Copying Formats  with the Format Painter

•   The Format Painter copies the formatting from one cell or range to another cell or range, without duplicating any of the data

•   Select the range containing the format you wish to copy

•   Click the Format Painter button on the Home tab

•   Click the cell to which you want to apply the format

Copying Formats with the  Paste Options Button

Copying Formats with Paste Special


Applying Styles

style is a collection of formatting

•   Select the cell or range to which you want to apply a style

•   In the Styles group on the Home tab, click the Cell Styles button

•   Point to each style in the Cell Styles gallery to see a Live Preview of that style on the selected cell or range

•   Click the style you want to apply to the selected cell or range

Applying Styles

Working with Themes

        • The appearance of these fonts, colors, and cell styles depends on the workbook’s current theme

Applying a Table Style  to an Existing Table

•   You can treat a range of data as a distinct object in a worksheet known as an Excel table 

•   Select the range to which you want to apply the table style

•   In the Styles group on the Home tab, click the Format as Table button

Click a table style in the Table Style gallery

Applying a Table Style to an Existing Table

Selecting Table Style Options

• After you apply a table style, you can choose which table elements you want included in the style

Introducing Conditional Formats

•   A conditional format applies formatting only when a cell’s value meets a specified condition

•   Select the range or ranges to which you want to add data bars.

•   In the Styles group on the Home tab, click the Conditional Formatting button, point to Data Bars, and then click a data bar color

Or

•   Select the range in which you want to highlight cells that match a specified rule

•   In the Styles group, click the Conditional Formatting button, point to Highlight Cells Rules or Top/Bottom Rules, and then click the appropriate rule

Select the appropriate options in the dialog box, and then click the OK button

Adding Data Bars

data bar is a horizontal bar added to the background of a cell to provide a visual indicator of

the cell’s value

•   Select the cell(s)

•   In the Styles group on the Home tab, click the Conditional Formatting button, point to Data Bars, and then click the DataBar option you wish to apply

Adding Data Bars

Hiding Worksheet Data

•   Hiding rows, columns, and worksheets is an excellent way to conceal extraneous or distracting information

•   In the Cells group on the Home tab, click the Format button, point to Hide & Unhide, and then click your desired option

Changing the Page Orientation  to Landscape

•   Click the Page Layout tab on the Ribbon

•   In the Page Setup group, click the Orientation button, and then click Landscape

Defining the Print Area

•   By default, all parts of the active worksheet containing text, formulas, or values are printed

•   You can select the cells you want to print, and then define them as a print area 

•   Select the range, in the Page Setup group on the Page Layout tab, click the Print Area button, and then click Set Print Area 

Inserting Page Breaks

•   Excel prints as much as fits on a page and then inserts a page break to continue printing the remaining worksheet content on the next page

•   Manual page breaks specify exactly where the page breaks occur

Setting and Removing Page Breaks

To set a page break:

•       Select the first cell below the row where you want to insert a page break

•       In the Page Setup group on the Page Layout tab, click the Breaks button, and then click Insert Page Break To remove a page break:

•       Select any cell below or to the right of the page break you want to remove

In the Page Setup group on the Page Layout tab, click the Breaks button, and then click Remove

Page Break (or click Reset All Page Breaks to remove all the page breaks from the worksheet)


Setting and Removing Page Breaks

Adding Print Titles

•   You can repeat information, such as the company name, by specifying which rows or columns in the worksheet act as print titles, information that prints on each page

•   In the Page Setup group on the Page Layout tab, click the Print Titles button

•   Click the Rows to repeat at top box, move your pointer over the worksheet, and then select the range

•   Click the OK button

Adding Print Titles

Adding Headers and Footers

header is the text printed in the top margin of each page

•   A footer is the text printed in the bottom margin of each page

•   Scroll to the top of the worksheet, and then click the left section of the header directly above cell A1 to display the Header & Footer Tools contextual tab

Adding Headers and Footers

Excel Tutorial 3

Working with Formulas and Functions Objectives

•   Copy formulas

•   Build formulas containing relative, absolute, and mixed references

•   Review function syntax

•   Insert a function with the Insert Function dialog box

•   Search for a function

•   Type a function directly in a cell

•   Use AutoFill to fill in a formula and complete a series

•   Enter the IF logical function

•   Insert the date with the TODAY function

•   Calculate monthly mortgage payments with the PMT financial function

Using Relative References

Using Absolute References


Using Mixed References

Entering Relative, Absolute, and Mixed References

•   To enter a relative reference, type the cell reference as it appears in the worksheet. For example, enter B2 for cell B2

•   To enter an absolute reference, type $ (a dollar sign) before both the row and column references. For example, enter $B$2

•   To enter a mixed reference, type $ before either the row or column reference. For example, enter $B2 or B$2 or

•   Select the cell reference you want to change

•   Press the F4 key to cycle the reference from relative to absolute to mixed and then back to relative

Understanding Function Syntax

• Every function has to follow a set of rules, or syntax, which specifies how the function should be written

-- Arguments

Categories of Excel Functions

Category

Contains functions that

Cube

Retrieve data from multidimensional databases involving online analytical processing or OLAP

Database

Retrieve and analyze data stored in databases

Date & Time

Analyze or create date and time values and time intervals

Engineering

Analyze engineering problems

Financial

Have financial applications

Information

Return information about the format, location, or contents of worksheet cells

Logical

Return logical (true-false) values

Lookup &

Reference

Look up and return data matching a set of specified conditions from a range

Math & Trig

Have math and trigonometry applications

Statistical

Provide statistical analyses of a set of data

Text

Return text values or evaluate text

Understanding Function Syntax

Math, Trig and Statistical functions

Function

Category

Description

AVERAGE(number1[,number2, number3, …])

Statistical

Calculates the average of a collection of numbers, where number1, number2 and so forth are either numbers or cell references. Only number1 is required. For more than one cell reference or to enter numbers directly into the function, use the optional arguments number2, number3 and so forth.



COUNT (value1 [,value2, value3, …])

Statistical

Counts how many cells in a range contain numbers, where value1, value2, and so forth are text, numbers, or cell references. Only value1 is required. For more than one cell reference or to enter numbers directly into the function, use the optional arguments value2, value3, and so forth. 

COUNTA(value1 [,value2, value3, …])

Statistical

Counts how many cells are not empty in range value1, value2 and so forth, or how many numbers are listed within value1, value2, and so forth.

INT (number)

Math & Trig

Displays the integer portion of a number, number.

MAX (number1[,number2, number3, …])

Statistical

Calculates the maximum value of collection of numbers, where number1, number2, and so forth are either numbers or cell references.

MEDIAN (number1[,number2, number3, …])

Statistical

Calculates the median, or middle, value of a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

MIN (number1[,number2, number3, …])

Statistical

Calculates the minimum value of a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

RAND ()

Math & Trig

Returns a random number between 0 and 1.

ROUND (number, num_digits)

Math & Trig

Rounds a number to a specified number of digits, where number is the number you want to round and num_digits specifies how many digits to which you want to round the number.

SUM (number1[,number2, number3, …])

Math & Trig

Adds a collection of numbers, where number1, number2, and so forth are either numbers or cell references.

Inserting a Function

•   Click the Formulas tab on the Ribbon

•   To insert a function from a specific category, click the appropriate category button in the Function Library group. To search for a function, click the Insert Function button in the Function

Library group, enter a description of the function, and then click the Go button

•   Select the appropriate function from the list of functions

•   Enter the argument values in the Function Arguments dialog box, and then click the OK button

Inserting a Function

Inserting a Function

Typing a Function

• As you begin to type a function name within a formula, a list of functions that begin with the letters you typed appears

Working with AutoFill

•   AutoFill copies content and formats from a cell or range into an adjacent cell or range

•   Select the cell or range that contains the formula or formulas you want to copy

•   Drag the fill handle in the direction you want to copy the formula(s) and then release the mouse button

•   To copy only the formats or only the formulas, click the AutoFill Options button and select the appropriate option Or

•   Select the cell or range that contains the formula or formulas you want to copy

•   In the Editing group on the Home tab, click the Fill button

•   Select the appropriate fill direction and fill type (or click Series, enter the desired fill series options, and then click the OK button)

Working with AutoFill

Using the AutoFill Options Button

•   By default, AutoFill copies both the formulas and the formats of the original range to the selected range

•   You can specify what is copied by using the AutoFill Options button that appears after you release the mouse button

Filling a Series

• AutoFill can also be used to create a series of numbers, dates, or text based on a pattern

Filling a Series

 

AutoFill applied to different series

Type

 

Initial Entry

Extended Series

Values

 

1, 2, 3

4, 5, 6, ….

2, 4, 6

8, 10, 12, ….

Dates Times

 

Jan

Feb, Mar, Apr,….

January

February, March, April, ….

15-Jan, 15-Feb

15-Mar, 15-Apr, 15-May, ….

12/30/2010

12/31/2010, 1/1/2011, 1/2/2011, ….

12/31/2010, 1/31/2011

2/28/2011, 3/31/2011, 4/30/2011, ….

Mon

Tue, Wed, Thu, ….

Monday

Tuesday, Wednesday, Thursday, ….

11:00 AM

12:00PM, 1:00PM, 2:00PM, ….

Patterned Text

 

1st period

2nd period, 3rd period, 4th period, ….

Region 1

Region 2, Region 3, Region 4, ….

Quarter 3

Quarter 4, Quarter 1, Quarter 2, ….

Qtr 3

Qtr4, Qtr1, Qtr2, ….

Creating a Series with AutoFill

•   Enter the first few values of the series into a range

•   Select the range, and then drag the fill handle of the selected range over the cells you want to fill Or

•   Enter the first few values of the series into a range

•   Select the entire range into which you want to extend the series

•   In the Editing group on the Home tab, click the Fill button, and then click Down, Right, Up, Left,

Series, or Justify to set the direction you want to extend the series

Working with Logical Functions

•   A logical function is a function that works with values that are either true or false

•   The IF function is a logical function that returns one value if the statement is true and returns a different value if the statement is false

•   IF(logical_test, value_if_true, [value_if_false])

Working with Logical Functions

• A comparison operator is a symbol that indicates the relationship between two values

   

Comparison operators

Operator

Statement

Tests Whether

=

A1 = B1

The value in cell A1 is equal to the value in cell B1

>

A1 > B1

The value in cell A1 is greater than the value in cell B1

<

A1 < B1

The value in cell A1 is less than the value in cell B1

>=

A1 >= B1

The value in cell A1 is greater than or equal to the value in cell

B1

<=

A1 <= B1

The value in cell A1 is less than or equal to  the value in cell B1

<>

A1 <> B1

The value in cell A1 is not equal to the value in cell B1

Working with Logical Functions

•   =IF(A1="YES", "DONE", "RESTART")

•   =IF(A1="MAXIMUM", MAX(B1:B10), MIN(B1:B10))

•   =IF(D33>0, $K$10, 0)

Working with Logical Functions

Working with Date Functions

 

Date Functions

Function

Description

DATE (year, month, day)

Creates a date value for the date represented by the year, month and day arguments

DAY (day)

Extracts the day of the month from the date value

MONTH (date)

Extracts the month number from the date value where

1=January, 2=February, and s forth

YEAR(date)

Extracts the year number from the date value

WEEKDAY (date,[return_type])

Calculates the day of the week from the date value, where 1=Sunday, 2=Monday, and so forth; to choose a different numbering scheme, set the optional return_type value to “1”

(1=Sunday, 2=Monday, …) “2” (1=Monday, 2=Tuesday, …), or

“3” (0=Monday, 1=Tuesday, …)

NOW ()

Displays the current date and time

TODAY ( )

Displays the current date

Working with Financial Functions

Financial functions for loans and investments

Function

Description

FV (rate, nper, pmt,[pv=0][type=0])

Returns the future value of an investment, where rate is the interest rate per period, nper is the total number of periods, pmt is the payment in each period, pv is the present value of the investment, and type indicates whether payments should be made at the end of the period (0) or the beginning of the period (1)

PMT (rate, nper, pv, [fv=0][type=0])

Calculates the payments required each period on a loan or investment

IPMT (rate, per,  nper, pv [fv=0][type=0])

Calculates the amount of a loan payment devoted to paying the loan interest, where per is the number of the payment period

PPMT (rate, per, nper, pv [fv=0][type=0])

Calculates the amount of a loan payment devoted to paying off the principal of a loan, where per is the number of the payment period

PV (rate, nper, pmt, [fv=0][type=0])

Calculates the present value of a loan or investment based on periodic, constant payments

NPER (rate, pmt, pv, [fv=0][type=0])

Calculates the number of periods required to pay off a loan or investment

RATE (nper, pmt, pv, [fv=0][type=0])

Calculates the interest rate of a loan or investment based on periodic, constant payments

Using the PMT Function to Determine a Monthly Loan Payment

•   For loan or investment calculations, you need to know the following information:

 The annual interest rate

 The payment period, or how often payments are due and interest is compounded

 The length of the loan in terms of the number of payment periods  The amount being borrowed or invested

•   PMT(rate, nper, pv, [fv=0] [type=0])

Using the PMT Function to Determine a Monthly Loan Payment

   

Using the PMT Function to Determine a Monthly Loan Payment

Excel Tutorial 4

Working with Charts and Graphics

Objectives

•   Create an embedded chart

•   Work with chart titles and legends

•   Create and format a pie chart

•   Work with 3D charts

•   Create and format a column chart

•   Create and format a line chart

•   Use custom formatting with chart axes

•   Work with tick marks and scale values

•   Create and format a combined chart

•   Insert and format a graphic shape

•   Create a chart sheet

Creating Charts

•   A chart, or graph, is a visual representation of a set of data

•   Select the data source with the range of data you want to chart

•   In the Charts group on the Insert tab, click a chart type, and then click a chart subtype in the Chart gallery

•   In the Location group on the Chart Tools Design tab, click the Move Chart button to place the chart in a chart sheet or embed it into a worksheet

Creating Charts

Selecting a Data Source

• The data source is the range that contains the data you want to display in the chart

 Data series

 Series name

 Series values

 Category values

Selecting a Chart Type

 

Categories of Excel Chart types

Chart Type

Description

Column

Compares values from different categories. Values are indicated by the height of the columns.

Line

Compares values from different categories. Values are indicated by the height of the line. Often used to show trends and changes over time.

Pie

Compares relative values of different categories to the whole. Values are indicated by the area of the pie slices.

Bar

Compares values from different categories. Values are indicated by the length of the bars.

Area

Compares values from different categories. Similar to the line chart except that areas under the lines contain a fill color.

XY (Scatter)

Show the patterns or relationship between two or more sets of values. Often used in scientific studies and statistical analyses.

Stock

Displays stock market data, including the high, low, opening and closing prices of a stock.

Surface

Compares three sets of values in a three-dimensional chart.

Doughnut

Compares relative values of different categories to the whole. Similar to the pie chart except that it can display multiple sets of data.

Bubble

Shows the patterns or relationship between two or more sets of values. Similar to the XY (Scatter) chart except the size of the data marker is determined by a third value.

Radar

Compares a collection of values from several different data sets.


Selecting a Chart Type

Click the Insert tab on the Ribbon

• In the Charts group, click the Pie button

Moving and Resizing Charts

•   By default, a chart is inserted as an embedded chart, which means the chart is placed in a worksheet next to its data source

•   You can also place a chart in a chart sheet 

•   In the Location group on the Chart Tools Design tab, click the Move Chart button

 

Selecting Chart Elements

Choosing a Chart Style and Layout

Choosing a Chart Style and Layout

 

Pie chart layouts

Layout

Name

Pie Chart with

Layout 1

Chart title, labels and percentages

Layout 2

Chart title, percentages and legend above the pie

Layout 3

Legend below the pie

Layout 4

Label in pie slices

Layout 5

Chart title and labels in pie slices

Layout 6

Chart title, percentages and legend to the right of the pie

Layout 7

Legend to the right of the pie

Working with the Chart Title and Legend

•   Click the chart title to select it

•   Type the chart title, and then press the Enter key

•   Click the Chart Tools Layout tab on the Ribbon

•   In the Labels group, click the Legend button, and then click the desired legend position

Working with the Chart Title and Legend

Formatting a Pie Chart

Click the chart to select it

• In the Labels group on the Chart Tools Layout tab, click the Data Labels button, and then click More Data Label Options 

Setting the Pie Slice Colors

•   In pie charts with legends, it’s best to make the slice colors as distinct as possible to avoid confusion

•   Click the pie to select the entire data series, and then click the slice you wish to change • Change the fill color


Working with 3D Options

To increase the 3D effect, you need to rotate the chart

• Click the Chart Tools Layout tab on the Ribbon, and then, in the Background group, click the 3-D Rotation button

 

Creating a Column Chart

•   A column chart displays values in different categories as columns; the height of each column is based on its value

•   The bar chart is a column chart turned on its side, so each bar length is based on its value

Creating a Column Chart

•   Select the range

•   Click the Insert tab on the Ribbon

•   In the Charts group, click the Column button and then choose the chart subtype

Formatting Column Chart Elements

Click the Chart Tools Layout tab on the Ribbon

Formatting the Chart Axes

Click the Chart Tools Layout tab on the Ribbon

 

Formatting the Chart Axes

 

Formatting Chart Columns

Click any column in the Sector Weightings chart

• In the Current Selection group on the Chart Tools Layout tab, click Format Selection 

Formatting Chart Columns

Creating a Line Chart

•   Select the range

•   Click the Insert tab on the Ribbon

•   In the Charts group, click the Line button, and then click the Line chart

Formatting Date Labels

Click the Chart Tools Layout tab on the Ribbon

• In the Axes group, click the Axes button, point to Primary Horizontal Axis, and then click More Primary Horizontal Axis Options

Formatting Date Labels

Setting Label Units

In the Axes group on the Chart Tools Layout tab, click the Axes button, point to Primary Vertical Axis, and then click More Primary Vertical Axis Options 

• Click the Display units arrow and then make your selection

Setting Label Units

Overlaying a Legend

•   In the Labels group on the Chart Tools Layout tab, click the Legend button, and then click More Legend Options 

•   Click the Show the legend without overlapping the chart check box to remove the check mark

Adding a Data Series to an Existing Chart

•   Select the chart to which you want to add a data series

•   In the Data group on the Chart Tools Design tab, click the Select Data button

•   Click the Add button in the Select Data Source dialog box

•   Select the range with the series name and series values you want for the new data series • Click the OK button in each dialog box


Adding a Data Series  to an Existing Chart

 

Creating a Combination Chart

•   Select a data series in an existing chart that you want to appear as another chart type

•   In the Type group on the Chart Tools Design tab, click the Change Chart Type button, and then click the chart type you want

•   Click the OK button

Creating a Combination Chart

Inserting a Shape

•   Click the Insert tab on the Ribbon

•   In the Illustrations group, click the Shapes button, and then choose the shape you want • Draw the shape in your worksheet

Aligning and Grouping Shapes

•   Hold down the Shift key and then click each shape to select it

•   Click the Drawing Tools Format tab on the Ribbon

•   In the Arrange group, click the Align button, and then click your alignment option

•   To group several shapes into a single unit, select the shapes, and then click the Group button in the Arrange group on the Drawing Tools Format tab

Aligning and Grouping Shapes

*--------------------------------------*--------------------------------------*--------------------------------------*

*--------------------------------------*--------------------------------------*--------------------------------------*

Designed by: Shakeel Armaan



2