Learning Excel, from the First Cell to a Finished Report

Most trouble with advanced Excel traces back to how the data was typed on day one. The layout that formulas, filters and PivotTables read smoothly is called tabular. The top row holds one header per column, every row beneath it is a complete record (a single sale, a single attendance entry, a single test score), and every column holds one kind of value. Merged cells, two-row headers, subtotals tucked between records and blank rows separating the months all look tidy on screen, yet each of them breaks the range Excel is trying to read.

A useful first exercise is rebuilding a sheet that was designed for printing into one designed for calculating. Take a workbook with a separate sheet for every month and stack the months into one long table, with a Month column to tell them apart. Split an entry like "5 kg" into a number column holding 5 and a unit column holding kg. Then click any cell inside the data and press Ctrl+T. The resulting Excel Table gets a name, filter buttons on every header, and formulas that extend to new rows on their own.

A worksheet offers 1,048,576 rows and 16,384 columns, so space is never a reason to scatter data across sheets. A good sign that tabular thinking has stuck: from any header, Ctrl+Down Arrow lands exactly on the last record, with no blank row stopping it halfway.

A formula that works in the first row and goes wrong by the tenth is nearly always a cell reference problem. A relative reference such as B2 shifts when the formula is copied down or across. An absolute reference such as $B$2 stays fixed. A mixed reference locks only one part: $B2 locks the column and B$2 locks the row. With the cursor on a cell address, pressing F4 cycles through all four forms in turn, from $B$2 to B$2, then $B2, then back to B2.

The classic drill for mixed references is a 10 by 10 multiplication grid. Type 1 to 10 down column A and across row 1, write a single formula in B2, and copy it to fill the whole grid. The formula that survives the copy is =$A2*B$1. A learner who can explain why each dollar sign sits where it does has understood references at the level that matters. A second drill closer to office work: keep a commission rate or an exchange rate in one cell and have the whole results column refer to it with an absolute address.

Regional settings matter here too. On many computers set to European or Indonesian regional formats, formula arguments are separated by semicolons, so an English example like =IF(A2>70,"Pass","Retry") has to be typed as =IF(A2>70;"Pass";"Retry"). Spotting this early saves a lot of confusion when copying examples from the web.

Most office recaps ask the same question in different clothes: total sales of this product in that region for a given month, how many times staff arrived late, the average transaction per branch. These three conditional functions handle all of them with matching argument patterns, so learning one goes most of the way to learning all three. SUMIFS opens with the range to add up, followed by pairs of criteria range and criterion, up to 127 pairs.

The usual snag is argument order. The older SUMIF puts the range to add up last, while SUMIFS puts it first. Learners switching between them mix up the order and get a wrong total with no error message at all. A second snag sits in criteria that use operators. "Greater than or equal to 100" is written as the text ">=100", and when the number lives in another cell it becomes ">="&D1. Asterisks and question marks work as wildcards, so "Jakarta*" catches every branch whose name begins with Jakarta.

A practical drill is checking SUMIFS against a manual filter. Filter the data by the same conditions, read the sum shown in the status bar at the bottom right, and compare it with the formula. Once the two agree for three different combinations of conditions, the formula has earned its place in a report.

Error codes are clues, and each one points to a different cause. #N/A means the value being looked up was not found. #VALUE! means the formula received the wrong kind of input, such as text where a number belongs. #REF! appears when a referenced cell or column has been deleted. #DIV/0! means the divisor is zero or empty, which often happens with an average for a month that has no data yet. #NAME? nearly always means a misspelt function name or a missing pair of quotation marks around text. In newer versions, #SPILL! shows up when a formula returning several values has no empty cells to spill into.

For long formulas, the Formulas tab offers Evaluate Formula, which steps through the calculation one stage at a time, and Trace Precedents, which draws arrows back to the source cells. In the formula bar, highlighting part of a formula and pressing F9 shows the result of that part alone; press Esc afterwards so the original formula stays intact. Ctrl+` (the key left of the 1) displays every formula on the sheet at once for a quick review.

IFERROR belongs at the very end, after the cause of an error is understood. Wrapping a formula in IFERROR too early hides genuine mistakes behind a neat-looking dash. The safe order is to prove the logic on a small test, confirm that any remaining errors are expected (a key that has not been registered yet, for example), and then wrap the outermost layer with a clear label such as "no data yet".

A workbook can calculate correctly and still confuse whoever opens it. The last part of learning Excel is about handing the work over. Freeze Panes on the View tab keeps the header row in sight while the table scrolls. Number formats with thousands separators and a consistent count of decimals let a column be read at a glance. Conditional Formatting highlights cells that need attention, such as stock below its limit, and keeps doing so as the data changes, which manual colouring never will.

For reports that get printed or sent as PDFs, Page Layout provides Print Area, Print Titles so the header row repeats on every page, and the Fit to scaling option so no column slips onto a second page. Print Preview gets a look before anything is sent. A file name that states the content and period, such as Sales-Recap-2026-09, makes the file easy to find months later. Workbooks stored in OneDrive or SharePoint can be edited by several people at once, with version history kept automatically.

Speed comes from shortcuts used over and over: Ctrl+T for a table, Ctrl+Shift+L for filters, Alt+= for a quick sum, Ctrl plus an arrow key to jump to the edge of the data, and Ctrl+; for today's date. Adding a few shortcuts each week sticks better than memorising one long list. A monthly report that is already tidy can be saved as a template, so the next period only needs fresh data.