Learn Excel from MrExcel
September 2005
![Learn Excel from MrExcel Learn Excel from MrExcel](jpg/learn-excel-from-mrexcel.jpg)
A revolutionary new book on Excel!
I've learned more from this class than in 12 years of working with Lotus and Excel!
Buy Now »- 836 Pages
- Publisher: Holy Macro! Books
- ISBN: 1-932802-12-6
"Almost as good as being live and in person at Jelen's Power Excel Seminar."
If you ever find yourself in Akron, Ohio - you should make sure to attend Bill Jelen's Power Excel Seminar at the University of Akron. Jelen will have you laughing while you learn more about Excel than you thought possible. If you use Excel 20+ hours a week, attending this seminar will save you 100+ hours per year. Don't take my word for it: read comments from attendees:
"Very knowledgeable!"
"This is the best class in Excel. I will be able to use these techniques every day. I only wish I had attended years ago."
"Best computer class I've taken, anywhere, anytime."
"Excellent! Would recommend it to everyone."
"Bill is great!"
My research at MrExcel shows that for the 10 most powerful spreadsheet tools, less than 45% of the people make regular use of each tool. My seminars are popular because I reveal how to really make use of these tools. I show real-life business examples, show how the tool can dramatically improve your efficiency. But... I am not just a puppet of Microsoft. I will also tell you the dark side of any tool. I will tell you the Gotchas and problems that can crop up when you try to use the tool. This is oh-so-important - when you try to use a tool for the first time and you don't know the gotchas, you will have a less-than-pleasant experience.
"Revolutionary - not an Excel bible - learn without even realizing you are learning."
Many Excel books try to go in a serial fashion through every single Excel option. These books are tough to use. No one at my dinner table ever uses the word "concatenation" in dinner conversation. How would you know to turn to the chapter on concatenation when you need to learn how to join a column of first names with a column of last names?
This book offers 277 real-life business problems. Each problem includes an explanation of the problem, strategy for solving, details on the strategy, any gotcha's, alternate approaches, and a summary. You will never have to go hunting for "Concatenation". Instead, turn to the topic on "How to join two text columns".
Over the course of reading through these business solutions, you will encounter every important Excel function and tool.
-
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