Excel Crash Course: Cells to PivotTables

This practical crash course takes you from the absolute basics of cell navigation to building your first PivotTable, giving you the exact steps to become productive in Excel quickly. You’ll learn how to enter and format data, write formulas, sort and filter, and summarize large datasets with PivotTables—all with clear, actionable instructions.

Start with the Grid: Cells, Ranges, and Navigation

Excel is a grid of cells arranged in columns (A, B, C…) and rows (1, 2, 3…). Each cell has a unique address, like A1 or B2. A group of cells is called a range, written as A1:B10 (from the top-left to the bottom-right cell). To move around, click any cell, use the arrow keys, or press Ctrl+Home to jump to A1 and Ctrl+End to go to the last used cell. To select a range, click and drag, or hold Shift while clicking the end cell. To select an entire column or row, click the letter or number header. To select everything, press Ctrl+A.

Entering and Formatting Data Like a Pro

Type directly into a cell and press Enter to move down, Tab to move right, or Esc to cancel. To edit an existing cell, double-click it or press F2. For dates, type in a recognizable format like 1/15/2025; Excel stores it as a number, which allows date arithmetic. For numbers, you can format them as currency, percentage, or with commas using the Number group on the Home tab. To format a cell or range, select it, then choose from the dropdown in the Number group. For text that should stay text (like zip codes), set the format to ‘Text’ before typing, or prefix with an apostrophe (‘).

Essential Formulas and Functions

Formulas always start with an equals sign (=). The basic operators are +, -, *, /, and ^ (exponent). For example, =A1+B1 adds the values in A1 and B1. Functions are pre-built formulas. The most common ones you need:

• SUM – adds a range: =SUM(A1:A10)
• AVERAGE – mean of a range: =AVERAGE(A1:A10)
• COUNT – counts numbers: =COUNT(A1:A10)
• COUNTA – counts non-empty cells: =COUNTA(A1:A10)
• IF – logical test: =IF(A1>10,"High","Low")
• VLOOKUP – finds a value in a table: =VLOOKUP(A1,TableRange,2,FALSE)

To reference a cell in another sheet, use Sheet2!A1. To keep a reference fixed when copying, use dollar signs: $A$1 (absolute), A$1 (row fixed), or $A1 (column fixed).

Sorting and Filtering Data

Sorting and filtering are essential for making sense of raw data. Select your data range, then go to the Data tab. Use Sort to order by one or more columns (e.g., by Last Name, then by First Name). Use Filter to display only rows that meet a condition. Click the filter arrow on a column header, then choose values or use Text Filters or Number Filters. To clear a filter, click the ‘Clear’ button on the Data tab. For complex filtering, you can use the Advanced Filter to copy results to another location.

Working with Tables

Tables (Insert > Table, or Ctrl+T) convert a range into a structured table with built-in filtering, sorting, and formatting. Tables use structured references like =SUM(Table1[Sales]) instead of cell ranges. They also auto-expand when you add new rows. To create a table, select your data and press Ctrl+T. Make sure ‘My table has headers’ is checked. Tables are the foundation for PivotTables, as they ensure your data range is always current.

Introduction to PivotTables

A PivotTable is a tool to summarize, analyze, and explore large datasets without writing formulas. To create one:

1. Click anywhere inside your data range or table.
2. Go to the Insert tab and click PivotTable.
3. In the dialog, confirm the range and choose where to place the PivotTable (New Worksheet is easiest).
4. In the PivotTable Field List, drag fields to the four areas: Filters, Columns, Rows, and Values.
5. For example, drag ‘Region’ to Rows, ‘Product’ to Columns, and ‘Sales’ to Values. You get a cross-tabulation of sales by region and product.

You can change the summary function (Sum, Count, Average) by right-clicking a value cell and selecting Summarize Values By. To refresh data after changes, right-click and choose Refresh, or use Alt+F5. To group dates, right-click a date field and choose Group.

Common Problems and How to Fix Them

• #DIV/0! – You’re dividing by zero or an empty cell. Fix the denominator or use IFERROR.
• #VALUE! – A formula has the wrong data type (e.g., text in a math operation). Check the referenced cells.
• #NAME? – Excel doesn’t recognize a function name, often because of a typo or missing quotes.
• PivotTable not updating – Right-click and Refresh, or change to a Table source.
• Numbers stored as text – Select the column, click the warning icon, and choose ‘Convert to Number’.

Keyboard Shortcuts That Save Time

• Ctrl+C / Ctrl+V – Copy and Paste
• Ctrl+Z / Ctrl+Y – Undo and Redo
• Ctrl+Arrow – Jump to the edge of data
• Ctrl+Shift+L – Toggle filters
• Alt+= – AutoSum
• Ctrl+` – Show formulas instead of results

Frequently Asked Questions

Do I need to learn VBA to be productive in Excel?

No. For most everyday tasks—data entry, formulas, charts, and PivotTables—you don’t need VBA. VBA is for automating repetitive tasks or building custom functions. Start with the basics and add VBA only when you hit a task that’s too slow to do manually.

What’s the difference between a PivotTable and a regular table?

A regular table (Ctrl+T) is just a structured range with filtering and formatting. A PivotTable is a separate object that summarizes data from a range or table. You can create a PivotTable from a regular table, but not the other way around.

Why does my PivotTable show ‘Count’ instead of ‘Sum’?

This happens when the field you dragged to Values contains text or blank cells. Excel defaults to Count for non-numeric data. To force Sum, right-click the field in the Values area, choose Summarize Values By > Sum, and ensure the source cells are numbers.

How do I copy a PivotTable without losing the formatting?

Select the PivotTable, then copy and paste as values (Ctrl+Alt+V, then V). This removes the PivotTable functionality but keeps the numbers and formatting. If you need to keep the interactivity, copy the entire sheet instead.

Can I use PivotTables with data from multiple sheets?

Yes, but not directly. You need to combine the sheets into one range first, either by copying them together or using Power Query (Get & Transform Data) to append them. Once combined, you can create a PivotTable from the unified data.

What is the fastest way to learn Excel?

The fastest way is to work on a real project. Take a dataset you care about (like your monthly expenses) and try to summarize it with a PivotTable. Use Excel’s built-in help and online tutorials for specific functions when you get stuck. Practice consistently for a few weeks.

Related

• Solved Windows 10 Slow Shutdown? Fixes That Work
• Can’t Remove Windows Device from Microsoft Account? Fix
• macOS Sequoia Writing Assistant: How to Use It