1 / 73

COLORS

COLORS. GRaPhiCS. Activity 20. 1. Open Microsoft Excel. 2. Create a Calendar as shown below. 3. Save the file as Activity 20 . Introduction to Formulas. What is a formula?

urania
Download Presentation

COLORS

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. COLORS GRaPhiCS

  2. Activity 20 1. Open Microsoft Excel. 2. Create a Calendar as shown below. 3. Save the file as Activity 20.

  3. Introduction to Formulas What is a formula? A formula is a data entry capable of performing mathematical and logical operations automatically. It is an equation used to calculate values in a cell. A data entry is treated as a formula when an equal sign (=) is the first character in the cell. A formula consists of two components: operands and operators. Operand is a number or cell reference used in the formula e.g A1 or B1. an operator tells Excel what to do with the operands. Example: 1. B3 + 10 B3 and 10 are the operands, + is the operator. The formula will add the content or cell B3 to 10. 2. 15 – 5 5 and 15 are operands, - is the operator. The formula will subtract 5 from 15. 3. A2 + B2 A2 and B2 are operands, + is the operator. The formula will add the contents of the cells A2 and B2.

  4. Arithmetic Operators

  5. How to Enter formula 1. Select the cell where you want to display the answer of the formula. 2. Determine the operands and operator. 3. Type equal sign (=). 4. Enter the first operand. If the operand is the value of a cell, you may point your mouse to the cell reference or type the cell name. 5. Type the operator. 6. Enter the second operand. 7. Press Enter key on the keyboard or Enter button on the formula bar. Example: I want to display the answer of my formula at cell C1. Operands = 5 on cell A1 and 10 on cell B1 Operator = addition (+)

  6. Result:

  7. Activity 21A 1. Open Microsoft Excel. 2. Type and complete the given table below. Use formulas in answering. 3. Save the file as Activity 21A.

  8. Activity21B 1. Create a new workbook. 2. Complete the given addition table: 3. Save the file as Activity 21B.

  9. Complex Formulas Complex formulas are formulas containing more than one operator. For example, the formula: = B3 * C5 + A1 The above formula has two operations, namely multiplication and addition. In calculating the given formula, Excel follows a sequence which is called precedence or order of evaluation. Here are the rules in evaluating complex formulas: 1. Contents with parentheses are evaluated first. If you used multiple pairs of parentheses, Excel will evaluate the innermost set of parentheses first. 2. Mathematical operators are evaluated according to this priority. Note: Operators with the same levels will be evaluated from left to right.

  10. Example: Let’s Practice: 3 * 3 - 5 / 2 * ( 5 – 1 ) 2 + 2 ^ ( 3 + 1 ) * ( 5 – 3 )

  11. Activity 22 1. Open Microsoft Excel. 2. Enter data as shown below. Rename Sheet 1 to Formulas. 3. Select all and change the font to Serifa BT and align it all at the center. Change the font size of the data in A1 and A2 to 12. 4. Enter the formulas needed to complete the table. 5. Apply border on the table. 6. Save the file as Activity 22.

  12. Relative & Absolute Cell References Relative Cell Reference Relative cell reference adjusts to its new location whenever a formula is copied or moved. Example: Cell A1 has a value of 2683 and cell A2 has a value of 1227. At cell A4, a formula was created = A1 + A2. As we try to copy cell A4 to C4, let’s see what happens. Notice that the operands A1 and A2 were Changed to C1 and C2. From the copied Formula,it adds the two cells above. Although the cell References were changed, the sequence ofoperators does not change.

  13. Absolute Cell Reference Absolute cell reference, do not change when the formula is moved or copied to a new cell. To create an absolute cell reference, insert a dollar sign ($) before the column letter and/or the row number of the cell reference. For example: Cell A1 has a value of 100 and cell A2 has a value of 125. On cell A3, An absolute formula was created = $A$1 + $A$2. After copying the data from A3 to B3, the formula bar copied the same formula and result as cell A3.

  14. Activity 23 PART I 1. Open Microsoft Excel. 2. Enter the data as shown below. Rename Sheet 1 to references. 3. Format the cell so that the worksheet will Be similar to the figure shown below. 4. Convert the following expressions into Excel formulas: 5. Save the file as Activity 23a. PART II 1. Input the following data as shown. 2. Use the following formulas to complete the Table. a. Consumption = Present – Previous b. Total – the sum of all Consumption c. Percentage = Consumption / Total 3. Format the worksheet as follows: 4. Save the file as Activity 23b.

  15. Relative & Absolute Cell References What are functions? Functions are predefined formulas that perform calculations by using specific values called arguments. Functions can be used to perform simple or complex calculations. Like a formula, a function starts with an equal sign. Steps on how to use the SUM function For the purpose of discussion, open the file Work23.xls. 1. Type equal sign (=) on the cell you want to put the function. 2. Type the function that you want to use and the range of the cells needed for the operation. 3. Press the Enter key when done.

  16. Commonly Used Function Displays the arithmetic sum of the arguments. Displays the average of the range Identified in the argument. Displays the number of cells with numerical values in the argument range. Displays the largest number contained in the range identified in the argument. Displays the smallest number contained in the range identified in the argument. Display text of values if certain condition exists. The first argument sets a condition for comparison called a logical test. The second argument determines the value that will be displayed if the logical test is true. The third argument determines the value that will be displayed if the logical test is false. SUM(number1,number2) AVERAGE(number1,number2) COUNT(value1,value2) MAX(number1,number2) MIN(number1,number2) IF()

  17. Activity 24 1. Open Microsoft Excel. 2. Type and complete the given worksheet. Use the SUM() to complete for the total of the cells with a ?. 3. Save the file as Activity 24.

  18. The AVERAGE Function The average function returns the average or the arithmetic mean of its arguments. Excel first computes for the sum of the arguments and then the sum will be divided by the total number of arguments. Steps on how to create the average function This discussion requires you to open Work23.xls. 1. Type equal sign (=) on the cell you want to put the function. 2. Type AVERAGE and the range of the cells needed for the operation. 3. Press the ENTER key when done. Try entering the average for the remaining columns.

  19. Activity 25 1. Open Microsoft Excel. 2. Type the given data. 3. Enter the formulas and functions needed to complete the table. 4. Apply borders if needed. 5. Format the sheet to look like the one below. 6. Save the file as Activity 25.

  20. COUNT, MIN & MAX This lesson will utilize Work23.xls work. Make sure the file is opened when the following functions are discussed. =COUNT() The COUNT function counts the number of cells That contain numbers and numbers within the list Of arguments. Syntax: =MIN() – The MIN function returns the smallest number in a set of values. It ignores logical values and text. =MAX() – The MAX functions returns the largest value in a set of values. It Ignores logical values and text.

  21. Complete the table by entering the functions MIN and MAX on the remaining columns on the table.

  22. Activity 26 1. Launch Microsoft Excel. 2. Open exceldata25 on your 3½ floppy. 3. Delete Row 15, and then merge cells A16 to C16, type MINIMUM. 4. Merge cells A17 to C17, type MAXIMUM. 5. Merge cells A18 to C18, type COUNT. 6. Complete the formula needed in the table. 7. Apply border on the second table. 8. Save the file as Activity 26.

  23. The IF Function The IF function checks whether a condition is met, and returns one value if TRUE, and another value if FALSE. Syntax: Logical_Testis any value or expression that can be evaluated as TRUE or FALSE. Value_If_Trueis the value that is returned if Logical_Test is TRUE. If omitted, TRUE is returned. Value_If_Falseis the value that is returned if Logical_Test is FALSE. If omitted, FALSE is returned. Steps on how to create the count function 1. Type equal sign (=) on the cell you want to put the function 2. Type IF then the Logical_Test, followed by the Value_If_True and Value_If_False. 3. Press the ENTER key when done.

  24. Activity 27 Part I 1. Open Microsoft Excel. 2. Type the data as shown below. Change the font to Hobo Medium. 3. Apply Borders and center alignment for column B – G. 4. Complete the table by applying the required functions. Column F requires the AVERAGE function to compute for the average of the four grades. Column G, using the IF function, will display PASSED if the final grade of the student is greater than 74. it will display FAILED if the final grade is less than 75. 5. Save the file as Activity 27a.

  25. Inserting Charts What is a chart? A chart is a graphical representation of Data contained in a worksheet. It can make The data in a worksheet easier to understand.

  26. Commonly used Types of Charts Column Chart A Column chart uses bars of varying heights To illustrate values in a worksheet and is useful For showing relationships among categories of Data. An example is shown on the previous page. Line Chart A line chart is similar to the column chart except Points connected by a line replace columns. The Line chart is ideal for illustrating trends overtime. Pie Chart Pie charts show the relationship of a part to a Whole. Each part is shown as a “slice” of the pie.

  27. Inserting Charts Scatter Chart It is also called XY chart. It shows the relationship between two categories of data. One category is represented on the vertical (Y) axis, and the other category is represented on the horizontal (X) axis. Connecting the data points with a line is not practical because points on a scatter chart usually do not relate to each other, as they do in a line chart. Creating Chart 1. Highlight the data from the worksheet that is to be included in the chart. 2. Choose chart on the insert menu or click the chart Wizard buttonon the toolbar.

  28. 3. Chart Wizard dialog box will appear; select the type of chart that you want to use. Then, click OK. 4. It will appear on a separate sheet from which you can edit its values. And there goes your chart.

  29. Activity 28 1. Open the file exceldata27 in your diskette. 2. Insert a chart using the data from cell A2 – B7. Chart Type = Pie Data Range = Series in Columns Chart Title = 1st Quarter Grades Data Label = Put check mark on Value and Category Name choose as object in Sheet 1 3. Save your file as Activity 28.

  30. Customized Charts Combination of Charts Excel offers a number of combination charts from the Custom Charts tab of the Chart Wizard. Charts that can be customized need to be arranged in order on the worksheet. This will be illustrated on the sample below. To create a combination chart: 1. Input the data. 2. Highlight the information and click On Chart Wizard button.. 3. Choose the Chart Wizard options to convert the data into a line chart. 4. Select any line on the chart (for this example choose Line A).

  31. 5. Select any line on the chart (for this example choose Line A).

  32. 5. Right click on the selected line and select Chart Type from the shortcut menu. 6. Select the type of chart from the Chart Type dialog. 7. Click Ok.

  33. Customized Charts Creating a Pictograph A pictograph is a graph that uses pictures to show and compare information. Excel stretches or stacks the picture to represent the values being graphed. To create a pictograph: 1. Create a spreadsheet to represent your data. 2. Highlight the information and click on the chart Wizard button. 3. Choose the Chart Wizard options to convert the data into bar chart. 4. Right-click the bar representing a data point. Then click format Data point. This will display the Format Data Point dialog. 5. On the dialog box click on Fill Picture Or texture fill. 6. Select the File... tab. 7. Choose picture and CilckInsert.

  34. 8. Repeat steps 4 to 7 for the remaining bars on the chart.

  35. Activity 29 Activity Part I 1. Create the following table. 2. Convert the data into a combination chart. 3. Save the workbook as Activity 29a.

  36. Activity Part II 1. Create the table (Fig. C). 2. Create a pictograph based on the given data. Yor output should be similar to the figure below. 3. Save your work as Activity 29b.

  37. Advanced Formatting Conditional Formatting Conditional formatting allows you to set rules or conditions in formatting the contents of the cells. If the cell contents meet the condition. The conditional formatting will be applied; otherwise, cell contents will use the general formatting of the worksheet. You can have up to 3 conditions set for each selected cell or group of cells. To add conditional formatting to the cell or group of cells: 1. Select the cells to be formatted. 2. Choose Conditional Formatting Highlight Cells Rules More Rules command.

  38. 3. Set the condition for formatting by selecting options under New Formatting rule. 4. Click the Format button, and select the formatting options you want to apply when the contents of the cell meets the condition from the Format dialog. 5. Repeat steps 3 & 4. 6. Click OK to return to the worksheet.

  39. Inserting Hyperlink Inserting Hyperlink A Hyperlink is an object that represents a link to another location in the same file or different file, and that, when clicked, bring up another page or a document. In Excel, you can insert hyperlink to text or graphic. To illustrate the steps in inserting hyperlink, FRUIT.XLS should be opened. To add a hyperlink: 1. Select the text or graphics. 2. Click on Insert Menu Hyperlink. This will open the Edit Hyperlink.

  40. 3. From the links option select Place in this document. 4. Type the cell reference or select from a predefined cell reference on the list. 5. Click on OK. 6. Click on the hyperlink to test if the link is working.

  41. Activity 30 Part I 1. Create and format the worksheet shown below based on the conditions found on the format table. 2. Save the worksheet as Activity 30a.

  42. Part II 1. Open a new workbook that contain the following worksheets: 2. On the YBG worksheet, add hyperlink to the color names. 3. Save the workbook as Activity 30b.

  43. Managing Data Microsoft Excel has a number of features that make it easy to manage and analyze data in a list. Sorting, filtering and validating data are some of the features Excel can provide to help you manage the data. In order to apply the features easily, the following guidelines in creating a list must be observed: • Use only list per worksheet • Put similar items in one column • Show rows and columns • Use formatted column labels • Use cell borders • Avoid blank rows and columns • Do not type leading or trailing spaces • Extend list formats and formulas

  44. Sorting Data Sorting arranges alphabetically or numerically the sequence of data in ascending or descending order. Rows of data are arranged based on the values of the column that is used as sorting column. To sort data using one column As the sorting key: 1. Click a cell in the column you would like to sort by. 2. Click or To sort data based on two or more columns: 1. Select all the cells in the list. 2. Choose Data Sort. 3. From the Sort by dropdown, select the first column.

  45. 2. Click the Data Filter AutoFilter command. You will notice that boxes with triangles appear next to each of the column headings of the list. 3. To use the filter, click on the box on the heading and choose the category you would like.

  46. 4. Select to sort in Ascending or Descending order. 5. From the Then by dropdown, select the second column and specify the order. 7. Click OK when done. Using AutoFilter A filter is a data management tool that is used to limit the data that will be displayed on the worksheet without changing the content of the list. Data that do not meet the filter conditions will be hidden from the list. In Excel, there are two techniques in applying a filter: AutoFilter and Advanced Filter. This topic will only cover the AutoFilter. To apply an AutoFilter: 1. Highlight the entire list including the column labels/headings.

  47. Data Validation Validation sets up certain criterion that must be met before data can be entered into the worksheet. It is a tool that helps you control the kind of information that is entered into the worksheet. With data validation, you can restrict cell entries to a specific type or size or provide users with a list of predefined acceptable data values. To apply data validation: 1. Select the cell or group of cells where validation will occur. 2. Click on Data Validation command. 3. Select the validation criteria by selecting the Allow Option of the Settings tab. Each Allow option Contains options to set the validation criteria.

  48. The example below utilizes the list Allow option. a. Enter the items for your drop-down list into a list in the worksheet. In this example, the day names start in cell A1 and extend to A7, but they can be in any out-of-the-way location on the worksheet. b. Select the cell that will contain the drop-down list. c. Apply data validation based on this list.

  49. d. Click the Settings tab. e. In the Allow field, select List. f. In the Source field, specify the range that contains the list items. g. Click OK. 4. Specify the Input Message that will appear every Time you click on the cell with data validation. This step is optional. 5. Specify the error message that will appear if the Inputted data do not meet the validation criteria.

More Related