excel-fundamentals

Foundational Excel knowledge for working with spreadsheets, cells, formatting, and data organization.

#Workbook Fundamentals

#Terminology

#Limits

#File Formats

#Cell Basics

#A1 Notation

Cells are addressed by column letter + row number:

A1   = Column A, Row 1
B5   = Column B, Row 5
AA1  = Column AA, Row 1
XFD1048576 = Last cell in sheet

#Reference Types

#Range Notation

#Named Ranges

Give meaningful names to cell ranges:

Rules for names:

• Start with letter or underscore
• No spaces (use underscores)
• Max 255 characters
• Cannot match cell references (e.g., "A1" invalid)

#Data Types

#Automatic Type Detection

Excel automatically detects data types when entering:

#Force Text Entry

Prefix with apostrophe to force text:

'123     -> Stored as text "123"
'01234   -> Preserves leading zero
'=SUM    -> Displays as text, not formula

#Date Serial Numbers

Excel stores dates as numbers:

• January 1, 1900 = 1
• January 1, 2024 = 45292
• Date + Time = Integer + Decimal

#Error Values

#Formatting Essentials

#Number Formats

#Common Format Codes

#Cell Alignment

#Text Control

#Borders

#Fill Colors

• Solid fills for emphasis
• Pattern fills for printing
• Gradient fills for visual appeal
• Use consistent color scheme

#Data Organization

#Sorting

Single column:

• Select data range
• Sort A-Z (ascending) or Z-A (descending)

Multi-level sort:

1. Primary: Sort by first column
2. Secondary: Then by second column
3. Tertiary: Then by third column

Sort options:

• Case sensitive
• Sort left to right (by rows)
• Custom sort order (months, days)

#Filtering (AutoFilter)

Enable filter dropdowns on headers:

• Select header row
• Enable AutoFilter
• Click dropdown to filter

Filter types:

#Tables

Convert range to table for:

• Automatic filter buttons
• Structured references ([@Column])
• Auto-expanding formulas
• Built-in styles
• Total row with functions

Create table:

1. Select data with headers
2. Insert > Table
3. Confirm range has headers

#Data Validation

Restrict cell input:

#View and Layout

#Freeze Panes

Keep rows/columns visible while scrolling:

Common patterns:

• Freeze row 1 for headers
• Freeze column A for labels
• Freeze A1:B3 for both headers and labels

#Split View

Divide window into panes:

• Each pane scrolls independently
• Useful for comparing distant parts of sheet
• Drag split bars to resize

#Hide/Show

Rows and columns:

• Right-click > Hide
• Select surrounding > Right-click > Unhide

Sheets:

• Right-click tab > Hide
• Right-click tab > Unhide

#Print Setup

Print titles:

• Rows to repeat at top
• Columns to repeat at left

#Keyboard Shortcuts Quick Reference

#Scripts

Inspect a workbook the user shares before answering questions about it:

python scripts/inspect_workbook.py report.xlsx
python scripts/inspect_workbook.py report.xlsx --sheet Data --sample 10

It reports sheets (including hidden ones), used ranges, formula and error counts, frozen panes, merged cells, Excel tables, data validation, conditional formatting, defined names (flagging broken #REF! names), external links and macros, using only the Python standard library.

#Additional Resources

• For cells and ranges, see cells-and-ranges.md
• For formatting and styles, see formatting-and-styles.md
• For sorting, filtering, and tables, see sorting-filtering-tables.md
• For sheets and views, see sheets-and-views.md
• For a structural report of any .xlsx, run scripts/inspect_workbook.py