Lecture 5-Microsoft excel.pptx8y8y9;hoyf

yigygu

1. MICROSOFT EXCEL 1 Getting Started with Excel

2. Objectives 2 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 Work with excel formulas and functions, charts, tables, pivot tables and conditional formatting.

3. Objectives 3 Select and move cell ranges Insert formulas and functions Insert, delete, move, and rename worksheets Work with editing tools Preview and print a workbook

4. Introducing Microsoft Excel 4 Computer program used to enter, store, analyze, and present quantitative data Creates electronic versions of spreadsheets Collection of text and numbers laid out in a grid Displays values calculated from data Allows what- if analysis Ability to change values in a spreadsheet and assess the effect they have on calculated values

5. Excel Basics Each Excel file is a workbook that can hold many worksheets. Excel Work sheet or spreadsheets organize information (text and numbers) by rows and columns: This is a row . Rows are represented by numbers along the side of the sheet. This is a column . Columns are represented by letters across the top of the sheet. 5

6. Excel Basics A cell is the intersection between a column and a row. Each cell is named for the column letter and row number that intersect to make it. 6

7. Visual Overview Workbook made up of individual sheets Active sheet Other sheets: sheet tab for inactive sheets Name bar : displays the cell reference of active cell. Select All button : selects all cells in spreadsheet Row headings Sheet tab scrolling button Formula bar :displays the contents of the active cell. Active cell Column headings 7

8. Data Entry There are two ways to enter information into a cell: Type directly into the cell. Click on a cell, and type in the data (numbers or text) and press Enter. Type into the formula bar. Click on a cell, and then click in the formula bar (the space next to the ). Now type the data into the bar and press Enter . 8

9. Entering Text, Numbers, and Dates 9 Text data Combination of letters, numbers, and symbols Often referred to as a text string Number data Numerical value to be used in a mathematical calculation Date and time data Commonly recognized formats for date and time values

10. Entering Text 10

11. Entering Numbers 11

12. Entering Text 12 New data appears in both the active cell and the formula bar Truncation AutoComplete feature To enter multiple lines of text within a cell – Create a line break with Alt + Enter

13. Working with Columns and Rows 13 To make data easier to read: Modify size of columns and rows in a worksheet To modify size of columns or rows: Drag border to resize Double- click border to autofit Format the Cells group to specify

14. Working with Columns and Rows 14 Column width Expressed in terms of number of characters or pixels (8.43 characters equals 64 pixels) Note: Pixel size is based on screen resolution Row height Measured in points (1/72 of an inch) or pixels Default row height: 15 points or 20 pixels

15. Working with Columns and Rows 15

16. Working with Columns and Rows Inserting a column or row – Existing columns or rows shift to accommodate 16

17. Working with Columns and Rows 17 Deleting and clearing a row or column Deleting removes both the data and the cells Clearing removes the data, leaving blank cells where data had been

18. Visual Overview File tab: opens backstage view. Provides access to commands for saving and printing workbook Page layout tab : provides commands to change the way worksheets appear on printed page A formula is an expression that returns value. This formular calculates the sum A group of cells is called a cell range or range . Ranges can either be adjacent or nonadjacent You can rename a sheet so that it has a brief description of content and purpose The Insert worksheet tab inserts a new worksheet at the end of the workbook The Status bar indicates whether you are in Ready mode or Edit mode 18

19. Worksheet Data Adjacent range : a group of cells in a single rectangular block Non Adjacent range : a group of cells in a single rectangular block View buttons : change how the worksheet content is displayed- Normal view, Page Layout view, or Pagebreak view 19

20. Data selection 20 Single clicking on the column label will highlight the whole column. Single clicking on the row label will highlight the whole row. Clicking and dragging across several row or column labels will highlight several rows or columns, respectively. Clicking and dragging across a group of cells will highlight the contiguous (adjacent) group.

21. Data selection cont’d 21 Single clicking on one cell, pressing the F8 key (once) or holding down the Shift key, and then using the arrow keys to select the contiguous group will also highlight them. – With the F8 key, you must press the F8 key once again or press the Escape key once to escape from the highlighting. Noncontiguous (non- adjacent) cells can be highlighted by holding down the Ctrl key and using the mouse to single click the desired cells. Clicking on the gray box where the row labels and column labels intersect (top left corner of worksheet) will highlight the whole worksheet.

22. Spreadsheet Navigation As you enter and edit data, you will need to move through the worksheet, which can be done using your mouse or keyboard shortcuts. 22

23. Spreadsheet Navigation 23

24. Data Entry exercise Open Excel ( Start  All Programs  MS Office  Excel ). Enter the following information into your spreadsheet: 24

25. 25

26. Editing data 26 When editing data that is already entered, you can: Select (single click) the cell and retype the information (the previous contents of the cell will be replaced). Select (single click) the cell and press F2, which will place the cursor at the end of the contents of the cell, and then modify the contents (using the left and right arrow keys, the Home, the End, the Backspace, or the Delete key). Double- click the cell to place the cursor at the point of interest, and modify the contents. Select (single click) the cell and go the the Formula bar, which displays the contents of the active cell, to modify the contents.

27. Cut, Copy, and Paste 27 Similar to Microsoft Word. Can access them either through the Edit drop menu, using the Standard Toolbar buttons, or by using their associated keyboard shortcuts: Action Ctrl+X Ctrl+C Ctrl+V Result Cut Copy Paste

28. Filling Data 28 The Autofill feature allows you to quickly fill in commonly used series of data, such as repetitive or sequential data. Example: If a cell contains the word ”January”, can quickly fill in other cells with ”February”, ”March”, etc. To use the Autofill feature: 1. In several cells, type the first few elements of the series in order for Autofill to distinguish the pattern. Example: Type 1, 3, and 5 into three different cells.

29. Filling Data 29 To use the Autofill feature: In several cells, type the first few elements of the series in order for Autofill to distinguish the pattern. Example: Type 1, 3, and 5 into three different cells. You can also incorporate blank cells into you pattern. Highlight the cells distinguishing the series. Select the handle at the bottom right corner of the cell with the left mouse and drag it down across as many rows as you want to fill. Can also autofill across columns by dragging right. Release the mouse button

30. Sorting data 30 To execute a basic descending sort based on one column: Highlight the cells, rows, or columns that will be sorted. Select Sort from the Data drop menu. From the Sort dialog box, select the column for sorting from the Sort By drop- down menu and choose either ascending or descending.

31. Formatting cells 31 By default all cells are in General format where text - left- aligned (contents of cell are flush with the left border of the cell) numbers - right- aligned (contents of cell are flush with the right border of the cell).

32. Format Cells dialog box 32 Highlight the cells you wish to format first and then open the Format Cells dialog box by one of several – Select Format cells from the Format drop menu. – Right- click on the highlighted cells and choose Format Cells from the shortcut menu. – Use the Ctrl+1 keyboard shortcut.

33. Format Cells dialog box tabs The formatting options are contained in several tabs: The Number tab allows you to specify the data type of the contents of the cell. The Alignment tab allows you to change the position and alignment of the data within the cell. – You can also wrap text within a cell, and merge cells in the Alignment tab. . The Font tab allows you to change all of the font attributes including font face, size, styles, and effects. 33

34. Format Cells dialog box tabs 34 The Font tab allows you to change all of the font attributes including font face, size, styles, and effects. The Border and Pattern tabs allow you to add borders, shading, and background colors to a cell.

35. Formatting Toolbar The shortcut buttons on the Formatting Toolbar can also be used to adjust the font and cell attributes The following formatting keyboard shortcuts can also be used: 35

36. worksheet 36

37. Worksheet Navigation 37 A workbook can have two kinds of sheets: Worksheet contains a grid of rows and columns into which user enters data Chart sheet provides visual representation of data Cell reference identifies column/row location

38. Worksheet Navigation To navigate between worksheets Use sheet tabs To navigate within a worksheet Use mouse, keyboard, GoTo dialog box, or type cell reference in Name box 38

39. Worksheet Navigation Keys 39

40. Planning a Workbook 40 Use a planning analysis sheet to define: Goal or purpose of workbook Type of data to collect Formulas needed to apply to data you collected and entered Appearance of workbook content

41. Previewing a Workbook 41 Working with page orientation Portrait orientation (default) Page is taller than wide Landscape orientation Page is wider than tall

42. Printing a Workbook 42 Print tab provides options for choosing what to print and how to print Printout includes only the data in the worksheet Other elements (e.g., row/column headings, gridlines) will not print by default Good practice: Review print preview before printing to ensure that printout looks exactly as you intended and avoid unnecessary reprinting

43. Printing a Workbook 43

44. Viewing and Printing Worksheet Formulas Switch to formula view – Useful when you encounter unexpected results and want to examine underlying formulas or to discuss your formulas with a colleague 44

45. Viewing and Printing Worksheet Formulas Scaling the printout of a worksheet forces contents to fit on a single page 45

46. Working with Worksheets 46 Inserting a worksheet Name of new worksheet is based on number and names of other sheets in the workbook Deleting a worksheet Renaming a worksheet 31 characters maximum, including blank spaces Width of sheet tab adjusts to length of name

47. Working with Worksheets 47 Moving and copying a worksheet To move: Click and drag To copy: Ctrl + drag and drop Place most important worksheets at beginning of workbook (leftmost sheet tabs), less important worksheets toward end (rightmost tabs)

48. Editing Worksheet Content Use Edit mode to edit cell contents – Keyboard shortcuts apply only to text within selected cell Undoing and redoing an action – Excel maintains a list of actions performed in a workbook during current session 48

49. Editing Worksheet Content Using find and replace 49

50. Editing Worksheet Content Using the spelling checker 50

51. Previewing a Workbook 51 Changing worksheet views Normal view Page Layout view Page Break Preview

52. Page Layout View 52

53. Page Break Preview 53

54. Previewing a Workbook 54 Working with page orientation Portrait orientation (default) Page is taller than wide Landscape orientation Page is wider than tall

55. Printing a Workbook 55 Print tab provides options for choosing what to print and how to print Printout includes only the data in the worksheet Other elements (e.g., row/column headings, gridlines) will not print by default Good practice: Review print preview before printing to ensure that printout looks exactly as you intended and avoid unnecessary reprinting

56. Printing a Workbook 56

57. Formulas and functions 57

58. Introduction 58 In Excel,the calculation can be specified using either a formula or a function. Formulas are self- defined instructions for performing calculations. In contrast, Functions are pre-defined formulas that come with Excel. In either case, all formulas and functions are entered in a cell and must begin with an equal sign ’=’.

59. Working with Formulas Formula An expression that returns a value Written using operators that combine different values, resulting in a single displayed value 59

60. Working with Formulas 60 Entering a formula Click cell where you want formula results to appear Type = and an expression that calculates a value using cell references and arithmetic operators Cell references allow you to change values used in the calculation without having to modify the formula itself Press Enter or Tab to complete the formula

61. Working with Formulas Viewing a formula Select cell and review expression displayed in the formula bar Each cell reference is color coded in the formula and corresponding cell in the worksheet Formula displayed in formula bar Cell border colours match colours in formula bar 61

62. Working with Formulas Copying and pasting formulas – Cell references adjust to reflect new location of the formula in the worksheet Cell containing copied formula Formulas pasted into selected range Shortcut button provides options for pasting formulas and values 62

63. Practice Enter the following information into a blank worksheet (ignore any formatting) in columns A, B, and C, and in rows 1 through 6. Then calculate the Total Cost for semester one using a formula to add up the individual Costs (Tuition, Housing, etc) 63

64. 64

65. Automatic calculation 65 Why use cell references? For automatic calculation The formula is calculated when values change Practice automatic calculation by changing values in for tuition in practice exercise. The sum should be recalculated

66. Turning on Automatic calculation Click on calculations options to turn on automatic calculation Click on Formulas tab 1 66 2

67. Introducing Functions 67 Function Named operation that returns a value Simplifies a formula, reducing a long formula into a compact statement; for example, to add values in the range A1:A10: Enter the long formula: =A1+A2+A3+A4+A5+A6+A7+A8+A9+A10 - or - Use the SUM function to accomplish the same thing: =SUM(A1:A10)

68. Function introduction 68 Functions differ from regular formulas in that, after the equal sign, you supply the cell addresses but not the arithmetic operators. Functions perform calculations by using specific values, called arguments , in a particular order called syntax .

69. Using functions 69 When using a function, remember the following: Use an equal sign to begin the function. Specify the function name. Enclose all of the function’s arguments within parentheses. Use a comma to separate the function’s individual arguments.

70. E ntering a Function ScreenTip shows the function being entered into the cell a colored border indicates the range used in the function 70

71. Entering Functions with AutoSum 71 Fast, convenient way to enter commonly used functions Includes buttons to quickly insert/generate: Sum of values in column or row (SUM) Average value in column or row (AVERAGE) Total count of numeric values in column or row (COUNT) Minimum value in column or row (MIN) Maximum value in column or row (MAX)

72. Entering Functions with AutoSum Click to enter an AutoSum function into the selected cell Excel inserts the SUM function and the most likely cell reference Excel selects the range over with the AutoSum is applied 1 72 2 3

73. Practice Exercise 73 In the Costs for the semester one and two spreadsheet, calculate the Total Cost for the semester two using the Autosum icon. Click on cell C6 to activate it. Click the Autosum button. Highlight cells C2 through C5. Press Enter.

74. Functions wizard You can access all of the available functions in Excel using the Function Wizard the select the cell where you want the formula to be Click to function/ formulas wizard on the standard toolbar 1 74 2

75. Using the functions wizard 75 Other ways of starting the Function Wizard are: Click on the drop down arrow next to the Autosum icon button. * You will first see the commonly used functions in Excel, and at the bottom of the menu, the More Functions option. * Clicking on More Functions will give you an alphabetical and categorical listing of all available functions in Excel.

76. Functions for Descriptive Statistics Below are several functions you will need to learn for this class. Try them out with the practice data set. =AVERAGE(first cell:last cell): calculates the mean =MEDIAN(first cell:last cell): calculates the median =MODE(first cell:last cell): calculates the mode =VAR P (first cell:last cell): calculates the variance =STDEV P (first cell:last cell): calculates the standard deviation You may directly write the functions for these statistics into cells or the formula bar, OR You may use the function wizard ( in the toolbar) 76

77. Fu Yo sp n nctions for Descriptive Statistics ur Excel readsheet should ow look like this: 77

78. Practice functions 78

79. Copying Formulas/Functions and Cell References 79

80. Cell references 80 Excel records cell addresses in formulas and functions in three different ways: Relative cell reference. Absolute cell reference. Mixed cell reference.

81. Relative Cell References 81 Calling cells by just their column and row labels (such as ’A1’) is called a relative cell reference . When formulas or functions contain relative cell referencing and they are copied from one cell to another, Excel changes the cell addresses relative to the row and column they are moved to. EXAMPLE: If a simple addition formula of ’=A1+B1’ in cell C1 is copied to cell C2, the formula would change to ’=A2+B2’ to reflect the new row. EXAMPLE: If a simple average function of ’=AVERAGE(B2:B5)’ in cell B6 is copied to cell C6, the function would change to ’=AVERAGE(C2:C5)’ to reflect the new column.

82. Practice 1 Enter the following information into a blank worksheet in columns A, B, and C, and in rows 1 through 5. Then define a formula in cell D2 to calculate the Cost of the group of Biology Textbooks, and copy the formula into cells D3 through Compare the formulas in cells D2 through D5 by looking in the Formula bar. What do you see 82

83. Practice 2 83 In the Costs for the semesters spreadsheet, copy the AVERAGE function in cell D3, which calculates the average Housing cost for both semesters, to cell D4 through D6. Compare the copied functions by looking in the Formula bar.

84. Absolute Cell References 84 An absolute cell reference refers to the same cell, no matter where the formula or function is copied. In other words, when a formula or function containing an absolute cell reference is copied to a new location, the cell reference is not adjusted. To create an absolute cell reference- add dollar signs (’$’) in front of both the column and row identifiers for the cell referenced – this fixes the row AND column.

85. Absolute Cell References 85 You can add the dollar signs (’$’) automatically by using the F4 keyboard shortcut – press theF4 key once after entering the cell address into the formula or function by typing or by using the point mode. .EXAMPLE: When the formula ’=B7*$C$1’ is copied from cell C7 to cell C8, the relative cell reference changes ’B7’ to ’B8’, but the ’$C$1’ absolute cell reference remains unchanged .

86. Absolute Cell References_ Advantage 86 An absolute cell reference is most often used when you want to use a constant in a formula or function. Place the constant in its own cell and using an absolute cell reference Allows you to change the value of the constant in the one cell and all the formulas and functions absolutely referencing the cell will be automatically updated. This is much better than typing the actual value of the constant into the formulas or functions.

87. Practice _ not using absolute ref The following exercise demonstrates what happens when you don’t use a absolute cell reference when you should. 1. Create the following spreadsheet in columns A through F and rows 1 through 4 (ignore any formatting). 87

88. 88

89. PRACTICE INSTRUCTIONS CONT’D 89 In cell D2, calculate the Total of Trial 1 and Trial 2 for subject A using the SUM function. Use the Autofill feature to copy the function for subjects B and C. In cell E2 enter the formulas ’=D2*F2’ either by typing or by using the point mode. Use the Autofill feature to copy the formula for subjects B and C. What do you notice about the result for subjects B and C? Edit the formula in cell E2 to reference the CONSTANT using an absolute cell reference: ’=D2*$F$2’. Use the Autofill feature to copy the correct formula for subjects B and C. What do you notice about the result for subjects B and C?

90. Mixed Cell References 90 You use a mixed cell reference to reference a cell that is part absolute and part relative. In other words, a mixed cell reference can be used when you wish to fix only the row OR the column. EXAMPLE: In the formula ’=A$1 + $B2’, the row of cell A1 is fixed and the column of cell B2 is fixed. As with absolute cell referencing, you need to add a dollar sign (’$’), but only in front of the column OR row identifier for the cell referenced.

91. Mixed Cell References 91 As before, you can add the dollar sign (’$’) automatically by using the F4 keyboard shortcut: In the active cell, type ’=’. Enter a cell address either by typing or using the point mode. Press F4 once – two dollar signs (’$’) are entered (both the column and row identifiers are absolute). Press F4 again – the column identified is now relative and the row identifier is now absolute. Press F4 again – the column identified is now absolute and the row identifier is now relative. Press the Escape key to deactivate the F4 key.

92. Order of Operations 92 Excel follows the standard order of operations when it performs all calculations: Parentheses: Computations enclosed in parentheses are performed first. Exponents: Computations involving exponents are performed next. Multiplication and Division: Performed in the order which they occur (from left to right). Addition and Subtraction: Performed in the order which they occur (left to right).

93. Working with Formulas Order of precedence – Set of predefined rules used to determine sequence in which operators are applied in a calculation 93

94. 94

95. Working with Formulas S/N FUNCTION CATEGORY DESCRIPTION USAGE 1 DATE Date & Time Returns the number that represents the date in excel code =DATE(2015,2,4) 2 DAYS Date & Time Find the number of days between two dates =DAYS(D6,C6) 3 MONTH Date & Time Returns the month from a date value =MONTH("4/2/2015") 4 MINUTE Date & Time Returns the minutes from a time value =MINUTE("12:31") 5 YEAR Date & Time Returns the year from a date value =YEAR("04/02/2015") Date Time Functions These functions are used to manipulate date values. The following table shows some of the common date functions 95

96. Practice In column E, use a function to compute the average of the best two done tests In column F, Comupute the final mark which is the 70% of the exam mark, plus 30% of the average of the test marks 96

97. Practice 97 https://exceljet.net/excel-functions/excel- countifs-function Practice the following functions COUNT IF including OR, AND, NOT

98. Pivot tables and Charts Conditional formatting Tables ADDITIONAL EXCEL 98

99. Conditional formatting 99

100. Conditional formatting 100 Let's say you have a worksheet with thousands of rows of data. It would be extremely difficult to see patterns and trends just from examining the raw information. Similar to charts, conditional formatting provides another way to visualize data and make worksheets easier to understand.

101. Conditional formatting 101 Conditional formatting allows you to automatically apply formatting— such as colors , icons , and data bars —to one or more cells based on the cell value . To do this, you'll need to create a conditional formatting rule . For example, a conditional formatting rule might be: If the value is less than $2000, color the cell red . By applying this rule, you'd be able to quickly see which cells contain values less than $2000.

102. Conditional formatting 102

103. Create a conditional formatting rule 103 Select the desired cells for the conditional formatting rule. From the Home tab, click the Conditional Formatting command. A drop- down menu will appear. Hover the mouse over the desired conditional formatting type , then select the desired rule from the menu that appears. In our example, we want to highlight cells that are greater than $4000

104. 104

105. Create a conditional formatting rule A dialog box will appear. Enter the desired value(s) into the blank field. In our example, we'll enter 4000 as our value. Select a formatting style from the drop- down menu. In our example, we'll choose Green Fill with Dark Green Text , then click OK . The conditional formatting will be applied to the selected cells. 105

106. Create a conditional formatting rule You can apply multiple conditional formatting rules to a cell range or worksheet, allowing you to visualize different trends and patterns in your data. 106

107. Removing conditional formatting 107 To remove conditional formatting: Click the Conditional Formatting command. A drop- down menu will appear. Hover the mouse over Clear Rules, and choose which rules you want to clear. In our example, we'll select Clear Rules from Entire Sheet to remove all conditional formatting from the worksheet.

108. Conditional formatting 108 Go to the site https://edu.gcfglobal.org/en/excel2016/condit ional- formatting/1/ Go through the explanation on conditional formatting Go through the challenge in your free time

109. Excel tables 109

110. Excel tables 110 To format data as a table: Select the cells you want to format as a table. From the Home tab, click the Format as Table command in the Styles group. Select a table style from the drop- down menu. A dialog box will appear, confirming the selected cell range for the table. If your table has headers , check the box next to My table has headers , then click OK . The cell range will be formatted in the selected table style. Tables include filtering by default. You can filter your data at any time using the drop- down arrows in the header cells.

111. Excel tables 111 To read more about Modifying tables, Go to https://edu.gcfglobal.org/en/excel2016/tables /1/ For practice purposes, do the challenge on the page referenced above.

112. Pivot tables 112

113. Pivot tables 113 You can think of a pivot table as a report. However, unlike a static report, a pivot table provides an interactive view of your data . When you have a lot of data, it can sometimes be difficult to analyze all of the information in your worksheet. PivotTables can help make your worksheets more manageable by summarizing your data and allowing you to manipulate it in different ways.

114. Using PivotTables to answer questions 114 Consider the example below. Let's say we wanted to answer the question What is the amount sold by each salesperson? Answering it could be time consuming and difficult; each salesperson appears on multiple rows, and we would need to total all of their different orders individually. We could use the Subtotal command to help find the total for each salesperson, but we would still have a lot of data to work with. Fortunately, a PivotTable can instantly calculate and summarize the data in a way that will make it much easier to read.

115. To create a PivotTable: Select the table or cells (including column headers) you want to include in your PivotTable. From the Insert tab, click the PivotTable command. 115

116. To create a PivotTable: The Create PivotTable dialog box will appear. Choose your settings, then click OK . In our example, we'll use Table1 as our source data and place the PivotTable on a new worksheet . 116

117. To create a PivotTable: A blank PivotTable and Field List will appear on a new worksheet. Once you create a PivotTable, you'll need to decide which fields to add. Each field is simply a column header from the source data. In the PivotTable Field List , check the box for each field you want to add. In our example, we want to know the total amount sold by each salesperson , so we'll check the Salesperson and Order Amount fields. 117

118. To create a PivotTable: The selected fields will be added to one of the four areas below. In our example, the Salesperson field has been added to the Rows area, while Order Amount has been added to Values . Alternatively, you can drag and drop fields directly into the desired area. 118

119. To create a PivotTable: 119 The PivotTable will calculate and summarize the selected fields. In our example, the PivotTable shows the amount sold by each salesperson . NOTE: If you change any of the data in your source worksheet, the PivotTable will not update automatically . To manually update it, select the PivotTable and then go to Analyze

120. Pivoting data 120 To add columns: So far, our PivotTable has only shown one column of data at a time. In order to show multiple columns , you'll need to add a field to the Columns area. Drag a field from the Field List into the Columns area. In our example, we'll use the Month field. The PivotTable will include multiple columns. In our example, there is now a column for each person's monthly sales , in addition to the grand total .

121. Pivot tables 121 Go to https://exceljet.net/excel- pivot- tables Go through the tutorial Use the data from the above link to practice.

122. CHARTS 122

123. Introduction 123 Excel also allows one to present your data visually, in the form of a chart (also know as a graph). Displaying data in a well- conceived chart can make your data more understandable. Useful for summarizing a series of numbers and their interrelationships. Often helps you spot trends and patterns that might otherwise go unnoticed.

124. Intro- formulas and charts 124 just like formulas and functions, charts are dynamic. A chart is linked to the data in the worksheet. If the data changes, the chart is automatically updated to reflect those changes.

125. Introduction 125 Before you can create a chart, you must have some data. A chart is essentially an ’object’ that Excel creates upon request. Consists of one or more data series. The data for each series is stored in a separate row or column. Appearance of the data series depends on the selected chart type .

126. Visual representation You can find the Charts group under the INSERT tab on the Ribbon. 126 Charts groups Insert tab on the ribbon The Charts group is formatted in such a way that − Types of charts are displayed. The subgroups are clubbed together. It helps you find a chart suitable to your data with the button Recommended Charts.

127. Chart tools When you click on a chart, a new tab Chart Tools is displayed on the ribbon. There are two tabs under CHART TOOLS − DESIGN FORMAT 127 Design tab Format tab

128. Chart types: Column. One of the most common chart types. Often used to compare discrete items. Displays each data point as a vertical column, the height of which corresponds to the value. The value axis (i.e., continuous axis) is displayed in the vertical axis, which is usually on the left side of the chart. Can specify any number of data series, and the corresponding data points from each series can be stacked on top of each other. * Can depict the differences between items in a series or items across multiple series. * Typically, each data series is depicted in a different color or pattern. 128

129. Chart types 129

130. Chart types 130

131. Practice exercise – charts types Enter the following information into a blank worksheet in columns A, B, and C, and in rows 1 through 15. Represent this data in charts Select different chart types Which is most appropriate 131

132. Gantt Chart 132 A Gantt chart is a type of bar chart that illustrates a project schedule , named after its inventor, Henry Gantt (1861–1919), who designed such a chart around the years 1910– 1915.

133. Gant chart (part of Assignment) 133 https://www.ablebits.com/office-addins- blog/2014/05/23/make-gantt- chart-excel/ https ://www.vertex42.com/edu/excel- tutorials- for-beginners.html