Excel Training Outline
1. 🔍 Introduction to Excel Interface, Navigation & Shortcuts
Key Concepts
• Ribbon & Tabs, Backstage View, Quick Access Toolbar, Navigation Pane
Essential Keyboard Shortcuts
Action Shortcut
New workbook Ctrl + N
Open workbook Ctrl + O
Save Ctrl + S
Copy / Paste Ctrl + C / Ctrl + V
Cut Ctrl + X
Undo / Redo Ctrl + Z / Ctrl + Y
Find / Replace Ctrl + F / Ctrl + H
Jump to edge of data region Ctrl + Arrow Key
Select to edge of region Ctrl + Shift + Arrow Key
Select entire column Ctrl + Space
Select entire row Shift + Space
Select entire worksheet Ctrl + Shift + Space
Edit active cell F2
Repeat last action F4
Fill down Ctrl + D
Fill right Ctrl + R
AutoSum Alt + =
Insert current date Ctrl + ;
Insert current time Ctrl + Shift + ;
Toggle formulas view Ctrl + `
Format cells dialog Ctrl + 1
Cycle forward – tabs Ctrl + Page Down
Cycle back – tabs Ctrl + Page up
2. 📂 Creating & Managing Worksheets and Workbooks
Key Concepts
• Create/Open/Save (.xlsx, .xlsm, .csv)
• Insert/Delete/Rename worksheets
• Group/Ungroup, Hide/Unhide, Protect Structure
Sample Dataset: Project Tracker
Project ID | Project Name | Start Date | End Date | Status
P001 | Website Redesign | 2023-01-15 | 2023-04-30 | In Progress
P002 | Mobile App Launch | 2023-02-01 | 2023-05-15 | Planned
P003 | Data Migration | 2023-03-10 | 2023-06-20 | Not Started
3. ✍️ Entering & Formatting Data (& Format Painter)
Key Concepts
• Cell Entry, AutoFill
• Format Cells: Font, Border, Fill, Alignment, Protection
• Number Formats: Currency, Date, Percentage, Custom
• Conditional Formatting: Color scales, Data bars, Icon sets
• Total: =Quantity * UnitPrice
Sample Dataset: Office Supply Sales
OrderID | Date | Category | Quantity | UnitPrice | Total
1001 | 2023-01-05 | Paper | 50 | 0.50 | 25.00
1002 | 2023-01-06 | Binder | 20 | 1.50 | 30.00
1003 | 2023-01-07 | Pen | 100 | 0.20 | 20.00
4. 🧮 Calculations with Formulas & Functions
Key Concepts
• Formulas: =A1+B1, parentheses
• Functions: Math (SUM(), AVERAGE(), etc.), Logical (IF(), AND(), etc.),SUMIF(), Lookup
(VLOOKUP(), etc.), Error Handling (IFERROR()), Today’s Date & Time (Now()), Today's Date
(Today()), Datedif() (to find the number of days, months or years between two dates)
• References: Relative vs. Absolute (A1 vs. $A$1)
Sample Exercises: High/Low Flag: =IF(Total>25,"High","Low")
5. 📊 Managing Data with Tables, Text to Columns, Text Extraction &
Data Tools
Key Concepts
• Lookup: VLOOKUP(), HLOOKUP(), INDEX()/MATCH(), XLOOKUP() (extra)
• Excel Tables (Ctrl + T), Structured References
• Sorting & Filtering, Remove Duplicates
• Data Validation: dropdowns, date limits
• Highlighting Duplicates: Home tab → Conditional Formatting → Highlight Cell Rules →
Duplicate Values
• Flash Fill
• Text Extraction: LEFT(), RIGHT(), MID(), LEN(), FIND(), TEXTSPLIT() (Excel 365)
• Text Manipulation: LOWER(), UPPER(), PROPER()
Text to Columns Exercise:
1. Select the FullName column → Data → Text to Columns
2. Choose Delimited → Next → select Space → Finish
Text Extraction Exercise:
First Name: =LEFT(FullName,FIND(" ",FullName)-1)
Last Name: =RIGHT(FullName,LEN(FullName)-FIND(" ",FullName))
Sample Dataset: Employee Records
EmployeeID | FullName | JobTitle | HireDate | Salary
1 | Jane Doe | Analyst | 2020-03-15 | 60000
2 | John Smith | Manager | 2018-07-22 | 80000
3 | Alice Johnson | Developer | 2019-11-01 | 70000
6. 📦 Analyzing Data with PivotTables, PivotCharts & Extras
Key Concepts
• Insert PivotTable → drag fields to Rows/Columns/Values/Filters
• PivotCharts
• Copy and Paste (Right-Click, select 1,2,3) to paste values alone
• Copy and Paste Special (Right-Click, select Paste Special) and under Operation either Add,
Subtract, Multiple or Divide by the copied number
Sample PivotTable: Sales by Category
Category | Sum of Total
Binder | 3,500
Paper | 5,000
Pen | 4,200
7. 📈 Visualizing Data with Charts, Sparklines & Extras
Key Concepts
• Insert Charts: Column, Line, Pie, Bar
• Recommended Charts, Chart Design & Format
• Trendlines, Error Bars, Data Labels
• Sparklines: in-cell mini charts
Sample Dataset: Monthly Revenue
Month | Revenue
Jan 2023 | 50,000
Feb 2023 | 52,000
Mar 2023 | 48,000
8. 🤝 Collaborating, Sharing & Protection
Key Concepts
• Share & Co-author via OneDrive/SharePoint
• Protect Sheets/Workbooks, Set Editable Ranges
• Track Changes, Comments, Version History
