Guerilla Data Analysis

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