Microsoft Excel Data Analysis - Learn How The Experts Use It

Discover how Excel functions as a ubiquitous analytics and BI tool across data sources, leveraging pivot tables, Power Pivot, Analysis Services, and four BI add-ins to unlock strategic insights.

Explore Excel's analytics facilities across core tools and add-ons, from tables, pivots, charts, and conditional formatting to Power Pivot, Power View, data model, and Data Explorer add-ins.

Explore data management and modeling in the core Excel and the Excel data model. See BI analytics capabilities and how to connect to data sources with the data connection wizard.

Demonstrate bringing external data into Excel with web queries. Import web data and Sequel Server data, add to the data model, and format as tables.

See how data connection files are created and stored by the data connection wizard, and manage them in the connections dialog to enhance analysis with spark lines.

Review how to manage office data connections by inspecting the data sources folder and distinguishing single-table from multi-table connections, such as the three-table product connection and the employee table connection.

Excel offers support for viewing, querying, and analyzing analysis services cubes, including pivot table field list for measures, dimensions, hierarchies, KPIs, actions, drill-through, what-if scenarios, and cube formula functions.

Connect Excel 2013 to an analysis services OLAP cube, build pivot tables and cube formulas to analyze multidimensional data with measures, dimensions, and KPI insights.

Explore the Excel data model, built on analysis services, powered by the x velocity in-memory analytics engine, storing workbook data in memory in a columnar, compressed format for fast queries.

Explore the genesis of the BI semantic model, merging analysis services, semantic modeling, and in-memory x velocity with DAX, Power Pivot, and Power View in Excel 2013 for self-service analytics.

Explore Power Pivot add-in and the Excel data model to perform explicit data modeling, semantic modeling, and enhanced reporting with a robust semantic model.

Acquire and activate the power pivot add-in for Excel 2010–2013, understand office edition requirements, and import data from relational databases, data feeds, and Excel workbooks for analysis.

Activate Power Pivot, import data from SQL Server and other sources, clean and rename tables, and build a star schema by visually configuring relationships.

Master advanced modeling in Power Pivot by building calculated columns and measures with DAX syntax, managing relationships, perspectives, hierarchies, and KPIs, and using diagram view to refine the model.

Create calculated columns and measures in Power Pivot, format as currency, and set a KPI for total sales; build hierarchies and a date table with slicers for geography and time.

Explore data discovery and visualization with Power View, leveraging the semantic model, data repository, and Power Pivot for ad hoc analysis and interactive, filterable reports in SharePoint and Excel 2013.

Create Power View reports that query semantic models, including direct query mode for tabular analysis services. Activate the add-ins, design reports in Excel or SharePoint, and export to PowerPoint.

Learn Power View basics in Excel, using the refined data model and Power Pivot to build interactive visuals—sales by country and shipper performance with cross-filtering.

Maximize visualizations in Power View, build filters for date columns, and use scatter and bubble charts where x, y, size, and color track multiple measures, plus a play axis.

Demonstrates filters and slicers, converting fields to slicers, and using a play axis to animate bubble charts with total sales as size and discount as y value.

Explore advanced properties in Power Pivot, like the representative column and image, and see how Power Pivot and Power View render keys and aggregations in the BI semantic model.

Learn how the Data Explorer add-in for Excel imports data from conventional and novel sources, and pushes results into Excel data model for use with Power Pivot and Power View.

Compare Excel's get external data from web feature with Data Explorer's web tables, pulling CNBC stock indices, commodities, forex, and Facebook likes into an Excel table or data model.

Explore data shaping and manipulation with data explorer: configure data types, modify schema, split delimited text, manage columns, sort, group, pivot, filter, replace values, and work with parent-child data.

Import Hadoop data from Windows Azure HDInsight into Excel using Data Explorer, shape and split columns, then model with Power Pivot and visualize with Power View.

Discover how Data Explorer records each step as a formula, enabling a script view of acquired data, filters, splits, and renames; the M language powers data modeling across types.

Explore Excel data analysis by replaying steps from the data explorer, viewing the m language code, and writing queries from scratch to analyze data stored in hd insight storage.

Explore geo flow, a mapping three-dimensional visualization addon for Excel that geocodes addresses on the fly and provides column, bubble, and heat map charts.

Explore geo flow in Excel 2013 by mapping Hadoop cell tower dwell time data using country and state geography; visualize dwell time as bar height in the data model.

Explore geo flow visuals, including column, bubble, and heat map charts with flexible aggregations and time regions. Master map controls, annotations, and theme options to enhance data storytelling.

Explore geo flow's advanced features: zooming, data shapes, and perspective changes. Switch chart types, legends, annotations, satellite themes, and use find to locate the Empire State Building.

Explore how layers, scenes, and tours let you overlay charts such as column charts and heat maps, adjust properties, lock scales, and create animated, themed transitions.

Practice building a data visualization tour by adding layers, creating scenes across regions, applying themes, and playing tours to assemble a full presentation.

Explore how Excel analytics integrate with SharePoint via Excel Services, Power Pivot, Power View, and Performance Point for dashboards and scorecards.

Explore how the Excel web app on Office 365 renders pivot tables, pivot charts, and Power View reports in the browser, with drill-downs and cross-platform sharing.

Explore how Power Pivot works with SharePoint to enable end-to-end data modeling, sharing, and analytics, plus Power View’s standalone capabilities and cross-compatibility with Analysis Services.

Explore how performance point and reporting services, as analysis services clients, query Excel data models in SharePoint workbooks. Build dashboards with unified filters and integrate Power Pivot data.

Explore Excel’s end-to-end analytics, from data acquisition with the data connection wizard and Data Explorer to the Excel data model, Power Pivot, Power View, and Geo Flow for visual analytics.

Learn how the experts use Microsoft Excel for data analysis and apply techniques to work with spreadsheets.