Guerrilla Data Analysis Using Microsoft Excel: Overcoming Crap Data and Excel Skirmishes, 3rd Edition

Length: 274 pages
Edition: 3
Language: English
Publisher: Holy Macro! Books
Publication Date: 2022-08-01
ISBN-10: 1615470743
ISBN-13: 9781615470747
Sales Rank: #1644802 (See Top 100 Books)

Bill Jelen, MrExcel and Oz du Soleil of Excel on Fire, two of the leading Excel channels on YouTube, joined forces to write a unique book to help you get the most from Excel and combat bad data.

Guerrilla Data Analysis Using Microsoft Excel: Excel Skirmishes and Conquering Crap Data Third Ed. goes beyond Excel tips & tricks and includes real world warnings and case studies.
When asked about the motivation for this book, Oz replied: “this is for the person who’s been thrown into the fire with Excel and data. They may not have a background for this work but they still have to perform because the world doesn’t wait for you to graduate from beginner to intermediate and then to advanced. No. You have to perform and deliver.”
In other words, GDA3 is a survival guide whether you’re new to Excel or a longtime professional who needs a reference guide. The book is light and quick, giving you bite-sized lessons, but going deep on critical topics like pivot tables and Power Query.

Introduction: Welcome to the World of Guerrilla Data Analysis!
    About This Book
    Blindsided by Data
    Small, Stupid Stuff and Big, Complicated Stuff
Chapter 1: Reviewing the Basics
    Overview of Excel Functions and Formulas
    Relative, Absolute, and Mixed References
    Text Manipulation Functions
    IF Statements
    Developing Dynamic Spreadsheets
    Concatenating Names and Changing Formulas to Values
    Linking Worksheets and Workbooks
    Helper Columns
    Sorting and Filtering
Chapter 2: Excel Tables: The Glue in Dynamic Spreadsheet Development
    Converting a Data Range to a Table
    Using a Total Row
    Naming a Table
    Using Tables to Make Dynamic Dropdown Lists
    Tables Functions and Cell References
    Some Warnings About Working with Tables
    Excel Tables Conclusion
Chapter 3: Collaboration Tools
    How to Share a Workbook
    The Awesome Part of Collaboration: Sheet Views
Chapter 4: Summing and Counting with Criteria
Chapter 5: VLOOKUP and XLOOKUP
    VLOOKUP: What Does It Do?
    XLOOKUP
Chapter 6: Pivot Tables: The Turning Point!
    What Is a Pivot Table, and What Can It Do?
    Getting to Know the Pivot Table Interface
    Building a Pivot Table to Sum and Count Values
    Summing and Counting Side-by-Side … and a Filter
    Filtering with the Pivot Table
    Grouping Dates in the Pivot Table
    Using the Pivot Table to Get the Percentage of the Total
    Pivot Table Percentages Without Totals
    Using the Pivot Table to Drill Down for Isolated Details
    Deleting a Pivot Table
    Saving Your Favorite Pivot Table Settings Using Pivot Table Defaults
    Creating a Year-over-Year Report in a Pivot Table
    Counting Distinct Values in a Pivot Table
    Pivot Table Conclusions
Chapter 7: Power Query
    Power Query: A Little Background
    Filling Down and Splitting Columns by Delimiter
    Splitting Column into Rows, Grouping By, and Duplicating a Query
    Data Types and Power Query
    Sorting in Power Query
    The Query Settings Pane
    Adding More Source Data
    Unpivoting and Filtering
    Blanks, Nulls, and Zeros: They Aren’t the Same in Power Query
    Joins and Merges in Power Query
    Appending (aka Stacking Stuff Up)
    Importing from a File or from a Folder
    Transformation Tables
    Fuzzy Matching
Chapter 8: Conditional Formatting
    Using Conditional Formatting to Find Duplicates
    Using Icons with Conditional Formatting
Chapter 9: De-duping in Excel
    De-duping with Advanced Filter
    De-duping Gets Ugly!
    Using IF to “LOOK”
    De-duping with an Assembled ID
Chapter 10: Dynamic Arrays
    SORT
    FILTER
    RANDARRAY
    UNIQUE
    The @ Operator, Briefly Known as SINGLE
    The Spill Indicator
Chapter 11: Data Is Never 100% Clean (Not for Very Long)
Chapter 12: Data Validation: Controlling Inputs and Maintaining Data Integrity
    Data Validation Overview
    Implementing Dropdown Lists
    New in 2022: AutoComplete in Validation Dropdown Lists
    Controlling Dates
    Reasonable Numbers
    Data Validation Cautions
    Data Validation Conclusions
Chapter 13: Protecting Sheets and Cells
    Locking Down an Entire Sheet
    Locking and Unlocking Cells
    Unprotecting a Sheet
Chapter 14: Octopus Spreadsheets
Chapter 15: INDIRECT
Chapter 16: OFFSET
    Using OFFSET to Sum a Range
Chapter 17: Recognizing Patterns
Chapter 18: Data Types and Stock History
    Original Release Data Types
    Second Release Data Types: Wolfram
    Data Types: Navigating the Data Card
    Custom Data Types: Features Needed
    Custom Data Types: The Choices Available Today
Chapter 19: Graphing
    Graphing a Histogram Using the FREQUENCY Function
    Using Chart Features
Chapter 20: The Dangers of Just Diving In
Chapter 21: The LET Function
    LET for Reusing Parts of Formulas
    LET for Easier Readability
Chapter 22: Warnings About Machine Learning–Driven Features in Excel and Power Query
Chapter 23: Avoid Working on Your Source Data
Chapter 24: Using Slicers
    Using Slicers with Tables
    Pivot Tables and Slicers
Chapter 25: Data Models and Relationships
    Foreign and Primary Keys
    Why a Data Model vs. Power Query?
Chapter 26: People, Processes, and Tools
Chapter 27: Keeping Your Data in as Few Places as Possible
Chapter 28: Rough-and-Tumble Tips and Insights
    Unhiding Column A
    Formula Triggers
    Adding Emojis to Cells and Formulas
    Hiding Unnecessary Zeros
    Forcing a Report to Fit on One Page
    Setting the Print Area to Print a Section of a Worksheet
    Alt+Enter for an Extra Line in a Cell
    Handling Dates
    Connecting Cell Values to Shapes or Objects
    Useful Excel Functions
    Integrity Checks and Troubleshooting
    Error-Handling Functions: IFNA vs. IFERROR
    Row Counts
Chapter 29: Spreadsheet Layout and Development
    A Final Word About Spreadsheet Layout and Development
Index