Learn Excel 97 Through Excel 2007 from Mr. Excel

377 Excel Mysteries Solved!

Bill Jelen

• Publisher: Holy Macro! Books
• Year: 2007
• Print ISBN: 9781932802276
• eBook ISBN: 9781615474028
• Edition: 2
• Topic: Ciencia de la computación
• Subtopic: Aplicaciones de escritorio

Updating the previous edition's tips to make them compatible with Excel 2007, and featuring new tips that are only available in Excel 2007, this new edition of Mr. Excel's popular software guide even incorporates suggestions sent in by readers. Each featured topic has a problem statement and description, followed by a broad strategy for solving the problem. Mr. Excel then walks readers through through the specific steps to solve the issue. Alternate strategies are also provided, along with common "gotchas" that trip users up, leaving readers with not only answers to their specific dilemmas, but also with new and quicker ways to use formulas and spreadsheets.

Use Live Preview

Problem: I often need to figure out which font to use and want to preview the different styles on a chart or SmartArt graphic.

Strategy: The Live Preview feature in Excel 2007 makes choosing from a gallery very easy. You just select a range in Excel and then open the Font dropdown. When you hover over a font name in the list, Excel will show you the spreadsheet in that font.

Figure 49

Hover over a font to see a preview.

Note that the change is not permanent in the worksheet. You can continue hovering over new fonts, and Excel will show you a preview of the font (see Figure 50).

Figure 50

Preview other fonts by hovering over them.

When you find a font that looks good, you can click the font name to select it. Excel will then apply the font to the selected range.

Additional Details: Many galleries besides the Font dropdown offer Live Preview. It is likely that even more galleries will inherit this feature in future versions of Excel.

Gotcha: Live Preview is memory intensive. You can turn off the feature if your computer doesn’t have the processing power to handle it. Select Office Icon – Excel Options – Popular – Enable Live Preview.

Summary: Live Preview allows you to quickly see the effects of many formatting changes in Excel.

Commands Discussed: Office Icon – Excel Options – Popular – Enable Live Preview

Get Quick Access to Formatting Options
Using the Mini Toolbar

Problem: Why do I have to always go to the top of the window to reach formatting commands? I loved having floating toolbars in Excel 2003. Why did Microsoft get rid of them?

Strategy: Excel 2007 offers one floating toolbar, but it is elusive. Here’s how you use it:

1) Select some text in a chart. Look very closely above and to the right of the selection. Excel draws in a nearly invisible Mini toolbar. (It may not even appear in the printed version of this book.) Look for the Bold icon above the final “a” in Data in Figure 51.

Figure 51

The Mini toolbar starts out nearly invisible.

2) Move the mouse toward the Mini toolbar. The Mini toolbar will become more visible and will be available for use (see Figure 52).

Figure 52

Move the mouse toward the toolbar, and it will solidify.

Gotcha: If you generally select text by dragging the mouse from right to left, you will never see the mini toolbar. I used Excel for months without ever causing it to appear.

Additional Details: If you move the mouse toward the Mini toolbar and then away, the Mini toolbar will solidify and then disappear. You can keep making it appear and disappear, but if you eventually get a certain number of pixels away from the toolbar, Excel will hide the toolbar until you reselect the data.

Additional Details: The Mini toolbar will appear often in Word. In order for it to appear in an Excel cell, you have to select only a portion of the characters in the cell. In this case, you will see an abbreviated version of the Mini toolbar.

You can also cause the Mini toolbar to appear if you select cells and right-click.

Summary: The Mini toolbar puts 22 commands in close proximity to your mouse.

MIX FORMATTING IN a Single CELL

Problem: I’d like to use strikethrough on the text in part of a cell. Is this possible?

Strategy: You can apply different formatting to certain characters in a cell.

You select the cell and then press F2 or double-click the cell. Select characters with the mouse or by using the arrow keys in combination with the Shift key. You can then apply formatting. Many icons on the Home tab of the ribbon are enabled. Any formatting shortcut keys, such as Ctrl+5 for strikethrough, will work. If you need to apply superscript or subscript, you use the Format Cells dialog by pressing Ctrl+1 or click the dialog launcher in the bottom-right corner of the Font group.

Figure 53 shows a variety formatting applied to part of a cell.

Figure 53

Format a subset of characters in a cell.

Gotcha: In addition to the character formatting, you can apply other formatting to the entire cell. For example, in C5, you can safely apply italic or underline to the cell without removing the bold from the first word. However, if you apply bold to the entire cell, Excel will not remember that you started with just the first word bold. You can not use the Bold icon on the entire cell to toggle back to the formatting shown in the figure.

Gotcha: If you later use the Justify command, the internal formatting will be lost.

Summary: You can mix font formatting within a cell.

Commands Discussed: Home – Format – Format Cells

Excel 97-2003: Format – Cells

Copy the Characters from a Cell Instead of Copying an Entire Cell

Problem: I need to copy...

Table of contents

• ABout The Author
• Acknowledgments
• Foreword
• 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
• 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?
• 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
• 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 My 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 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
• 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
• Afterword
• The Print Version of This Book Is Heavy