Learn Excel from Mr. Excel

Name: Learn Excel from Mr. Excel | 277 Excel Mysteries Solved
Brand: Holy Macro! Books
Price: 16.99 EUR
Availability: OnlineOnly

277 Excel Mysteries Solved

Bill Jelen(Author)

Holy Macro! Books (Publisher)

Published on 28. September 2005

877 pages

E-Book

PDF with Adobe-DRM

System requirements

978-1-932802-19-1 (ISBN)

€16.99incl. 7% vat

System requirements

for PDF with Adobe-DRM

E-Book Single Licence

Available for download

Description

More details

Other editions

Content

BOOK COVER
COPYRIGHT PAGE
TABLE OF CONTENTS
ABOUT THE AUTHOR
ACKNOWLEDGMENTS
DEDICATION
FOREWORD
PART-I: 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 CHAGES 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 RANGE
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 TEST 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 SUMF
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 SUMMARISE 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 WITHA 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 INA 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 APIVOT 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
INDEX
BACK COVER

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

Learn Excel from Mr. Excel

Description

More details

Other editions

Additional editions

Content

System requirements