Microsoft Excel Fundamentals Study Notes

Fundamentals of Microsoft Excel

• Definition: Microsoft Excel is a spreadsheet program within Microsoft Office used to analyze and interpret data using a grid of rows and columns.

• Worksheet and Workbook: A computerized spreadsheet is a worksheet; the file containing worksheets is a workbook.

• Capacity: A single worksheet contains $16,384$ columns and $1,048,576$ rows.

• Interface Elements: Key parts include the Quick Access Toolbar, Ribbon, Name Box, Formula Bar, Active Cell, Status Bar, and Sheet Tabs.

Workbook Structure and Navigation

• Columns and Rows: Columns are vertical and identified by letters; rows are horizontal and identified by numbers.

• Cell Reference: A cell is the intersection of a row and a column. The Name Box displays the unique reference of the active cell.

• Navigation: Use the Go To command ($Ctrl + G$) to move quickly to specific cells.

• Ranges: A group of cells is a range (e.g., $A3:C5$). Adjacent ranges form a rectangle; non-adjacent ranges are selected using the $Ctrl$ key.

Data Entry and Formatting

• Data Types: Cells contain text (letters/symbols), numbers (values, dates, times), or formulas.

• Number Formats: Standard formats include General, Number, Currency, Accounting (vertical alignment of symbols), Date, Time, Percentage, Fraction, Scientific, and Special (e.g., zip codes).

• Custom Formats: Users can create specific formats using the Format Cells dialog (e.g., formatting a number as "ID"###-### for IDs or "# kg" for units).

• Paste Special: Allows pasting specific attributes like formulas, formats, or values from the Office Clipboard, which stores up to $24$ selections.

Formulas and Mathematical Logic

• Basics: Formulas always begin with an equal sign ($=$) and use operators: $+$ (addition), $-$ (subtraction), $*$ (multiplication), $/$ (division), and ^ (exponentiation).

• Order of Evaluation:     1. Contents in parentheses.     2. Exponentiation (^).     3. Positive or negative literals.     4. Multiplication ($*$) or division ($/$).     5. Addition ($+$) or subtraction ($-$).

• Evaluation Direction: Expressions with the same precedence level are evaluated from left to right.

Specialized Excel Functions

• Mathematical: Includes $\text{SQRT}(\text{number})$, \text{ROUND}(\text{number}, \text{num_digits}), and $\text{LN}(\text{number})$.

• Statistical: Includes $\text{AVERAGE}$, $\text{COUNT}$ (numeric cells), $\text{COUNTA}$ (non-empty cells), $\text{MAX}$, $\text{MIN}$, $\text{STDEV.P}$, and $\text{VAR.P}$.

• Logical: Used to test conditions:     * \text{IF}(\text{logical_test}, \text{value_if_true}, \text{value_if_false}).     * $\text{AND}$, $\text{OR}$, $\text{NOT}$, and $\text{IFERROR}$.

• Condition-Based: Functions like $\text{COUNTIF}$, $\text{COUNTIFS}$, $\text{AVERAGEIF}$, and $\text{AVERAGEIFS}$ perform calculations based on single or multiple criteria.

Data Management and Analysis

• Freezing and Splitting: Use "Freeze Panes" on the View tab to keep rows/columns visible while scrolling; use "Split" to divide the window into independent panes.

• Data Validation: Controls the type of data entered (e.g., whole numbers only) to prevent typos or mistakes.

• PivotTable and PivotChart: Used for creating concise reports that summarize large datasets.

• What-If Analysis: Use Scenario Manager to test and compare different versions/outcomes of data without changing the original budget total.

• Consolidate: Merges data from different worksheets into a single summary.

Visualization and Layout

• Charts:     * Column charts: Illustrate relationships.     * Line charts: Show trends over time.     * Pie charts: Show relationship of parts to a whole.     * Scatter (XY) charts: Show relationships between two categories.

• Sparklines: Tiny charts that display trends within the background of a single cell.

• Conditional Formatting: Changes the appearance (color, icons, data bars) of cells based on rules like "Greater Than" or "Top $10$ Items."

Workbook Security and Optimization

• Protection: Cells can be "Locked" (Home tab) and worksheets "Protected" (Review tab) to prevent editing. Passwords can be set via File > Save As > Tools > General Options.

• Formula Auditing: Use "Show Formulas" ($Ctrl + `$) or the "Evaluate Formula" tool to debug calculations.

• Macros: Recorded sequence of actions stored in Visual Basic XML (eXtensible Markup Language). Workbooks with macros must be saved as "Excel Macro-Enabled Workbook."

• Printing: Scale sheet to fit on one page via Page Layout. Gridlines and headings are hidden by default on printed outputs.