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
