Excel Basics – Unit 1 Notes with Example
1) How to open a file
• File → Open → Browse/Recent/Cloud, then select file.
• Shortcuts: Ctrl + O, Ctrl + N, double-click file in explorer, drag & drop into Excel.
• Data → Get Data → From Text/CSV/Workbook for structured imports (Power Query).
2) Objective of Excel in Data Analytics
• Data capture & storage (tables, named ranges).
• Cleaning & preparation (remove duplicates, fix formats).
• Exploratory analysis (functions, pivots).
• Business analysis (What-if, Goal Seek, Scenario Manager).
• Visualization & reporting (charts, dashboards).
• Automation (Power Query refresh, macros).
• Collaboration (comments, co-authoring, protection).
3) Introduction to the Data Tab
• Get & Transform Data (Power Query) – import, transform, refresh.
• Sort & Filter – sort A–Z, filter by text, number, date, color.
• Data Tools – Text to Columns, Flash Fill, Remove Duplicates, Data Validation.
• Forecast – Goal Seek, Scenario Manager, Forecast Sheet.
• Outline – Group/Ungroup, Subtotal.
• Queries & Connections – manage queries, refresh settings.
4) Data Validation – Objective & Process
• Objective: restrict input for accuracy and consistency.
• Types: whole number, decimal, list (dropdown), date/time, text length, custom formulas.
• Steps: Select range → Data Validation → Allow type → Set criteria → Input message & error alert.
• Examples:
• • No blanks: =LEN(A2)>0
• • No duplicates: =COUNTIF($A$2:$A$100,A2)=1
• • Date not in future: =A2<=TODAY()
5) Sorting & Filtering (Basics)
• Sort: A–Z, Z–A, custom multi-level, by color/icon.
• Filter: by value, text/number/date condition, by color.
• Shortcuts: Alt + ↓ (filter menu), Ctrl + Shift + L (toggle filter).
• Use SUBTOTAL instead of SUM for filtered ranges.
6) Hypothetical Case Problem
• Scenario: Analyze monthly sales CSV with columns (Date, OrderID, Region, Product, Qty, Unit Price,
Salesperson).
• Steps:
• • Open via Data → Get Data → From Text/CSV, clean with Power Query.
• • Add Amount column = Qty * Unit Price.
• • Apply Data Validation: Qty ≥0, valid categories, unique OrderIDs.
• • Sort & filter: top 5 orders, region/category-specific, last 7 days.
• • Pivot Table: Region → Product with Sum of Amount & Qty.
• • Use Slicers for interactive filtering.
Sample Dataset (for practice)
Date OrderID Region Product Category Qty Unit Price
01-Apr-2025 O1001 North Laptop Electronics 2 50000
02-Apr-2025 O1002 South Headphones Accessories 5 2000
03-Apr-2025 O1003 East Tablet Electronics 3 15000
04-Apr-2025 O1004 West Mouse Accessories 10 500
05-Apr-2025 O1005 North Printer Electronics 1 12000
Use this dataset to practice opening files, applying data validation, sorting/filtering, and creating pivot tables.
