Free Excel Course Part 1 — Navigation & Formatting | 14 Topics, 40+ Shortcuts & Formulas Explained

By Subham Ghadge  |  Subu Data Labs  |  Excel Training & Free Workbooks  |  July 2026

If you use Excel every day at work—whether in finance, accounting, HR, office administration, or data management—and find yourself repeating tasks manually or searching for shortcuts, this guide is designed for you.

Many working professionals learn Excel informally over time without building a clear foundation. You might use advanced functions like VLOOKUP daily, but still spend extra time highlighting large data ranges with a mouse instead of using simple shortcuts like Ctrl + Shift + Arrow.

This guide is Part 1 of a comprehensive 3-part Excel training series. It covers 14 foundational topics on navigation, formatting, cell locking, and data cleaning—the core skills needed before building complex spreadsheets. Each topic includes step-by-step explanations, real-world examples, and formula references that you can practice directly using the downloadable Excel workbook.

---

Curriculum Overview — All 14 Topics

---

Relying heavily on a mouse to navigate large datasets slows down daily spreadsheet tasks. Learning key navigation shortcuts builds muscle memory and speeds up data entry significantly.

---

Professional formatting goes beyond font colors. Adopting consistent formatting standards makes financial models and executive reports easier to read and maintain.

Error Handling Syntax (IFERROR):

=IFERROR(D15/D14, 0)     ← Displays 0 if a division error occurs
=IFERROR(D15/D14, "")    ← Displays a blank cell if an error occurs
=IFERROR(D15/D14, "N/A") ← Displays custom text if an error occurs

Cell Lock States (F4 Key):

$F$9  ← Locks both row and column references (Press F4 once)
F$9   ← Locks row reference only (Press F4 twice)
$F9   ← Locks column reference only (Press F4 three times)

---

Date calculations are essential across finance, payroll, and logistics workflows. Using built-in date functions ensures payment schedules, invoice aging, and quarter roll-ups compute accurately.

Quarter Classification Formula:

="Q"&ROUNDUP(MONTH(A1)/3,0)&" "&YEAR(A1)
→ Evaluates a date in cell A1 to: "Q1 2026"

Month-End Date Calculations (EOMONTH):

=EOMONTH(A1,  0)  → Jan 31, 2026  (End of active month)
=EOMONTH(A1,  1)  → Feb 28, 2026  (End of next month)
=EOMONTH(A1, -1)  → Dec 31, 2025  (End of prior month)

---

Calculating shipment delivery dates or contract completion deadlines requires accounting for non-working days. The WORKDAY function adds a specified number of business days to a start date, skipping weekends and optional holidays automatically.

Function Syntax:

=WORKDAY(start_date, days, [holidays])

Practical Example — Delivery Date Calculation:

=WORKDAY(Order_Date, Lead_Time_Days, Holiday_Range)

Order Date:       Dec 15, 2025
Lead Time Days:   28 Workdays
Holidays List:    Jan 1 (New Year's Day — excluded automatically)
→ Output Result:  Jan 26, 2026 ✅

---

Understanding the root cause of standard Excel error codes helps you troubleshoot and fix broken formulas quickly.

Wrapping Formulas with IFERROR:

=IFERROR(Target_Formula, 0)      ← Replaces error code with 0
=IFERROR(Target_Formula, "N/A")  ← Replaces error code with text
=IFERROR(Target_Formula, "")     ← Replaces error code with a blank cell

---

✅ Correct Method: Cut =SUM(E31:E46) from D22 → Paste into D23
   Formula moves cleanly to D23 without leaving a duplicate calculation in D22.

❌ Incorrect Method: Copy =SUM(E31:E46) from D22 → Paste into D23
   Formula now runs in BOTH D22 and D23, risking duplicate totals.

---

Auditing complex financial models requires understanding how calculations link together. Formula auditing arrows allow you to trace input sources and downstream formula dependencies visually.

Auditing Workflow:

1. Select the target cell you want to inspect.
2. Go to the Formulas tab → click Trace Precedents to see source inputs.
3. Click Trace Dependents to see downstream formulas linked to the cell.
4. Blue tracer arrows will display across the worksheet mapping references.
5. Click Remove Arrows when audit review is complete.

Common Audit Scenarios:

• Verifying the calculation lineage of key financial KPI summary numbers.
• Confirming whether a cell is referenced elsewhere before deleting it.
• Troubleshooting #REF! or calculation errors in inherited workbooks.

---

Using Group & Ungroup lets you collapse detail rows to show summary views without hiding rows permanently or deleting data.

How to Group Data Rows:

1. Highlight the detail rows you want to collapse (do not include the summary total row).
2. Go to the Data tab → click Group.
3. A toggle bar displaying + / − buttons appears in the margin.
4. Click − to collapse detail rows; click + to expand them.

Nested Group Structure Example:

▼ TOTAL CASH RESERVES          ← Level 1 Group (Collapses entire section)
  ▼ Operational Cash           ← Level 2 Group
      Petty Cash - Main Office
      Petty Cash - Factory Yard
  ▼ Bank Accounts              ← Level 2 Group
      HDFC Current Account
      SBI Corporate Account

---

Before sharing workbooks with external teams, apply cell protection to prevent accidental edits to background calculation formulas.

Steps to Protect a Worksheet:

1. Go to the Review tab → click Protect Sheet.
2. Select permitted user actions (e.g., selecting cells, applying filters).
3. Enter a protection password → click OK.

Steps to Unlock Specific Data Entry Cells:

1. Select the specific input cells users need to edit.
2. Right-click → Format Cells → Protection tab → uncheck Locked.
3. Go to Review tab → Protect Sheet.
4. All cells remain locked except the specific input range unlocked in step 2.

---

Excel includes an integrated screenshot capture feature. This allows you to capture active application windows (such as ERP screens or banking portals) and insert image clips directly into your worksheet without using external apps.

Capture Steps:

1. Open the application window you want to capture in the background.
2. In Excel, go to the Insert tab → click Screenshot.
3. Select the target window thumbnail from Available Windows.
4. The screenshot image inserts directly onto the active worksheet.

---

Sorting complex datasets often requires multi-level sorting—such as sorting records first by Region, then by Branch, and finally by Transaction Amount.

Multi-Level Sort Setup:

1. Select the complete data table (including all data columns).
2. Go to Home tab → Sort & Filter → Custom Sort.
3. Select primary column under "Sort by" (e.g., Region, A to Z).
4. Click Add Level to configure secondary sort rules (e.g., Sales Amount, Largest to Smallest).
5. Click OK to execute the multi-column sort.

---

Raw exports from accounting systems or CRM databases frequently contain repeated entries. Cleaning duplicate rows before creating summary reports ensures metric accuracy.

De-duplication Process:

1. Highlight the target table or data range.
2. Go to the Data tab → click Remove Duplicates.
3. Select which key columns to evaluate for identical entries.
4. Click OK; Excel removes duplicate rows and reports the number of records removed.

De-duplication Example:

Original List:   Mumbai, Pune, Pune, Delhi, Mumbai, Bengaluru, Delhi
Cleaned Output:  Mumbai, Pune, Delhi, Bengaluru

---

Data exported from external databases often combines multiple fields into a single cell (e.g., employee name and phone number separated by a colon). Text to Columns splits these strings cleanly into separate fields.

Splitting Steps:

1. Select the column containing combined text strings.
2. Go to the Data tab → click Text to Columns.
3. Select Delimited → click Next.
4. Choose the delimiter character (Comma, Space, Tab, or type custom character like : under Other).
5. Click Finish to split data into adjacent columns.

Transformation Example:

---

Conditional formatting applies cell highlights, data bars, or color scales automatically based on cell values—making variances and outliers easy to spot.

Applying a Value Highlight Rule:

1. Highlight the data range you want to monitor.
2. Go to Home tab → Conditional Formatting → Highlight Cells Rules → Less Than.
3. Enter the target numeric threshold (e.g., 4000000).
4. Select highlight styling (e.g., Light Red Fill with Dark Red Text).
5. Click OK; formatting updates automatically whenever cell values change.

---

Course Roadmap — Part 2 & Part 3

---

Download Practice Workbook — Free

Download the accompanying Excel practice workbook containing all 14 topic modules. Each topic is organized into a dedicated tab with practical datasets and exercise instructions.