Practical Excel skills: from a table to a reliable report

When someone tells me they know Excel, I would like to see one ordinary report. Where did the data come from? What does a row represent? What happens when another file arrives tomorrow? Those questions tell me more than a list of fifty functions on a CV. I look for evidence that you can produce a result, explain it and repeat the work reliably.

Excel can help with organising records, summarising data and checking ideas. Opening a workbook does not guarantee any of those outcomes. Here I propose a small project that takes you from a single calculation to a report another person can use. All orders and amounts below are synthetic. They describe a learning exercise, with no claim about a real company’s performance or a guaranteed saving in working hours.

Start with the reader’s question

Imagine someone responsible for fulfilling orders. They want to know the value of open orders in each region. You could build fifteen charts, but the first deliverable is an answer to that question. I would write it down before choosing colours or arranging a dashboard. That gives every field a purpose and makes it easier to decide which attractive features can wait until a later version.

Define open carefully. Does a partially shipped order belong in that group? Are rejected orders excluded? Does the amount include tax? These decisions can change the result more than an elegant formula. For this exercise I use two statuses, open and closed, one currency and one amount for the entire order. Treat those as explicit simplifications. A real process may need additional states and a different definition of outstanding value.

The reader also needs to know when the information was current. A report based on Monday’s export does not automatically describe Tuesday’s position. I would include the source date and the covered period. A file named latest is not enough. A month later, neither you nor the reader should have to reconstruct what latest meant by comparing attachment dates across several email threads.

Give every row one meaning

Create a register with order ID, date, region, status and amount. Each row represents one order, so its amount appears once. If an export contains order lines, you need to change the stated level of detail or aggregate the lines first. Repeating an entire order’s value on every line can multiply the reported amount while leaving the spreadsheet looking perfectly tidy. Understanding the row is part of understanding the calculation.

I would keep subtotal rows, regional headings and merged cells outside this source register. The summary belongs elsewhere. Store dates as dates and amounts as numbers with a consistent meaning. An identifier containing a leading zero may need to remain text. Decide that before importing it. Formatting a damaged identifier afterwards can make the display look plausible without restoring the original code or proving that a lookup will work.

Microsoft’s overview of Excel tables describes features including headers, filtering and calculated columns. I would turn the register into a table and give it a descriptive name, such as Orders. The visual style is secondary. The useful skill is recognising the source boundary and referring to that source consistently when you create calculations and summaries elsewhere in the workbook.

Check a dataset you can understand

Use four orders to begin with. The first belongs to North, is open and has an amount of 100. The second also belongs to North, is closed and has an amount of 200. The third belongs to South, is open and has an amount of 50. The fourth belongs to South, is open and has an amount of 150. Give all four different identifiers. You can inspect this entire dataset without scrolling.

North’s open value is 100. South’s open value is 50 plus 150, giving 200. The combined open value is therefore 300, while all four orders together total 500. Keep these figures as a reference. If the report shows an open value of 500, check whether the status filter was applied. If it shows 400, investigate the actual discrepancy. Typing the expected number over a formula would conceal the problem you need to understand.

Now add a fifth order with a new identifier: North, open, 25. North’s open value should become 125 and the combined open value 325. That small change reveals whether the source includes additional records and whether the summary has been refreshed. Practical Excel skill includes how a workbook behaves after its inputs change. A screenshot of the finished report cannot show you whether that part works.

Learn to read references

When a formula refers to a cell, you need to understand what happens when the formula is copied. A relative reference can move with the formula; a fixed reference can keep pointing to a parameter. I would practise this with a short table and one clearly labelled rate. Inspect each dependency. You should be able to explain why the next row uses the intended value, without relying on the result looking approximately reasonable.

Tables also support structured references. A field name can explain the source more clearly than a bare column letter. The point here is to trace the calculation rather than collect syntax variations for every language setting. Identify which field supplies each value, which records are included and where the parameters live. Someone maintaining your workbook later should be able to follow the same route.

I would avoid embedding the same rate separately inside many formulas. When it changes, every copy becomes another place to inspect. One labelled parameter makes the dependency easier to see. Some rates vary by product or effective date, however. Those need a matching rule and possibly a history table. A single highlighted cell would oversimplify that situation. Choose the model according to the data’s meaning, then choose a convenient way to calculate it.

Summarise with a PivotTable

For the example, place region in rows, amount in values and status in a filter. Select open. Check whether the values area sums the amounts or counts them. Numbers imported as text may reveal themselves at this stage. I would correct the source type and then revisit the summary. Changing the displayed number format is not a substitute for establishing that the underlying values are numeric.

PivotTables let you rearrange summaries, and their refresh behaviour matters after source changes. Compare the initial result with North 100 and South 200. After adding the fifth order, compare it with North 125 and South 200. Do not assume the summary is current simply because the source worksheet already shows the additional record. Include refresh as a visible step in the reporting process.

Next, replace the amount summary with a count of orders. Initially, North has one open order and South has two. You are now answering a different question. A region with a higher value does not necessarily have more work to process, and more orders do not establish greater difficulty. Before presenting a ranking, ask what action the reader intends to take. That conversation is part of building a useful report.

Separate missing values from zero

An open order without an amount needs an explanation. Zero can be a valid amount, but it may also indicate an order awaiting a quotation. Replacing every missing value with zero makes the report look complete while leaving its actual value uncertain. I would show the number of unpriced orders separately and ask what the empty field means. That gives the reader a reason to investigate instead of silently accepting an understated total.

Region labels deserve similar attention. South, a spelling variation and a value with an extra space might represent the same category. Software cannot resolve every naming disagreement on your behalf. Establish a permitted list and a correction rule. Keep access to the original input so you can distinguish what arrived from what changed during preparation. Otherwise, a later discussion about a category can become an argument over a file that no longer exists.

I would not delete records just because two amounts match. Different orders can have identical values. Compare the identifiers and the level of detail first. If an order number repeats with different statuses, you may be looking at a change history. You then need a rule for selecting its current state. A duplicate-removal command cannot infer that business process from the numbers alone, however convenient its button might appear.

Use Power Query for repeatable imports

If a similarly structured file arrives every week, consider saving the preparation steps. Power Query supports importing and transforming data, including selecting fields and setting types. Available sources and capabilities depend on the environment. Check your Excel version and the intended connection before planning a process around it. A feature demonstrated on another platform may not be available in exactly the same form on your machine.

Start with one file. Import it, select the necessary fields and inspect the output. Move to a folder of exports once you understand that first transformation. Establish whether each file adds a new period or contains another full snapshot. Combining five complete exports can repeat the same orders five times. A technically successful import can therefore create an impressive error unless you first define how the files relate to each other.

Saved steps still need checks. A source owner can rename a column, change a separator or supply dates in another format. After refresh, inspect row counts, conversion errors and date coverage. Decide what happens when these checks fail: stop the report, flag the problem or present partial data with an explanation. Automation repeats the rules you gave it, including poorly chosen ones. Reliability comes from understanding those rules and recognising when their assumptions no longer hold.

Make the workbook usable by someone else

The next reader did not watch you build the file. I would leave a short instruction explaining which input to provide, what to refresh and how to recognise successful completion. State the amount’s unit, the status filter and the source date. A few clear sentences can replace a surprising amount of guesswork. Worksheet names should also help the reader find the input and the result without opening every tab.

Separate inputs, calculations and output. Mark editable parameters explicitly instead of relying entirely on colour. A monochrome printout or a different way of accessing the file can hide colour cues. Put the control total and missing-value information near the result. A message saying complete is useful only when everyone understands what completed and which records were covered. A report should communicate its boundaries as well as its headline number.

Check which connections and files the workbook needs outside itself. A path available on your computer may be inaccessible to a colleague. Try handing over the workbook in its intended environment to someone who will actually use it. You do not need to build an elaborate reporting platform for this exercise. You need to establish whether another person can follow the process without calling you to explain each step.

Match a price list using an explicit key

Suppose orders contain product codes and prices come from another table. Before choosing a lookup function, establish whether each code has exactly one applicable price. Two entries may describe different periods or sales channels. A product code alone is then insufficient: the matching rule also needs a date, variant or other pricing condition.

For practice, create three products but only two prices. Leave one code unmatched deliberately. Explain what happens to that order’s value. Replacing an absent price with zero would imply something the source has not established: that the product is free. A separate count of unpriced orders preserves the issue for the report’s reader.

Then add another price for one code. Can you identify the ambiguity and ask which entry applies? Finding a value and defining the correct business rule are separate activities. I would treat recognising an unresolved match as a useful outcome. Silently accepting the first plausible price makes the final number harder to defend.

Compare periods with the same meaning

A weekly comparison needs a definition. Open order value at Friday’s close describes a position, while the value of new orders received during the week describes an inflow. Both use the same unit, but they answer different questions. Placing two columns together does not make their underlying definitions comparable.

An export containing current statuses may be insufficient to reconstruct last week’s position. An order closed today may have been open then. You need history or an appropriate dated snapshot. I would state that limitation rather than manufacture a historical result from a field that has since been overwritten.

Keep two small, explicitly dated snapshots for practice. Follow an order whose status changed between them. Explain why open value decreased even though no order was deleted. Choose the presentation afterwards. Similar reasoning applies to stock levels, staffing and unresolved tickets: a familiar reporting tool does not remove the need to define each measure separately.

Keep the interpretation within the evidence

South’s higher open value does not establish that its team performs worse. The regions may differ in size, delivery schedules or order types. Separate the observed amount from a proposed explanation. Our four records establish a small synthetic total; a claim about performance would require more context.

A useful note could say that South has an open value of 200 and the dataset contains no due dates, so the report does not assess lateness. Add a next action, such as obtaining those dates or reviewing the largest open order. The reader gets both a result and a practical boundary around its interpretation, without depending on the author to repeat the same explanation at every meeting.

Choose the next lesson from the current problem

If cell addresses confuse you, practise references. If a price lookup changes the number of records, revisit keys and matching. If you repeatedly combine exports by hand, study the import process. Each lesson then has a specific purpose. Watching another course can be enjoyable, but there also needs to be a point where you make a decision independently and find out whether you understood the material.

Useful follow-ups include my Excel learning plan and VLOOKUP price-list guide. Choose the topic that explains your present difficulty. You do not need to know every function before finishing your first report. It is often more instructive to complete one small process, recognise its limitations and improve the part that currently causes the most confusion.

Finally, explain the workbook without sharing your screen. What question does it answer? What inputs does it require? How do you check the result, and what does it leave unresolved? Then open the file, add an order and show the change. That provides something concrete to discuss when describing your skills. I find a small, understandable demonstration much more useful than an extremely long list of familiar keyboard shortcuts.