Selecting data for charts in Excel - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of a dynamic chart data range formula

Given the formula =OFFSET(A1,0,0,COUNTA(A:A),1) used as the data range for a chart, what is the height (number of rows) of the range if column A has values in cells A1 through A10?

A 11

B 9

C 10

D 0

💡 Hint

COUNTA counts all non-empty cells in the column.

The formula COUNTA(A:A) counts all non-empty cells in column A. Since cells A1 to A10 have values, it returns 10. The OFFSET starts at A1 and extends down 10 rows, so the height is 10.

❓ Function Choice

intermediate

Choosing the correct function to select chart data

Which Excel function is best to create a dynamic named range that automatically adjusts as you add more data in column B for a chart?

A INDEX

B SUM

C COUNTA

D VLOOKUP

💡 Hint

Think about a function that can return a reference to a cell or range.

The INDEX function can return a reference to a cell or range, which is useful for defining dynamic ranges for charts. COUNTA counts cells but does not return a range. VLOOKUP and SUM are not used for defining ranges.

🎯 Scenario

advanced

Selecting non-adjacent data for a chart

You want to create a chart using data from cells A1:A5 and C1:C5. Which method correctly selects these two non-adjacent ranges for the chart data source?

A Select A1:C5 and insert the chart

B Select A1:A5, then hold Ctrl and select C1:C5 before inserting the chart

C Select A1:A5 only and insert the chart, then manually add C1:C5 later

D Select C1:C5 only and insert the chart

💡 Hint

Think about how to select multiple ranges at once.

To select non-adjacent ranges, hold the Ctrl key while selecting each range. This allows the chart to use both ranges as data sources.

📊 Formula Result

advanced

Result of INDIRECT function for chart data

If cell D1 contains the text Sheet1!$A$1:$A$10, what does the formula =SUM(INDIRECT(D1)) return assuming cells A1 to A10 in Sheet1 contain numbers 1 through 10?

A #REF! error

B 10

C 1

D 55

💡 Hint

INDIRECT converts text to a range reference.

The INDIRECT function converts the text in D1 to a range reference. SUM then adds the numbers 1 through 10, resulting in 55.

❓ data_analysis

expert

Analyzing chart data range after row insertion

You have a chart based on the range =Sheet1!$A$1:$B$5. If you insert a new row at row 3, what will be the new data range of the chart?

A Sheet1!$A$1:$B$6

B Sheet1!$A$1:$B$5

C Sheet1!$A$1:$B$4

D Sheet1!$A$2:$B$6

💡 Hint

Inserting rows shifts ranges down and expands them if relative references are used.

When you insert a row inside a chart's data range, Excel automatically adjusts the range to include the new row, expanding it from 5 rows to 6 rows.