Excel Data Analysis Your Visual Blueprint To Analyzing Data, Charts and Pivot Tables

Excel Data Analysis: Your visual blueprint™ for creating and analyzing data, charts, and PivotTables......Page 1
CREDITS......Page 7
ABOUT THE AUTHOR......Page 8
AUTHOR'S ACKNOWLEDGMENTS......Page 9
TABLE OF CONTENTS......Page 10
The Organization of This Book......Page 14
What's on the CD-ROM......Page 15
EXCEL DATA ANALYSIS OPTIONS......Page 16
EXCEL DATA TYPES......Page 18
LOCATE A VALUE IN A WORKSHEET......Page 20
SELECT A RANGE OF CELLS......Page 21
NAME A RANGE......Page 22
CREATE LABEL RANGES......Page 24
MODIFY NAMED RANGES......Page 26
COPY AND PASTE A RANGE OF CELLS......Page 28
CREATE A CUSTOM NUMBER FORMAT......Page 30
APPLY AUTOFORMAT TO A WORKSHEET......Page 32
CREATE A NAMED STYLE......Page 34
CREATE A CUSTOM TEMPLATE......Page 36
PROTECT WORKSHEETS......Page 38
CREATE A LIST......Page 40
ADD A SERIES TO A LIST......Page 42
SORT A LIST......Page 44
CREATE A CUSTOM SORT......Page 46
CONSOLIDATE DATA......Page 50
OUTLINE YOUR DATA......Page 54
APPLY CONDITIONAL FORMATTING......Page 56
SUMMARIZE DATA WITH SUBTOTALS......Page 60
FILTER A LIST......Page 62
CREATE A CUSTOM FILTER......Page 64
CREATE AN ADVANCED FILTER......Page 66
CREATE SCENARIOS......Page 68
VALIDATE DATA......Page 70
SUM CELLS WITH THE AUTOSUM BUTTON......Page 72
ADD A FUNCTION VIA THE INSERT FUNCTION DIALOG BOX......Page 74
EDIT FORMULAS......Page 76
EVALUATE A FORMULA......Page 78
USING SOLVER TO PRODUCE SPECIFIC VALUES......Page 80
CREATE A CONDITIONAL FORMULA......Page 82
SOLVE A FORMULA WITH A DATA TABLE......Page 84
TRACE A FORMULA ERROR......Page 86
LOOK UP A VALUE IN A SPECIFIC ROW AND COLUMN......Page 88
DETERMINE THE LOCATION OF A VALUE......Page 90
RETURN A VALUE AT A SPECIFIC LOCATION IN A DATA LIST......Page 92
RANK A VALUE WITHIN A DATA LIST......Page 94
CREATE AN AMORTIZATION TABLE FOR A LOAN......Page 96
LINK DATA TO OTHER WINDOWS PROGRAMS......Page 98
IMPORT A DELIMITED TEXT FILE......Page 100
IMPORT A DELIMITED TEXT FILE......Page 102
DIVIDE A COLUMN INTO MULTIPLE COLUMNS......Page 104
CREATE A WEB QUERY......Page 106
IMPORT A DATABASE TABLE......Page 108
USING QUERIES TO SCREEN EXTERNAL DATABASES......Page 110
FIND THE AVERAGE OF A DATABASE RANGE......Page 116
SAVE FOR WEB......Page 118
CHART BASICS......Page 120
MODIFY A CHART TYPE......Page 124
EXPLODE SLICES OF A PIE CHART......Page 125
CREATE A CUSTOM CHART TYPE......Page 126
ADD A TRENDLINE TO A CHART......Page 128
ADD OR CHANGE NEW DATA TO A CHART......Page 130
CREATE A CHART WITH MULTIPLE CHART TYPES......Page 132
PIVOTTABLE BASICS......Page 134
A VIEW OF THE PIVOTTABLE REPORT FIELDS......Page 135
CREATE A PIVOTTABLE REPORT FROM AN EXCEL LIST......Page 136
CHANGE THE LAYOUT OF A PIVOTTABLE......Page 140
FILTER A FIELD......Page 142
CHANGE THE CALCULATION OF A DATA FIELD......Page 144
GROUP PIVOTTABLE DATA ITEMS......Page 146
ADD ANOTHER DATA AREA CALCULATION......Page 148
ADD A CALCULATED FIELD......Page 150
ADD A CALCULATED ITEM......Page 152
RETRIEVE A VALUE FROM A PIVOTTABLE REPORT......Page 154
CREATE A PIVOTCHART FROM AN EXTERNAL DATABASE......Page 156
CHANGE THE LAYOUT OF A PIVOTCHART......Page 160
CHANGE THE CHART TYPE OF A PIVOTCHART......Page 162
FILTER A PIVOTCHART FIELD......Page 164
GROUP ITEMS IN A PIVOTCHART FIELD......Page 166
ADD A DATA TABLE TO A PIVOTCHART......Page 168
AN INTRODUCTION TO MACROS......Page 170
RECORD A MACRO......Page 178
RUN A MACRO......Page 180
ACTIVATE THE VISUAL BASIC EDITOR......Page 182
CREATE A MACRO USING THE VISUAL BASIC EDITOR......Page 184
UPDATE A RECORDED MACRO......Page 186
SET MACRO SECURITY......Page 188
CREATE A CUSTOM FUNCTION......Page 192
DECLARE A VARIABLE......Page 194
EXECUTE A TASK A SPECIFIC NUMBER OF TIMES......Page 196
DISPLAY A MESSAGE BOX......Page 198
REQUEST USER INPUT FOR A MACRO......Page 200
ASSIGN A MACRO TO A MENU......Page 202
RUN A MACRO AS A WORKBOOK OPENS......Page 204
USING AN EXCEL FUNCTION IN A MACRO......Page 206
AN INTRODUCTION TO FORMS......Page 208
THE FORMS TOOLBAR......Page 209
VBA DIALOG BOX BASICS......Page 210
ADD A FORM CONTROL TO A WORKSHEET......Page 212
SPECIFY VALUES FOR A FORM CONTROL......Page 214
CUSTOMIZE FORM CONTROLS WITH MACROS......Page 216
CREATE A CUSTOM DIALOG BOX......Page 218
CALL A CUSTOM DIALOG BOX FROM A MACRO......Page 220
CAPTURE INPUT FROM A CUSTOM DIALOG BOX......Page 222
VALIDATE INPUT FROM A DIALOG BOX......Page 226
INSTALL EXCEL ADD-INS......Page 228
USING THE CONDITIONAL SUM WIZARD......Page 230
ANALYSIS OF VARIANCE FOR LISTS OF VALUES (ANOVA)......Page 234
FIND THE CORRELATION BETWEEN TWO SETS OF DATA......Page 236
DETERMINE THE COVARIANCE OF TWO SETS OF DATA......Page 238
CALCULATE DESCRIPTIVE STATISTICS......Page 240
COMPARE THE VARIANCES IN TWO GROUPS OF DATA......Page 242
CALCULATE A MOVING AVERAGE......Page 244
DETERMINE RANK AND PERCENTILE......Page 246
EDITING SHORTCUTS......Page 248
FORMATTING SHORTCUTS......Page 249
GENERAL PROGRAM SHORTCUTS......Page 250
PIVOTTABLE AND PIVOTCHART SHORTCUTS......Page 251
SELECTION SHORTCUTS......Page 252
WORKSHEET NAVIGATION SHORTCUTS......Page 253
DATABASE FUNCTIONS......Page 254
DATE AND TIME FUNCTIONS......Page 255
ENGINEERING FUNCTIONS......Page 256
FINANCIAL FUNCTIONS......Page 258
LOGICAL FUNCTIONS......Page 261
LOOKUP & REFERENCE FUNCTIONS......Page 262
MATH & TRIG FUNCTIONS......Page 263
STATISTICAL FUNCTIONS......Page 265
TEST FUNCTIONS......Page 269
VBA STATEMENTS QUICK REFERENCE......Page 270
VBA FUNCTION QUICK REFERENCE......Page 273
EXCEL EVENTS QUICK REFERENCE......Page 280
Appendix D: FORMULA BASICS IN EXCEL......Page 284
Appendix E: WHAT'S ON THE CD-ROM......Page 288
USING THE E-VERSION OF THIS BOOK......Page 290
WILEY PUBLISHING, INC. END-USER LICENSE AGREEMENT......Page 292
INDEX......Page 294