Microsoft Excel and Spreadsheet Software

INTRODUCTION TO SPREADSHEET/ MS EXCEL by Mwalugeni, Benjamin

Introduction to Spreadsheets Microsoft Excel 2010 is the spreadsheet program in Microsoft Office 2010. A spreadsheet is a grid of rows and columns in which you enter text, numbers, and the results of calculations. In Excel, a computerized spreadsheet is called a worksheet. The file used to store worksheets is called a workbook. 2

3 Starting Excel You start Excel from the Start menu in Windows. Click the Start button, click All Programs, click Microsoft Office, and then click Microsoft Excel 2010. The Excel program window has the same basic parts as all Office programs: the title bar, the Quick Access Toolbar, the Ribbon, Backstage view, and the status bar.

Starting Excel (continued) Excel program window

5 Exploring the Parts of the Workbook Each workbook contains three worksheets by default. The worksheet displayed in the work area is the active worksheet. Columns appear vertically and are identified by letters. Rows appear horizontally and are identified by numbers. A cell is the intersection of a row and a column. Each cell is identified by a unique cell reference. 5

6 Exploring the Parts of the Workbook (continued) The cell in the worksheet in which you can type data is called the active cell. The Name Box, or cell reference area, displays the cell reference of the active cell. The Formula Bar displays a formula when a worksheet cell contains a calculated value. A formula is an equation that calculates a new value from values currently in a worksheet. 6

Overview of Spreadsheet Programs

Screen Components The opening screen for Microsoft Excel 2010 looks like this…

While different versions have different appearances, they all have most of the same features. If you know what to call it, you should be able to find it in other versions. Terminology

The area outlined in red is called the title bar. It displays the names of the open program (in this case Microsoft Excel) and the name of the current file.

This file has not yet been saved so its name is Book2. Files created in Microsoft Excel are often referred to as spreadsheets or workbooks and have the file extension .xls or .xlsx

The area outlined in red contains the minimize, maximize/restore and close buttons for the program window.

These three buttons are on almost every window that opens in a Windows based platform. They are on Mac windows as well, but they are circles instead of squares.

When you click the minimize button the program becomes a button on the Windows taskbar located at the bottom of the screen.

You can restore the document to its original shape and size by either: Clicking on the button on the task bar one time to restore it to active mode,

If you have multiple files from the same program open you will need to select the one you want to restore to active mode.

When you click the maximize / restore button the program assumes the same shape and size it was before you minimized it. Or The program window will fill the screen.

When you click the close button the program will ask you if you want to save the changes if you have made any changes. Once you have responded to this question the program will close.

The area outlined in red is called the quick access toolbar. It contains the most commonly used commands in Microsoft Excel: 1. Save 2. Undo 3. Repeat

Microsoft Office 2007 & 2010 use what is referred to as the “Ribbon” interface. The area outlined in red is the Ribbon. The ribbons we are going to go over today are the default ribbons.

You may customize the ribbon and or a group on the ribbon on your personal computer to have only the features you want to use. In order to do this all you have to do is right mouse click on the ribbon or the group you want to customize.

The words File, Home, Insert, etc… outlined in red are referred to as tabs. Each Tab has several Groups attached to it.

The area outlined in red is referred to as the worksheet and most of its components remain the same regardless of which tab you are currently using.

This slide is mainly visual.

The File Tab menu contains the commands most commonly associated with the file.

The Home Tab Groups contain the commands most commonly associated with the formatting and editing of cells and their contents. Clipboard Font Alignment Number Styles Cells Editing

The Insert Tab Groups contain the commands most commonly associated with adding something to the document. Tables Illustrations Charts Sparklines Filter Links Text Symbols

The Page Layout Groups contain the commands most commonly associated with settings that would affect the entire page or worksheet. Themes Page Setup Page Background Paragraph Arrange

The Formulas Groups contain the commands most commonly associated with Excel’s formulas and functions. Function Library Defined Names Formula Auditing Calculation

The Data Tab groups contain the commands most commonly associated with data from external sources, and evaluating or arranging data. Get External Data Connections Sort & Filter Data Tools Outline

The Review Tabs groups contain the commands most commonly associated with the text portions of a spreadsheet and sharing changes. Proofing Language Comments Changes

The View Tab groups contain the commands most commonly associated with the variety of ways you can “look at” a worksheet or workbook. Workbook Views Show Zoom Window Macros

33 Moving the Active Cell in a Worksheet 33 The easiest way to change the active cell in a worksheet is to move the pointer to the cell you want to make active and click. You can display different parts of the worksheet by using the mouse to drag the scroll box in the scroll bar to another position. You can also move the active cell to different parts of the worksheet using the keyboard or the Go To command.

Moving the Active Cell in a Worksheet (continued) Keys for moving the active cell in a worksheet

Selecting a Group of Cells A group of selected cells is called a range. The range is identified by its range reference, for example, A3:C5. In an adjacent range, all cells touch each other and form a rectangle. To select an adjacent range, click the cell in a corner of the range, drag the pointer to the cell in the opposite corner of the range, and release the mouse button.

Selecting a Group of Cells (continued) A nonadjacent range includes two or more adjacent ranges and selected cells. To select a nonadjacent range, select the first adjacent range or cell, press the Ctrl key as you select the other cells or ranges you want to include, and then release the Ctrl key and the mouse button.

Entering Data in a Cell Worksheet cells can contain text, numbers, or formulas. Text is any combination of letters and numbers and symbols. Numbers are values, dates, or times. Formulas are equations that calculate a value. You enter data in the active cell.

Changing Data in a Cell You can edit, replace, or clear data. You can edit cell data in the Formula Bar or in the cell. The contents of the active cell always appear in the Formula Bar. To replace cell data, select the cell, type new data, and press the Enter button on the Formula Bar or the Enter key or the Tab key. To clear the active cell, you can use the Ribbon, the keyboard, or the mouse. 38 38

Formulas Formulas perform operations such as addition, multiplication, and comparison on worksheet values. Formulas can refer to other cells on the same worksheet, cells on other worksheets in the same workbook, or even cells on worksheets in other workbooks. Formulas may make use of built-in functions.

Formulas Cont… Excel knows you are entering a formula in a cell because every formula starts with an = sign. If you forget the = sign, what you enter will be treated as text (unless it can be interpreted as a number in some format).

Formulas Cont… The following example adds the value of cell B4 to 25 and then divides the result by the sum of cells D5, E5, and F5. = (B4+25)/SUM(D5:F5)

This example illustrates the use of The cell reference B4 The numerical constant 25 Arithmetic operators + and / The use of parentheses to specify order of calculation The built-in SUM function A range reference D5:F5

About formula syntax Formulas calculate values in an order based on usual mathematical conventions. You can always add parentheses to control the order of calculation. For example, 4+3*2 is interpreted as 4+(3*2), which is 10. If you want the addition done first, use (4+3)*2, which is 14.

Calculation operators in formulas Operators specify the type of calculation that you want to perform on the elements of a formula. Microsoft Excel includes four different types of calculation operators: arithmetic, comparison, text, and reference. Reference operators are used in range references, and we examine them later.

Calculation operators in formulas Cont… Arithmetic operators perform basic mathematical operations such as addition, subtraction, or multiplication; combine numbers; and produce numeric results.

Calculation operators in formulas Cont… Arithmetic operator + (plus sign) – (minus sign) * (asterisk) / (forward slash) % (percent sign) ^ (caret) Meaning and Example Addition 3+3 Subtraction 3-1 or Negation -1 Multiplication 3*3 Division 3/3 Percent 20% Exponentiation3^2

An example of a and a Complex formula Simple Formula

Comparison operators compare two values and then produce the logical value TRUE or FALSE. Comparison operator = (equal sign) > (greater than sign) < (less than sign) >= (greater than or equal to sign) <= (less than or equal to sign) <> (not equal to sign) Meaning & Example Equal to A1=B1 Greater than A1>B1 Less than A1<B1 Greater than or equal to A1>=B1 Less than or equal to A1<=B1 Not equal to A1<>B1

The comparison operators are often used with certain built-in functions like IF to produce a numeric result. For example, IF(A1>B1,5,0) would yield 5 if the value in cell A1 is greater than the value in cell B1 and 0 otherwise.

Range References Formulas can refer to individual cells or ranges of cells, or to names or labels that represent cells or ranges. Ranges of cells are most often used as arguments to functions that can be applied to a variable number of cells, and they are also used when specifying what values are to be plotted in a chart.

Some range reference examples SUM(B5:E10) adds up the values in all the cells in the rectangular area of the chart from column B through column E and from row 5 through row 10. B5:B10 is the range reference, referring to a total of 24 cells:

This slide is mainly visual.

This is what is called a contiguous range, which represents a rectangular set of cells within the worksheet. Other examples of contiguous ranges are D7:H7 (all within one row) and C14:C25 (all within one column).

Consider SUM(B5:E10,G1:G7) Here, a total of 24+7=31 numbers are to be summed. B5:E10,G1:G7 is considered a range reference as well, but this is called a non- contiguous range:

This slide is mainly visual.

The main reference operators are then : (colon) – used to specify a contiguous range of cells , (comma) – used to represent the union of two or more contiguous ranges

Another reference operator is the single space, used to represent the intersection of two ranges. This is most often used when rows and columns are given names. E.g., in our earlier grade book example, Adams Final would specify the cell at the intersection of the Adams row and the Final column.

Tip on entering cell and range references in formulas When you’re entering a formula, instead of typing a cell reference like B4 in the formula, click in cell B4 and the reference will be created for you automatically. To get a contiguous range reference, click and drag from one corner of the range to the opposite corner.

To get a non-contiguous range reference, click and drag to select the first contiguous sub-range, then hold the shift key down while clicking and dragging to select all the remaining contiguous sub-ranges.

Functions Microsoft Excel contains many predefined functions that can be used in formulas. Functions can be used to perform simple or complex calculations. To enter a function, you can either type it in directly, or select it from the dialog box that appears when you click the Paste Function button on the standard toolbar:

Exploring Office 2003 - Grauer and Barber 61 Function Description SUM (argument) Calculates the sum of the arguments AVERAGE (argument) Calculates the average of the arguments MAX (argument) Displays the largest value among the arguments MIN (argument) Display the smallest value among the arguments COUNT (argument) Calculates the number of values in the arguments PMT (argument) Calculates loan payment amounts IF (argument) Determines a value if a condition is true or false TODAY() Returns the current date using a date format

This slide is mainly visual.

Functions Cont… The most common function in worksheets is the SUM function, which is used to add ranges of cells. In fact, it is so commonly used that it has its own button, the AutoSum button, on the standard toolbar.

Functions Cont… Although SUM(B3:B5) yields the same value as B3+B4+B5, it is generally much better to use the SUM function because the range reference B3:B5 adapts automatically to changes like inserting additional rows within the range.

Using functions to calculate values Functions are predefined formulas that perform calculations by using specific values, called arguments, in a particular order, called the syntax. For example, the SUM function adds values or ranges of cells, and the PMT function calculates the loan payments based on an interest rate, the length of the loan, and the principal amount of the loan.

Syntax of a function The syntax of a function begins with the function name, followed by an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. If the function starts a formula, don’t forget to type an equal sign (=) before the function name.

As you create a formula that contains a function, the Formula Palette can help you. This is a dialog box that comes up after you select a function from the Paste Function dialog box and click OK.

Arguments to functions can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #N/A, cell references, constants, formulas, or other functions

Functions within functions, or nesting Functions can be used as arguments for other functions. When a function is used as an argument, or nested, it must return the same type of value that the argument uses. If a nested function does not return the correct type of value, Microsoft Excel will display a #VALUE! error value.

A formula can contain up to seven levels of nested functions. When Function B is used as an argument in Function A, Function B is a second-level function. If Function B contains Function C as an argument, Function C would be a third-level function.

=IF(I2>=70,"A",IF(I2>=60,"B +",IF(I2>=50,"B",IF(I2>=40," C","F"))))

Error Messages (Cell Reference) ######### Column not wide enough #VALUE! Wrong Argument or Reference #DIV/0! Dividing by zero #Name? Excel doesn’t recognize text in formula #N/A! Missing data or wrong value for lookup function #REF Cell reference not valid (Deleted cell, pasted over) #NUM! Invalid numeric value #NULL! Specified cells do not intersect