Learn Excel – A ‘How-To’ Manipulate Data Guide for Excel

Table of Contents: Learn Excel from Mr Excel – 277 Excel Mysteries Solved

Are you new to Microsoft Excel? Or consider yourself an intermediate or expert user? Here you will find a wealth of walk through tutorials as found in the acclaimed book ‘Learn Excel with Mr. Excel’. Solving 277 mysteries and walking you through topics, this is a great step by step or standalone set of user guides for your entire Excel environment. Here are the topics as expertly laid out by author Bill Jelen.

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
• 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 ‘Profit & 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 Pivot Table 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 1: The Excel Environment

Part 2: Calculating with Excel

Part 3: Wrangling Data

Part 4: Formatting Excel