MS EXCEL is a spreadsheet application

1. MS EXCEL is a spreadsheet application Excel covers: Calculation Graphic tools Pivot tables Macro programming language called VBA EXCEL is a part of MS Office.

2. SOME DEFINITIONS Spreadsheet: A document that stores data in a grid of horizontal rows and vertical columns. Worksheet: Excel spreadsheet Workbook: contains one or more worksheets • A spreadsheet is divided into rowsand columns. • Each row and column is assigned a heading. • Rows are given numeric headings ranging from 1 to 65,536. • Columns are assigned alphabetic headings from column A to Z, then continue from AA to AZ, and then from BA to BZ and so on.

3. What is a cell? Cell - the intersection of a row and column form a cell. Active cell – the cell shown in black borders and in the formula bar Cell contents - actual entry of a cell containing formulas (opposite of displayed value) Cell reference - each cell has a unique address or reference such as A9 (cell located at column A and row 9). Column heading always precedes the row heading in the cell reference.

4. Sample Cell references A10 -> The cell in column A and row 10 A10:A20 -> The range of cells in column A and rows 10 through 20 B15:E15 -> The range of cells in row 15 and columns B through E 5:5 -> All cells in row 5 5:10 -> All cells in rows 5 through 10 H:H -> All cells in column H H:J -> All cells in columns H through J

6. Quick Access Toolbar - allows you to add frequently used commands. Click on the down arrow at the end of the toolbar to display the toolbar's options. Ribbon - The Ribbon is the strip of buttons and icons located above the work area. The Ribbon is organized into a series of tabs - such as File, Home, and Formulas. Each tab contains a number of related features and options. Formula Bar - displays the contents of the active cell. It can also be used for entering or editing data and formulas. Name Box -displaysthe cell reference or the name of the active cell. Sheet Tabs - By default there are three worksheets in an Excel file. Switching between worksheets can be done by clicking on the tab of the sheet you wish to access. ScreenTips appear when the mouse points to a button

7. Cell Contents Constant - an entry that does not change and may be numeric or descriptive text Formula - a combination of numeric constants, cells references, arithmetic operators, and/or functions that produce a new value from existing values. Every formula should start with an equal sign =. Order of computation in formulas: • Functions • Parentheses • Exponentiation (^) • Multiplication (*) or Division (/) • Addition (+) or Subtraction (–)     Order of evaluation is from left to right among equal precedence operations

8. Common Errors • What does the error ##### mean? * The numeric value entered into a cell is too wide to display within the cell. You can resize the column by dragging the boundary between the column headings. *  The formula in the cell produces a result that is too long to fit in the cell. You can increase the width of the column by dragging the boundary between the column headings or by changing the number format for the cell. To change the number format, click Cell on the Format menu, click the Number tab, and then select another format.

9. ERRORS CONT. • What does the error #VALUE! mean? * The #VALUE! error value occurs when the wrong type of argument or operand is used, or if the Formula AutoCorrect feature cannot correct the formula. • What does the error #DIV/0! mean? * The #DIV/0! error value occurs when a formula divides by 0 (zero).

10. FILL HANDLE Fill handle is a tiny black square that appears in the lower-right corner of the selected cell(s). To use the fill handle, you • Select the cell(s) to be copied. • Point to the fill handle for selected cell(s). • Click and drag the fill handle over the destination range. A border appears to outline the destination range. • Release the mouse to complete the copy operation.

11. SUM FUNCTION SUM: Adds all the numbers in a range of cells. Syntax:SUM(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want the total value or sum.

12. AVERAGE FUNCTION AVERAGE: Returns the average (arithmetic mean) of the arguments. Syntax:AVERAGE(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want the average.

13. MAX FUNCTION MAX: Returns the largest value in a set of values. Syntax: MAX(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want to find the maximum value.

14. MIN FUNCTION MIN: Returns the smallest value in a set of values. Syntax: MIN(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want to find the smallest value.

15. COUNT FUNCTION COUNT: Arguments that are numbers, dates, or text representations of numbers are counted; arguments that are error values or text that cannot be translated into numbers are ignored. Syntax: COUNT(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want to count.

16. COUNTA FUNCTION • COUNTA: Counts the number of cells that are not empty Empty text “ “ is counted. Syntax: COUNTA(number1,number2, ...) • Number1, number2, ... are 1 to 30 arguments for which you want to count.

17. COUNTIF FUNCTION • COUNTIF: Counts the number of cells in a given range that meets a given criteria. Syntax: COUNTIF(range, criteria) eg. =COUNTIF(E2:E26,"A")