Excel Quiz 61

You want to lock a worksheet to prevent users from altering formulas, but you still want them to be able to enter data into a specific input range (e.g., B2:B10).

What must you do before applying the "Protect Sheet" command?

You have a single column containing full names (e.g., "Smith, John"). In the adjacent blank column, you manually type "John" and press Enter. You want Excel to automatically extract the first names for the rest of the list based on the pattern you just provided.

Which feature accomplishes this instantly?

Your PivotTable contains several empty cells where no data exists, and they are currently displaying as blank.

Where do you configure the PivotTable to display a "0" in these empty cells instead?

You have a dataset where 12 months of sales data are spread out horizontally across 12 separate columns. You need to transform this so all months are listed vertically in a single column.

Which Power Query feature performs this transformation?

You have inserted three different PivotTables on a dashboard, all built from the exact same underlying source data. You insert a Region Slicer and want it to filter all three PivotTables simultaneously.

How do you link the Slicer to the other PivotTables?

While cleaning data in the Power Query Editor, you want to quickly see a visual green/red/grey bar indicating the percentage of Valid, Error, and Empty values directly beneath every column header.

Which Data Profiling option must you check in the View tab?

You know the exact target net profit you want to achieve for the next quarter. You need Excel to automatically adjust the "Units Sold" input cell until the profit calculation reaches your specific target.

Which What-If Analysis tool is designed for this exact scenario?

You are creating a relationship between a Sales table and a Products lookup table in the Excel Data Model (Power Pivot).

What is a strict requirement for the Product ID column in the lookup table to establish a valid relationship?

You have three separate tables containing regional sales data (North, South, East). All three tables share the exact same column headers.

Which Power Query operation combines them by stacking the rows on top of each other into one master table?

You are creating a combo chart that plots Total Revenue (in millions) and Profit Margin (in percentages).

Why is it necessary to assign the Profit Margin series to a Secondary Axis?