Modern Data Analytics in Excel (First Early Release)

If you haven't modernized your data cleaning and reporting processes in Microsoft Excel, you're missing out on big productivity gains. And if you're looking to conduct rigorous data analysis, more can be done in Excel than you think. This practical book serves as an introduction to the modern Excel suite of features along with other powerful tools for analytics. George Mount of Stringfest Analytics shows business analysts, data analysts, and business intelligence specialists how to make bigger gains right from your spreadsheets by using Excel's latest features. You'll learn how to build repeatable data cleaning workflows with Power Query, and design relational data models straight from your workbook with Power Pivot. You'll also explore other exciting new features for analytics, such as dynamic array functions, AI-powered insights, and Python integration. Learn how to build reports and analyses that were previously difficult or impossible to do in Excel. This book shows you how to Build repeatable data cleaning processes for Excel with Power Query Create relational data models and analysis measures with Power Pivot Pull data quickly with dynamic arrays Use AI to uncover patterns and trends from inside Excel Integrate Python functionality with Excel for automated analysis and reporting 1. Tables: The portal to modern analytics Creating and referring to table headers Viewing the table footers Naming Excel tables Formatting Excel tables Updating table ranges Organizing data for analytics Conclusion Exercises 2. Transforming Rows in Power Query Removing the missing values Refreshing the query Splitting data into rows Splitting Signups by column Stripping the Whitespace Filling in headers and cell values Replacing column headers Conclusion Exercises states worksheet: midwest_cities worksheet: 3. Transforming Columns in Power Query Changing column case Delimiting by column Changing data types Deleting columns Reformatting data Creating custom columns Loading & inspecting the data Calculated columns versus measures Reshaping data Conclusion Exercises 4. Introducing Dynamic Array Functions Dynamic array functions explained What is an array in Excel? Array references Static array references Dynamic array references Array formulas Static array formulas Dynamic array functions An overview of dynamic array functions Finding Distinct and Unique Values with UNIQUE() The UNIQUE() function parameters Finding unique versus distinct values Using the spill operator Filtering records with FILTER() Adding a header column Filtering by multiple criteria Filtering by multiple criteria Sorting records with SORT() and SORTBY() Sorting by one criterion with SORT() SORTBY() orders an array by another array Sorting by multiple criteria Sorting by another column without printing it Creating modern lookups with XLOOKUP() XLOOKUP() versus VLOOKUP() A basic XLOOKUP() XLOOKUP() and error handling XLOOKUP() and looking up to the left Other dynamic array functions Dynamic arrays and modern Excel Simplicity Familiarity Real-time updates Conclusion Exercises 5. Augmented Analytics and the Future of Excel The growing complexity of data and analytics Excel and the legacy of self-service BI Excel for augmented analytics Using Analyze Data for AI-powered insights Building statistical models with XLMiner Reading data from camera Sentiment analysis with Azure Machine Learning Converged Analytics and the Future of Excel Exercises 6. Python with Excel Reader prerequisites The Role of Python in Modern Excel A growing stack requires glue Network effects mean faster development time Bring modern development to Excel Python and the future of Excel Using Python and Excel together with pandas and openpyxl Why pandas for Excel? The limitations of working with pandas for Excel What openpyxl contributes How to use openpyxl with pandas Other Python packages for Excel Demonstration of Excel automation with pandas and openpyxl Cleaning up the data in pandas Summarize findings with openpyxl Adding a styled data source Conclusion Exercises