Guerrilla Data Analysis Using Microsoft Excel: Overcoming Crap Data and Excel Skirmishes, 3rd Edition
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
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.