Excel Analytics Course

Which of the following best describes the PowerQuery tool?

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

We want to add a formula in the D column using the following condition: if the category is Cameras and camcorders (cell J3 value), and SalesValue€ is above 5.000.000€ (cell I3 value), then 15% discount is applied (cell K3 value).

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

We want to apply conditional formatting in the table below. The condition is that for any value greater than 5.000.000 (cell H2 value), the whole row of the table needs to turn green.

Sort in the correct order actions you need to use to add this type of conditional formatting to your data range.

Which of the following functions can you use to return multiple matching rows when performing lookup operations?

What are the main features of PowerQuery?

What are the advantages of using modern Array formulas?

Study the screenshot below. Several Conditional Formatting rules have been applied to this worksheet.

How can you ascertain which rules have been applied and where?

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

We want to add condition in D column using conditions set in table in range G3:I4.

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

Which PivotTable feature do we need to use to calculate the average of Unit price?

Can you use modern array functions as a source for data validation list, like in a picture below?

Select all advantages when using XLOOKUP function compared to VLOOKUP:

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

Which PivotTable feature do we need to use to calculate the % share of SalesQTY in a selected Year (the last column in the visual below)?

How can we show a result of an array function in a data validation list?

Is this an optimal data structure to import in a Pivot table?

Which objects exist in PowerPivot data model?

Combine the correct statement with keyword:

Which symbol do we need to use after referencing cell containing array formula to active its #SPILL feature?

How is the red squared cell value calculated?

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

Using the following formula in a cell K7 will yield:

Combine the appropriate Excel table syntax with its usage:

How can you connect multiple tables imported in PowerPivot data model?

What are the benefits of using slicers?

Select all the features that apply to PivotTables:

Can you create a PowerPivot chart without the PivotTable?

Which filters are active in the red cell?

Which of the following objects can you filter using slicers?

You need to create a wild card search based on text in the J column. Which wildcard symbol do you need to use in the VLOOKUP function to find Business Revenue in a list of values in column A?

Is chart a part of Worksheet cells?

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, and category specified in cell M2.

Write the correct formula using table reference (all relative refences).

Using the picture below, match PivotChart fields with ColumnNames:

We have a PowerPivot table plotted on a spreadsheet, like in the picture below.

Where can we check what the Table3 table data structure looks like?

We wish to create a list of unique brands from Table1 sorted in ascending order.

Which function/functions are correct?

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

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

What is DAX (Data Analysis Expressions) being used for?

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.

Observe the picture below:

Source of the selected Pivot chart is:

Can you import multiple different source tables into a single PowerPivot data model?

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

You wish to use the SORT function to sort the whole table by Brand column in descending order.

Which formula should you use?

Can you clear only cell formatting without deleting cell content?

Which type of charts are not supported in PivotChart?

Where do you input PowerPivot DAX formulas?

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

You wish to use the SORT function to sort the whole table first by column Description eng, then by  SalesValue€. Both columns need to be sorted in descending order.

Write the formula using table reference.

Combine tasks with appropriate function:

What are PivotChart benefits when compared to a regular chart?

Import of data in PowerPivot is done through:

What are the differences between IF and SUMIFS functions?

If you need to enter a formula to a single cell on a worksheet, do you need to understand what referencing is (the $ signs)?

Which general type of fields can you can add to a PivotTable?

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

In a proper table, can you have 2 columns with the same name?

Which Loading options should you choose if you wish to load the PowerQuery script only in the PowerPivot data model? (Select all that applies)

If you need to enter a formula in a cell, then copy that formula across a range of cells, do you need to understand what referencing is (the $ signs)?

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.

Can you add a column to the Values field of the PivotTable?

What is the source of PowerPivot table?

How can you fix the width of the PivotTable columns so they do not change as you select different filters?

Match the correct statement with the correct Excel tool

Combine pictures with the correct type of PivotTables:

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

Can you add Measures to Rows or Column fields of the Power PivotTable?

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

Combine Excel operations with keyboard shortcuts:

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)

In Excel, how can we push parameter values inside of a Query?

Your database consists of 2 different tables. Dimension table with a list of countries and its descriptions, and a fact table with the name of the Country and Quantity of products sold for each country.

You want to denormalize dimension descriptions in a fact table (like in a picture below).

You can see that in the merged table there are null values meaning that you have to add those countries with its descriptions to the dimension table. What is the easiest way to filter out values that exist in the fact table, but are non existing in dimension? (Picture below)

If there is no cell formatting applied, match types of entry with their position in a cell (after confirming entry).

How does Excel interpret this entry?

Which keyboard combination can split the text into multiple rows inside of a single cell?

Combine Excel shortcuts with appropriate cell data entry:

Which kind of files does binary type usually hold?

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

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

You created a script in which data source is .csv document exported from your local system. You stored both data sources and PBI file containing a script on a server location that all your colleagues can access and use. After some time colleagues start sending you a screenshot like in a picture below. What happened?

Which symbol is used to lock column and/or row references when writing formulas?

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 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?

Put the applied steps in the correct order to transform the data in the picture below into optimal form.

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

In the input area, write the function syntax you need to enter in cell D4 so that it shows correct results across the whole D4:D1000 range (do not use spaces in formula).

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?

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?

You tried to filter out the Fun Fly product from the table, but it didn’t work (picture below). What happened?

You enter a formula as in the picture below:

What would happen if we cut cell G5 (the one with the value 2.99), and paste it into cell B5?

What does the exclamation mark icon in the picture represent?

When we use Copy/Paste native shortcuts (CTRL+C ==> CTRL+V), what is being pasted into the new range? (Select all options that apply)

For the list of companies in the left table, we need to find its corresponding values from the table to the right.

Which functions (one or more selections are correct) can help us find the correct values for years 2019 and 2020? In the table to the right, there are over 500 unique company names.

For the list of companies in the left table, we need to find its corresponding values from the table to the right.

In the table to the right, there are over 500 unique company names (list ending in row 507).

Using VLOOKUP, which function entered in cell B8 will yield a correct answer when copied across the whole range B8:C22?

For the list of companies in the left table, we need to find its corresponding values from the table to the right. We must make no structural changes to the table to the right.

Which functions (one or more selections are correct) can help us find the correct values for years 2019 and 2020? In the table to the right, there are over 500 unique company names.

We want to give a special discount to all products coming from the Audio category if their sales exceed 2,500,000 € SalesValue.

The formula needs to work when copied across the whole D4:416 range. Which formula is the correct one?

We want to apply conditional formatting in the table below. The condition is that for any value greater than 5.000.000 (cell H2 value), the whole row of the table needs to turn green.

Can we use any of the default circled options from the conditional formatting pane to achieve this or do we need to define a custom rule?

We want to apply conditional formatting in the table below. The condition is that for any value greater than 5.000.000 (cell H2 value), the whole row of the table needs to turn green.

If using a custom formatting rule, which is the correct formula that will format the whole row in green in case it evaluates to true?

Is this a propper Excel table?

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

In Cell O8 we wish to sum all Sold QTY from the table named Sales (picture below). The last populated row in a table is row 1200.

Which formula syntax is the correct one? (One or more correct answers)

We wish to add a new column to a table called SalesValue. Its formula is ProductPrice * SoldQTY.

Which formula syntax is the correct one if entered in a cell P10? (One or more correct answers)

We wish to create SUMIFS conditional formula which sums all Sold QTY in a Sales table based on the criteria in a range L3:M5.

Both provided formulas will return the correct result. Which one is better to use?

We wish to create SUMIFS conditional formula which sums all Sold QTY in a Sales table based on the criteria in a range L3:M5.

Which statements are correct if we use =SUMIFS(O:O,I:I,M3,J:J,M4,K:K,M5) formula (formula addressing whole column ranges)?

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):

In cell F3 we want to enter a formula that will sum only the visible table rows based on filters applied.

Which formula is correct?

You want to filter the table so that it includes only sales in the year 2022. You open the filter on the date column but do not receive year selection. Instead, you receive a list of all dates.

What is wrong?

Which statement about PivotTables is correct?

Which statement about PivotTables is correct?