Excel Annoyances

Book description

It's the solution to almost all of your electronic organization needs. Need to present a detailed expense report? Try an Excel spreadsheet. Keeping track of a complicated budget? Excel to the rescue. Want to keep tabs on your office football pool? You guessed it. Thanks to its incredible versatility and power, Excel has emerged as more than just a mainstream program; it's now one of the most used applications on the planet. Everyone from run-of-the-mill PC users to leading financial analysts count on Excel to make sense of overflowing data. And to keep up with the overwhelming user demand, three different versions of Excel have hit the market since the debut of Excel 97: Excel 97, 2000, 2002, and 2003. Naturally, each version offers a new slate of next-generation upgrades--and, of course, operating bugs! At last, Excel users have some relief: Excel Annoyances emerged from the suggestions of numerous Excel users who've struggled with these irritating bugs over the years. Written in the popular Annoyances format, this latest O'Reilly helper addresses all of the quirks, bugs, inconsistencies, and hidden features found in each of the four versions. Chances are if someone, somewhere, found a certain step confusing, then it's addressed in Excel Annoyances. Author Curtis D. Frye breaks down the cavalcade of information into several tip-of-the-finger categories such as Entering Data, Formatting, Charting, Printing, and more. If you're one of the millions of people who use Excel, you're sure to find a goldmine of helpful nuggets that you can use to fix the program's most annoying traits. In the end, Excel Annoyances will help you to truly maximize Excel's seemingly limitless potential.

Table of contents

  1. Excel Annoyances
    1. Dedication
    2. Introduction
        1. WHY WRITE EXCEL ANNOYANCES?
        2. WHY THIS BOOK’S ESPECIALLY IMPORTANT RIGHT NOW
        3. IS EXCEL ANNOYANCES RIGHT FOR YOU?
        4. HAVE AN ANNOYANCE?
        5. HOW TO USE THIS BOOK
        6. CONVENTIONS USED IN THIS BOOK
        7. USING CODE EXAMPLES
        8. ABOUT THE AUTHOR
        9. ABOUT THE CONTRIBUTORS
        10. ABOUT THE TECHNICAL TEAM
        11. ACKNOWLEDGMENTS
    3. 1. Entering Data Annoyances
      1. GENERAL DATA ENTRY ANNOYANCES
        1. KILL CLIPPY
          1. The Annoyance:
          2. The Fix:
        2. RETAIN LEADING ZEROS WHEN YOU ENTER DATA
          1. The Annoyance:
          2. The Fix:
        3. EXTEND A NUMERIC SERIES AUTOMATICALLY
          1. The Annoyance:
          2. The Fix:
        4. USE THE SERIES DIALOG BOX TO DEFINE A SERIES OF VALUES
          1. The Annoyance:
          2. The Fix:
        5. CREATE DATA SERIES MINUS THE MOUSE
          1. The Annoyance:
          2. The Fix:
        6. CREATE A CUSTOM FILL SERIES
          1. The Annoyance:
          2. The Fix:
        7. ENTER DATA INTO MULTIPLE WORKSHEETS AT ONCE
          1. The Annoyance:
          2. The Fix:
        8. ALLOW MORE THAN ONE PERSON TO EDIT A FILE AT THE SAME TIME
          1. The Annoyance:
          2. The Fix:
        9. ADD A CARRIAGE RETURN TO A CELL’S CONTENTS
          1. The Annoyance:
          2. The Fix:
        10. ADD SYMBOLS TO YOUR EXCEL WORKSHEET
          1. The Annoyance:
          2. The Fix:
        11. RESTRICT AUTOCORRECT INTERFERENCE
          1. The Annoyance:
          2. The Fix:
        12. KEEP WEB AND FILE ADDRESSES AS PLAIN TEXT
          1. The Annoyance:
          2. The Fix:
      2. IMPORTING DATA ANNOYANCES
        1. COPYING A WORD TABLE INTO EXCEL 97 INTRODUCES BLANK ROWS
          1. The Annoyance:
          2. The Fix:
        2. DATA IN WORD FILES ISN’T AVAILABLE FOR IMPORT
          1. The Annoyance:
          2. The Fix:
      3. CUT-AND-PASTE ANNOYANCES
        1. PUT MORE THAN ONE ITEM ON THE CLIPBOARD
          1. The Annoyance:
          2. The Fix:
        2. PREVENT THE OFFICE CLIPBOARD FROM APPEARING
          1. The Annoyance:
          2. The Fix:
        3. CONTROL PASTED CELL FORMATS
          1. The Annoyance:
          2. The Fix:
        4. INSERT OR DELETE SINGLE CELLS
          1. The Annoyance:
          2. The Fix:
        5. TRANSPOSE ROWS AND COLUMNS
          1. The Annoyance:
          2. The Fix:
        6. CHANGE THE DEFAULT SAVE LOCATION
          1. The Annoyance:
          2. The Fix:
      4. NAVIGATION AND DISPLAY ANNOYANCES
        1. KEEP THE SAME ACTIVE CELL WHEN YOU MOVE TO A NEW WORKSHEET
          1. The Annoyance:
          2. The Fix:
        2. SHRINK THE EXCEL WINDOW
          1. The Annoyance:
          2. The Fix:
        3. MAGNIFY A SELECTION
          1. The Annoyance:
          2. The Fix:
        4. MOVE TO THE LAST ROW IN A LIST
          1. The Annoyance:
          2. The Fix:
        5. KEEP HEADERS CONSTANT AS YOU SCROLL
          1. The Annoyance:
          2. The Fix:
        6. SEARCH A PORTION OF A WORKSHEET
          1. The Annoyance:
          2. The Fix:
        7. DIVIDE A WORKSHEET INTO MULTIPLE SCROLLABLE AREAS
          1. The Annoyance:
          2. The Fix:
      5. DATA VALIDATION ANNOYANCES
        1. RESTRICT DATA ENTRY WITH VALIDATION RULES
          1. The Annoyance:
          2. The Fix:
        2. CREATE A FORM TO EASE DATA ENTRY
          1. The Annoyance:
          2. The Fix:
        3. BASE VALIDATION RULES ON FORMULA RESULTS
          1. The Annoyance:
          2. The Fix:
        4. USE DATA IN ANOTHER WORKSHEET AS VALIDATION CRITERIA
          1. The Annoyance:
          2. The Fix:
        5. AVOID DUPLICATE ENTRIES IN A COLUMN
          1. The Annoyance:
          2. The Fix:
        6. EXPLAIN DATA VALIDATION RULES
          1. The Annoyance:
          2. The Fix:
        7. HIGHLIGHT INVALID WORKSHEET DATA
          1. The Annoyance:
          2. The Fix:
        8. COPY A VALIDATION RULE TO ANOTHER CELL
          1. The Annoyance:
          2. The Fix:
        9. PREVENT EXCEL FROM SCROLLING TOO QUICKLY
          1. The Annoyance:
          2. The Fix:
    4. 2. Format Annoyances
      1. CELL FORMATTING ANNOYANCES
        1. FORMAT PART OF A CELL’S CONTENTS
          1. The Annoyance:
          2. The Fix:
        2. WRAP TEXT IN A CELL WITH JUST ONE MOUSE CLICK
          1. The Annoyance:
          2. The Fix:
        3. CHANGE WORKSHEET TAB COLORS
          1. The Annoyance:
          2. The Fix:
      2. CONDITIONAL FORMATTING ANNOYANCES
        1. CHANGE CELL FORMATTING BASED ON THE CELL’S VALUE
          1. The Annoyance:
          2. The Fix:
        2. EXCEL APPLIES THE WRONG CONDITIONAL FORMAT
          1. The Annoyance:
          2. The Fix:
        3. LOCATE CELLS WITH CONDITIONAL FORMATS
          1. The Annoyance:
          2. The Fix:
      3. TEMPLATE ANNOYANCES
        1. CREATE A WORKBOOK TEMPLATE
          1. The Annoyance:
          2. The Fix:
        2. CREATE A WORKSHEET TEMPLATE
          1. The Annoyance:
          2. The Fix:
      4. COLOR MANAGEMENT ANNOYANCES
        1. REPLACE REPEATED COLORS IN THE EXCEL COLOR PALETTE
          1. The Annoyance:
          2. The Fix:
        2. TRANSLATE BETWEEN COLOR PALETTE POSITION, COLOR NAME, AND COLORINDEX VALUE
          1. The Annoyance:
          2. The Fix:
        3. COPY ANOTHER WORKBOOK’S COLOR PALETTE
          1. The Annoyance:
          2. The Fix:
        4. DISPLAY THE RGB VALUES OF COLORS IN A WORKBOOK’S COLOR PALETTE
          1. The Annoyance:
          2. The Fix:
        5. MANAGE COLORS AND FORMATS WITH A CUSTOM ADD-IN
          1. The Annoyance:
          2. The Fix:
      5. WORKBOOK FORMATTING ANNOYANCES
        1. CHANGE EXCEL’S DEFAULT FONT
          1. The Annoyance:
          2. The Fix:
        2. HOW TO TELL A STYLE FROM A FORMAT
          1. The Annoyance:
          2. The Fix:
        3. SIMPLIFY EXCEL FORMATTING WITH STYLES
          1. The Annoyance:
          2. The Fix:
        4. SEARCH FOR CELLS WITH SPECIFIC FORMATTING
          1. The Annoyance:
          2. The Fix:
        5. THE CASE OF THE VANISHING GRIDLINES
          1. The Annoyance:
          2. The Fix:
        6. HIDE AND UNHIDE ROWS AND COLUMNS
          1. The Annoyance:
          2. The Fix:
        7. HIDE AND UNHIDE SHEETS
          1. The Annoyance:
          2. The Fix:
      6. CUSTOM FORMAT ANNOYANCES
        1. CREATE CUSTOM NUMBER DISPLAY FORMATS
          1. The Annoyance:
          2. The Fix:
        2. ADD TEXT TO A DISPLAYED NUMERICAL VALUE
          1. The Annoyance:
          2. The Fix:
        3. ROUND VALUES WITHOUT USING THE ROUND() FUNCTION
          1. The Annoyance:
          2. The Fix:
        4. ROUND VALUES TO THE MILLIONS AND DISPLAY “MILLIONS” AFTER THE VALUE
          1. The Annoyance:
          2. The Fix:
        5. ALIGN NUMBERS IN A COLUMN BY DECIMAL POINT
          1. The Annoyance:
          2. The Fix:
        6. CREATE A CUSTOM DATE FORMAT
          1. The Annoyance:
          2. The Fix:
        7. CREATE CUSTOM TIME FORMATS
          1. The Annoyance:
          2. The Fix:
        8. SETTING DATE AND TIME USING OTHER LOCAL RULES
          1. The Annoyance:
          2. The Fix:
        9. APPLY SPECIAL FORMATS FOR ZIP CODES
          1. The Annoyance:
          2. The Fix:
        10. ADDING CUSTOM FOREIGN CURRENCY SYMBOLS
          1. The Annoyance:
          2. The Fix:
    5. 3. Formula Annoyances
      1. FORMULA ENTRY AND EDITING ANNOYANCES
        1. PREVENT COPIED FORMULAS FROM CHANGING CELL REFERENCES
          1. The Annoyance:
          2. The Fix:
        2. TOGGLE BETWEEN RELATIVE AND ABSOLUTE REFERENCES
          1. The Annoyance:
          2. The Fix:
        3. REFER TO CELLS ON ANOTHER WORKSHEET
          1. The Annoyance:
          2. The Fix:
        4. LEARN R1C1 NOTATION
          1. The Annoyance:
          2. The Fix:
        5. CREATE A REFERENCE TO A PIVOTTABLE CELL
          1. The Annoyance:
          2. The Fix:
        6. DEFINE THE INTERSECTION OF TWO RANGES IN A FORMULA
          1. The Annoyance:
          2. The Fix:
        7. NEST FUNCTIONS WITHIN FUNCTIONS
          1. The Annoyance:
          2. The Fix:
        8. DELETE A FORMULA AND KEEP THE RESULT
          1. The Annoyance:
          2. The Fix:
        9. MARK CELLS THAT CONTAIN A FORMULA
          1. The Annoyance:
          2. The Fix:
        10. DISPLAY FORMULAS IN CELLS
          1. The Annoyance:
          2. The Fix:
        11. DROP YOUR OWN PROCEDURE INTO A FORMULA
          1. The Annoyance:
          2. The Fix:
        12. PERFORM THE SAME OPERATION ON A GROUP OF CELLS
          1. The Annoyance:
          2. The Fix:
        13. MONITOR WATCH VALUES
          1. The Annoyance:
          2. The Fix:
        14. ADD COMMENTS TO A FORMULA
          1. The Annoyance:
          2. The Fix:
        15. IMPROVE EXCEL’S STATISTICAL ACCURACY
          1. The Annoyance:
          2. The Fix:
      2. FORMULA ERROR AND AUDITING ANNOYANCES
        1. TELL EXCEL WHAT IS AN ERROR AND WHAT ISN’T
          1. The Annoyance:
          2. The Fix:
        2. FIND FORMULA PRECEDENTS
          1. The Annoyance:
          2. The Fix:
        3. DISPLAY ALL DEPENDENT CELLS
          1. The Annoyance:
          2. The Fix:
        4. MASK ERROR MESSAGES IN A WORKSHEET
          1. The Annoyance:
          2. The Fix:
      3. ARRAY FORMULA ANNOYANCES
        1. DEPLOY ARRAY FORMULAS
          1. The Annoyance:
          2. The Fix:
      4. RECALCULATION ANNOYANCES
        1. RECALCULATE NOW!
          1. The Annoyance:
          2. The Fix:
        2. SPEED UP RECALCULATIONS
          1. The Annoyance:
          2. The Fix:
      5. DATE AND TIME ANNOYANCES
        1. DISPLAY PARTIAL HOURS AS DECIMAL NUMBERS
          1. The Annoyance:
          2. The Fix:
        2. ROUND HOURS TO THE NEXT TENTH OF AN HOUR
          1. The Annoyance:
          2. The Fix:
        3. ROUND HOURS TO THE NEXT QUARTER OF AN HOUR
          1. The Annoyance:
          2. The Fix:
        4. SAVE A DATE OR TIME AS TEXT
          1. The Annoyance:
          2. The Fix:
        5. FIND THE NUMBER OF WORKDAYS BETWEEN TWO DATES
          1. The Annoyance:
          2. The Fix:
        6. ADD HOURS, MINUTES, OR SECONDS TO A TIME
          1. The Annoyance:
          2. The Fix:
        7. IMPRESS HISTORIANS WITH ROMAN NUMERALS
          1. The Annoyance:
          2. The Fix:
        8. FIND THE NUMBER OF A WEEK
          1. The Annoyance:
          2. The Fix:
      6. NAMED RANGE ANNOYANCES
        1. CREATE NAMED RANGES
          1. The Annoyance:
          2. The Fix:
        2. UPDATE EXISTING FORMULAS TO USE NEWLY DEFINED NAMED RANGES
          1. The Annoyance:
          2. The Fix:
        3. CREATE NAMED CONSTANTS
          1. The Annoyance:
          2. The Fix:
        4. CREATE A SELF-EXTENDING NAMED RANGE
          1. The Annoyance:
          2. The Fix:
        5. CREATE A NAMED RANGE FROM MULTIPLE SHEETS
          1. The Annoyance:
          2. The Fix:
        6. PASTE A LIST OF NAMES
          1. The Annoyance:
          2. The Fix:
        7. SELECT AN ENTIRE NAMED RANGE
          1. The Annoyance:
          2. The Fix:
        8. AVOID RENAMING CELLS IN A NAMED RANGE
          1. The Annoyance:
          2. The Fix:
      7. TEXT FORMULA ANNOYANCES
        1. PARSE FIXED-LENGTH SUBSTRINGS
          1. The Annoyance:
          2. The Fix:
        2. PARSE DATA OF INCONSISTENT LENGTHS
          1. The Annoyance:
          2. The Fix:
        3. DIVIDE TEXT INTO MULTIPLE COLUMNS
          1. The Annoyance:
          2. The Fix:
        4. GET RID OF SPACES AND INVISIBLE CHARACTERS
          1. The Annoyance:
          2. The Fix:
      8. COUNTING AND CONDITIONAL SUM ANNOYANCES
        1. SUM VALUES THAT MEET A CRITERION
          1. The Annoyance:
          2. The Fix:
        2. COUNT VALUES THAT MEET A CRITERION
          1. The Annoyance:
          2. The Fix:
        3. COUNT NONBLANK CELLS IN A RANGE
          1. The Annoyance:
          2. The Fix:
        4. COUNT BLANK CELLS IN A RANGE
          1. The Annoyance:
          2. The Fix:
        5. COUNT THE NUMBER OF UNIQUE VALUES IN A RANGE
          1. The Annoyance:
          2. The Fix:
    6. 4. Manipulating Data Annoyances
      1. SORTING AND FILTERING ANNOYANCES
        1. SORT BY VALUES IN MULTIPLE COLUMNS
          1. The Annoyance:
          2. The Fix:
        2. SORT DATA BY A CUSTOM SORT ORDER
          1. The Annoyance:
          2. The Fix:
        3. SORT DATA LEFT TO RIGHT
          1. The Annoyance:
          2. The Fix:
        4. FILTER WORKSHEET DATA
          1. The Annoyance:
          2. The Fix:
        5. DISPLAY TOP OR BOTTOM VALUES
          1. The Annoyance:
          2. The Fix:
        6. CREATE A MULTILEVEL FILTER
          1. The Annoyance:
          2. The Fix:
        7. FIND DUPLICATE ENTRIES IN A DATA LIST
          1. The Annoyance:
          2. The Fix:
        8. COPY ONLY VISIBLE CELLS FROM A FILTERED LIST
          1. The Annoyance:
          2. The Fix:
        9. SUMMARIZE VISIBLE CELLS ONLY
          1. The Annoyance:
          2. The Fix:
      2. LOOKUP FUNCTION ANNOYANCES
        1. LOOK UP VALUES IN DATA LISTS
          1. The Annoyance:
          2. The Fix:
        2. LOOK UP A VALUE IN ANY COLUMN
          1. The Annoyance:
          2. The Fix:
        3. CORRECT AN INCORRECT VLOOKUP() RESULT IN EXCEL 97
          1. The Annoyance:
          2. The Fix:
        4. TEXT-NUMBER MIX OVERWHELMS LOOKUP
          1. The Annoyance:
          2. The Fix:
        5. PERFORM A CASE-SENSITIVE LOOKUP
          1. The Annoyance:
          2. The Fix:
        6. WORK WITH INCORRECT TIMES SERIES
          1. The Annoyance:
          2. The Fix:
        7. SPEED UP MULTIPLE LOOKUPS
          1. The Annoyance:
          2. The Fix:
        8. FIND THE FIRST OR LAST MATCH IN AN ARRAY
          1. The Annoyance:
          2. The Fix:
      3. WHAT-IF ANALYSIS ANNOYANCES
        1. CREATE A SCENARIO
          1. The Annoyance:
          2. The Fix:
        2. FIND A VALUE THAT GENERATES A SPECIFIC RESULT
          1. The Annoyance:
          2. The Fix:
        3. USE SOLVER TO SOLVE MULTIVARIATE PROBLEMS
          1. The Annoyance:
          2. The Fix:
        4. PLACE DATA ANALYSIS ON A MENU
          1. The Annoyance:
          2. The Fix:
      4. PIVOTTABLE ANNOYANCES
        1. LEARN TO USE PIVOTTABLES
          1. The Annoyance:
          2. The Fix:
        2. CREATE A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        3. PIVOT A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        4. FILTER PIVOTTABLE DATA
          1. The Annoyance:
          2. The Fix:
        5. CREATE ADVANCED PIVOTTABLE FILTERS
          1. The Annoyance:
          2. The Fix:
        6. FILTER A PIVOTTABLE BY ANY FIELD
          1. The Annoyance:
          2. The Fix:
        7. FIND PIVOTTABLE ADD-INS
          1. The Annoyance:
          2. The Fix:
        8. APPLY AUTOFORMAT TO A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        9. STOP PIVOTTABLES FROM PIVOTING!
          1. The Annoyance:
          2. The Fix:
        10. PIVOTTABLE DATA DISPLAYS AS NUMBER SIGNS
          1. The Annoyance:
          2. The Fix:
        11. “PIVOTTABLE IS NOT VALID” ERROR
          1. The Annoyance:
          2. The Fix:
        12. PIVOTTABLE LOSES FORMATTING
          1. The Annoyance:
          2. The Fix:
        13. USE CALCULATED FIELDS IN A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        14. CHANGE SUMMARY CALCULATIONS IN A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        15. HIDDEN DATA DOESN’T APPEAR IN THE SUMMARY
          1. The Annoyance:
          2. The Fix:
        16. “REFERENCE NOT VALID” ERROR
          1. The Annoyance:
          2. The Fix:
        17. DISPLAY PIVOTTABLE DATA AS A PERCENTAGE OF A TOTAL
          1. The Annoyance:
          2. The Fix:
    7. 5. Chart Annoyances
      1. CHART CREATION AND FORMATTING ANNOYANCES
        1. INSTANT CHARTS
          1. The Annoyance:
          2. The Fix:
        2. SPIFF UP A CHART QUICKLY
          1. The Annoyance:
          2. The Fix:
        3. UPDATE A CHART TO INCLUDE NEW EXCEL DATA
          1. The Annoyance:
          2. The Fix:
        4. CREATE AND UPDATE CHARTS FROM QUERY DATA
          1. The Annoyance:
          2. The Fix:
        5. EXPLODE YOUR PIE CHART
          1. The Annoyance:
          2. The Fix:
        6. TWO CHARTS FOR THE PRICE OF ONE
          1. The Annoyance:
          2. The Fix:
        7. CHART DATA SERIES PROPERLY
          1. The Annoyance:
          2. The Fix:
        8. FORMAT CHART ELEMENTS
          1. The Annoyance:
          2. The Fix:
        9. TAKE CONTROL OF YOUR AXES
          1. The Annoyance:
          2. The Fix:
        10. ADD TEXT TO A CHART AXIS
          1. The Annoyance:
          2. The Fix:
        11. GLITZ UP YOUR COLUMNS
          1. The Annoyance:
          2. The Fix:
        12. ADD ERROR BARS TO A GRAPH
          1. The Annoyance:
          2. The Fix:
      2. CHART MANIPULATION ANNOYANCES
        1. COPY CHARTS AS PICTURES
          1. The Annoyance:
          2. The Fix:
        2. MOVE A CHART TO A SEPARATE SHEET
          1. The Annoyance:
          2. The Fix:
        3. PRINT A CHART WITHOUT SURROUNDING DATA
          1. The Annoyance:
          2. The Fix:
        4. CHOOSE YOUR AXIS
          1. The Annoyance:
          2. The Fix:
        5. SEEKING YOUR GOAL WITH A CHART
          1. The Annoyance:
          2. The Fix:
      3. PIVOTCHART ANNOYANCES
        1. PIVOTCHARTS AND PIVOTTABLES GO TOGETHER LIKE ...
          1. The Annoyance:
          2. The Fix:
        2. FIRST A PIVOTCHART, THEN A PIVOTTABLE
          1. The Annoyance:
          2. The Fix:
        3. MAKE NORMAL CHARTS FROM PIVOTTABLES
          1. The Annoyance:
          2. The Fix:
      4. CHART INTERACTION ANNOYANCES
        1. PRINT AN EXCEL 97 CHART FROM POWERPOINT
          1. The Annoyance:
          2. The Fix:
        2. CHART MACRO CRASHES EXCEL
          1. The Annoyance:
          2. The Fix:
        3. CHART GROWS WHEN MOVED TO TEXT BOX
          1. The Annoyance:
          2. The Fix:
        4. DISAPPEARING SCATTER PLOT POINTS ON THE WEB
          1. The Annoyance:
          2. The Fix:
        5. PASTED CHART TURNS INTO GRAY RECTANGLE
          1. The Annoyance:
          2. The Fix:
        6. CAN’T PUBLISH A CHART FROM MULTIPLE SHEETS
          1. The Annoyance:
          2. The Fix:
        7. DRAGGING EXCEL CHART CRASHES WORD
          1. The Annoyance:
          2. The Fix:
        8. CHART TITLES SHOW ONLY ONE LINE
          1. The Annoyance:
          2. The Fix:
        9. PREVIEW A CHART, CRASH EXCEL 97
          1. The Annoyance:
          2. The Fix:
        10. FALSE PERCENTAGES IN PIE CHART
          1. The Annoyance:
          2. The Fix:
        11. THE CASE OF THE DISAPPEARING AXIS LABELS
          1. The Annoyance:
          2. The Fix:
        12. CHART IMAGE CHOPPED
          1. The Annoyance:
          2. The Fix:
      5. CHART ADD-INS AND HELPER APPLICATIONS
        1. MAGIC ADD-INS FOR CHARTS AND GRAPHS
          1. The Annoyance:
          2. The Fix:
        2. BETTER 2D AND 3D CHARTS
          1. The Annoyance:
          2. The Fix:
      6. 3D CHART ANNOYANCES
        1. CAN’T CHANGE 3D COLORS IN EXCEL 97
          1. The Annoyance:
          2. The Fix:
        2. FLOOR TURNS BLACK IN 3D CHART
          1. The Annoyance:
          2. The Fix:
        3. PIE EXPLOSIONS ARE NO FUN IN 3D
          1. The Annoyance:
          2. The Fix:
    8. 6. Exhanging Data Annoyances
      1. MS QUERY AND DATABASE ANNOYANCES
        1. BRING IN SELECTED DATABASE RECORDS
          1. The Annoyance:
          2. The Fix:
        2. AVOID DUPLICATE RECORDS
          1. The Annoyance:
          2. The Fix:
        3. CONTROL THE NUMBER OF IMPORTED RECORDS
          1. The Annoyance:
          2. The Fix:
        4. GET MORE THAN 65,536 RECORDS INTO EXCEL
          1. The Annoyance:
          2. The Fix:
        5. ADD A COUNTING FIELD TO QUERY RESULTS
          1. The Annoyance:
          2. The Fix:
        6. IMPORT DATA FROM MORE THAN ONE TABLE
          1. The Annoyance:
          2. The Fix:
        7. TRANSLATE ERROR MESSAGES INTO ENGLISH
          1. The Annoyance:
          2. The Fix:
      2. IMPORT AND EXPORT ANNOYANCES
        1. MOVE DATA BACK AND FORTH FROM EXCEL TO ACCESS
          1. The Annoyance:
          2. The Fix:
            1. Moving data from Excel to Access
            2. Moving data from Access to Excel
        2. TRUNCATED DATA IN ACCESS
          1. The Annoyance:
          2. The Fix:
        3. UPDATE EMBEDDED POWERPOINT CHART
          1. The Annoyance:
          2. The Fix:
        4. INCLUDE ANOTHER FILE IN AN EXCEL WORKBOOK
          1. The Annoyance:
          2. The Fix:
        5. LINK TO ANOTHER FILE FROM AN EXCEL WORKBOOK
          1. The Annoyance:
          2. The Fix:
      3. DATA FORMAT ANNOYANCES
        1. PLACE EXCEL DATA ON THE WEB
          1. The Annoyance:
          2. The Fix:
        2. PLACE A USABLE WORKSHEET ON A WEB PAGE
          1. The Annoyance:
          2. The Fix:
        3. CONTROL THE WEB PAGE WORKSHEET
          1. The Annoyance:
          2. The Fix:
        4. BREAK LINKS TO EXTERNAL DATA
          1. The Annoyance:
          2. The Fix:
        5. EXTERMINATE EXTERNAL LINKS
          1. The Annoyance:
          2. The Fix:
        6. IMPORT SELECTED FIELDS ONLY
          1. The Annoyance:
          2. The Fix:
        7. PREPARE DATA FOR A DATABASE
          1. The Annoyance:
          2. The Fix:
        8. EXPORTED DATA LOSES 100 YEARS
          1. The Annoyance:
          2. The Fix:
        9. OUTLOOK MESSAGE INCLUDES TOO MUCH WORKSHEET
          1. The Annoyance:
          2. The Fix:
        10. PIVOTTABLE CONTAINS TWO FIELDS WITH THE SAME NAME
          1. The Annoyance:
          2. The Fix:
        11. PIVOTTABLE WORKS IN EXCEL BUT NOT ON THE WEB
          1. The Annoyance:
          2. The Fix:
        12. FILE EXPORT MACRO IGNORES REGIONAL SETTINGS
          1. The Annoyance:
          2. The Fix:
    9. 7. Printing Annoyances
      1. BASIC PRINTING ANNOYANCES
        1. SQUEEZE THAT ENTIRE WORKSHEET ONTO ONE PAGE
          1. The Annoyance:
          2. The Fix:
        2. PRINT ONLY SELECTED REGIONS
          1. The Annoyance:
          2. The Fix:
        3. PRINT AN ENTIRE WORKBOOK—OR MULTIPLE SELECTED SHEETS
          1. The Annoyance:
          2. The Fix:
        4. PRINT MULTIPLE WORKBOOKS IN ONE PRINT JOB
          1. The Annoyance:
          2. The Fix:
        5. PRINT A LIST FROM WITHIN A WORKSHEET
          1. The Annoyance:
          2. The Fix:
        6. MAKE PART OF THE WORKSHEET PRINT EVERY TIME
          1. The Annoyance:
          2. The Fix:
      2. PRINT LAYOUT ANNOYANCES
        1. PUT PAGE BREAKS WHERE YOU WANT ‘EM
          1. The Annoyance:
          2. The Fix:
        2. CHANGE THE ORDER IN WHICH PAGES ARE PRINTED
          1. The Annoyance:
          2. The Fix:
        3. CHANGE WORKBOOK MARGINS
          1. The Annoyance:
          2. The Fix:
        4. SET PAPER SIZE AND ORIENTATION
          1. The Annoyance:
          2. The Fix:
        5. ONE PAGE IN LANDSCAPE, ANOTHER IN PORTRAIT
          1. The Annoyance:
          2. The Fix:
        6. CUSTOM FOLIOS
          1. The Annoyance:
          2. The Fix:
        7. MORE ROOM FOR HEADERS AND FOOTERS
          1. The Annoyance:
          2. The Fix:
      3. PRINT CONTENT ANNOYANCES
        1. ADD GRIDLINES TO A PRINTOUT
          1. The Annoyance:
          2. The Fix:
        2. ADD TIME AND DATE TO YOUR PRINTOUT
          1. The Annoyance:
          2. The Fix:
        3. PRINT COLUMN LETTERS AND ROW NUMBERS
          1. The Annoyance:
          2. The Fix:
        4. PRINT COLUMN HEADINGS AND ROW LABELS ON EVERY PAGE
          1. The Annoyance:
          2. The Fix:
        5. PUT A SPECIAL REPORT TITLE ON EVERY PAGE
          1. The Annoyance:
          2. The Fix:
        6. PRINT WORKSHEET COMMENTS IN CONTEXT
          1. The Annoyance:
          2. The Fix:
        7. PRINT PIVOTTABLES, NOT THE REST OF THE WORKSHEET
          1. The Annoyance:
          2. The Fix:
        8. BLOCK PRINTOUT OF ERROR MESSAGES
          1. The Annoyance:
          2. The Fix:
      4. PRINTER MANAGEMENT ANNOYANCES
        1. SET A DEFAULT PRINTER
          1. The Annoyance:
          2. The Fix:
        2. PRINT A WORKSHEET IN GLORIOUS BLACK AND WHITE
          1. The Annoyance:
          2. The Fix:
        3. SET OPTIMUM PRINT QUALITY
          1. The Annoyance:
          2. The Fix:
        4. CHECK THE STATUS OF YOUR PRINT JOB
          1. The Annoyance:
          2. The Fix:
        5. SEND A WORKSHEET AS A FAX
          1. The Annoyance:
          2. The Fix:
    10. 8. Customization, Macro, and VBA Annoyances
      1. INTERFACE CUSTOMIZATION ANNOYANCES
        1. EXPAND THE RECENTLY USED FILE LIST
          1. The Annoyance:
          2. The Fix:
        2. ALWAYS SHOW FULL MENUS
          1. The Annoyance:
          2. The Fix:
        3. PROTECT YOUR WORKBOOK
          1. The Annoyance:
          2. The Fix:
        4. PROTECT YOUR DATA
          1. The Annoyance:
          2. The Fix:
        5. REQUIRE A PASSWORD TO OPEN OR MODIFY A WORKBOOK
          1. The Annoyance:
          2. The Fix:
        6. OPEN A PROTECTED WORKBOOK
          1. The Annoyance:
          2. The Fix:
        7. FORGET PASSWORDS! USE ENCRYPTION
          1. The Annoyance:
          2. The Fix:
        8. USE EXCEL 2003 WORKBOOK PERMISSIONS
          1. The Annoyance:
          2. The Fix:
        9. INCREASE THE NUMBER OF UNDO LEVELS
          1. The Annoyance:
          2. The Fix:
      2. TOOLBAR AND MENU BAR ANNOYANCES
        1. SHOW ALL YOUR BUTTONS
          1. The Annoyance:
          2. The Fix:
        2. ADD STANDARD BUTTONS TO YOUR TOOLBAR
          1. The Annoyance:
          2. The Fix:
        3. ADD A NEW BUTTON TO A TOOLBAR
          1. The Annoyance:
          2. The Fix:
        4. CHANGE THE APPEARANCE OF A TOOLBAR BUTTON
          1. The Annoyance:
          2. The Fix:
        5. HIDE OR DISPLAY A BUILT-IN TOOLBAR
          1. The Annoyance:
          2. The Fix:
        6. CREATE A CUSTOM TOOLBAR
          1. The Annoyance:
          2. The Fix:
        7. RESET A TOOLBAR OR MENU TO ITS DEFAULT SETTINGS
          1. The Annoyance:
          2. The Fix:
        8. ADD A BUILT-IN MENU TO A TOOLBAR
          1. The Annoyance:
          2. The Fix:
        9. CREATE AND POPULATE A CUSTOM MENU
          1. The Annoyance:
          2. The Fix:
      3. MACRO ANNOYANCES
        1. RECORD A MACRO
          1. The Annoyance:
          2. The Fix:
        2. RUN A MACRO
          1. The Annoyance:
          2. The Fix:
        3. RECORD A MACRO WITH RELATIVE REFERENCES
          1. The Annoyance:
          2. The Fix:
        4. PLACE YOUR MACRO ON A TOOLBAR OR MENU
          1. The Annoyance:
          2. The Fix:
        5. STEP THROUGH A MACRO
          1. The Annoyance:
          2. The Fix:
        6. EDIT A MACRO
          1. The Annoyance:
          2. The Fix:
        7. RUN A MACRO STORED IN ANOTHER WORKBOOK
          1. The Annoyance:
          2. The Fix:
      4. MACRO SECURITY ANNOYANCES
        1. AVOID MACRO VIRUSES
          1. The Annoyance:
          2. The Fix:
        2. ADD A PUBLISHER TO YOUR TRUSTED PUBLISHERS LIST
          1. The Annoyance:
          2. The Fix:
        3. REMOVE A PUBLISHER FROM YOUR TRUSTED PUBLISHERS LIST
          1. The Annoyance:
          2. The Fix:
        4. ACQUIRE A DIGITAL CERTIFICATE
          1. The Annoyance:
          2. The Fix:
        5. DIGITALLY SIGN A WORKBOOK
          1. The Annoyance:
          2. The Fix:
      5. VBA ANNOYANCES
        1. MEET OBJECT-ORIENTED PROGRAMMING
          1. The Annoyance:
          2. The Fix:
        2. CREATE A NEW CODE MODULE
          1. The Annoyance:
          2. The Fix:
        3. VIEW THE OBJECT MODEL
          1. The Annoyance:
          2. The Fix:
        4. CREATE A SUBPROCEDURE
          1. The Annoyance:
          2. The Fix:
        5. FUN WITH VARIABLES
          1. The Annoyance:
          2. The Fix:
        6. DECLARE VARIABLES BEFORE USING THEM
          1. The Annoyance:
          2. The Fix:
        7. ADD COMMENTS TO VBA CODE
          1. The Annoyance:
          2. The Fix:
        8. CONTINUE A LINE OF CODE ON A SECOND LINE
          1. The Annoyance:
          2. The Fix:
        9. CREATE VARIABLES THAT REFER TO OBJECTS
          1. The Annoyance:
          2. The Fix:
        10. CREATE A FUNCTION PROCEDURE
          1. The Annoyance:
          2. The Fix:
        11. FORCE A MACRO TO AFFECT THE ACTIVE WORKBOOK
          1. The Annoyance:
          2. The Fix:
        12. PREVENT A PROCEDURE FROM BEING CALLED FROM ANOTHER WORKBOOK
          1. The Annoyance:
          2. The Fix:
        13. BEGONE DULL SCREEN FLICKER
          1. The Annoyance:
          2. The Fix:
        14. MACROS MAKE MY COMPUTER SLUGGISH
          1. The Annoyance:
          2. The Fix:
        15. USE AN IF...THEN CRITERION STATEMENT
          1. The Annoyance:
          2. The Fix:
        16. CREATE A STRING FROM VALUES IN MORE THAN ONE CELL
          1. The Annoyance:
          2. The Fix:
        17. AFFECT EVERY MEMBER OF A COLLECTION
          1. The Annoyance:
          2. The Fix:
        18. RUN A MACRO WHEN AN EVENT HAPPENS
          1. The Annoyance:
          2. The Fix:
        19. REQUIRE A PASSWORD TO VIEW VBA CODE
          1. The Annoyance:
          2. The Fix:
        20. ABBREVIATE OBJECT REFERENCES IN VBA CODE
          1. The Annoyance:
          2. The Fix:
        21. EXCEL OPENS A WORKBOOK IN REPAIR MODE
          1. The Annoyance:
          2. The Fix:
        22. CAN’T SAVE FILE WITHOUT NETWORK CONNECTION
          1. The Annoyance:
          2. The Fix:
        23. RUNTIME RUNAROUND
          1. The Annoyance:
          2. The Fix:
        24. RANGE.CALCULATE ERROR
          1. The Annoyance:
          2. The Fix:
      6. HANDY VBA PROCEDURES
        1. CREATE A WORKBOOK WITH A WORKSHEET FOR EACH MONTH
          1. The Annoyance:
          2. The Fix:
        2. PICK RANDOM ENTRIES FROM A LIST
          1. The Annoyance:
          2. The Fix:
        3. GENERATE NONREPEATING RANDOM VALUES
          1. The Annoyance:
          2. The Fix:
    11. 9. Excel 2003 Annoyances
      1. EXCEL 2003 LIST ANNOYANCES
        1. CREATE A LIST
          1. The Annoyance:
          2. The Fix:
        2. REMOVE LIST BORDERS
          1. The Annoyance:
          2. The Fix:
      2. EXCEL 2003 XML ANNOYANCES
        1. GET A GRIP ON XML
          1. The Annoyance:
          2. The Fix:
        2. INFER A SCHEMA FROM XML DATA
          1. The Annoyance:
          2. The Fix:
        3. TROUBLESHOOT IMPORTING SCHEMAS
          1. The Annoyance:
          2. The Fix:
        4. STEP THROUGH AN XML SCHEMA
          1. The Annoyance:
          2. The Fix:
        5. CREATE A DATA MAP
          1. The Annoyance:
          2. The Fix:
        6. POPULATE AN XML-MAPPED WORKBOOK
          1. The Annoyance:
          2. The Fix:
        7. EXPORT DATA FROM AN XML-MAPPED WORKBOOK
          1. The Annoyance:
          2. The Fix:
      3. EXCEL 2003 MENU AND FUNCTION ANNOYANCES
        1. CLOSE BUTTON IS UNAVAILABLE
          1. The Annoyance:
          2. The Fix:
        2. CHART TOOL GENERATES WRONG R-SQUARED VALUE
          1. The Annoyance:
          2. The Fix:
        3. EXCEL RANDOM NUMBER GENERATOR GENERATES NEGATIVE NUMBERS
          1. The Annoyance:
          2. The Fix:
      4. EXCEL 2003 PROGRAM INTERACTION ANNOYANCES
        1. CAN’T EXPORT A WEB PAGE TO AN EXCEL WORKBOOK
          1. The Annoyance:
          2. The Fix:
        2. EXCEL CRASHES WHEN I RUN LIVEMEETING
          1. The Annoyance:
          2. The Fix:
    12. Index
    13. Colophon

Product information

  • Title: Excel Annoyances
  • Author(s): Curtis D. Frye
  • Release date: December 2004
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596007287