By: Daniel Calbimonte | Comments (4) | Related: More > Professional Development Certifications
Problem
I'd like to take and pass Microsoft exam 70-799 Analyzing and Visualizing Data with Microsoft Excel. What resources are available to help me learn more and prepare for this exam?
Solution
In this tip, we will talk about Microsoft exam 70-779, Analyzing and Visualizing Data with Microsoft Excel.
FAQ
Is this exam in Beta version?
Yes, it is. When this tip was written it was still a Beta version.
Can I take the Beta exam and be certified?
Yes, however your score will be published after the expiration of the beta period, which may be between September and October.
Why is Excel used in BI?
BI experts work with Visual Studio, SSDT or SSMS, Power BI and many other tools. However, many data analysts feel very comfortable with Excel. That is why they are using it. The BI Tools are not so popular compared with Excel. It has some limitations, but it is definitely a very powerful tool.
Do I need to have an Azure subscription to study for this exam?
Yes, however there are free versions that require a credit card, but are free to use. The exam contains some topics related to Azure like connecting from Excel to SQL Azure or Big Data.
What Microsoft Certifications are related to this exam?
This exam will be an optional exam for the MCSE Data Management and Analytics certification.
Is the exam difficult?
Excel is not that hard to use, however, you need to setup environments like Azure, Big Data, SSAS and other environments that require a lot of knowledge for the setup. Once that the environments are configured, using Excel is not so difficult.
Which books would you recommend for this exam?
The following books may be useful:
- Big Data Analytics with Microsoft HDInsight in 24 Hours, Sams Teach Yourself
- The Definitive Guide to DAX: Business intelligence with Microsoft Excel, SQL Server Analysis Services, and Power BI (Business Skills) 1st Edition
- Excel as Your Database
- Excel: Pivot Tables & Charts (Quick Study: Computer)
- Mastering Excel: Power Pack Bundle
- Marco Russo books
- HDInsight Essentials - Second Edition
- Microsoft Big Data Solutions
- Power Query for Power BI and Excel
Are there some courses for this exam?
Yes, the following courses will be useful:
- Excel Udemy courses
- Pluralsight courses
- edX courses
- MVA courses
- Coursera Excel courses
- GoSkill courses
- ExcelIsFun courses
- DAT206x courses
Can you provide some links to study, for this exam?
Yes, here you have some useful links:
Consume and Transform Data by Using Microsoft Excel Connect to data sources
- Import from data sources
- Connect Excel to an Azure SQL database and create a report
- Using Excel to interact with a SSAS cube
- Query Oracle database
- Connect to an Oracle database (Power Query)
- Connect Excel to Hadoop by using Power Query
- Connect Excel to Hadoop in Azure HDInsight with the Microsoft Hive ODBC driver
- Import or export text (.txt or .csv) files
- Get data from Excel workbook files
- Connect to (Import) external data
- Import and analyze data
- Perform data transformations
- Get & Transform in Excel 2016
- Microsoft Excel — The BASICS of Data Transformation
- Basic Excel Business Analytics #27: Clean & Transform Data: Formulas, Flash Fill, Power Query, TTC
- Excel 2013 Power Query #02: Transform Data and Import Into PowerPivot or Excel Table
- Apply Business Rules (MDS Add-in for Excel)
- Format a date the way you want
- Convert dates stored as text to dates
- Filter
- Advanced Filter
- Number and Text Filters
- Date Filters
- Decimal Places
- Date and Time Formats
- Fractions
- Currency vs Accounting
- Text to Numbers
- Numbers to Text
- Custom Number Format
- Format Painter
- Cell Styles
- Themes
- Cleanse data
Model Data
- Create and optimize data models
- Create calculated columns, measures, and tables
- Create performance KPIs
- Create hierarchies
Visualize Data
- Create and manage PivotTables
- Create and manage PivotCharts
- Interact with Power BI
Next Steps
For more information about this exam, refer to these links:
- Analyzing and Visualizing Data with Microsoft Excel (beta)
- Blogs office
- The 69 World’s Best Excel Blogs
- Check out all of the other related certification tips
About the author
Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and 6-time Microsoft Certified IT Professional. Daniel started his career in 2001 and has worked with SQL Server 6.0 to 2022. Daniel is a DBA as well as specializes in Business Intelligence (SSIS, SSAS, SSRS) technologies.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
