Excel Basics — What You Still Need to Learn in the AI Era

Chapter 2: Excel Efficiency Fundamentals

Many people use Excel for a decade and remain inefficient — still dragging to select ranges, manually fixing formula references, fighting with merged cells. The root cause is almost never a lack of intelligence — it's a lack of fundamentals. This chapter covers the essentials systematically: 30 must-know shortcuts, data entry standards, the Super Table (Ctrl+T), and reference types. Master these and your daily Excel time drops by 30-40%.

Why Fundamentals Determine Your Ceiling

If you watch a "10-year Excel user" work, you can almost immediately spot a dozen inefficiencies: dragging to select hundreds of rows (Ctrl+Shift+End does it instantly), broken formula copies because they don't understand absolute references, date columns that look like dates but are actually text strings, merged cells that silently break every filter and pivot table.

These aren't advanced-feature problems. They're foundational knowledge gaps. Without fixing the foundation, learning more functions or VBA just builds on unstable ground.

Three layers of Excel fundamentals:

1. Operation efficiency — Shortcuts and navigation. Determines how fast you do the same task.
2. Data standards — Entry format, table structure, naming. Determines whether formulas and analysis work at all.
3. Reference mechanics — Absolute vs. relative references. Determines whether your formulas copy correctly.

30 Essential Shortcuts

80% of efficiency gains come from about 30 shortcuts. Organized by category:

Navigation (7)

Ctrl + Home
Jump to cell A1
Return to the start of the sheet from anywhere. Essential when working with large tables.


Ctrl + End
Jump to last used cell
Quickly check how many rows/columns your data has. Also reveals "phantom" data lurking past your visible range.


Ctrl + Shift + End
Select from current cell to last used cell
The fastest way to select a large data range. Replaces mouse dragging entirely.


Ctrl + Arrow Keys
Jump to edge of data region
Navigate to the first/last cell with data in a continuous region. Like pressing End then arrow.


Ctrl + Shift + Arrows
Select to edge of data region
Fastest way to select an entire column or row of data. Go to A1, then Ctrl+Shift+Down selects the whole column.


Ctrl + G / F5
Go To dialog
Find blank cells, formula cells, last cell, etc. Invaluable for data cleaning — especially filling blanks left by merged cells.


Ctrl + F / Ctrl + H
Find / Find & Replace
Ctrl+H with wildcards (* and ?) enables fuzzy bulk replacement. Core data cleaning tool.

Selection & Editing (9)

Ctrl + T
Create Super Table
KEY SHORTCUT. Converts a data range to an Excel Table with auto-expansion, structured references, and auto-filter.


Alt + Enter
Line break within cell
Force a new line inside a single cell. Useful for notes or multi-line labels. Note: increases row height.


Ctrl + D / Ctrl + R
Fill Down / Fill Right
Copy content from the top/left cell to the entire selection. Select the range first (including source cell), then press.


Ctrl + Enter
Fill all selected cells
Select multiple cells, type content, press Ctrl+Enter — all selected cells fill simultaneously. Great for batch entry.


Ctrl + Shift + L
Toggle AutoFilter
Enable/disable filter dropdowns instantly. No menu navigation needed. Used constantly during data analysis.


F2
Enter cell edit mode
Edit current cell without double-clicking. In formula editing, lets you move cursor through the formula precisely.


Ctrl + Z / Ctrl + Y
Undo / Redo
Excel keeps 100 undo steps by default. Note: undo history is cleared when you close and reopen the file.


Ctrl + A
Select All (smart in Tables)
In a Table: first press selects data region, second press selects entire table including headers.


Alt + H + V + S
Paste Special
Paste values only (strip formulas), paste formats only, transpose, etc. Essential for converting formula results to static values.

Formatting (7)

Ctrl + 1
Format Cells dialog
The master dialog for number format, alignment, borders, and fill. Much faster than right-clicking.


Ctrl + Shift + 1
Number format (thousands, no decimals)
Instantly format as 1,234. Standard for financial reports.


Ctrl + Shift + 5
Percentage format
Convert decimal to percentage display: 0.15 shows as 15%. The underlying value doesn't change.


Ctrl + B / I / U
Bold / Italic / Underline
Basic text formatting. Use for header rows, key totals, and callout cells.


Alt + H + H
Fill color picker
Press Alt, H, H in sequence to open the color picker. Highlight key rows and columns faster than the toolbar.


Ctrl + Shift + ~
Clear number format (General)
Remove all number formatting and reset to General. Useful for fixing formatting chaos in imported data.


Alt + =
AutoSum (SUM)
Click below or to the right of a numeric range, press Alt+= to auto-generate a SUM formula. Fastest way to total a column.

Formula & Reference (7)

F4
Cycle reference type (KEY)
While editing a formula, press F4 on a reference to cycle: A1 → $A$1 → A$1 → $A1 → A1. No need to type $ manually.


Ctrl + ` (backtick)
Show/hide formulas
Toggle between showing formula text and calculated values. Great for auditing all formulas in a sheet at once.


F9
Evaluate selected formula part
In edit mode, select part of a formula and press F9 to see its calculated value. Essential for debugging nested formulas.


Shift + F3
Insert Function wizard
Opens the function search and argument entry dialog. Faster than looking up help when you can't remember the parameter order.


Ctrl + Shift + A
Show function arguments
After typing a function name, press this to see the argument list. Quick reminder of parameter order.


Ctrl + Shift + Enter
Enter array formula (legacy)
For Excel 2019 and earlier. Excel 365/2021 users can usually just press Enter — dynamic arrays handle it automatically.


Ctrl + [ (bracket)
Go to referenced cells
Jump directly to the cells that the current cell's formula references. Useful for tracing formula dependencies.

Data Entry Standards

Data entry standards are the most-ignored and highest-impact Excel fundamental. Bad entry habits silently break formulas, pivot tables, charts, and analysis that come later.

The Four Most Common Entry Mistakes

1. Mixed date formats. Excel dates are internally numbers (days since Jan 1, 1900). Only formats Excel recognizes as dates get stored as numbers — anything else is stored as text. Text "dates" make all date functions (DATEDIF, WEEKDAY, NETWORKDAYS) fail silently.

Quick check: Real dates right-align in Excel; text "dates" left-align. If your date column left-aligns, it's text.

Use consistently: 2024/01/15 or 2024-01-15. Set display format via Ctrl+1 — don't type the display format, set it separately.

2. Numbers with embedded units. "1000 USD" or "500 pcs" are text strings to Excel. SUM, AVERAGE, all math — zero. Put units in the column header or in cell format (Ctrl+1 → Custom → type 0"pcs" to display as "500pcs" while storing as 500).

3. Hidden spaces. Data exported from systems or pasted from web pages often has invisible leading/trailing spaces. These silently cause VLOOKUP mismatches and text comparisons to fail.

=TRIM(A2)   — removes leading/trailing spaces and collapses internal multiple spaces to one

After importing any external data, run TRIM on all text columns, or use Ctrl+H to find all spaces and replace.

4. Merged cells. Merged cells look clean but are Excel's most destructive pattern. They break sorting (can't sort a column with merged cells), filtering (shows only first row of merged block), pivot tables (can't correctly read merged source data), and VLOOKUP (merged lookup areas cause errors).

Replace with: "Center Across Selection" for visual centering (Ctrl+1 → Alignment → Horizontal: Center Across Selection), or repeating the category value in each row. Only merge cells in final print-layout copies, never in your data tables.

Naming Standards & Super Tables

Pressing Ctrl+T converts any data range into an Excel Table (called "Super Table" in Chinese Excel communities). This single action delivers five major upgrades:

1. Auto-expansion: Add a row at the bottom — the table range expands automatically. Formulas, charts, and pivot tables referencing this table update too.
2. Structured references: Instead of =SUM(B2:B100), you write =SUM(SalesTable[Amount]). Column names make formulas self-documenting.
3. Auto-style and total row: Alternating row colors adjust automatically as data grows. Enable the Total Row to get per-column sum/count/average with a dropdown.
4. Built-in filter: Filter dropdowns appear in header row automatically, always.
5. Formula auto-fill: Enter one formula in a column — Excel fills it down to all rows automatically.

Naming your tables: After creating a table, rename it in the Table Design tab (e.g., tblSales, tblProducts). When you reference it with VLOOKUP or other functions, the table name makes the formula far easier to understand later.

Absolute vs. Relative References

The most common source of broken formula copies. The four types:

F4 in formula edit mode cycles through all four: A1 → $A$1 → A$1 → $A1 → A1. No typing of $ needed.

The golden rule: If a reference should stay fixed when you copy the formula (tax rate, commission percentage, lookup range), lock it with $. If it should move with the copy (each row's value), leave it relative.

Calculating commission (rate in D1, sales in column B):
❌ =B2*D1  →  copies to =B3*D2 (rate reference drifts to wrong cell)
✅ =B2*$D$1  →  copies to =B3*$D$1 (rate always from D1)

Calculating % of total (total in B10):
❌ =B2/B10  →  denominator drifts when copied
✅ =B2/$B$10  →  denominator always B10

Version Differences

Key functions this book uses, and their version requirements:

ℹ️ Note

Version note for this book:

All examples are tested on Excel 365. When a function requires 365/2021, a compatible alternative is provided below the example. WPS users should note Power Query and VBA chapters may have workflow differences.

Previous ← Chapter 1: AI + Formula Thinking Next Chapter 3: Lookup Functions Complete Guide →