1 / 24

John Campbell Joe McDaid

Advanced analytics in Microsoft Excel: Get deep insights from your data including AI-powered capabilities. John Campbell Joe McDaid. BRK3082. The analyst journey. Get Data. Analyze. Visualize. Publish. Consume. Data Types. Data Types Power Query Connectors & Transformations.

warren
Download Presentation

John Campbell Joe McDaid

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced analytics in Microsoft Excel: Get deep insights from your data including AI-powered capabilities John Campbell Joe McDaid BRK3082

  2. The analyst journey Get Data Analyze Visualize Publish Consume Data Types Data Types Power Query Connectors & Transformations Data Types Ideas Dynamic Arrays Data Types Ideas Dynamic Arrays PivotTableFormulasPower PivotETS Forecasting Ideas ChartsPivotChartsCustom Visuals Ideas Power BI & Microsoft Teams integration Full fidelity consumption across devices

  3. Intelligence and analytics There is an abundance of data and a scarcity of data skills Today Tomorrow Expect people to learn and adapt to software and tools Expect software and tools to adapt to people, and accelerate their path to mastery

  4. Services are intelligent if they are… Connected Personalized Smart Acts on behalf of users, connecting anticipated needs with outcomes Learns about and reacts to the users, organizations, and communities Gets smarter over time

  5. Data Types

  6. Data Types Work with cell values beyond just flat text and numbers Intelligent data from the cloudEasily get data related to a cell by intelligent conversion and disambiguation Data types are “cell values”Data types work a lot like numbers or text – they are values and can be used by formulas, filters, sort, find, and more

  7. Data Types best practices They are “values” Compound data (records) in cells Dereference with “dot” operator – use with any function Sort, Filter, Find, and more Change the data type “Overtype” in the cell Right click menu  change or flatten back to text Convert only key cells The “nouns” – use “dot” operator to unpack as needed Intelligent Conversion Context from other converted cells + adjacent cells Search in Data Selector pane Some features will #VALUE Use part of the data type via dereference in grid

  8. Ideas

  9. Ideas Automatically surface patterns in your data Intelligent analysis & visualization toolIt uses AI to automatically identify patterns in your data. For example, trends, outliers and correlations. It then presents them to you visually. Democratizes advanced analyticsBy surfacing prebuilt PivotCharts and PivotTables, it allows users with no previous experience to easily use them in their analysis. Gets better over timeThe recommendations will continuously improve based on signals from our users and ongoing backend improvements Cloud deliveredAllowing it to be cross platform with a high improvement cadence

  10. Demo

  11. Ideas best practices Remove ambiguity from data, this allows the AI to focus on what's important Clean tabular data: Column headers: Sales, Qty, Category, etc. Unpivot crosstab data using Power Query Remove extraneous columns Apply Formatting Good use of dates, %, $, etc Size Small to medium-sized data sets Max of ~16MB (approximately 250,000 cells)

  12. Dynamic Arrays Announced at Ignite

  13. Single Values Since Excel’s inception, regular formulas could only return single values : Numbers, Text, Booleans, Errors and, the recently added, Rich data types. One formula for each value.

  14. The Grid Natively Understands Arrays We’ve added the ability for cells to natively understand arrays (a.k.a lists) via a feature we are calling dynamic arrays

  15. New Array Focused Functions We are adding 7 fantastic new functions to Excel are array focused

  16. Demo Uses Simple Formulas 3 Formulas Built in minutes Self expanding

  17. Key Advantages of Dynamic Arrays More capable Less chance of error Faster to build

  18. Dynamic Array Best practices For resizing arrays, apply formats to the largest expected size. Use the A1# references where possible Use tables for data input. Structured table references grow as more rows as more data is added.

  19. Dynamic Array Learn More Visit Tech Community Blog Great community content released already!

  20. Stay connected and learn more Learn more about Excel Excel blog on TechCommunity Making Excel work for FP&A Guide What Is New in Excel Office Training Center Excel Analytics Capabilities Answers to your Excel questions Office.com/Excel/Help Give us feedback • UserVoice • Share Feedback on Excel Join us • Excel User Community • Excel on Facebook and Twitter

  21. Related sessions

  22. Please evaluate this sessionYour feedback is important to us! Please evaluate this session through MyEvaluations on the mobile appor website. Download the app:https://aka.ms/ignite.mobileApp Go to the website: https://myignite.techcommunity.microsoft.com/evaluations

More Related