How to Use Excel: From Beginner Formulas to VBA Macros
Key Takeaways
- Master core Excel skills: formulas, cell references, and basic functions like SUM and AVERAGE
- Pivot tables summarize thousands of rows in under 10 clicks—no manual counting
- VBA macros automate repetitive tasks; a 3-line macro can save you 30 minutes daily
- Practice with real data: use your own sales records or download sample datasets
Getting Started: The Excel Interface
When you first open Excel, you see a grid of cells. Each cell has an address: column letter + row number (like A1, B2). The ribbon at the top holds tabs (Home, Insert, Data, etc.). Don't memorize everything—focus on what you need right now.
Your first task: Type something in cell A1. Press Enter. Now click that cell again. Look at the formula bar above the grid—that's where you'll see and edit cell contents.
Beginner Formulas: Make Excel Do the Math
Formulas always start with an equals sign (`=`). For example, to add numbers in cells A1 and A2:
```
=A1+A2
```
Press Enter. That's it. But let me save you time: use functions instead of typing `+` manually.
Essential functions:
- `=SUM(A1:A10)` — adds all numbers in that range
- `=AVERAGE(B1:B20)` — calculates the average
- `=COUNT(C1:C100)` — counts cells with numbers
- `=IF(D1>100,"High","Low")` — returns "High" if D1 is over 100, else "Low"
Real example: I once managed a budget for 50 line items. Instead of adding each row, I used `=SUM(B2:B51)` and got the total in one second. That formula still works if I add more rows—just expand the range.
Pivot Tables: Summarize Data Without Formulas
Pivot tables are Excel's most underused feature. With a pivot table, you can turn 10,000 rows of sales data into a clean summary in 4 steps.
How to create one:
1. Select any cell in your data range
2. Go to Insert > PivotTable (or press Alt+N+V)
3. Choose where to place it (new worksheet is safest)
4. Drag fields: "Product" to Rows, "Sales Amount" to Values
That's it. You now see total sales per product. Want to filter by year? Drag "Date" to Filters. Want percentages? Right-click any value > Show Value As > % of Grand Total.
Comparison: Manual vs. Pivot Table
| Task | Manual Method | Pivot Table Method |
| ------ | --------------- | ------------------- |
| Count sales per region | Write formulas for each region (20 mins) | Drag fields (2 mins) |
| Find average order value | Filter, sort, write AVERAGE (10 mins) | Drag field, change to Average (1 min) |
| Update with new data | Recalculate everything (5 mins) | Refresh pivot table (5 sec) |
Advanced Formulas: INDEX-MATCH and XLOOKUP
VLOOKUP is popular but limited. If the lookup column is not the leftmost, VLOOKUP fails. Use INDEX-MATCH (or XLOOKUP in Excel 365).
XLOOKUP syntax:
```
=XLOOKUP(lookup_value, lookup_array, return_array)
```
Example: Find a customer's phone number using their ID.
```
=XLOOKUP("CUST123", A2:A100, D2:D100)
```
This searches for "CUST123" in column A and returns the matching value from column D. No column order restrictions.
INDEX-MATCH (for older Excel):
```
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
```
Same result, but two functions. Once you get used to it, it's faster than VLOOKUP.
VBA Macros: Automate the Boring Stuff
VBA (Visual Basic for Applications) records steps you do manually. If you repeat a task daily—like formatting a report, saving as PDF, or copying data—a macro does it in one click.
Your first macro (no coding):
1. Go to View > Macros > Record Macro
2. Name it "FormatReport"
3. Do your steps: bold headers, set column widths, add borders
4. Stop recording
5. Next time, run it from Macros > View Macros > Run
If you want to write code: Press Alt+F11 to open the VBA editor. Here's a simple macro that deletes empty rows:
```vba
Sub DeleteEmptyRows()
Dim r As Range
For Each r In Selection.Rows
If Application.CountA(r) = 0 Then r.Delete
Next r
End Sub
```
This loops through selected rows and deletes any with zero data. I use this weekly to clean messy exports.
Real numbers: A colleague once spent 2 hours each Monday reformatting a report. I wrote a 15-line macro that did it in 3 seconds. He saved 104 hours per year.
Common Pitfalls (and How to Fix Them)
- #VALUE! error: Usually means you're adding text to numbers. Check cell formats.
- #REF! error: You deleted a row or column that a formula referenced. Undo (Ctrl+Z) or edit the formula.
- Pivot table not updating: Right-click > Refresh. Or set it to refresh on file open (PivotTable Options > Data > Refresh data when opening the file).
- Macro not running: Enable macros in Trust Center (File > Options > Trust Center > Trust Center Settings > Enable all macros). Be careful with unknown sources.
FAQ
Q: How long does it take to learn Excel basics?
A: You can learn formulas and pivot tables in about 10 hours of practice. Most people grasp the core concepts in 2–3 focused sessions if they use real data. VBA takes longer—expect 20–30 hours to write simple macros confidently.
Q: Do I need to memorize all Excel functions?
A: No. There are over 450 functions. I remember maybe 30. Use Excel's built-in help (F1) or search online when you need something specific. The most used ones are SUM, AVERAGE, COUNT, IF, XLOOKUP, and VLOOKUP.
Q: Can I run macros on Excel for Mac?
A: Yes, but with limitations. Excel for Mac supports VBA, but some keyboard shortcuts differ (e.g., Alt+F11 opens the editor on Windows; on Mac it's Fn+Alt+F11). Complex macros with Windows API calls won't work. For heavy automation, Windows is more reliable.
Final Thoughts
Start small. Don't try to learn everything at once. Pick one task you do repeatedly—like totaling monthly expenses—and automate it with a formula. Then try a pivot table. When you're comfortable, record a macro. Each step builds on the last.
Excel is a tool, not a test. The goal is to get your work done faster, not to become a spreadsheet guru. And if you get stuck, remember: someone on YouTube has already solved your exact problem.