Guerrilla Data Analysis 2nd Edition
May 2015
Stop Being a Victim of Bad Data!
Data is coming from everywhere, about all kinds of things. All the cool kids are talking about big data, data science, and predictive analysis. But data also presents everyday problems that aren’t as glamorous as millions of rows of data and Nate Silver coming to tell us the future. Data analysis involves both the small, stupid stuff and big, complicated stuff.
Add to Cart: $9.99 »Product Details
- 247 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1-61547-033-4
- PDF ISBN: 978-1-61547-228-4
This book includes step-by-step examples and case studies that teach users the many power tricks for analyzing data in Excel. These tips have been honed by Oz du Soleil, a former financial analyst charged with taking mainframe data and turning it into useful information quickly, and "MrExcel" himself, Bill Jelen. Topics include data quality, validation, perfectly sorting with one click, matching lists of data, data consolidation, data subtotals, pivot tables, pivot charts, tables, and much more. This new edition has been updated for Excel 2013.
- Introduction: Welcome to the World of Guerrilla Data Analysis!
- In The Heat of Conflict
- Small, Stupid Stuff
- Big, Complicated Stuff
- How to Use This Book
- Download Files
- Reviewing the Basics
- Changing Formulas to Values
- Using Paste Special in Other Ways
- Transposing Columns and Rows
- Performing a Calculation on Every Cell in a Range
- Using Helper Columns
- Using Relative, Absolute, and Mixed References
- Developing Dynamic Spreadsheets
- Conditional Formatting
- Using Conditional Formatting to Make Deadline Alerts
- Using Conditional Formatting to Find Duplicates
- Using Icons with Conditional Formatting
- Using IF Statements
- Using an IF Statement with COUNTIF
- Sorting
- Rules for Sorting
- The Data Range Must Be Contiguous (No Completely Blank Rows or Columns)
- Headings Must Be Only One Cell Tall
- Merged Cells Do Not Sort
- Using Sorting
- Using the Quick-Sort Icons
- Understanding Ascending and Descending Sorts
- Let’s Get Some Data Sorted Out
- Sorting with Two Criteria
- Sorting with the Help of a Helper Column
- Filtering
- What You Need to Know About Filtering Before You Do It
- Getting Down to Filtering Business
- What’s In the Data Set?
- Finding Records Quickly with AutoFilter
- Filtering to Find the Top (or Bottom) Five Transactions
- Faster Filtering with Filter by Selection
- Creating Somewhat Complex Queries by Using the (Custom) AutoFilter
- Filtering Dates Using a Custom AutoFilter
- Using Advanced Filter
- Advanced Filter Example 1: Filtering in Place
- Advanced Filter Example 2: OR vs AND Advanced Filtering and Copying to a New Location
- Advanced Filter Example 3: Copying Only Certain Fields to Another Location
- Advanced Filter Example 4: Filtering Unique Records Only
- Advanced Filter Example 5: Conditions Created as the Result of a Formula
- Advanced Filter Example 6: Replacing 362,880 Conditions
- Filtering Conclusions
- Using Consolidate
- Using Consolidate to Combine Duplicates in Column 1
- Using Consolidate to Add New Data to Old Data
- Using Subtotals
- Copying Only the Subtotals
- Removing Subtotals
- Adding Additional Subtotals
- Warning: Be Careful How You Subtotal!
- Summing and Counting Using Criteria
- Using SUMIF
- Using SUMIFS and COUNTIFS
- Matching Lists of Data
- Comparing What’s Been Shipped and What’s Been Received
- Matching Reps and Rep IDs Using VLOOKUP
- VLOOKUP Using TRUE
- Looking Left, Right, and All Around: INDEX and MATCH
- Using INDEX and MATCH
- Using INDEX/MATCH/MATCH
- Using Pivot Tables
- What Is a Pivot Table?
- Example 1: Summing Values with a Pivot Table
- Example 2: Counting Values with a Pivot Table
- Example 3: Filtering with a Pivot Table
- Example 4: Using a Pivot Table to Find a Sum and an Average at the Same Time
- Creating a Pivot Table
- Summing Values with the Pivot Table
- Filling Blanks with Zero
- Counting Values with the Pivot Table
- Filtering with the Pivot Table
- Grouping Dates in the Pivot Table
- Grouping by Week in a Pivot Table
- Creating a Year-over-Year Report in a Pivot Table
- What is the Point of GetPivotData?
- Using the Pivot Table to Get a Sum & Average at the Same Time
- Using the Pivot Table to Get the Percentage of the Total
- Using the Pivot Table to Filter for the Top Five
- Using the Pivot Table to Drill Down for Isolated Details
- Making Many Copies of a Pivot Table
- Deleting a Pivot Table
- Overriding the Default Row Sequence in a Pivot Table
- Rearranging Pivot Table Headers
- Using Calculated Items & Calculated Fields
- Working with Calculated Items
- Working with Calculated Fields
- Final Notes on Calculated Items and Calculated Fields
- Pivot Table Q&A
- Pivot Table Conclusions
- Using Array Formulas
- Basic Array Formula
- You Cannot Change Part of an Array
- Copying an Array Formula
- Modifying an Array Formula
- Using FREQUENCY to Create a Histogram
- Going One Step Further: An Array Inside an Array Formula
- Array Formulas and System Memory
- Stepping Up to Excel Tables
- Converting a Data Range to a Table
- Adding New Data to a Table
- Adding a New Column
- Using SUMIFS with Table References
- Using the Table Design Tab
- Other Cool Table-Related Stuff
- Some Warnings About Working With Tables
- Mixing Formulas in a Column
- Adding New Data to a Table
- Sheet Protection: Tables Must Be Completely Protect or Completely Unprotected
- Excel Tables Conclusion
- Using the INDIRECT and OFFSET Functions
- Using INDIRECT
- Using INDIRECT with VLOOKUP
- Using INDIRECT in an Array Formula
- Using OFFSET
- Controlling Data Inputs and Maintaining
- Data Integrity
- Data Validation Overview
- Implementing Dropdown Lists
- Controlling Dates
- Ensuring Reasonable Numbers
- Preventing Start and End Times from Being Reversed
- Data Validation Conclusions
- Implementing Error-Handling and Formula Triggers
- Error-Handling Example
- Formula Trigger Example
- Error-Handling Functions: IFNA vs. IFERROR
- Using Pivot Charts
- Playing Around with a Pivot Chart
- Adding New Data to a Pivot Chart
- Changing the Chart Type
- Using Slicers with Pivot Charts
- Using Slicers
- Excel 2013: Guerrilla Data Analysis Gets Real
- Using Slicers with Tables in Excel 2013
- Understanding Data Models and Relationships
- Graphing aand Charting
- Using Excel on the Internet
- Embedding Excel in a Blog Post
- Differences between Excel Web App and Desktop Excel
- Down and Dirty Tips and Insights
- Overview of Excel Formulas and Functions
- Forcing a Report to Fit on One Page
- Boolean Logic
- Using & to Mix Text and Numbers in the Same Cell
- Using Text-Manipulation Functions
- Handling Dates
- Handling Time
- Converting All Results to Minutes
- Useful Excel Functions
- Using PMT to Predict a Loan Payment
- Using FORECAST
- Using RANK
- Using RANK, RANK.EQ, and RANK.AVG
- Breaking Ties Based on Position in a List
- Using CEILING and FLOOR
- Using MAX, MIN, LARGE, and SMALL
- Using COUNTA
- Using CONVERT
- Using ABS to Compare Errors in Absolute Terms
- Using RAND and RANDBETWEEN
- Using RANDBETWEEN and INDEX to Make Random Assignments
- Using CHOOSE
- Putting CHOOSE to Work
- Using SUMPRODUCT
- Using EOMONTH
- Troubleshooting Excel
- Quickly Checking Sums and Averages
- Volatile, Slow, and Peculiar Functions and Features
- Using SUM vs. Adding Individual Cells
- Troubleshooting by Using CTRL+` (also known as Ctrl+~)
- Crossfooting
- Using the Formula Evaluator
- Troubleshooting by Checking Highlighted Ranges in a Formula
- Unhiding Column A
- Getting Rid of Gridlines
- Linking
- Linking Between Worksheets
- Linking One Cell to Another on a Different Worksheet
- Using a VLOOKUP with References to Another Worksheet
- Inserting Table References Between Worksheets
- Linking Workbooks
- Spreadsheet Layout and Development
- Digging into the Details of the Layout
- Taking Advantage of Good Spreadsheet Layout
- Adding Some Data and Making a Pivot Table
- Using Keyboard Shortcuts
- Quickly Navigating Using the Ctrl or End Key
- Navigating Between Worksheets
- Formatting Shortcuts
- Clipboard Shortcuts
- Calculation Shortcuts
- Editing Shortcuts
- Excel Commands
- F4 Repeats Last Command
- The Amazing F4 Key (While Editing a Formula)
- Using Arrow Keys to Enter a Formula
- Wrap-Up
