Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel Practice Test
Exam 70-779 focuses on using Excel to prepare, model, analyze, and present data. The available practice material includes scenarios involving Power Query transformations, PivotTables and PivotCharts, the Excel Data Model, Power Pivot measures written in DAX, and visual choices such as secondary axes and Sparklines. These tasks connect data handling with clear analytical reporting.
Exam overview
About the Exam 70-779: Analyzing and Visualizing Data with Microsoft Excel Exam
Working with analytical data in Excel requires choosing the right tool at each stage. Power Query can filter rows and reshape columns, while PivotTables summarize data and provide calculations such as running totals. In the Data Model, relationships connect tables; DAX measures can perform calculations such as distinct counts or comparisons with a prior year when the model supports them. Report design also involves selecting suitable chart scales and adding interactive filters with slicers or compact trend visuals with Sparklines. Practice scenarios can help you distinguish among these features, identify the transformation or calculation that matches a reporting need, and consider dependencies such as refresh behavior, compatible PivotTables, and a suitable date table.
Sample Practice Questions
Review a selection from this exam's published US Practice Tests practice bank.
A chart uses a measure with values ranging from 0 to 100,000 and another measure ranging from 0 to 100. The smaller-scale series is nearly invisible. What chart adjustment can make both series readable while preserving their different scales?
Show answer
Correct answer: AA secondary axis allows a series with a different magnitude to use its own scale, making it easier to see alongside the larger-scale series. The axes should be clearly labeled so viewers can interpret the chart accurately.
A Power Pivot measure needs to count the number of distinct customers in a Customer ID column, even when IDs repeat across transaction rows. Which DAX function is appropriate?
Show answer
Correct answer: ADISTINCTCOUNT returns the number of unique values in a column, so repeated customer IDs are counted once. SUM and AVERAGE are numeric aggregations, while RELATED retrieves a related value rather than counting unique IDs.
A report uses a PivotTable based on the Excel Data Model. You want a single interactive control to filter several PivotTables that share the same model. Which feature can provide that control when the reports are compatible?
Show answer
Correct answer: AA slicer can be connected to multiple compatible PivotTables, enabling a shared interactive filter. The reports must share an appropriate data source or model connection.
A PivotTable is based on a table that has been extended with new rows. The PivotTable still shows the prior results. What should you do to update its displayed results?
Show answer
Correct answer: ARefreshing a PivotTable updates its cache or connection from the source data, including newly added records when the source range or table includes them. Sorting or changing calculation mode does not refresh PivotTable data.
A Power Pivot model contains a Sales table and a Calendar table. A measure should calculate sales for the same period one year earlier. Which DAX function is commonly used for this date-based comparison when the model has a suitable date table and relationship?
Show answer
Correct answer: ASAMEPERIODLASTYEAR returns the corresponding date period shifted back one year, a common pattern for time comparisons. Reliable time-intelligence behavior depends on an appropriate date table and model relationship.
In Power Query, you want to keep only rows where the Status column equals "Complete". Which transformation should you apply?
Show answer
Correct answer: AFiltering the Status column to the required value removes rows that do not meet the condition while preserving the remaining records. Grouping, transposing, or splitting would change the data in unrelated ways.
In a PivotTable, you want to display the running cumulative total of a value field down a sequence of dates. Which value-field setting is designed for this calculation?
Show answer
Correct answer: ARunning Total In accumulates values across items in a selected base field, such as dates, to show a cumulative result. The other settings calculate proportions, rankings, or differences instead.
How to use this practice test
US Practice Tests provides free practice questions for this exam. Each session draws up to 20 random questions from the available bank and displays results afterward. Use the questions to check how well you distinguish Excel data-preparation steps, PivotTable features, DAX calculations, and visualization choices; revisit missed concepts in Excel.
Preparation tips
- In Power Query, practice filtering rows and unpivoting columns; note how each transformation changes the table’s shape.
- Build PivotTables and PivotCharts from the Data Model, then test refresh behavior, slicer connections, and running-total settings.
- Write DAX measures for distinct counts and prior-year comparisons; verify the role of table relationships and date-table setup.
- Compare chart options for measures with different ranges, and use Sparklines to show row-level trends within a worksheet.