Here’s what you will learn in each chapter of Modern Data Analytics in Excel

Learning objectives are crucial in instructional design because they provide a clear roadmap for both the instructor and the learner, outlining what is expected to be achieved by the end of a course or lesson. They can help in designing a course or book, selecting appropriate instructional strategies, and assessing learner progress.

By explicitly sharing these objectives with learners, instructors set clear expectations, which can increase student motivation and focus. Moreover, when learners are aware of the objectives, they can better monitor their own progress and understanding, leading to a more self-directed learning experience.

To that end, I’d like to share learning objectives for each chapter of my book, Modern Data Analytics in Excel. You can learn more about the book and where to get it here:

A summary of what you’ll learn and be able to do after reading each chapter of the book follows.

Chapter 1: Tables: The Portal to Modern Excel

By the end of this chapter, you’ll understand:

• The importance and foundational aspects of using tables in Excel to enhance data analysis.
• How to properly utilize table headers and footers to structure your data effectively.
• The advantages of naming tables for easier reference and organization in formulas and data analysis.
• The significance of formatting tables to improve readability and presentation.
• The concept of tidy data and the necessary steps to reorganize data into a tidy format for advanced analytical tasks.
• How Excel tables integrate with tools like Power Query and Power Pivot to expand data manipulation capabilities beyond basic table functionalities.

And you’ll be able to:

• Create tables in Excel and convert existing data ranges into formatted tables.
• Implement headers and footers in tables, customize their features such as total rows, and use structured references.
• Name tables within Excel to streamline references and improve clarity in formulas.
• Apply various formatting options to enhance the visual appeal of tables, including banded rows and colored headers.
• Reorganize data into a tidy format, adhering to the principles of tidy data for more effective analysis.
• Integrate Excel tables with Power Query for data transformation tasks, further leveraging the structured references for automated updates and calculations.

Chapter 2: First Steps in Excel Power Query

By the end of this chapter, you’ll understand:

• How Power Query serves as a cornerstone feature in modern Excel, simplifying the data import and cleansing process by enabling users to connect to, combine, and refine data from a wide array of sources.
• How Power Query effectively addresses Excel’s traditional limitations regarding reproducibility, handling of null values, and processing of large datasets.
• The structure and functionality of the Power Query Editor, including the Ribbon Menu, Data Preview Options, and the Applied Steps list, which meticulously tracks and records every transformation step.
• A comprehensive understanding of the ETL (Extract, Transform, Load) process, which involves extracting data from a source, transforming it to meet analytical needs, and loading it into Excel for utilization.

And you’ll be able to:

• Perform data extraction from various sources, utilize Power Query’s transformation capabilities to clean and restructure data, and finally load processed data back into Excel.
• Familiarize yourself with using the Power Query Editor to add new columns, apply transformations, and understand the implications of each step with the help of the Applied Steps list.Employ Power Query’s data profiling options to check data quality, understand data distribution and spot data issues like missing values, errors, or unusual data points.
• Address and debunk common myths associated with Excel’s capabilities using Power Query, such as issues with data reproducibility, the inability to handle null values, and limitations on processing large datasets.

Chapter 3: Transforming Rows in Power Query

By the end of this chapter, you’ll understand:

• The functionality of Power Query as an ETL tool within Excel for manipulating rows, including sorting, filtering, and removing duplicates and errors.
• How to use Power Query to handle missing values and correct data entries efficiently.
• The benefits of using Power Query for data transformation tasks over traditional Excel methods, which are often less repeatable and more error-prone.
• Methods for refreshing queries in Power Query to ensure data remains up-to-date with minimal effort.
• Techniques for splitting data into rows using delimiters and filling down data to correct and complete datasets.
• How to replace headers and fill in missing cell values automatically using Power Query, simplifying what would otherwise be manual and tedious adjustments.

And you’ll be able to:

• Load and prepare data for cleaning by removing blanks, duplicates, and sorting entries alphabetically in Power Query.
• Identify and correct typos and misprints in datasets to improve data accuracy.
• Refresh and update data transformations in Power Query with new or changed data entries efficiently.
• Use the Split Column feature in Power Query to transform comma-separated entries into individual rows, enhancing the structure and usability of the data.
• Trim unwanted spaces from data entries to maintain clean data formats.
• Automate the replacement of column headers and the filling of blank rows in datasets to ensure consistency and completeness across data records.
• Execute complex transformations and prepare data for analysis by generating PivotTables from cleaned datasets.

Chapter 4: Transforming Columns in Power Query

By the end of this chapter, you’ll understand:

• How to transform text columns to proper, uppercase, and lowercase formats in Power Query and ensure proper spacing using the Replace Values feature.
• The process of delimiting text into columns using Power Query’s Split Column by Delimiter feature and how it differs from Excel’s Text to Columns.
• How to change data types in Power Query for various columns, such as converting numbers to text or currency, to better suit the data’s purpose.
• Various ways to work with date columns in Power Query, including extracting year, month, and day into separate columns.
• How to create custom columns in Power Query using the M language, particularly for calculating ratios like winning percentages.
• The distinction between data types in Power Query and cell formatting in Excel, emphasizing how changes in Power Query affect data storage but not Excel display formatting.
• The differences and appropriate uses of calculated columns versus DAX measures in Power Pivot, especially in the context of data aggregation and pivot tables.
• Techniques for reshaping data using the Unpivot feature in Power Query to convert wide data (multiple columns) into long format (key-value pairs).

And you’ll be able to:

• Utilize Power Query to modify the case of text in columns and implement spacing corrections.
• Split text into multiple columns based on delimiters and rename these columns appropriately in Power Query.
• Change the data types of columns in Power Query to match the data’s intended use, such as text or currency.
• Delete unnecessary columns from data queries in Power Query and recover them if needed.
• Duplicate and transform date columns into separate year, month, and day columns, and understand how to format these correctly.
• Create and configure custom columns in Power Query using formulas written in the M language, and set their data type to reflect their content, such as percentage.
• Correctly apply data types in Power Query and understand the implications for Excel’s cell formatting.
• Choose between using calculated columns and DAX measures depending on the analysis needs, especially when working with PivotTables.
• Reshape data from wide to long formats using the Unpivot function in Power Query, facilitating more flexible data analysis options.

Chapter 5: Merging and Appending Data in Power Query

By the end of this chapter, you’ll understand:

• How to merge and append data from multiple sources using Power Query in Excel, including files and external databases.
• The differences between various types of joins (e.g., left outer join, inner join) and when to use each.
• How to connect to external Excel workbooks and .csv files for data consolidation.
• The importance of maintaining clean and organized data in Power Query to simplify data analysis tasks.

And you’ll be able to:

• Append multiple data sources into a single dataset using Power Query.
• Perform left outer and inner joins to consolidate data from different tables based on common keys.
• Manage, group, and view dependencies of queries within Power Query to maintain a well-organized data analysis workflow.
• Navigate and execute operations within the Power Query interface, including merging queries and handling various data types.
• Transform and clean datasets in Power Query before loading them into Excel to ensure data integrity.
• Use Power Query’s data profiling tools to inspect the results of data merges and appends, verifying the accuracy and completeness of the data.

Chapter 6: First Steps in Power Pivot

By the end of this chapter, you’ll understand:

• The fundamental concepts and advantages of using Power Pivot as a data modeling and analysis tool integrated within Excel.
• How Power Pivot allows for the creation of complex data models by establishing relationships across multiple data tables without needing to physically merge them.
• The comparative benefits and limitations of using Power Pivot versus traditional lookup functions (like VLOOKUP() or XLOOKUP()) and Power Query for data analysis in Excel.
• The role of the DAX formula language in creating intricate calculations, measures, and key performance indicators (KPIs) within the Power Pivot environment.
• How Power Pivot enhances Excel’s capabilities by enabling advanced data manipulation, which surpasses the functionalities of simple Excel formulas and Power Query transformations.

And you’ll be able to:

• Set up and access the Power Pivot add-in from within Excel to start using its features.
• Navigate the Power Pivot interface to manage data relationships and create robust data models.
• Use the Data Model to integrate multiple data sources effectively, utilizing the relationships feature to enhance data analysis without duplicating data.
• Create and utilize DAX measures and KPIs to perform advanced data analysis tasks that involve aggregating, calculating, and analyzing data across various dimensions.
• Understand and apply different methods for combining data sources in Excel, recognizing when to use Power Pivot relationships versus Power Query merges.
• Avoid common pitfalls associated with Excel’s traditional lookup functions by leveraging Power Pivot’s relational data capabilities.

Chapter 7: Creating Relational Models in Power Pivot

By the end of this chapter, you’ll understand:

• The fundamental concepts of Power Pivot and how it can be used effectively for data analysis and reporting, particularly with relational data modeling from multiple sources.
• How to connect data to Power Pivot using Power Query to enhance data cleaning processes before data modeling.
• The process of creating relationships among tables in Power Pivot to streamline data analysis, reduce redundancy, and simplify dataset management.
• The identification and utilization of fact and dimension tables within Power Pivot to create comprehensive Data Models that are scalable and maintainable.
• The concepts of cardinality and filter direction in relational data models, and how they influence data integrity and query efficiency.

And you’ll be able to:

• Navigate and use Power Pivot for data modeling, including the use of Diagram View and Data Model management functionalities.
• Create and manage relationships between tables efficiently using the Power Pivot interface.
• Identify and designate fact and dimension tables appropriately to optimize the structure of your Data Model.
• Utilize Power Pivot’s functionalities to format data properly, enabling clearer and more meaningful data presentation.
• Implement hierarchical organization within the Data Model to facilitate detailed and multi-level data analysis.
• Load and transform Data Models into insightful PivotTables and PivotCharts, showcasing the practical application of modeled data in Excel.
• Prepare and adapt your Power Pivot Data Models for integration with Power BI for more advanced data visualization and analysis capabilities.

Chapter 8: Creating Measures and KPIs in Power Pivot

By the end of this chapter, you’ll understand:

• The difference between implicit and explicit DAX measures in Power Pivot.
• How to create implicit measures by dragging fields directly into PivotTables and how Power Pivot automatically generates and manages these measures.
• The process of creating explicit measures using the Power Pivot Measures option and the benefits of explicit measures in terms of customization and advanced calculations.
• The importance of KPIs (Key Performance Indicators) in tracking business performance and how to create them in Power Pivot.

And you’ll be able to:

• Create implicit measures by aggregating data directly in PivotTables and understand their limitations in customization and reusability.
• Create explicit measures using the Power Pivot Measures option, including formatting, validating, and editing them.
• Define KPIs based on explicit measures to track performance against targets and set status thresholds to provide context.
• Apply KPIs to PivotTables for visual representation of performance metrics.

Chapter 9: Intermediate DAX for Power Pivot

By the end of this chapter, you’ll understand:

• The purpose and functionality of the CALCULATE() function in Power Pivot
• How CALCULATE() can modify filter contexts to enable more advanced analysis in PivotTables
• Techniques for incorporating single and multiple criteria into CALCULATE() measures
• The role of the ALL() function in conjunction with CALCULATE() to clear filter contexts
• How to leverage time intelligence functions in Power Pivot for trend analysis and comparison
• The importance of adding a calendar table to facilitate time-based calculations

And you’ll be able to:

• Craft CALCULATE() measures with single and multiple criteria to enhance PivotTable reporting
• Apply time intelligence functions, such as TOTALYTD() and SAMEPERIODLASTYEAR(), to analyze trends in Excel
• Add a calendar table to the Data Model and establish relationships for accurate time-based analysis

Chapter 10: Introducing Dynamic Array Functions

By the end of this chapter, you’ll understand:

• The fundamental differences between traditional static array references and dynamic array references in Excel.
• How dynamic array functions can automatically adjust and expand to accommodate data changes without the need for manual updates.
• The key advantages of using dynamic array functions for real-time data analysis and manipulation over traditional methods.
• How the FILTER(), SORTBY(), and XLOOKUP() functions provide enhanced capabilities for filtering, sorting, and looking up data dynamically in Excel.

And you’ll be able to:

• Create and manipulate dynamic arrays using functions like SORT(), FILTER(), and UNIQUE() to manage and analyze data more efficiently.
• Implement dynamic array functions to automatically update and handle array sizes based on data changes, removing the need for manual adjustments.
• Utilize the XLOOKUP() function to perform more flexible and powerful lookups compared to traditional functions like VLOOKUP().
• Apply dynamic array functions to practical scenarios in Excel, such as creating dynamic drop-downs and handling real-time data updates in dashboards.

Chapter 11: Augmented Analytics and the Future of Excel

By the end of this chapter, you’ll understand:

• The concept of augmented analytics and its role in the future evolution of Excel in a data-driven world.
• How the growing complexity of data impacts the functionality and applicability of Excel in business and analytics.
• The limitations of traditional Excel in handling large, unstructured, or real-time data and the importance of AI and machine learning in overcoming these challenges.
• The enhancements brought by augmented analytics features like the Analyze Data feature, XLMiner, and optical character recognition in Excel.
• The process of conducting sentiment analysis within Excel using Azure Machine Learning and the implications of AI integration in traditional spreadsheet software.

And you’ll be able to:

• Use augmented analytics tools within Excel to handle and analyze complex data sets more efficiently.
• Apply the Analyze Data feature in Excel to generate AI-powered insights from structured data.
• Build predictive models using the XLMiner add-in, understanding its interface and capabilities within Excel.
• Utilize optical character recognition to convert image-based data into editable spreadsheet formats for further analysis.
• Perform sentiment analysis using Azure Machine Learning to evaluate text data directly within Excel, understanding the importance of data schema for AI effectiveness.
• Assess and interpret the output from these AI-enhanced tools to make informed decisions and enhance business strategies.

Chapter 12: Python with Excel

By the end of this chapter, you’ll understand:

• The significant role Python plays as a “glue” language in the modern analytics environment, integrating seamlessly with Excel and other platforms.
• The advantages of using Python with Excel over traditional Excel methods for automating tasks, creating complex calculations, and managing large datasets.
• How to utilize Python libraries like pandas and openpyxl to enhance data manipulation capabilities beyond Excel’s native features.
• The practical steps to integrate Python into Excel tasks, covering installation, setup, and basic script execution.

And you’ll be able to:

• Install Python and set up a programming environment to start automating Excel tasks using Python scripts.
• Use pandas for advanced data cleaning and analysis tasks that are cumbersome or not possible with Excel alone.
• Create, format, and manipulate Excel files using openpyxl to automate routine reporting and data handling tasks.
• Combine Python’s powerful data visualization tools with Excel to create insightful charts and reports.
• Develop robust Excel applications using Python to handle real-world data analysis challenges efficiently.

I hope you see that this book covers a wide range of material and is intended as an introductory resource for Excel users who are unfamiliar with topics such as tables, Power Query, or AI-powered Excel. Please take a moment to review it and share your thoughts with the community in the form of a review.

What questions do you have about the book or the topics presented in it? Let me know in the comments.