Product Image

Analyzing Data with Excel (MS-20779)

87% of 100
If you are interested in updating your skills of excel sheet with the BI techniques, you have come to the right place. QuickStart offers a variety of Data science trainings and courses to keep you updated and in markets spot light.


Learning Style





3 Days

Course Duration

Course Info

Download PDF


See Sample

empty table

You have already taken demo for this course.

If you want to get access to demo again, feel free to contact our support at (855) 800-8240
If you are interested in updating your skills of excel sheet with the BI techniques, you have come to the right place. QuickStart offers a variety of Data science trainings and courses to keep you updated and in markets spot light.

Course Information

The exam associated with this course will retire on 31st January 2021. However, the course is still valid as training material for learning purposes.

About this course:

Analyzing Data with Excel (MS-20779) is a 3 day course, that focuses mostly on integrating BI techniques to Excel Data analysis, therefore this course increases the capabilities of students from making pie charts and tables to Pivot charts, the Excel data Model and Power BI.  The courseware is supported by virtual labs to provide practice on real platforms. Moreover, students will get a recognized certificate on completion off the course.

A qualified Solutions Developers with Microsoft Azure skills earns $81,714 annually.

Course Objective: 

After completing this course, students will be able to: 

  • Explore and extend a classic Excel dashboard.

  • Explore and extend an Excel data model.

  • Pre-format and import a .CSV file.

  • use data from a SQL Server database

  • use data from a previous reports.

  • Build measures using advanced DAX functions.

  • Build data visualizations in Excel.

  • Build a Power BI dashboard with Excel.


The target audience of this course includes:

  • Students experienced in analyzing data with Excel and who wish to add BI techniques to their expertise.

  • SQL Server report creators who want to learn alternative methods of presenting data.


Students attempting this course must have a previous understanding of the following:  

  • Basic working knowledge of the Microsoft Windows operating system and its functions

  • Familiarity of relational databases.

  • Complete Knowhow of Excel spreadsheets including formulas, charts, filtering, sorting, and sub-totals.

Suggested prerequisites courses:

  • SQL Database for Beginners

Career & Salary Insight


Frequently Asked Questions

Q1. How will this training benefit me in my current job role? This training will help you in your Job’s current role by learning and getting hands on experience in the following areas:

Q2. How to create Excel Dashboards, Analyze Data, How to use Excel Pivot tables and what are some limitations of Data Analysis in Excel.

  • Learn about Data modelling using Excel and how to write DAX queries.
  • Data Transformation and Shaping, Loading into excel and Data Importing from Pre-Formatted Excel files.
  • Learn how to import data from SQL Server, CSV file and create a Data Table.
  • You will also learn how to import and transform data from excel reports to create meaningful insights for your business to make key-business decisions.
  • You will learn how to write some advanced DAX functions, write complex DAX queries to create measures.
  • Data Visualizations help businesses understand factual information and this requires understanding of Pivot Charts, Cube Functions and Charts available for Cube Functions, which you learn in this course.
  • Most powerful BI tool to this date “Power BI”. You will be able to get an overview of it regarding how to upload your excel data into Power BI and what are some

Q3. How To Replace One Value With Another In Excel? You can replace one value with another using the FIND & REPLACE feature in Excel. To do this, select the data set and use the keyboard shortcut - CONTROL H (hold the control key and then press H). This will open the Find & Replace dialog box. In this dialog box, you can specify the value you want to change and the replacement value.

Q4. How Can You Select All Blank Cells In Excel?

  • If you work with a data set that has blank cells in it, you can easily select these cells in Excel. Once selected, you can choose to highlight these, delete these, or add some value to it (such as 0 or NA).
  • To do this, you need to use the Go To Special dialog box in Excel.

Here are the steps to select all blank cells in Excel:
  1. Select the entire data set (including blank cells).
  2. Press F5 (this opens the Go to dialog box).
  3. Click the 'Special' button (this opens the Go To special dialogue box).
  4. Select Blanks and click OK (this selects all the blank cells in your data set).

Q5. Explain the function of Solver in Excel?

  • Solver in Excel is an add-in that allows you to get an optimum solution when there are many variables and constraints.
  • You can consider it to be an advanced version of Goal Seek.
  • With Solver, you can specify what the constraints are and the objective that you need to achieve.
  • It does the calculation in the back-end to give you a possible solution

Q6. What Is A One-variable Data Table?

One variable Data Table in Excel is most suited in situations when you want to see how the final result changes when you change one of the input variables.

For Example:

  • If you want to know how much on monthly installment change if you increase/decrease the number of months, you can set up a one-variable data table for it.
  • This can be useful when you want to keep the monthly payment less than $500 and know what all options you have (6 months, 9 months, 12 months, etc.)

More Information

More Information
Brand Microsoft
Subjects Data Platform
Lab Access Yes
Technology Microsoft
Learning Style Virtual Classroom
Learning Type Course
Difficulty Intermediate
Course Duration 3 Days
Language English

Acclaim Badge


Monthly Payments With Affirm

QuickStart and Affirm have teamed up to offer you financing, allowing you to pay off your purchase over time, on your own terms. Simply select Affirm in checkout, and you will need to take a few moments to fill out some information for a real-time decision. Checking your eligibility and terms won't affect your credit score. Once you are qualified, you will have the option to choose from 3, 6, 12, 18 or 26-month financing terms*, with each term clearly showing your monthly payment. No hidden fees or costs! it's that simple!

Rates are between 0-30% APR. As an example, a $700 purchase might cost $63.24/mo for 12 months at 15% APR. A down payment may be required. Subject to eligibility check and approval. Payment options depend on your purchase amount. Estimated payment excluded taxes and shipping fees. Paid interest is nonrefundable. Actual terms may vary. Payment options through Affirm are provided by these lending partners: Visit for more info.


Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Course Expert:


Tom Robertson
(Data Science Enthusiast)

Tom is an innovator first, and then a Data Scientist & Software Architect. He has integrated expertise in business, product, technology and management. Tom has been involved in creating category defining new products in AI and big data for different industries, which generated more than hundred million revenue cumulatively, and served more than 10 million users.
As a Data Scientist and Software Architect Tom has extensive experience in data science, engineering, architecture and software development. To date Tom has accumulated over a decade of experience in R, Python & Linux Shell programming.

Tom has expertise on Python, SQL, and Spark. He has worked on several libraries including but not limited to Scikit-learn, Pandas, NumPy, Matplotlib, Seaborn, SciPy, NLTK, Keras, and Tensorflow.

Customer Reviews
  1. Analyzing Data with Excel (MS20779)
    Overall Satisfaction
    Quality of Instructor
    Course Quality
    Like the materials and labs.

    Review by

    Posted on

  2. Analyzing Data with Excel (MS20779)
    Overall Satisfaction
    Quality of Instructor
    Course Quality
    MY account was not provisioned appropriately so i missed the first two hours almost of my first class. While support got me up and running the fact that nothing was ready for me on day 1 was a real bummer. This 7 rating only comes because Muhammad Shoaib was a great instructor.

    Review by

    Posted on

  3. Analyzing Data with Excel (MS20779)
    Overall Satisfaction
    Quality of Instructor
    Course Quality
    It has been an enjoyable 3 days course with instructor Muhammad Shoaib. The skill covered in this course is something I have not seen many people taking advantage or make use of. Highly recommended.

    Review by

    Posted on