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 ------------ Excel Courses Excel Index ------------ Code Vault Testimonials Tip Jar Advice for Consultants

Microsoft Excel

You can use this index to see what topics are covered in each class. If you are looking for a particular course topic, you can use your browser's search function to find that topic. Internet Explorer and FireFox users, pressss CTRL-F.

Excel 2010
Excel 2010 Beginner 2
 Running Time: 1 hour, 28 minutes 00. Intro (7:37) 01. Cut Copy Paste (9:30) The Clipboard Copy, Paste Cut, Paste Formula References Updated Paste Multiple Times Clipboard Heyboard Shortcuts 02. Autofill (7:46) Autofill a Formula Autofill Handle Mistakes Moving a cell Move a Cell Value 03. Cell Formats 1 (10:39) Text, Number Missing Leading Zeros Currency, Accounting Short Date, Long Date 04. Cell Formats 2 (7:32) Fraction Mixed Fraction Percentage Increase Decimal Decrease Decimal Comma Style Scientific Style 05. Rows & Columns (9:42) Clear v. Delete Selecting Multiple Columns Delete Columns Delete Rows Right-Click Delete Insert Column Insert Row Insert Multiple Row Clear Contents 06. Sheets (9:34) Insert New Worksheet Delete Worksheet Rename Sheet Moving Sheets Copying a Sheet Sheet Tab Color 07. Sorting Data (6:25) Sort A to Z Dealing with Total Rows Select Everything First Sort on a Different Column Use TAB to Change Columns 08. Conditional Formatting (8:03) Highlight Cells Rules Greater Than, Less Than Above Average Multiple Conditions Clear Formatting from Sheet Data Bars Color Scales Icon Sets 09. Find & Replace (7:35) Find Data Find Next Find All Replace Replace All 10. Review (4:40)
Excel 2010 Beginner 3
 Running Time: 1 Hour, 40 Minutes 00. Intro (8:00) 01. Functions 1 (8:14) What is a Function The SUM Function Benefits of Functions 02. Functions 2 (9:42) AVERAGE Function COUNT MAX, MIN Move a Block of Cells 03. Functions 3 (11:17) Practice with Functions The Key Cell Format Trick Insert New Month of Sales AutoFill Multiple Cells 04. Styles & Themes (9:03) Selecting Cell Styles Using Styles to Format Changing Themes Theme Colors vs. Standard Colors 05. Page Layout (11:55) Margins View, Page Layout Print Preview Orientation Page Size Set Print Area Clear Print Area Page Breaks Background Gridlines: View, Print Headings: View, Print 06. Illustrations 1 (13:11) Insert a Picture Move and Resize a Picture Rotate Picture Picture Tools, Format Tab Brightness, Contrast Reset Picture Compress Pictures Delete a Picture Insert Clipart 07. Illustrations 2 (11:38) Insert Shapes Rectangle Drawing Tools, Format Tab Shape Styles How Themes Effect Shape Styles Shape Outline Shape Effects Add Text Format Text Quick Mini Toolbar Popup WordArt Styles Inserting an Arrow 08. Illustrations 3 (12:39) Insert SmartArt Basic Process Format SmartArt Options Screenshots Screen Clippings Insert WordArt WordArt Options 09. Review (5:03)
Excel 2010 Beginner 4
 Running Time: 1 Hour 38 Minutes 00. Introduction (8:46) 01. Charts 1 (10:51) What is a Chart Parts of a Chart Insert a Column Chart 02. Charts 2 (11:24) Add Data to an Existing Chart Line Charts Multiple Series in a Chart Chart Styles & Themes 03. Charts 3 (16:14) Selecting Non-Contiguous Data 3D Pie Charts Move a Chart to a Different Sheet Format Objects Inside Charts Chart Layouts Leader Lines Change Chart Type 3D Column Charts Stacked 3D Column Chart 04. Charts 4 (13:52) Switch Row/Column Current Selection Box Format Selection Reset to Match Style Insert Shapes Insert Text Box Labels Chart Title Axis Titles Legend Data Labels Data Table 05. Charts 5 (13:15) Copy Excel Charts into Word Creating a Summary Sheet / Dashboard Separate a Slice of Pie Create a Leader Line 06. Tables (17:05) Creating a Table Why Use a Table Table Styles Table Formulas are Uniform New Rows Automatically Copy Formulas Sort in a Table Filter in Tables Turn off Filtering Total Row Insert New Row 07. Review (6:46)
Excel 2010 Beginner 5
Excel 2010 Expert 1
 Running Time: 68 Minutes 00. Intro (7:20) 01. Working With Functions (10:06) Typing in a function Function library Grouped categories of functions AutoSum button Changing the AutoSum function Insert Function dialog CONCATENATE function Argument Select Buttons Concatenate with the & sign 02. Relative v Absolute References (8:53) What is an Absolute Reference? What is a Relative Reference? Reference behavior with AutoFill Sales Tax Rate example F4 keyboard trick 03. Named Cells and Ranges (7:03) What is a Named Cell Create a Name with the Name Box Jump to a Named Cell Define Name button Name Manager Edit, Delete Names Create a Named Range Named Range example with SUM 04. Values on Other Sheets (10:05) Refer to a Cell on Another Sheet =SheetName!CellName Moving a Named Cell Copy, Paste Link Creating a Summary Sheet 05. Text Functions 1 (14:10) Checking if Two Strings are Equal =A1=B1 EXACT CONCATENATE =A1&B1 LEN LEFT RIGHT MID FIND Separate First and Last Names SEARCH 06. Text Functions 2 (6:23) SUBSTITUTE REPLACE TRIM 07. Review (4:27)
Excel 2010 Expert 2
 Running Time: 1 Hour 33 Minutes 00. Intro (7:49) 01. Excel Dates & Times (9:12) Valid Date Formats Valid Time Formats Valid Date/Time Formats Understanding Internal Dates Date Arithmetic Adding Days Adding Hours 02. Custom Date/Time Codes (6:33) Customizing Date Formats Custom Date & Time Codes 03. Date/Time Functions 1 (11:11) Current System Date/Time Current Time NOW() F9 to Recalculate Current Date TODAY() Calculating the Time Only Components of a Date YEAR, MONTH, DAY HOUR, MINUTE, SECOND WEEKDAY Changing WEEKDAY Start Day Building a Date from Components DATE Buildnig a Time from Components TIME Adding Dates with DATE Function Adding Times with TIME Function 04. Date/Time Functions 2 (14:13) Calculate Difference in Dates Calculate Age in Years Using Math INT function Difference in Whole Years YEARFRAC DATEDIF Difference in Months Between Dates 05. Date/Time Functions 3 (13:49) Converting Bad Imported Dates Convert 20110801 to 8/1/2011 Review of LEFT, RIGHT, MID Convert Text Date to Actual Date DATEVALUE, TIMEVALUE EDATE Determine Last Day of the Month EOMONTH Work Days Between Two Dates NETWORKDAYS Custom List of Excluded Holidays Count Number of Workdays Forward WORKDAY NETWORKDAYS.INTL WORKDAY.INTL Custom Work Days String "0000011" Week Number of the Year WEEKNUM 06. Date/Time Functions 4 (12:04) AutoFill with Days of the Week AutoFill Date Options Fill Days, Months, Years, Weekdays Subtract Two Times Format as a Number Convert Days to Hours Display Hours and Fractions Display Hours and Minutes Adding up hours with [h]:mm 07. Date/Time Tips & Tricks (13:57) Creating a series of dates with AutoFill Calculating specific dates First day of a month Last day of a month Number of days in a month What quarter is a date in First day of the year Last day of the year Day number (of the year) Days remaining in the year Inserting the current date and time Adding up hours, minutes, seconds [h]:mm [mm]:ss Displaying fractions of a second h:mm:ss.00 08. Review (4:28)
Excel 2010 Expert 3
 Running Time: 1 Hour 11 minutes 00. Intro (6:29) 01. Logic Functions (19:05) TRUE, FALSE Values 0 = FALSE AND, OR, NOT Is an invoice late? Is an invoice paid? Collection Letter Example Shipping an Order Example Credit Card Batching Example TRUE, FALSE Functions 02. IF Function 1 (14:18) IF THEN Statement IF THEN ELSE Statement Charge Sales Tax Example Set Tax Rate if in NY Calculate Sales Tax Calculate Credit Card Batch Total Give Students PASS or FAIL Grade 03. IF Function 2 (14:05) Nested IF Functions Assign a Letter Grade with IF Fixing Our Time Sheet Example Shifts That Cross Over Midnight Calculating Overtime Pay 04. IS Functions (14:05) ISBLANK, ISERROR ISEVEN, ISODD ISLOGICAL, ISNUMBER ISTEXT, ISNONTEXT Extra Space Between Names FN, MI, LN "Mr.", "Mrs.", or "Mr. & Mrs." Divide by Zero errors ISERROR to fix #DIV/0! IFERROR to fix #DIV/0! 05. Review (3:20)
Excel 2010 Expert 4
 Running Time: 1 Hour 10 minutes 00. Intro (5:58) 01. VLOOKUP 1 (8:10) Look up week day name VLOOKUP simple function Lookup Value Table Array Column Index Number Problem With AutoFill Use Absolute Reference Use Named Range 02. VLOOKUP 2 (7:01) Student Letter Grades Range Lookup Assign A to F Convert to Table Hide empty values IF and ISBLANK 03. VLOOKUP 3 (9:22) Employee Time Sheet Employee List Table Rename a Table Timecard Worksheet Exact Match Lookup Lookup Employee Name Lookup Pay Rate Calculate Hours Worked Calculate Total Pay 04. Other Lookups (13:50) HLOOKUP LOOKUP MATCH, INDEX Less Than Exact Match Greater Than Wildcard Match Backward Lookup Searching an Array 05. Cell References (10:26) Construct a cell reference Deconstruct a cell reference ADDRESS INDIRECT Year to date sales figure Sales between two months 06. Compare Two Lists (4:18) Use MATCH to compare Is item in list missing ISNA 07. Closest to Value (7:06) Which value is closest to target Calculate Difference Autofill Double-Click Trick ABS Absolute Value MIN to find closest value MATCH to locate the value INDEX to determine winner Closest to average value
Excel 2010 Expert 5
 Running Time 1 Hour, 5 Minutes 00. Intro (5:49) 01. More With SUM (5:01) Non-contiguous ranges with SUM Insert function with multiple ranges Running balance Running total 02. SUMIF (11:05) Add up values with specific criteria ">=100" Variable criteria ">="&A1 SUMIF, COUNTIF, AVERAGEIF Sum Range vs. Criteria Range Make sure to use absolute references 03. SUMIFS (10:03) Sum of commissions by sales rep Commissions by rep over \$100 Orders past due Unpaid orders past due SUMIFS, COUNTIFS, AVERAGEIFS 04. Counting & Rounding (11:29) COUNT COUNTA COUNTBLANK What you see isn't necessarily valid Decrease decimal hides true value ROUND ROUNDUP ROUNDDOWN MROUND CEILING FLOOR INT TRUNC 05. Misc Math (13:03) MEDIAN MODE PRODUCT Scientific Notation 4.19E+23 POWER SQRT 10^2 Integer Division QUOTIENT MOD Modulus ABS SIGN Random Numbers RAND RANDBETWEEN Turn off Automatic Recalculation F9 to Recalculate 06. Trigonometry (5:10) SIN, COS, TAN PI RADIANS DEGREES Calculate Height of a Building 07. Review (3:08)
Excel 2010 Expert 6
 RunningTime: 1 Hour, 8 Minutes 00. Intro (6:14) 01. Financial Terms (4:49) PV, FV, PMT, RATE, NPER APR (Annual Percentage Rate) Simple Interest Compound Interest 02. Loan Calculator (9:39) Calculate Mortgage Payment PMT function Compare Multiple Scenarios 03. Investment Calculator (5:03) FV Future Value Function Calculate Investment in 5 Years 04. Interest Rate Calculator (6:09) What is your Return on Investment RATE function Calculate interest rate 05. Millionaire Calculator (6:01) NPER Function How many years until goal reached 06. Initial Deposit Calculator (2:29) PV Function What was the initial deposit? 07. Home Value Calculator (4:04) How much house can I afford PV function Optional FV and TYPE parameters 08. Loan Amortization (12:10) Microsoft's Amortization Template Create your own amortization table IPMT, PPMT functions Cumulative interest and principal paid CUMPRINC, CUMIPMT 09. Credit Card Payments (6:08) How many payments at minimum How long to pay off card debt What payment to pay off in 6 months? NPER and PMT 10. Review (4:44)
Excel 2010 Expert 7
 Running Time: 1 Hour, 5 Minutes00. Intro (5:55) 01. What is a PivotTable (4:58) Why are they useful? What you can do with them. 02. Our First PivotTable (6:07) Create Data Table Optional download from web site Create PivotTable Set Row Labels Set Column Labels Set Values Change from Count to Sum 03. Editing Your PivotTable (8:28) Changing Fields Multiple Fields per Axis Expanding / Collapsing Levels Filter & Sort Data Label Filter Value Filter Report Filter "Pivoting" Your Table 04. PivotTable Options (12:42) Multiple Sets of Values PivotTable Pane Layout Mode Defer Layout Update PivotTable Options Menu For Empty Cells Show Group / Ungroup Slicers Select Multiple Items Slicer Settings Multiple Slicers 05. PivotTable Design (5:14) Layout Show Subtotals Grand Totals Report Layout Blank Rows Row & Column Headers Banded Rows / Columns PivotTable Styles Double-Click to Create Data Subset 06. PivotCharts (4:51) Creating a PivotChart PivotChart Options 07. Grouping Data (11:47) Group by Month Table Better Than Range Refresh Change Data Source Group by Year/Month Subtotal Row Custom Weekly Grouping Group Ranges of Numbers Group on Age Distribution 08. Review (4:37)
Excel 2010 Expert 8
Excel 2010 Expert 9
Excel 2010 Expert 10
 00. Intro (5:33) 01. Custom Views (6:06) Turn on filtering Create a Custom View Add Dropdown to Toolbar Add View with Dropdown 02. SUBTOTAL Function (7:12) SUM calculates ALL cells SUBTOTAL function know filters Select a subtotal type SUM, AVERAGE, etc. Just HIDING a row Only works in columns, not rows AutoSum uses SUBTOTAL for SUM only 03. Outline Subtotals (7:33) Create a Subtotal Expand Collapse Buttons Multiple Subtotal Groups 04. Outlines (7:45) Creating a Manual Outline Group, Ungroup Buttons Clear Outline Auto Outline 05. Data Validation 1 (10:05) Validation Rules Text Length Circle Invalid Data Input Message Error Alert Stop, Warning, Information 06. Data Validation 2 (9:30) Date Values TODAY() function Appts in 30 days Range Formulas for Validation Ignore First Row Error Times Whole Numbers List Dropdown List Values Conditional Formatting 07. Data Validation 3 (8:43) Custom Rules ISTEXT() AND() MID() LEN() Exceeding a Budget 08. Data Validation 4 (8:46) Cascading Lists Pick a Car Make Show Models for that Company DGET Function Lookup with Multiple Criteria 09. Review (4:36)
Excel Expert 11
 00. Intro (4:37)01. Video Time Conversion (27:39) Separating Text StringsFIND, LEFT, LEN, MID, RIGHT Converting to SecondsAdding TimesQUOTIENT, MOD, TEXT FunctionsHow Many Hours in X SecondsForce 2 Digit Minute, Sec 00:0002. XLOOKUP Part 1 (13:28)Benefits of XLOOKUPLook up to the left or rightReturn multiple resultsVertical or horizontal lookupsReverse search (last to first)Defaults to an exact matchSupports wildcards (*, ?, ~)Sspecify a value if not foundReturn entire columns or rowsCan work with arraysReview of VLOOKUPWeekday LookupAbsolute References F4Still works if you move rangesStudent GradesGrade Lookup Table List doesn't have to be in asc orderExact Match Lookup Approximate Lookup (Range)Match Mode: Exact Match or Next Smaller Item03. XLOOKUP Part 2 (11:13)Returning multiple valuesmatch_modeMatch Not Found: if_not_found argumentWilcard Searches * ? ~search_modeBinary Searches - discussionXMATCH04. XLOOKUP Part 3 (16:48)Two-way lookups (double lookup)Clothes, Sizes, Look up PriceCourse, Grade, Look up LetterBONUS: Developer TabCombo BoxInput RangeCell Link 05. Review (2:49)
Excel Developer 1
 Scheduled for production soon. Be sure to indicate your interest for more Excel training on our Waiting List.
Excel 2007
Excel 2007 Basic 1
Excel 2007 Basic 2
 Running Time: 66 minutes00. Intro (6:57)01. Cut Copy Paste (6:53) The ClipboardCopy, PasteCut, PasteFormula References UpdatedPaste Multiple Times02. Autofill (5:36)Autofill a FormulaMistakesAutofill HandleMove a Cell Value03. Cell Formats 1 (6:48)Text, NumberMissing Leading Zeros Currency, AccountingShort Date, Long Date04. Cell Formats 2 (4:16)FractionPercentage Increase DecimalDecrease DecimalComma Style Scientific Style05. Rows & Columns (6:56) Clear v. DeleteSelecting Multiple ColumnsDelete Columns Delete RowsRight-Click DeleteInsert ColumnInsert Row Clear Contents06. Sheets (5:17) Insert New WorksheetDelete WorksheetRename Sheet Moving SheetsCopying a SheetSheet Tab Color 07. Sorting Data (5:44)Sort A to Z Dealing with Total RowsSelect Everything FirstSort on a Different ColumnUse TAB to Change Columns08. Conditional Formatting (5:52)Highlight Cells Rules Greater Than, Less ThanAbove AverageData BarsColor ScalesIcon Sets09. Find & Replace (7:53) Find DataFind NextFind AllReplaceReplace All 10. Review (3:56)
Excel 2007 Basic 3
 Running Time: 67 minutes00. Intro (7:14)01. Functions 1 (6:39) What is a FunctionThe SUM FunctionBenefits of Functions 02. Functions 2 (4:36)AVERAGE Function COUNTMAX, MINMove a Block of Cells03. Functions 3 (6:56)Practice with FunctionsThe Key Cell Format TrickInsert New Month of SalesAutoFill Multiple Cells04. Styles & Themes (7:53) Selecting Cell StylesWhat's a #DIV/0! ErrorUsing Styles to FormatChanging Themes05. Page Layout (7:37)MarginsView, Page LayoutPrint Preview OrientationPage SizeSet Print AreaClear Print Area Page BreaksBackgroundGridlines: View, PrintHeadings: View, Print06. Illustrations 1 (9:07) Insert a PictureMove and Resize a PictureRotate PicturePicture Tools, Format TabBrightness, Contrast Reset PictureCompress PicturesDelete a Picture 07. Illustrations 2 (7:37)Insert Shapes RectangleDrawing Tools, Format TabShape StylesHow Themes Effect Shape StylesShape OutlineShape Effects Add TextFormat TextQuick Mini Toolbar PopupWordArt StylesInserting an Arrow08. Illustrations 3 (5:50)Insert SmartArtBasic ProcessFormat SmartArt OptionsInsert WordArtWordArt Options 09. Review (3:47)
Excel 2007 Basic 4
 Running Time: 64 Minutes00. Introduction (7:48) 01. Charts 1 (7:53)What is a ChartParts of a ChartInsert a Column Chart 02. Charts 2 (8:08)Add Data to an Existing ChartLine ChartsMultiple Series in a ChartChart Styles & Themes03. Charts 3 (11:00) Selecting Non-Contiguous Data3D Pie ChartsMove a Chart to a Different SheetFormat Objects Inside ChartsChart LayoutsChange Chart Type3D Column Charts 04. Charts 4 (7:03)Switch Row/ColumnCurrent Selection BoxFormat Selection Reset to Match Style Insert ShapesInsert Text BoxLabelsChart Title Axis TitlesLegendData LabelsData Table 05. Charts 5 (8:05)Copy Excel Charts into WordCreating a Summary Sheet / DashboardSeparate a Slice of PieCreate a Leader Line06. Tables (9:45) Creating a TableWhy Use a TableTable Styles Table Formulas are UniformNew Rows Automatically Copy FormulasAutoFilter in TablesTotal RowInsert New Row 07. Review (5:02)
Excel 2007 Basic 5
Excel 2007 Expert 1
 The differences between Excel 2007 and 2010 are minor. To continue learning Excel as an Expert user, please see our Excel 2010 lessons (above). You should have no problems following along with 2007.
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