Charts, sorting and data tools

1. Chart area — the entire chart object, including titles and legend. Selecting it moves or resizes the whole chart.

2. Plot area — the rectangle inside which the data is actually drawn, bounded by the axes.

3. Data series — one complete set of related values, usually a single row or column of the worksheet. A chart may hold several series, each in its own colour.

4. Data point — one individual value within a series: one column, one slice, one marker on a line.

5. Category (X) axis — the horizontal axis, carrying the labels of the things being compared (subjects, months, regions).

6. Value (Y) axis — the vertical axis, carrying the numeric scale against which the data points are measured.

Others worth naming: the legend, which says which colour belongs to which series; the chart title and axis titles, which state what is shown and in what units; data labels, printing the value on each point; gridlines, helping the eye read a value off the scale; and a trendline, a fitted line showing the overall direction.

On single-series charts, the legend should be removed — it merely repeats the title.

The two are often used together: filter to the rows of interest, then sort those into a useful order.

Why the whole table must be selected before sorting.

Sorting rearranges rows. For the data to stay meaningful, every column must be rearranged together, so that each row's values remain attached to the same record.

If only the Marks column is selected and sorted, Excel reorders only that column. The names, roll numbers and fees stay exactly where they were, so the highest mark ends up beside whichever name happened to be in the first row. Every student now has someone else's mark.

The damage is undetectable afterwards: the sheet looks perfectly normal, every name is present and every mark is present, and nothing indicates that they have been paired wrongly. There is no way to reconstruct the original pairing from the file.

In practice: click one cell inside the list and let Excel detect the whole block, or select the entire table including headings. Excel usually offers to expand the selection — that warning must be read rather than dismissed. Before a large sort, copy the sheet.

1. Column chart. Vertical bars whose heights represent values, for comparing separate categories.
Suited to: marks by subject, sales by branch, population by district — anything where distinct items are compared.

2. Line chart. Points joined by a line, showing how a value changes along a continuous scale.
Suited to: monthly sales over a year, daily temperature, share price — the category axis must be something genuinely ordered, normally time.

3. Pie chart. A circle divided into slices, each proportional to its share of the total.
Suited to: the composition of one whole — how a monthly budget divides between rent, food, travel and savings; the share of votes each party received.

4. XY (scatter) chart. Points plotted against two numeric axes, showing whether two quantities are related.
Suited to: hours studied against marks obtained; height against weight; advertising spend against sales.

5. Stacked column chart. Columns divided into segments, showing both the total for each category and its composition.
Suited to: total marks per student split by subject; monthly revenue split by product line.

Also: bar charts (a column chart turned horizontal, for long category names); area charts; combo charts (columns and a line together, for two different scales); doughnut and radar.

Why a pie chart is wrong for marks in five subjects.

A pie chart makes one claim about the data: these values are parts of a single whole, and their sum is meaningful. That claim is false here.

1. The total means nothing. Marks in Mathematics, English and Computer Science do not combine into any real quantity. A slice reading "Mathematics 28%" invites the reader to think mathematics is 28% of something — but 28% of an arbitrary sum is not a fact about the student.

2. The question being asked is comparison, not composition. A reader wants to know which subject is strongest and by how much. Judging that from the angles of slices is much harder than from the lengths of columns — an established finding about how people read charts, and the reason a column chart is right.

3. A pie hides the scale entirely. Marks of $80,70,60,50,40$ and marks of $40,35,30,25,20$ produce identical pie charts, because the proportions are the same. A pie chart cannot distinguish an excellent student from a failing one — it discards exactly the information that matters.

The correct choice is a column chart, with subjects on the category axis, a value axis running from $0$ to the maximum mark, and data labels. A pie chart would only be right if the question were genuinely about composition — for instance how a student's 168 hours in a week divide between classes, study, sleep and travel.

Conditional formatting applies formatting to a cell only when a stated condition is true, and removes it when the value changes. It makes a sheet indicate its own exceptions, so a reader is not obliged to check every number.

It differs from ordinary formatting in being dynamic: colouring a failing mark red by hand is wrong the moment the mark is corrected, whereas a conditional rule re-evaluates itself.

1. Highlight Cells Rules. Formats cells meeting a comparison — greater than, less than, between, equal to, text containing, a date occurring, or duplicate values.
Use: colour every mark below $40$ red, so failures are visible at a glance in a list of 200 students. Or highlight duplicate roll numbers, which finds data-entry errors that no formula would reveal.

2. Data Bars. Draws a bar inside each cell, its length proportional to the value — a small chart within the column.
Use: a sales column where relative sizes must be seen while the exact figures remain readable. It gives the comparative power of a chart without leaving the table.

3. Colour Scales. Applies a two- or three-colour gradient across a range, so each cell's colour indicates its position between the lowest and highest values — a heat map.
Use: a grid of monthly sales by region, where the eye immediately finds the strong and weak combinations without reading a single number.

4. Icon Sets. Places a small symbol in each cell — traffic lights, arrows, flags — according to which band the value falls in.
Use: a project tracker where green, amber and red show progress against target; or up, level and down arrows against last month's figures.

5. Formula-based rules deserve mention as the most powerful form. Choosing Use a formula to determine which cells to format allows any condition, including one that formats a whole row based on one column:

=$D2<40

applied to the range A2:E101 colours the entire row of any student failing. The mixed reference $D2 is essential — the column is locked so every cell in the row consults column D, while the row is free so each row tests its own value.

Managing rules. Conditional Formatting → Manage Rules lists them in priority order, since several may apply to one cell, and Stop If True prevents lower rules being applied once one has matched.

A pivot table is an interactive summary that cross-tabulates a large list, grouping records and aggregating a numeric field — without writing a single formula and without altering the source data.

Insert → PivotTable, then drag field names into four areas.

An example. Take $5,000$ sales records with the fields Date, Region, Salesperson, Product, Quantity, Amount.

Drag Region to Rows, Month to Columns and Amount to Values as Sum. The result is a complete region-by-month sales table:

Now drag Product into Rows in place of Region, and the same 5,000 records are re-summarised by product instantly. Add Salesperson beneath Region and the table nests one inside the other. That rearrangement is the pivot.

Why it beats SUMIF formulas.

1. No formulas to write, and none to get wrong. The table above would need $3\times 3$ SUMIFS formulas plus row and column totals — around sixteen formulas, each with absolute references to get right. The pivot table needs three drags.

2. It discovers the categories itself. SUMIFS requires you to know in advance that the regions are North, South and East, and to have typed those labels as row headings. A pivot table reads the distinct values out of the data. If a fourth region appears next month, the pivot table shows it on refresh; the SUMIFS grid silently omits it, and its sales vanish from a total that still looks complete.

3. Rearranging costs nothing. Summarising by product instead of by region means one drag. With formulas it means rebuilding the entire grid.

4. It scales. The same three drags handle 5,000 rows or 500,000. Sixteen formulas over half a million rows recalculate slowly, and a hundred such grids make a workbook unusable.

5. Extras come free: row and column totals, percentage of total, grouping dates into months, quarters and years, expanding a cell to see the underlying records (drill down), slicers, and pivot charts.

The one caution. A pivot table does not update itself when the source data changes — it must be refreshed (right-click → Refresh). SUMIF formulas recalculate at once. This is the single genuine advantage of formulas, and the reason a live dashboard cell is often better as a formula while analysis is better as a pivot table.

When formulas are still right: a handful of specific figures embedded in a report, values that must recalculate instantly, or a result that feeds further calculations. When a pivot table is right: exploring a large list, or any summary whose shape may need to change.