Reading notes is one thing; sitting a timed exam is another. The ActualCollection test engines recreate the pressure of the real Microsoft Analyzing and Visualizing Data with Microsoft Excel exam, so the 070-779 testing experience feels familiar long before you book your seat.
Microsoft 070-779 Exam Overview:
| Certification Vendor: | Microsoft |
|---|---|
| Exam Name: | Analyzing and Visualizing Data with Microsoft Excel |
| Exam Number: | 70-779 |
| Certificate Validity Period: | Retired (no longer available; certification path discontinued) |
| Passing Score: | 700 (on a 1000-point scale) |
| Real Exam Qty: | 40-60 (approximate range) |
| Exam Duration: | 120 minutes |
| Related Certifications: | MCSA: BI Reporting Microsoft Certified: Data Analyst Associate (Power BI - successor track) |
| Exam Format: | Active screen/lab tasks, Case studies, Multiple choice, Drag and drop |
| Available Languages: | English, Simplified Chinese, German, Korean, French, Spanish, Japanese |
| Exam Price: | $165 USD (varies by region) |
| Recommended Training: | Microsoft Learn: Excel data analysis learning paths Power BI and Excel data modeling resources |
| Exam Registration: | Pearson VUE Microsoft Exams Microsoft Certification Exam Registration |
| Sample Questions: |  |
| Exam Way: | Online proctored or test center exam (Pearson VUE) |
| Pre Condition: | No formal prerequisites, but experience with Excel 2016, Power Query, and Power Pivot is strongly recommended |
| Official Syllabus URL: | https://learn.microsoft.com/en-us/credentials/certifications/exams/70-779 |
Microsoft 070-779 Exam Syllabus Topics:
| Section | Objectives |
|---|---|
| Consume and Transform Data | - Clean, shape, and transform data - Get data from various sources using Excel and Power Query - Combine and append datasets |
| Publish and Share Data | - Publish Excel workbooks to Power BI or SharePoint - Manage and consume shared reports |
| Visualize Data | - Apply filters, slicers, and conditional formatting - Design interactive dashboards in Excel - Create PivotTables and PivotCharts |
| Model Data | - Define relationships between tables - Create and manage data models using Power Pivot - Create calculated columns and measures using DAX |
Microsoft 070-779 Exam: Frequently Asked Questions
The Microsoft Analyzing and Visualizing Data with Microsoft Excel exam is the official Microsoft test registered under exam code 070-779. Passing it earns you the MCSA: BI Reporting (retired) certification, a credential at the Associate level. It is also linked to the related certifications: MCSA: BI Reporting, Microsoft Certified: Data Analyst Associate (Power BI - successor track). Microsoft exams are valued because they test job-ready skills, so a passing score here carries real weight on a resume.
The Microsoft Analyzing and Visualizing Data with Microsoft Excel exam includes 40-60 (approximate range) questions to be completed within 120 minutes. Do the pacing math before exam day: with that many items on the clock, you need a steady rhythm and the discipline to flag a hard question and move on instead of stalling. Two or three full timed sessions with the ActualCollection test engine will show you exactly what that pace feels like, so time pressure stops being a factor on the real day.
To pass the Microsoft Analyzing and Visualizing Data with Microsoft Excel exam you need 700 (on a 1000-point scale), and the official registration fee is $165 USD (varies by region). A retake is not discounted: a failed attempt means paying the full $165 USD (varies by region) again, so treat your first sitting as the expensive one. A sensible rule is to book your seat only after you are scoring comfortably above the passing mark on the ActualCollection practice tests, not just squeaking past it once.
No formal prerequisites, but experience with Excel 2016, Power Query, and Power Pivot is strongly recommended
Eligibility rules do change from time to time, so confirm the current requirements before you register on the official exam page.
Registration for the Microsoft Analyzing and Visualizing Data with Microsoft Excel exam goes through the official channels below.
As for the delivery format, the exam is taken Online proctored or test center exam (Pearson VUE).
Microsoft points candidates toward the following training options for Microsoft Analyzing and Visualizing Data with Microsoft Excel.
Course work builds the foundation; question practice makes it stick. The 118 practice questions in the ActualCollection 070-779 package let you rehearse each topic under exam-style pressure before the real thing.
Yes. ActualCollection offers a free PDF demo of the Microsoft Analyzing and Visualizing Data with Microsoft Excel material so you can judge the question quality and format before spending anything. After purchase, your license includes 365 days of free updates, and if you want to keep receiving updates after that period, renewals are available at a 50% discount.
If you take the Microsoft Analyzing and Visualizing Data with Microsoft Excel exam within 60 days of your purchase and do not pass, ActualCollection backs you with a 100% money-back guarantee. The claim must match the exam your product covers: attempts taken within 3 days of purchase are not eligible (that is too little preparation time), and neither are downloaded-but-unused products, free materials, or expired orders. The candidate name must match the payer name, and you need to submit a scanned enrollment slip plus the official Score Report PDF within 2 days of the exam; claims are processed within 7 days. Prefer not to refund? You can swap instead and receive two other exam products of equal value for free while keeping the update service on your original purchase.
Delivery itself is instant: your files are downloadable right away and emailed to you within one minute of payment. If nothing arrives within 2 hours, contact customer service. There is no limit on how many computers you may install the software on.
The official Microsoft Analyzing and Visualizing Data with Microsoft Excel syllabus is organized into 4 domains. Key areas include Visualize Data, Consume and Transform Data, and Model Data. The complete, up-to-date topic list appears in the exam topics section above; work through it line by line and flag anything you cannot yet explain in your own words.
Microsoft Analyzing and Visualizing Data with Microsoft Excel Sample Questions:
Question 1
You have a workbook query that uses an Excel data source. The data source contains the following table.
You need the data to appear as shown in the following table.
How should you transform the data from Query Editor? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Question 2
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You have a Power Pivot model that contains the following tables.
There is a relationship between Products and ProductCategory.
You need to create a hierarchy in Products that contains ProductCategoryName and ProductName.
Solution: You create a measure that uses the USERELATIONSHIP DAX function.
Does this meet the goal?
A. No
B. Yes
Question 3
Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is the same in each question in this series.
Start of repeated scenario.
You are creating reports for a car repair company. You have four datasets in Excel spreadsheets. Four workbook queries load the datasets to a data model. A sample of the data is shown in the Data Sample exhibit.
(Click the Exhibit button.)
Data Sample exhibit:
The data model is shown in the Data Model exhibit. (Click the Exhibit button.)
The tables in the model contain the following data:
* DailyRepairs has a log of hours and revenue for each day, workshop, and repair type. Every day, a log entry is created for each workshop, even if no hours or revenue are recorded for that day. Total Hours and Total Revenue column.
* Workshops have a list of all the workshops and the current and previous workshop managers. The format of the Workshop Manager column is always Firstname Lastname. A value of 1 in the IsLatest column indicates that the workshop manager listed in the record is the current workshop manager.
* RepairTypes has a list of all the repair types
* Dates has a list of dates from 2015 to 2018
End of repeated scenario.
You create a measure named Average Revenue Per Hour that calculates the average revenue per hour.
You need to populate a cell in a worksheet to display the Average Revenue Per Hour where Repair Type is Engine.
Which Excel formula should you use?
A. =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[Dimensions]. [Repair Type]. [Engine]"))
B. =CUBEVALUE("ThisWorkbookDataModel", "[Measures]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. [Engine]"))
C. =CUBEMEMBER("ThisWorkbookDataModel", "[DailyRepairs]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[RepairTypes]. [Repair Type]. [Engine]"))
D. =CUBEMEMBER("ThisWorkbookDataModel", "[DailyRepairs]. [Avg Revenue Per Hour]", CUBEMEMBER ("ThisWorkbookDataModel", "[Dimensions]. [Repair Type]. [Engine]"))
Question 4
You have a pie chart.
You need the wedges of the pie chart to be separated as shown in the following exhibit.
A. Select a wedge of the pie chart and then drag the wedge.
B. Change the chart type to Pie of Pie.
C. Right-click the pie chart, click Expand/Collapse, and Then click Expand Entire Field
D. Right-click the pie chart, click Expand/Collapse, and then click Expand.
Question 5
You have the following table named SalesOrder Detail in a model.
You need to calculate the sum of SalesTotal for all the rows that have a quantity greater than 1.
Solutions:
| Question 1 Answer: Only visible for members | Question 2 Answer: A | Question 3 Answer: A | Question 4 Answer: A | Question 5 Answer: Only visible for members |
1115 Customer Reviews
