Spreadsheets for Business: MS Excel Basics, Formulas, Charts, Pivot Tables and Data Analysis

Introduction to spreadsheets and MS Excel

A spreadsheet is an electronic worksheet used to store, organise and analyse data using rows and columns.

MS Excel is widely used in business for:

• calculations and budgeting
• sales and inventory tracking
• data analysis and dashboards
• charts and reports

Workbook, worksheet, rows, columns and cells

• Workbook: Excel file containing one or more worksheets.
• Worksheet: a single sheet where data is entered.
• Row: horizontal line (1,2,3...).
• Column: vertical line (A,B,C...).
• Cell: intersection of row and column (e.g., A1).

Each cell can contain text, numbers, dates or formulas.

Data entry, formatting and basic operations

Basics:

• enter data in cells, use autofill for series
• format numbers (currency, percentage)
• apply borders, font style, alignment
• freeze panes for headings

Business best practice:

• keep headings clear (Date, Item, Qty, Amount)
• avoid mixing text and numbers in same column

Formulas and operators (relative/absolute references)

A formula starts with =.

Operators:

• + addition, - subtraction, * multiplication, / division

Cell references:

• Relative reference: changes when copied (A1).
• Absolute reference: fixed using $ (e.g., $A$1).

Example:

• =B2*C2 (amount = qty × rate)

Common Excel functions for business (SUM, AVERAGE, IF, COUNTIF, VLOOKUP)

Common functions:

• SUM(range) total
• AVERAGE(range) average
• MAX(range), MIN(range)
• IF(condition, value_if_true, value_if_false)
• COUNTIF(range, criteria) count matching records
• SUMIF(range, criteria, sum_range)
• VLOOKUP(value, table, col_index, FALSE) lookup value in a table

Exam note: IF and lookup functions are frequently asked.

Charts and graphs (column, line, pie) and business uses

Charts convert data into visuals.

Types:

• Column chart: compare categories (sales by product)
• Line chart: show trends over time (monthly sales)
• Pie chart: show proportion (market share)

Business uses:

• management presentations
• performance comparison
• trend analysis

Sorting, filtering and data validation (basic)

• Sorting: arrange data A→Z, smallest→largest.
• Filtering: show only required records (e.g., only one region).
• Data validation: restrict input (e.g., allow only dates or dropdown list).

These features improve accuracy and speed.

Pivot tables: meaning, steps and uses

A pivot table summarises large data quickly.

Steps (simple):

1. Select data range with headings
2. Insert → PivotTable
3. Drag fields into Rows, Columns, Values, Filters

Uses:

• region-wise sales summary
• category totals
• monthly performance report

Basic data analysis in Excel (what-if, conditional formatting)

Tools:

• Conditional formatting: highlight high/low values, duplicates
• What-if analysis (basic): see impact of changing values (e.g., price change)
• Goal seek (basic): find input needed to reach target

Business reports in Excel (sales, inventory, budget)

Common Excel reports:

• sales report (product/region wise)
• inventory report (stock in/out, reorder level)
• budget vs actual report
• expense tracking

Excel supports decision-making through timely reports.

Useful Excel shortcuts (productivity)

• Ctrl+S save
• Ctrl+C copy, Ctrl+V paste
• Ctrl+Z undo
• Ctrl+F find
• Ctrl+1 format cells
• Alt+= auto sum

Key terms explained

• Workbook/Worksheet — file/sheet.
• Cell reference — address like A1.
• Function — built-in formula.
• Pivot table — summary table.
• Conditional formatting — rules-based highlighting.

Quick recap (1-minute revision)

• Excel is used for calculations, reports and analysis.
• Formulas start with = and use cell references.
• Functions like SUM, IF, COUNTIF and VLOOKUP are important.
• Pivot tables summarise large data quickly.
• Charts show trends and comparisons.