BOOK COVER -- HALF TITLE -- FULL TITLE -- COPYRIGHT PAGE -- TABLE OF CONTENTS -- ABOUT THE AUTHOR -- ACKNOWLEDGMENTS -- DEDICATION -- FOREWORD -- PART-I: THE EXCEL ENVIRONMENT -- SHOW FULL MENUS ALL THE TIME -- SHOW FULL TOOLBARS ALL THE TIME -- ADD A CLOSE BUTTON TO THE STANDARD TOOLBAR -- CLOSE ALL OPEN WORKBOOKS -- DOUBLE THE VALUE OF THE RECENTLY USED FILE LIST -- REMEMBER WORKBOOKS TO OPEN USING A WORKSPACE -- AUTOMATICALLY MOVE THE CELL POINTER IN A DIRECTION AFTER ENTERING A NUMBER -- HOW TO SEE HEADINGS AS YOU SCROLL AROUND A REPORT -- HOW TO SEE HEADINGS AND ROW LABELS AS YOU SCROLL AROUND A REPORT -- HOW TO PRINT TITLES AT THE TOP OF EACH PAGE -- PRINT A LETTER AT THE TOP OF PAGE 1 AND REPEAT HEADINGS AT THE TOP OF EACH SUBSEQUENT PAGE -- HOW TO PRINT PAGE NUMBERS AT THE BOTTOM OF EACH PAGE -- HOW TO MAKE A WIDE REPORT FIT TO ONE PAGE WIDE BY MANY PAGES TALL -- ARRANGE WINDOWS TO SEE TWO OR MORE OPEN WORKBOOKS -- WHY IS THERE A ":2" AFTER MY WORKBOOK NAME IN THE TITLE BAR? -- HAVE EXCEL ALWAYS OPEN CERTAIN WORKBOOK(S) -- SET UP EXCEL ICONS TO OPEN A SPECIFIC FILE ON STARTUP -- USE A MACRO TO FURTHER CUSTOMIZE STARTUP -- CONTROL SETTINGS FOR EVERY NEW WORKBOOK AND WORKSHEET -- OPEN A COPY OF A WORKBOOK -- OPEN A SAVED FILE WHOSE NAME YOU CANNOT RECALL -- SUPPRESS THE UPDATE LINKS MESSAGE -- SEND EXCEL FILE AS AN ATTACHMENT -- SAVE EXCEL DATA AS A TEXT FILE -- USE A LASER PRINTER TO HAVE EXCEL CALCULATE FASTER -- USE EXCEL AS A WORD PROCESSOR -- SPELLCHECK A REGION -- USE HYPERLINKS TO CREATE AN OPENING MENU FOR YOUR WORKBOOK -- GET QUICK ACCESS TO PASTE SPECIAL -- USE SHIFT KEY TO REVERSE POPULAR TOOLBAR ICONS -- CREATE A MENU OR A TOOLBAR OF YOUR FAVORITE ICONS -- RESTORE YOUR MENUS AFTER CUSTOMIZING -- QUICKLY COPY A FORMULA TO ALL ROWS OF DATA -- QUICKLY TURN A RANGE ON ITS SIDE -- STOP EXCEL FROM AUTOCORRECTING CERTAIN WORDS.

USE AUTOCORRECT TO ENABLE A SHORTCUT -- WHY WON'T THE TRACK CHAGES FEATURE WORK IN EXCEL? -- COPY CELLS FROM ONE WORKSHEET TO MANY WORKSHEETS -- HAVE EXCEL TALK TO YOU -- ENTER SPECIAL SYMBOLS -- FIND TEXT ENTRIES -- PART 2: CALCULATING WITH EXCEL -- COPY A FORMULA THAT CONTAINS RELATIVE REFERENCES -- COPY A FORMULA WHILE KEEPING ONE REFERENCE FIXED -- CREATE A MULTIPLICATION TABLE -- CALCULATE A SALES COMMISSION -- SIMPLIFY ENTRY OF DOLLAR SIGNS IN FORMULAS -- LEARN R1C1 REFERENCING TO UNDERSTAND FORMULA COPYING -- CREATE EASIER-TO-UNDERSTAND FORMULAS WITH NAMED RANGE -- USE NAMED CONSTANTS TO STORE NUMBERS -- BUILD A FORMULA USING LABELS INSTEAD OF CELL ADDRESSES -- USE NATURAL LANGUAGE FORMULAS TO REFER TO THE CURRENT ROW -- ASSIGN A FORMULA TO A NAME -- TOTAL WITHOUT USING A FORMULA -- COUNT, AVERAGE, ETC. WITHOUT USING A FORMULA -- ADD TWO COLUMNS WITHOUT USING FORMULAS -- HOW TO CALCULATE SALES OVER QUOTA -- HOW TO JOIN TWO TEXT COLUMNS -- HOW TO SORT ON ONE SEGMENT OF AN ACCOUNT ID -- HOW TO ISOLATE THE CENTER PORTION OF AN ACCOUNT ID -- HOW TO ISOLATE EVERYTHING BEFORE A DASH IN A COLUMN BY USING FUNCTIONS -- HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER A DASH IN A COLUMN -- HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER THE SECOND DASH IN A COLUMN -- HOW TO SEPARATE A PART NUMBER INTO THREE COLUMNS -- AVOID #REF! ERRORS WHEN DELETING COLUMNS -- CREATE RANDOM NUMBERS -- CREATE RANDOM NUMBERS TO SEQUENCE A CLASS OF STUDENTS -- PLAY DICE GAMES WITH EXCEL -- PLAY BUNCO WITH EXCEL -- PLAY CRAPS WITH EXCEL -- CREATE RANDOM LETTERS -- CONVERT NUMBERS TO TEXT -- CALCULATE A LOAN PAYMENT -- CALCULATE MANY SCENARIOS FOR LOAN PAYMENTS -- GET HELP ON ANY FUNCTION WHILE ENTERING A FORMULA -- DISCOVER NEW FUNCTIONS USING THE FX BUTTON -- THREE METHODS OF ENTERING FORMULAS -- USE AUTOSUM TO QUICKLY ENTER A TOTAL FORMULA.

AUTOSUM DOESN'T ALWAYS PREDICT MY DATA CORRECTLY -- USE AUTOSUM BUTTON TO ENTER AVERAGES, MIN, MAX, AND COUNT -- THE COUNT OPTION OF THE AUTOSUM DOESN'T APPEAR TO WORK -- AUTOMATICALLY NUMBER A LIST OF EMPLOYEES -- RANK SCORES -- SORTING WITH A FORMULA -- RANK A LIST WITHOUT TIES -- ADD COMMENTS TO A FORMULA -- CALCULATE A MOVING AVERAGE -- CALCULATE A TRENDLINE FORECAST -- BUILD A MODEL TO PREDICT SALES BASED ON MULTIPLE REGRESSION -- USE F9 IN FORMULA BAR TO TEST A FORMULA -- QUICK CALCULATOR -- WHEN ENTERING A FORMULA, YOU GET THE FORMULA INSTEAD OF THE RESULT -- CALCULATE A PERCENTAGE OF TOTAL -- CALCULATE A RUNNING PERCENTAGE OF TOTAL -- USE ^ SIGN FOR EXPONENT -- RAISE A NUMBER TO A FRACTION TO FIND THE SQUARE OR THIRD ROOT -- CALCULATE A GROWTH RATE -- FIND THE AREA OF A CIRCLE -- FIGURE OUT LOTTERY PROBABILITY -- HELP YOUR KIDS WITH THEIR MATH -- MEASURE THE ACCURACY OF A SALES FORECAST -- ROUND PRICES TO NEXT HIGHEST 5 -- WHY IS THIS PRICE SHOWING 27.85000001 CENTS? -- YOU CHANGE A CELL IN EXCEL BUT THE FORMULAS DO NOT CALCULATE -- USE PARENTHESES TO CONTROL ORDER OF CALCULATIONS -- BEFORE DELETING A CELL, FIND OUT IF OTHER CELLS RELY ON IT -- NAVIGATE TO EACH PRECEDENT -- FORMULA AUDITING -- HOW IS THIS CELL CALCULATED? -- TOTAL MINUTES THAT EXCEED AN HOUR -- CONVERT TEXT TO MINUTES AND SECONDS -- CONVERT TEXT TO HOURS, MINUTES, AND SECONDS -- CONVERT TIMES FROM H:MM TO M:SS -- DISPLAY DATES AS MONTHS -- GROUP DATES BY MONTH -- CALCULATE LAST DAY OF MONTH -- CREATE A TIMESHEET THAT CAN TOTAL OVER 24 HOURS -- FIND WHICH CUSTOMERS ARE IN AN EXISTING LIST -- USE VLOOKUP TO FIND WHICH CUSTOMERS ARE IN AN EXISTING LIST -- MATCH CUSTOMERS USING VLOOKUP -- WATCH FOR DUPLICATES WHEN USING VLOOKUP -- COUNT RECORDS THAT MATCH A CRITERIA -- BUILD A TABLE THAT WILL COUNT BY CRITERIA -- BUILD A SUMMARY TABLE TO PLACE EMPLOYEES IN AGE BANDS.

TOTAL REVENUE FROM ROWS THAT MATCH A CRITERION -- USE CONDITIONAL SUM WIZARD TO HELP WITH SUMF -- CREATE A CSE FORMULA TO BUILD A SUPER FORMULA -- LEARN TO USE BOOLEAN LOGIC FACTS TO SIMPLIFY LOGIC -- REPLACE IF FUNCTION WITH BOOLEAN LOGIC -- TEST FOR TWO CONDITIONS IN A SUM -- CAN THE RESULTS OF A FORMULA BE USED IN COUNTIF? -- BACK INTO AN ANSWER USING GOAL SEEK -- PROTECT CELLS WITH FORMULAS -- PART 3: WRANGLING DATA -- HOW TO SET UP YOUR DATA FOR EASY SORTING AND SUBTOTALS -- HOW TO FIT A MULTILINE HEADING INTO ONE CELL -- HOW TO SORT DATA -- HOW TO SPECIFY MORE THAN THREE COLUMNS IN A SORT -- HOW TO SORT A REPORT INTO A CUSTOM SEQUENCE -- QUICKLY FILTER A LIST TO CERTAIN RECORDS -- FIND THE UNIQUE VALUES IN A COLUMN -- COPY MATCHING RECORDS TO A NEW WORKSHEET -- ADD SUBTOTALS TO A DATASET -- USE GROUP & OUTLINE BUTTONS TO COLLAPSE SUBTOTALED DATA -- COPY JUST TOTALS FROM SUBTOTALED DATA -- ENTER A GRAND TOTAL OF DATA MANUALLY SUBTOTALED -- WHY DO SUBTOTALS COME OUT AS COUNTS? -- SUBTOTAL MANY COLUMNS AT ONCE -- MY MANAGER WANTS SUBTOTALS ABOVE THE DATA -- ADD OTHER TEXT DATA TO THE AUTOMATIC SUBTOTAL LINES -- BE WARY -- GENERAL PROTECTION FAULTS -- CREATE SUBTOTALS BY PRODUCT WITHIN REGION -- MY MANAGER WANTS THE SUBTOTAL LINES IN BOLD PINK TAHOMA FONT -- MY MANAGER WANTS A BLANK LINE AFTER EVERY SUBTOTAL -- SUBTOTAL ONE COLUMN AND SUBAVERAGE ANOTHER COLUMN -- HOW TO DO 40 DIFFERENT WHAT-IF ANALYSES QUICKLY -- REMOVE BLANKS FROM A RANGE -- REMOVE BLANKS FROM A RANGE WHILE KEEPING THE ORIGINAL SEQUENCE -- INCREASE A RANGE BY TWO PERCENT -- USE FIND AND REPLACE TO FIND AN ASTERISK -- USE A CUSTOM HEADER OF "PROFIT & LOSS" -- USE CONSOLIDATION TO COMBINE TWO LISTS -- FIND TOTAL SALES BY CUSTOMER BY COMBINING DUPLICATES -- CREATE A SUMMARY OF FOUR LISTS -- NUMBER EACH RECORD FOR A CUSTOMER, STARTING AT ONE FOR A NEW CUSTOMER.

ADD A GROUP NUMBER TO EACH SET OF RECORDS WITH A UNIQUE CUSTOMER NUMBER -- DEAL WITH DATA WHERE EACH RECORD TAKES FIVE PHYSICAL ROWS -- ADD A CUSTOMER NUMBER TO EACH DETAIL RECORD -- USE A PIVOT TABLE TO SUMMARISE DETAILED DATA -- YOUR MANAGER WANTS YOUR REPORT CHANGED -- MOVE OR CHANGE PART OF A PIVOT TABLE -- SEE DETAIL BEHIND ONE NUMBER IN A PIVOT TABLE -- UPDATE DATA BEHIND A PIVOT TABLE -- REPLACE BLANKS IN A PIVOT TABLE WITH ZEROES -- ADD OR REMOVE FIELDS FROM AN EXISTING PIVOT TABLE -- SUMMARIZE PIVOT TABLE DATA BY THREE MEASURES -- MAKE PIVOT TABLES BE TALLER THAN WIDE -- MANUALLY RESEQUENCE THE ORDER OF DATA IN A PIVOT TABLE -- PRESENT A PIVOT TABLE IN HIGH-TO-LOW ORDER BY REVENUE -- LIMIT A PIVOT REPORT TO SHOW JUST THE TOP 12 CUSTOMERS -- QUICKLY PRODUCE REPORTS FOR EACH REGION -- CREATE AN AD-HOC REPORTING TOOL -- CREATE A UNIQUE LIST OF CUSTOMERS WITHA PIVOT TABLE -- CREATE A PIVOT TABLE WITH FEWER CLICKS -- CREATE A REPORT SHOWING COUNT, MIN. MAX, AVERAGE, ETC. -- USE MULTIPLE DATA FIELDS AS A COLUMN FIELD -- COMPARE FOUR WAYS TO SHOW TWO DATA FIELDS INA PIVOT TABLE -- GROUP DAILY DATES UP BY MONTH IN A PIVOT TABLE -- GROUP BY WEEK IN A PIVOT TABLE -- PRODUCE AN ORDER LEAD-TIME REPORT -- USE AUTOFORMAT WITH PIVOT TABLES -- SPECIFY A NUMBER FORMAT FOR A PIVOTTABLE FIELD -- SUPPRESS TOTALS IN A PIVOT TABLE -- ELIMINATE BLANKS IN THE OUTLINE FORMAT OF A PIVOT TABLE -- USE A PIVOT TABLE TO COMPARE TWO LISTS -- CALCULATED FIELDS IN A PIVOT TABLE -- ADD A CALCULATED ITEM TO GROUP ITEMS IN APIVOT TABLE -- QUICKLY CREATE CHARTS FOR ANY REGION -- USE QUERY TO GET A UNIQUE SET OF RECORDS -- IMPORT A TABLE FROM A WEB PAGE INTO EXCEL -- HAVE WEB DATA UPDATE AUTOMATICALLY WHEN YOU OPEN WORKBOOK -- HAVE WEB DATA UPDATE AUTOMATICALLY EVERY TWO MINUTES -- THE SPACES IN THIS WEB DATA WON'T GO AWAY -- USE A BUILT-IN DATA ENTRY FORM.

TRANSFORM BLACK AND WHITE SPREADSHEETS INTO COLOR.