Microsoft Excel | W6L6

Spreadsheet Software, Worksheet Parts, and Basic Functions With worksheet examples and function outputs

Spreadsheet Software

Is application software used for analyzing, storing, and organizing data in tabular form. Information is arranged so that related values can be viewed and compared clearly. The organized layout supports both mathematical and logical operations.

Tabular Organization

A worksheet contains rows and columns.

What a Spreadsheet Can Do

Store data in an organized structure. Arrange data in rows and columns. Analyze values and compare information. Perform mathematical computations. Perform logical tests and return results.

Microsoft Excel

Is one of the most popular spreadsheet software applications. It is developed by Microsoft. It is available for Windows, macOS, Android, and iOS.

Excel Features Mentioned in the File

Calculation tools for mathematical and logical operations. Graphing tools for presenting data visually. Pivot tables for organizing and summarizing information. A macro programming language called Visual Basic for Applications, or VBA.

Excel File

Is a workbook that can contain multiple worksheets.

The attached workbook contains "Sample 1", "Sheet1", "Sheet2", and "Sheet3".

Use the sheet tabs at the bottom to move between worksheets.

Active Cell

Is the currently selected cell. A green outline identifies it, and new data is entered into that cell.

Columns

Run vertically and are identified by letters.

Rows

Run horizontally and are identified by numbers.

Cell References

Combines a column letter and a row number.

Name Box

Displays the reference of the active cell.

Formula Bar

Displays the contents or formula of the active cell.

It can also be used to enter or edit values and formulas.

The Name Box is located to the left of it.

Sheet Tabs

Are located at the bottom of the workbook window. Selecting a tab switches to another worksheet in the same file.

Entering and Editing Data

Select the intended cell before entering data. Type directly in the cell or use the Formula Bar. The Name Box confirms where the entry will be stored.

Formula

Performs a mathematical or logical operation. The formulas in this module begin with an equal sign (=).

Function

Is a named formula, such as SUM, AVERAGE, IF, MIN, or MAX. Reduce the amount of manual calculation needed.

Function Structure

Uses a function name followed by parentheses. Values, cell references, or ranges are placed inside the parentheses.

=SUM(number1, number2, numberN)

SUM Function

Syntax

=___(number1, number2, numberN)

Purpose

___ adds the values from multiple cells or ranges.

AVERAGE Function

Syntax

=AVERAGE(number1, number2, numberN)

Purpose

AVERAGE calculates the mean of values from multiple cells or ranges.

IF Function

Syntax

=__(logical_test, [value_if_true], [value_if_false])

Purpose

__ checks whether a logical test is true or false.

It returns one value when the test is true and another value when the test is false.

How IF Chooses an Output

1. Evaluate the logical test.

2. Return value_if_true when the test is true.

3. Return value_if_false when the test is false. =IF(G8>=90, "At least 90", "Below 90") Because G8 contains 85, the result is Below 90.

Combining IF and SUM

The sample worksheet uses this formula to return the student with the higher total:

=IF(SUM(G8:J8)>SUM(G9:J9), F8,F9)

SUM(G8:J8) calculates the total for S1.

SUM(G9:J9) calculates the total for S2.

IF compares the two totals and returns F8 or F9.

MIN Function

Syntax

=___(number1, number2, numberN)

Purpose

___ displays the lowest value from the selected cells or range.

MAX Function

Syntax

=___(number1, number2, numberN)

Purpose

___ displays the highest value from the selected cells or range.

Combining IF and OR

Syntax
=IF(OR(A2=”Active Cell”,A2=”Column Letter”,A2=”Formula Bar”,A2=”Name Box”,A2=”Row Number”,A2=”Sheet Tab”0.”PARTS OF MS EXCEL”,” “)

Purpose

A2 contains Formula Bar.

OR checks whether A2 matches any listed Excel part.

Because one condition is true, the output is PARTS OF MS EXCEL.

Nested IF

Syntax

=IF($A2=”What is a Spreadsheet?”,AB2,IF($A2=”Mircosoft Excel”,AB5,” “))

Purpose

The first IF checks the selected topic.

When the first test is false, another IF checks the next topic.

Selecting Microsoft Excel returns its matching description.

Absolute Cell Reference in the Formulas

Syntax

=IF($A$2=”Microsoft Excel”,”Uses fixed reference SAS2”,”No match”)

Purpose

$A$2 fixes both column A and row 2.

When the formula is copied down, every copy still checks A2.

This prevents the reference from changing.