Excel Class 2: Formatting, Formulas, and Functions
Learning Objectives
- Master professional Excel formatting techniques
- Understand basic formulas and mathematical operations
- Learn essential Excel functions (SUM, AVERAGE, COUNT)
- Create professional-looking spreadsheets
- Build calculation-based worksheets
- Apply conditional formatting for visual data analysis
Getting Started: Open Your Excel Class 1 Workbook
2.1 Reviewing Your Previous Work
- Open Microsoft Excel
- Click File > Open or press Ctrl+O
- Locate your contact list from Class 1
- Review your data structure and formatting
- Save a backup copy: File > Save As → "Contact List Class 2"
Pro Tip: Always work on copies to preserve your original data
Excel Formatting Fundamentals
2.2 Essential Formatting Tools
Text Formatting Options
- Bold Text: Ctrl+B or click Bold button
- Italic Text: Ctrl+I for emphasis
- Underline: Ctrl+U for important headers
- Font Size: Increase readability with larger headers
- Font Color: Blue for headers, black for data
- Cell Background: Light gray for header rows
Number Formatting Techniques
- Currency Format: Select cells → Right-click → Format Cells → Currency
- Date Format: mm/dd/yyyy or dd/mm/yyyy options
- Percentage: Automatic % symbol addition
- Decimal Places: Control precision display
- Thousands Separator: Add commas for large numbers
2.3 Column Width and Row Height Optimization
Auto-Resize Methods
- Double-click column border: Automatically fits content
- Drag column border: Manual width adjustment
- Right-click column header: Format Column Width
- Select multiple columns: Resize several at once
Professional Appearance Tips
- Headers should be bold and centered
- Data columns should be left-aligned for text
- Number columns should be right-aligned
- Consistent spacing improves readability
Excel Formulas: Your First Calculations
2.4 Understanding Excel Formula Basics
Formula Structure Rules
- Always start with equals sign (=)
- Use cell references instead of typing numbers
- Follow order of operations (PEMDAS)
- Case doesn't matter for function names
Basic Mathematical Operations
- Addition: =A1+B1+C1
- Subtraction: =A1-B1
- Multiplication: =A1*B1
- Division: =A1/B1
- Exponents: =A1^2 (A1 squared)
2.5 Hands-On Exercise: Budget Calculator
Step-by-Step Budget Creation
-
Create headers in row 1:
- A1: "Expense Category"
- B1: "Budgeted Amount"
- C1: "Actual Amount"
- D1: "Difference"
-
Add expense categories in column A:
- A2: "Rent"
- A3: "Groceries"
- A4: "Transportation"
- A5: "Entertainment"
- A6: "Utilities"
-
Enter sample budget amounts in column B:
- B2: 1200
- B3: 400
- B4: 300
- B5: 200
- B6: 150
-
Add actual spending in column C:
- C2: 1200
- C3: 450
- C4: 275
- C5: 250
- C6: 180
-
Create difference formula in D2:
- Click cell D2
- Type: =B2-C2
- Press Enter
- Copy formula down: Select D2, copy (Ctrl+C), select D3:D6, paste (Ctrl+V)
Essential Excel Functions
2.6 The Big Three Functions Every Excel User Needs
SUM Function: Adding Numbers
- Basic syntax: =SUM(range)
- Example: =SUM(B2:B6) adds all budget amounts
- Manual range: =SUM(B2,B3,B4,B5,B6)
- Multiple ranges: =SUM(B2:B6,D2:D6)
AVERAGE Function: Finding Mean Values
- Basic syntax: =AVERAGE(range)
- Example: =AVERAGE(B2:B6) calculates average budget
- Ignores text: Only calculates numeric values
- Use for: Performance metrics, grade calculations
COUNT Function: Counting Entries
- COUNT: =COUNT(range) counts only numbers
- COUNTA: =COUNTA(range) counts all non-empty cells
- COUNTIF: =COUNTIF(range,criteria) counts based on conditions
- Example: =COUNTA(A2:A6) counts expense categories
2.7 Advanced Budget Analysis Exercise
Add Summary Section
-
Create summary in row 8:
- A8: "Total Budgeted"
- B8: =SUM(B2:B6)
- A9: "Total Actual"
- B9: =SUM(C2:C6)
- A10: "Overall Difference"
- B10: =B8-B9
-
Add analysis calculations:
- A11: "Average Budget"
- B11: =AVERAGE(B2:B6)
- A12: "Number of Categories"
- B12: =COUNTA(A2:A6)
Visual Enhancement with Conditional Formatting
2.8 Conditional Formatting Basics
Highlight Cells Rules
- Select your difference column (D2:D6)
- Go to Home tab > Conditional Formatting
- Choose Highlight Cells Rules > Less Than
- Enter 0 (zero)
- Select red background formatting
- Click OK
Data Bars for Visual Impact
- Select budget amounts (B2:B6)
- Conditional Formatting > Data Bars
- Choose blue gradient bars
- Watch your data come alive visually
Color Scales for Quick Analysis
- Select actual amounts (C2:C6)
- Conditional Formatting > Color Scales
- Choose red-yellow-green scale
- Instantly see spending patterns
Professional Spreadsheet Design
2.9 Design Best Practices
Header Formatting Standards
- Bold headers: Make them stand out
- Background color: Light blue or gray
- Center alignment: Professional appearance
- Freeze panes: Keep headers visible while scrolling
Data Presentation Tips
- Consistent decimal places: 2 for currency
- Proper alignment: Numbers right, text left
- White space: Don't cram data together
- Gridlines: Use sparingly for clean look
Color Scheme Guidelines
- Headers: Dark blue background, white text
- Positive numbers: Green or black
- Negative numbers: Red for immediate attention
- Alternating rows: Light gray for readability
Excel Class 2 Practice Assignment
2.10 Create a Personal Expense Tracker
Requirements Checklist
- 10 expense categories minimum
- 3 months of data (Jan, Feb, Mar columns)
- Monthly totals using SUM function
- Average monthly spending using AVERAGE
- Highest/lowest categories using MAX/MIN functions
- Professional formatting with colors and borders
- Conditional formatting for over-budget items
- Summary section with key statistics
Bonus Challenge Features
- Percentage calculations: Each category as % of total
- Year-to-date totals: Running totals across months
- Budget variance analysis: Planned vs actual comparison
- Visual charts: Simple bar charts for top expenses
Common Excel Formula Errors and Solutions
2.11 Troubleshooting Guide
Error Messages Explained
- #DIV/0!: Division by zero error - check denominators
- #VALUE!: Wrong data type in formula
- #REF!: Deleted cell reference in formula
- #NAME?: Excel doesn't recognize function name
- #NUM!: Number too large or small for Excel
Prevention Strategies
- Double-check cell references before pressing Enter
- Use absolute references when copying formulas
- Validate data types before creating formulas
- Test formulas with simple examples first
- Save frequently to prevent data loss
Excel Class 2 Completion Checklist
✅ Skills Mastered Today
- ✅ Applied professional formatting to spreadsheets
- ✅ Created basic mathematical formulas
- ✅ Used SUM, AVERAGE, and COUNT functions
- ✅ Implemented conditional formatting rules
- ✅ Built a functional budget calculator
- ✅ Designed professional-looking worksheets
- ✅ Troubleshot common formula errors
- ✅ Practiced cell reference techniques
Next Steps: Excel Class 3 Preview
Advanced Topics Coming Soon
- Data sorting and filtering techniques
- Pivot table creation and analysis
- Advanced chart types and customization
- VLOOKUP and HLOOKUP functions
- Data validation and dropdown lists
- Protecting worksheets and workbooks
Recommended Practice
- Daily formula practice: 15 minutes with simple calculations
- Real-world applications: Track personal expenses or projects
- Explore function library: Try new functions each week
- Format consistency: Apply professional standards to all work
Additional Excel Resources
Microsoft Excel Help and Training
- Excel Online Help Center: Step-by-step tutorials
- Microsoft Excel Templates: Pre-built budget and tracking sheets
- Excel Function Reference: Complete function documentation
- Excel Keyboard Shortcuts: Speed up your workflow
Practice Workbook Downloads
- Budget Template: Ready-to-use expense tracker
- Formula Practice Sheet: 50+ exercises with solutions
- Formatting Examples: Professional design templates
FAQ: Excel Formatting and Formulas
Q: Can I use Excel formulas in Excel Online? A: Yes, Excel Online supports all basic formulas and functions covered in Class 2.
Q: What's the difference between relative and absolute cell references? A: Relative references (A1) change when copied, absolute references ($A$1) stay fixed. We'll cover this in detail in Class 3.
Q: How many decimal places should I use for currency? A: Standard practice is 2 decimal places for currency (dollars and cents).
Q: Can I undo conditional formatting if I don't like it? A: Yes, select the cells and go to Conditional Formatting > Clear Rules > Clear Rules from Selected Cells.
Q: What's the maximum number of functions I can nest in one formula? A: Excel allows up to 64 levels of nested functions, but keep it simple for readability.
Ready for Excel Class 3? Master advanced data analysis, pivot tables, and professional chart creation. Build on your formatting and formula skills to become an Excel power user.
Course Duration: 1.5-2 hours | Skill Level: Beginner to Intermediate | Prerequisites: Excel Class 1 completion