Log In
Or create an account -> 
Imperial Library
  • Home
  • About
  • News
  • Upload
  • Forum
  • Help
  • Login/SignUp

Index
Title Page Copyright Page Table of Contents ABOUT THE AUTHOR ACKNOWLEDGMENTS DEDICATION FOREWORD PART 1 - 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 CHANGES 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 RANGES 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 T EST 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 SUMIF 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 SUMMARIZE 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 WITH A 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 IN A 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 A PIVOT 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 YOUR MANAGER IS OBSESSED WITH FORMATTING AND CANNOT MAKE UP HER MIND
PART 4 - MAKING THINGS LOOK GOOD
CREATE A CHART WITH ONE CLICK CHANGE A CHART FROM A CHART SHEET TO AN EMBEDDED CHART CUSTOMIZE ANYTHING ON A CHART WITH RIGHT-CLICK HOW TO MINIMIZE OVERLAP OF PIE CHART LABELS ADD NEW DATA TO A CHART ADD A TRENDLINE TO A CHART DISPLAY PROFITABILITY IN A PROFIT WATERFALL CHART FOR EACH CELL IN COLUMN A, HAVE THREE ROWS IN COLUMN B COPY FORMATTING TO A NEW RANGE COPY WITHOUT CHANGING BORDERS LEAVE HELPFUL NOTES WITH CELL COMMENTS CHANGE APPEARANCE OF CELL COMMENTS FORCE CERTAIN COMMENTS TO BE ALWAYS VISIBLE TO PROVIDE A HELP SYSTEM TO USERS OF YOUR SPREADSHEET CONTROL NAME THAT APPEARS IN COMMENTS CHANGE SHAPE OF COMMENT TO A STAR ADD A POP-UP PICTURE OF AN ITEM IN A CELL ADD A POP-UP PICTURE TO MULTIPLE CELLS CHANGE THE BACKGROUND OF THE WORKSHEET ADD A PRINTABLE BACKGROUND TO YOUR SPREADSHEET REMOVE HYPERLINKS AUTOMATICALLY INSERTED BY EXCEL CHANGE WIDTH OF ALL COLUMNS IN ONE COMMAND CONTROL PAGE NUMBERING IN A MULTISHEET WORKBOOK USE WHITE COLOR FOR FONTS TO HIDE DATA HIDE AND UNHIDE DATA TEMPORARILY SEE A HIDDEN COLUMN WITHOUT UNHIDING BUILD COMPLEX REPORTS WHERE COLUMNS IN SECTION 1 DON’T LINE UP WITH SECTION 2 PASTE A LIVE PICTURE OF A CELL MONITOR FAR-OFF CELLS IN EXCEL 2002 AND LATER VERSIONS ADD A PAGE BREAK AT EACH CHANGE IN CUSTOMER USE HORIZONTAL PAGE BREAKS EVEN WHEN YOU USE FIT TO N PAGES WIDE HIDE ERROR CELLS WHEN PRINTING ORGANIZE YOUR WORKSHEET TABS WITH COLOR COPY CELL FORMATTING, INCLUDING COLUMN WIDTHS WHY DOES EXCEL MARK ALL MY TRUE CELLS WITH AN INDICATOR? DEBUG FROM A PRINTED SPREADSHEET COPIED FORMULA HAS STRANGE BORDERS DOUBLE UNDERLINE A GRAND TOTAL USE THE BORDER TAB IN THE FORMAT CELLS DIALOG FIT A SLIGHTLY TOO-LARGE VALUE IN A CELL SHOW RESULTS AS FRACTIONS COLOR ALL SALES GREEN FOR A DAY IF TOTAL SALES > $999 COLOR SALES FOR A DAY THAT EXCEEDS $999 TURN OFF WRAP TEXT IN PASTED DATA DELETE ALL PICTURES IN PASTED DATA DRAW AN ARROW TO VISUALLY ILLUSTRATE THAT TWO CELLS ARE CONNECTED ADD AN AUTOSHAPE TO YOUR WORKSHEET DRAW PERFECT CIRCLES DRAW PERFECT SQUARES DRAW MORE THAN THE FOUR BASIC SHAPES CHANGE AN EXISTING AUTOSHAPE ADD TEXT TO AN AUTOSHAPE USE THE TOOLBAR TO CHANGE THREE COLORS OF AN AUTOSHAPE ROTATE AN AUTOSHAPE ALTER THE KEY INFLECTION POINT IN AN AUTOSHAPE ADD A SHADOW TO AN AUTOSHAPE ADD A 3-D EFFECT TO AN AUTOSHAPE ADD CONNECTORS TO JOIN SHAPES JOIN TWO AUTOSHAPES CHANGE PROPERTIES OF ONLY ONE AUTOSHAPE IN A GROUP WHEN TWO AUTOSHAPES OVERLAP, CONTROL WHICH IS ON TOP MAKE ANY LOGO INTO AN AUTOSHAPE USE THE SCRIBBLE TOOL PLACE CELL CONTENTS IN AN AUTOSHAPE DRAW BUSINESS DIAGRAMS WITH EXCEL DRAW ORG CHARTS WITH EXCEL ADD WORDART TO A CHART OR WORKSHEET USE MAPPOINT TO PLOT DATA ON A MAP ADD A DROPDOWN TO A CELL STORE LISTS FOR DROPDOWNS ON A HIDDEN SHEET ADD A TOOLTIP TO A CELL TO GUIDE THE PERSON USING THE WORKBOOK CONFIGURE VALIDATION TO “EASE UP” USE VALIDATION TO CREATE DEPENDENT LISTS
AFTERWORD
  • ← Prev
  • Back
  • Next →
  • ← Prev
  • Back
  • Next →

Chief Librarian: Las Zenow <zenow@riseup.net>
Fork the source code from gitlab.

This is a mirror of the Tor onion service:
http://kx5thpx2olielkihfyo4jgjqfb7zx7wxr3sd4xzt26ochei4m6f7tayd.onion