Excel is a spreadsheet program that can present tables of values arranged in rows and columns. These values can be manipulated mathematically using both basic and complex arithmetic operations and functions.
Basic Parts of Microsoft Excel
CELL: An individual rectangular box located in the central area of a worksheet. Each cell has a unique name identified by its column letter and row number.
ACTIVE CELL: The currently selected cell, recognized by a green outline. Data is entered into the active cell.
NAME BOX: Displays the name of the currently selected cell (e.g., A1) and can be used to jump to a specific cell.
FORMULA BAR: Displays the contents of the active cell and can be used to enter or edit data and formulas. Typing an equal sign (=) activates the Formula Bar.
SHEET TABS: By default, an Excel file contains at least one worksheet (Sheet1, Sheet2, etc.). Each sheet is a page that contains its own collection of cells.
COLUMNS: Vertical divisions identified by letters (A, B, C, …). Columns run top to bottom.
ROWS: Horizontal divisions identified by numbers (1, 2, 3, …). Rows run left to right.
ZOOM SLIDER: Adjusts the zoom level of the worksheet view.
VIEW BUTTONS: Options to change the workbook view (e.g., Normal, Page Layout).
WORKSHEET: A single page within a workbook that contains its own grid of cells.
Quick Reference: UI Cues and Pointer Icons (from the slides)
ARROW: Used to select a cell or a range of cells.
THICK CROSS: Used to select text within the formula bar.
I-BEAM: Used to adjust the column width and row height.
DOUBLE-HEADED-ARROW: Used to copy cell content.
How to Read a Cell Reference
A cell’s name is a combination of its column letter and row number (e.g., A1, B5, G12).
The column letter identifies the vertical position; the row number identifies the horizontal position.
Part 2: How a Worksheet is Organized
A workbook contains sheets (worksheets). Each sheet has its own grid of cells.
The Name Box shows the address of the currently selected cell or the name you assign to a cell or range.
Lesson 3: Functions & Formulas
A formula is a set of instructions Excel uses to perform calculations. It can be written in the Formula Bar.
Example shown: =sum(A1:A4)
The equal sign (=) at the start of a formula tells Excel that what follows is a calculation.
FUNCTION NAME: Predefined formulas available in Excel (e.g., SUM, COUNT, AVERAGE, MIN, MAX, IF, VLOOKUP).
RANGE/PARAMETER/ARGUMENT: A specific input to a function (e.g., A1:A4).
Example formulas:
=SUM(A1:A4)
=A1+A2+A3+A4
A formula can consist of the following elements:
EQUAL SIGN: The beginning of the formula.
OPERATORS: Arithmetic and comparison operators used on operands.
CELL REFERENCE: A single cell or a range of cells (e.g., A1, A1:A4).
VALUES: Raw numbers or dates (e.g., 2,406,9−July−18).
LABELS: Descriptive text such as names or months.
CRITERIA: A value or label that determines if a cell is part of a range to be averaged or processed (e.g., in COUNTIF).
Examples of simple references:
=SUM(A1:A5)
A1:A4=A1 (interpreted as the range A1:A4 and a comparison in context)
Commonly used functions (as listed in the slides):
VLOOKUP,IF,MATCH,INDEX,MAX,MIN,COUNT,AVERAGE,SUM
Common Formula Elements and Examples
A formula can be built from the following elements:
EQUALextSIGN to start the formula.
OPERATORS for arithmetic and comparison operations.
CELLextREFERENCE such as A1 or A1:A4.
VALUES such as numbers or dates.
LABELS such as names or months.
CRITERIA used by functions like COUNTIF.
Example: =SUM(A1:A4) computes the sum of the range A1 through A4.
Reference interpretation: A1:A4 means the cells A1, A2, A3, and A4.
Common Formula Errors (Common Errors to Diagnose)
When a column is not wide enough to display a value, Excel shows a placeholder (e.g., a series of # characters).
#NAME?: The text in a formula is not recognized by Excel.
#VALUE!: The formula uses an argument of the wrong type.
#DIV/0!: A number is divided by zero.
#REF!: A formula refers to a cell that is not valid (e.g., deleted cell).
Lesson 2: Advanced Spreadsheet Skills – Data Cleaning & Sorting
Data Cleaning & Sorting involves arranging data and preparing it for analysis.
Sorting: Organize data in ascending/descending order based on one or more columns.
Filtering: Show only rows that meet specified criteria.
Data Validation: A feature that restricts what can be entered into a cell. Configured from the Data tab in Excel.
Data Validation
Data Validation is a feature used to control what a user can enter into a cell.
Validation Criteria: Defines allowed data types and ranges. Examples of settings:
Allow: Any value, Whole number, Decimal, List, Date, Time, Text length, Custom
Data: Specifies the condition (e.g., between, greater than, less than)
Minimum / Maximum: If you choose Between, set a numeric range.
Input Message: A prompt shown when a cell is selected to guide data entry.
Error Alert: A message shown if incorrect data is entered.
You can apply Data Validation to a range of cells; changes apply to all cells with the same settings.
Data Validation settings are accessed via the Data tab on the ribbon.
Examples:
Create a list of entries that restricts values allowed in a cell (List option).
Set a numeric range with Minimum and Maximum values.
Show an input message: Title and instruction text to guide the user.
Show an error alert: Style options include Stop; custom error messages such as "The allowed value for mark is between 0 to 100.""
Conditional Formatting
Conditional Formatting applies a format to a cell or range based on certain criteria.
Common uses include highlighting values that meet criteria.
Options include:
Highlight Cells Rules > Greater Than, Less Than, Between, Equal To, etc.
Data Bars, Color Scales, Icon Sets to visually represent data trends.
Text that Contains, New Rule, Date Occurring, Duplicate Values, etc.
Manage Rules and Clear Rules to edit or remove formats.
Protect Sheet & Workbook
The Protect Sheet & Workbook feature allows restricting edits to certain cells or the entire workbook.
Practical implications include safeguarding formulas, preventing accidental changes, and controlling access in collaborative environments.
Quick Reference: Common Functions and Concepts
SUM: computes the total of numeric values in a range.
COUNT: counts the number of cells containing numbers in a range.
AVERAGE: computes the mean of numbers in a range.
MIN: returns the smallest value in a list.
MAX: returns the largest value in a list.
IF: checks a condition and returns one value if TRUE and another if FALSE.
VLOOKUP: looks up a value in the first column of a table and returns a value in the same row from a specified column.
INDEX: returns a value from a table based on row and column numbers; INDEX(MATCH()) is a common combination to perform lookups.
MATCH: returns the position of a value in a range.