Excel 01: Foundations - Data Analyst Mastery

Comprehensive practice flashcards covering Level 4 Data Analyst foundational Excel concepts including cell referencing, formulas, logic, lookup functions, PivotTables, and Power Query.

What does the Excel cell reference B3 identify?

The cell in column B, row 3.

Which character must start an Excel formula?

An equals sign: =

A formula =B2*C2 is copied down one row. What does it become?

=B3*C3

Which reference fixes both the column and row of cell B2?

$B$2

In the reference $B2, what stays fixed when copied?

Column B; the row can change.

In the reference B$2, what stays fixed when copied?

Row 2; the column can change.

What is the keyboard shortcut to turn a selected data range into an Excel Table?

Ctrl+T (Windows).

Why use an Excel Table rather than an ordinary range for regularly updated meter data?

Tables expand as rows are added and support structured references, filters and automatic formula filling.

What is the difference between filtering and deleting rows?

Filtering temporarily hides nonmatching rows; deleting removes data.

Write a formula that totals energy consumption in cells C2:C100.

=SUM(C2:C100)

Write a formula that calculates average consumption in C2:C100.

=AVERAGE(C2:C100)

Write a formula to find the highest consumption in C2:C100.

=MAX(C2:C100)

What is the difference between COUNT and COUNTA?

COUNT counts numeric cells (including dates); COUNTA counts nonempty cells.

Write a formula to count blank cells in B2:B100.

=COUNTBLANK(B2:B100)

Write a formula to round the value in C2 to two decimal places.

=ROUND(C2,2)

Write an IF formula that returns 'High' when C2 exceeds 1000, otherwise 'Normal'.

=IF(C2>1000,"High","Normal")

Write a formula returning 0 if dividing C2 by D2 causes an error.

=IFERROR(C2/D2,0)

Write a formula to count how many cells in A2:A100 contain 'St Ives'.

=COUNTIF(A2:A100,"St Ives")

With building in A and kWh in C, sum the kWh for 'St Ives' across rows 2:100.

=SUMIF(A2:A100,"St Ives",C2:C100)

With building in A, year in B, and kWh in C, sum St Ives kWh for 2025 (rows 2:100).

=SUMIFS(C2:C100,A2:A100,"St Ives",B2:B100,2025)

With building in A and year in B, count St Ives rows from 2025 (rows 2:100).

=COUNTIFS(A2:A100,"St Ives",B2:B100,2025)

What does SUBTOTAL(109,C2:C100) do in a filtered list?

Sums the visible values in C2:C100, ignoring filtered-out and manually hidden rows.

Use XLOOKUP to find the building in E2 within A2:A100 and return its kWh from C2:C100; show 'Not found' if missing.

=XLOOKUP(E2,A2:A100,C2:C100,"Not found")

What is one advantage of XLOOKUP over VLOOKUP?

XLOOKUP can return values to the left or right without relying on a fixed column number.

What does #N/A usually mean in a lookup formula?

The lookup value was not found in the lookup range.

Why can wrapping every formula in IFERROR be risky?

It can hide genuine data or formula problems that need investigation.

Write a formula that returns 'Check' if C2 > 1000 AND D2 > 500, otherwise 'OK'.

=IF(AND(C2>1000,D2>500),"Check","OK")

What does =TODAY() return?

The current date, updated when Excel recalculates.

Write a formula that extracts the year from a date in B2.

=YEAR(B2)

Write a formula that removes leading, trailing and repeated ordinary spaces from A2.

=TRIM(A2)

Write a formula that converts text '1250' in A2 into a numeric value.

=VALUE(A2)

Write a formula that counts the number of characters in A2.

=LEN(A2)

Write a formula that returns the first three characters of text in A2.

=LEFT(A2,3)

Write a formula that lists distinct building names from A2:A100 (Microsoft 365 Excel).

=UNIQUE(A2:A100)

Write a formula that returns rows A2:C100 where column C exceeds 1000 (Microsoft 365 Excel).

=FILTER(A2:C100,C2:C100>1000)

What is the purpose of Data Validation in Excel?

To restrict or guide allowed entries, such as using a dropdown list of building names.

Why should you check for duplicate meter readings before calculating totals?

Duplicate rows can inflate consumption totals and distort comparisons.

What is a structured reference for summing the kWh column in an Excel Table named MeterData?

=SUM(MeterData[kWh])

In a PivotTable, where would you place Building and kWh to show total consumption by building?

Building in Rows; kWh in Values, summarised by Sum.

Why might a PivotTable display Count of kWh instead of Sum of kWh?

Excel may treat some source values as text or other nonnumeric data; check and clean the column.

What must you do after changing the source data of an existing PivotTable?

Refresh the PivotTable (and check its source range if it is not based on an expanding Table).

What does a PivotTable slicer let you do?

Filter a PivotTable interactively using clickable categories.

How would you compare annual consumption by building in a PivotTable?

Place Building in Rows, Year in Columns and kWh in Values as Sum.

Which chart type is usually best for showing monthly electricity use over time?

A line chart, with months on the horizontal axis.

In Excel, what is Power Query primarily used for?

Importing, cleaning, transforming and combining data through repeatable steps.

In Power Query, what is the difference between Append and Merge?

Append stacks rows from tables; Merge joins matching rows using key columns.

A file has Jan, Feb and Mar in separate columns. Which Power Query operation turns them into Month and Value rows?

Unpivot Columns.

In Power Query, how could you calculate total kWh per building?

Group By Building and aggregate the kWh column using Sum.

What is the benefit of refreshing a Power Query connection when a new monthly meter file arrives?

It reruns the saved cleaning and transformation steps instead of repeating them manually.

A building used 1200 kWh last year and 1500 kWh this year. What is the percentage increase?

$25\%$; calculate $\frac{1500-1200}{1200} \times 100$.