Automation and Efficiency
Work Smarter, Not Harder
The difference between a slow Excel user and a fast one isn't just knowledge — it's efficiency. Keyboard shortcuts, templates, and automation let you do in seconds what others take minutes to do.
This chapter covers how to work faster.
Essential Keyboard Shortcuts
Navigation
| Shortcut | Action |
|---|---|
| Ctrl+Home | Go to cell A1 |
| Ctrl+End | Go to last used cell |
| Ctrl+Arrow | Jump to edge of data region |
| Ctrl+G or F5 | Go To dialog |
| Ctrl+Page Up/Down | Move between worksheets |
Selection
| Shortcut | Action |
|---|---|
| Ctrl+Shift+Arrow | Select to edge of data |
| Ctrl+Space | Select entire column |
| Shift+Space | Select entire row |
| Ctrl+A | Select all (current region, then all) |
| Ctrl+Shift+End | Select from cursor to last used cell |
Editing
| Shortcut | Action |
|---|---|
| F2 | Edit cell |
| Ctrl+C | Copy |
| Ctrl+X | Cut |
| Ctrl+V | Paste |
| Ctrl+Alt+V | Paste Special |
| Ctrl+Z | Undo |
| Ctrl+Y | Redo |
| Ctrl+D | Fill down |
| Ctrl+R | Fill right |
| Delete | Clear cell contents |
Formatting
| Shortcut | Action |
|---|---|
| Ctrl+B | Bold |
| Ctrl+I | Italic |
| Ctrl+U | Underline |
| Ctrl+1 | Format Cells dialog |
| Ctrl+Shift+$ | Currency format |
| Ctrl+Shift+% | Percentage format |
| Ctrl+Shift+# | Date format |
Formulas
| Shortcut | Action |
|---|---|
| = | Start formula |
| Enter | Confirm formula |
| Tab | Accept autocomplete suggestion |
| F4 | Toggle absolute/relative reference |
| Ctrl+` | Show/hide formulas |
| Ctrl+Shift+Enter | Array formula (legacy) |
| F9 | Calculate (when in manual mode) |
Workbook and File
| Shortcut | Action |
|---|---|
| Ctrl+N | New workbook |
| Ctrl+O | Open file |
| Ctrl+S | Save |
| F12 | Save As |
| Ctrl+W | Close workbook |
| Ctrl+P |
The F4 Key
Toggle Cell References
When editing a formula, press F4 to cycle through reference types:
A1 → $A$1 → A$1 → $A1 → A1...
This is the fastest way to create absolute references.
Repeat Last Action
Outside of formula editing, F4 repeats your last action.
Applied bold? Press F4 to bold the next selection too.
Paste Special
Ctrl+Alt+V opens Paste Special with options:
Values: Paste only the results, not formulas
Formulas: Paste only formulas
Formats: Paste only formatting
Transpose: Flip rows and columns
Multiply/Divide: Apply operation to existing values
Common Uses
Convert formulas to values:
- Copy cells
- Paste Special → Values
Remove formatting:
- Copy a plain cell
- Select formatted cells
- Paste Special → Formats
Transpose data:
- Copy range
- Paste Special → Transpose
Templates
Creating Templates
Build a well-structured workbook with:
- Standard formatting
- Common formulas
- Data validation
- Instructions
Save as: File → Save As → Excel Template (.xltx)
Using Templates
Templates appear when you create new files. Or open the .xltx file — it creates a new file based on the template.
Good Template Candidates
- Monthly reports
- Budget trackers
- Invoice generators
- Project plans
- Data entry forms
Quick Analysis
Select a range → Quick Analysis button appears (bottom right)
Quick access to:
- Formatting (conditional formatting)
- Charts
- Totals (SUM, AVERAGE, COUNT)
- Tables
- Sparklines
Flash Fill
Excel recognizes patterns and fills automatically.
- Type the pattern for 1-2 examples
- Start typing the third — Excel suggests the rest
- Press Enter to accept, or Ctrl+E to trigger Flash Fill
Examples:
- Extract first name from "Last, First"
- Combine "First" + "Last" into "First Last"
- Reformat phone numbers
- Extract domains from emails
AutoFill
Drag to Fill
Select a cell or range → drag the fill handle (bottom right corner)
Works with:
- Patterns (1, 2, 3...)
- Dates (Jan, Feb, Mar...)
- Custom lists (Monday, Tuesday...)
- Formulas (copies and adjusts references)
Custom Lists
File → Options → Advanced → Edit Custom Lists
Create lists for things you enter frequently (regions, team members, priorities).
Macros (Basics)
What Macros Do
Record a sequence of actions. Play them back with one click.
Recording a Macro
View → Macros → Record Macro
- Name the macro
- Optionally assign a shortcut
- Perform the actions
- Stop recording
Running a Macro
View → Macros → View Macros → Run
Or use the assigned shortcut.
Good Macro Candidates
- Formatting tasks you do repeatedly
- Data cleanup sequences
- Report generation steps
- Copy/paste routines
Saving Macros
Save as .xlsm (macro-enabled workbook) to keep macros.
Named Ranges for Speed
Quick Creation
Select range → type name in Name Box → Enter
Using Named Ranges
In formulas: =SUM(SalesData) instead of =SUM(B2:B1000)
In Go To (Ctrl+G): Type the name to jump there.
Workbook Protection
Protecting Structure
Review → Protect Workbook
Prevents adding, deleting, or renaming sheets.
Protecting Sheets
Review → Protect Sheet
Prevents editing protected cells. Choose what users can/can't do.
Locking Cells
By default, all cells are "locked" but protection isn't on.
- Unlock cells you want editable: Format Cells → Protection → Uncheck Locked
- Protect the sheet
AI Prompt: Efficiency Help
I do this task repeatedly in Excel:
[Describe the task]
Help me:
1. Find a faster way to do it
2. Identify keyboard shortcuts that would help
3. Suggest whether a template, macro, or formula would automate it
4. Create step-by-step instructions for the faster approach
What's Next
You can work fast. Now let's bring AI fully into your spreadsheet workflow.
Next chapter: Using AI with spreadsheets — let AI write formulas, clean data, and solve problems.