Excel 2010-2019 Excel 2007 Excel 2003 Tips & Tricks Excel Forum Course Index CIG Excel Book

 Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon More... What's New? Popular Links ------------ Access Courses Access Index Access TechHelp Access Forum Access Troubleshooter Access Developer Network Access AI Bot ------------ Excel Courses Excel Index ------------ Code Vault Testimonials Tip Jar Advice for Consultants

Old Excel 2003 Outlines

Excel 2000-2003
Excel 101
Excel 102
Excel 103
Excel 104
 0. INTRODUCTION Objectives Pre-Requisites 1. FORMAT CELLS: NUMBER FORMATS Setting up a sales log sheet Right Click, Format Cells The Number Tab General Number Format Number of decimal places Use 1000 Separator Formats for negative numbers Currency Style Accounting Style The Date Formats The Time Formats Percentage Formats Fractions Format Precisions with fractions Scientific notation format Plain Text Formats Special Formats Custom Formats, a brief discussion on Using Format Painter on a whole column Percent Style Button Calculating sales tax by a tax rate Math for calculating totals, subtotals 2. FORMAT CELLS: ALIGNMENTS Format Cells, Alignment Tab Setting vertical alignment Top Vertical Alignment Wrap Text to keep text in a cell Text Orientation Displaying text at an angle Insert Cut Cells to move a whole row Shrink To Fit text Merge and Center Center Across Columns Merge Cells 3. FORMAT CELLS: FONTS Format Cells, Font Tab Strikethrough, Superscript, Subscript Borders Tab None, outline, inside borders Manually turning borders on/off Diagonal (strike) borders Border styles, colors, thickness Patterns tab Setting a pattern and color Cell protection, brief discussion 4. CONDITIONAL FORMATTING Format, Conditional Formatting Formatting a cell based on its value Example: Highlighting sales over \$300 Adding more conditions Example: Highlighting sales under \$100 Formatting based on a cell value 5. SPELLING, FIND, REPLACE, AUTOFORMAT Tools, Spelling to spell check your sheet Ignore once Ignore all Add to dictionary Change Change All Edit, Replace Find and Replace Dialog Finding based on format Match case Match entire cell contents Find Within (workbook, sheet, range) Search By (rows, columns) Look in (formulas) Format, AutoFormat Pre-designed AutoFormats 6. FINANCIAL CALCULATOR Creating a loan calculator Entering the value of the house, down payment Calculating the financed amount Entering the interest rate, period Learning the PMT function Using PMT to calculate the monthly payment Calculating number of payments Figuring out total interest paid Currency Style button Comparison shopping: compare two loans Changing interest rates, periods, down payments Calculating your savings Locking specific cells to prevent tampering Turning on sheet protection Format Cells, Protection tab Locked cells Tools, Protection, Protect Sheet Unprotect Sheet 7. REVIEW Review Topics
Excel 201
Excel 202
 0. INTRODUCTION Objectives Pre-Requisites 1. REVIEW AND SHEET SETUP Reconstruct Student Gradebook Short review of important concepts 2. NAMED CELLS Defining a named range Insert, Name, Define Refers To Box Using the Name Box Naming the PassingGrade cell Jumping to a named cell Using the named cell in functions Collapse Dialog Expand Dialog Valid names for cells 3. NAMED RANGES, PART ONE Defining a named range Pasting a named cell or range Insert, Name, Paste Using named ranges in formulas Jumping to named ranges Range Intersections 4. NAMED RANGES, PART TWO Deleting a named range or cell Editing a named range or cell Pasting a list of your named ranges Paste List Creating names based on your headers Insert, Name, Create Applying names to existing ranges Insert, Name, Apply 5. NESTED IF FUNCTIONS What is a Nested IF function Using the COUNT function Checking multiple conditions Giving our students an Incomplete Giving a letter grade using nested IFs 6. VLOOKUP, PART ONE Creating a subtable (lookup table) Grade values lookup table Using VLOOKUP to return the letter grade Using VLOOKUP to return a commission rate 7. VLOOKUP, PART TWO Expanding our lookup table to three columns Using VLOOKUP to return multiple columns Creating a basic invoice using Excel Using HLOOKUP to calculate a discount Using the Function Wizard for HLOOKUP 8. REVIEW Review Topics
Excel 220
 0. INTRODUCTION Objectives Pre-Requisites 1. DATA LISTS Data Lists Defined Header Row Fields Records Database Terminology Pick From List 2. FORMATTING DATA LISTS Formatting the Header Row Freezing Panes (review) Calculate Time Difference (in hours) Absolute References (review) Conditional Formatting (review) Spreadsheet v. Database (when to switch) 3. DATA ENTRY FORMS Data entry forms defined Opening the data form Moving between fields Entering data New records Find Previous Find Next Deleting records You can't undo deletes Calculated fields in data forms Searching for records Search Criteria Using wildcard characters * and ? Wildcard rules Restore button 4. ADVANCED SORTING The sum preview on the status bar Changing from sum to a different function Count v. Count Nums Sorting month names in month order (not alpha) First key sort order Sort options Custom Lists (using in sorts) Sorting on more than 3 fields Restoring a list's original sort order Alphanumeric sorting rules 5. CUSTOMIZING TOOLBARS Toolbars and commands defined Customizing built-in toolbars Reviewing the command button options Moving buttons around Removing buttons from toolbars Copying a button from one toolbar to another Resetting a toolbar to its default settings Changing a button from icon to text Creating a New Toolbar Attaching a toolbar to a workbook (to share it) Change Button Image Creating a custom button image Button Editor Edit Button Image Copying ANY image onto a button Changing the tooltip text Toolbars Options tab Toolbar Quick-Add Buttons Toolbar buttons with dual functions How to backup your custom toolbars 8. REVIEW Review Topics
Excel 221
Excel 222
Excel 223
 What is a PivotTable Building a PivotTable Manipulating PivotTable Fields PivotTable rules for data Field Settings Pivoting Your Data Inner & Outer Fields Example: Sales Log Example: Employee List Refreshing PivotTable Data Filtering Data Using Page Fields to Filter Data Sorting PivotTable Data
Excel 224
 Grouping Data Drill to Detail Grouping Dates Together (months, quarters, etc.) Grouping Numeric Values Subtotals Subdetails Advanced Field Settings Formatting PivotTables Copying PivotTable Data PivotTable Options Calculated Fields Calculated Items Base Fields & Items Running Totals Showing Data as a Percentage of Previous Data Example: Employee Costs using IF, VLOOKUP, and a PivotTable
Excel 230
 Lesson 1. Multisheet Ranges Selecting Multisheet (3D) Ranges Tracking sales for multiple years Formatting multiple sheets with one selection Creating a summary sheet Lesson 2. Go To > Special Comments, Formulas, Blanks, Current Region, Objects, Row & Column Differences Precedents & Dependents, Last Cell Visible Cells Only, Conditional Formats, Data Validation Lesson 3. Advanced Find & Replace Find, Replace Options Wildcards: *, ? using tilde ~ to find a wildcard character Find Format > Choose Format From Cell Find in Sheet, Workbook Find All Selecting all found cells Find in Formulas, Values, Comments Match Case Match Entire cell Careful when doing Replace All, esp w numbers! Lesson 4. File Options AutoRecover AutoRecover Options Saving Your AutoRecover file File Save Options Creating Automatic Backups Password to Open, Modify Encryption Workbook Summary Summary Fields (Subject, Title, etc.) Custom Fields - creating a workbook database Item, Type, Data Lesson 5. Templates Understanding templates Modifying the default Excel template Setting up custom workbook templates Book.xlt, Sheet.xlt XLStart Folder
Excel 231
 LESSON 1. ERROR TYPES Types of Errors Syntax Errors Logical Errors Reference Errors Circular References Mismatched Parentheses LESSON 2. SPECIFIC ERRORS #DIV/0! Handle DIV/0 error with IF function ISERROR() function #NAME? #REF! #VALUE! #NUM! SQRT() Function for square root Exponents too large 10^308 #NULL! #N/A =NA() Function LESSON 3. ERRORS WITHOUT MESSAGES Logical Errors Relative v. Absolute References Rounding errors Actual v. displayed values ROUND() function Force "Precision as displayed" Floating Point Errors Numbers close to zero not rounding right LESSON 4. LOGICAL FUNCTIONS TRUE FALSE NOT AND OR =IF(AND(X,Y),TRUE,FALSE) LESSON 5. AUDITING Tracking Errors Auditing Toolbar Trace Dependents Trace Precedents Dependents on another sheet Goto jumping to those dependents Trace Errors Blue and Red Arrows Evaluate Formula Evaluate, Step In, Step Out Circle Invalid Data Watch Window LESSON 6. HIDING ERRORS Automatic Error Checking Hiding Cell Errors from Printouts LESSON 7. CIRCULAR REFERENCES Circular Iterations When you WANT a Circular Reference Simulating a DO LOOP LESSON 8. TIPS & TRICKS Transpose Data Edit > Fill Series Trend Increasing X Weekdays AutoFill with RIGHT Mouse Button

Start a NEW Conversation

Subscribe to Old Excel 2003 Outlines

Computer Learning Zone is not responsible for any content shown or offers made by these ads.

 Learn  Access - index Excel - index Word - index Windows - index PowerPoint - index Photoshop - index Visual Basic - index ASP - index Seminars More... Customers  Login My Account My Courses Lost Password Memberships Student Databases Change Email Info  Latest News New Releases User Forums Topic Glossary Tips & Tricks Search The Site Code Vault Collapse Menus Help  Customer Support Web Site Tour FAQs TechHelp Consulting Services About  Background Testimonials Jobs Affiliate Program Richard Rost Free Lessons Mailing List PCResale.NET Order  Video Tutorials Handbooks Memberships Learning Connection Idiot's Guide to Excel Volume Discounts Payment Info Shipping Terms of Sale Contact  Contact Info Support Policy Mailing Address Phone Number Fax Number Course Survey Email Richard [email protected]