How to Use Excel: From Basics to VBA Macros (Step-by-Step)
Key Takeaways
- Master core formulas like SUM, VLOOKUP, and IF with step-by-step examples.
- Build pivot tables in under 2 minutes to summarize thousands of rows.
- Automate repetitive tasks with simple VBA macros—no coding experience required.
- Practice with real datasets (sales, budgets, inventory) to cement skills.
---
Introduction: Why Excel Still Matters in 2025
Excel is not dead. Despite fancy BI tools, over 750 million people use Excel daily. I’ve worked with data analysts who still prefer Excel for quick ad-hoc analysis because it’s fast and flexible. Whether you’re tracking expenses or building complex financial models, this guide takes you from zero to functional.
I’ll skip the fluff and show you exactly what works—starting with the basics, then formulas, pivot tables, and finally VBA macros.
---
Step 1: Excel Basics You Actually Need
Before jumping into formulas, get comfortable with the interface. Spend 10 minutes on these three things:
- Cells and ranges: A cell is one box (e.g., A1). A range is a group (e.g., A1:B10). Click and drag to select.
- Basic navigation: Ctrl + arrow keys jump to the last filled cell in a direction. Ctrl + Shift + arrow selects entire rows/columns of data.
- Formatting numbers: Right-click a cell → Format Cells → choose Number, Currency, or Percentage. Never leave numbers as “General” when they represent money.
Pro tip: Use Ctrl + ` (grave accent) to toggle formula view. This saves hours when troubleshooting.
---
Step 2: Core Formulas (Beginner to Intermediate)
Formulas are the heart of Excel. Start with these three, then stack them.
SUM, AVERAGE, and COUNT
```excel
=SUM(A2:A100) ' Adds all numbers in A2 through A100
=AVERAGE(B2:B100) ' Returns the mean
=COUNT(C2:C100) ' Counts numeric entries only
```
Real example: A small business with 500 sales records can use `=SUM(F2:F501)` to get total revenue in 2 seconds.IF, VLOOKUP, and XLOOKUP
- IF lets you flag data. Example: `=IF(D2>1000, "High", "Low")` marks orders over $1,000.
- VLOOKUP finds data in a table. But XLOOKUP (Excel 365/2021) is simpler and more powerful. Use:
```excel
=XLOOKUP(G2, A2:A100, B2:B100) ' Finds G2 in column A, returns matching value from column B
```
- XLOOKUP handles missing values and works left-to-right or right-to-left.
INDEX-MATCH (advanced alternative)
VLOOKUP fails when your lookup column is to the right. INDEX-MATCH solves this:
```excel
=INDEX(return_range, MATCH(lookup_value, lookup_array, 0))
```
Example: `=INDEX(C2:C100, MATCH("Product X", A2:A100, 0))` returns product’s price even if price column is left of product names.
---
Step 3: Pivot Tables in 60 Seconds
Pivot tables summarize thousands of rows into a neat table. Here’s a concrete workflow:
1. Select your data (include headers).
2. Go to Insert → PivotTable (or press Alt + N + V).
3. Drag a field to Rows (e.g., “Region”), another to Values (e.g., “Sales”).
4. Right-click any value → Summarize Values By → Sum or Average.
Real example: A 10,000-row sales dataset: drag “Month” to Rows, “Revenue” to Values, and “Product Category” to Columns. In 30 seconds, you see monthly revenue per category.
Comparison Table: Pivot Table vs. Manual Sorting
| Feature | Pivot Table | Manual Sorting/Filtering |
| --------- | ------------- | -------------------------- |
| Speed | Instant summary for 100k rows | Slow, error-prone |
| Flexibility | Drag fields to rearrange | Must re-sort each time |
| Aggregation | Sum, count, average, % of total | Requires helper columns |
| Learning curve | 5 minutes to learn basics | Familiar but tedious |
I’ve seen people spend 20 minutes manually grouping data that a pivot table does in 5 seconds. Learn it once.
---
Step 4: VBA Macros – Automate the Repetitive Stuff
VBA macros are recorded or written instructions that automate tasks. You don’t need to be a programmer.
Recording Your First Macro
1. Go to Developer tab (if hidden: File → Options → Customize Ribbon → check Developer).
2. Click Record Macro → give it a name (no spaces) → choose shortcut key (e.g., Ctrl+Shift+F).
3. Perform actions—like formatting a header row bold and blue.
4. Click Stop Recording.
5. To run: press the shortcut or View Macros → Run.
Writing a Simple VBA Macro
Open the VBA editor (Alt + F11). Insert a module and paste:
```vba
Sub BoldHeaders()
Range("A1:Z1").Font.Bold = True
Range("A1:Z1").Interior.Color = RGB(0, 102, 204)
End Sub
```
This bolds the first row and gives it a blue background. Run it with F5.
Real use case: I once automated a weekly report that took 30 minutes manually. A macro that copied data, applied formatting, and saved as PDF reduced it to 10 seconds.
---
Common Pitfalls and How to Avoid Them
- #N/A in VLOOKUP: Your lookup value doesn’t exist. Use IFERROR: `=IFERROR(VLOOKUP(...), "Not Found")`.
- Pivot table not updating: Right-click → Refresh (or press Alt + F5).
- Macro security: Save files as .xlsm (macro-enabled) and enable macros only from trusted sources.
---
FAQ
1. What’s the fastest way to learn Excel formulas?Practice with real data. Download a free dataset (Google “sales data CSV”) and try SUM, AVERAGE, and IF on it. Also, use Excel’s formula autocomplete (press Tab when you see a suggestion).
2. How long does it take to learn VBA?
Recording macros takes 10 minutes. Writing simple macros takes a few hours of practice. For complex automation, expect 2-4 weeks of steady learning. Start by recording and then editing the generated code.
3. Can I use Excel for data analysis without pivot tables?
Yes, but pivot tables save hours. If you’re analyzing less than 100 rows, filters and SUMIF might suffice. But for anything larger, pivot tables are non-negotiable.
---
Next Steps
- Practice: Open a sample budget file and build a pivot table showing expenses by category.
- Explore: Try the XLOOKUP on a product list.
- Automate: Record a macro that formats your monthly report.
Excel rewards those who practice. Start with one formula today, and you’ll be surprised how quickly you progress.