Learn Excel from MrExcel

PART 1: 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 Specifi c 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 Changes 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 Ranges
• 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 ƒx 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 SUMIF
• 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 “Profi t & 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 Summarize 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 with a 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 in a 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 a Pivot 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 Profi tability in a Profi t 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 Infl ection 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
• Confi gure Validation to “Ease up”
• Use Validation to Create Dependent Lists