Learn Excel 97 through Excel 2007 from MrExcel
November 2007
![Learn Excel 97 through Excel 2007 from MrExcel Learn Excel 97 through Excel 2007 from MrExcel](jpg/learn-excel-97-through-2007-from-mrexcel.jpg)
I never thought there could be so many 'secrets' just laying there in front of me. This book has helped me become more efficient already."
I've learned more from this class than in 12 years of working with Lotus and Excel!
Buy Now »- 827 Pages
- Publisher: Holy Macro! Books
- ISBN: 978-1-932802-27-6
- PDF ISBN: 978-1-615472-04-8
"Includes 100 more tips than the previous edition. Instructions have been expanded to include commands in either Excel 2007 or Excel 97-2003."
This is an in-depth look at the topics from Bill's Power Excel Seminar. In the process of solving 377 real-world business problems, you will learn how to use Excel more efficiently than you ever thought possible. Updated to cover Excel 97 through Excel 2007, this book includes 100 new topics. There are three ways to get this book:
"Each featured topic has a problem statement and description, followed by a broad strategy for solving the problem."
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 377 real-life business problems. Each topic starts with a problem and then provides a strategy for solving the problem. Some topics may offer additional details, alternate strategies, results, gotchas, and other elements, as appropriate to the topic. Each chapter wraps up with a summary and a list of any Excel commands or functions used in the chapter. Commands are given separately for Excel 2007 and Excel 97-2003. 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.
-
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