Microsoft Excel Intermediate
Formulas, Charts & Data Management
VUB Intermediate Computer Skills
Week 5 of 8
Money Mystery
Can you solve these without a calculator?
Let's see how fast your brain can crunch numbers!
Quick Math Challenge
- You spend $12.50 on lunch for 5 days. Total? ($62.50)
- Your electric bill was $142, $156, and $138 for 3 months. Average? ($145.33)
- Gas costs: $45, $52, $38, $61. Highest? Lowest? ($61, $38)
Now Imagine...
- What if you had 50 expenses to add up?
- What if you needed the average of a year of bills?
- That's what Excel formulas do — instantly!
Discussion
What numbers do you deal with regularly? Bills? Prescriptions? Mileage? Today, Excel will do ALL the math for you!
Why Excel Matters
Numbers, calculations, and organization
Budgeting
Track income, expenses, savings goals
Tracking
Medications, appointments, health data
Lists
Inventory, contacts, to-do items
Analysis
Visualize trends, compare data
Today's Goals
- Use formulas to calculate totals and averages
- Sort and filter data to find what you need
- Create charts to visualize your data
- Build a practical budget spreadsheet
The Excel Interface
Understanding cells, rows, and columns
Key Terms
- Cell - One box where you enter data
- Row - Horizontal line (numbered 1, 2, 3...)
- Column - Vertical line (lettered A, B, C...)
- Cell Address - Location like "A1" or "B5"
Important Areas
- Formula Bar - Shows cell contents
- Name Box - Shows selected cell address
- Sheet Tabs - Multiple worksheets
- Ribbon - Tools and commands
| A | B | C | |
|---|---|---|---|
| 1 | A1 | B1 | C1 |
| 2 | A2 | B2 | C2 |
Cell "A1" is selected (highlighted in green)
Introduction to Formulas
Let Excel do the math for you
A formula is an instruction that tells Excel to calculate something. All formulas start with an equals sign (=).
Formula Rules
- Always start with
= - Use cell addresses (like A1, B2) instead of typing numbers
- When the source data changes, the formula updates automatically!
Simple Math Example
| A | B | C | |
|---|---|---|---|
| 1 | 10 | 5 | =A1+B1 |
| 2 | Result: 15 |
In cell C1, the formula =A1+B1 adds the values in A1 and B1. If you change A1 to 20, C1 automatically becomes 25!
SUM Formula
Add up a range of numbers
The SUM function adds up all numbers in a range. It's the most commonly used formula in Excel!
=SUM(A1:A5)Adds all values from A1 through A5
Example: Monthly Expenses
| A | B | |
|---|---|---|
| 1 | Groceries | $320 |
| 2 | Utilities | $150 |
| 3 | Gas | $80 |
| 4 | Insurance | $200 |
| 5 | Total | =SUM(B1:B4) |
Result in B5: $750
Quick Tip: AutoSum
Select the cell below your numbers, then press Alt + = to instantly insert a SUM formula!
AVERAGE Formula
Find the mean of your numbers
The AVERAGE function calculates the mean (average) of a range of numbers.
=AVERAGE(B1:B4)Calculates the average of values from B1 through B4
Example: Test Scores
| A | B | |
|---|---|---|
| 1 | Quiz 1 | 85 |
| 2 | Quiz 2 | 92 |
| 3 | Quiz 3 | 78 |
| 4 | Quiz 4 | 95 |
| 5 | Average | =AVERAGE(B1:B4) |
Result in B5: 87.5
Practical Uses
- Average monthly spending
- Average blood pressure readings
- Average miles driven per week
Other Useful Formulas
COUNT, MAX, and MIN
COUNT
=COUNT(A1:A10)
Counts how many cells have numbers
MAX
=MAX(A1:A10)
Finds the highest number
MIN
=MIN(A1:A10)
Finds the lowest number
Example: Expense Analysis
| A | B | |
|---|---|---|
| 1 | Total | =SUM(B1:B4) |
| 2 | Average | =AVERAGE(B1:B4) |
| 3 | Highest | =MAX(B1:B4) |
| 4 | Lowest | =MIN(B1:B4) |
| 5 | Count | =COUNT(B1:B4) |
Sorting Data
Organize your information
Sorting rearranges your data in a specific order - alphabetically, numerically, by date, etc.
- Click any cell in the column you want to sort by
- Go to Data tab on the Ribbon
- Click Sort A to Z (ascending) or Sort Z to A (descending)
Before Sort (by Amount)
| Item | Amount |
|---|---|
| Groceries | $320 |
| Gas | $80 |
| Insurance | $200 |
After Sort (Highest First)
| Item | Amount |
|---|---|
| Groceries | $320 |
| Insurance | $200 |
| Gas | $80 |
Important!
Make sure to select ALL your data (or click inside the data range) before sorting. Otherwise, only one column will sort and your data will become mismatched!
Filtering Data
Show only what you need
Filtering temporarily hides rows that don't match your criteria. The data isn't deleted - just hidden from view.
- Click any cell in your data
- Go to Data tab
- Click Filter (dropdown arrows appear on headers)
- Click a dropdown arrow and choose what to show
Example Uses
- Show only expenses over $100
- Show only "Food" category items
- Show only appointments in March
To Remove Filter
Click Data → Clear to show all data again, or click the dropdown and select "Clear Filter".
Creating Charts
Visualize your data
Charts make it easy to understand data at a glance. Excel can create charts automatically from your data.
- Select your data including headers (e.g., A1:B5)
- Go to Insert tab
- Click a chart type in the Charts group
- Choose a specific chart style
- Excel creates the chart - you can move and resize it
Quick Charts
Select your data and press Alt + F1 to instantly create a chart on the same sheet!
Chart Types
Choosing the right visualization
Column/Bar Chart
Compare quantities across categories (expenses by month)
Pie Chart
Show parts of a whole (budget breakdown)
Line Chart
Show trends over time (weight tracking)
When to Use Each
- Column: Comparing different items (Jan vs Feb vs Mar expenses)
- Pie: Showing percentages (30% food, 20% gas, 50% rent)
- Line: Showing change over time (daily blood pressure)
Budget Spreadsheet
Putting it all together
Let's create a simple monthly budget using everything we've learned!
| A | B | C | |
|---|---|---|---|
| 1 | Category | Budgeted | Actual |
| 2 | Rent/Mortgage | $800 | $800 |
| 3 | Groceries | $300 | $325 |
| 4 | Utilities | $150 | $142 |
| 5 | Transportation | $100 | $95 |
| 6 | TOTAL | =SUM(B2:B5) | =SUM(C2:C5) |
Add More Analysis
- Difference: =C6-B6 (Are you over or under budget?)
- Chart: Create a bar chart comparing Budgeted vs Actual
Workshop Time!
Build your own budget tracker
Excel Budget Workshop — Parts 1 & 2 (Guided)
Use the Excel Budget Workshop handout to follow along.
Part 1: Data Entry (10 min)
- Create headers: Category, Budgeted, Actual
- Enter 8 expense categories
- Format amounts as currency
Part 2: Formulas (15 min)
- SUM for totals
- AVERAGE, MAX, MIN
- Difference column
- Fill Handle trick
Remember
Every formula starts with = (equals sign). If it shows as text, you forgot the equals sign!
Break Time
10 minutes — Save your work first!
10-Minute Break
Before You Get Up
- Press Ctrl + S to save your spreadsheet
- Name it "Budget_YourName" if you haven't already
- Don't close Excel — we'll continue after break!
After Break: Independent Practice
You'll sort, filter, create charts, and personalize your budget with real data. Parts 3 & 4 of the workshop!
Knowledge Check
Let's review what we learned!
Question 1
What symbol must every formula start with?
The equals sign (=)
Question 2
Which formula adds up a range of numbers?
=SUM(range)
Question 3
What's the difference between sorting and filtering?
Sorting rearranges data in order; filtering hides data that doesn't match criteria.
Question 4
Which chart type is best for showing parts of a whole (percentages)?
Pie chart
Show & Share
Partner check — test each other's formulas!
Partner Swap Challenge
Trade seats with your neighbor and try this test:
- Find one expense amount in your partner's "Actual" column and change the number
- Watch the TOTAL, AVERAGE, MAX, or MIN — did the formulas update automatically?
- Check the Difference column — did it recalculate too?
- Change the number back to the original value when done!
Check Your Partner's Work
- ✓ All formulas recalculated?
- ✓ Chart updated with the new data?
- ✓ Conditional formatting colors correct?
Discussion
- What surprised you about formulas?
- Which formula will you use most?
- Any tricks you discovered?
Your Excel Challenge
Put your skills to work this week!
This Week's Mission
Track your real spending every day this week in your budget spreadsheet.
- Open your budget each evening and update the "Actual" column
- Add new categories if you spend on something not in your list
- Watch your formulas — totals, averages, and charts update automatically!
- Bring your budget to Week 6 — we'll save it to Google Drive so you can access it anywhere
Formulas
=SUM, =AVERAGE, =MAX, =MIN
Sort & Filter
Organize and find data fast
Charts
See your budget at a glance
Your Budget
Real data, real savings!
Next Week Preview
Week 6: Cloud Computing & Google Drive
Coming Up: Week 6
We'll learn about storing files in the cloud and using Google Drive!
Topics We'll Cover:
- What is the Cloud? - Understanding online storage
- Google Drive - Free storage, access anywhere
- Uploading & Organizing - Move files to the cloud
- Sharing - Send files and collaborate
- Google Docs - Create documents online
Before Next Week
Make sure you remember your Gmail login from Week 3 - Google Drive uses the same account!
Great work today!
You can now use Excel for budgeting and data analysis. See you next class!