Introduction to Excel Keyboard Shortcuts

This section covers essential keyboard shortcuts in Excel to enhance your efficiency in data analysis and formula usage.

Basic Navigation

• Move between cells

  • Up: ?
  • Down: ?
  • Left: ?
  • Right: ?

• Select a range of cells

  • Shift + Arrow Keys

• Jump to the beginning or end of a row/column

  • End: Ctrl + Arrow Keys

Cell Operations

• Copy selected cells

  • Ctrl + C

• Cut selected cells

  • Ctrl + X

• Paste copied or cut cells

  • Ctrl + V

• Undo last action

  • Ctrl + Z

• Redo last undone action

  • Ctrl + Y

Formula Operations

• Start a formula

  • =

• Insert a function

  • Shift + F3

• Toggle absolute and relative references

  • F4

• Calculate all worksheets

  • F9

Formatting Shortcuts

• Bold

  • Ctrl + B

• Italic

  • Ctrl + I

• Underline

  • Ctrl + U

• Format as General

  • Ctrl + Shift + ~

• Format as Number

  • Ctrl + Shift + !

Miscellaneous

• Open Excel Options

  • Alt + F + T

• Open Save As dialog

  • F12

• Insert or delete rows and columns

  • Insert row: Ctrl + Shift + +
  • Delete row: Ctrl + -

By mastering these shortcuts, you can perform tasks more efficiently and improve your productivity in Excel.

Basic Navigation and Selection Shortcuts in Excel

Navigation Shortcuts

Action	Shortcut
Move cell up	?
Move cell down	?
Move cell left	?
Move cell right	?
Move to the beginning of row	Home
Move to the last cell in the current row	End ?
Move to the first cell in the worksheet	Ctrl + Home
Move to the last cell used in the sheet	Ctrl + End
Move one screen down	Page Down
Move one screen up	Page Up
Move one screen to the right	Alt + Page Down
Move one screen to the left	Alt + Page Up
Move to the next worksheet	Ctrl + Page Down
Move to the previous worksheet	Ctrl + Page Up

Selection Shortcuts

Action	Shortcut
Select entire column	Ctrl + Space
Select entire row	Shift + Space
Select the entire worksheet	Ctrl + A
Select the current region around the active cell	Ctrl + Shift + * (asterisk)
Select cells with data in the left of the active cell	Shift + ?
Select cells with data in the right of the active cell	Shift + ?
Select cells with data above the active cell	Shift + ?
Select cells with data below the active cell	Shift + ?
Extend the selection to the last non-empty cell in the row or column	Ctrl + Shift + ?, ?, ?, ?
Select entire row (after expansion)	Shift + Space, then Shift + ? or ?
Select entire column (after expansion)	Ctrl + Space, then Shift + ? or ?

Focus on mastering these keyboard shortcuts to significantly improve your navigation and selection efficiency in Excel, enhancing overall productivity in data analysis and formula usage.

Essential Shortcuts for Formulas in Excel

Insert and Edit Formulas

• Insert a formula: Alt + =
• Start a new line in the same cell: Alt + Enter
• Edit the active cell: F2

Navigation within Formulas

• Move cursor to the end of the formula: Ctrl + End
• Move cursor to the beginning of the formula: Ctrl + Home
• Select the argument within a formula: Ctrl + Shift + A
• Toggle absolute and relative references (cycle through): F4

Function and Name Usage

• Open the Insert Function dialog box: Shift + F3
• Insert the AutoSum formula: Alt + Shift + =
• Define a name: Ctrl + F3
• Paste a defined name into a formula: F3

Evaluating and Debugging Formulas

• Calculate active worksheet: Shift + F9
• Calculate all worksheets: F9
• Evaluate the selected part of the formula: F9 (within formula editing mode)
• Toggle between displaying cell values and formulas: Ctrl + ` (Ctrl + grave accent)

Selection and References

• Select entire worksheet: Ctrl + A
• Cycle through all combinations of absolute and relative references: F4
• Insert a function from the AutoComplete options: Tab

Array Formulas

• Enter array formula: Ctrl + Shift + Enter

Error Checking and Tracing

• Show trace precedents for the selected cell: Ctrl + [
• Show trace dependents for the selected cell: Ctrl + ]

Miscellaneous

• Sum selected cells: Alt + =
• Close the current workbook: Ctrl + W

Use these shortcuts to effectively improve your productivity and accuracy while working with formulas in Excel.

Data Entry and Editing Shortcuts in Excel

Shortcuts for Data Entry

• Enter Data Quickly
  • Ctrl + Enter: Fill selected cells with the current entry.
  • Alt + Enter: Start a new line within the same cell.
  • Shift + Enter: Complete entry and move to the cell above.

Fill Down and Fill Right

• Fill Series or Patterns
  • Ctrl + D: Fill down. Copies the data from the cell above.
  • Ctrl + R: Fill right. Copies the data from the cell to the left.

Rapid Editing

• Editing Cells
  • F2: Edit the active cell and position the insertion point at the end of the cell's contents.
  • Esc: Cancel the cell entry.
  • Ctrl + Z: Undo the last cell entry.
  • Ctrl + Y: Redo the last undone action.

Insert and Delete Operations

• Insert Rows and Columns
  • `Ctrl + Shift + "+": Insert new blank cells, rows, or columns.
  • `Ctrl + "-": Delete selected cells, rows, or columns.

Number and Date Formatting

• Apply Cell Formatting
  • Ctrl + Shift + $: Apply currency format.
  • Ctrl + Shift + %: Apply percentage format.
  • `Ctrl + Shift + #: Apply date format.

Miscellaneous

• Quick Navigation in Data Entry
  • Tab: Move to the next cell.
  • Shift + Tab: Move to the previous cell.
  • Ctrl + Arrow Keys: Move to the edge of data region in the direction of the arrow.

---

Apply these shortcuts practically in Excel to optimize data entry and editing workflow.

Unit 5: Advanced Excel Keyboard Shortcuts for Data Analysis

1. Summarize Data Quickly

• Alt + =
  • Automatically inserts the SUM function.

2. Create a Pivot Table

• Alt + N + V
  • Opens the PivotTable Wizard.

3. Grouping Data

• Shift + Alt + Right Arrow
  • Group selected rows/columns.

4. Ungrouping Data

• Shift + Alt + Left Arrow
  • Ungroup selected rows/columns.

5. Add Filters

• Ctrl + Shift + L
  • Toggle autofilter.

6. Refresh All Data

• Ctrl + Alt + F5
  • Refreshes all data connections and PivotTables.

7. Insert and Delete Cells, Rows, Columns

• Insert Cells: Ctrl + Shift + +
• Delete Cells: Ctrl + -

8. Trace Precedents/Dependents

• Ctrl + [
  • Trace precedents of selected cell.
• Ctrl + ]
  • Trace dependents of selected cell.

9. Cycle through Open Workbooks

• Ctrl + Tab
  • Switch to the next open workbook.

10. Repeat Last Action

• F4
  • Repeats the last command or action, if possible.

11. Format Painter

• Ctrl + Shift + C
  • Copy formatting.
• Ctrl + Shift + V
  • Apply copied formatting.

12. Adjust Column Widths

• Alt + O + C + A
  • Auto fits selected column(s) width according to data.

13. Toggle Gridlines

• Alt + W + V + G
  • Show or hide gridlines in the active worksheet.

14. Toggle Formula View

• Ctrl + ~
  • Toggle between displaying cell values and cell formulas.

---

This unit offers advanced shortcuts efficiently catered to data analysis tasks in Excel. Integrate these shortcuts to enhance your data analysis workflows seamlessly.

Formatting and Cell Manipulation Shortcuts in Excel

Keyboard Shortcuts for Formatting

Font and Alignment

Bold

• Ctrl + B: Bold the selected text or remove bold formatting.

Italics

• Ctrl + I: Italicize the selected text or remove italic formatting.

Underline

• Ctrl + U: Underline the selected text or remove underline formatting.

Align Center

• Alt + H + A + C: Center-align the cell content.

Align Left

• Alt + H + A + L: Left-align the cell content.

Align Right

• Alt + H + A + R: Right-align the cell content.

Number Formatting

General Format

• Ctrl + Shift + ~: Apply the general number format.

Currency Format

• Ctrl + Shift + $: Format cells as currency.

Percentage Format

• Ctrl + Shift + %: Format cells as a percentage.

Number Format

• Ctrl + Shift + !: Apply the number format with two decimal places.

Date Format

• Ctrl + Shift + #: Apply the date format.

Cell Borders and Fill

Add Border

• Ctrl + Shift + 7: Add an outline border to the selected cell(s).

Remove Border

• Ctrl + Shift + –: Remove all borders from the selected cell(s).

Fill Color

• Alt + H + H: Open the fill color menu.

Font Color

• Alt + H + FC: Open the font color menu.

Cell Manipulation Shortcuts

Insert and Delete

Insert Cells

• Ctrl + Shift + "+": Open the "Insert" dialog to insert cells, rows, or columns.

Delete Cells

• Ctrl + "-": Open the "Delete" dialog to delete cells, rows, or columns.

Rows and Columns

Insert Row

• Shift + Space: Select the entire row.
• Ctrl + Shift + "+": Insert a new row above.

Insert Column

• Ctrl + Space: Select the entire column.
• Ctrl + Shift + "+": Insert a new column to the left.

Delete Row

• Shift + Space: Select the entire row.
• Ctrl + "-": Delete the selected row.

Delete Column

• Ctrl + Space: Select the entire column.
• Ctrl + "-": Delete the selected column.

Row Height and Column Width

Auto-fit Column Width

• Alt + H + O + I: Auto-fit the width of the selected column(s).

Auto-fit Row Height

• Alt + H + O + A: Auto-fit the height of the selected row(s).

Set Column Width

• Alt + H + O + W: Open the "Column Width" dialog.

Set Row Height

• Alt + H + O + H: Open the "Row Height" dialog.

Merge and Unmerge Cells

Merge Cells

• Alt + H + M + M: Merge cells and center the content.

Unmerge Cells

• Alt + H + M + U: Unmerge cells.

Summary Table

Shortcut Key	Action
Ctrl + B	Bold
Ctrl + I	Italics
Ctrl + U	Underline
Alt + H + A + C	Center-align
Alt + H + A + L	Left-align
Alt + H + A + R	Right-align
Ctrl + Shift + ~	General format
Ctrl + Shift + $	Currency format
Ctrl + Shift + %	Percentage format
Ctrl + Shift + !	Number format
Ctrl + Shift + #	Date format
Ctrl + Shift + 7	Add border
Ctrl + Shift + –	Remove border
Alt + H + H	Fill color menu
Alt + H + FC	Font color menu
Ctrl + Shift + "+"	Insert cells/rows/columns
Ctrl + "-"	Delete cells/rows/columns
Shift + Space then Ctrl + Shift + "+"	Insert row
Ctrl + Space then Ctrl + Shift + "+"	Insert column
Shift + Space then Ctrl + "-"	Delete row
Ctrl + Space then Ctrl + "-"	Delete column
Alt + H + O + I	Auto-fit column width
Alt + H + O + A	Auto-fit row height
Alt + H + O + W	Column width dialog
Alt + H + O + H	Row height dialog
Alt + H + M + M	Merge cells
Alt + H + M + U	Unmerge cells

Utilize these shortcuts to enhance your efficiency in Excel.

Shortcuts for Managing Worksheets and Workbooks

Worksheet Management Shortcuts

Switch Between Worksheets

• Next Worksheet: Ctrl + Page Down
• Previous Worksheet: Ctrl + Page Up

Add, Rename, and Delete Worksheets

• Add New Worksheet: Shift + F11
• Rename Current Worksheet: Alt + H, O, R
• Delete Current Worksheet: Alt + H, D, S

Move or Copy Worksheet

• Move Worksheet: Alt + E, M
• Copy Worksheet: Alt + E, M

Workbook Management Shortcuts

Switch Between Workbooks

• Next Workbook: Ctrl + Tab
• Previous Workbook: Ctrl + Shift + Tab

Open and Close Workbooks

• Open Workbook: Ctrl + O
• Close Workbook: Ctrl + W

Save and Save As

• Save Workbook: Ctrl + S
• Save As: F12

New Workbook

• New Workbook: Ctrl + N

Protect Workbook

• Protect Workbook: Alt + R, P, W

Print and Print Preview

• Print: Ctrl + P
• Print Preview: Ctrl + F2

By using these keyboard shortcuts, you'll efficiently manage your worksheets and workbooks in Excel.

Practice Scenarios and Case Studies for Mastering Excel Keyboard Shortcuts

Scenario 1: Analyzing Sales Data

1. Navigate to Sales Data Worksheet:

   • Ctrl + PgDn to move to the next worksheet until you reach the "Sales Data" sheet.

2. Select Sales Data Range:

   • Ctrl + Shift + Arrow Keys to select the entire range of sales data.

3. Create a Pivot Table:

   • Alt + N + V to open the PivotTable creation dialog.
   • Enter to create it in a new worksheet.

4. Pivot Table Configuration:

   • Use Arrow Keys to navigate and the Spacebar to select fields for Rows, Columns, and Values.

5. Format Values:

   • Ctrl + 1 to open the Format Cells dialog.
   • Use Arrow Keys to navigate through options and Enter to apply.

Scenario 2: Budget Planning

1. Open Recent Worksheet:

   • Ctrl + O to open recent documents.
   • Use Arrow Keys to navigate and Enter to select the desired file.

2. Enter Data:

   • Use Tab for horizontal navigation and Enter for vertical navigation between cells to fill in budget values.

3. Calculate Totals:

   • Alt + = to insert the AutoSum function and Enter to complete the sum formula.

4. Apply Conditional Formatting:

   • Alt + H + L to open the Conditional Formatting menu.
   • Use Arrow Keys to choose formatting options and Enter.

5. Navigate and Review:

   • Ctrl + Arrow Keys to quickly move through the dataset for review.

Scenario 3: Creating Monthly Reports

1. Duplicate a Worksheet:

   • Ctrl + Shift + Drag the worksheet tab to create a copy.

2. Update Report Data:

   • Use Ctrl + F to find and replace dates or values.
   • Fill in new data using Ctrl + D to duplicate cells vertically or Ctrl + R horizontally.

3. Insert Charts:

   • Select data range using Shift + Arrow Keys.
   • Alt + F1 to create a default chart.

4. Copy and Paste Charts:

   • Ctrl + C to copy the chart.
   • Navigate to the destination sheet using Ctrl + PgDn and Ctrl + V to paste.

5. Update Chart Data Source:

   • F11 to insert the chart and then Right-Click and select "Select Data" to update.

Scenario 4: Data Cleaning and Preparation

1. Navigate to Raw Data Sheet:

   • Use Ctrl + PgUp or Ctrl + PgDn.

2. Remove Duplicates:

   • Select range using Ctrl + Shift + Arrow Keys.
   • Alt + A + M to open the Remove Duplicates dialog.

3. Text and Formula Editing:

   • F2 to edit cell content.
   • Ctrl + Shift + U to expand the formula bar.

4. Use Flash Fill:

   • Enter sample data.
   • Use Ctrl + E to automatically fill data.

5. Convert Data Types:

   • Alt + H + E to open the Clear menu, then choose Clear Formats.
   • Change data type using Ctrl + 1 to format cells.

This practical guide helps you apply your learning and improve your efficiency with Excel's keyboard shortcuts through realistic scenarios.