Everything the adaptive question bank can teach and test in Excel & Spreadsheets, from foundations through advanced practice. Work through it in order, or start practicing and let the questions find your level.
Each level owns different things, which is why a name can be unique on one sheet and collide at workbook level. Knowing which level a setting belongs to predicts what survives when a sheet is copied into another workbook.
A whole-column reference is convenient and costs calculation across every row, which matters once formulas are numerous. It also silently includes anything added below the data, which is a feature for growth and a hazard for stray notes.
The five behave differently in almost every function, so a formula that works on one type can fail on another without being edited. Blank is the one that surprises: it is not zero, not empty text, and each counting function treats it differently.
Silently is the problem: nothing errors, the total is simply too low, and the cells look identical to real numbers. Left alignment by default is the visible tell, and a fast check is whether COUNT and COUNTA disagree over the same range.
A formula returning empty text produces a cell that looks blank and is not, so ISBLANK reports FALSE and COUNTA counts it. That one distinction explains most surprises where a total or a lookup includes rows that appear to hold nothing.
The sheet name is part of the reference, so renaming a sheet rewrites every formula pointing at it — which is why renaming is safe and deleting is not. A sheet name containing a space needs quotation marks around it.
The link stores a file path, so moving or renaming the source breaks it, and the symptom is a stale value rather than an error until a refresh is attempted. Values persist from the last successful read, which is what hides the staleness.
B. Formula fundamentals
The leading sign is what separates a formula from text, which is why one typed into a cell preformatted as Text simply sits there as a string. The argument separator is locale-dependent, so a formula copied between regions may not parse.
Negation binds tighter than exponentiation here, which is unusual and yields a positive result where most languages give a negative one. Parentheses settle it, and are worth adding wherever a reader would have to recall the table.
They return real logical values rather than the words, which is why the result can be multiplied or added directly. A comparison against an empty cell treats it as zero, so a blank tests as equal to zero rather than as missing.
Coercion runs one way here: numbers become text and the result can no longer be summed, which is how a tidy report column becomes unusable downstream. Joining a date this way loses the formatting and exposes the serial number underneath.
Relative references move with the fill and absolute ones do not, which is the whole content of the next chapter. Formatting travels too, while anything typed as a constant inside the formula stays exactly where it was written.
In manual mode the displayed values are whatever was last computed, so a workbook can show a consistent and wrong answer indefinitely. The mode is saved with the file, so it travels to whoever opens it next.
The warning names the first cell in the loop rather than the cause, so tracing precedents from there is how the loop gets found. Enabling iteration converts a definite error into an approximate answer, which is occasionally intended and usually not.
The rule is that arithmetic coerces where it can and comparison does not, so text digits add correctly and still fail an equality test against a number. That asymmetry is why a lookup can miss on values that look identical.
C. Cell references — relative, absolute, mixed
The reference is stored as an offset rather than as an address, which is why it changes on copy and not on move. Cutting and pasting a formula therefore preserves its references while copying rewrites every one of them.
Pinning both coordinates makes a reference survive any copy, which is what a lookup table or a single rate cell needs. Over-pinning is the commoner error, since a formula that cannot travel is one that has to be retyped for every row.
Pinning one axis is what lets a single formula fill an entire grid, such as a multiplication table or a sensitivity analysis. Choosing which axis to pin means asking which one should stay still as the formula travels.
Choose the anchor from where the formula is going rather than from where it sits: filled down it needs its row pinned, filled across its column. Writing it and testing the second copy is faster than reasoning the whole grid out first.
The space operator is the forgotten one, returning the overlap of two ranges, so an accidental space between references is a silent change of meaning rather than an error. Union with a comma is what lets non-adjacent blocks be summed together.
Inserting rewrites references so formulas keep pointing at the same data, which is why insertion is safe. Deleting removes the target entirely and leaves a reference error behind, which is the usual origin of that particular error.
Because the reference is assembled from text, the dependency tree cannot see it: the formula does not recalculate when its target changes, and moving the target does not update the text. It is powerful and it opts out of the safety net.
Volatile means recalculating on every change anywhere in the workbook, so a handful can make a large file feel slow for reasons invisible in any single formula. Two on this list are volatile precisely because they take a reference as text.
D. Names, constants and readable formulas
The gain is that the formula states the business rule rather than a coordinate, so a reader can check the logic without navigating to the cells. A moved range also stays correct, since the name follows the range rather than the address.
A sheet-scoped name shadows a workbook-scoped one of the same name on that sheet, so identical formula text can mean two different things in one file. That is the reason an apparently identical formula gives different answers on different tabs.
Having no cell behind it means the value cannot be seen or edited on the grid, which is an advantage for a true constant and a liability for anything a user may need to change. Choose it when nobody should be adjusting the number.
The collision rule is the one that bites: a name that looks like a cell address is rejected, which rules out many short and natural choices. Case-insensitivity means two names differing only in capitalization are the same name.
The point is that charts, validation lists and formulas built on it pick up new rows without being edited. A table does the same job more simply, so this technique is mostly for cases where a table is unavailable or unwanted.
Names outlive the ranges they pointed at, so a deleted sheet leaves names resolving to an error that then propagates into every formula using them. They are invisible on the grid, which is why they accumulate unnoticed for years.
E. Errors and formula debugging
A blank divisor counts as zero, which is why this appears in a half-filled template that is otherwise correct. It is reporting that the divisor is missing rather than that the formula is wrong, so the fix usually belongs to the data.
It means the formula asked for a type it did not get, so the cause is almost always in the inputs rather than in the formula. Text that looks numeric is the usual culprit, which is why the grid chapter covers that first.
It is unrecoverable by design: the reference is gone, so the formula cannot be repaired by changing what is displayed. Undoing the deletion immediately is the only cheap fix, which is why noticing it at once matters so much.
Three different causes produce one error: a misspelled function, a name that no longer exists, and text that was never quoted. Asking whether the flagged word should have been in quotation marks catches the last one quickly.
It is an answer rather than a fault, which is why suppressing it indiscriminately is a mistake: it reports that the key was not found, and that is usually information about the data. The next entry is about suppressing it precisely.
It means the arithmetic is impossible or did not converge, so it points at the values rather than at the syntax. A root of a negative number, and an iterative function given an unreachable target, are the two common shapes.
The formula is correct and the destination is occupied, which is why the fix belongs on the grid rather than in the formula. Merged cells are the least obvious blocker, because they look empty while occupying several cells.
The broad one hides genuine mistakes alongside the expected miss, so a typo in the formula quietly returns the fallback and the error never surfaces. Prefer the narrow one wherever the only expected failure is a lookup that finds nothing.
It answers where a number came from rather than whether it is right, which makes it the tool for an unexpected value rather than for an error message. Following the arrows one level at a time is what isolates where a wrong input entered.
Stepping through shows the intermediate values a nested formula never displays, so it identifies the specific argument that is wrong instead of condemning the whole expression. It is the right tool when the formula is long and the fault is not obvious.
F. Math and aggregation functions
What it skips is the useful part: text, logical values and empty cells are ignored rather than treated as zero, so a total can be right and still omit rows. That silence is what makes numbers stored as text so hard to notice.
They report one member of the range rather than describing the whole of it, so neither says how many rows reached that value or how typical it is. Both ignore text and blanks, which is why an empty range returns zero rather than an error.
Removing the sign is what makes it the tool for a size comparison, such as finding the largest variance whether the miss was over or under. Wrapping a difference in it also prevents positive and negative errors from canceling in a total.
Three counting functions give three different answers over one range, and picking the wrong one is a common silent error. This one also counts a cell holding empty text as blank, which the ISBLANK test does not.
Multiplying then adding in one step is what lets it do conditional work without an array entry, which is why it survived long after dedicated functions arrived. It also handles criteria that the conditional family still cannot express.
The digits argument accepts a negative value, which rounds to tens, hundreds and beyond — the part most people never learn. Rounding up and down here mean away from and toward zero, which differs from flooring on negative numbers.
On positive numbers the two agree, which is why the difference stays hidden until a negative appears and the answers separate by one. Choosing between them is a decision about negatives, and nothing else.
The result takes the sign of the divisor rather than of the dividend, which is the behavior that surprises anyone arriving from another language. It is the standard tool for cycling, alternating and every-nth-row work.
Rounding to an arbitrary multiple is what separates these from the round family, which only works in powers of ten. That makes them the right tools for pack sizes, price points and time slots rather than for tidy decimals.
The function numbers encode the behavior, and the second half of the range is what ignores manually hidden rows rather than only filtered ones. The newer function adds error skipping, which is what lets a total survive a broken row.
Being volatile, they change on every recalculation, so a sample cannot be reasoned about while it keeps moving. Copying the results and pasting them as values is what freezes a draw for analysis.
G. Logical functions and decision formulas
Omitting the third argument returns FALSE rather than a blank, which is rarely what was wanted and is easy to miss on screen. Supplying empty text instead looks blank and brings the empty-string caveat from the grid chapter with it.
Evaluation stops at the first true condition, so an unsorted band list silently returns the wrong tier rather than failing. Sorting the boundaries consistently in one direction is what makes the chain readable and correct.
Flattening the nest removes the bracket counting and keeps the first-match rule, so ordering still matters exactly as much. The gap is that no default exists unless one is written, and an unmatched row returns an error rather than a blank.
Chained comparison does not work: a double inequality is evaluated left to right and the result is compared against the next value, which is almost never what was meant. Two explicit tests combined are the only correct form.
It matches exact values rather than evaluating conditions, so ranges and inequalities are outside its scope. Within that limit it reads far more clearly than a chain of equality tests, and the default argument covers the unmatched case.
Because logical values coerce to one and zero, a comparison can be summed to count matches or multiplied to combine conditions. That is the mechanism behind most array-style conditional work, including the entries in the arrays chapter.
A table moves the logic out of the formula and onto the grid, where it can be read, audited and changed without editing anything. The threshold for switching is usually about three branches, past which the nest stops being reviewable.
H. Conditional aggregation
The criterion is supplied as text, which is why operators have to live inside the quotation marks and why a cell reference needs joining rather than embedding. That argument's syntax is the subject of its own entry below.
Every criterion must hold at once, so asking for two different values in one column returns zero rather than a union. Expressing an OR means adding two results together, or restructuring the question.
The older and newer functions take their arguments in opposite orders, which is a genuine inconsistency rather than a memory failure. Checking the order before writing either one is faster than debugging a total that is subtly wrong.
Every range involved has to be the same shape, and mismatched heights produce an error rather than a partial answer. That constraint is also a safeguard: it catches a range that failed to grow with the data.
The difference is whether the cell reference is evaluated or taken literally: joined to the operator it becomes the cell's value, and inside the quotes it stays the letters themselves. This one distinction causes most silent zero results.
They work in criteria and in some text functions but not in ordinary comparisons, which is why an apparently equivalent formula behaves differently. Escaping matters when the data itself contains one of the wildcard characters.
They complete the conditional family, giving extremes the same treatment counting and summing already had. Before they existed the same result needed an array formula, which is why older workbooks still contain those constructions.
One counts numbers and the other counts anything present, so the gap between them measures how many entries are non-numeric. Comparing the two over a column is the quickest probe for values stored as text.
I. Lookup and reference
The range-lookup flag defaults to approximate when omitted, which is the single most consequential default in the function — an unsorted table then returns a plausible wrong answer rather than an error. State it every time.
It is the same function rotated, and it is rare because data is usually stored with fields in columns. Meeting one often signals a layout that should be transposed rather than worked around.
The column index is a number rather than a reference, so inserting a column shifts the data and leaves the index pointing somewhere else without any error. That fragility is the argument for the composable pair two entries down.
Approximate matching requires a sorted first column and returns the largest value not exceeding the key, which is exactly right for bands and disastrous for identifiers. Choosing it deliberately is the difference between the two use cases.
It takes positions rather than values, which is why it is useless alone and powerful paired with something that finds a position. It can also return an entire row or column when one coordinate is left as zero.
Match type works like the lookup flag elsewhere, and it too defaults to approximate, requiring sorted data. Supplying zero for an exact match is the habit worth forming, for the same reason as in the lookup functions.
Splitting finding from returning is what removes the fixed column index, so inserting a column cannot break it. The lookup and return ranges are also independent, which is how it searches to the left.
Its arguments are ranges rather than a table and an offset, which removes the counting error entirely. The built-in if-not-found argument also means the common wrapper is no longer needed, and it defaults to an exact match.
Search order is what lets it return the last match rather than the first, which the older functions could not do at all. The match modes cover wildcards and next-larger, making a separate banded approach unnecessary.
The table holds the lower bound of each band, sorted ascending, and the approximate match walks down to the right one. Understanding it as walking rather than as searching is what makes the sort requirement obvious.
The three causes look identical on screen: an invisible trailing space, a number stored as text on one side only, and a range that did not extend far enough. Checking length and type before rewriting the formula saves most of the time.
A lookup cannot report that it found several, so it returns the first and the ambiguity disappears silently. Counting the keys before relying on a lookup is what turns an invisible problem into a visible number.
J. Text functions
They work on fixed positions, so they only suit data with a reliable layout, and anything variable needs a found position instead. Combining them with a search is the standard way to handle a delimiter that moves.
Comparing a length against what it should be finds trailing spaces, non-printing characters and truncated identifiers, none of which are visible. It is the fastest first probe when two values look equal and do not match.
Two differences travel together: one is case-sensitive and rejects wildcards, the other is neither. Choosing by case sensitivity alone is how a wildcard unexpectedly starts or stops working.
One works by content and the other by position, so they suit different problems entirely and are not alternatives. The instance-number argument is what lets only the second or third occurrence be changed.
Trimming removes leading, trailing and repeated internal spaces but not every invisible character, which is why the pair exists. Neither touches a non-breaking space, the character most often pasted in from a web page.
It converts a number into text formatted a chosen way, which is exactly what a display needs and exactly what breaks a later calculation. Use it at the point of presentation rather than anywhere upstream of arithmetic.
The delimiter is applied between elements rather than after each, and the ignore-empty flag decides whether gaps produce doubled separators. Those two behaviors are what the older concatenation approach had to handle by hand.
Splitting inside a formula keeps the source intact and updates when it changes, which the menu-driven equivalent does not. The result spills, so it brings the whole spill chapter's constraints with it.
Converting back is the repair for text digits, and the second form matters when the separators do not match the current locale. Both fail on stray characters, which is why cleaning usually has to come first.
K. Dates and times
Because a date is a number, arithmetic on it works and a formatting change can make it look like something else entirely. It is also why a date and a duration are indistinguishable to the grid and are told apart only by their format.
One carries a time component and the other does not, so a difference between them can be fractional where a whole number was expected. Both are volatile, which means a saved workbook shows a different value every time it opens.
Rollover is the useful behavior: a month of thirteen becomes January of the next year, and a day of zero becomes the last day of the previous month. That is what makes end-of-month arithmetic possible without a special case.
Each returns a number rather than a name, so producing a month name needs a formatting step. Extracting parts also discards everything else, which is why reassembling them is how a date gets truncated to a month or a year.
Adding days works because dates are numbers; adding a month does not, because months differ in length. Rebuilding the date with its parts, incrementing the month, is the reliable way, and it handles the end-of-month case for free.
Whole-unit counting and fractional counting answer different questions, and an age is almost always the first. The fractional version also depends on a day-count basis argument, which changes the answer more than most people expect.
Weekends are assumed unless a variant is used, so a business operating other days needs the version that takes a weekend code. The holiday list is a range you supply, meaning the function is only as current as that range.
It projects forward or backward by working days rather than calendar days, which is what a due date actually needs. It shares the weekend and holiday arguments with the counting function, so the two stay consistent when used together.
Past twenty-four hours a duration wraps, because time is the fractional part of a day and the whole numbers roll into dates. Bracketing the hours in the format code is what stops the wrap and shows a true total.
The symptom is left alignment and a failure to sort chronologically, and date functions returning an error on them. Repair depends on the original format, which is why an import setting is usually a better fix than a formula.
L. Number formats and display
The stored value is what every formula uses, so two cells displaying the same thing can compute differently. That single fact underlies the rounding entry below and most disputes about a total that is off by a small amount.
Displayed values are rounded for the eye and summed at full precision, so a visible column can fail to add up. Rounding in the formula is what makes the displayed and computed numbers agree, and it is a decision about where precision should stop.
The sections are separated by semicolons and applied by sign, so supplying fewer than four changes which cases are covered. Two sections mean positive and negative only, with zero falling into the first.
The display is multiplied while the stored value is untouched, so a cell holding a small decimal shows as a percentage and still computes as the decimal. Typing a whole number into a percent-formatted cell is where the confusion usually starts.
The same letter means month or minute depending on what surrounds it, which is why a code can produce a nonsensical result without being invalid. Placing it next to an hour or a second code is what resolves the ambiguity.
Formatting as Text before entry means what follows is stored as typed, so a formula becomes a visible string and a number becomes text. Changing the format afterwards does not convert what is already there, which is the part that frustrates.
It is a display symptom rather than an error, and the value is intact underneath. Widening the column fixes the ordinary case; a negative result in a date or time format produces the same symptom and needs a different fix.
Conditions inside a format code apply formatting only, so they cannot change a value or fire an alert. They also come before conditional formatting in precedence, which is why a rule sometimes appears not to take effect.
M. Dynamic arrays and spilling
One formula owning many cells changes the mental model: the extra cells cannot be edited individually and deleting one is not possible. The ghosted border is the visual cue that a region belongs to a single formula.
It refers to the current extent rather than to a fixed range, so anything built on it grows and shrinks automatically. That makes it the natural source for a chart or a validation list that should track a filtered result.
The if-empty argument matters more than it looks: without it, a filter matching nothing returns an error rather than a blank, which then propagates. Supplying empty text there is usually what a report actually wants.
Sorting by a key that is not part of the output is what the second form adds, which covers ranking by a column the reader should not see. Both recalculate, so the order updates as the data changes rather than freezing.
The exactly-once option is a different question from distinct values: it returns items that appear a single time, discarding anything repeated. Confusing the two produces a list that looks plausible and is missing the common entries.
Generating numbers as a formula removes the helper column that used to be needed, and the result spills into a grid of a chosen shape. It is most useful feeding another function rather than displayed on its own.
The operator marks where a formula written before dynamic arrays would have taken a single value from a range, so its presence is a record of a conversion. Removing it usually changes the result, which is why it was inserted rather than dropped.
Anything occupying the destination blocks it, including a cell holding empty text and a merged cell that appears blank. Selecting the formula highlights the intended region, which is the fastest way to see what is in the way.
N. Array formulas and matrix work
Writing an array literally is how a small lookup table lives inside a formula instead of on the grid. The two separators encode shape, so mixing them wrongly changes a row into a column and the result's orientation with it.
The braces are added by the entry method rather than typed, so a formula that shows them was committed differently from an ordinary one. Modern versions mostly do not need them, but an inherited workbook still behaves as it was built.
Multiplying comparisons gives an AND because any false term zeroes the product, and adding them gives an OR. That pair is the whole technique, and it works wherever the logical functions cannot operate element by element.
It still wins where the criteria functions cannot reach: comparing two columns to each other, testing a computed value, or working across a closed workbook. Those are the cases worth remembering it for rather than as a general substitute.
Flipping orientation is straightforward and the consequence is not: everything downstream now expects the other shape, and a formula written for a row fails against a column. Shape mismatches are the commonest error in array work.
Naming an intermediate result means it is computed once rather than each time it appears, which is a real speed gain in a formula that repeats an expensive term. The readability gain is usually the larger one.
It turns a formula into a named function taking parameters, so logic can be defined once and called by name. That moves a workbook toward having its own vocabulary, with the same maintenance questions any codebase has.
A row against a column broadcasts into a grid, which is deliberate and surprising the first time a formula returns far more cells than expected. Checking the two operands' orientations explains almost every unexpected result size.
O. Tables and structured references
Converting changes behavior immediately: the range gains a name, filters appear, and new rows join automatically. It is a structural change rather than a visual one, which is why formatting a range to look like a table achieves nothing.
Inheritance is the feature and also the surprise: a new row silently acquires the formulas, formats and validation of the rows above it. Anything that should not be inherited has to be kept outside the table.
Referring to a column by name makes a formula self-describing and immune to insertion, since the name travels with the column. It also grows with the table, which removes the whole category of ranges that failed to extend.
It means the row the formula sits on, so it only has meaning inside the table. Used outside, it either errors or resolves against whatever row it happens to occupy, which is the usual cause of a confusing result.
It defaults to a filter-aware function rather than a plain sum, so the displayed total reflects what is visible. That is usually what is wanted and is worth knowing, because it explains why the figure changes when a filter is applied.
The default names are sequential and meaningless, so every downstream formula reads worse than it needs to. Renaming is one action and it updates every existing reference, which makes it cheap to do late.
A plain range is still right where the layout is not tidy, where the data will never grow, or where a report needs merged cells and irregular shapes. Forcing a table onto that is friction rather than structure.
Pointing a chart, a pivot or a validation list at a table means the source grows without anything being edited. That is the practical reason to convert, and it removes the most common cause of a report that quietly stops including new rows.
P. Sorting and filtering
Header detection is a guess based on formatting and type, and it is occasionally wrong, which sorts the header into the data. Stating explicitly whether the range has headers removes a failure that is tedious to undo.
Sorting one column alone reorders it while leaving every other column in place, which silently destroys the association between fields. It is the single most damaging routine mistake in a spreadsheet, and it is not obvious afterwards.
Levels apply in sequence, so the second only breaks ties within the first rather than competing with it. Getting the order wrong produces a result that looks sorted and groups the data by the wrong field.
Alphabetical order is wrong for anything with a domain sequence, which is why days, months and size labels need a list of their own. Defining the list once makes it available to sorting and to fill everywhere in the application.
Filters across different columns combine as an AND, so each one narrows what the previous left. That compounding is why a filtered view can end up empty for reasons that are not visible without checking every column's indicator.
The available criteria change with the column's detected type, so a date column offers periods and a text column offers contains. A column holding dates stored as text offers the text options, which is a useful diagnostic in itself.
Its distinctive ability is OR across columns, which the ordinary filter cannot express: separate rows in the criteria range are alternatives. Extraction to another location is the other reason to reach for it.
An ordinary total ignores filtering entirely, so a filtered report can show a total for the whole dataset beside a handful of visible rows. Using a filter-aware function is what makes the total mean what the reader assumes it means.
Q. Data validation
The custom type is the general case and the others are conveniences, so anything the list cannot express can still be written as a formula. Validation constrains typing only, which the bypass entry below makes concrete.
A typed list is fastest and hardest to maintain, since it lives inside the rule where nobody looks. A range or a name keeps the options visible and editable, and a table reference makes the list grow on its own.
The second list is built by resolving a name from the first choice, which is why this technique depends on the names matching the values exactly. A value containing a space cannot be a name, which is the usual thing that breaks it.
The rule is any formula returning true or false, evaluated from the perspective of the active cell, so anchoring behaves exactly as it does in conditional formatting. That shared model is worth learning once for both features.
One guides before entry and the other blocks after, so they address different moments and a rule with neither is silently restrictive. A message explaining what is expected prevents more bad data than a rejection does.
Pasting replaces the cell wholesale, rule and all, so validation is not a guarantee about what a sheet contains. Circling invalid data is the audit that finds what got in, and it is worth running before trusting a collected file.
R. Conditional formatting
The built-in types cover the common cases without a formula, which makes them quick and limited. Anything comparing one cell to another, or depending on a different column, needs the formula type in the next entry.
The formula is written for the top-left cell of the range and then applied relative to it, exactly like filling a formula across a grid. Getting the anchoring wrong there is why a rule highlights the wrong cells in a pattern.
Highlighting a whole row needs the column pinned and the row left free, so the test always looks at the same column while traveling down. That single anchoring choice is the difference between one cell lighting up and the row doing so.
Rules are evaluated in order and can both apply, so a later rule may only change what an earlier one left alone. The stop switch ends evaluation for that cell, which is how a precedence is made explicit rather than accidental.
The bar length encodes the value relative to a range, so the default automatic scaling means two columns are not comparable to each other. Setting the axis explicitly is what makes negative values read correctly.
A gradient shows relative position rather than magnitude, so it answers where a value sits in the spread and not how large it is. That makes it good for spotting pattern and poor for reading a number off.
The default thresholds are percentiles, which means roughly a third of rows get each icon whatever the data looks like. Setting real thresholds is what turns a decorative column into a statement about which rows need attention.
S. PivotTables
The precondition is the tidy shape from the import chapter, and a pivot built on anything else produces a result that is wrong rather than an error. Blank rows in particular truncate the range that gets picked up.
It replaces a column of hand-written conditional totals with a layout, which is why it is faster and also why the logic is no longer visible in a formula. Understanding what it is aggregating is the thing to keep explicit.
Rows and columns decide the grouping, values decide the arithmetic, and filters decide what is included before any of it. Moving one field between areas changes the question being asked rather than just the presentation.
A numeric field defaulting to a count rather than a sum almost always means the column contains text somewhere, so the default is a data-quality signal. Fixing the source beats changing the summarization.
These compute relative figures inside the pivot, so no helper column is needed and the calculation follows the grouping automatically. Knowing which base a percentage uses is essential, since several of the options differ only in that.
Grouping happens in the pivot rather than in the source, so the underlying rows stay intact and the grouping can be changed freely. A date column containing text cannot be grouped, which is a common diagnostic for a messy import.
Slicers show the current selection on the page, which a report filter does not, so a printed pivot can otherwise hide what it excluded. That visibility is the practical reason to prefer them in anything shared.
A pivot reads a stored copy of the source rather than the sheet itself, which is why edits do not appear until a refresh. That cache is also why a file can be much larger than its visible data suggests.
It is robust rather than annoying: it names the fields, so it keeps pointing at the right figure when the layout changes. Replacing it with a plain reference is what breaks a report the next time the pivot is rearranged.
T. Charts and visualization
The question decides the chart: comparison across categories, change over time, relationship between two variables, or composition. A type chosen before the question is what produces a chart nobody can read an answer from.
What gets treated as a series rather than an axis depends on the shape of the selection, so transposing the source changes the chart entirely. Checking that assignment first explains most charts that appear to plot nonsense.
A line chart spaces categories evenly regardless of their values, so plotting irregular dates on one distorts the trend. Where the horizontal axis is genuinely numeric, only a scatter respects the spacing.
Starting an axis above zero magnifies small differences, which is legitimate for a zoomed comparison and misleading when the reader assumes a full scale. The obligation is to make the truncation obvious rather than to avoid it always.
Two independent scales mean the crossing point of the two series is arbitrary, so any apparent relationship can be manufactured by rescaling. That is why it needs a reason beyond making both series visible.
Most defaults add ink without adding information, so deleting is usually the productive edit. A legend for a single series and gridlines behind labeled points are the two most common candidates.
Living in a cell means they sit beside the number rather than replacing it, so a table can carry both level and trend. They have no axis, which is what makes them a shape indicator rather than something to read values from.
Each of these distorts a comparison the reader is making by eye: angles are hard to compare, added dimensions exaggerate, and two scales can be set to imply anything. The test is whether the visual ranking matches the numeric one.
U. Getting data in and cleaning it
A CSV carries values and nothing else — no formulas, formats, types or multiple sheets — which is why a round trip through one silently discards work. Quoting is what lets a value contain the delimiter, and it is where malformed files break.
The zeros are lost because the value is read as a number, and once lost they cannot be recovered by formatting, since the information is gone. Declaring the column as text during import is the only reliable point of intervention.
Conversion happens on entry and on import, so a gene name or a part code can become a date and a long identifier can become scientific notation. Both are lossy, and both look like ordinary values afterwards.
The type step is the one people skip, and it is where a column can be declared text rather than accepting the default guess. Running it on a single column with no delimiter is also the standard trick for forcing a retype.
It infers a pattern from examples and writes static values, so it does not update when the source changes and it fails quietly on rows that do not fit. It is a one-off convenience rather than a transform.
Which columns are ticked defines what counts as a duplicate, so an unticked key column silently widens the match. It deletes in place with no record of what went, which is why a copy of the data beforehand is the norm.
A trailing space is invisible and breaks every exact match, which is why a lookup can fail between two values that look identical on screen. Comparing lengths is the diagnostic, and trimming on import is the cure.
One row per observation is what makes filtering, pivoting and formulas all work without special cases, so the shape is a precondition rather than a preference. Most spreadsheet difficulty traces back to a layout built for reading rather than computing.
V. Power Query (get and transform)
Recording the transform means the cleanup is repeatable and inspectable, which manual editing never is. That changes the unit of work from a cleaned file to a pipeline that can be pointed at next month's file.
Each step being visible and reorderable is what makes the process auditable, so a wrong result can be traced to the step that caused it. It also means a fix is made once rather than repeated on every refresh.
Typing inside the query fixes the value before it reaches the grid, which is where most of the conversion traps in the import chapter happen. It also makes a type failure visible as an error rather than as a silently wrong value.
The trap is cardinality: a key that repeats on the second table multiplies rows rather than adding columns, so the result grows unexpectedly. Checking distinct key counts on both sides before merging is what prevents it.
Stacking assumes the shapes match, and a column named differently in one file becomes a separate column full of nulls rather than an error. Reviewing the column list after an append is what catches the mismatch.
It converts a report layout back into the tidy shape, which is the transformation most often needed on data that was built to be read. Selecting the columns to keep rather than the ones to unpivot survives new columns appearing.
Connection-only keeps a query available without landing it on a sheet, which is what intermediate steps should use. Loading everything to sheets is the usual reason a workbook becomes slow and much larger than its data.
The query stores a path and a credential, so a moved file or a changed password breaks the refresh rather than the query logic. A parameterized path is what makes a pipeline portable between machines.
W. Financial and statistical modeling
The rate and the number of periods have to be in the same unit, so an annual rate with monthly periods must be divided. That conversion is the most common error in the whole financial family, and it produces a plausible wrong figure.
The sign convention treats money paid out and received as opposites, which is why a result arrives negative and looks wrong. Entering the amounts with consistent signs is what makes the answer read the way the question was asked.
These solve the same relationship for a different unknown, so the inputs and the unit rule carry over exactly. The rate version is iterative, which is why it can fail to converge and return an error rather than a number.
The function discounts the first value by one period, so a cash flow occurring today must sit outside it and be added separately. Including an initial outlay inside the range is the standard error and understates the result.
Irregular dates need the dated version, since the ordinary one assumes evenly spaced periods. Where cash flows change sign more than once several rates can satisfy the equation, so a single returned figure may not be the only answer.
The mean is pulled by extreme values, so it misrepresents a skewed distribution that the median describes accurately. Reporting both is what reveals skew, and a large gap between them is itself the finding.
The choice is whether the rows are the whole population or a sample from a larger one, and it changes the divisor. Choosing the sample form by default is usually right, because data in a spreadsheet is rarely the entire population.
Correlation measures linear association only, so a strong curved relationship can report near zero. A fitted trend extended beyond the data assumes the relationship continues, which is an assumption rather than a result.
Goal seek solves backwards for one input, a data table shows a grid of outcomes, and scenarios store named sets of inputs. Choosing between them is a question of how many variables are moving and whether the answers need keeping.
X. Workbook hygiene, protection and collaboration
Every cell is locked by default and nothing is enforced until protection is switched on, which reverses what most people assume. The workflow is therefore to unlock the input cells first and protect the sheet second.
The checkboxes decide what remains possible, so a protected sheet can still allow sorting, filtering or formatting if those are permitted. Protecting without reviewing them is what makes a sheet unusable rather than merely safe.
Structure protection stops sheets being added, deleted, renamed or unhidden, which is a different concern from protecting the cells on them. Confusing the two leaves either the layout or the contents unguarded.
The password prevents accidental edits and is not a confidentiality control, so nothing hidden in a protected sheet is hidden from someone determined. Genuinely sensitive data should not be in the file at all.
An ordinarily hidden sheet can be unhidden from the menu by anyone, while the very-hidden state cannot, which is why an audit should check for both. Neither is a security measure, for the reason the previous entry gives.
Simultaneous editing requires features that conflict with it to be switched off, so a workbook using them cannot be shared that way. Automatic saving also removes the habit of discarding a session's changes by not saving.
Each format drops something: one cannot hold macros, one is binary and faster, and one keeps only values. Choosing is about what has to survive the save rather than about preference, and the default silently strips macros.
Separating the three layers means an input can be changed without touching a formula, and a report can be rebuilt without touching either. Mixing them is what produces a workbook nobody can safely modify.
Y. Navigation, printing and automation
The split is placed relative to the selected cell rather than chosen directly, which is why the result is often one row off. Selecting the first cell that should scroll is the rule that makes it land correctly every time.
They move to the edge of the current block rather than to the end of the sheet, so a stray value stops them early — which makes them a probe for gaps as well as a way to move. Adding the shift key selects on the way.
Selecting only blanks, only formulas or only visible cells turns an audit into one action, and copying visible cells only is what makes a filtered extract behave. It is the fastest way to find the gaps in a column.
Pasting values is how a formula becomes its result permanently, which is the standard way to freeze a calculation. The arithmetic options apply an operation during the paste, which converts a whole block in one step.
Repeating header rows is a print setting rather than a page-layout trick, so it applies to every page without being duplicated in the data. A print area also persists, which is why an updated sheet can print the wrong extent.
Recording produces readable code that can then be edited, which makes it a route into automation rather than a fixed recording. It runs in the web application, so it is not interchangeable with the desktop macro language.
Enabling content runs code with the permissions of whoever opened the file, which is why the prompt exists and why habitually accepting it is the risk. The macro-enabled extension is the visible signal that code may be present.
Keentune is not affiliated with or endorsed by the organizations whose documentation informs these maps.
All exam, test, and product names and trademarks are the property of their respective owners and are used here for identification and reference only. Keentune is independent study practice — not affiliated with, authorized, or endorsed by any of these organizations.