Advance MS Excel
This course takes up where the Optima Train Excel Pivot Tables and Pivot Charts course leaves off. It all revolves around the fairly new suite of Microsoft “Power” tools, often referred to as Power BI. The course has three primary themes. First, it teaches you a number of methods for importing data (from a database, text files, or other sources) into Excel. The primary emphasis is on Power Query. Second, it devotes considerable time to the Excel Data Model and Power Pivot for analyzing data with pivot tables. These take traditional pivot tables to a whole new level. Third, it shows how Power View and Power Map can be used to create insightful reports and maps with very little work.
Objectives
- An understanding of how the four components of Microsoft’s Power BI suite (Power Query, Power Pivot, Power
View, and Power Map) work together to provide amazing
data analysis possibilities, all within Excel - How to import data from various data sources into Excel,
both with older tools and the newer Power Query / Get and Transform tools, with emphasis on the latter - An understanding of the relationship between the Excel Data Model and Power Pivot
- An understanding of how Power Pivot adds significant power to Excel’s existing data analysis tools
- How to use the Power Pivot user interface, and how to create pivot tables with Power Pivot
- How to use the DAX language in Power Pivot to create calculated columns and measures, and understand why DAX is so powerful
- How to create named sets, hierarchies, and KPIs (key performance indicators) with Power Pivot
- How Power View and Power Map can be used to create insightful reports and maps with very little work
Excel Tables, Slicers and Timelines
- Learn how to turn a range of cells into a Microsoft Office Excel table.
- Learn about the elements of an Excel table i.e. Header row, Banded rows, Calculated column, and Total Rows.
- Learn Table features that you can use to manage table data i.e. sorting and filtering, formatting table data, inserting and deleting table rows and columns, using a calculated column, displaying and calculating “table data totals” and using structured references.
Dashboard Reporting
- How to use multiple PivotTables, Pivot Charts, and related tools to create a dynamic dashboard.
- How to quickly filter the data in a way you want with Slicers and a Timeline, which allow your PivotTables and charts to automatically expand and contract to display only the information that you want to see.
- How to quickly refresh your dashboard when you add or update data.
Building Data Models using PowerPivot
- Understand how to organize tables for the Pivot Table tool relationally, freeing the analyst from the need to import data as Excel sheets before analyzing them
- Learn how fast, space-saving and columnar database can handle huge amounts of data without the limitations of Excel sheets
- Learn how to integrate different sources of data, such as databases, Excel sheets, and data sources available on the Internet, and virtually any kind of data
Key Performance Indicators (KPIs)
- How to create KPI (Base Value, Target Value and Status thresholds)
Power BI Desktop (Introduction)
- You will learn how to create interactive map, pie, bar, and column charts, with just a few clicks to bring your data to life
- You will learn how hierarchies can make your reports more interactive, in-depth, and engaging for your audience.
- You also learn to put multiple visualizations into o n the report, and how those visualizations can filter, interact, and build on one another.
Pivot Table
- Querying large amounts of data in many user-friendly ways. • Subtotaling and aggregating numeric data, summarizing
data by categories and subcategories, and creating custom
calculations and formulas. - Expanding and collapsing levels of data to focus your results,
and drilling down to details from the summary data for areas
of interest to you. - Moving rows to columns or columns to rows (or “pivoting”)
to see different summaries of the source data. - Filtering, sorting, grouping, and conditionally formatting the
most useful and interesting subset of data enables you to
focus on just the information you want. - Presenting concise, attractive, and annotated online or
printed reports.
Power Query / Get and Transform Data
- Connect – make connections to data sitting in the cloud, in a service, or locally including: files like Excel workbooks or CSV files, databases such as Access, SQL Server, Oracle, and MySQL, Azure services such as HDInsight or Blob Storage, and al sorts of other sources such as the Web, SharePoint Lists, Hadoop Files, Facebook, and Salesforce.
- Transform – shape the data to meet your needs; the source remains unchanged. You can remove a column, change a data type, merge tables, etc.
- Combine – create a data model from multiple data sources,
and get a unique view of the data - Share – once your query is complete you can save it, share it
or use it for reports
Data Analysis Expressions (Basics)
- Understand DAX, and how this powerful functional language can define complex expressions on top of the relational database. How It make it possible to define surprisingly rich expressions compared to those standards in Excel
- Understand Calculated Columns & Calculated fields (Measures)
- How to create a “Calendar table”, and apply basic DAX functions i.e. Related, Filter, Calculate
