Excel assessment study guide

Workbook essentials

Read the cell address before editing. Select the full target range before applying a format, and keep the underlying value separate from the way it is displayed.

1. Find the named worksheet and target cell or range. A1 is column A, row 1; A1:C4 includes every cell in that rectangle.
2. Make the requested change, then inspect both the displayed value and the formula bar. A currency or percentage format changes the display.
3. Review every part of the instruction. A task may ask for a number format, a renamed sheet and a frozen header row together.

Before you submit: Formatting a value does not change its value. Check the requested range, decimal places and sheet name before submitting.

Formulas & references

Begin a formula with =. Use cell references so the answer updates when data changes. When filling down, relative references move with the row; a $ fixes its row or column.

1. Translate the calculation into steps before typing. Parentheses keep a discount or tax adjustment together.
2. Enter the formula in the first result cell. Check it with a quick calculation using that row’s inputs.
3. Select the first formula cell and the destination cells below it, then use Fill down. Inspect the last formula to check which references moved.

Before you submit: A typed answer can look correct but miss a request for a formula. Lock the shared rate, not the inputs that need to change on each row.

Lookups

Identify the lookup key, the column containing that key, and the value to return. Use exact matching for product codes or employee IDs, where a nearby result is not acceptable.

1. Check that the lookup value and the reference keys use the same kind of data. Extra spaces or inconsistent codes can prevent an exact match.
2. Select matching lookup and return ranges, excluding headers. For VLOOKUP, count the return column from the left edge of the selected table.
3. Verify one known record before filling down. A missing match needs investigation; it is not the same as a price of zero.

Check the Excel version named in your invitation. Microsoft states that XLOOKUP is unavailable in Excel 2016 and Excel 2019. XLOOKUP function guidance

Before you submit: The VLOOKUP key must be in the first column of its table. Match the return range to the lookup range and fix both when filling down.

Data cleaning

Inspect the original text before choosing a function. Remove unwanted spaces, standardize case where requested, and extract characters only when the record has a consistent structure.

1. Identify the specific problem: extra ordinary spaces, inconsistent case or an unwanted prefix. Choose a function that addresses that problem.
2. Put the cleaning formula in the requested output column, keeping the source available for comparison.
3. Fill down and inspect records with different lengths or spacing. Check that useful characters, such as leading zeros in codes, remain intact.

Before you submit: Text that looks like a number may still be text. Preserve source data unless the task asks you to replace it, and check the output in more than one row.

Sort & filter

Select the complete table so every field stays with its record. Tell the sort or filter control whether the first row contains headers, then choose the relevant column and condition.

1. Select the full table, including every column belonging to a record. Mark whether the range includes a header row.
2. Choose the sort column and direction. Use the relevant text or numeric filter condition to narrow the visible rows.
3. Inspect an entire record after sorting. After filtering, check which rows remain visible and clear the filter when you need all records again.

Before you submit: Sorting only the amount column separates amounts from the people or orders they belong to. Check the full range and header setting.

Pivot tables

Start with a rectangular table and a header for each column. Choose what each row should represent, which value to summarize, and whether the task asks for a sum, count or average.

1. Inspect the source headers and exclude any existing total row. Choose an empty destination with space for the summary.
2. Set the row field, optional column field, value field and aggregation. Add a filter only when the instruction requests a subset.
3. Check one category by adding or counting its source records. Confirm the result uses the requested summary, not merely the right labels.

For the corresponding workflow in Microsoft Excel, see Microsoft’s guide to creating a PivotTable.

Before you submit: Count of Revenue counts records; Sum of Revenue adds their values. Check the requested aggregation and any filter before creating the summary.

Charts

Select the category labels and the numeric series together. A column or bar chart compares categories; a line chart emphasizes change across an ordered sequence; a pie chart shows parts of one whole.

1. Choose the category labels and the values to plot. Include the headers when using them as series names.
2. Select the requested chart type and add a descriptive title. Keep time periods in chronological order for a trend.
3. Read the finished chart against the source. Check for missing categories, an extra total row or an unintended numeric series.

Before you submit: Exclude totals that would double-count the same values. A title does not repair an incorrect data range; check labels and series as well as the chart type.

What the practice workbook supports

The workbook runs in your browser without an Excel installation. It provides the tools needed for these original tasks. Use a desktop or laptop so you have room to read the worksheet and its controls.

Cells, formulas and formatting

Edit cells, select ranges, enter formulas and fill down with relative, absolute or mixed references. Apply number, currency, percentage and date formats, decimal places, bold, italic, underline, alignment, wrap, borders, fill color and font color.

Data and summaries

Sort and filter tables. Create column, bar, line or pie charts. Build a pivot summary with a row field, an optional column field, a value summarized by sum, count or average, and an optional filter.

Worksheet setup

Switch between the task’s worksheets, rename a sheet, freeze rows or columns and set a print area. Each task is independent; changes do not carry into the next workbook.

This is a focused practice workbook, with a limited set of formulas and controls. It does not import or export Excel files, run macros or VBA, use Power Query, or reproduce every Microsoft Excel feature. A print-area task checks the chosen range; it does not print or export a file.