EXCEL TIMESAVING TECHNIQUES
PART V: WORKSHEET EDITING TIMESAVERS TECHNIQUE 31: QUICKLY FINDING THE WORKBOOK YOU WANT TO EDIT OPENING RECENTLY USED FILES MAKING THE MOST OF THE FILE SEARCH TASK PANE DOING A BASIC FILE SEARCH CONDUCTING THE FILE SEARCH AND USING THE RESULTS DOING AN ADVANCED FILE SEARCH SEARCHING FOR FILES IN THE OPEN DIALOG BOX TECHNIQUE 32: CONTROLLING THE WORKSHEET WINDOW DISPLAY ZOOMING IN FOR THE EDITS FROZEN PANES IN MY WINDOWS A WORKSHEET WITH A CUSTOM VIEW TECHNIQUE 33: MANAGING WORKSHEET WINDOWS OPENING PANES IN THE WORKSHEET WINDOW COMPARING SHEETS IN THE SAME WORKBOOK COMPARING SHEETS IN SEPARATE WORKBOOKS TECHNIQUE 34: QUICK AND EASY INSERTION AND DELETION INSERTING AND DELETING CELLS IN AN EXISTING RANGE INSERTING AND DELETING ROWS AND COLUMNS INSERTING AND DELETING WORKSHEETS TECHNIQUE 35: OUTLINE AND SUBTOTAL MAGIC ADDING OUTLINE LEVELS TO A TABLE OR LIST SUBTOTALING A TABLE OR LIST TECHNIQUE 36: CONSOLIDATING DATA FROM DIFFERENT WORKSHEETS CONSOLIDATING DATA BY POSITION CONSOLIDATING DATA BY CATEGORY LINKING CONSOLIDATED DATA TECHNIQUE 37: EDITING WITH SEARCH & REPLACE AND SPELL CHECK USING FIND AND REPLACE ELIMINATING TYPING ERRORS WITH SPELL CHECK CUSTOMIZING THE SPELL CHECK SETTINGS ADDING WORDS TO A CUSTOM DICTIONARY PART VI: TIPS FOR PRINTING, SHARING, AND REVIEWING WORKBOOKS TECHNIQUE 38: SPREADSHEET SECURITY ASSIGNING A PASSWORD FOR OPENING A WORKBOOK OPENING A PASSWORD-PROTECTED WORKBOOK CHANGING OR DELETING PASSWORDS PROTECTING THE WORKSHEET AGAINST UNWANTED CHANGES UNLOCKING CELLS FOR DATA ENTRY TURNING ON WORKSHEET PROTECTION REMOVING PROTECTION FROM A WORKSHEET ENABLING CELL RANGE EDITING BY CERTAIN USERS PROTECTING THE STRUCTURE OF THE WORKBOOK FILE HIDING SENSITIVE WORKSHEET DATA TECHNIQUE 39: PRINTING TRICKS FOR FLAWLESS REPORTS MAKING LAST-MINUTE ADJUSTMENTS WITH PRINT PREVIEW MANIPULATING THE MARGINS AND COLUMN WIDTHS MANIPULATING THE PAGE SETTINGS CONTROLLING BAD PAGE BREAKS USING THE PAGE BREAK PREVIEW FEATURE CHANGING THE SCALE OF THE PRINTING ADDING HEADINGS TO YOUR REPORT HEADERS AND FOOTERS MADE TO ORDER PRINT TITLES ON EVERY PAGE PRINTING THE FORMULAS IN THE REPORT PRINTING THE CHARTS IN THE REPORT TECHNIQUE 40: SHARING DATA WITH OTHER OFFICE PROGRAMS SWAPPING DATA VIA THE CLIPBOARD IMPORTING TEXT FILES INTO EXCEL EMBEDDING EXCEL DATA IN OTHER OFFICE DOCUMENTS EMBEDDED WORKSHEET DATA LINKING WORKSHEET DATA TECHNIQUE 41: SHARING WORKBOOKS ON A NETWORK LET’S ALL LEARN TO SHARE EDITING CHANGES NOT AVAILABLE TO A SHARED WORKBOOK SHARING A WORKBOOK SETTING YOUR SHARING OPTIONS TURNING ON CHANGE TRACKING MERGING CHANGES FROM DIFFERENT USERS CONFLICT RESOLUTION WORKSHEET STYLE ACCEPTING OR REJECTING HIGHLIGHTED CHANGES TURNING OFF FILE SHARING MERGING DIFFERENT COPIES OF A SHARED WORKBOOK DISTRIBUTING THE COPIES MERGING THE CHANGES TECHNIQUE 42: SENDING WORKBOOKS OUT FOR REVIEW GETTING A WORKBOOK READY FOR REVIEW GETTING YOUR TWO CENTS IN COMMENTS: NOW YOU SEE THEM, NOW YOU DON’T EDITING AND FORMATTING COMMENTS DELETING COMMENTS IN A WORKSHEET SENDING OUT A WORKBOOK FOR REVIEW REPLYING WITH CHANGES MERGING CHANGES INTO THE ORIGINAL WORKBOOK PART VII: STREAMLINING DATA LISTING AND DATA ANALYSIS TECHNIQUE 43: ADDING AND EDITING DATA LISTS WITH THE DATA FORM CREATING A NEW DATA LIST AND DATA FORM ADDING NEW RECORDS WITH THE DATA FORM FINDING RECORDS WITH THE DATA FORM EDITING RECORDS IN THE DATA FORM REMOVING RECORDS FROM THE DATA LIST WITH THE DATA FORM TECHNIQUE 44: SORTING WORKSHEET DATA DON’T BE OUT OF SORTS SORTING RECORDS IN A DATA LIST SORTING ON A RECORD NUMBER FIELD TO RESTORE A LIST TO ITS ORIGINAL ORDER SORTING A LIST ON MORE THAN THREE KEY FIELDS SORTING THE FIELD NAMES IN A DATA LIST TECHNIQUE 45: QUICK AND EASY BASIC DATA LIST FILTERING AUTOFILTER BASICS MAKING IT INTO THE TOP-TEN LIST SAVING SUBSETS OF A DATA LIST AS CUSTOM VIEWS CUSTOMIZING THE AUTOFILTER SETTINGS TECHNIQUE 46: MORE DATA LIST FILTERING PLUS STATISTICAL ANALYSIS PUTTING THE ADVANCED FILTER IN SERVICE SPECIFYING FILTERING CRITERIA SETTING UP AND AND OR FILTERING CRITERIA SETTING UP CALCULATED FILTERING CRITERIA GETTING DATA LIST STATISTICS TECHNIQUE 47: DOING WHAT-IF ANALYSIS IN A SNAP WITH DATA TABLES CREATING A ONE-VARIABLE DATA TABLE CREATING A TWO-VARIABLE DATA TABLE TECHNIQUE 48: EASY WHAT-IF ANALYSIS THROUGH SCENARIOS AND GOAL SEEKING EXPLORING DIFFERENT SCENARIOS REACHING A TARGET WITH GOAL SEEKING PERFORMING GOAL SEEKING GOAL SEEKING GRAPH STYLE TECHNIQUE 49: SUMMARIZING DATA WITH PIVOT TABLES AND PIVOT CHARTS CREATING PIVOT TABLES PIVOTING THE FIELDS IN THE TABLE FORMATTING THE VALUES IN THE PIVOT TABLE SELECTING NEW SUMMARY FUNCTIONS CREATING A CALCULATED FIELD FOR THE PIVOT TABLE CREATING A PIVOT CHART PART VIII: INTERNET-RELATED TIMESAVERS TECHNIQUE 50: SAVING WORKSHEETS AS WEB PAGES SAVING WORKSHEETS AS WEB PAGES OPENING YOUR NEW WEB PAGE AT THE TIME YOU SAVE IT SAVING ALL THE WORKSHEETS IN A WORKBOOK SAVING JUST PART OF A WORKSHEET ADDING DATA TO AN EXISTING WEB PAGE CREATING INTERACTIVE WEB PAGES TECHNIQUE 51: IMPORTING WEB DATA INTO THE WORKSHEET CAPTURING INFORMATION FOR THE SPREADSHEET WITH WEB QUERIES IMPORTING XML DATA INTO A WORKSHEET TECHNIQUE 52: USING HYPERLINKS TO MAKE JUMPS IN WORKBOOKS ADDING HYPERLINKS TO A WORKSHEET FOLLOWING LINKS IN A WORKSHEET EDITING LINKS IN A WORKSHEET CREATING HYPERLINKS FOR CUSTOM MENUS AND TOOLBARS PART IX: THE SCARY (OR FUN) STUFF TECHNIQUE 53: INSTANT LISTS IN EXCEL CREATING AN EXCEL 2003 LIST CONVERTING AN EXISTING LIST INTO AN EXCEL LIST CREATING AN EXCEL LIST FROM SCRATCH SORTING AND FILTERING THE LIST TOGGLING THE LIST’S TOTAL ROW ON AND OFF EASY LIST EDITING INSERTING OR DELETING ROWS AND COLUMNS CONVERTING A LIST BACK INTO A REGULAR CELL RANGE DELETING A LIST TECHNIQUE 54: SHARING EXCEL WORKBOOKS AND LISTS WITH A SHAREPOINT WEB SITE ADDING EXCEL SPREADSHEETS TO THE SHAREPOINT SITE OPENING THE SPREADSHEETS ON THE SHAREPOINT SITE ADDING A LIST OF YOUR SPREADSHEETS TO SHAREPOINT SITE HOME PAGE PUBLISHING LISTS TO THE SHAREPOINT SITE PUBLISHING AN EXCEL LIST ON A SHAREPOINT SITE SYNCHRONIZING LIST DATA TECHNIQUE 55: ENTERING DATA AND ISSUING COMMANDS BY VOICE HANDS-FREE DATA ENTRY JUST TELL ME WHAT TO DO CHOOSING MENU ITEMS, DIALOG BOX OPTIONS, AND TOOLBAR BUTTONS TELLING THE CELL POINTER “WHERE TO GO” TECHNIQUE 56: SPRUCING UP YOUR SPREADSHEETS WITH GRAPHICS JAZZ IT UP WITH CLIP ART ADDING IMAGES FROM GRAPHICS FILES DRAWING OBJECTS FOR THE SPREADSHEET DRAWING VARIOUS SHAPES USING TEXT BOXES AS CALLOUTS MAKING A STATEMENT WITH WORDART DRAWING DIAGRAMS AND ORGANIZATION CHARTS TECHNIQUE 57: DOING AUTOMATED TABLE LOOKUPS LOOKING UP A SINGLE TABLE VALUE PERFORMING A VERTICAL TABLE LOOKUP PERFORMING A HORIZONTAL LOOKUP DOING A TWO-WAY LOOKUP IN A DATA TABLE TECHNIQUE 58: USING TEXT FORMULAS FOR FUN AND PROFIT GETTING RIGHT ON THE CASE JOINING SEPARATE TEXT ENTRIES TOGETHER REPLACING TEXT FORMULAS WITH THEIR RESULTS TECHNIQUE 59: CREATING QUERIES TO IMPORT DATA FROM AN EXTERNAL DATABASE SETTING UP THE DATA SOURCE DEFINITION CREATING THE DATABASE QUERY TECHNIQUE 60: AUTOMATING REPETITIVE TASKS WITH MACROS RECORDING AND PLAYING BACK MACROS RECORDING THE MACRO PLAYING BACK THE MACRO EDITING MACROS IN THE VISUAL BASIC EDITOR MODIFYING THE SETTINGS FOR VBA PROPERTIES GETTING USER INPUT BY ADDING A CUSTOM DIALOG BOX TECHNIQUE 61: CREATING CUSTOM FUNCTIONS TO USE IN YOUR WORKSHEETS CREATING CUSTOM FUNCTIONS SAVING CUSTOM FUNCTIONS IN AN EXCEL ADD-IN