MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Guerilla Data Analysis


September 2002

Guerilla Data Analysis

This is a book that I will read, chew, and digest many times. Your real-life examples and anecdotes were excellent. The exposition on pivot tables transcends any other writing I have read about them.

Customer Review

category: Spreadsheets  
covers: Excel

Product Details
  • eBook:  108 Pages + 38 Page Appendix
  • Publisher: Holy Macro! Books
  • ISBN: 0972425802

You wouldn't normally designate a technical Book like this as a page-turner, but if you're someone who has to process large amounts of data and turn it into information that's critical to a management decision then trust me - it is a page-turner!

If you could have seen me while I read this book, you would have seen me nodding my head in agreement and muttering phrases like "How did you know?!" or "Yes, yes, yes - that's exactly what happens!" or "So that's how you do that!' Bill Jelen - where were you during the preparation for my last staff meeting??

But no more! Now I have this remarkable "Everyman's Tool" for Excel and I'm going to be a hero. In fact, I’m getting another copy because someone is bound to steal this one or it will just plain wear out from use.

Don't believe me - go ahead and read it for yourself and you'll see what I mean.

Bill Jelen uses his combined experience and analytical ingenuity to de-mystify the arduous task of dealing with downloaded data. He uses real-life examples of real-life management requests, and then walks you through the maze of Excel tools and formulas that not only cuts valuable time out of the process, but teaches you in plain English how to overcome the most common analytical obstacles.

Now you can truly unleash the power of Excel and get the world's most powerful analytical tools to work for you - instead of the other way around.

  • Introduction
  • In the trenches
  • Changing formulas to values
    • Other uses for Paste Special

      • Performing a calculation on every cell in a range
      • Changing text that looks like numbers to real numbers
      • ransposing a column to a row
      • Preserving borders
  • Multiple customer names/single customer number
    • VLOOKUP

      • VLOOKUP left
      • Avoiding #NA in VLOOKUP
  • All sorts of sorts
    • Sorting rules

      • Rule 1: Columns must have headings
      • Rule 2: Headings must be only one cell tall
      • Rule 3: No completely blank rows or columns
      • Rule 4: No named ranges called "Database"
    • Comparing sort techniques
  • Matching two lists of data
    • Building a formula for forecast balance
    • Eliminating rounding errors
    • Calculating total expected
    • Manually catching errors
    • Automated error checking
    • Conclusion
  • Data->Consolidate
  • Data->Subtotals and Go To Special
    • Subtotaling data
    • Go To Special
    • To remove subtotals
    • To add additional subtotals
    • Subtotals bug in Excel 7.0 (95)
  • Filtering Data
    • Finding records quickly with AutoFilter

      • Finding all records for a particular field
      • Finding all records for a different customer
      • Finding all records for a particular product
      • Filtering based on two fields
      • Filtering to find Top 10 (or Top 3%) records
      • Combining a top 10 revenue filter with a customer filter does not work
      • Creating somewhat complex queries using the (Custom) AutoFilter
      • Filtering dates using Custom AutoFilter
      • Removing an AutoFilter (removing the dropdown boxes)
      • Limitations with AutoFilter
      • One incredibly cool trick with VBA and AutoFilters
    • Using Advanced Filter

      • Advanced Filter example 1 - Filter in place
      • Advanced Filter example 2 - Copy to another location
      • Advanced Filter example 3 - Copy only certain fields to another location
      • Advanced Filter example 4 - Unique records only
      • Advanced Filter example 5 - Conditions created as the result of a formula
      • Advanced Filter example 6 - Solving the 362,880 condition problem
      • Conclusion
  • SUMIF and COUNTIF
    • SUMIF
    • SUMIF with two criteria
    • COUNTIF
  • The keys to the kingdom: CSE
    • Using array formulas
    • Copying array formulas
    • Advanced array formulas
    • Finding CSE in the help files
    • Array formulas and their impact on overhead
  • Pivot tables
    • Pivot table capabilities
    • How to create a pivot table
    • Things to note about pivot tables
    • Intermediate tips, tricks, and troubleshooting

      • Change a pivot table on the fly
      • "Count of Sales" instead of "Sum of Sales"
      • Error messages
      • How to delete a pivot table
      • Double-click any pivot table cell to zoom in
      • Pre-specify sequence of pivot table
      • Manually specify sequence of pivot table
      • Report top 10 customers per region
      • Automatically group dates by month, quarter, year
      • Calculated items and calculated fields: useful, but not perfect
    • Pivot table Q & A
  • INDIRECT and OFFSET functions
    • INDIRECT function
    • Advanced INDIRECT
    • Using INDIRECT with an array formula
    • Using INDIRECT as an Argument for Another Function
    • OFFSET Function
  • Remedial reading
    • Relative, absolute, and mixed references

      • Cell referencing tips
    • Use the End key to "ride the range"
    • Nesting formulas
    • IF functions

      • Nested IF statements
      • Using AND and OR in IF statements
    • Use Boolean logic facts

      • Use the & sign to concatenate text and numbers
    • Page Setup forces reports to fit
    • Text manipulation formulas
    • Date handling
    • Quickly see Sum/Average of a range

      • Rank values without sorting
    • Customizing Any Chart Feature with a Right-Click
  • Introduction to Excel functions
    • Predict your car loan payment with PMT()
    • Round prices up with CEILING()
    • Convert between units of measurement with CONVERT()
    • Present bad financial news in Roman numerals with ROMAN()
    • Compare errors in absolute terms with ABS()
    • Use random numbers to provide simulations with RAND()
    • Get sales forecasts with =FORECAST()
    • Figure out the slope of a line with LINEST()
    • Rewrite the CSE chapter with SUMPRODUCT()
    • Choose from a list with CHOOSE()
    • Test to see if a case is full with ISEVEN()
    • Put 7th grade math teachers out of a job with GCD(), LCM(), FACT()
    • Impress your Lotus 1-2-3 friends with DAVERAGE()
    • There are many more cool functions!
  • Appendix: Quick function reference

Look Inside

Here are some sample pages:

  • Excerpt (Page 6)

    Excerpt (Page 6)
    Excerpt (Page 6)
  • Excerpt (Page 67 - Pivot Tables)

    Excerpt (Page 67 - Pivot Tables)
    Excerpt (Page 67 - Pivot Tables)
  • Excerpt (Page 100 - Remedial Reading)

    Excerpt (Page 100 - Remedial Reading)
    Excerpt (Page 100 - Remedial Reading)

Data Files Used in the Book

This was the first book that I wrote and I was not smart enough to save the data files as I wrote. However, I now teach concepts from this book in my Excel Tip &Tricks lunchbox seminar. This gdafile.zip contains three worksheets that contain suitable data to test subtotals, consolidation, autofilter and pivot tables.

Where to Buy

MrExcel Store

PDF from MrExcel.com Store:

e-Book from MrExcel »

Other Editions