Session 5: Practical Excel

Sorting reorders rows. Data → Sort lets you sort by one column, or add levels — sort by Category, then within each category by Amount descending. The rule that prevents disaster: always sort the whole table, never a single column. If Excel prompts 'Expand the selection', choose it. Sorting one column alone shuffles that column against the others and destroys the relationship between a name and its amount, which is a data-integrity failure you may not notice for weeks.

Filtering hides rows that do not match a condition, leaving the data intact. Select the table, Data → Filter, and dropdown arrows appear on the headers. Tick boxes for exact values, or use Text Filters / Number Filters for conditions like 'greater than 50000' or 'contains church'. The status bar then shows the count and sum of visible cells only — which is how you answer 'what did we spend on transport this month' in three clicks. To clear, use Data → Clear; the data was never deleted, only hidden.

Select any data range and press Ctrl+T to convert it to an Excel Table. You gain filter buttons, banded rows, automatic expansion, a repeating header on every printed page, and structured references. It also means new rows inherit the column's format and formulas automatically. For any list you will keep adding to, this is the first thing to do.

Percentages are where most spreadsheet errors live. A percentage is a ratio, and Excel stores ratios as decimals: 15% is 0.15. To find what share one item is of a total, divide: `=D2/$D$15`, then format the result as Percentage. To add VAT of 7.5% to an amount, multiply by 1.075 (`=D2*1.075`) — not by 0.075, which gives you only the VAT. To remove VAT from a VAT-inclusive figure, divide by 1.075. Learn these three patterns cold; they cover most Nigerian financial spreadsheet work, and 7.5% VAT appears in almost all of it.

Then the everyday calculations: discount `=Price*(1-DiscountRate)`; margin `=(Selling-Cost)/Selling`; growth between two periods `=(New-Old)/Old`; running total `=SUM($D$2:D2)` filled down, where the mixed reference locks the start and lets the end move.

This is the concept that separates people who use Excel from people who fight it. When you copy a formula, Excel adjusts its references relative to how far the formula moved. `=B2*C2` filled down one row becomes `=B3*C3` — usually exactly what you want. But if that formula refers to a fixed cell, like a VAT rate in E1, filling it down turns E1 into E2, E3, E4 and your calculation quietly breaks.

The dollar sign locks a reference. `$E$1` never changes no matter where the formula is copied — that is an absolute reference. `$E1` locks the column but lets the row move; `E$1` locks the row but lets the column move — these are mixed references and they are what make cross-tabulation tables work. Press F4 while the cursor is inside a reference to cycle through the four forms. Learn to reach for F4 without thinking: it is the single keystroke that prevents the most expensive category of spreadsheet error, the one where the numbers look plausible and are wrong.

Charts exist to answer a question faster than reading numbers can. Choose by the question, not by what looks attractive. Bar and column charts compare quantities across categories — spending by category, sales by branch. Use columns for few categories with short labels and horizontal bars when labels are long. Pie charts show parts of a whole and are only honest with five slices or fewer, clearly different values, and a total that genuinely sums to 100% — otherwise use a bar chart. Line charts show change over time and are the correct choice for anything with dates on the horizontal axis; a pie chart of monthly figures is meaningless because months are not parts of a whole.

Build them from properly structured data: categories in one column, values in the adjacent column, headers in row 1. Select both, Insert → the chart type. Then do the three things beginners skip: give the chart a title that states the finding rather than the topic ('Transport was 34% of September spending', not 'Spending Chart'), add axis titles with units, and add data labels only where they help. Delete the legend if the categories are already labelled on the axis. Resize by dragging corners, never edges, or you distort the proportions.

Spreadsheets print badly by default because they are wide. Fix it deliberately: Page Layout → Orientation → Landscape for wide tables; Scale → 'Fit All Columns on One Page' so nothing spills onto a second sheet; Margins → Narrow if needed; and Page Layout → Print Area → Set Print Area to print only the table rather than the empty grid. Print Titles (Page Layout → Print Titles → Rows to repeat at top) makes your header row appear on every printed page, which is essential for multi-page reports.

Before printing anything, Ctrl+P and read the preview page by page. Check that no column is split across pages, that totals appear on the last page, and that gridlines and headers are on if you want them (Page Layout → Sheet Options → Print → tick Gridlines and Row & Column Headings). Then export a PDF and send that, because a PDF preserves the layout you just fought for.