Excel Tutorial: From Basic Formulas to Pivot Tables and VBA Macros
Key Takeaways
- Excel formulas start with equals signs: `=SUM(A1:A10)` adds values in cells A1 through A10. You can combine functions like `=AVERAGE(B2:B20)` to find the mean of a column.
- Pivot tables summarize thousands of rows in seconds. Drag fields to rows, columns, and values to see sales by region or date without writing formulas.
- VBA macros automate repetitive tasks. Record a macro to format a report, then edit the code to loop through sheets. No programming experience needed to start.
- Practice with sample data from Excel's built-in templates or your own sales spreadsheet. Hands-on work beats reading tutorials every time.
---
# How to Use Excel: A Step-by-Step Guide from Beginner to Advanced
I've taught Excel to hundreds of people over the years, from fresh college grads to seasoned accountants. The biggest mistake I see is jumping into complex topics without mastering the basics. Let's fix that. This guide walks you through real-world examples you can try right now.
Getting Started: The Foundation
Open Excel and you'll see a grid of cells. Each cell has an address: column letter, row number. A1 is the first cell. Click any cell and start typing. Press Enter to confirm.
Essential shortcuts I use daily:
- `Ctrl + C` to copy, `Ctrl + V` to paste
- `Ctrl + Z` to undo a mistake
- `Ctrl + Shift + L` to add filters to your headers
- `F2` to edit the active cell's content
- `Ctrl + Arrow Key` to jump to the last filled cell in a row or column
Try this: type "January" in A1, "February" in B1, "Sales" in C1. Then enter numbers in C2 through C13. You've built a simple table.
Writing Formulas That Work
Formulas always start with `=`. For example, to add cells C2 through C13, type `=SUM(C2:C13)` and press Enter. Excel calculates the total instantly.
Common functions to know:
| Function | Purpose | Example |
| ---------- | --------- | --------- |
| `SUM` | Adds numbers | `=SUM(D2:D100)` |
| `AVERAGE` | Calculates mean | `=AVERAGE(B2:B50)` |
| `IF` | Returns one value if true, another if false | `=IF(A1>100,"High","Low")` |
| `VLOOKUP` | Looks up a value in the left column of a table | `=VLOOKUP("Widget",A2:D10,4,FALSE)` |
| `COUNTIF` | Counts cells matching a condition | `=COUNTIF(E2:E200,"Yes")` |
I once helped a client who spent hours manually totaling invoices. A single `SUM` formula saved them 30 minutes each week. That's 26 hours a year.
Pivot Tables: Your Data Summary Superpower
Pivot tables let you drag and drop fields to see patterns. Here's how to create one:
1. Click any cell inside your data table.
2. Go to the Insert tab and click PivotTable.
3. In the dialog, choose where to place the pivot table (new worksheet is easiest).
4. Drag a field (like "Region") into the Rows area.
5. Drag a numeric field (like "Sales") into the Values area.
That's it. You now see total sales by region. Add a date field to Rows to see monthly trends. Right-click any value and choose Sort to order from highest to lowest.
I trained a marketing team who used pivot tables to analyze campaign performance across 20,000 rows. They found that email campaigns outperformed social ads by 40% in Q3. Without pivot tables, they would have needed advanced SQL skills.
VBA Macros: Automating the Boring Stuff
VBA (Visual Basic for Applications) is Excel's programming language. You don't need to be a coder. Start by recording a macro:
1. Go to the Developer tab (if not visible, right-click the ribbon, choose Customize Ribbon, and check Developer).
2. Click Record Macro. Give it a name like "FormatReport".
3. Perform actions: bold headers, set column widths, apply borders.
4. Click Stop Recording.
5. Press `Alt + F8` to see your macro. Click Run to replay it.
To edit the macro, press `Alt + F11` to open the VBA editor. You'll see code like:
```vba
Range("A1:C1").Font.Bold = True
Columns("A:C").ColumnWidth = 15
```
Change `15` to `20` to widen columns. Add a loop to process multiple sheets:
```vba
For Each ws In Worksheets
ws.Range("A1:C1").Font.Bold = True
Next ws
```
This runs the formatting on every sheet in your workbook. I use this to prepare monthly reports for 12 regional sheets in under 2 seconds.
Practical Tips from My Experience
- Use tables: Select your data and press `Ctrl + T`. This creates a structured table that auto-expands with new rows. Formulas adjust automatically.
- Name ranges: Instead of `=SUM(A1:A50)`, name the range "SalesData" and use `=SUM(SalesData)`. Easier to read and maintain.
- Learn INDEX-MATCH: It's more flexible than VLOOKUP. For example, `=INDEX(C2:C100,MATCH("Widget",A2:A100,0))` returns the value from column C where column A matches "Widget".
- Backup before running macros: Macros can't undo. Save a copy of your workbook first.
Frequently Asked Questions
Q: What's the fastest way to learn Excel formulas?
A: Start with real data you care about. Download your bank transactions or use a sample sales dataset from Microsoft's template gallery. Practice `SUM`, `AVERAGE`, `IF`, and `VLOOKUP` on that data until you're comfortable. Expect to make mistakes—that's how you learn.
Q: Can I use pivot tables on data with blank cells?
A: Yes, but blank cells can cause issues. Fill blanks with zeros or a placeholder like "N/A" before creating the pivot table. Or use the pivot table's field settings to ignore blanks. Right-click the field, choose Field Settings, and select "Show items with no data" if needed.
Q: Is VBA still relevant in modern Excel?
A: Absolutely. While Power Query and Office Scripts offer newer alternatives, VBA remains the most flexible automation tool for complex tasks. It works across all Excel versions and can interact with other Office apps like Outlook and Word. I still use it daily for report generation.
---
Excel mastery comes from consistent practice. Start with formulas, then pivot tables, then macros. Each skill builds on the last. Open Excel now and try one example from this guide. You'll be surprised how quickly it clicks.