PDP Url

Analyzing Data with Excel (MS-20779)

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.

Virtual

Learning Style

Intermediate

Difficulty

3 Days

Course Duration

3

SATV Value

Course Info

Download PDF

Certificate

See Sample

Subscribe Buy Individually
When you subscribe, you get:
Master Subscription plan gives you access to this course and over 1204 other popular courses
On Sale!
Now Only $2,999.00 Regular Price $3,499.00
Now Only $2,999.00 Regular Price $3,499.00
/ Year
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

If you enroll in this course without the Master Subscription plan, you receive a Free Official Exam Voucher (excluding purchases using Training Vouchers / SATV) for the 70-779 Exam. This course does not include Exam Voucher if enrolled within the Master Subscription, however, you can request to purchase the Official Exam Voucher separately.

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.

Audience:

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.

Prerequisites:

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

Outline

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 IT Ops & Management
Lab Access Yes
Technology Microsoft
Learning Style Virtual Classroom
Difficulty Intermediate
Course Duration 3 Days
Language English
SATV Value 3
VPA Discount VPA Discount

Reviews

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

Contact A Learning Consultant