book

Excel Annoyances

Name: Excel Annoyances
Author: Curtis D. Frye
ISBN: 9780596007287

by Curtis D. Frye

December 2004

Beginner

256 pages

10h 49m

English

O'Reilly Media, Inc.

Read now

Unlock full access

Excel Annoyances
Dedication
Introduction
WHY WRITE EXCEL ANNOYANCES?WHY THIS BOOK’S ESPECIALLY IMPORTANT RIGHT NOWIS EXCEL ANNOYANCES RIGHT FOR YOU?HAVE AN ANNOYANCE?HOW TO USE THIS BOOKCONVENTIONS USED IN THIS BOOKUSING CODE EXAMPLESABOUT THE AUTHORABOUT THE CONTRIBUTORSABOUT THE TECHNICAL TEAMACKNOWLEDGMENTS
1. Entering Data Annoyances
GENERAL DATA ENTRY ANNOYANCESKILL CLIPPYThe Annoyance:The Fix:RETAIN LEADING ZEROS WHEN YOU ENTER DATAThe Annoyance:The Fix:EXTEND A NUMERIC SERIES AUTOMATICALLYThe Annoyance:The Fix:USE THE SERIES DIALOG BOX TO DEFINE A SERIES OF VALUESThe Annoyance:The Fix:CREATE DATA SERIES MINUS THE MOUSEThe Annoyance:The Fix:CREATE A CUSTOM FILL SERIESThe Annoyance:The Fix:ENTER DATA INTO MULTIPLE WORKSHEETS AT ONCEThe Annoyance:The Fix:ALLOW MORE THAN ONE PERSON TO EDIT A FILE AT THE SAME TIMEThe Annoyance:The Fix:ADD A CARRIAGE RETURN TO A CELL’S CONTENTSThe Annoyance:The Fix:ADD SYMBOLS TO YOUR EXCEL WORKSHEETThe Annoyance:The Fix:RESTRICT AUTOCORRECT INTERFERENCEThe Annoyance:The Fix:KEEP WEB AND FILE ADDRESSES AS PLAIN TEXTThe Annoyance:The Fix:
IMPORTING DATA ANNOYANCES
COPYING A WORD TABLE INTO EXCEL 97 INTRODUCES BLANK ROWSThe Annoyance:The Fix:DATA IN WORD FILES ISN’T AVAILABLE FOR IMPORTThe Annoyance:The Fix:
CUT-AND-PASTE ANNOYANCES
PUT MORE THAN ONE ITEM ON THE CLIPBOARDThe Annoyance:The Fix:PREVENT THE OFFICE CLIPBOARD FROM APPEARINGThe Annoyance:The Fix:CONTROL PASTED CELL FORMATSThe Annoyance:The Fix:INSERT OR DELETE SINGLE CELLSThe Annoyance:The Fix:TRANSPOSE ROWS AND COLUMNSThe Annoyance:The Fix:CHANGE THE DEFAULT SAVE LOCATIONThe Annoyance:The Fix:
NAVIGATION AND DISPLAY ANNOYANCES
KEEP THE SAME ACTIVE CELL WHEN YOU MOVE TO A NEW WORKSHEETThe Annoyance:The Fix:SHRINK THE EXCEL WINDOWThe Annoyance:The Fix:MAGNIFY A SELECTIONThe Annoyance:The Fix:MOVE TO THE LAST ROW IN A LISTThe Annoyance:The Fix:KEEP HEADERS CONSTANT AS YOU SCROLLThe Annoyance:The Fix:SEARCH A PORTION OF A WORKSHEETThe Annoyance:The Fix:DIVIDE A WORKSHEET INTO MULTIPLE SCROLLABLE AREASThe Annoyance:The Fix:
DATA VALIDATION ANNOYANCES
RESTRICT DATA ENTRY WITH VALIDATION RULESThe Annoyance:The Fix:CREATE A FORM TO EASE DATA ENTRYThe Annoyance:The Fix:BASE VALIDATION RULES ON FORMULA RESULTSThe Annoyance:The Fix:USE DATA IN ANOTHER WORKSHEET AS VALIDATION CRITERIAThe Annoyance:The Fix:AVOID DUPLICATE ENTRIES IN A COLUMNThe Annoyance:The Fix:EXPLAIN DATA VALIDATION RULESThe Annoyance:The Fix:HIGHLIGHT INVALID WORKSHEET DATAThe Annoyance:The Fix:COPY A VALIDATION RULE TO ANOTHER CELLThe Annoyance:The Fix:PREVENT EXCEL FROM SCROLLING TOO QUICKLYThe Annoyance:The Fix:
2. Format Annoyances
CELL FORMATTING ANNOYANCESFORMAT PART OF A CELL’S CONTENTSThe Annoyance:The Fix:WRAP TEXT IN A CELL WITH JUST ONE MOUSE CLICKThe Annoyance:The Fix:CHANGE WORKSHEET TAB COLORSThe Annoyance:The Fix:
CONDITIONAL FORMATTING ANNOYANCES
CHANGE CELL FORMATTING BASED ON THE CELL’S VALUEThe Annoyance:The Fix:EXCEL APPLIES THE WRONG CONDITIONAL FORMATThe Annoyance:The Fix:LOCATE CELLS WITH CONDITIONAL FORMATSThe Annoyance:The Fix:

TEMPLATE ANNOYANCES
CREATE A WORKBOOK TEMPLATEThe Annoyance:The Fix:CREATE A WORKSHEET TEMPLATEThe Annoyance:The Fix:
COLOR MANAGEMENT ANNOYANCES
REPLACE REPEATED COLORS IN THE EXCEL COLOR PALETTEThe Annoyance:The Fix:TRANSLATE BETWEEN COLOR PALETTE POSITION, COLOR NAME, AND COLORINDEX VALUEThe Annoyance:The Fix:COPY ANOTHER WORKBOOK’S COLOR PALETTEThe Annoyance:The Fix:DISPLAY THE RGB VALUES OF COLORS IN A WORKBOOK’S COLOR PALETTEThe Annoyance:The Fix:MANAGE COLORS AND FORMATS WITH A CUSTOM ADD-INThe Annoyance:The Fix:
WORKBOOK FORMATTING ANNOYANCES
CHANGE EXCEL’S DEFAULT FONTThe Annoyance:The Fix:HOW TO TELL A STYLE FROM A FORMATThe Annoyance:The Fix:SIMPLIFY EXCEL FORMATTING WITH STYLESThe Annoyance:The Fix:SEARCH FOR CELLS WITH SPECIFIC FORMATTINGThe Annoyance:The Fix:THE CASE OF THE VANISHING GRIDLINESThe Annoyance:The Fix:HIDE AND UNHIDE ROWS AND COLUMNSThe Annoyance:The Fix:HIDE AND UNHIDE SHEETSThe Annoyance:The Fix:
CUSTOM FORMAT ANNOYANCES
CREATE CUSTOM NUMBER DISPLAY FORMATSThe Annoyance:The Fix:ADD TEXT TO A DISPLAYED NUMERICAL VALUEThe Annoyance:The Fix:ROUND VALUES WITHOUT USING THE ROUND() FUNCTIONThe Annoyance:The Fix:ROUND VALUES TO THE MILLIONS AND DISPLAY “MILLIONS” AFTER THE VALUEThe Annoyance:The Fix:ALIGN NUMBERS IN A COLUMN BY DECIMAL POINTThe Annoyance:The Fix:CREATE A CUSTOM DATE FORMATThe Annoyance:The Fix:CREATE CUSTOM TIME FORMATSThe Annoyance:The Fix:SETTING DATE AND TIME USING OTHER LOCAL RULESThe Annoyance:The Fix:APPLY SPECIAL FORMATS FOR ZIP CODESThe Annoyance:The Fix:ADDING CUSTOM FOREIGN CURRENCY SYMBOLSThe Annoyance:The Fix:
3. Formula Annoyances
FORMULA ENTRY AND EDITING ANNOYANCESPREVENT COPIED FORMULAS FROM CHANGING CELL REFERENCESThe Annoyance:The Fix:TOGGLE BETWEEN RELATIVE AND ABSOLUTE REFERENCESThe Annoyance:The Fix:REFER TO CELLS ON ANOTHER WORKSHEETThe Annoyance:The Fix:LEARN R1C1 NOTATIONThe Annoyance:The Fix:CREATE A REFERENCE TO A PIVOTTABLE CELLThe Annoyance:The Fix:DEFINE THE INTERSECTION OF TWO RANGES IN A FORMULAThe Annoyance:The Fix:NEST FUNCTIONS WITHIN FUNCTIONSThe Annoyance:The Fix:DELETE A FORMULA AND KEEP THE RESULTThe Annoyance:The Fix:MARK CELLS THAT CONTAIN A FORMULAThe Annoyance:The Fix:DISPLAY FORMULAS IN CELLSThe Annoyance:The Fix:DROP YOUR OWN PROCEDURE INTO A FORMULAThe Annoyance:The Fix:PERFORM THE SAME OPERATION ON A GROUP OF CELLSThe Annoyance:The Fix:MONITOR WATCH VALUESThe Annoyance:The Fix:ADD COMMENTS TO A FORMULAThe Annoyance:The Fix:IMPROVE EXCEL’S STATISTICAL ACCURACYThe Annoyance:The Fix:
FORMULA ERROR AND AUDITING ANNOYANCES
TELL EXCEL WHAT IS AN ERROR AND WHAT ISN’TThe Annoyance:The Fix:FIND FORMULA PRECEDENTSThe Annoyance:The Fix:DISPLAY ALL DEPENDENT CELLSThe Annoyance:The Fix:MASK ERROR MESSAGES IN A WORKSHEETThe Annoyance:The Fix:
ARRAY FORMULA ANNOYANCES
DEPLOY ARRAY FORMULASThe Annoyance:The Fix:
RECALCULATION ANNOYANCES
RECALCULATE NOW!The Annoyance:The Fix:SPEED UP RECALCULATIONSThe Annoyance:The Fix:
DATE AND TIME ANNOYANCES
DISPLAY PARTIAL HOURS AS DECIMAL NUMBERSThe Annoyance:The Fix:ROUND HOURS TO THE NEXT TENTH OF AN HOURThe Annoyance:The Fix:ROUND HOURS TO THE NEXT QUARTER OF AN HOURThe Annoyance:The Fix:SAVE A DATE OR TIME AS TEXTThe Annoyance:The Fix:FIND THE NUMBER OF WORKDAYS BETWEEN TWO DATESThe Annoyance:The Fix:ADD HOURS, MINUTES, OR SECONDS TO A TIMEThe Annoyance:The Fix:IMPRESS HISTORIANS WITH ROMAN NUMERALSThe Annoyance:The Fix:FIND THE NUMBER OF A WEEKThe Annoyance:The Fix:
NAMED RANGE ANNOYANCES
CREATE NAMED RANGESThe Annoyance:The Fix:UPDATE EXISTING FORMULAS TO USE NEWLY DEFINED NAMED RANGESThe Annoyance:The Fix:CREATE NAMED CONSTANTSThe Annoyance:The Fix:CREATE A SELF-EXTENDING NAMED RANGEThe Annoyance:The Fix:CREATE A NAMED RANGE FROM MULTIPLE SHEETSThe Annoyance:The Fix:PASTE A LIST OF NAMESThe Annoyance:The Fix:SELECT AN ENTIRE NAMED RANGEThe Annoyance:The Fix:AVOID RENAMING CELLS IN A NAMED RANGEThe Annoyance:The Fix:
TEXT FORMULA ANNOYANCES
PARSE FIXED-LENGTH SUBSTRINGSThe Annoyance:The Fix:PARSE DATA OF INCONSISTENT LENGTHSThe Annoyance:The Fix:DIVIDE TEXT INTO MULTIPLE COLUMNSThe Annoyance:The Fix:GET RID OF SPACES AND INVISIBLE CHARACTERSThe Annoyance:The Fix:
COUNTING AND CONDITIONAL SUM ANNOYANCES
SUM VALUES THAT MEET A CRITERIONThe Annoyance:The Fix:COUNT VALUES THAT MEET A CRITERIONThe Annoyance:The Fix:COUNT NONBLANK CELLS IN A RANGEThe Annoyance:The Fix:COUNT BLANK CELLS IN A RANGEThe Annoyance:The Fix:COUNT THE NUMBER OF UNIQUE VALUES IN A RANGEThe Annoyance:The Fix:
4. Manipulating Data Annoyances
SORTING AND FILTERING ANNOYANCESSORT BY VALUES IN MULTIPLE COLUMNSThe Annoyance:The Fix:SORT DATA BY A CUSTOM SORT ORDERThe Annoyance:The Fix:SORT DATA LEFT TO RIGHTThe Annoyance:The Fix:FILTER WORKSHEET DATAThe Annoyance:The Fix:DISPLAY TOP OR BOTTOM VALUESThe Annoyance:The Fix:CREATE A MULTILEVEL FILTERThe Annoyance:The Fix:FIND DUPLICATE ENTRIES IN A DATA LISTThe Annoyance:The Fix:COPY ONLY VISIBLE CELLS FROM A FILTERED LISTThe Annoyance:The Fix:SUMMARIZE VISIBLE CELLS ONLYThe Annoyance:The Fix:
LOOKUP FUNCTION ANNOYANCES
LOOK UP VALUES IN DATA LISTSThe Annoyance:The Fix:LOOK UP A VALUE IN ANY COLUMNThe Annoyance:The Fix:CORRECT AN INCORRECT VLOOKUP() RESULT IN EXCEL 97The Annoyance:The Fix:TEXT-NUMBER MIX OVERWHELMS LOOKUPThe Annoyance:The Fix:PERFORM A CASE-SENSITIVE LOOKUPThe Annoyance:The Fix:WORK WITH INCORRECT TIMES SERIESThe Annoyance:The Fix:SPEED UP MULTIPLE LOOKUPSThe Annoyance:The Fix:FIND THE FIRST OR LAST MATCH IN AN ARRAYThe Annoyance:The Fix:
WHAT-IF ANALYSIS ANNOYANCES
CREATE A SCENARIOThe Annoyance:The Fix:FIND A VALUE THAT GENERATES A SPECIFIC RESULTThe Annoyance:The Fix:USE SOLVER TO SOLVE MULTIVARIATE PROBLEMSThe Annoyance:The Fix:PLACE DATA ANALYSIS ON A MENUThe Annoyance:The Fix:
PIVOTTABLE ANNOYANCES
LEARN TO USE PIVOTTABLESThe Annoyance:The Fix:CREATE A PIVOTTABLEThe Annoyance:The Fix:PIVOT A PIVOTTABLEThe Annoyance:The Fix:FILTER PIVOTTABLE DATAThe Annoyance:The Fix:CREATE ADVANCED PIVOTTABLE FILTERSThe Annoyance:The Fix:FILTER A PIVOTTABLE BY ANY FIELDThe Annoyance:The Fix:FIND PIVOTTABLE ADD-INSThe Annoyance:The Fix:APPLY AUTOFORMAT TO A PIVOTTABLEThe Annoyance:The Fix:STOP PIVOTTABLES FROM PIVOTING!The Annoyance:The Fix:PIVOTTABLE DATA DISPLAYS AS NUMBER SIGNSThe Annoyance:The Fix:“PIVOTTABLE IS NOT VALID” ERRORThe Annoyance:The Fix:PIVOTTABLE LOSES FORMATTINGThe Annoyance:The Fix:USE CALCULATED FIELDS IN A PIVOTTABLEThe Annoyance:The Fix:CHANGE SUMMARY CALCULATIONS IN A PIVOTTABLEThe Annoyance:The Fix:HIDDEN DATA DOESN’T APPEAR IN THE SUMMARYThe Annoyance:The Fix:“REFERENCE NOT VALID” ERRORThe Annoyance:The Fix:DISPLAY PIVOTTABLE DATA AS A PERCENTAGE OF A TOTALThe Annoyance:The Fix:
5. Chart Annoyances
CHART CREATION AND FORMATTING ANNOYANCESINSTANT CHARTSThe Annoyance:The Fix:SPIFF UP A CHART QUICKLYThe Annoyance:The Fix:UPDATE A CHART TO INCLUDE NEW EXCEL DATAThe Annoyance:The Fix:CREATE AND UPDATE CHARTS FROM QUERY DATAThe Annoyance:The Fix:EXPLODE YOUR PIE CHARTThe Annoyance:The Fix:TWO CHARTS FOR THE PRICE OF ONEThe Annoyance:The Fix:CHART DATA SERIES PROPERLYThe Annoyance:The Fix:FORMAT CHART ELEMENTSThe Annoyance:The Fix:TAKE CONTROL OF YOUR AXESThe Annoyance:The Fix:ADD TEXT TO A CHART AXISThe Annoyance:The Fix:GLITZ UP YOUR COLUMNSThe Annoyance:The Fix:ADD ERROR BARS TO A GRAPHThe Annoyance:The Fix:
CHART MANIPULATION ANNOYANCES
COPY CHARTS AS PICTURESThe Annoyance:The Fix:MOVE A CHART TO A SEPARATE SHEETThe Annoyance:The Fix:PRINT A CHART WITHOUT SURROUNDING DATAThe Annoyance:The Fix:CHOOSE YOUR AXISThe Annoyance:The Fix:SEEKING YOUR GOAL WITH A CHARTThe Annoyance:The Fix:
PIVOTCHART ANNOYANCES
PIVOTCHARTS AND PIVOTTABLES GO TOGETHER LIKE ...The Annoyance:The Fix:FIRST A PIVOTCHART, THEN A PIVOTTABLEThe Annoyance:The Fix:MAKE NORMAL CHARTS FROM PIVOTTABLESThe Annoyance:The Fix:
CHART INTERACTION ANNOYANCES
PRINT AN EXCEL 97 CHART FROM POWERPOINTThe Annoyance:The Fix:CHART MACRO CRASHES EXCELThe Annoyance:The Fix:CHART GROWS WHEN MOVED TO TEXT BOXThe Annoyance:The Fix:DISAPPEARING SCATTER PLOT POINTS ON THE WEBThe Annoyance:The Fix:PASTED CHART TURNS INTO GRAY RECTANGLEThe Annoyance:The Fix:CAN’T PUBLISH A CHART FROM MULTIPLE SHEETSThe Annoyance:The Fix:DRAGGING EXCEL CHART CRASHES WORDThe Annoyance:The Fix:CHART TITLES SHOW ONLY ONE LINEThe Annoyance:The Fix:PREVIEW A CHART, CRASH EXCEL 97The Annoyance:The Fix:FALSE PERCENTAGES IN PIE CHARTThe Annoyance:The Fix:THE CASE OF THE DISAPPEARING AXIS LABELSThe Annoyance:The Fix:CHART IMAGE CHOPPEDThe Annoyance:The Fix:
CHART ADD-INS AND HELPER APPLICATIONS
MAGIC ADD-INS FOR CHARTS AND GRAPHSThe Annoyance:The Fix:BETTER 2D AND 3D CHARTSThe Annoyance:The Fix:
3D CHART ANNOYANCES
CAN’T CHANGE 3D COLORS IN EXCEL 97The Annoyance:The Fix:FLOOR TURNS BLACK IN 3D CHARTThe Annoyance:The Fix:PIE EXPLOSIONS ARE NO FUN IN 3DThe Annoyance:The Fix:
6. Exhanging Data Annoyances
MS QUERY AND DATABASE ANNOYANCESBRING IN SELECTED DATABASE RECORDSThe Annoyance:The Fix:AVOID DUPLICATE RECORDSThe Annoyance:The Fix:CONTROL THE NUMBER OF IMPORTED RECORDSThe Annoyance:The Fix:GET MORE THAN 65,536 RECORDS INTO EXCELThe Annoyance:The Fix:ADD A COUNTING FIELD TO QUERY RESULTSThe Annoyance:The Fix:IMPORT DATA FROM MORE THAN ONE TABLEThe Annoyance:The Fix:TRANSLATE ERROR MESSAGES INTO ENGLISHThe Annoyance:The Fix:
IMPORT AND EXPORT ANNOYANCES
MOVE DATA BACK AND FORTH FROM EXCEL TO ACCESSThe Annoyance:The Fix:Moving data from Excel to AccessMoving data from Access to ExcelTRUNCATED DATA IN ACCESSThe Annoyance:The Fix:UPDATE EMBEDDED POWERPOINT CHARTThe Annoyance:The Fix:INCLUDE ANOTHER FILE IN AN EXCEL WORKBOOKThe Annoyance:The Fix:LINK TO ANOTHER FILE FROM AN EXCEL WORKBOOKThe Annoyance:The Fix:
DATA FORMAT ANNOYANCES
PLACE EXCEL DATA ON THE WEBThe Annoyance:The Fix:PLACE A USABLE WORKSHEET ON A WEB PAGEThe Annoyance:The Fix:CONTROL THE WEB PAGE WORKSHEETThe Annoyance:The Fix:BREAK LINKS TO EXTERNAL DATAThe Annoyance:The Fix:EXTERMINATE EXTERNAL LINKSThe Annoyance:The Fix:IMPORT SELECTED FIELDS ONLYThe Annoyance:The Fix:PREPARE DATA FOR A DATABASEThe Annoyance:The Fix:EXPORTED DATA LOSES 100 YEARSThe Annoyance:The Fix:OUTLOOK MESSAGE INCLUDES TOO MUCH WORKSHEETThe Annoyance:The Fix:PIVOTTABLE CONTAINS TWO FIELDS WITH THE SAME NAMEThe Annoyance:The Fix:PIVOTTABLE WORKS IN EXCEL BUT NOT ON THE WEBThe Annoyance:The Fix:FILE EXPORT MACRO IGNORES REGIONAL SETTINGSThe Annoyance:The Fix:
7. Printing Annoyances
BASIC PRINTING ANNOYANCESSQUEEZE THAT ENTIRE WORKSHEET ONTO ONE PAGEThe Annoyance:The Fix:PRINT ONLY SELECTED REGIONSThe Annoyance:The Fix:PRINT AN ENTIRE WORKBOOK—OR MULTIPLE SELECTED SHEETSThe Annoyance:The Fix:PRINT MULTIPLE WORKBOOKS IN ONE PRINT JOBThe Annoyance:The Fix:PRINT A LIST FROM WITHIN A WORKSHEETThe Annoyance:The Fix:MAKE PART OF THE WORKSHEET PRINT EVERY TIMEThe Annoyance:The Fix:
PRINT LAYOUT ANNOYANCES
PUT PAGE BREAKS WHERE YOU WANT ‘EMThe Annoyance:The Fix:CHANGE THE ORDER IN WHICH PAGES ARE PRINTEDThe Annoyance:The Fix:CHANGE WORKBOOK MARGINSThe Annoyance:The Fix:SET PAPER SIZE AND ORIENTATIONThe Annoyance:The Fix:ONE PAGE IN LANDSCAPE, ANOTHER IN PORTRAITThe Annoyance:The Fix:CUSTOM FOLIOSThe Annoyance:The Fix:MORE ROOM FOR HEADERS AND FOOTERSThe Annoyance:The Fix:
PRINT CONTENT ANNOYANCES
ADD GRIDLINES TO A PRINTOUTThe Annoyance:The Fix:ADD TIME AND DATE TO YOUR PRINTOUTThe Annoyance:The Fix:PRINT COLUMN LETTERS AND ROW NUMBERSThe Annoyance:The Fix:PRINT COLUMN HEADINGS AND ROW LABELS ON EVERY PAGEThe Annoyance:The Fix:PUT A SPECIAL REPORT TITLE ON EVERY PAGEThe Annoyance:The Fix:PRINT WORKSHEET COMMENTS IN CONTEXTThe Annoyance:The Fix:PRINT PIVOTTABLES, NOT THE REST OF THE WORKSHEETThe Annoyance:The Fix:BLOCK PRINTOUT OF ERROR MESSAGESThe Annoyance:The Fix:
PRINTER MANAGEMENT ANNOYANCES
SET A DEFAULT PRINTERThe Annoyance:The Fix:PRINT A WORKSHEET IN GLORIOUS BLACK AND WHITEThe Annoyance:The Fix:SET OPTIMUM PRINT QUALITYThe Annoyance:The Fix:CHECK THE STATUS OF YOUR PRINT JOBThe Annoyance:The Fix:SEND A WORKSHEET AS A FAXThe Annoyance:The Fix:
8. Customization, Macro, and VBA Annoyances
INTERFACE CUSTOMIZATION ANNOYANCESEXPAND THE RECENTLY USED FILE LISTThe Annoyance:The Fix:ALWAYS SHOW FULL MENUSThe Annoyance:The Fix:PROTECT YOUR WORKBOOKThe Annoyance:The Fix:PROTECT YOUR DATAThe Annoyance:The Fix:REQUIRE A PASSWORD TO OPEN OR MODIFY A WORKBOOKThe Annoyance:The Fix:OPEN A PROTECTED WORKBOOKThe Annoyance:The Fix:FORGET PASSWORDS! USE ENCRYPTIONThe Annoyance:The Fix:USE EXCEL 2003 WORKBOOK PERMISSIONSThe Annoyance:The Fix:INCREASE THE NUMBER OF UNDO LEVELSThe Annoyance:The Fix:
TOOLBAR AND MENU BAR ANNOYANCES
SHOW ALL YOUR BUTTONSThe Annoyance:The Fix:ADD STANDARD BUTTONS TO YOUR TOOLBARThe Annoyance:The Fix:ADD A NEW BUTTON TO A TOOLBARThe Annoyance:The Fix:CHANGE THE APPEARANCE OF A TOOLBAR BUTTONThe Annoyance:The Fix:HIDE OR DISPLAY A BUILT-IN TOOLBARThe Annoyance:The Fix:CREATE A CUSTOM TOOLBARThe Annoyance:The Fix:RESET A TOOLBAR OR MENU TO ITS DEFAULT SETTINGSThe Annoyance:The Fix:ADD A BUILT-IN MENU TO A TOOLBARThe Annoyance:The Fix:CREATE AND POPULATE A CUSTOM MENUThe Annoyance:The Fix:
MACRO ANNOYANCES
RECORD A MACROThe Annoyance:The Fix:RUN A MACROThe Annoyance:The Fix:RECORD A MACRO WITH RELATIVE REFERENCESThe Annoyance:The Fix:PLACE YOUR MACRO ON A TOOLBAR OR MENUThe Annoyance:The Fix:STEP THROUGH A MACROThe Annoyance:The Fix:EDIT A MACROThe Annoyance:The Fix:RUN A MACRO STORED IN ANOTHER WORKBOOKThe Annoyance:The Fix:
MACRO SECURITY ANNOYANCES
AVOID MACRO VIRUSESThe Annoyance:The Fix:ADD A PUBLISHER TO YOUR TRUSTED PUBLISHERS LISTThe Annoyance:The Fix:REMOVE A PUBLISHER FROM YOUR TRUSTED PUBLISHERS LISTThe Annoyance:The Fix:ACQUIRE A DIGITAL CERTIFICATEThe Annoyance:The Fix:DIGITALLY SIGN A WORKBOOKThe Annoyance:The Fix:
VBA ANNOYANCES
MEET OBJECT-ORIENTED PROGRAMMINGThe Annoyance:The Fix:CREATE A NEW CODE MODULEThe Annoyance:The Fix:VIEW THE OBJECT MODELThe Annoyance:The Fix:CREATE A SUBPROCEDUREThe Annoyance:The Fix:FUN WITH VARIABLESThe Annoyance:The Fix:DECLARE VARIABLES BEFORE USING THEMThe Annoyance:The Fix:ADD COMMENTS TO VBA CODEThe Annoyance:The Fix:CONTINUE A LINE OF CODE ON A SECOND LINEThe Annoyance:The Fix:CREATE VARIABLES THAT REFER TO OBJECTSThe Annoyance:The Fix:CREATE A FUNCTION PROCEDUREThe Annoyance:The Fix:FORCE A MACRO TO AFFECT THE ACTIVE WORKBOOKThe Annoyance:The Fix:PREVENT A PROCEDURE FROM BEING CALLED FROM ANOTHER WORKBOOKThe Annoyance:The Fix:BEGONE DULL SCREEN FLICKERThe Annoyance:The Fix:MACROS MAKE MY COMPUTER SLUGGISHThe Annoyance:The Fix:USE AN IF...THEN CRITERION STATEMENTThe Annoyance:The Fix:CREATE A STRING FROM VALUES IN MORE THAN ONE CELLThe Annoyance:The Fix:AFFECT EVERY MEMBER OF A COLLECTIONThe Annoyance:The Fix:RUN A MACRO WHEN AN EVENT HAPPENSThe Annoyance:The Fix:REQUIRE A PASSWORD TO VIEW VBA CODEThe Annoyance:The Fix:ABBREVIATE OBJECT REFERENCES IN VBA CODEThe Annoyance:The Fix:EXCEL OPENS A WORKBOOK IN REPAIR MODEThe Annoyance:The Fix:CAN’T SAVE FILE WITHOUT NETWORK CONNECTIONThe Annoyance:The Fix:RUNTIME RUNAROUNDThe Annoyance:The Fix:RANGE.CALCULATE ERRORThe Annoyance:The Fix:
HANDY VBA PROCEDURES
CREATE A WORKBOOK WITH A WORKSHEET FOR EACH MONTHThe Annoyance:The Fix:PICK RANDOM ENTRIES FROM A LISTThe Annoyance:The Fix:GENERATE NONREPEATING RANDOM VALUESThe Annoyance:The Fix:
9. Excel 2003 Annoyances
EXCEL 2003 LIST ANNOYANCESCREATE A LISTThe Annoyance:The Fix:REMOVE LIST BORDERSThe Annoyance:The Fix:
EXCEL 2003 XML ANNOYANCES
GET A GRIP ON XMLThe Annoyance:The Fix:INFER A SCHEMA FROM XML DATAThe Annoyance:The Fix:TROUBLESHOOT IMPORTING SCHEMASThe Annoyance:The Fix:STEP THROUGH AN XML SCHEMAThe Annoyance:The Fix:CREATE A DATA MAPThe Annoyance:The Fix:POPULATE AN XML-MAPPED WORKBOOKThe Annoyance:The Fix:EXPORT DATA FROM AN XML-MAPPED WORKBOOKThe Annoyance:The Fix:
EXCEL 2003 MENU AND FUNCTION ANNOYANCES
CLOSE BUTTON IS UNAVAILABLEThe Annoyance:The Fix:CHART TOOL GENERATES WRONG R-SQUARED VALUEThe Annoyance:The Fix:EXCEL RANDOM NUMBER GENERATOR GENERATES NEGATIVE NUMBERSThe Annoyance:The Fix:
EXCEL 2003 PROGRAM INTERACTION ANNOYANCES
CAN’T EXPORT A WEB PAGE TO AN EXCEL WORKBOOKThe Annoyance:The Fix:EXCEL CRASHES WHEN I RUN LIVEMEETINGThe Annoyance:The Fix:
Index
Colophon

Content preview from Excel Annoyances

Chapter 3. Formula Annoyances

Transforming the data in your worksheets using formulas is what Excel does best. What could be annoying about that? Lots! Take one horrible example: consider what happens when you create a formula in one cell and copy it to another cell. If you don’t build the formula just right, the cell references in the formula might change based on where you pasted the copied formula! I’ll show you how to prevent this and other annoyances from happening.

Dates and times can present significant challenges in formulas. The arithmetic isn’t particularly hard—it just doesn’t always work the way you’d expect it to. Excel uses an exotic numerical system to refer to dates that seems totally arbitrary. But don’t worry: you’ll learn how to deal with this and other Excel quirks by the time you finish this chapter.

Finally, you’ll learn how to wrestle array formulas to the ground. This is one of Excel’s most powerful tools and one of the most annoying, because array formulas can be way confusing at first. But once you understand them, they end up saving you a lot of time.

FORMULA ENTRY AND EDITING ANNOYANCES

PREVENT COPIED FORMULAS FROM CHANGING CELL REFERENCES

The Annoyance:

I track sales for a department store using the worksheet shown in Figure 3-1. My boss asked me to highlight sales for the jewelry department, so I copied the formula from cell F15 to cell F18—but the formula changed from =SUM(F3:F14) to =SUM(F6:F17). I’m screwed! How do I keep that formula from changing?

Figure 3-1. When ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.

Read now

Unlock full access

More than 5,000 organizations count on O’Reilly

O’Reilly covers everything we've got, with content to help us build a world-class technology community, upgrade the capabilities and competencies of our teams, and improve overall team performance as well as their engagement.

Julian F.

Head of Cybersecurity

I wanted to learn C and C++, but it didn't click for me until I picked up an O'Reilly book. When I went on the O’Reilly platform, I was astonished to find all the books there, plus live events and sandboxes so you could play around with the technology.

Addison B.

Field Engineer

I’ve been on the O’Reilly platform for more than eight years. I use a couple of learning platforms, but I'm on O'Reilly more than anybody else. When you're there, you start learning. I'm never disappointed.

Amir M.

Data Platform Tech Lead

I'm always learning. So when I got on to O'Reilly, I was like a kid in a candy store. There are playlists. There are answers. There's on-demand training. It's worth its weight in gold, in terms of what it allows me to do.

Mark W.

Embedded Software Engineer

Publisher Resources

ISBN: 0596007280Catalog Page Errata

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel Annoyances

by Curtis D. Frye

Chapter 3. Formula Annoyances

FORMULA ENTRY AND EDITING ANNOYANCES