Guerilla Data Analysis Using Microsoft Excel by Soleil, Oz du -- Read -- Imperial Library of Trantor

Index

Dedications About the Authors

Oz du Soleil

Bill Jelen

Acknowledgements 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 Index

← Prev
Back
Next →

← Prev
Back
Next →