4  Excel Essentials for Accounting Analytics

Learning Objectives

After completing this chapter, you will be able to:

1. Explain why the structure of a workbook is a control over the accuracy of an analysis, drawing on research into spreadsheet errors.
2. Build an analysis workbook by importing Excel Tables with Power Query, and use structured references and calculated columns in formulas that are easier to read and maintain than cell addresses.
3. Distinguish between relative, absolute, and mixed cell references, apply each correctly, and use named cells to hold the parameters of an analysis.
4. Apply multi-level sorts, filters, slicers, and the SUBTOTAL function to explore transaction-level data and summarize only the records in view.
5. Use conditional formatting to surface patterns and exceptions, and data validation to protect the cells that drive an analysis.
6. Organize and document an analytical workbook so that a reviewer can follow every number back to its source.

Chapter slides View slides Download PowerPoint

Opening Scenario

You are the staff accountant from Part I, and the controller has read your data readiness memo. She accepts its conclusion, with the limits it states. Measured on the posted Furniture revenue and cost accounts, gross margin fell from the third quarter of the 2026 fiscal year to the fourth. The invoice and shipment totals of those quarters agree with their ledger postings, and the analysis must exclude the year-end closing entry. Now she wants the diagnostic answer. The later chapters of Part II measure the margin on the invoice lines at standard cost, a related but different measure, so keep your memo’s definitions and limits beside the new work.

Before you begin, she has one request. The finance team, and possibly the auditors, will review the analysis, so she wants it built in a workbook that anyone on the team can refresh, that never alters the company’s data, and that a reviewer can follow from any number back to its source. “Build it as if you were going to hand it to someone else,” she says, “because you are.” In Part I you filtered worksheets and wrote test formulas in your working copy, CharlesRiver_Work.xlsx, but those were one-off checks. This chapter teaches you to build a sturdier foundation: to bring the data you need into a workbook of your own, write formulas that stay correct as the data changes, make patterns and exceptions visible, and document the work so that it can be reviewed.

4.1 Why Workbook Structure Matters

The first three chapters of this book used Excel to explore and test the Charles River data. You filtered the Shipment worksheet for blank tracking numbers, wrote MINIFS formulas to compare invoice and shipment dates, built PivotTables of the general ledger, and traced a sale from its order to its ledger postings. Each of those tasks answered a single question and could be repeated from scratch if needed. An analysis that supports a management decision or an audit conclusion is different. It is built once, extended many times, handed to reviewers, and refreshed when new data arrives, and every one of those steps is an opportunity for an error to enter.

Research on spreadsheet errors shows why this matters. In an early synthesis of the evidence, Panko (1998) concluded that errors occur in a few percent of all spreadsheet cells, which means that for a large spreadsheet the question is not whether an error exists but how many there are. He also reported that, according to surveys of spreadsheet developers, spreadsheet creation is informal and few organizations have comprehensive policies for it.

Later field audits refined these estimates. When Powell et al. (2009a) applied a structured auditing protocol to 50 operational spreadsheets, they found errors in 0.9 to 1.8 percent of formula cells, depending on how an error was defined, and the error rate differed widely from one spreadsheet to another. In a companion study, Powell et al. (2009b) asked the developers of 25 operational spreadsheets to confirm the potential errors that the auditors had found. Many of the confirmed errors had no effect on the results, but correcting the largest changed an output of its spreadsheet by more than $100 million. The study measured that effect on a calculated result, not a financial loss. Executives report the same pattern. In interviews with 45 managers and analysts, Caulkins et al. (2007) found that almost all considered spreadsheet errors common, that most could describe errors that had led to losses or poor decisions, and that quality control was in most cases informal.

For accountants, these findings carry professional weight. Excel remains the tool in which much accounting analysis is done. A survey of small and medium-sized entities by Church et al. (2022) found that they continue to perform their leading accounting tasks in Excel spreadsheets. A spreadsheet that supports a cost analysis, a disclosure, or an audit procedure must be accurate, and it must also be reviewable, because a reviewer who cannot follow the logic cannot detect the errors in it. Which design practices prevent which errors was still an open question when Powell et al. (2008) reviewed the literature. They observed that relatively little was known about what types of errors occur, how they are created, and how they can be avoided. There is evidence, however, that a structured design approach helps with some kinds of error. In two field experiments, Janvrin and Morrison (2000) found that participants who followed a structured design approach made significantly fewer errors in the links between different parts of a workbook.

The practices in this chapter should be read in that light. Excel Tables, structured references, named parameters, validation rules, and documentation do not guarantee a correct analysis, and they do not replace careful review. What they do is remove common opportunities for error, such as a formula that stops one row short of the data, and make the remaining logic visible to the person who checks it. They are controls in the accounting sense of the word: procedures that reduce the risk of a misstatement and leave evidence that the work was done properly.

TipIn Practice

The field audits by Powell et al. (2009b) found that errors were not spread evenly. The audit confirmed no errors in nine of the 25 spreadsheets, and many of the confirmed errors had no effect on the results, yet correcting the largest one changed a result by more than $100 million. The practical lesson for a reviewer is that a spreadsheet’s appearance says little about its accuracy, and that a clean audit shows only that its procedures found no error. The workbooks that can be reviewed efficiently are the ones whose structure lets the reviewer see where each number comes from.

4.2 Excel Tables and Structured References

An Excel Table is a range of data that Excel manages as a single named object. When data is stored in a Table, Excel knows where the data begins and ends, which row holds the column headers, and what each column is called. It adds filter buttons to the header row, applies banded formatting, and extends the Table automatically when you add rows or columns. Most importantly for analysis, it lets formulas refer to the data by name rather than by cell address.

You have already worked with Excel Tables, even if you did not know it. Every worksheet in CharlesRiver.xlsx stores its data as a Table, which is why the worksheets you filtered in Part I had banded rows and filter buttons in the header row. Each Table has a name made of a number and the name of the database table it holds, such as T17_SalesInvoiceLine or T44_Item. You can see the name by clicking any cell in the data and looking at the Table Name box at the left end of the Table Design tab (the Table tab in Excel for Mac). Because the filter buttons are already part of each Table, you do not need to turn them on. In fact, the Data > Filter command toggles them, so clicking it on one of these worksheets turns the filter buttons off.

4.2.1 Tables Compared with Plain Ranges

To see why Tables matter, consider the alternative. A plain range is simply a block of cells. Excel does not know that the block is a dataset, so a formula that totals it must name the exact cells, such as =SUM(K2:K27934). If new rows are added below the block, the formula does not include them, and nothing warns you that the total is now incomplete. A range error of this kind produces a number that looks reasonable, which is exactly what makes it dangerous. Table 4.1 summarizes the practical differences.

Table 4.1: Excel Tables compared with plain data ranges

Feature	Plain range	Excel Table
Boundaries	Formulas name fixed cells, such as K2:K27934, that must be extended by hand when rows are added	The Table knows its own extent, and new rows typed directly below it become part of it
Formula references	Cell addresses, such as K2:K27934	Structured references, such as SalesInvoiceLine[LineTotal]
Column formulas	Copied down by hand, and easy to leave incomplete	A formula entered in an empty column fills every row as a calculated column, by default
Filter buttons and banding	Added separately	Built into the header row
Slicers	Not available	Available from the Table Design tab
Charts and PivotTables	Their source ranges must be extended by hand	They follow the Table as it grows, although a PivotTable still needs to be refreshed
Name	None, unless you define one	Every Table has a name, which appears in the Name Manager and in formulas

When data arrives as a plain range, for example in a file exported from another system, you can convert it into a Table in one step. Click any cell in the data and press Ctrl+T (Cmd+T on a Mac). Excel shows the range it has detected and asks whether the first row contains headers. Confirm, and then give the Table a descriptive name in the Table Name box, because a default name such as Table1 tells a reviewer nothing. In this chapter you will create Tables a different way, by loading them with Power Query, which names each Table for you.

4.2.2 Structured References

A structured reference refers to Table data by the Table’s name and the column’s name instead of by cell address. The formula =SUM(SalesInvoiceLine[LineTotal]) totals the LineTotal column of the Table named SalesInvoiceLine, however many rows the Table holds. Inside a Table, the reference [@LineTotal] means the LineTotal value in the current row, so a formula such as =[@Quantity]*[@BaseListPrice] multiplies two columns of the same row. You rarely need to type these references. When you click a Table cell or select a Table column while writing a formula, Excel inserts the structured reference for you.

Structured references make formulas self-documenting. A reviewer who reads =AVERAGE(SalesInvoiceLine[LineTotal]) knows immediately what is being averaged, whereas =AVERAGE(K2:K27934) sends the reviewer to column K to find out, and then to the bottom of the data to confirm that row 27934 really is the last row. Figure 4.1 shows the same data as a plain range and as a Table, using invoice lines that include the sale you traced in Tutorial 3.2.

Figure 4.1: A Plain Range Compared with an Excel Table.

4.2.3 Calculated Columns

When you type a formula into an empty column of a Table, Excel by default copies it to every row of that column, creating a calculated column. If rows are added later, the formula extends to them as well. Calculated columns are the natural place for row-level measures such as an extended amount or a discount, because one formula serves the whole column. In a plain range, the same column takes a formula typed in the first row and copied down by hand. Every row then holds its own copy, and nothing ensures that rows added later get one, as the top half of Figure 4.1 shows. A calculated column makes one formula the default rather than a guarantee, because a value or a different formula can still be typed into a single row. When that happens, Excel’s error checking, which is on unless someone turns it off, marks the cell with a small triangle in its corner, a useful warning when you review someone else’s workbook.

WarningWatch out

By default, a Table grows when you type next to it. A value or formula typed in the row directly below a Table becomes a new row of the Table, and one typed in the column directly to its right becomes a new column. That behavior is convenient when you mean to extend the data, but a summary formula typed beside a Table ends up inside it, where it may be copied to thousands of rows or refer to itself. Keep summaries, parameters, and notes on separate worksheets, and leave the data worksheets for the Tables alone.

4.3 Bringing Data into the Workbook with Power Query

The simplest way to start an analysis is to open the company workbook, or a copy of it, and work there, as you did in Part I. For a lasting analysis that approach has three drawbacks. Every change made in the company workbook alters the company’s data, the workbook is far larger than the analysis needs, and the steps you took to select and prepare the data are not recorded anywhere. A copy, such as your Part I working copy, removes the first problem but not the others, and it adds a new one, because the copy no longer updates when the source does.

Power Query, which appears in Excel under Data > Get Data, addresses all of these. Power Query is Excel’s tool for connecting to data, shaping it, and loading the result into a workbook. You point it at a source, such as another Excel workbook, a text file, or a database, and choose the data you want. In the Power Query Editor you can then remove columns you do not need, filter out rows, and correct data types, and each action is recorded as a step in the Applied Steps list. When you close the editor, Power Query loads the result into your workbook as an Excel Table. The source is never changed. When the source is updated, Data > Refresh All reads it again and repeats every recorded step, so the Tables in your workbook stay current without any manual copying.

When the source is an Excel workbook, the Power Query Navigator lists everything the workbook contains, each worksheet and each Table, with a different icon for each kind. For CharlesRiver.xlsx, choosing the Table objects, such as T17_SalesInvoiceLine, is the better choice, because a Table already defines its header row and its extent. Once the data has been loaded, the list of applied steps becomes a record of how the data was prepared, which is exactly the kind of evidence a reviewer looks for.

By default, Power Query also gives each column a data type when it reads a workbook, and it records the choice as a step named Changed Type. It decides by looking at only the first 200 rows, so a column whose first rows are unusual can get the wrong type. The Discount column of the invoice lines is an example: the first 200 lines were billed before any promotion, so their Discount is 0, and Power Query may type the column as a whole number, which would round every discount rate to 0. Check the type of every column you will calculate with before you load a query, as Tutorial 4.1 does.

WarningWatch out

Power Query is built into the desktop versions of Excel for Windows that Microsoft supports, and Excel for Microsoft 365 is the recommended one, as What You Need in the Preface explains. On a Mac, the Power Query Editor requires Excel for Microsoft 365, and some commands are arranged differently from the Windows version described in the tutorials. The tutorials also need the desktop application, because the Charles River workbook is large and Excel for the web limits both the size of the workbooks it opens and the features it offers.

NoteConnecting the Dots

In Chapter 2 you learned that a reliable analysis depends on being able to trace every number back to its source. Power Query supports that traceability inside Excel. The query records which workbook and which Table each result came from, and its applied steps record every filter and change made on the way. You will use Power Query again in Chapter 5, when the analysis requires data from several tables to be prepared and combined.

4.4 Guided Tutorial 4.1: Building the Furniture Analysis Workbook with Power Query

Context and objective. In this tutorial, you will create the workbook in which you will analyze the controller’s question. Instead of working in CharlesRiver.xlsx, you will start a new workbook and use Power Query to import the four Tables the analysis needs: the sales invoices, the invoice lines, the items Charles River sells, and the promotion programs. You will filter and trim the data before loading it, add two calculated columns with structured references, and build control totals that check the imported data. The master workbook is never changed, and the new workbook can be refreshed whenever the data is updated.

Prerequisites. You need the desktop version of Microsoft Excel with Power Query (see the Watch out box earlier in this chapter and What You Need in the Preface) and the CharlesRiver.xlsx file provided with this textbook. You do not need to open CharlesRiver.xlsx, because Power Query reads it directly.

Step-by-step instructions.

Step 1. Create the analysis workbook. Open Excel and create a blank workbook. Save it as Charles River Furniture Analysis.xlsx, in the same folder as CharlesRiver.xlsx.

Step 2. Connect to CharlesRiver.xlsx and choose the Tables. Power Query can read the workbook without opening it.

1. Choose Data > Get Data > From File > From Excel Workbook, select CharlesRiver.xlsx, and click Import.
2. After a moment, the Navigator window lists everything the workbook contains. Each worksheet appears with a worksheet icon under its own name, such as SalesInvoiceLine, and each Excel Table appears with a table icon under its Table name, such as T17_SalesInvoiceLine.
3. Select the Select multiple items check box, and then select four Tables: T16_SalesInvoice, T17_SalesInvoiceLine, T44_Item, and T7_PromotionProgram.
4. Click one of them to see a preview of its data.
5. Click Transform Data, not Load, so that you can shape the data before it reaches your workbook.

Figure 4.2 shows the Navigator with the four Tables selected.

Figure 4.2: The Power Query Navigator for CharlesRiver.xlsx.

Step 3. Rename the queries. Each query’s name becomes the name of the Table that Power Query loads into your workbook, so give the queries short names before you load them.

1. In the Queries pane on the left of the Power Query Editor, double-click T16_SalesInvoice and type SalesInvoice.
2. Rename the other three queries SalesInvoiceLine, Item, and PromotionProgram in the same way.

The Queries pane now lists the four queries under their short names.

Step 4. Keep only the items Charles River sells. The Item Table holds every item Charles River buys or sells, but the analysis needs only the items it sells. As you found in Tutorial 2.1, the items with a blank ListPrice are ones Charles River buys, such as raw materials, packaging, capital equipment, and operating services.

1. Select the Item query.
2. Click the filter button on the ListPrice column header.
3. Clear the check box for the blank value, which Power Query shows as (null), and click OK.

The preview now shows only items that have a list price, and a new step, Filtered Rows, appears in the Applied Steps list.

Step 5. Keep only the columns the analysis needs. The analysis uses fewer than half of the Item Table’s columns.

1. With the Item query still selected, choose Home > Choose Columns.
2. Clear Select All Columns, and select only ItemID, ItemCode, ItemName, ItemGroup, ItemType, StandardCost, ListPrice, UnitOfMeasure, SupplyMode, CollectionName, and LifecycleStatus.
3. Click OK.
4. Look at the Applied Steps list on the right.

The Applied Steps list now ends with one step for the filter and one for the columns you kept, and you can click any step to see the data as it was at that point. The status bar at the bottom of the editor shows how many columns and rows remain, as in Figure 4.3.

Figure 4.3: The Item Query in the Power Query Editor.

Step 6. Check the data types. The icon at the left of each column header shows the column’s data type, which Power Query chose in the Changed Type step by looking at only the first 200 rows.

1. Select the SalesInvoice query. If InvoiceDate shows a time as well as a date, such as 12:00:00 AM, click the icon on its header, choose Date, and click Replace current when Power Query asks whether to replace the current conversion.
2. Do the same for DueDate and PaymentDate.
3. Check that FreightAmount shows the decimal number icon, 1.2. If it shows the whole number icon, 123, choose Decimal Number and click Replace current in the same way.
4. Select the SalesInvoiceLine query and check Quantity, BaseListPrice, UnitPrice, Discount, and LineTotal in the same way.

Discount needs the most care. The first 200 invoice lines carry no promotion, so Power Query may have typed Discount as a whole number, which rounds every rate, such as 0.1, to 0. Replace current corrects the type in the Changed Type step, before any value is rounded, whereas Add new step would correct it only after the rates were lost. Keep every row of both queries, because the analysis will compare several periods.

Step 7. Load the queries into the workbook.

1. Choose Home > Close & Load > Close & Load To. In the Import Data dialog, select Table and New worksheet, and click OK. The choice applies to all four queries, so Power Query loads each into a new worksheet as an Excel Table; a plain Close & Load would load several new queries at once to the Data Model instead, which is Excel’s default.
2. Open the Queries & Connections pane, which shows each query with the number of rows it loaded.
3. Rename each new worksheet after the Table it holds: SalesInvoice, SalesInvoiceLine, Item, and PromotionProgram.

The invoice lines outnumber the invoices, because many invoices have more than one line, and the Item Table holds only a few hundred rows.

Step 8. Check the Table name. Click any cell in the SalesInvoiceLine Table and look at the Table Design tab. The Table Name box shows SalesInvoiceLine, the name you gave the query. The Table already has filter buttons and banded rows, just like the worksheets in CharlesRiver.xlsx.

Step 9. Add two calculated columns. ListAmount is what each line would have been billed at the item’s list price, and DiscountAmount is the promotional discount taken on the line.

1. Click the first empty cell to the right of the last header, PricingMethod, type ListAmount, and press Enter. The Table expands to include the new column.

2. In the first data row of ListAmount, type =ROUND( and click the Quantity cell in the same row, type an asterisk, click the BaseListPrice cell, and finish the formula so that it reads =ROUND([@Quantity]*[@BaseListPrice],2).

3. Press Enter. Excel fills the formula down the entire column.

4. Repeat the process for a second column, DiscountAmount, with this formula:

   =ROUND([@Quantity]*[@UnitPrice]*[@Discount],2)

Discount holds a rate, such as 0.10 for 10 percent, and it is zero on lines without a promotion, so DiscountAmount is zero on those lines too. Figure 4.4 shows the Table with both calculated columns.

Figure 4.4: The SalesInvoiceLine Table with Its Calculated Columns.

Step 10. Build control totals to check the import. A control total is a total calculated independently from a set of data, which the data must agree with. Here, the invoice lines must add up to the invoices they belong to.

1. Insert a new worksheet and name it Analysis.
2. In cell A1, type Control totals.
3. In A3 through A8, type the labels Invoice lines, Invoices, Line total, Invoice subtotal, Difference, and Discount taken.
4. In B3 through B8, enter these formulas:
   • B3: =COUNTA(SalesInvoiceLine[SalesInvoiceLineID])
   • B4: =COUNTA(SalesInvoice[SalesInvoiceID])
   • B5: =SUM(SalesInvoiceLine[LineTotal])
   • B6: =SUM(SalesInvoice[SubTotal])
   • B7: =ROUND(B5-B6,2)
   • B8: =SUM(SalesInvoiceLine[DiscountAmount])
5. Add one more row: type List amount in A9 and enter =SUM(SalesInvoiceLine[ListAmount]) in B9.
6. Format the amounts with a thousands separator and two decimals.

The SubTotal on each invoice is the sum of its lines, so the two totals in B5 and B6 come from different Tables but should agree to the cent, and B7 should show zero. The ROUND in B7 removes the tiny differences, far smaller than a cent, that computers produce when they add long columns of decimal numbers. Compare the list amount in B9 with the line total in B5.

Notice what the zero difference compares: two Tables that came through the same import. If one invoice and all its lines had been left out of both, B5 and B6 would fall by the same amount, and B7 would still show zero although a whole invoice was missing; two errors of the same size in opposite directions would cancel in the same way. Agreement inside the workbook cannot reveal an omission like that. Only evidence from outside the import can, such as the number of rows in each source Table, counted in CharlesRiver.xlsx itself, or a test for gaps in the invoice numbers, which Chapter 8 introduces. The control totals therefore show that the invoice lines and the invoices you imported agree with each other. On their own, they do not show that the import is complete.

Step 11. Test the refresh. Power Query reads CharlesRiver.xlsx again and repeats every step, so a refresh should change nothing while the source is unchanged.

1. Choose Data > Refresh All.
2. When it finishes, confirm that the row counts in the Queries & Connections pane and your control totals are unchanged.
3. Confirm that the ListAmount and DiscountAmount columns are still filled.
4. Save the workbook.

The calculated columns survive the refresh because Excel keeps a Table’s calculated columns when a query reloads its rows.

Checkpoint. Your workbook should now contain four data worksheets, each holding a Table named after its query, and an Analysis worksheet. Check that:

• the Item Table holds only the items Charles River sells;
• the line total and the invoice subtotal agree exactly, and the difference is zero;
• the list amount is well above the line total;
• the discount taken is a small fraction of the line total, but not zero.

The zero difference tells you that the imported lines add up to the imported invoices, which they would not if lines had been lost without their invoices; as Step 10 explained, it cannot tell you whether an invoice is missing together with all its lines. The list amount is above the line total because most lines are billed at price-list prices below the item’s list price and some also carry a promotional discount. The discount is not zero because some lines were billed under a promotion, and Tutorial 4.3 finds out which ones.

4.5 Cell References and Named Cells

Structured references handle most formulas that work inside a Table. Many analyses, however, also need formulas outside a Table: summary grids, percentages of a total, and calculations that depend on a parameter such as a threshold or a date. For these, you need to understand how Excel’s cell references behave when a formula is copied.

4.5.1 Relative, Absolute, and Mixed References

A relative reference, such as A2, adjusts when the formula is copied. Copy a formula that refers to A2 one row down and the reference becomes A3; copy it one column to the right and it becomes B2. Relative references are what you want when each row or column of a calculation should use its own data.

An absolute reference, such as $A$2, does not change when the formula is copied, because the dollar signs lock both the column and the row. Absolute references are what you want when every copy of a formula must point to the same cell, such as a total used as the denominator of a percentage, or a single threshold used by every row of a test.

A mixed reference locks only one part. The reference $A2 locks the column but lets the row adjust, and A$2 locks the row but lets the column adjust. Mixed references make it possible to fill a two-way grid with a single formula: the row labels sit in a locked column, the column headers sit in a locked row, and each cell of the grid combines its own row label with its own column header. When you are editing a formula, pressing F4 (Cmd+T on a Mac) cycles the selected reference through the four forms. Table 4.2 summarizes the reference types, and Figure 4.5 shows how each behaves when a formula is copied.

Table 4.2: Cell reference types in Excel

Reference type	Example	When the formula is copied	Typical use
Relative	A2	Row and column both adjust	Row-by-row calculations, such as an extended amount on each line
Absolute	$A$2	Neither the row nor the column changes	A single cell used by every copy, such as a grand total or a threshold
Mixed, column locked	$A2	The column stays A and the row adjusts	Row labels of a two-way grid whose formula is copied across columns
Mixed, row locked	A$2	The row stays 2 and the column adjusts	Column headers of a two-way grid whose formula is copied down rows
Structured	Item[ListPrice]	Unchanged when copied and pasted, but column names shift when the formula is filled across	Formulas that use Table data

Figure 4.5: How Cell References Behave When a Formula Is Copied.

WarningWatch out

Structured references behave differently from cell references when you drag a formula with the fill handle. Filling down leaves them unchanged, but filling across shifts each column name to the next column of the Table, as if the formula were a series. A COUNTIFS formula that refers to SalesInvoiceLine[PricingMethod], filled one column to the right, can end up referring to a different column without any error message. To reproduce a formula across a grid, copy it and paste it instead, which leaves structured references unchanged, or lock the column by writing it as SalesInvoiceLine[[PricingMethod]:[PricingMethod]]. After copying any formula, click a cell in the middle of the result and read the formula bar to confirm that every reference points where you intend.

4.5.2 Named Cells

A named cell gives a cell a descriptive name that formulas can use instead of its address. Select the cell, click the Name Box at the left end of the formula bar, type a name such as LargeLineThreshold, and press Enter. From then on, a formula can refer to it by name, as this one does:

=COUNTIFS(SalesInvoiceLine[LineTotal],">"&LargeLineThreshold)

A name created this way refers to its cell with an absolute reference, so it does not shift when the formula is copied, and the formula states its own logic: it counts the lines above the large-line threshold. Names cannot contain spaces or look like a cell address, which is why a name such as Q4 is not allowed.

Named cells are the natural home for the parameters of an analysis, such as the percentile that defines a large transaction (a percentile is the value below which a given share of the data falls), the start and end dates of the period under review, or a materiality amount (the size of an error that would change the decisions of someone relying on the numbers). Placing every parameter on one worksheet, each with a name and a short description, makes the assumptions of the analysis easy to find and easy to change. When a parameter changes, every formula that uses it updates, and a reviewer can see in one place which values drove the results. The Name Manager, on the Formulas tab, lists every name in the workbook with its value and the cell it refers to. It also lists every Table, because a Table name is a name too.

4.6 Guided Tutorial 4.2: Parameters, Named Cells, and a Summary Grid

Context and objective. In this tutorial, you will give the analysis workbook a parameters worksheet and use named cells, absolute references, and mixed references to summarize the invoice lines. You will define a large-line threshold from the data itself rather than typing an amount, and you will build a grid that shows how the invoice lines are distributed across pricing methods and discount rates. The grid is filled from a single formula, which is the purpose of mixed references.

Prerequisites. You need the Charles River Furniture Analysis workbook from Tutorial 4.1, with the SalesInvoiceLine Table and the Analysis worksheet. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 4.1 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Create the parameters worksheet. The parameters of the analysis will live in one place, each with a name and a description.

1. Insert a new worksheet and name it Parameters.
2. In row 1, type the headers Parameter, Value, and Description in columns A, B, and C.
3. In A3, type Threshold percentile; in B3, type 0.9; and in C3, type Share of lines at or below the threshold.
4. Select B3, click the Name Box at the left end of the formula bar, type ThresholdPercentile, and press Enter.
5. Format B3 with blue font to mark it as an input that a user may change.

The Parameters worksheet now holds one named input. Every formula that needs the percentile will refer to ThresholdPercentile, so changing B3 later changes them all.

Step 2. Derive the large-line threshold from the data. The threshold comes from the line totals themselves rather than from a guess.

1. In A4, type Large-line threshold, and in B4, enter this formula:

   =PERCENTILE.INC(SalesInvoiceLine[LineTotal],ThresholdPercentile)

2. Name B4 LargeLineThreshold in the Name Box, and format it as currency.

3. In C4, type Line total above which a line counts as large.

4. Leave B4 in black font, because it is a formula.

The function returns the amount below which 90 percent of the line totals fall. The threshold is several thousand dollars, well above the median line total, the middle value when the lines are sorted by amount (Chapter 6 returns to it), but far below the largest lines.

Step 3. Count the large lines and measure their share.

1. Go to the Analysis worksheet. In A11, type Large lines, and in B11, enter this formula:

   =COUNTIFS(SalesInvoiceLine[LineTotal],">"&LargeLineThreshold)

2. In A12, type Share of line total, and in B12, enter this formula, which divides the total of the large lines by the line total you calculated in Tutorial 4.1:

   =SUMIFS(SalesInvoiceLine[LineTotal],SalesInvoiceLine[LineTotal],">"&LargeLineThreshold)/$B$5

3. Format B12 as a percentage.

4. Return to the Parameters worksheet, change B3 to 0.95, and watch B4 update.

5. Go back to the Analysis worksheet and read B11 and B12, which have updated as well.

6. Set Parameters!B3 back to 0.9.

About a tenth of the lines are large by this definition, yet together they account for close to a third of the value billed.

Step 4. Lay out the grid.

1. On the Analysis worksheet, type Invoice lines by pricing method and discount rate in A14.
2. In B15 through E15, type the four discount rates that appear on the invoice lines: 0, 0.08, 0.1, and 0.12, formatted as percentages.
3. In A16 through A19, type the four pricing methods exactly as they appear in the PricingMethod column: Approved Override, Base List, Customer Price List, and Segment Price List. You can check the spelling in the column’s filter list.
4. Type Total in A20 and in F15.

The grid’s labels are in place, with the discount rates across row 15 and the pricing methods down column A. Because the next formula matches these labels against the data, they must be spelled exactly as the data spells them.

Step 5. Fill the grid with one formula.

1. In B16, enter this formula:

   =COUNTIFS(SalesInvoiceLine[PricingMethod],$A16,SalesInvoiceLine[Discount],B$15)

2. Copy B16 with Ctrl+C, select B16:E19, and paste with Ctrl+V. Do not drag the fill handle across the grid, for the reason given in the Watch out box on structured references.

3. Click E19 and read the formula bar: it should refer to $A19 and E$15.

The reference $A16 locks the column, so every copy of the formula reads its pricing method from column A while the row adjusts. The reference B$15 locks the row, so every copy reads its discount rate from row 15 while the column adjusts.

Step 6. Add the totals.

1. In F16, enter =SUM(B16:E16) and copy it down to F19.
2. In B20, enter =SUM(B16:B19) and copy it across to F20.

The grand total in F20 must equal the number of invoice lines in B3, because every line has exactly one pricing method and one discount rate. If it does not, a label in column A or row 15 does not match the data exactly.

Step 7. Convert the counts into shares of all lines.

1. In A22, type Share of all invoice lines, and repeat the pricing method labels in A23 through A26.
2. In B23, enter =B16/$F$20, copy it, and paste it into B23:E26.
3. Format the block as percentages with one decimal.

The absolute reference $F$20 keeps every copy pointed at the grand total. Figure 4.6 shows the finished worksheet.

Figure 4.6: The Analysis Worksheet with the Control Totals and the Summary Grid.

Checkpoint. Before moving on, check that:

• the grand total of the grid equals the number of invoice lines;
• the share block adds up to 100 percent, which =SUM(B23:E26) confirms, because the rounded percentages on display can add up to slightly more.

Then read the grid. Most lines are billed at a segment price list with no discount. Discounts of 8, 10, and 12 percent, the rates of the promotion programs, appear alongside every pricing method except Base List, which Charles River uses only for its hourly design services, such as Design Consultation. A promotion is therefore not a separate way of pricing a line. It is a discount applied on top of the line’s price, whichever way that price was set. That is why a slicer on the PromotionID column, which you will add in Tutorial 4.3, is the way to find out which promotions were used.

4.7 Sorting, Filtering, and Slicers

Sorting and filtering are the most basic tools for exploring transaction data, and you used both throughout Part I. This section adds the techniques that make them more useful for analysis: sorting on more than one column, filtering on conditions rather than individual values, summarizing only the rows in view, and filtering with slicers.

4.7.1 Sorting on More Than One Column

The sort commands on a column’s filter button sort the Table by that column. Their labels depend on the type of data, for example Sort Smallest to Largest for numbers, Sort Oldest to Newest for dates, and Sort A to Z for text. To sort on several columns at once, use Data > Sort, which lets you add levels. For example, you can sort the SalesInvoiceLine Table first by PromotionID and then, within each promotion, by LineTotal from largest to smallest. Excel applies the levels in order, so the first level decides the overall order and each later level only breaks ties within it. Sorting a Table rearranges its rows but never separates the values of a row from one another, because Excel moves each row as a whole.

4.7.2 Filtering on Conditions

A column’s filter button offers more than a list of values to check. Number filters select values greater than, less than, or between amounts you specify, or the top items by count or percentage. Text filters select values that begin with, end with, or contain given characters. Date filters select values before, after, or between dates, and they also offer relative choices such as This Quarter and Last Year. Filters on different columns combine, so a Table filtered on two columns shows only the rows that meet both conditions. The status bar at the bottom of the window reports how many records are in view, as it did in Tutorial 2.1.

WarningWatch out

Relative date filters such as This Quarter, Last Year, and Year to Date are measured from today’s date, not from the dates in the data. A historical extract such as the Charles River data ends on a fixed date, so these filters return the wrong period or nothing at all. Filter historical data with explicit dates instead, for example Between 2026-10-01 and 2026-12-31 for the fourth quarter of fiscal 2026.

4.7.3 Summarizing Only the Visible Rows

A filter hides rows, but it does not change what most formulas calculate. The formula =SUM(SalesInvoiceLine[LineTotal]) returns the total of every row in the Table, including the rows the filter has hidden. To summarize only the rows in view, use the SUBTOTAL function, whose first argument chooses the calculation: =SUBTOTAL(109,SalesInvoiceLine[LineTotal]) sums the visible rows, and =SUBTOTAL(103,SalesInvoiceLine[SalesInvoiceLineID]) counts them. Rows removed by a filter are always excluded. The function numbers in the 100s also exclude rows that you have hidden manually, while the corresponding numbers below 100, such as 9 for a sum, include them. Placing a SUM next to a SUBTOTAL is a quick way to show both the total population and the part of it currently in view.

4.7.4 Slicers

A slicer is a panel of buttons, one for each value in a column, that filters a Table when you click a button. To add one, click any cell in the Table, choose Insert Slicer on the Table Design tab, and select the columns you want. Clicking a button filters the Table to that value, and you can select several buttons at once with the Multi-Select button at the top of the slicer or by holding Ctrl as you click. The Clear Filter button at the top of the slicer removes its filter. Slicers show at a glance which filter is active, which makes them well suited to comparing categories one at a time, such as the promotions or pricing methods on the invoice lines.

NoteConnecting the Dots

Slicers also filter PivotTables, which you will build later in Part II, and they reappear in the Power BI reports of Part IV, where, by default, a slicer filters the other visuals on its report page. The skill is the same in all three places: isolating one part of the data to answer a focused question, while keeping track of the filter that produced the answer.

4.8 Conditional Formatting

Conditional formatting changes the appearance of cells that meet a rule you define. It does not change the data. What it does is direct your attention, so that instead of reading thousands of rows you can see at once where the large amounts, the exceptions, or the unusual combinations are. Table 4.3 summarizes the main rule types, all of which are on the Home tab under Conditional Formatting.

Table 4.3: Types of conditional formatting rules

Rule type	What it does	Accounting example
Highlight Cells Rules	Formats cells greater than, less than, between, or equal to a value, or containing given text	Highlight invoice lines above a review threshold
Top/Bottom Rules	Formats the highest or lowest items by count or percentage, or values above or below the average	Highlight the top 10 percent of line amounts
Data Bars	Fills each cell with a bar whose length reflects its value	Compare discount dollars across promotions in a summary grid
Color Scales	Shades cells along a color gradient from low to high	Show the spread of margins across items
Icon Sets	Places an icon in each cell according to thresholds	Mark variances as favorable, neutral, or unfavorable
Formula-based rules	Formats cells for which a formula you write returns TRUE	Highlight every row of the invoice lines that carries a promotional discount

The formula-based rule, reached through New Rule and “Use a formula to determine which cells to format,” is the most flexible. It lets you format a whole row based on the value in one of its columns. If you select the data rows of a Table and enter the rule =$J2>0, Excel evaluates the formula for every selected cell, adjusting the row for each one, while the dollar sign keeps the column fixed on column J. Every cell in a row whose column J value is greater than zero is formatted, so the entire row stands out. Formulas in conditional formatting rules use cell references rather than structured references, so check the column letter of the field you want before writing the rule.

You can apply several rules to the same cells, and Excel applies them all. When two rules would set the same property, such as the fill color, the rule higher in the list wins. The Manage Rules dialog shows the list, lets you change the order, and offers a Stop If True option that prevents lower rules from applying to a cell that meets the current one. A new rule is added at the top of the list, so the most recently created rule takes priority unless you reorder the rules.

WarningWatch out

Color alone is a weak way to communicate a finding. Readers who are color-blind may not see the difference, a printed copy may lose it, and a highlighted cell cannot be counted or totaled. Use conditional formatting to find the exceptions, and then record them in a form that survives: a flag column, a COUNTIFS total on a summary sheet, or a filtered list. Excel’s Filter by Color command, on the filter button of a formatted column, is a quick way to go from the highlighted cells to such a list.

4.9 Data Validation

Data validation controls what can be entered in a cell. In an analytical workbook its main use is to protect the parameter cells that drive the analysis, because a mistyped parameter flows silently into every formula that depends on it. To add a rule, select the cell and choose Data > Data Validation. The Settings tab offers several types of criteria. A List of allowed values creates a drop-down menu in the cell. Whole Number and Decimal accept numbers, optionally between a minimum and a maximum, and Date and Time accept values within a range. Text Length limits the number of characters, and Custom accepts any entry for which a formula you write returns TRUE.

The dialog has two more tabs. The Input Message tab lets you write a note that appears whenever the cell is selected, which is a convenient place to explain what the parameter means and what values are sensible. The Error Alert tab decides what happens when someone enters an invalid value. The Stop style refuses the entry. The Warning and Information styles only caution the user, who can accept the invalid value anyway. For a parameter cell, Stop is usually the right choice.

Validation has limits worth knowing. It checks only values typed into a cell. A value pasted or filled into the cell gets no alert, and an ordinary paste also replaces the cell’s rule with that of the copied cell, while Paste Values leaves the rule in place. A formula whose result breaks the rule raises no alert either. Adding a rule does not check the values already in the cells, although the Circle Invalid Data command on the Data Validation menu marks every value that breaks a rule, whether it was typed, pasted, or calculated by a formula. Validation is therefore a guard against typing mistakes, not a complete control, and it works best alongside the review of parameters that a documentation worksheet makes possible.

TipIn Practice

Audit and finance workbooks often gather their parameters on a single worksheet, with each parameter named, validated, and described: the period under review, the materiality amount, a sampling threshold, or an exchange rate. A reviewer can then see every assumption that drove the results in one place and confirm that each is appropriate, instead of hunting for numbers typed into formulas across the workbook. When a parameter changes, the change is made once, in a cell that refuses implausible values.

4.10 Organizing and Documenting the Workbook

A well-organized workbook is easier to build, easier to review, and less likely to contain errors. The organizing principle is separation. The data, the parameters, the calculations, and the explanation of the work each belong in their own place, so that a reviewer can check each one on its own and follow a number from one to the next.

4.10.1 Layers of an Analysis Workbook

An analysis workbook built with Power Query naturally falls into four layers, which Figure 4.7 shows. The data worksheets hold the Tables that the queries load, and nothing else, so that a refresh can replace their contents safely. The parameters worksheet holds the named inputs of the analysis, protected by validation, and the named values derived from them. The analysis worksheets hold the formulas that summarize the data, using structured references to the Tables and names for the parameters. The documentation worksheet, placed first in the workbook, explains the rest. If you need a formatted output for a report, it goes on its own worksheet as well, drawing its figures from the analysis worksheets rather than recalculating them.

Figure 4.7: Layers of an Analysis Workbook.

This separation is the spreadsheet equivalent of an audit trail. If a reviewer questions a number on the analysis worksheet, the formula names the Table and the parameter it uses, the query names the source Table and every step applied to it, and the source is the company’s own data. Each link can be checked. It is the same idea as the source-to-ledger traceability you studied in Chapters 1 and 3, applied to your own work.

4.10.2 Documentation

Every analytical workbook should begin with a documentation worksheet that tells a reviewer what the workbook is and how it was built. It should record:

• the purpose of the analysis and the question it addresses;
• the source of the data, including the file, the Tables used, and the filters applied in each query;
• the date the data was last refreshed;
• the parameters and what each one controls;
• the name of the preparer and the date the work was completed and, once it has been reviewed, the name of the reviewer and the date of the review;
• the worksheets and what each contains;
• any open questions or limitations.

A short note on formatting conventions helps too. A common convention uses blue font for input cells that a user may change and black font for formulas. Whatever convention you choose, apply it consistently and state it on the documentation worksheet.

Within the analysis worksheets, cell notes can explain a formula that is not self-evident or record a decision made during the analysis. The goal of all of this is the one that auditing standards set for audit documentation. For audits of public companies, the Public Company Accounting Oversight Board (PCAOB, n.d.) requires audit documentation that lets an experienced auditor with no previous connection to the engagement understand the work performed, the evidence obtained, and the conclusions reached. The documentation must also show who performed the work and who reviewed it, and when. An analysis workbook documented to that standard can be reviewed, refreshed, and extended by someone other than the person who built it.

NoteConnecting the Dots

The documentation worksheet you build in this chapter is the one you will extend throughout Part II, as the analysis workbook grows. When you reach the audit analytics work at the end of Part II, you will find that the same practices underlie an audit workpaper, where recording the source, the parameters, the steps, the preparer, and the reviewer is a professional requirement rather than good practice.

4.11 Guided Tutorial 4.3: Surfacing Promotions and Documenting the Workbook

Context and objective. In this tutorial, you will use the techniques from the last four sections to finish the analysis workbook. You will protect the threshold parameter with data validation, highlight the invoice lines that carry a promotional discount, sort and slice the lines to compare the promotions, and use SUBTOTAL to measure the lines in view. You will then look up the promotion that stands out and record what you found, and what remains open, on a documentation worksheet.

Prerequisites. You need the Charles River Furniture Analysis workbook from Tutorials 4.1 and 4.2. If you start here, download Charles River Furniture Analysis.xlsx as it stands at the end of Tutorial 4.2 from Companion Files, and point its queries at your copy of CharlesRiver.xlsx as that page explains.

Step-by-step instructions.

Step 1. Protect the threshold parameter with data validation.

1. On the Parameters worksheet, select B3, the ThresholdPercentile cell, and choose Data > Data Validation.
2. On the Settings tab, allow Decimal between a minimum of 0.5 and a maximum of 0.99.
3. On the Input Message tab, type the title Threshold percentile and the message Enter a share from 0.5 to 0.99, such as 0.9.
4. On the Error Alert tab, choose the Stop style and type a short explanation. Click OK.
5. Type 1.5 in B3 to test the rule.
6. When Excel displays the alert, click Cancel so that 0.9 remains.

Excel refuses the entry and shows your explanation. The rule blocks an out-of-range value typed into the cell, the mistake it is there to catch, although, as the section on data validation explains, it cannot stop a value pasted into the cell. Selecting B3 now also displays the input message, which tells the next user what the cell expects.

Step 2. Highlight every line with a promotional discount. A formula rule can shade an entire row based on the value in one of its columns.

1. Go to the SalesInvoiceLine worksheet.
2. Click cell A2, the first data cell of the Table, and press Ctrl+A, which selects the Table’s data rows without the header while A2 stays the active cell.
3. Choose Home > Conditional Formatting > New Rule, select Use a formula to determine which cells to format, and enter =$J2>0. Column J holds Discount; if your Discount column has a different letter, use that letter instead.
4. Click Format, choose a light fill color on the Fill tab, and click OK twice.
5. Scroll through the Table.

Excel reads the formula as written for the active cell in row 2 and adjusts the row for every other selected cell, which is why A2 must be the active cell. Every row whose discount is greater than zero is now shaded across the whole Table, and the shaded rows are scattered among many unshaded ones.

Step 3. Sort the lines so that the promotion lines come together.

1. Choose Data > Sort.
2. For the first level, sort by PromotionID from Smallest to Largest.
3. Click Add Level, and sort the second level by DiscountAmount from Largest to Smallest. Click OK.

The shaded rows now sit at the top of the Table, grouped by promotion, with the largest discount first within each group. The lines without a promotion have a blank PromotionID, and blank cells always sort to the bottom.

Step 4. Add a slicer.

1. With a cell in the Table selected, choose Table Design > Insert Slicer, select PromotionID, and click OK.
2. Move the slicer beside the Table. It shows one button for each promotion number and one for the blank value.
3. Click the button for promotion 1, and read the status bar at the bottom of the window, which reports how many of the Table’s records are in view.
4. Click each of the other promotion buttons in turn and compare the counts.

Each button filters the Table to the lines of one promotion, and the slicer shows which button is active. The Table’s rows are hidden, not deleted, so clearing the slicer later brings them all back.

Step 5. Measure the lines in view.

1. On the Analysis worksheet, type Lines in view in A28 and enter this formula in B28:

   =SUBTOTAL(103,SalesInvoiceLine[SalesInvoiceLineID])

2. Type Discount in view in A29 and enter =SUBTOTAL(109,SalesInvoiceLine[DiscountAmount]) in B29.

3. Type Discount, all lines in A30 and enter =SUM(SalesInvoiceLine[DiscountAmount]) in B30.

4. Return to the slicer and click each promotion button again, noting the two SUBTOTAL results each time.

B30 never changes, because SUM includes the hidden rows, while B28 and B29 follow the slicer. One promotion stands out on both measures: promotion 8 has more lines than any other and far more discount dollars. Figure 4.8 shows the Table with promotion 8 selected.

Figure 4.8: The Invoice Lines Filtered to Promotion 8 with a Slicer.

Step 6. Find out what promotion 8 is.

1. Go to the PromotionProgram worksheet and locate the row whose PromotionID is 8.
2. Read its PromotionCode, PromotionName, ScopeType, ItemGroup, DiscountPct, EffectiveStartDate, and EffectiveEndDate.
3. Compare its DiscountPct with the Discount on the promotion 8 lines in the SalesInvoiceLine Table; they should agree.
4. Return to the slicer and click its Clear Filter button so that every line is in view again.

Promotion 8 is PROMO-2026-008, the Furniture Seasonal Promotion. It applied a 10 percent discount to the entire Furniture item group, and it was effective from September 1 to October 31, 2026.

Step 7. Create the documentation worksheet. The documentation worksheet comes first in the workbook, so that a reviewer reads it before anything else.

1. Insert a new worksheet, name it Documentation, and drag its tab to the far left.
2. In A1 through A11, type these labels: Purpose, Source, Query steps, Last refreshed, Parameters, Calculated columns, Worksheets, Conventions, Prepared by, Reviewed by, and Open questions.
3. Format column A in bold, and widen column B so that the entries you type next are readable.

The labels give the worksheet the structure of Figure 4.9. Column B, which you fill in next, holds what a reviewer needs to know under each label.

Step 8. Record what the workbook contains. The entries below describe what to record; your wording can differ.

1. In column B, beside each label, enter the information for your workbook:

   • Purpose: diagnose the decline in Furniture gross margin from the third to the fourth quarter of fiscal 2026, as requested by the controller.
   • Source: CharlesRiver.xlsx and its folder location; Tables T16_SalesInvoice, T17_SalesInvoiceLine, T44_Item, and T7_PromotionProgram, imported with Power Query.
   • Query steps: Item keeps only items with a list price, and eleven columns; SalesInvoice has its date columns set to the Date type; FreightAmount and Discount have the decimal number type in their Changed Type steps; the other queries keep every row and column.
   • Last refreshed: the date of your last Refresh All.
   • Parameters: ThresholdPercentile (Parameters!B3, an input validated between 0.5 and 0.99) and LargeLineThreshold (Parameters!B4, a formula).
   • Calculated columns: ListAmount and DiscountAmount on the SalesInvoiceLine Table, with their formulas.
   • Worksheets: each worksheet in the workbook and what it contains.
   • Conventions: blue font marks input cells; black font marks formulas.
   • Prepared by: your name and today’s date. Reviewed by: left blank until the workbook is reviewed, when the reviewer adds a name and the date of the review.
   • Open questions: Promotion 8, the Furniture Seasonal Promotion (10 percent, September 1 to October 31, 2026), has the most promotion lines and the most discount dollars, and its dates straddle the two quarters under review. Its effect on Furniture prices in each quarter cannot yet be measured, because the invoice lines carry no invoice date or item group.

2. Open Formulas > Name Manager and read the list of names.

The Name Manager lists your two named cells alongside the four Tables, and it may also list a name for the slicer. Together with the Parameters entry, it gives a reviewer a complete list of the names that the workbook’s formulas use. Figure 4.9 shows a completed example.

Figure 4.9: The Documentation Worksheet of the Analysis Workbook.

Step 9. Put the workbook in order and refresh it.

1. Arrange the worksheet tabs in the order of the layers in Figure 4.7: Documentation first, then the four data worksheets, then Parameters and Analysis.
2. Choose Data > Refresh All one more time, and check that the calculated columns and the highlighting are still in place.
3. Update the Last refreshed date on the Documentation worksheet.
4. With the Documentation worksheet still selected, save the workbook. Excel opens a workbook on the worksheet that was selected when it was saved.

The workbook now opens on its own explanation, and a refresh repeats every query step without disturbing the formulas, formats, or documentation built on top of the data.

Checkpoint. Your workbook should now open on a Documentation worksheet that explains it. Check that:

• every number on the Analysis worksheet traces to a named Table or a named parameter;
• an out-of-range threshold percentile is refused;
• the promotion lines are shaded;
• the slicer shows that promotion 8, the Furniture Seasonal Promotion, has both the most lines and the most discount dollars of any promotion.

You have not yet shown that this promotion explains the fall in the Furniture margin, and the open question on your Documentation worksheet says why: to measure its effect in each quarter, the invoice lines must first be combined with the invoice dates and item groups that live in other Tables.

4.12 Looking Ahead

This chapter has built the foundation for the Excel work that follows. You have seen why the structure of a workbook acts as a control over the accuracy of an analysis. You have used Power Query to bring selected Charles River Tables into a workbook of your own without altering the company’s data, and you have written structured references and calculated columns that stay correct as the data changes. You have used relative, absolute, and mixed references and named parameter cells to build a summary grid, explored the invoice lines with sorting, filters, slicers, and SUBTOTAL, made exceptions visible with conditional formatting, protected a parameter with data validation, and documented the workbook so that a reviewer can follow it.

The workbook has also produced the first clue to the controller’s question. More invoice lines carry the Furniture Seasonal Promotion than any other promotion, and its effective dates, September and October 2026, straddle the end of the third quarter and the start of the fourth, the two quarters whose margins the controller wants explained. A clue is not an answer, however. The invoice lines carry neither an invoice date nor an item group, so the workbook cannot yet say how many of those lines fell in each quarter, or how much the promotion lowered Furniture prices compared with the other causes a margin can have. Answering those questions requires combining the invoice lines with the invoices, the items, and the customers into a single analytical table, which is the work of Chapter 5. The later chapters of Part II then summarize that table and measure the effects that explain the change in the Furniture margin on the invoice lines.

4.13 Chapter Summary

An analysis that supports a decision or an audit conclusion must be accurate and reviewable, and the structure of the workbook that holds it is one of the controls that make it so. Research on operational spreadsheets has found errors in a small percentage of cells, which in a large workbook makes some errors close to certain, and it has found that the quality control around spreadsheets is usually informal. Structured design practices remove common opportunities for error and make the remaining logic visible to a reviewer, although they do not replace careful review.

Excel Tables manage a dataset as a single named object that knows its own extent. Structured references, such as SalesInvoiceLine[LineTotal] and [@Quantity], refer to Table data by name, so formulas remain correct as the data grows and state their own logic to a reviewer. Calculated columns apply one formula to every row of a Table by default, and Excel flags a row whose formula has been changed. Power Query brings data into a workbook without altering its source: it connects to a file such as CharlesRiver.xlsx, records every filter and change as an applied step, loads the result as a Table named after the query, and repeats all of it on refresh. The analysis workbook you built imports only the Tables and rows the Furniture question needs, and its control totals show that the imported invoice lines agree with the imported invoices, although only evidence from outside the import, such as a count of the rows in the source, can show that nothing is missing from both.

Relative, absolute, and mixed references determine how a formula changes when it is copied. Relative references suit row-by-row calculations, absolute references point every copy at one cell, and mixed references let a single formula fill a two-way grid. Structured references stay fixed when copied but shift when filled across, which is a trap worth remembering. Named cells hold the parameters of an analysis, such as a threshold derived from the data with PERCENTILE.INC, and make every formula that uses them easier to read.

Multi-level sorts, condition-based filters, and slicers let you explore transaction data one question at a time, and the SUBTOTAL function summarizes only the rows in view. Conditional formatting, including formula-based rules that shade whole rows, makes patterns and exceptions visible, and data validation guards parameter cells against implausible values typed into them. A layered workbook, with data, parameters, analysis, and a documentation worksheet placed first, lets a reviewer follow any number back to its source. Applied to the Charles River invoice lines, these tools showed that the Furniture Seasonal Promotion accounts for more promotion lines and more discount dollars than any other promotion, a clue to the controller’s question that the rest of Part II will test.

4.14 Key Terms

Absolute reference. A cell reference in which both the column and the row are locked with dollar signs, such as $A$2, so that the reference does not change when the formula is copied. Absolute references are used when every copy of a formula must refer to the same cell, such as a grand total used as a denominator.

Applied steps. The list in the Power Query Editor that records each action taken on a query, such as filtering rows, removing columns, or changing a data type. Power Query repeats the steps in order every time the query is refreshed, and the list documents how the data was prepared.

Calculated column. A column of an Excel Table whose formula Excel applies by default to every row, including rows added later. Calculated columns are the natural place for row-level measures, such as the list amount or the discount on each invoice line. A single row can still be overwritten, which Excel’s error checking flags.

Conditional formatting. An Excel feature that changes the appearance of cells that meet a rule, such as values above a threshold, the top items in a range, or rows for which a formula returns TRUE. It directs attention to patterns and exceptions without changing the data.

Control total. A total calculated independently from a set of data, which the data must agree with, such as the sum of the invoice subtotals against the sum of the invoice lines. A difference calls for investigation, because records may be missing, duplicated, or wrongly included, or values may be wrong. Agreement shows only that the two sides match: records missing from both sides go unnoticed.

Data validation. An Excel feature that restricts what can be typed into a cell, such as a list of allowed values or a number within a range, and that can display an input message and refuse invalid entries. In analytical workbooks it protects the parameter cells that drive the results.

Documentation worksheet. A worksheet, usually the first in a workbook, that records the purpose of the analysis, the data sources and query steps, the parameters, the worksheets and their contents, the preparer and reviewer, and any open questions, so that someone else can understand and review the work.

Excel Table. A range of data that Excel manages as a single named object, with a header row, filter buttons, banded formatting, automatic expansion, and support for structured references. An Excel Table is a feature of the spreadsheet, distinct from a table in a relational database, although each worksheet of CharlesRiver.xlsx stores one database table as an Excel Table.

Mixed reference. A cell reference that locks either the column or the row but not both, such as $A2 or A$2. Mixed references let a single formula fill a two-way grid, with the row labels in a locked column and the column headers in a locked row.

Named cell. A cell to which a descriptive name has been assigned, such as LargeLineThreshold, so that formulas can use the name in place of the cell address. A name created in the Name Box refers to its cell with an absolute reference, so it behaves like one while making the formula easier to read. A name can also refer to a range of cells, and the Name Manager lists every name in a workbook.

Parameter cell. A cell that holds an input to an analysis, such as a threshold, a percentile, or the start date of a review period, so that the value is stated once and used by every formula that needs it. Parameter cells are usually named, validated, and gathered on one worksheet.

Power Query. Excel’s tool for connecting to data, shaping it, and loading it into a workbook, found under Data > Get Data. It records each transformation as an applied step, loads results as Excel Tables without altering the source, and repeats the steps when the data is refreshed.

Relative reference. A cell reference, such as A2, that adjusts when the formula is copied, so that each copy refers to the cell in the same relative position. Relative references are the default in Excel and suit calculations in which each row uses its own data.

Slicer. A panel of buttons, one for each value in a column, that filters an Excel Table or a PivotTable when a button is clicked. Slicers make it easy to compare categories one at a time and show which filter is active.

Structured reference. A reference that uses a Table’s name and a column’s name instead of cell addresses, such as SalesInvoiceLine[LineTotal] for a whole column or [@LineTotal] for the current row. Structured references adjust automatically as the Table grows and make formulas self-documenting.

SUBTOTAL function. An Excel function that calculates a sum, count, average, or other summary of only the rows that are visible, excluding the rows hidden by a filter. Function numbers from 101 to 111, such as 109 for a sum, also exclude rows hidden manually.

4.15 Multiple Choice Questions

1. A staff accountant stores a dataset of invoice lines as an Excel Table instead of a plain range. Which of the following is a direct benefit of the Table?

1. The Table prevents other users from changing any of the values it contains.
2. The Table automatically creates a PivotTable summary of every numeric column.
3. Formulas can refer to its columns by name and include rows added later.
4. The Table checks each value against the source database and flags values that differ.

2. An analyst filters the SalesInvoiceLine Table to show only the lines of one promotion and then enters =SUM(SalesInvoiceLine[LineTotal]) on another worksheet. The result equals the total of all invoice lines, not just the lines in view. What should the analyst use instead?

1. =SUBTOTAL(109,SalesInvoiceLine[LineTotal])
2. =SUMIFS(SalesInvoiceLine[LineTotal],SalesInvoiceLine[LineTotal],">0")
3. =COUNTIFS(SalesInvoiceLine[LineTotal],">0")
4. =SUM(SalesInvoiceLine[@LineTotal])

3. Inside the SalesInvoiceLine Table, a calculated column uses the formula =ROUND([@Quantity]*[@BaseListPrice],2). What does [@Quantity] refer to?

1. The entire Quantity column of the Table
2. The Quantity value in the same row as the formula
3. The first value in the Quantity column
4. The Quantity column of whichever Table is selected when the workbook recalculates

4. A formula in cell D2 reads =C2/$C$500, where C500 holds the total of column C. The formula is copied down to D3 through D499. Which type of reference is $C$500?

1. Relative reference
2. Mixed reference with the column locked
3. Structured reference
4. Absolute reference

5. A summary grid lists pricing methods in column A and discount rates in row 15. The formula in B16 is:

=COUNTIFS(SalesInvoiceLine[PricingMethod],$A16,SalesInvoiceLine[Discount],B$15)

The formula is copied and pasted into C16. What does the copy in C16 refer to?

1. $B16 and C$15
2. $A16 and B$15
3. $A16 and C$15
4. $A17 and B$16

6. An analysis workbook imports the SalesInvoiceLine Table from CharlesRiver.xlsx with Power Query, after filtering and removing columns in the Power Query Editor. When a colleague later chooses Data > Refresh All, what happens?

1. Power Query writes the filtered data back to CharlesRiver.xlsx so that the two workbooks match.
2. Power Query rereads the source and repeats every applied step, leaving the source unchanged.
3. Power Query reloads the data without the filters, which must then be applied again by hand.
4. Power Query deletes the Table from the analysis workbook and asks the colleague to choose the data again.

7. In the Power Query Navigator, CharlesRiver.xlsx lists both a worksheet named SalesInvoiceLine and a Table named T17_SalesInvoiceLine. Why is the Table usually the better choice to import?

1. The Table already defines the header row and the extent of the data.
2. Only Tables can be refreshed; worksheets can be imported only once.
3. The Table contains more rows than the worksheet that holds it.
4. Importing a worksheet changes the formatting of the source workbook.

8. An auditor opens the Charles River shipment data, which ends on December 31, 2026, and applies the date filter This Quarter to the ShipmentDate column. The filter shows no rows, or rows from the wrong period. What is the most likely explanation?

1. Date filters work only on columns formatted as text.
2. The ShipmentDate column contains no dates in the fourth quarter.
3. Filters cannot be combined with the banded formatting of an Excel Table.
4. This Quarter counts from today’s date, not from the data’s dates.

9. A slicer on the PromotionID column is connected to the SalesInvoiceLine Table. An analyst clicks the button for promotion 8. What happens?

1. The rows for every other promotion are deleted from the Table until the workbook is refreshed.
2. The Table shows only promotion 8’s rows, and the others are hidden, not deleted.
3. A new worksheet is created that holds a copy of the rows for promotion 8.
4. The rows for promotion 8 are highlighted, and every row remains visible.

10. An analyst selects the data rows of a Table, with cell A2 active, and creates the conditional formatting rule =$J2>0, where column J holds the Discount. Why does the reference include a dollar sign before J but not before 2?

1. The column stays fixed on J while the row adjusts, so each row is tested on its own Discount.
2. Conditional formatting rules require every reference in the formula to be absolute.
3. The dollar sign converts the rule into a structured reference to the Discount column.
4. The dollar sign limits the rule to the cells in column J and leaves the other columns unformatted.

11. A worksheet has two conditional formatting rules on the same cells. The first rule, created earlier, fills values above 1,000 with green. The second rule, created later, fills values above 5,000 with red. Neither rule uses Stop If True, and the order has not been changed. What fill does a cell containing 6,200 display?

1. Green, because the rule created first is always evaluated first
2. Both colors, because Excel divides the cell between the two rules
3. Red, because the newer rule sits higher in the list and wins
4. No fill, because the two rules conflict and cancel each other

12. A parameter cell has a data validation rule that allows decimals between 0.5 and 0.99. A user types 1.5, Excel displays an alert, and the user clicks Yes to continue, after which the cell holds 1.5. What explains this result?

1. Data validation does not apply to decimal values.
2. The rule was entered with the minimum and maximum reversed.
3. Data validation checks only values pasted into the cell, not values typed.
4. The alert style was Warning, which lets a user keep an invalid entry.

13. An analyst types a summary formula in the empty column immediately to the right of an Excel Table. What is the most likely result, and what is the better practice?

1. The Table absorbs the column and may fill the formula down; summaries belong on a separate worksheet.
2. Excel refuses the entry, because cells next to a Table are locked; summaries must be entered in a new workbook.
3. The formula is ignored until the Table is refreshed; summaries should be entered only after a refresh.
4. The formula is converted into a PivotTable; summaries are best created as PivotTables in any case.

14. Which of the following best explains why an analysis workbook should begin with a documentation worksheet?

1. Excel requires a documentation worksheet in any workbook that contains Power Query queries.
2. It tells a reviewer the purpose, sources, steps, and parameters, so that someone new can review the work.
3. A documentation worksheet makes the workbook’s formulas calculate faster and use less memory.
4. Accounting standards require every spreadsheet used by a company to begin with a documentation worksheet.

15. Instead of typing an amount such as 5,000, an analyst defines a large-line threshold with this formula in a cell named LargeLineThreshold:

=PERCENTILE.INC(SalesInvoiceLine[LineTotal],ThresholdPercentile)

What is the main advantage of this approach?

1. PERCENTILE.INC guarantees that every line above the threshold is an error to investigate.
2. A named cell cannot be changed, so the threshold is protected without any data validation.
3. The threshold comes from the data, sits in one named cell, and updates with it.
4. The formula keeps the threshold identical from one year to the next, whatever the data.

4.16 Applied Exercises

Work on these exercises in a copy of your analysis workbook, made after you finish the chapter’s tutorials, or in a new workbook where an exercise says so. Put each exercise on its own worksheets, named after it (such as Ex 4.1), and do not rename or edit the queries the tutorials created. The tutorials of the next chapters continue in the original workbook and expect it to be exactly as the tutorials left it.

4.16.1 Financial Accounting Exercises

Exercise 4.1: A Billing Schedule by Fiscal Year

Table group: Order-to-Cash

Scenario. You are a financial reporting analyst at Charles River. The controller wants a schedule of amounts billed to customers in each fiscal year, built so that it can be refreshed and reviewed. The schedule must tie to the invoice detail and state clearly what it does and does not include.

Requirements.

1. In your copy of the analysis workbook, use the SalesInvoice Table that Tutorial 4.1 loaded, with its date columns already set to the Date type.

2. On a worksheet named Ex 4.1, create two named cells, PeriodStart and PeriodEnd, and apply data validation that allows only dates from January 1, 2024, to December 31, 2026. Enter the first and last days of fiscal 2026. Remember that Charles River’s fiscal year is the calendar year.

3. On the same worksheet, use COUNTIFS and SUMIFS with the named cells to calculate, for the period, the number of invoices and the totals of SubTotal, FreightAmount, TaxAmount, and GrandTotal. Confirm that SubTotal plus FreightAmount plus TaxAmount equals GrandTotal.

4. Build a grid with the fiscal years 2024, 2025, and 2026 in a column and the four amounts across a row. Fill it with one SUMIFS formula that uses mixed references, calculating each year’s start and end dates with the DATE function. The four amount columns sit side by side in the SalesInvoice Table, in the order SubTotal, FreightAmount, TaxAmount, GrandTotal, so here you can fill the formula across rather than paste it, provided you lock the column that must not move, as the Watch out on structured references explains. Add a total row and show that it equals the totals of the entire SalesInvoice Table.

5. Write two or three sentences explaining why this schedule is not a revenue figure for the financial statements. Consider, for example, the CreditMemo worksheet in CharlesRiver.xlsx, sales tax, and freight.

Deliverable. A refreshable billing schedule by fiscal year that ties to the invoice detail, with validated period parameters and a short note on its limitations.

Exercise 4.2: Reviewing Promotional Discounts

Table group: Order-to-Cash

Scenario. The sales director has asked the finance team for a summary of the promotion programs Charles River ran and the discounts they gave away. You are asked to prepare it from the analysis workbook and to check that each promotion was applied at its approved rate.

Requirements.

1. Using the SalesInvoiceLine and PromotionProgram Tables from Tutorial 4.1, including the DiscountAmount calculated column, build a summary on a worksheet named Ex 4.2 with one row for each PromotionID in the PromotionProgram Table. For each promotion, show the PromotionCode and PromotionName, the number of invoice lines, the total LineTotal, and the total DiscountAmount.

2. Add a column that shows each promotion’s share of all discount dollars, using an absolute reference to the grand total. Apply data bars to the discount column of the summary.

3. For each promotion, use COUNTIFS to count the invoice lines whose Discount differs from the promotion’s DiscountPct. Report whether any promotion was applied at a rate other than its approved rate.

4. Identify the promotion with the largest share of the discount dollars, and describe its scope, its rate, and its effective dates from the PromotionProgram Table.

5. Write a brief memo (one half page) summarizing the promotions and the consistency test, with what a matching rate does and does not show, and what the analysis cannot yet show about when each discounted line was billed. If any promotion’s effective dates in the PromotionProgram Table look wrong, list them as a question to follow up rather than correcting them.

Deliverable. A promotion summary with shares of the discount dollars, a rate consistency test, and a half-page memo.

4.16.2 Managerial Accounting Exercises

Exercise 4.3: List-Price Margins by Product Group

Table group: Master Data (Item)

Scenario. You are a management accountant preparing a product overview for the operations team. The team wants to know which product groups carry the highest list-price margins and which items fall below a margin target.

Requirements.

1. In your copy of the workbook, add two calculated columns to the Item Table from Tutorial 4.1: UnitMargin, equal to ListPrice minus StandardCost, and MarginPct, equal to UnitMargin divided by ListPrice.

2. Filter the Table to the items whose ItemType is Finished Good, which excludes the design services. Explain why the design services would distort a margin comparison.

3. Sort the Table by ItemGroup from A to Z and then by MarginPct from largest to smallest. Apply a color scale to the MarginPct column.

4. Create a named parameter cell, MarginTarget, set to 0.45 and validated to allow decimals between 0 and 1. Add a formula-based conditional formatting rule that highlights the items whose MarginPct is below MarginTarget, applied to the columns left of MarginPct so that its fill does not hide the color scale, and use COUNTIFS to count them. A filter hides rows from view but not from COUNTIFS, so give the formula an ItemType condition rather than relying on the filter of requirement (2).

5. Create a parameter cell with a List validation rule that offers the four product groups Accessories, Furniture, Lighting, and Textiles. Use AVERAGEIFS, with the same ItemType condition, to calculate the average UnitMargin and MarginPct of the finished goods in the selected group, and record the results for each group.

6. Write three to four sentences interpreting the results. Explain why a margin at list price is not the margin Charles River actually earns, and why an average of the items’ margins, which counts every item once, is not the margin on a product group’s sales.

Deliverable. An Item analysis with margin columns, a validated margin target, highlighted exceptions, a group selector driving AVERAGEIFS results, and a short interpretation.

Exercise 4.4: Freight Billed Versus Freight Incurred

Table group: Order-to-Cash

Scenario. The logistics manager has asked whether the freight Charles River bills to customers covers what it pays its carriers. The answer will inform a discussion about freight terms.

Requirements.

1. Use Power Query to load the Shipment and SalesOrder Tables from CharlesRiver.xlsx into your copy of the workbook. Load both as Tables on new worksheets, with Load To (or Close & Load To) and then Table and New worksheet, as in Tutorial 4.1, Step 7.

2. On the Shipment Table, add a calculated column FreightTerms that brings in the FreightTerms of each shipment’s sales order with XLOOKUP, using SalesOrderID, as you did with lookups in Part I. Add a second calculated column, FreightMargin, equal to BillableFreightAmount minus FreightCost.

3. Apply a formula-based conditional formatting rule that highlights the shipments with a negative FreightMargin, and use COUNTIFS and SUMIFS to count and total them.

4. Build a small grid with the FreightTerms values as rows and, as columns, the number of shipments, the total FreightCost, the total BillableFreightAmount, and the total FreightMargin. Fill it with formulas that use mixed references.

5. Add a slicer on FreightTerms and use it, together with SUBTOTAL, to compare the average FreightCost under each term.

6. Write a brief interpretation (four to five sentences) of what the grid shows. Distinguish the freight Charles River absorbs by design from any shortfall on the freight it intends to recover.

Deliverable. A freight analysis with calculated columns, highlighted shortfalls, a summary grid by freight terms, and a short interpretation.

4.16.3 Auditing Exercises

Exercise 4.5: A Refreshable Shipment Exception Report

Table group: Order-to-Cash

Scenario. You are a first-year auditor. In Tutorial 2.1 you found shipments with no tracking number and shipments still marked In Transit after their delivery date had passed. Your senior asks you to turn those one-time filters into an exception report that the team can refresh when the next extract arrives.

Requirements.

1. Use Power Query to load the Shipment Table from CharlesRiver.xlsx into a new workbook, with ShipmentDate and DeliveryDate set to the Date type.

2. On a Parameters worksheet, create a named cell ReviewDate, validated to accept only dates, and set it to December 31, 2026, the year-end under review and the last shipment date in the extract. Explain in a note beside the cell why the report uses a fixed review date rather than the TODAY function.

3. Add two formula-based conditional formatting rules to the Shipment Table: one that highlights rows with a blank TrackingNumber, and a second, in a different color, that highlights rows whose Status is In Transit and whose DeliveryDate is before ReviewDate. Remember that conditional formatting formulas use cell references, and that a formula rule can refer to a named cell.

4. On an Analysis worksheet, use COUNTIFS to count the shipments in each exception category, the blank tracking numbers by Status, and the shipments in both categories.

5. Add a slicer on Status and use it, with SUBTOTAL, to confirm the blank tracking numbers by Status. With the slicer on In Transit, compare the number of shipments in view with your count of In Transit shipments past their DeliveryDate, and explain any difference. Then change ReviewDate to December 1, 2026, explain how the count of shipments past their DeliveryDate changes, and set ReviewDate back to December 31, 2026.

6. Prepare a brief memo for your senior (one half page) describing each exception, its likely cause, and the evidence you would request.

Deliverable. A refreshable exception report with a validated review date, highlighted exceptions, counts by category, and a half-page memo.

Exercise 4.6: Reviewing Price Override Approvals

Table group: Order-to-Cash

Scenario. Charles River lets its sales team request a price below the price list for a customer, and a manager must approve each request before the price is used. Your audit team wants a preliminary review of the approval records to see whether the control operated as designed.

Requirements.

1. Use Power Query to load the PriceOverrideApproval Table (T8_PriceOverrideApproval) from CharlesRiver.xlsx into a new workbook. Set RequestDate and ApprovedDate to the Date type.

2. Add a calculated column, DiscountFromReference, equal to one minus ApprovedUnitPrice divided by ReferenceUnitPrice, formatted as a percentage.

3. Apply formula-based conditional formatting rules that highlight, in different colors, requests with a blank ApprovedByEmployeeID and requests whose RequestedByEmployeeID equals the ApprovedByEmployeeID. COUNTIFS cannot compare two columns of the same row, so add a calculated column, SelfApproved, with the formula =[@RequestedByEmployeeID]=[@ApprovedByEmployeeID]. Use COUNTIFS to count each group, and use a slicer on Status to see how the groups relate to it.

4. Build a small grid with the requesting employees as rows and, as columns, the number of requests, the number approved by someone other than the requester, the number approved by the requester, and the number still Pending. Count a request as approved only when its Status is Approved: a blank ApprovedByEmployeeID differs from the requester’s ID, but it is not an approval by someone else. Look up the requesters’ names and job titles in the Employee worksheet of CharlesRiver.xlsx.

5. Sort the Table by DiscountFromReference from largest to smallest, and describe the range of the discounts on the Approved requests. A price recorded on a Pending request has not been approved.

6. Prepare a brief memo (one half page) that describes each exception, explains why it matters for the design of the pricing control, and lists the follow-up procedures you would propose, including how you would find out whether the pending requests were used on sales.

Deliverable. A reviewed approval Table with highlighted exceptions, a grid of requests by requester, and a half-page memo on the pricing control.

4.17 Further Reading

Bradbard, D. A., Alvis, C., & Morris, R. (2014). Spreadsheet usage by management accountants: An exploratory study. Journal of Accounting Education, 32(4), 24–30. https://doi.org/10.1016/j.jaccedu.2014.09.001 This exploratory study asks which spreadsheet features management accountants use and which features employers expect new hires to use. It is a useful guide to the Excel skills that matter in managerial accounting work, as the role shifts from recording transactions toward decision support, planning, and control.

Caulkins, J. P., Morrison, E. L., & Weidemann, T. (2007). Spreadsheet errors and decision making: Evidence from field interviews. Journal of Organizational and End User Computing, 19(3), 1–23. https://doi.org/10.4018/joeuc.2007070101 Based on interviews with 45 executives, managers, and analysts in the private, public, and non-profit sectors, this paper describes how spreadsheet errors reach decisions in practice. Almost all interviewees considered errors common, and quality control was usually informal. The paper is a readable account of why the review and documentation practices in this chapter matter.

Church, K. S., Riley, J., & Schmidt, P. J. (2022). Has Excel become a “golden hammer”: The paradox of data analytics in SME clusters. Journal of Emerging Technologies in Accounting, 19(2), 211–234. https://doi.org/10.2308/jeta-2020-051 This survey of small and medium-sized entities finds that they continue to perform their leading accounting tasks in Excel and lag larger firms in adopting data analytics technology. It explains why Excel skills remain central for accountants who will work in the many organizations of that size.

Panko, R. R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing, 10(2), 15–21. https://doi.org/10.4018/joeuc.1998040102 This early and widely cited synthesis concludes that errors occur in a few percent of spreadsheet cells, so that large spreadsheets almost certainly contain some. It also argues that careful inspection of a finished spreadsheet, cell by cell, may do more to catch errors than design rules alone, a useful counterweight to any claim that good structure makes a workbook correct.

Powell, S. G., Baker, K. R., & Lawson, B. (2008). A critical review of the literature on spreadsheet errors. Decision Support Systems, 46(1), 128–138. https://doi.org/10.1016/j.dss.2008.06.001 This review summarizes and critiques the research on spreadsheet errors from the viewpoint of a manager who wants to improve spreadsheet quality. It notes that, although errors were widely accepted to be common and costly, relatively little was known about what types of errors occur, how they arise, and how they can be avoided, and it suggests directions for further research.

Schneider, K. N., Becker, L. L., & Berg, G. G. (2017). Beyond the mechanics of spreadsheets: Using design instruction to address spreadsheet errors. Accounting Education, 26(2), 127–143. https://doi.org/10.1080/09639284.2016.1274912 Written for accounting educators, this paper argues that accounting graduates need spreadsheet design skills, not just spreadsheet mechanics. It outlines a three-step approach to teaching design practices and summarizes foundational readings on spreadsheet errors, which makes it a good starting point for further study.

Zeller, T. L., Dingrando, E., & Booker, D. (2024). Workbook design and controls: A framework. Journal of Accounting Education, 69, Article 100933. https://doi.org/10.1016/j.jaccedu.2024.100933 This teaching case, built from a real-world workbook, presents a framework for designing a workbook that supports a business decision: one that can be used repeatedly, reviewed by a manager, structured to minimize errors, and used interactively. It extends the organization and documentation practices of this chapter to a complete decision workbook.

4.18 References

Caulkins, J. P., Morrison, E. L., & Weidemann, T. (2007). Spreadsheet errors and decision making: Evidence from field interviews. Journal of Organizational and End User Computing, 19(3), 1–23. https://doi.org/10.4018/joeuc.2007070101

Church, K. S., Riley, J., & Schmidt, P. J. (2022). Has Excel become a “golden hammer”: The paradox of data analytics in SME clusters. Journal of Emerging Technologies in Accounting, 19(2), 211–234. https://doi.org/10.2308/jeta-2020-051

Janvrin, D., & Morrison, J. (2000). Using a structured design approach to reduce risks in end user spreadsheet development. Information & Management, 37(1), 1–12. https://doi.org/10.1016/S0378-7206(99)00029-4

Panko, R. R. (1998). What we know about spreadsheet errors. Journal of Organizational and End User Computing, 10(2), 15–21. https://doi.org/10.4018/joeuc.1998040102

Powell, S. G., Baker, K. R., & Lawson, B. (2008). A critical review of the literature on spreadsheet errors. Decision Support Systems, 46(1), 128–138. https://doi.org/10.1016/j.dss.2008.06.001

Powell, S. G., Baker, K. R., & Lawson, B. (2009a). Errors in operational spreadsheets. Journal of Organizational and End User Computing, 21(3), 24–36. https://doi.org/10.4018/joeuc.2009070102

Powell, S. G., Baker, K. R., & Lawson, B. (2009b). Impact of errors in operational spreadsheets. Decision Support Systems, 47(2), 126–132. https://doi.org/10.1016/j.dss.2009.02.002

Public Company Accounting Oversight Board. (n.d.). AS 1215: Audit documentation. https://pcaobus.org/oversight/standards/auditing-standards/details/AS1215