Data Tools, Pivot Tables and Charts
Organising data
| Tool | What it does |
|---|---|
| Sort | Data → Sort A to Z / Z to A. Multi-level sort and custom lists are possible. |
| Filter | Ctrl + Shift + L turns AutoFilter on. Hides rows that do not match. Advanced Filter uses a separate criteria range and can copy results elsewhere. |
| Remove Duplicates | Deletes repeated rows. |
| Text to Columns | Splits one column at a delimiter or fixed width. |
| Flash Fill | Ctrl + E. Fills by copying a pattern. |
| Freeze Panes | Keeps top rows or left columns visible while scrolling. |
| Subtotal | Inserts SUBTOTAL rows per group. The data must be sorted by that column first. |
| Group / Outline | Collapses and expands rows or columns. |
| Excel Table | Ctrl + T. Turns a range into a table that grows automatically and uses structured references. |
| Paste Special | Pastes only values, formulas, formats, or transposes; can add, subtract, multiply or divide while pasting. |
Data Validation
Data → Data Validation restricts what may be typed. Types: Whole number, Decimal, List (a drop-down), Date, Time, Text length, Custom (a formula). It also has an Input Message and an Error Alert with three styles: Stop, Warning, Information. Circle Invalid Data marks entries that break the rule.
Conditional Formatting
Home → Conditional Formatting formats cells automatically by rule: Highlight Cells Rules (greater than, duplicate values), Top/Bottom Rules, Data Bars, Color Scales, Icon Sets. A formula rule such as =$C2<40 colours a whole row for a failed student.
What-If Analysis
| Tool | Use |
|---|---|
| Goal Seek | Finds the one input needed to reach a target result. Example: what interest rate gives an EMI of ₹10,000? |
| Scenario Manager | Saves sets of input values (best, worst, expected) and compares them. |
| Data Table | Shows a result for many values of one or two inputs. |
| Solver | Add-in for optimisation with several variables and constraints. |
Pivot Tables
A PivotTable summarises a large table without writing formulas. Insert → PivotTable.
| Area | Purpose |
|---|---|
| Filters | Report-level filter |
| Rows | Items listed down the side |
| Columns | Items listed across the top |
| Values | The numbers calculated: Sum, Count, Average, Max, Min |
- Numbers default to Sum; text defaults to Count.
- A pivot table does not update automatically when the source data changes. Use Refresh (Alt + F5).
- Slicers are button filters. Pivot Chart is a chart linked to the pivot table. Group combines dates into months, quarters and years. A Calculated Field adds a formula-based field.
- Example: Rows = District, Values = Sum of Fee gives total fee per district: Anantnag 6,000, Baramulla 2,500, Jammu 9,000, Srinagar 6,000.
Charts
Insert → Charts. Select the data first. Alt + F1 makes a chart on the same sheet; F11 makes a chart on its own sheet.
| Chart | Best for |
|---|---|
| Column / Bar | Comparing categories. |
| Line | Trends over time. |
| Pie / Doughnut | Parts of a whole. A pie shows only one data series. |
| Scatter (XY) | Relationship between two numeric variables. |
| Area | Totals over time. |
| Histogram | Distribution of data (2016 and later). |
| Combo | Two chart types together (columns and a line). |
| Waterfall | Rising and falling totals. |
| Sparkline | A tiny chart inside one cell. |
Chart elements: title, axis titles, legend, data labels, gridlines, data table. Category axis is the X axis; value axis is the Y axis.
Protecting and printing
- Review → Protect Sheet / Protect Workbook (passwords). Format Cells → Protection → Locked works only after the sheet is protected. Hidden hides formulas.
- Page Layout: Print Area, Print Titles (repeat header rows on each page), Orientation, Margins, Page Break Preview, Fit to one page.
- Macros: record repetitive steps. Saved in .xlsm files, written in VBA (Visual Basic for Applications). Alt + F11 opens the VBA editor and Alt + F8 lists macros.
Keep practising
Notes are only half the work. Test yourself with questions and discuss doubts with other aspirants.
Prepared for exam revision from standard computer textbooks, Microsoft documentation and official sources. Features differ between Excel and Word versions (2016, 2019, 2021, 365) so check which version your exam follows. Found a mistake? Tell us.