Unit 2: Excel for business analytics
Computer Applications and Data Visualization for Managers notes · PTU syllabus (MBA 208-26)
On this page
- Unit summary
- Workbook creation and formatting
- Sorting, filtering and data validation
- Financial and statistical functions
- Logical, lookup and text functions
- Conditional formatting
- Pivot tables
- Charts
- Dashboard creation in Excel
- Scenario and what-if analysis
- AI-assisted spreadsheet tools
- Key terms
- Quick revision
- Important questions
Unit summary
Excel remains the manager's everyday analytics tool. This unit covers workbook creation and formatting, sorting, filtering and data validation, financial, logical, statistical and text functions, conditional formatting, pivot tables, charts and dashboards, scenario and what-if analysis, and AI-assisted spreadsheet tools.
After this unit you can
- Create and format workbooks and manage data with sorting, filtering and validation
- Apply financial, logical, statistical and text functions
- Summarise data with conditional formatting, pivot tables, charts and dashboards
- Use scenario and what-if analysis and AI-assisted tools
PTU syllabus topics
- Workbook creation
- formatting
- sorting
- filtering
- data validation
- financial/logical/statistical/text functions
- conditional formatting
- pivot tables
- charts and dashboard creation
- scenario and what-if analysis
- AI-assisted spreadsheet tools
- 1
Clean the data as a table
- 2
Summarise with pivot tables
- 3
Create pivot charts
- 4
Add slicers for filters
- 5
Highlight with conditional formatting
- 6
Arrange on one sheet
Topic 1
Workbook creation and formatting
A workbook contains worksheets; each cell has an address (column letter + row number), e.g. C5.
- Enter text, numbers, dates and formulas (starting with =).
- Format numbers (currency ₹, percentage, decimals), fonts, borders, fill colours and column widths.
- AutoFill copies formulas or continues series (Jan, Feb, …).
- Relative references (A1) change when copied; absolute references ($A$1) stay fixed.
Topic 2
Sorting, filtering and data validation
- Sort: Data → Sort — by one or more columns, ascending or descending, or by custom list.
- Filter: Data → Filter — show only rows meeting criteria (text, number, date filters); Advanced Filter copies results elsewhere.
- Tables (Ctrl+T): auto-expanding ranges with structured references, banded rows and built-in filters.
- Data validation: Data → Data Validation — restrict entries to a list (drop-down), whole numbers, dates or a range; add input messages and error alerts.
Example
A sales entry sheet uses a drop-down list of regions and allows discounts only between 0% and 20%, preventing typing errors.
Topic 3
Financial and statistical functions
| Function | Purpose | Example |
|---|---|---|
| SUM / AVERAGE | Total / mean | =SUM(B2:B10) |
| MAX / MIN / COUNT | Highest / lowest / count | =MAX(C2:C20) |
| IF | Conditional result | =IF(D2>=40,"Pass","Fail") |
| PMT | Loan instalment | =PMT(10%/12, 60, -500000) |
| FV / PV | Future / present value | =FV(8%, 5, -10000) |
| NPV / IRR | Investment appraisal | =NPV(10%, B2:B6) |
| STDEV / MEDIAN | Spread / middle value | =STDEV.S(E2:E50) |
Example
A ₹5,00,000 loan at 10% a year for 5 years: =PMT(10%/12, 60, −500000) gives an EMI of about ₹10,624.
Topic 4
Logical, lookup and text functions
| Function | Purpose | Example |
|---|---|---|
| IF | Returns one value if a test is true, another if false | =IF(B2>=50000,"Target met","Below target") |
| AND / OR | Combine conditions | =IF(AND(B2>0,C2="Paid"),"OK","Check") |
| IFERROR | Replace errors with a value | =IFERROR(B2/C2,0) |
| SUMIFS / COUNTIFS | Sum or count with conditions | =SUMIFS(D:D,A:A,"North",B:B,"Q1") |
| VLOOKUP / XLOOKUP | Find a value in a table | =XLOOKUP(E2,A:A,C:C) |
| CONCAT / TEXTJOIN | Join text | =CONCAT(A2," ",B2) |
| LEFT / RIGHT / MID / LEN | Extract or count characters | =LEFT(A2,3) |
| TRIM / UPPER / PROPER | Clean and standardise text | =PROPER(TRIM(A2)) |
Topic 5
Conditional formatting
- Home → Conditional Formatting: highlight cells greater than a value, top or bottom 10, duplicates; data bars, colour scales and icon sets; formula-based rules.
- Uses: flag overdue invoices, highlight branches below target, show heat maps of sales by month and region.
Topic 6
Pivot tables
A pivot table summarises large data by dragging fields into Rows, Columns, Values and Filters.
- 1
Clean data in a table
- 2
Insert → PivotTable
- 3
Drag fields to Rows, Columns, Values, Filters
- 4
Choose summary (sum, count, average, % of total)
- 5
Add slicers and timelines
- 6
Insert a PivotChart
- Features: grouping dates by month or quarter, calculated fields, drill-down by double-click, refresh when data change.
Topic 7
Charts
Column/bar
Comparisons between categories
Sales by region
Line
Trends over time
Monthly revenue
Pie
Parts of a whole
Expense share
Scatter
Relationship between two variables
Advertising vs sales
Insert → Charts; add titles, axis labels and data labels for clarity.
Topic 8
Dashboard creation in Excel
- Dashboard: a single screen showing key metrics and trends for quick decisions.
- Steps: define KPIs and audience, prepare data in tables, build pivot tables and charts, add slicers for interactivity, arrange on one sheet with clear titles, protect and share.
- Design tips: most important KPI at top left, consistent colours, avoid 3D charts, show targets vs actuals.
Topic 9
Scenario and what-if analysis
Scenario Manager
Store and compare sets of inputs (best, base, worst case)
Goal Seek
Find the input needed to reach a target result
Data Table
Show results for many values of one or two inputs
Solver (add-in)
Optimise a result subject to constraints
Example
Goal Seek: what monthly sales volume makes profit ₹5 lakh? Set the profit cell to 5,00,000 by changing the volume cell; Excel finds the break-even-style answer instantly.
Topic 10
AI-assisted spreadsheet tools
- Excel features: Analyze Data (natural-language questions and suggested charts), Flash Fill (Ctrl+E) for pattern-based text cleaning, Copilot in Microsoft 365 (formula suggestions, summaries, chart creation), Python in Excel.
- Google Sheets: Explore and Gemini assistance.
- Cautions: verify AI-generated formulas and outputs, protect confidential data, keep an audit trail.
Key terms
- Data validation
- Rules restricting what can be entered in a cell
- XLOOKUP
- Function that finds a value and returns a matching item
- Pivot table
- Interactive summary of a data table
- Goal Seek
- Finds the input needed for a target output
- Slicer
- Visual filter button for pivot tables and charts
Quick revision
- Workbooks, formatting, tables; sort, filter, validation.
- Functions: financial (PMT, NPV, IRR), statistical, logical (IF, AND), lookup, text.
- Conditional formatting; pivot tables and pivot charts.
- Dashboards with slicers; chart choice.
- Scenario Manager, Goal Seek, Data Table, Solver; AI tools.
Important exam questions
Practice questions written to the PTU exam pattern for this unit's syllabus: short answers (Section A style) and long answers (Sections B and C style).
Short-answer questions
- Q1.What is data validation?
- Q2.Write an IF formula to show "Pass" for marks of 40 or more.
- Q3.What is a pivot table?
- Q4.Distinguish Goal Seek and Scenario Manager.
- Q5.What is conditional formatting?
- Q6.Name two AI-assisted features in Excel.
Long-answer questions
- Q1.Explain sorting, filtering and data validation in Excel.
- Q2.Explain financial, logical and text functions with examples.
- Q3.Explain how pivot tables and charts are used to build a dashboard.
- Q4.Discuss scenario and what-if analysis tools in Excel.
Stuck on this unit?
Message SBS on WhatsApp for help with Computer Applications and Data Visualization for Managers, or to ask about studying MBA at Synetic.