CSM 183 Excel Introduction to Computers PDF

CSM 183 INTRODUCTION TO COMPUTERS PART III: EXCEL GETTING STARTED WITH EXCEL  Applications that form the Microsoft Office suite are  Microsoft Word (a word processor for creating professional documents),  Microsoft Access (database management application for creating and maintain...

CSM 183 INTRODUCTION TO COMPUTERS PART III: EXCEL GETTING STARTED WITH EXCEL  Applications that form the Microsoft Office suite are  Microsoft Word (a word processor for creating professional documents),  Microsoft Access (database management application for creating and maintaining databases), 2  Microsoft PowerPoint (presentation software),  Microsoft Outlook,  Microsoft Publisher and  Microsoft Excel. The Excel is a spreadsheet application. 3  SPREADSHEETS AND THEIR USES A spreadsheet (also known as an Electronic Spreadsheet) is a piece of software (computer program), which is simply the electronic equivalent of the accounting worksheet.  Both consist of rows and columns.  The intersection of the rows and columns are used to store numbers and text  and is capable of a wide range of manipulations from simple arithmetic calculations such as totals, products or percentages, to complex automated calculations and analysis. 4  SPREADSHEETS AND THEIR USES Spreadsheets are widely used in research, industry and business for storing, manipulating and comparing data and for planning and forecasting. Below are some of the major areas that spreadsheet can be used  1. In Accounting field:- financial and other non financial institutions such as KNUST, CSIR, SSNIT, Bank of Ghana, etc. can use spreadsheet for the following: 5  SPREADSHEETS AND THEIR USES I. Preparation of budgets, Balance sheet, Trial Balance, analysis of cash flows, costing projects, managing inventory, payroll, and other financial plans involving income and expenditure. II. Discounts, Loan, taxation, investment, interest and interest rate calculations. III. For predicting or forecasting into the future. IV. Investment proposals, and many other tasks. 6  SPREADSHEETS AND THEIR USES  2. Scientific environment: Scientist such as Mathematicians, Engineers, Physicists, Chemists, meteorologists, statisticians, biologist, etc. use spreadsheets to perform statistical computations such as averages, standard deviations, variance, R-Squared, etc.  They can also use spreadsheet to calculate regression coefficients, perform analysis of variance (ANOVA), to solve simultaneous equations (i.e. systems of linear equation), to build frequency distribution tables, etc 7 SPREADSHEETS AND THEIR USES  3. Graphical Representation: Spreadsheets are used in many disciplines to represent data graphically for easy analysis such as Pie chart, Histogram, line graphs, etc.  For example, hospitals, statistical division, etc. can use spreadsheet to represent information such as mortality values, morbidity values, the trend of cases with respect to an outbreak of a disease, etc. in graphical forms. 8 SPREADSHEETS AND THEIR USES  4. Forex bureau and other multi-national companies use spreadsheets for their currency conversions. For example, using a spreadsheet it is possible to convert from one currency to another without having to use any calculating device. 9  SPREADSHEETS AND THEIR USES  5. Database management: Businesses and individuals can use spreadsheets for maintaining their data base. It allows them to perform operations such as sorting the data in their database, extracting information/records from a given database, etc. Spreadsheet can also be used in the preparation of pay vouchers, etc. 10 Spreadsheets  Spreadsheets are particularly good at providing answers to the “what if …?” type of questions which occur very frequently in business.  For example builders or architects may use a spreadsheet to assist in the design of a storm drain. In this case, they would want to know the answer to a question such as “what if we change the diameter of the storm drain, how will that affect the rate of flow of water carried along it? 11  Spreadsheets  Also, a business may want to know what will happen to profit if income and expenditure values over a certain period increase or decrease. Will the architect, builders or businesses have to perform all the calculations again? The simple answer is NO. Whenever there is a change in one value Excel for example will automatically update or recalculate all values that are dependent on the changed value.  In this way a spreadsheet can be used not only for creating financial statements or budgets but also as a planning tool. 12 Spreadsheets Packages  There are a number of spreadsheets packages one can choose from  AS-EASY-AS,  SUPERCALC,  LOTUS 1-2-3,  SYMPHONY,  QUATROPRO,  FRAMEWORK IV,  EXCEL, etc 13 Spreadsheets  The term spreadsheet is often used to refer to the computer program. Sometimes the same term is used to refer to the sheet on which the work is done. Some spreadsheet programs use the term “worksheet” for this 14  Overview of Spreadsheet Programs Cell Address Cell Workbook Cell Pointer Status bar Column Working Sheet Row  STARTING AND QUITTING EXCEL To start the Excel application, follow these steps. I.Click the Start button on the taskbar at the bottom left of the screen. 16  STARTING AND QUITTING EXCEL II. Scroll through the programs to find Excel and click on it to open. 17 STARTING AND QUITTING EXCEL  Another way you can start Excel is from a shortcut icon on the desktop. Double-click the EXCEL short-cut icon on the desktop. When Excel starts you will obtain the following initial screen. 18 STARTING AND QUITTING EXCEL 19  STARTING AND QUITTING EXCEL  To Close or quit Excel in order to free memory for other applications or at the end of the day when you have finished working, follow these steps I. Make sure your work is saved, click on the close button the title bar. 20 Cells A cell is always referred to by using the row and column heading. For example, the cell at the intersection of column B and row 10 has the cell reference B10. The cell reference of the active cell is displayed in the name box at the left of the formula bar. 21  Working in a Spreadsheet To work with a spreadsheet, you enter data in the cells of the spreadsheet.  You enter data by clicking a cell and typing the data.  To replace data in a cell, you click the specific cell and type the new data.  Working in a spreadsheet (cont.) You can enter three types of data in a spreadsheet:  Text: Text data has no numeric value associated with it.  Numbers: A number has a constant numeric value, such as the test scores attained by a student.  Formulas and functions: Formulas and functions are mathematical equations. Enter Data To ENTER data:  click on the cell  type information  press ENTER. The data can be both number and text.  ENTERING TEXT AND NUMBERS You can type as many as 255 characters in a cell. If a cell is not wide enough, all characters may not be displayed if the cell to the right contains data. With the General format, the text automatically aligns on the left side of the cell. 25  ENTERING TEXT AND NUMBERS  You can make Excel accept a number as text by typing an apostrophe (‘) followed by the number e.g. ‘25,000. You may also enter numbers as text by placing an equal sign in front of the number and enclosing the number in quotation marks e.g. = “25,000”. 26 ENTERING TEXT AND NUMBERS  In order to display a number that exceeds the cell width, it may be necessary to have the number displayed as text else the cell will be filled with # signs or in some cases may be displayed in scientific notation.  E.g. 2.17E+09. Note, however, that you may not perform any arithmetic operation with text values. 27  ENTERING TEXT AND NUMBERS  Numbers are constant values containing only the following characters: 1 2 3 4 5 6 7 8 9 0 - + /. Ee  When a number is entered, Excel stores both the number typed into a cell and the format (or appearance) in which the number should be displayed (i.e. the format).  InitiallyExcel tries to establish how the number should be formatted. 28  ENTERING TEXT AND NUMBERS  Apart from the digits 0 to 9, none of the other characters can appear more than once in a given number. For example 2.4e4e2 and 2.3.3 are invalid numbers and Excel treat them as text and cannot therefore be used in computations. 29  ENTERING DATES AND TIMES  Excel recognizes dates and times typed in most common ways.  When you type a date or time, Excel converts your entry to a serial number.  This enables Excel to do date and time calculations.  For a date, the serial number represents the number of days from the beginning of the 20 th 30 century (i.e. 1/1/1900) until the date you type.  ENTERING DATES AND TIMES  For example, the date 1/1/1902 is represented as 732 and that is the number of days since 1/1/1900.  Time is displayed as a decimal fraction of a 24-hour day.  Correctlyentered dates appear in the formula bar with the format m/d/yyyy, regardless of how the cell is formatted. 31  ENTERING DATES AND TIMES To format a date in the default date format, select the cell containing the date and press Ctrl + # To format a time in the default format press Ctrl+ @  To quickly enter the current date in a cell press Ctrl+; To quickly enter the current time in a cell press Ctrl+: 32  ENTERING DATES AND TIMES A valid date entry in an unformatted cell is aligned, as a number, to the right. If the cell has been previously formatted with a numeric format, it appears as a serial number. 33  ENTERING DATES AND TIMES  To enter a date, type the date into the cell with any of these formats. Format Example m/d/yy 7/8/97 dd/mm/yy 07/08/97 d-mmm-yy 8-July-97 m/d/yy h:mm 6/8/97 09:45 mmm-yy July – 97 mmmm d, yyyy July 8, 1997 34 ENTERING DATES AND TIMES  Times may be entered in the following formats. Format Example h:mm 13:32 h:mm:ss 13:32:45 h:mm AM/PM 1:32PM h:mm:ss AM/PM 1:32:45PM m/d/yy h:mm 6/8/97 13:32 mm:ss 45:15 [h]:mm:ss 21:45:15 35 Cutting & Pasting data  Cutting & Pasting data (cont.)  To COPY contents of a cell: Click on the cell, Select the Home tab, Click Copy from the Clipboard Group or Press Ctrl+C on your keyboard  ToPASTE contents of a cell: click on the cell, Select the Home tab, click Paste from the Clipboard Group or press Ctrl+X on your keyboard.  Selecting cells To select a range of cells in a column/row, click the left mouse button in a cell & drag the mouse pointer to highlight the cells of your choice. Adding rows & columns To INSERT a Row/Column: – Select the row/column heading, – Click the Home Tab, – Click the Insert button from the Cells Group. The insertion occurs before the selected column/row. Deleting Rows and Columns  To delete a column/row:  click the column/row heading click the Delete button on the Cells Group of the Home Ribbon. Assignment 1  Auto complete  Auto fill  Fill series  Finding the right size  You can expand the width of a column or the height of the row to increase the visible space in each cell.  To begin changing the width of the column, move the mouse pointer over the right edge of column heading until the mouse pointer changes to a double-headed arrow.  Drag along to the desired size Inserting a chart  Formulas Formulas are the core of an Excel worksheet.  A formula may be defined as any data that does not place itself in a cell but rather the result that it generates. It may generate either a value or a label.  Formulas are used to do all the calculations that we use to do by hand or with calculators. Without formulas, there would be no point to using an electronic worksheet such as Excel. 44  Formulas  Formulas may be used to do simple calculations involving addition, subtraction, multiplication and division, as well as to carry out very complex financial, statistical or scientific calculations. When a formula is entered in a worksheet cell, the result of the formula usually appears on the worksheet. 45  Formulas To view the formula that produces the results, select the cell and the formula appears in the formula bar. If you wish to view the formula in-cell, double- click the cell or select the cell and press F2. 46 A formula in Excel Must start with either of the following symbols = , + or -. Note that starting a formula with the minus sign cause the value of the address or the constant that the minus sign precedes to be negated. For example, -B3+B4 is a valid formula. If B3 and B4 contain 10 and 15 respectively then the above formula will give 5 as the result of the current cell. 47 Allows the use of mathematical operators such as + (addition), - (subtraction) , * (multiplication) and ^ (exponentiation), etc Allows the use of relational operators such as >, =, etc Can be a valid mathematical expression or built-in function. E.g. COS(20). Generally, formulas in Excel always begin with an equal sign (=) and can include numeric and text values (constants) arithmetic operators, text operators, functions, parentheses, cell references, and names. 48 Why use formulas? One of the advantages of using formulas in a worksheet is that cell entries can be changed and the formula will automatically recalculate the values and insert the result in the cell containing the formula. 49  CELL REFERENCING Cell references, also called addresses, are used in a formula to refer to the contents of a cell or a group of cells. Cell references allow you to use values from different parts of a worksheet and execute a desired calculation 50 Relative, Absolute and Mixed References  A reference identifies a cell or a range of cells in a worksheet.  There are basically three main types of addressing namely ABSOLUTE, RELATIVE and MIXED addressing.  There is also a fourth type CIRCULAR ADDRESSING which is not permitted in Excel although its use would not force Lotus to signal an error. 51 Relative, Absolute and Mixed References Excel treats cell references in formulas differently when they are copied from one cell to another and hence the need to have a good understanding of the different addressing.  Relative cell references refer to cells relative to a position in a formula. 52  Absolute references refer to cells in a specific location. Absolute, relative or Mixed referencing  A relative cell reference adjusts when a formula is copied while an absolute cell reference remains constant when a formula is copied.  A mixed cell reference does both – either the column remains absolute and the row is relative or the column is relative and the row is absolute.  An absolute reference has the dollar sign before the column and/or row cell reference while a relative 53 reference has none. Relative References  This is a cell or range address in a formula that Excel interprets by virtue of its location relative to the cell that contains the formula.  When a formula containing relative addresses are copied from one cell to another, Excel copies the relationship between the cells/ranges in the formula in terms of their positions relative to the cell that holds the result, and then adjust the cell addresses in the copied formula such that the relationship is maintained.  Unless you specify otherwise, Excel uses relative referencing 54 for cell addresses when you enter a formula. Absolute Referencing  As mentioned above, by default Excel treats all formulas to contain relative addresses unless either the column letter, row number or both in a cell address is/are preceded by a dollar sign.  Absolute addresses are identified by having a dollar sign preceding a column letter and a row number of a cell address.  Absolute addressing is used to express permanent link to the values of columns and/or rows.  A permanent link means should the content of a cell with a formula containing absolute addresses be copied, the absolute cell addresses in the formula should 55 not be updated to reflect the new column and/or row Absolute Referencing  E.g. $A$5 indicates a permanent link to the cell A5 (i.e. a permanent link to Column A row 5 or to cell A5).  Example, if cell F4 contains the formula =SUM (B4:D4) * $D$4 and if we copy the formula to cell F5 we will obtain =SUM (B5:D5) * $D$4 56 Absolute Reference  You enter an absolute reference by typing the dollar sign in front of the row or column that you want to remain the same  or by pressing the F4 key when the flashing insertion point in the formula bar has been placed at where the cell reference is to be made absolute.  Each time F4 is pressed, the type of reference changes, from eg. 57 A10 to $A$10, A$10, $A10, A10. Mixed References  On some occasions you want only the row to stay fixed or only the column to stay fixed when copied.  In this case we use mixed referencing. Mixed addressing combines relative and absolute addressing, that is either the column or the row number is preceded by a dollar sign.  Thus, $G5 or G$5 is a mixed addressing. The former implies column is linked permanently to Column G whiles the row is relative to row 5 and the latter also implies that the row is linked permanently to row 5 while the column is relative to column G. 58 Mixed Referencing  Therefore, when a formula containing a mixed address is copied from one cell to another all the absolute addresses would remain the same while the relative part would be updated.  For example the formula = $A4 * B$3 has two mixed references. $A4 has an absolute column and a relative row while B$3 has a relative column and an absolute row.  Mixed cell references allow you to fill in column and row data 59 using only one formula.  Circular references  Circular addressing involves the use of the current cell’s address in the formula for the current cell either directly or indirectly.  That is defining a formula for the current cell by including the address of the current cell in the formula or simply put defining a cell in terms of itself. An example is =(+B4-B3)/B2 when the current cell is either B2, B3 or B4.  For example if the content of cell D3 is (D1+D2-D3)/3, then the result of the formula in D3 will keep on changing whenever data is stored in a cell. This situation may be useful a times but unless it is intended, try and avoid circular referencing 60  3-D References  You can use 3-D references to refer to a cell range that includes two or more sheets in a workbook.  A 3-D reference consists of a sheet range specifying the beginning and ending referred to.  For example, =SUM(Sheet1:Sheet4!$D$1:$D$10)  SUMS up the values in the range of cells $D$1:$D$10 in each of the sheets from sheet1 to sheet4 and adds the SUMS together resulting in a grand total. 61 Operators in Formulas Arithmetic operator Meaning Example + (plus sign) Addition A2+3 – (minus sign) Subtraction C3–A3 Negation –B4 * (asterisk) Multiplication A4*8 / (forward slash) Division B4/B3 % (percent sign) Percent 95% ^ (caret) Exponentiation) 4^3 (which gives 64) 62 Comparison operators These are operators that make it possible for you to compare two values. When two values are compared, the result is a logical value and that can be either True or False. The comparison operators are as follows Comparison operator Meaning Example = (equal sign) Equal to A3=C4 > (greater than sign) Greater than A4>C5 < (less than sign) Less than A4= (greater than or Greater than or equal to A3>=B4 equal to sign) D1) is true. The AND function. This joins test conditions. It returns TRUE if all logical argument are TRUE, and FALSE if any logical argument is FALSE. Logical arguments include statements such as C10> 20 or A4 + A10 = 16 Format is AND (logical1 , logical2 …) e.g. if C1 = 10, D1 = 16 and E1= 20 Then AND (C1 >D1, E1>10) will return FALSE since the first logical argument (C1>D1) is false. Both logical arguments (or conditions) must be true for the statement to be TRUE. 86 The OR function The function joins test conditions like the AND function but returns TRUE if one or more logical arguments is TRUE, and FALSE only if all logical arguments are FALSE. Format is OR (logical 1, logical 2,..) E.g. If C1 =10, D1 = 16 and E1 =20 Then OR (C1>D1, E1>10) will return TRUE since at least one logical argument is TRUE. 87 The IF function This function permits you to test a logical condition to determine the appropriate value for a cell. Format is: IF (logical test, value-if-true, value if false) ARGUMENTS Logical test – this is any logical expression that can be evaluated as true or false, e.g. A1=B10, A1= “ PASS”, A1> =4, B10-C51000000, 88 or NOT(T1 60).  Value-if-true - This is the value the cell containing the IF function will assume if the condition is true. This can be a cell reference, a value or text (which should be enclosed in double quotation marks) e.g. A5, “GHANA”, 200.00.  Value-if-false - This is the value the cell containing the 1F function will assume if the condition is false. All conditions listed under value if true also apply here. 89 EXAMPLE Suppose cell B10 contain the logical function below = IF (F1 > 40, (F1-40)*1.5,0) And assuming cell F1 contains the hours worked by an employee for the week, then the above means “if the hours worked for the week is greater than 40, subtract 40 from the hours worked and multiply the result by 1.5, putting the result in cell B10. If the hours worked for the week is not greater then (i.e. less than or equal to) 40 then cell B10 should contain 0. 90 One can also have nested conditions such as = IF (TYPE = 1, IF (SALE> 1000000, 0.08, 0.05), IF SALE>2500000,0.06,0.03)) Assume the above logical condition is in cell A1 and represents Discount. The above means that: If Type is 1 And If SALE is greater than 1,000,000 Then Discount = 0.08 Else Discount = 0.05 Else (i.e. if Type is not 1) And If SALE is greater than 2,500,000 91 Then Discount = 0.06 Else Discount = 0.03 92 Exercise 1 Employees of KNUST Computer Company are paid on hourly basis at the end of every month. If an employee works for not more than 40 hours a month, it is considered regular and Overtime for hours worked in excess of 40. Regular hours are paid at 50 cedis per hour while the overtime rate is one and half times the regular rate per hour. All employees are to pay 15% of their gross pay as Income Tax, 2.5% as National Health Insurance Levy, 1% as District Tax 93. Employees who have more than three children are to pay 1 cedi per child in excess of three towards GetFUND. Assume you have just been employed by the above company and that you have been given the spreadsheet below to complete. 94 95  What formulas will be needed in the following cells such that they can later be copied into other cells to complete the worksheet. The percentage tax rate for income, district and NHIL are to be picked from the spreadsheet. Note, your formulas should be such that whenever the income tax rate, the district tax rate and the NHIL rate change, only their corresponding new values have to be entered to replace the exiting ones in cells I2, I3 and I4 respectively, and that there will be no need to re-copy any formula from one cell to another for subsequent changes in the rates. 96 (i) E7 (ii) F7 (iii) G7 (iv) H7 (v) I7 (vi) J7 (vii) C23 through to J23 Exercise 2 A Social Science lecturer marked her mid semester and end of semester examination scripts each over 100 instead of 30 and 70 respectively. After marking she decided to use Excel to compute the final marks of the students and also indicate the appropriate letter grade of every student to determine how many students scored each of the letter grade. The following is the spreadsheet that she has managed to create. Her problem now is to put in formulas to complete her work. 97 98 Assume this lecturer wants you to help, what formulas will be required into the following cells: (i) E6 (ii) F6 (iii) C23 (iv) C24 (v) C25 (vi) C26 (vii) C27 (viii) C28 (ix) C29 (x) C30 99 How to Print Spreadsheet Data To print a spreadsheet, click the File Tab, point to Print, and then click Print. To specify the pages that you want to print, in the Print dialog box, under Print range, in the From and To boxes, type the pages that you want to print. SORTING  Oneof the common data processing operations that is performed by the computer is SORTING  Sortingis the process of arranging data in numerical or alphabetical order. The order of the arrangement may be ascending or descending order 101 SORTING  Tosort, each row of a worksheet form a record (information about a particular person, city, products, etc) and each column forms a field.  Let us consider the following worksheet 102 Sorting 103 Sorting  Let us sort the records into ascending number of index numbers  The needed steps will be as follows: I.. Select cells B6 to M30 II.. From the Home menu, select Sort & Filter from the Editing group. You will obtain the following figure III.. To sort into ascending order of the index no Select Sort A to Z and into descending order you will select Sort Z to A 104 Sorting  Assume you selected from step 3 Sort A to Z then your worksheet will appear as follows: 105 Sorting  Let us assume we now want the results to be displayed in order of merit, that is into descending order of SWA  The needed steps are as follows: I. Select cells B6 to M30 106 Sorting II. From the Home menu, select Sort & Filter from the Editing group. You will obtain the following figure III. Since the column the sorting is based on is not the first column in the range of cells selected, you can neither use Sort A to Z nor Sort Z to A but rather Custom Sort 107 Sorting  You will obtain the following sort dialog box 108 Sorting IV. Pull down the Sort by and select Column M (the SWA column). V.. Pull down the Order and select Z to A (or Largest to Smallest). This depends on how Excel treats some of your numeric data) since we want the records to be sorted into descending order of the SWA VI. Click at the OK button and your worksheet will appear as follows. 109 Sorting 110 Sorting Data based on more than one Column  It is possible to sort data based on two or more columns. The steps involved are not much different from sorting based on a single column  Let us assume that we want to sort the above worksheet on descending order of CSM 203 within ascending order of CSM 201 111 Sorting Data based on more than one Column  This means we want to first sort the data into ascending order of CSM 201 and when two or more of the CSM 201 marks are the same, those that are the same should be sorted into descending order of CSM 203  In this case the steps needed are as follows 112 Sorting Data based on more than one Column I. Select cells B6 to M30 II. From the Home menu, select Sort & Filter from the Editing group. III. Select Custom Sort  Complete the Sort dialog box as follows: 113 Sorting Data based on more than one Column  Note that the Then By line will only show when you click at the Add Level button. Your worksheet will now appear as follows: 114 Sorting Data based on more than one Column  As can be seen from the above worksheet the sorting was based on the CSM 201 marks.  Looking at where two candidates had the same mark in CSM 201, , their records are sorted based on their marks in CSM 203  Those with serial numbers 17 and 18 marks this quite clear as the others appear to have been based on the index numbers and not their marks in CSM 203 115 Filtering  Filteringis a process of displaying only the rows of data that meet a specified criteria  Itcan also be define as the conditions you specify to limit which records are included in the result set of a query  After you have filtered data, you can copy, find, edit, format, chart, and print only the filtered data without rearranging or moving it 116 Filtering  You can also filter by more than one column  Using the AutoFilter, you can create three types of filters: by a list values, by a format, or by criteria  For example, it is possible to filter by cell color or by a list of numbers, but not by both; you can filter by icon or by a custom filter, but not by both. Let us consider the worksheet above and do some filtering 117 Filtering  Example 1: Let us assume that we want the records of only students who failed CSM 203 so that they can be advised to work hard by the Examiner  Tofilter only this group of students the steps needed are as follows: 118 Filtering I. Preferably, select the cells that are just above the first record of the students. If this is not done in our example, you will not be able to filter and hence why it is suggested here to select the cells just above the first record II. From the Home menu, select Sort & Filter from the Editing group. You will obtain the following figure 119 Filtering I. Select filter to obtain the following screen: 120 Filtering IV. Since we want to filter those who failed at least a course, pull down the down arrow key in cell D6 and move the mouse pointer over Number filter V. Select Less Than and you will obtain the following screen: 121 Filtering 122 Filtering VI. Since a mark of 40 is failed, enter 40 as shown in the above figure and click ok. By that we are asking to show only rows where the mark is less than 40. The screen obtained will be as follows:  Notethat we have shown those who failed in CSM 203 123 Filtering 124 Assignment  Protecting your worksheet