Learn Excel 97 through Excel 2007 from MrExcel

THE EXCEL ENVIRONMENT

• Find Icons on the Ribbon
• Go Wide
• Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003
• The Office Development Team Likes the Artist Formerly Known as Prince
• The Paste Icon Is Really Two Icons
• Use Dialog Launchers to Access the Excel 2003 Dialog
• Make Your Most-Used Icons Always Visible
• The Alt Keystrokes Still Work in 2007 (If You Type Them Slowly Enough)
• Use New Keyboard Shortcuts to Access the Ribbon
• The Blue Question Mark Is Help
• All Commands Start at the Top (Except for 2 Controls at the Bottom)
• What Happened to Tools – Customize?
• What Happened to Tools – Options?
• Where Are My Macros?
• Why Do I Have Only 65,536 Rows?
• Which File Format Should I Use?
• Share Files with People Who Are Still Using Excel 97 Through Excel 2003
• Use Live Preview
• Get Quick Access to Formatting Options Using the Mini Toolbar
• MIX FORMATTING IN a Single CELL
• Copy the Characters from a Cell Instead of Copying an Entire Cell
• I am a Lobbyist Writing Policy Papers for the White House
• My Manager Wants Me to Create a New Expense Report from Scratch
• Increase the Number of Documents in the Recent Documents List
• Keep Favorites in the Recent Documents List
• I’ve Searched Everywhere. Where Is the Save Workspace Command?
• Use a Workspace to Remember What Workbooks to Open
• Close All Open Workbooks
• Automatically Move the Cell Pointer in a Direction After Entering a Number
• Return to the First Column After Typing the Last Column
• Enter Data in a Circle (Or Any Other Pattern)
• 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
• Excel 2007’S Obsession with Security Has Destroyed Linked Workbooks
• I Navigate by Sliding the Scrollbar and Now the Slider Has Become Tiny
• Send an 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
• Add Word to Excel
• Spell check a Region
• Translate with Excel
• Use Hyperlinks to Create an Opening Menu for a Workbook
• Get Quick Access to Paste Values
• Quickly Copy a Formula to All Rows of Data
• Enter a Series of Months, Days, or More by Using the Fill Handle
• Have the Fill Handle Fill Your List of Part Numbers
• 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 Other Worksheets
• Have Excel Talk to You
• Enter Special Symbols
• Find Text Entries
• What Do All the Triangles Mean?
• Why Can’t Excel Find a Number?
• Get Free Excel Help

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 the 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
• Assign a Name to a Formula
• Total Without Using a Formula
• Add Two Columns Without Using Formulas
• How to Calculate Sales in Excess of a Quota
• How to Join Two Text Columns
• Join Text with a Date or Currency
• How to Sort on One Portion 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
• Combine Intermediate Formulas into a Mega-Formula
• Change Smith, Jane to Jane Smith
• Add the Worksheet Name as a Title
• Avoid #REF! Errors When Deleting Columns
• Create Random Numbers
• Create Random Numbers to Sequence a Class of Students
• Play Dice Games with Excel
• Create Random Letters
• Convert Numbers to Text
• Calculate a Loan Payment
• Calculate Many Scenarios for Loan Payments
• Back into an Answer Using Goal Seek
• Create an Amortization Table
• Get Help on Any Function While Entering a Formula
• Discover New Functions Using the fx Button
• Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses
• Three Methods of Entering Formulas
• Start a Formula with = or +
• Use AutoSum to Quickly Enter a Total Formula
• AutoSum Doesn’t Always Predict My Data Correctly
• Use the AutoSum Button to Enter Averages, Min, Max, and Count
• The Count Option of the AutoSum Dropdown Doesn’t Appear to Work
• Use AutoSum After Filtering
• Use Table Functionality to Simplify Copying of Formulas
• Rename Your Tables
• Use Simple References in a Table
• 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 the Formula Bar to Test a Formula
• Quick Calculator
• When Entering a Formula, You Get the Formula Instead of the Result
• Why Don’t Dates Show as Dates?
• Handle Long Formulas in the New Excel 2007 Formula Bar
• Calculate a Percentage of Total
• Calculate a Running Percentage of Total
• Use the ^ Sign for Exponents
• 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 the Next Highest $5
• Round to the Nearest Nickel with MROUND
• Why Is This Price Showing $27.85000001 Cents?
• You Change a Cell in Excel but the Formulas Do Not Calculate
• Use Parentheses to Control the Order of Calculations
• Before Deleting a Cell, Find out if Other Cells Rely on It
• Navigate to Each Precedent
• Calculate a Formula in Slow Motion
• Which Cells Flow into This Cell?
• 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 Monthly Dates
• Group Dates by Month
• Calculate the Last Day of the Month
• Create a Timesheet That Can Total over 24 Hours
• Can Excel Track Negative Time?
• What Is the Difference Between Now and Today?
• Calculate Work Days
• Convert Units
• Use Match to 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
• Remove Leading and Trailing Spaces
• I Don’t Want to Use a Lookup Table to Choose One of Five Choices
• Fill a Cell with Repeating Characters
• Match Web Colors with HEX2DEC
• Switching Columns into Rows Using a Formula
• Count Records That Match a Criterion
• Build a Table That Will Count by Criteria
• Build a Summary Table to Place Employees in Age Brackets
• Count Records Based on Multiple Conditions
• Total Revenue from Rows that Match a Criterion
• Use the Conditional Sum Wizard to Build Conditional Formulas
• 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?

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
• Sort Days of the Week
• How to Sort a Report into a Custom Sequence
• Sort All Red Cells to the Top of a Report
• Quickly Filter a List to Certain Records
• Find the Unique Values in a Column
• Copy Matching Records to a New Worksheet
• Replace Multiple Filter Criteria with a Single Row of Formulas
• Add Subtotals to a Data set
• 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
• Add Subtotals Above the Data
• Add Other Text to the Subtotal Lines
• Create Subtotals by Product Within Region
• My Manager Wants the Subtotal Lines in Bold Pink Cambria Font
• My Manager Wants a Blank Line After Each Subtotal
• Subtotal One Column and Subaverage Another Column
• Be Wary
• Send Error Reports
• Help Make Excel 2009 Better
• How to Do 40 Different What-if Analyses Quickly
• Remove Blank Rows from a Range
• Remove Blanks from a Range While Keeping the Original Sequence
• Add a Blank Row Between Every Row of Your Data Set
• Excel Is Randomly Parsing Pasted Data
• Increase a Range by Two Percent
• Use Find to Find an Asterisk
• Use an Ampersand in a Header
• Hide Zeros & Other Custom Number Formatting Tricks
• 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 1 for a New Customer
• Add a Group Number to Each Set of Records That Has a Unique Customer Number
• Deal with Data in Which 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
• Why Does This Look Different from Excel 2003?
• 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 Zeros
• Add or Remove Fields from an Existing Pivot Table
• Summarize Pivot Table Data by Three Measures
• Collapse and Expand Pivot Fields
• Manually Re-sequence 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
• Explore the New Filters Available in Excel 2007 Pivot Tables
• Why Aren’t the Cool New Filters Available in My Pivot Table?
• Why Can’t Co-Workers with Excel 2003 Use My Pivot Table?
• Limit a Report to Just One Region
• Create an Ad-Hoc Reporting Tool
• Create a Report for Every Customer
• Create a Unique List of Customers with a Pivot Table
• Create a Report That Shows Count, Min, Max, Average, Etc
• Use Multiple Value Fields as a Column Field
• Compare Four Ways to Show Two Values Fields in a Pivot Table
• Specify a Number Format for a Pivot Table Field
• Group Daily Dates by Month in a Pivot Table
• Group by Week in a Pivot Table
• Produce an Order Lead-Time Report
• Report Revenue Many Ways in a Pivot Table
• Format Pivot Tables with the Gallery
• None of the 23,233 Built-In Styles Do What My Manager Asks For
• Select Parts of a Pivot Table
• Apply Conditional Formatting to a Pivot Table
• 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
• Instead of Using Calculated Items Group Text Fields
• Build a Better Top 10 by Using Group Selection
• Group Ages into Age Ranges
• Use a Pivot Table When There Is No Numeric Data
• Why Does the Pivot Table Field List Dialog Keep Disappearing?
• Control the Shape of Report Filter Fields
• Create a Pivot Table from Access Data
• Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?
• Quickly Create Charts for Any Customer
• Use Microsoft Query to Get a Unique Set of Records
• Use a Trusted Location to Prevent Excel’s Constant Warnings
• 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
• How Do I Clean Up This Data?
• Transform Black-and-White Spreadsheets to Color by Using a Table
• Remove Duplicates
• Protect Cells That Contain Formulas

MAKING THINGS LOOK GOOD

• Change the Look of Your Workbook with Document Themes
• Add Formatting to Pictures in Excel
• Create a Chart with One Click
• Move a Chart from an Embedded Chart to a Chart Sheet
• Excel Creates a Chart at the Bottom Of Data; How Can I Move It to the Top?
• How Can I Nudge a Chart Within the Visible Excel Window?
• Why Does Excel Add a Legend to a One-Series Chart?
• Why Do None of the Built-in Chart Layouts Look Good?
• The Chart Styles Are Cool, But Why So Few Colors?
• Display an Axis in Millions Using the Layout Tab’s Built-in Menus
• Display an Axis in Trillions Using the More Options Choice
• Customize Anything on a Chart by Right-Clicking
• The Format Dialog Box Offers a New Trick
• Charts Acting Flaky? It’s Not Just You
• Minimize Overlap of Pie Labels by Rotating the Chart
• Add New Data to a Chart
• Add a Trendline to a Chart
• Chart Two Series with Differing Orders of Magnitude
• Use Meaningful Chart Titles
• Move the Legend to the Left or Top
• Avoid 3-D Chart Types
• Save Your Chart Settings as a Template
• Other Charting Notes
• For Each Cell in Column A, Have Three Rows in Column B
• Copy Formatting to a New Range
• Copy Without Changing Borders
• Group Columns Instead of Hiding Them
• Move Columns by Sorting Left to Right
• Move Columns Using Insert Cut Cells
• Move Rows or Columns with Shift Drag
• Change All Red Font Cells to Blue Font
• Use Cell Styles to Change Formats
• Leave Helpful Notes with Cell Comments
• Change the Appearance of Cell Comments
• Force Certain Comments to Be Always Visible to Provide a Help System to Users of Your Spreadsheet
• Control How Your Name Appears in Comments
• Change the Comment Shape to a Star
• Add a Pop-up Picture of an Item in a Cell
• Add a Pop-up Picture to Multiple Cells
• Draw an Arrow to Visually Illustrate That Two Cells Are Connected
• Circle a Cell on Your Worksheet
• Draw Perfect Circles
• Create Dozens of Lightning Bolts
• Rotate a Shape
• Alter the Key Inflection Point in a Shape
• Make a Logo into a Shape
• Use the Scribble Tool
• Add Text to Any Closed Shape
• Place Cell Contents in a Shape
• Add Connectors to Join Shapes
• Draw Business Diagrams with Excel
• Choose the Right Type of SmartArt
• Use the Text Pane to Build SmartArt
• Change a SmartArt Layout
• Finalize a SmartArt Layout Before Adding Pictures
• Format SmartArt
• Switch to the Format Tab to Format Individual Shapes
• Don’t Convert Another Layout to Create an Organization Chart
• How Do the Labeled Hierarchy SmartArt Graphics Work?
• How Does Excel Decide How Many Shapes Per Row?
• Add New SmartArt Layouts
• Use Cell Values as the Source for SmartArt Content
• Change the Background of a Worksheet
• Add a Printable Background to a Worksheet
• Remove Hyperlinks Automatically Inserted by Excel
• Change the Width of All Columns with One Command
• Control Page Numbering in a Multisheet Workbook
• Use White Text to Hide Data
• Hide and Unhide Data
• Temporarily See a Hidden Column Without Unhiding
• Build Complex Reports Where Section 1 Doesn’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
• Hide Error Cells When Printing
• Organize Your Worksheet Tabs with Color
• Copy Cell Formatting, Including Column Widths
• Debug Using a Printed Spreadsheet
• Copied Formula Has Strange Borders
• Double Underline a Grand Total
• Use the Border Tab in Format Cells
• Fit a Slightly Too-Large Value in a Cell
• Show Results as Fractions
• Convert a Table of Numbers to a Visualization
• Prevent Outliers from Skewing the Visualizations
• Add Icons to Only the Good Cells
• Select Every Kid in Lake Wobegon
• Color All Sales Green for a Day if Total Sales Exceed $1,000
• Turn Off Wrap Text in Pasted Data
• Delete All Pictures in Pasted Data
• Add WordArt to a Worksheet
• Chart and SmartArt Text Is Automatically WordArt
• Use MapPoint to Plot Data on a Map
• Why Does Excel Mark Cells with a Purple Indicator?
• Add a Dropdown to a Cell
• Store Lists for Dropdowns on a Hidden Sheet
• Allow Validation Lists to Automatically Redefine as They Grow
• Configure Validation to “Ease up”
• Use Validation to Create Dependent Lists
• Add a ToolTip to a Cell to Guide the Person Using the Workbook