Modern Data Analytics in Excel

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