Learn Excel from Mr. Excel: 277 Excel Mysteries SolvedContaining 277 business case studies that illustrate nearly every aspect of Excel, this book presents real-life business problems and works them through to their solutions. In addition to exemplary solutions, each case analysis considers alternate approaches and gotchas, and includes a summary of the necessary commands and functions. Excel files that can be downloaded and worked through step-by-step are included for each case. |
Contents
| 3 | |
| 6 | |
| 8 | |
| 10 | |
| 11 | |
| 12 | |
| 13 | |
| 14 | |
USE GROUP OUTLINE BUTTONS TO COLLAPSE SUBTOTALED DATA | 384 |
COPY JUST TOTALS FROM SUBTOTALED DATA | 386 |
ENTER A GRAND TOTAL OF DATA MANUALLY SUBTOTALED | 389 |
WHY DO SUBTOTALS COME OUT AS COUNTS? | 392 |
SUBTOTAL MANY COLUMNS AT ONCE | 394 |
MY MANAGER WANTS SUBTOTALS ABOVE THE DATA | 396 |
ADD OTHER TEXT DATA TO THE AUTOMATIC SUBTOTAL LINES | 397 |
BE WARY | 402 |
| 17 | |
| 19 | |
| 22 | |
| 25 | |
| 27 | |
| 30 | |
| 34 | |
| 36 | |
| 38 | |
| 42 | |
| 44 | |
| 46 | |
| 47 | |
| 48 | |
| 50 | |
| 52 | |
| 56 | |
| 60 | |
| 61 | |
| 63 | |
| 64 | |
| 67 | |
RESTORE YOUR MENUS AFTER CUSTOMIZING | 72 |
QUICKLY COPY A FORMULA TO ALL ROWS OF DATA | 76 |
QUICKLY TURN A RANGE ON ITS SIDE | 78 |
STOP EXCEL FROM AUTOCORRECTING CERTAIN WORDS | 81 |
USE AUTOCORRECT TO ENABLE A SHORTCUT | 82 |
WHY WONT THE TRACK CHAGES FEATURE WORK IN EXCEL? | 83 |
COPY CELLS FROM ONE WORKSHEET TO MANY WORKSHEETS | 84 |
HAVE EXCEL TALK TO YOU | 86 |
ENTER SPECIAL SYMBOLS | 88 |
FIND TEXT ENTRIES | 90 |
CALCULATING WITH EXCEL | 95 |
COPY A FORMULA THAT CONTAINS RELATIVE REFERENCES | 97 |
COPY A FORMULA WHILE KEEPING ONE REFERENCE FIXED | 100 |
CREATE A MULTIPLICATION TABLE | 102 |
CALCULATE A SALES COMMISSION | 104 |
SIMPLIFY ENTRY OF DOLLAR SIGNS IN FORMULAS | 106 |
LEARN R1C1 REFERENCING TO UNDERSTAND FORMULA COPYING | 110 |
CREATE EASIERTOUNDERSTAND FORMULAS WITH NAMED RANGE | 115 |
USE NAMED CONSTANTS TO STORE NUMBERS | 119 |
BUILD A FORMULA USING LABELS INSTEAD OF CELL ADDRESSES | 120 |
USE NATURAL LANGUAGE FORMULAS TO REFER TO THE CURRENT ROW | 124 |
ASSIGN A FORMULA TO A NAME | 125 |
TOTAL WITHOUT USING A FORMULA | 133 |
COUNT AVERAGE ETC WITHOUT USING A FORMULA | 136 |
ADD TWO COLUMNS WITHOUT USING FORMULAS | 138 |
HOW TO CALCULATE SALES OVER QUOTA | 141 |
HOW TO JOIN TWO TEXT COLUMNS | 143 |
HOW TO SORT ON ONE SEGMENT OF AN ACCOUNT ID | 147 |
HOW TO ISOLATE THE CENTER PORTION OF AN ACCOUNT ID | 149 |
HOW TO ISOLATE EVERYTHING BEFORE A DASH IN A COLUMN BY USING FUNCTIONS | 150 |
HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER A DASH IN A COLUMN | 153 |
HOW TO USE FUNCTIONS TO ISOLATE EVERYTHING AFTER THE SECOND DASH IN A COLUMN | 155 |
HOW TO SEPARATE A PART NUMBER INTO THREE COLUMNS | 158 |
AVOID REF ERRORS WHEN DELETING COLUMNS | 162 |
CREATE RANDOM NUMBERS | 165 |
CREATE RANDOM NUMBERS TO SEQUENCE A CLASS OF STUDENTS | 168 |
PLAY DICE GAMES WITH EXCEL | 170 |
PLAY BUNCO WITH EXCEL | 173 |
PLAY CRAPS WITH EXCEL | 178 |
CREATE RANDOM LETTERS | 180 |
CONVERT NUMBERS TO TEXT | 183 |
CALCULATE A LOAN PAYMENT | 186 |
CALCULATE MANY SCENARIOS FOR LOAN PAYMENTS | 187 |
GET HELP ON ANY FUNCTION WHILE ENTERING A FORMULA | 189 |
DISCOVER NEW FUNCTIONS USING THE FX BUTTON | 192 |
THREE METHODS OF ENTERING FORMULAS | 194 |
USE AUTOSUM TO QUICKLY ENTER A TOTAL FORMULA | 199 |
AUTOSUM DOESNT ALWAYS PREDICT MY DATA CORRECTLY | 201 |
USE AUTOSUM BUTTON TO ENTER AVERAGES MIN MAX AND COUNT | 204 |
THE COUNT OPTION OF THE AUTOSUM DOESNT APPEAR TO WORK | 207 |
AUTOMATICALLY NUMBER A LIST OF EMPLOYEES | 212 |
RANK SCORES | 215 |
SORTING WITH A FORMULA | 217 |
RANK A LIST WITHOUT TIES | 221 |
ADD COMMENTS TO A FORMULA | 224 |
CALCULATE A MOVING AVERAGE | 225 |
CALCULATE A TRENDLINE FORECAST | 226 |
BUILD A MODEL TO PREDICT SALES BASED ON MULTIPLE REGRESSION | 231 |
USE F9 IN FORMULA BAR TO TEST A FORMULA | 235 |
QUICK CALCULATOR | 236 |
WHEN ENTERING A FORMULA YOU GET THE FORMULA INSTEAD OF THE RESULT | 237 |
CALCULATE A PERCENTAGE OF TOTAL | 241 |
CALCULATE A RUNNING PERCENTAGE OF TOTAL | 244 |
USE SIGN FOR EXPONENT | 247 |
CALCULATE A GROWTH RATE | 249 |
FIND THE AREA OF A CIRCLE | 251 |
FIGURE OUT LOTTERY PROBABILITY | 253 |
HELP YOUR KIDS WITH THEIR MATH | 254 |
MEASURE THE ACCURACY OF A SALES FORECAST | 256 |
ROUND PRICES TO NEXT HIGHEST 5 | 259 |
WHY IS THIS PRICE SHOWING 2785000001 CENTS? | 261 |
YOU CHANGE A CELL IN EXCEL BUT THE FORMULAS DO NOT CALCULATE | 263 |
USE PARENTHESES TO CONTROL ORDER OF CALCULATIONS | 264 |
BEFORE DELETING A CELL FIND OUT IF OTHER CELLS RELY ON IT | 266 |
NAVIGATE TO EACH PRECEDENT | 270 |
FORMULA AUDITING | 274 |
HOW IS THIS CELL CALCULATED? | 277 |
TOTAL MINUTES THAT EXCEED AN HOUR | 280 |
CONVERT TEXT TO MINUTES AND SECONDS | 287 |
CONVERT TEXT TO HOURS MINUTES AND SECONDS | 289 |
MM TO MSS | 293 |
DISPLAY DATES AS MONTHS | 299 |
GROUP DATES BY MONTH | 302 |
CALCULATE LAST DAY OF MONTH | 307 |
CREATE A TIMESHEET THAT CAN TOTAL OVER 24 HOURS | 310 |
FIND WHICH CUSTOMERS ARE IN AN EXISTING LIST | 313 |
USE VLOOKUP TO FIND WHICH CUSTOMERS ARE IN AN EXISTING LIST | 315 |
MATCH CUSTOMERS USING VLOOKUP | 316 |
WATCH FOR DUPLICATES WHEN USING VLOOKUP | 320 |
COUNT RECORDS THAT MATCH A CRITERIA | 322 |
BUILD A TABLE THAT WILL COUNT BY CRITERIA | 324 |
BUILD A SUMMARY TABLE TO PLACE EMPLOYEES IN AGE BANDS | 326 |
TOTAL REVENUE FROM ROWS THAT MATCH A CRITERION | 329 |
USE CONDITIONAL SUM WIZARD TO HELP WITH SUMF | 330 |
CREATE A CSE FORMULA TO BUILD A SUPER FORMULA | 336 |
LEARN TO USE BOOLEAN LOGIC FACTS TO SIMPLIFY LOGIC | 338 |
REPLACE IF FUNCTION WITH BOOLEAN LOGIC | 342 |
TEST FOR TWO CONDITIONS IN A SUM | 343 |
CAN THE RESULTS OF A FORMULA BE USED IN COUNTIF? | 346 |
BACK INTO AN ANSWER USING GOAL SEEK | 347 |
PROTECT CELLS WITH FORMULAS | 350 |
WRANGLING DATA | 355 |
HOW TO SET UP YOUR DATA FOR EASY SORTING AND SUBTOTALS | 357 |
HOW TO FIT A MULTILINE HEADING INTO ONE CELL | 358 |
HOW TO SORT DATA | 362 |
HOW TO SPECIFY MORE THAN THREE COLUMNS IN A SORT | 364 |
HOW TO SORT A REPORT INTO A CUSTOM SEQUENCE | 367 |
QUICKLY FILTER A LIST TO CERTAIN RECORDS | 370 |
FIND THE UNIQUE VALUES IN A COLUMN | 374 |
COPY MATCHING RECORDS TO A NEW WORKSHEET | 376 |
ADD SUBTOTALS TO A DATASET | 380 |
GENERAL PROTECTION FAULTS | 403 |
CREATE SUBTOTALS BY PRODUCT WITHIN REGION | 405 |
MY MANAGER WANTS THE SUBTOTAL LINES IN BOLD PINK TAHOMA FONT | 411 |
MY MANAGER WANTS A BLANK LINE AFTER EVERY SUBTOTAL | 413 |
SUBTOTAL ONE COLUMN AND SUBAVERAGE ANOTHER COLUMN | 429 |
HOW TO DO 40 DIFFERENT WHATIF ANALYSES QUICKLY | 435 |
REMOVE BLANKS FROM A RANGE | 439 |
REMOVE BLANKS FROM A RANGE WHILE KEEPING THE ORIGINAL SEQUENCE | 440 |
INCREASE A RANGE BY TWO PERCENT | 443 |
USE FIND AND REPLACE TO FIND AN ASTERISK | 445 |
USE A CUSTOM HEADER OF PROFIT LOSS | 447 |
USE CONSOLIDATION TO COMBINE TWO LISTS | 450 |
FIND TOTAL SALES BY CUSTOMER BY COMBINING DUPLICATES | 455 |
CREATE A SUMMARY OF FOUR LISTS | 458 |
NUMBER EACH RECORD FOR A CUSTOMER STARTING AT ONE FOR A NEW CUSTOMER | 461 |
ADD A GROUP NUMBER TO EACH SET OF RECORDS WITH A UNIQUE CUSTOMER NUMBER | 463 |
DEAL WITH DATA WHERE EACH RECORD TAKES FIVE PHYSICAL ROWS | 465 |
ADD A CUSTOMER NUMBER TO EACH DETAIL RECORD | 476 |
USE A PIVOT TABLE TO SUMMARISE DETAILED DATA | 480 |
YOUR MANAGER WANTS YOUR REPORT CHANGED | 487 |
MOVE OR CHANGE PART OF A PIVOT TABLE | 489 |
SEE DETAIL BEHIND ONE NUMBER IN A PIVOT TABLE | 491 |
UPDATE DATA BEHIND A PIVOT TABLE | 493 |
REPLACE BLANKS IN A PIVOT TABLE WITH ZEROES | 495 |
ADD OR REMOVE FIELDS FROM AN EXISTING PIVOT TABLE | 499 |
SUMMARIZE PIVOT TABLE DATA BY THREE MEASURES | 502 |
MAKE PIVOT TABLES BE TALLER THAN WIDE | 505 |
MANUALLY RESEQUENCE THE ORDER OF DATA IN A PIVOT TABLE | 506 |
PRESENT A PIVOT TABLE IN HIGHTOLOW ORDER BY REVENUE | 509 |
LIMIT A PIVOT REPORT TO SHOW JUST THE TOP 12 CUSTOMERS | 511 |
QUICKLY PRODUCE REPORTS FOR EACH REGION | 514 |
CREATE AN ADHOC REPORTING TOOL | 516 |
CREATE A UNIQUE LIST OF CUSTOMERS WITHA PIVOT TABLE | 517 |
CREATE A PIVOT TABLE WITH FEWER CLICKS | 519 |
CREATE A REPORT SHOWING COUNT MIN MAX AVERAGE ETC | 521 |
USE MULTIPLE DATA FIELDS AS A COLUMN FIELD | 522 |
COMPARE FOUR WAYS TO SHOW TWO DATA FIELDS INA PIVOT TABLE | 523 |
GROUP DAILY DATES UP BY MONTH IN A PIVOT TABLE | 526 |
GROUP BY WEEK IN A PIVOT TABLE | 528 |
PRODUCE AN ORDER LEADTIME REPORT | 530 |
USE AUTOFORMAT WITH PIVOT TABLES | 536 |
SPECIFY A NUMBER FORMAT FOR A PIVOTTABLE FIELD | 542 |
SUPPRESS TOTALS IN A PIVOT TABLE | 546 |
ELIMINATE BLANKS IN THE OUTLINE FORMAT OF A PIVOT TABLE | 549 |
USE A PIVOT TABLE TO COMPARE TWO LISTS | 554 |
CALCULATED FIELDS IN A PIVOT TABLE | 559 |
ADD A CALCULATED ITEM TO GROUP ITEMS IN APIVOT TABLE | 562 |
QUICKLY CREATE CHARTS FOR ANY REGION | 566 |
USE QUERY TO GET A UNIQUE SET OF RECORDS | 570 |
IMPORT A TABLE FROM A WEB PAGE INTO EXCEL | 580 |
HAVE WEB DATA UPDATE AUTOMATICALLY WHEN YOU OPEN WORKBOOK | 584 |
HAVE WEB DATA UPDATE AUTOMATICALLY EVERY TWO MINUTES | 586 |
THE SPACES IN THIS WEB DATA WONT GO AWAY | 588 |
USE A BUILTIN DATA ENTRY FORM | 592 |
TRANSFORM BLACK AND WHITE SPREADSHEETS INTO COLOR | 594 |
YOUR MANAGER IS OBSESSED WITH FORMATTING AND CANNOT MAKE UP HER MIND | 596 |
MAKING THINGS LOOK GOOD | 605 |
CREATE A CHART WITH ONE CLICK | 607 |
CHANGE A CHART FROM A CHART SHEET TO AN EMBEDDED CHART | 609 |
CUSTOMIZE ANYTHING ON A CHART WITH RIGHTCLICK | 610 |
HOW TO MINIMIZE OVERLAP OF PIE CHART LABELS | 618 |
ADD NEW DATA TO A CHART | 620 |
ADD A TRENDLINE TO A CHART | 622 |
DISPLAY PROFITABILITY IN A PROFIT WATERFALL CHART | 628 |
FOR EACH CELL IN COLUMN A HAVE THREE ROWS IN COLUMN B | 641 |
COPY FORMATTING TO A NEW RANGE | 643 |
COPY WITHOUT CHANGING BORDERS | 646 |
LEAVE HELPFUL NOTES WITH CELL COMMENTS | 648 |
CHANGE APPEARANCE OF CELL COMMENTS | 651 |
FORCE CERTAIN COMMENTS TO BE ALWAYS VISIBLE TO PROVIDE A HELP SYSTEM TO USERS OF YOUR SPREADSHEET | 656 |
CONTROL NAME THAT APPEARS IN COMMENTS | 658 |
CHANGE SHAPE OF COMMENT TO A STAR | 660 |
ADD A POPUP PICTURE OF AN ITEM IN A CELL | 664 |
ADD A POPUP PICTURE TO MULTIPLE CELLS | 670 |
CHANGE THE BACKGROUND OF THE WORKSHEET | 672 |
ADD A PRINTABLE BACKGROUND TO YOUR SPREADSHEET | 676 |
REMOVE HYPERLINKS AUTOMATICALLY INSERTED BY EXCEL | 680 |
CHANGE WIDTH OF ALL COLUMNS IN ONE COMMAND | 681 |
CONTROL PAGE NUMBERING IN A MULTISHEET WORKBOOK | 684 |
USE WHITE COLOR FOR FONTS TO HIDE DATA | 685 |
HIDE AND UNHIDE DATA | 687 |
TEMPORARILY SEE A HIDDEN COLUMN WITHOUT UNHIDING | 689 |
BUILD COMPLEX REPORTS WHERE COLUMNS IN SECTION 1 DONT LINE UP WITH SECTION 2 | 691 |
PASTE A LIVE PICTURE OF A CELL | 697 |
MONITOR FAROFF CELLS IN EXCEL 2002 AND LATER VERSIONS | 699 |
ADD A PAGE BREAK AT EACH CHANGE IN CUSTOMER | 701 |
USE HORIZONTAL PAGE BREAKS EVEN WHEN YOU USE FIT TO N PAGES WIDE | 707 |
HIDE ERROR CELLS WHEN PRINTING | 709 |
ORGANIZE YOUR WORKSHEET TABS WITH COLOR | 710 |
COPY CELL FORMATTING INCLUDING COLUMN WIDTHS | 711 |
WHY DOES EXCEL MARK ALL MY TRUE CELLS WITH AN INDICATOR? | 713 |
DEBUG FROM A PRINTED SPREADSHEET | 715 |
COPIED FORMULA HAS STRANGE BORDERS | 716 |
DOUBLE UNDERLINE A GRAND TOTAL | 718 |
USE THE BORDER TAB IN THE FORMAT CELLS DIALOG | 719 |
FIT A SLIGHTLY TOOLARGE VALUE IN A CELL | 722 |
SHOW RESULTS AS FRACTIONS | 724 |
COLOR ALL SALES GREEN FOR A DAY IF TOTAL SALES 999 | 727 |
COLOR SALES FOR A DAY THAT EXCEEDS 999 | 733 |
TURN OFF WRAP TEXT IN PASTED DATA | 737 |
DELETE ALL PICTURES IN PASTED DATA | 739 |
DRAW AN ARROW TO VISUALLY ILLUSTRATE THAT TWO CELLS ARE CONNECTED | 741 |
ADD AN AUTOSHAPE TO YOUR WORKSHEET | 743 |
DRAW PERFECT CIRCLES | 746 |
DRAW PERFECT SQUARES | 748 |
DRAW MORE THAN THE FOUR BASIC SHAPES | 750 |
CHANGE AN EXISTING AUTOSHAPE | 753 |
ADD TEXT TO AN AUTOSHAPE | 756 |
USE THE TOOLBAR TO CHANGE THREE COLORS OF AN AUTOSHAPE | 758 |
ROTATE AN AUTOSHAPE | 763 |
ALTER THE KEY INFLECTION POINT IN AN AUTOSHAPE | 764 |
ADD A SHADOW TO AN AUTOSHAPE | 767 |
ADD A 3D EFFECT TO AN AUTOSHAPE | 770 |
ADD CONNECTORS TO JOIN SHAPES | 774 |
JOIN TWO AUTOSHAPES | 777 |
CHANGE PROPERTIES OF ONLY ONE AUTOSHAPE IN A GROUP | 779 |
WHEN TWO AUTOSHAPES OVERLAP CONTROL WHICH IS ON TOP | 782 |
MAKE ANY LOGO INTO AN AUTOSHAPE | 784 |
USE THE SCRIBBLE TOOL | 786 |
PLACE CELL CONTENTS IN AN AUTOSHAPE | 789 |
DRAW BUSINESS DIAGRAMS WITH EXCEL | 793 |
DRAW ORG CHARTS WITH EXCEL | 801 |
ADD WORDART TO A CHART OR WORKSHEET | 808 |
USE MAPPOINT TO PLOT DATA ON A MAP | 818 |
ADD A DROPDOWN TO A CELL | 821 |
STORE LISTS FOR DROPDOWNS ON A HIDDEN SHEET | 825 |
ADD A TOOLTIP TO A CELL TO GUIDE THE PERSON USING THE WORKBOOK | 828 |
CONFIGURE VALIDATION TO EASE UP | 830 |
USE VALIDATION TO CREATE DEPENDENT LISTS | 833 |
AFTERWORD | 837 |
| 839 | |
BACK COVER | 854 |
Other editions - View all
Common terms and phrases
Additional Details Additional Information Air Canada Alignment arrow Australia Australia Automatic AutoShape Bell Canada Bill Jelen button calculate cell pointer chart choose Format Choose OK Click Color Commands Discussed copy the formula create dataset Date default Delete Diagram dialog box display Double-click drag Drawing toolbar dropdown enter the formula entries File Fill handle Follow these steps Font formula bar Functions Discussed Gotcha Group icon Insert labels menu Microsoft Excel mouse number formats OK Cancel Fig Options Page Fields Paste Special pivot table PivotChart PivotTable Field Print Problem Product Query range reference Region Result Right-click scroll Select cell sheet shown in Fig Smart Tag sort spreadsheet Steve-O Strategy subtotals Sum of Revenue Summary Sun Life Financial Tools Total Total Total Validation values VLOOKUP Wal-Mart Window Wizard WordArt workbook worksheet
Popular passages
Page 8 - Commands | Options ] To add a command to a toolbar: select a category and drag the command out of this dialog box to a toolbar. Categories: Commands...