book

Modern Data Analytics in Excel

by George Mount

April 2024

Beginner to intermediate

244 pages

5h 18m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Includes

Includes Quizzes

Learning ObjectivePrerequisitesTechnical RequirementsTechnological RequirementsHow I Got HereWhat Is “Modern Analytics”? Why Excel?Book OverviewPart I, Data Cleaning and Transformation with Power QueryPart II, Data Modeling and Analysis with Power PivotPart III, The Excel Data Analytics ToolkitEnd-of-Chapter ExercisesThis Is Not a Laundry ListConventions Used in This BookUsing Code ExamplesO’Reilly Online LearningHow to Contact UsAcknowledgments
Creating and Referring to Table HeadersViewing the Table FootersNaming Excel TablesFormatting Excel TablesUpdating Table RangesOrganizing Data for AnalyticsConclusionExercises
What Is Power Query?Power Query as Excel Myth Buster“Excel Is Not Reproducible”“Excel Does Not Have a True null”“Excel Can’t Process More Than 1,048,576 Rows”Power Query as Excel’s ETL ToolExtractTransformLoadA Tour of the Power Query EditorThe Ribbon MenuQueriesThe Imported DataExiting the Power Query EditorReturning to the Power Query EditorData Profiling in Power QueryWhat Is Data Profiling?Exploring the Data Preview OptionsOverriding the Thousand-Row LimitClosing Out of Data ProfilingConclusionExercises
Removing the Missing ValuesRefreshing the QuerySplitting Data into RowsFilling in Headers and Cell ValuesReplacing Column HeadersFilling Down Blank RowsConclusionExercises
Changing Column CaseDelimiting by ColumnChanging Data TypesDeleting ColumnsWorking with DatesCreating Custom ColumnsLoading & Inspecting the DataCalculated Columns Versus MeasuresReshaping DataConclusionExercises
Appending Multiple SourcesConnecting to External Excel WorkbooksAppending the QueriesUnderstanding Relational JoinsLeft Outer Join: Think VLOOKUP()Inner Join: Only the MatchesManaging Your QueriesGrouping Your QueriesViewing Query DependenciesConclusionExercises
What Is Power Pivot?Why Power Pivot?Power Pivot and the Data ModelLoading the Power Pivot Add-inA Brief Tour of the Power Pivot Add-InData ModelCalculationsTablesRelationshipsSettingsConclusionExercises
Connecting Data to Power PivotCreating RelationshipsIdentifying Fact and Dimension TablesArranging the Diagram ViewEditing the RelationshipsLoading the Results to ExcelUnderstanding CardinalityOne-to-One CardinalityOne-to-Many RelationshipsMany-to-Many RelationshipsWhy Does Cardinality Matter?Understanding Filter DirectionFiltering orders with usersFiltering users with ordersFilter Direction and CardinalityFrom Design to Practice in Power PivotCreating Columns in Power PivotCalculating in Power Query Versus Power PivotExample: Calculating Profit MarginRecoding Column Values with SWITCH()Creating and Managing HierarchiesCreating a Hierarchy in Power PivotUsing Hierarchies in the PivotTableLoading the Data Model to Power BIPower BI as the Third Piece of “Modern Excel”Importing the Data Model to Power BIViewing the Data in Power BIConclusionExercises

Creating DAX MeasuresCreating Implicit MeasuresCreating Explicit MeasuresCreating KPIsAdjusting Icon StylesAdding the KPI to the PivotTableConclusionExercises
CALCULATE() and the Importance of Filter ContextCALCULATE() with One CriterionCALCULATE() with Multiple CriteriaAND ConditionsOR ConditionsCALCULATE() with ALL()Time Intelligence FunctionsAdding a Calendar TableCreating Basic Time Intelligence MeasuresConclusionExercises
Dynamic Array Functions ExplainedWhat Is an Array in Excel?Array ReferencesArray FormulasAn Overview of Dynamic Array FunctionsFinding Distinct and Unique Values with UNIQUE()Finding Unique Versus Distinct ValuesUsing the Spill OperatorFiltering Records with FILTER()Adding a Header ColumnFiltering by Multiple CriteriaSorting Records with SORTBY()Sorting by Multiple CriteriaSorting by Another Column Without Printing ItCreating Modern Lookups with XLOOKUP()XLOOKUP() Versus VLOOKUP()A Basic XLOOKUP()XLOOKUP() and Error HandlingXLOOKUP() and Looking Up to the LeftOther Dynamic Array FunctionsDynamic Arrays and Modern ExcelConclusionExercises
The Growing Complexity of Data and AnalyticsExcel and the Legacy of Self-Service BIExcel for Augmented AnalyticsUsing Analyze Data for AI Powered InsightsBuilding Statistical Models with XLMinerReading Data from an ImageSentiment Analysis with Azure Machine LearningConclusionExercises
Reader PrerequisitesThe Role of Python in Modern ExcelA Growing Stack Requires GlueNetwork Effects Mean Faster Development TimeBring Modern Development to ExcelUsing Python and Excel Together with pandas and openpyxlOther Python Packages for ExcelDemonstration of Excel Automation with pandas and openpyxlCleaning Up the Data in pandasSummarizing Findings with openpyxlAdding a Styled Data SourceConclusionExercises
Exploring Excel’s Other FeaturesLET() and LAMBDA()Power Automate, Office Scripts, and Excel OnlineContinued Exploration of Power Query and Power PivotPower Query and MPower Pivot and DAXPower BI for Dashboards and ReportsAzure and Cloud ComputingPython ProgrammingLarge Language Models and Prompt EngineeringParting Words

Content preview from Modern Data Analytics in Excel

About the Author

George Mount is the founder and CEO of Stringfest Analytics, a consulting firm specializing in analytics training. George speaks regularly on this topic and maintains a blog at stringfestanalytics.com.

In addition to being the author of Advancing into Analytics: From Excel to Python and R (O’Reilly, 2021), he has been recognized as a Microsoft Most Valuable Professional (MVP) for his contributions to the community and technical expertise in Excel.

George holds a bachelor’s degree in economics from Hillsdale College, as well as master’s degrees in finance and information systems from Case Western Reserve University. He currently resides in Cleveland, Ohio.

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Start your free trial

Data Modeling with Microsoft Excel

Bernard Obeng Boateng

Data Analysis Fundamentals with Excel (Video)

Chris Sorensen / Ammul Shergill

Microsoft Excel Data Analysis and Business Modeling (Office 2021 and Microsoft 365), 7th Edition

Wayne Winston

Microsoft Excel Pivot Table Data Crunching (Office 2021 and Microsoft 365)

Bill Jelen

Publisher Resources

ISBN: 9781098148812Errata Page Supplemental Content