Excel Skill Test Preview

Which of the following best describes the PowerQuery tool?

Which of the following objects can you filter using slicers?

What are the differences between IF and SUMIFS functions?

Select all the features that apply to PivotTables:

Using the Excel file you can download here,  answer the following question.

You wish to add a FILTER formula that will dynamically show all table rows with a date specified in cell M3.

Write the formula using table reference.

Match the wildcard symbol with the corresponding search values (we use the # symbol to represent the needed symbol).

Download the following Excel workbook and use it to answer the question.

We want to add condition in D column stating that if SalesValue€ is less than 500.000€ (cell H3 value), then we apply 15% discount (cell I3 value). Else we apply 27% discount (cell I4 value).

Which formula should be entered into cell D4? The formula should show the correct result when copied across the whole D column. Use absolute references where necessary.

In which situations could you benefit from lookup functions (VLOOKUP, XLOOKUP, INDEX, etc.)?

What are the advantages of using modern Array formulas?

Is this an optimal data source structure for importing in a PivotTable?

Select all advantages when using XLOOKUP function compared to VLOOKUP:

What are PivotChart benefits when compared to a regular chart?

Which formula/s will return the correct TRUE/FALSE if cells in column B equal cells in column C?

When importing data in the PowerPivot model, how many rows is the maximum import limit?

Combine tasks with appropriate function:

Match the correct statement with the correct Excel tool

What are the differences between PivotTable created from the data model (PowerPivot), and regular Pivot created from table/range? Select all that apply.

When considering data cleaning operations, what are PowerQuery’s advantages compared to VBA?

If there is no previous formatting applied, which rows have a text value entered in a cell? (Multiple choices)

If there is no previous formatting applied, which rows have a text value entered in a cell? (Multiple choices)

If there is no previous formatting applied, which rows have a correct date entered in a cell? (Multiple choices)

How does Excel interpret this entry?

You need to add a VAT (Value added tax) to each net amount.

Which is the preferred way of entering the 15% VAT.

You need to change the table in the picture below into a better modeling structure.

The optimal data structure should look like in the table below.

Which Power Query transformation do we need to run over the initial table to transform it into an optimal structure?

We want to create a multiplication table in Excel. Which formula entered in cell AA3 will yield a correct answer when copied across the whole range (AA3:AJ12)?

You enter a formula as in the picture below:

What would happen if we copy cell F4 (the one with the formula), and paste it into cell B2?

Is this a propper Excel table?

What are the main benefits of using real tables as compared to table-like structures? (Multiple choices)

We wish to sum all the SalesQTY in a table Sales (picture below). We want the solution to be dynamic and to account for any newly added rows to the table (currently 21.721 rows in a table).

Sort formulas from best to worst in terms of performance and flexibility (possibility to account for newly added rows):

Which statement about PivotTables is correct?