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
-
Excel Annoyances
- Dedication
- Introduction
-
1. Entering Data Annoyances
-
GENERAL DATA ENTRY ANNOYANCES
- KILL CLIPPY
- RETAIN LEADING ZEROS WHEN YOU ENTER DATA
- EXTEND A NUMERIC SERIES AUTOMATICALLY
- USE THE SERIES DIALOG BOX TO DEFINE A SERIES OF VALUES
- CREATE DATA SERIES MINUS THE MOUSE
- CREATE A CUSTOM FILL SERIES
- ENTER DATA INTO MULTIPLE WORKSHEETS AT ONCE
- ALLOW MORE THAN ONE PERSON TO EDIT A FILE AT THE SAME TIME
- ADD A CARRIAGE RETURN TO A CELL’S CONTENTS
- ADD SYMBOLS TO YOUR EXCEL WORKSHEET
- RESTRICT AUTOCORRECT INTERFERENCE
- KEEP WEB AND FILE ADDRESSES AS PLAIN TEXT
- IMPORTING DATA ANNOYANCES
- CUT-AND-PASTE ANNOYANCES
- NAVIGATION AND DISPLAY ANNOYANCES
-
DATA VALIDATION ANNOYANCES
- RESTRICT DATA ENTRY WITH VALIDATION RULES
- CREATE A FORM TO EASE DATA ENTRY
- BASE VALIDATION RULES ON FORMULA RESULTS
- USE DATA IN ANOTHER WORKSHEET AS VALIDATION CRITERIA
- AVOID DUPLICATE ENTRIES IN A COLUMN
- EXPLAIN DATA VALIDATION RULES
- HIGHLIGHT INVALID WORKSHEET DATA
- COPY A VALIDATION RULE TO ANOTHER CELL
- PREVENT EXCEL FROM SCROLLING TOO QUICKLY
-
GENERAL DATA ENTRY ANNOYANCES
-
2. Format Annoyances
- CELL FORMATTING ANNOYANCES
- CONDITIONAL FORMATTING ANNOYANCES
- TEMPLATE ANNOYANCES
- COLOR MANAGEMENT ANNOYANCES
- WORKBOOK FORMATTING ANNOYANCES
-
CUSTOM FORMAT ANNOYANCES
- CREATE CUSTOM NUMBER DISPLAY FORMATS
- ADD TEXT TO A DISPLAYED NUMERICAL VALUE
- ROUND VALUES WITHOUT USING THE ROUND() FUNCTION
- ROUND VALUES TO THE MILLIONS AND DISPLAY “MILLIONS” AFTER THE VALUE
- ALIGN NUMBERS IN A COLUMN BY DECIMAL POINT
- CREATE A CUSTOM DATE FORMAT
- CREATE CUSTOM TIME FORMATS
- SETTING DATE AND TIME USING OTHER LOCAL RULES
- APPLY SPECIAL FORMATS FOR ZIP CODES
- ADDING CUSTOM FOREIGN CURRENCY SYMBOLS
-
3. Formula Annoyances
-
FORMULA ENTRY AND EDITING ANNOYANCES
- PREVENT COPIED FORMULAS FROM CHANGING CELL REFERENCES
- TOGGLE BETWEEN RELATIVE AND ABSOLUTE REFERENCES
- REFER TO CELLS ON ANOTHER WORKSHEET
- LEARN R1C1 NOTATION
- CREATE A REFERENCE TO A PIVOTTABLE CELL
- DEFINE THE INTERSECTION OF TWO RANGES IN A FORMULA
- NEST FUNCTIONS WITHIN FUNCTIONS
- DELETE A FORMULA AND KEEP THE RESULT
- MARK CELLS THAT CONTAIN A FORMULA
- DISPLAY FORMULAS IN CELLS
- DROP YOUR OWN PROCEDURE INTO A FORMULA
- PERFORM THE SAME OPERATION ON A GROUP OF CELLS
- MONITOR WATCH VALUES
- ADD COMMENTS TO A FORMULA
- IMPROVE EXCEL’S STATISTICAL ACCURACY
- FORMULA ERROR AND AUDITING ANNOYANCES
- ARRAY FORMULA ANNOYANCES
- RECALCULATION ANNOYANCES
-
DATE AND TIME ANNOYANCES
- DISPLAY PARTIAL HOURS AS DECIMAL NUMBERS
- ROUND HOURS TO THE NEXT TENTH OF AN HOUR
- ROUND HOURS TO THE NEXT QUARTER OF AN HOUR
- SAVE A DATE OR TIME AS TEXT
- FIND THE NUMBER OF WORKDAYS BETWEEN TWO DATES
- ADD HOURS, MINUTES, OR SECONDS TO A TIME
- IMPRESS HISTORIANS WITH ROMAN NUMERALS
- FIND THE NUMBER OF A WEEK
- NAMED RANGE ANNOYANCES
- TEXT FORMULA ANNOYANCES
- COUNTING AND CONDITIONAL SUM ANNOYANCES
-
FORMULA ENTRY AND EDITING ANNOYANCES
-
4. Manipulating Data Annoyances
- SORTING AND FILTERING ANNOYANCES
- LOOKUP FUNCTION ANNOYANCES
- WHAT-IF ANALYSIS ANNOYANCES
-
PIVOTTABLE ANNOYANCES
- LEARN TO USE PIVOTTABLES
- CREATE A PIVOTTABLE
- PIVOT A PIVOTTABLE
- FILTER PIVOTTABLE DATA
- CREATE ADVANCED PIVOTTABLE FILTERS
- FILTER A PIVOTTABLE BY ANY FIELD
- FIND PIVOTTABLE ADD-INS
- APPLY AUTOFORMAT TO A PIVOTTABLE
- STOP PIVOTTABLES FROM PIVOTING!
- PIVOTTABLE DATA DISPLAYS AS NUMBER SIGNS
- “PIVOTTABLE IS NOT VALID” ERROR
- PIVOTTABLE LOSES FORMATTING
- USE CALCULATED FIELDS IN A PIVOTTABLE
- CHANGE SUMMARY CALCULATIONS IN A PIVOTTABLE
- HIDDEN DATA DOESN’T APPEAR IN THE SUMMARY
- “REFERENCE NOT VALID” ERROR
- DISPLAY PIVOTTABLE DATA AS A PERCENTAGE OF A TOTAL
-
5. Chart Annoyances
-
CHART CREATION AND FORMATTING ANNOYANCES
- INSTANT CHARTS
- SPIFF UP A CHART QUICKLY
- UPDATE A CHART TO INCLUDE NEW EXCEL DATA
- CREATE AND UPDATE CHARTS FROM QUERY DATA
- EXPLODE YOUR PIE CHART
- TWO CHARTS FOR THE PRICE OF ONE
- CHART DATA SERIES PROPERLY
- FORMAT CHART ELEMENTS
- TAKE CONTROL OF YOUR AXES
- ADD TEXT TO A CHART AXIS
- GLITZ UP YOUR COLUMNS
- ADD ERROR BARS TO A GRAPH
- CHART MANIPULATION ANNOYANCES
- PIVOTCHART ANNOYANCES
-
CHART INTERACTION ANNOYANCES
- PRINT AN EXCEL 97 CHART FROM POWERPOINT
- CHART MACRO CRASHES EXCEL
- CHART GROWS WHEN MOVED TO TEXT BOX
- DISAPPEARING SCATTER PLOT POINTS ON THE WEB
- PASTED CHART TURNS INTO GRAY RECTANGLE
- CAN’T PUBLISH A CHART FROM MULTIPLE SHEETS
- DRAGGING EXCEL CHART CRASHES WORD
- CHART TITLES SHOW ONLY ONE LINE
- PREVIEW A CHART, CRASH EXCEL 97
- FALSE PERCENTAGES IN PIE CHART
- THE CASE OF THE DISAPPEARING AXIS LABELS
- CHART IMAGE CHOPPED
- CHART ADD-INS AND HELPER APPLICATIONS
- 3D CHART ANNOYANCES
-
CHART CREATION AND FORMATTING ANNOYANCES
-
6. Exhanging Data Annoyances
- MS QUERY AND DATABASE ANNOYANCES
- IMPORT AND EXPORT ANNOYANCES
-
DATA FORMAT ANNOYANCES
- PLACE EXCEL DATA ON THE WEB
- PLACE A USABLE WORKSHEET ON A WEB PAGE
- CONTROL THE WEB PAGE WORKSHEET
- BREAK LINKS TO EXTERNAL DATA
- EXTERMINATE EXTERNAL LINKS
- IMPORT SELECTED FIELDS ONLY
- PREPARE DATA FOR A DATABASE
- EXPORTED DATA LOSES 100 YEARS
- OUTLOOK MESSAGE INCLUDES TOO MUCH WORKSHEET
- PIVOTTABLE CONTAINS TWO FIELDS WITH THE SAME NAME
- PIVOTTABLE WORKS IN EXCEL BUT NOT ON THE WEB
- FILE EXPORT MACRO IGNORES REGIONAL SETTINGS
-
7. Printing Annoyances
- BASIC PRINTING ANNOYANCES
- PRINT LAYOUT ANNOYANCES
-
PRINT CONTENT ANNOYANCES
- ADD GRIDLINES TO A PRINTOUT
- ADD TIME AND DATE TO YOUR PRINTOUT
- PRINT COLUMN LETTERS AND ROW NUMBERS
- PRINT COLUMN HEADINGS AND ROW LABELS ON EVERY PAGE
- PUT A SPECIAL REPORT TITLE ON EVERY PAGE
- PRINT WORKSHEET COMMENTS IN CONTEXT
- PRINT PIVOTTABLES, NOT THE REST OF THE WORKSHEET
- BLOCK PRINTOUT OF ERROR MESSAGES
- PRINTER MANAGEMENT ANNOYANCES
-
8. Customization, Macro, and VBA Annoyances
- INTERFACE CUSTOMIZATION ANNOYANCES
-
TOOLBAR AND MENU BAR ANNOYANCES
- SHOW ALL YOUR BUTTONS
- ADD STANDARD BUTTONS TO YOUR TOOLBAR
- ADD A NEW BUTTON TO A TOOLBAR
- CHANGE THE APPEARANCE OF A TOOLBAR BUTTON
- HIDE OR DISPLAY A BUILT-IN TOOLBAR
- CREATE A CUSTOM TOOLBAR
- RESET A TOOLBAR OR MENU TO ITS DEFAULT SETTINGS
- ADD A BUILT-IN MENU TO A TOOLBAR
- CREATE AND POPULATE A CUSTOM MENU
- MACRO ANNOYANCES
- MACRO SECURITY ANNOYANCES
-
VBA ANNOYANCES
- MEET OBJECT-ORIENTED PROGRAMMING
- CREATE A NEW CODE MODULE
- VIEW THE OBJECT MODEL
- CREATE A SUBPROCEDURE
- FUN WITH VARIABLES
- DECLARE VARIABLES BEFORE USING THEM
- ADD COMMENTS TO VBA CODE
- CONTINUE A LINE OF CODE ON A SECOND LINE
- CREATE VARIABLES THAT REFER TO OBJECTS
- CREATE A FUNCTION PROCEDURE
- FORCE A MACRO TO AFFECT THE ACTIVE WORKBOOK
- PREVENT A PROCEDURE FROM BEING CALLED FROM ANOTHER WORKBOOK
- BEGONE DULL SCREEN FLICKER
- MACROS MAKE MY COMPUTER SLUGGISH
- USE AN IF...THEN CRITERION STATEMENT
- CREATE A STRING FROM VALUES IN MORE THAN ONE CELL
- AFFECT EVERY MEMBER OF A COLLECTION
- RUN A MACRO WHEN AN EVENT HAPPENS
- REQUIRE A PASSWORD TO VIEW VBA CODE
- ABBREVIATE OBJECT REFERENCES IN VBA CODE
- EXCEL OPENS A WORKBOOK IN REPAIR MODE
- CAN’T SAVE FILE WITHOUT NETWORK CONNECTION
- RUNTIME RUNAROUND
- RANGE.CALCULATE ERROR
- HANDY VBA PROCEDURES
-
9. Excel 2003 Annoyances
- EXCEL 2003 LIST ANNOYANCES
- EXCEL 2003 XML ANNOYANCES
- EXCEL 2003 MENU AND FUNCTION ANNOYANCES
- EXCEL 2003 PROGRAM INTERACTION ANNOYANCES
- Index
- Colophon
Product information
- Title: Excel Annoyances
- Author(s):
- Release date: December 2004
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9780596007287
You might also like
book
Excel 2007: The Missing Manual
Microsoft Excel continues to grow in power, sophistication, and capability, but one thing that has changed …
book
Microsoft® Office Excel 2003 Inside Out
Take your Microsoft Excel experience to the next level! This supremely organized reference packs hundreds of …
book
Excel 2000 in a Nutshell
By virtue of the estimated 20-million-strong installed base of Microsoft Office users, Excel has become the …
book
Business Solutions Formulas and Functions with Microsoft® Office Excel 2007
“If you’ve never quite grasped formulas and functions, Paul McFedries will radically expand your understanding and …