Your One Stop for Excel Tips & Solutions

  MrExcel - Photos of MrExcel
About MrExcel
Consulting Services
Learn Excel Resources
Excel - Visual Learning
Challenge of the Month
MrExcel Seminars
Message Board
MrExcel Store
Having an Excel Emergency?

Click Add as Fan in top right
of our Facebook page



Paperback: 806 Pages


Learn Excel 2007 through Excel 2010 From MrExcel by Bill Jelen

Master Pivot Tables, Subtotals, Charts, VLOOKUP, IF, Data Analysis, and Much More!

512 Excel Mysteries Solved!

"Includes 35% more tips than the previous edition. "

This is an in-depth look at the topics from Bill's Power Excel Seminar. In the process of solving 512 real-world business problems, you will learn how to use Excel more efficiently than you ever thought possible. The new topics include features unique to Excel 2010, and new techniques available in all versions of Excel.. There are two 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 512 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. 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.

A Note About Versions

The original edition of Learn Excel from MrExcel offered 277 tips that applied to Excel 97 through Excel 2003.

The second edition offered 377 tips and covered Excel 97 through Excel 2007.

This third edition offers 512 tips and includes features in Excel 2007 and Excel 2010. You will find that 60 topics were removed from the second edition (or consolidated with other topics.) That means there are 195 new topics in the book. Of the 195 new topics, you will find that 44 of them are applicable to Excel 2010 only. However, throughout the book, you will find 151 brand new tips that will apply equally well to Excel 2007 through Excel 2010. These are topics that Iíve picked up since the second edition was published.

Read what others have commented about this book:

"His no-nonsense approach and "Everyman's' explanation help those of us who are not 'propeller heads', but still need to harness Excels power to do what we want & need it to do. I've saved countless hours of aggravation and fustration through his guidance, in turn, saving our shop $ and LOTS of user hours. There is no substitute for MrExcel!! "

"MrExcel's tips will take you from Excel Zero to Hero in no time flat! This book is sure to make you the "MrExcel" at your office!"

"I have never read anything about the computer or computer programs so well written and easily followed, with clear diagrams and explanations. I am one of those people that cannot read directions, but these Excel lessons changed all that."

Product Details
Paperback: 806 Pages ; Dimensions (in inches): 9.375 x 7.25 x 1.8 inches
Publisher: Holy Macro! Books; Third edition (July 2011)
ISBN: 978-1-932802-44-3
Price: $39.95


Complete Table of Contents:

Find Icons on the Ribbon, page 3

Seriously, I Really Canít Find This, page 4

Where Are My Macros?, page 5

What Happened to Tools, Options?, page 7

New for Excel 2010! Customizing the Ribbon, page 8

Go Wide, page 13

Minimize the Ribbon to Make Excel Feel a Bit More Like Excel 2003, page 15

New! Use a Wheel Mouse to Scroll Through The Ribbon Tabs, page 15

Use Dialog Launchers to Access the Excel 2003 Dialog, page 16

New! Why Do The Charting Ribbon Tabs Keep Disappearing?, page 16

Icon, Dropdowns, and Hybrids, page 17

All Commands Start at the Top (Except for 2 Controls at the Bottom), page 18

Make Your Most-Used Icons Always Visible, page 20

The Alt Keystrokes Still Work in 2010 (If You Type Them Slowly Enough), page 22

Use New Keyboard Shortcuts to Access the Ribbon, page 24

Why Do I Have Only 65,536 Rows?, page 26

Which File Format Should I Use?, page 26

Share Files with People Who Are Still Using Excel 97 Through Excel 2003, page 28

New for Excel 2010! Why Does The File Menu Cover The Entire Screen?, page 30

Increase the Number of Workbooks in the Recent Documents List, page 31

New for Excel 2010! How Do I Close The File Menu?, page 31

New for Excel 2010! I Just Want The Old Print Preview Back, page 33

New for Excel 2010! Change All Print Settings in Excel 2010, page 34

Use Live Preview, page 36

Get Quick Access to Formatting Options Using the Mini Toolbar, page 37

New for Excel 2010! What Is Protected Mode?, page 38

Use a Trusted Location to Prevent Excelís Constant Warnings, page 39

My Manager Wants Me to Create a New Expense Report from Scratch, page 40

Open a Saved File Whose Name You Cannot Recall, page 41

Open a Copy of a Workbook, page 42

ExcelíS Obsession with Security Has Hampered Linked Workbooks, page 43

Use a Workspace to Remember What Workbooks to Open, page 44

New! Open Excel with Ctrl+Alt+X, page 45

Have Excel Always Open Certain Workbook(s), page 46

Set up Excel Icons to Open a Specific File on Startup, page 46

Use a Macro to Customize Startup, page 48

Control Settings for Every New Workbook and Worksheet, page 49

Automatically Move the Cell Pointer in a Direction After Entering a Number, page 50

Return to the First Column After Typing the Last Column, page 51

Enter Data in a Circle (Or Any Pattern), page 52

How to See Headings as You Scroll Around a Report, page 54

How to See Headings and Row Labels as You Scroll Around a Report, page 56

I Navigate by Sliding the Scrollbar and Now the Slider Has Become Tiny, page 58

New! Jump to the Edge of the Data, page 59

New! Jump to Next Corner of Selection, page 60

Copy a Formula to All Data Rows, page 61

New! Zoom with the Wheel Mouse, page 61

Copy the Characters from a Cell ≠Instead of Copying an Entire Cell, page 62

A Faster Way To Paste Special, page 62

Quickly Turn a Range on Its Side, page 67

New! Hide the Paste Options in Excel 2007, page 67

New! Quickly Rearrange Rows Or Columns, page 68

Copy Cells from One Worksheet to Many Other Worksheets, page 70

Find Text Numbers, page 71

New! Quickly Copy Worksheets, page 71

Why Canít Excel Find a Number?, page 73

Mix Formatting In A Single Cell, page 76

Enter a Series of Months, Days, or More by Using the Fill Handle, page 76

Have the Fill Handle Fill Your List of Part Numbers, page 79

Use Excel as a Word Processor, page 81

New! Teach Excel to Fill A, B, C, page 81

Add Excel to Word, page 83

Use Hyperlinks to Create an Opening Menu for a Workbook, page 84

Arrange Windows to See Two or More Open Workbooks, page 86

Compare Two Worksheets Side by Side, page 87

Spell check a Region, page 88

Stop Excel from AutoCorrecting Certain Words, page 88

Use AutoCorrect to Enable a Shortcut, page 90

Why Wonít the Track Changes Feature Work in Excel?, page 91

Translate with Excel, page 92

I am a Lobbyist Writing Policy Papers for the White House, page 94

How to Print Titles at the Top of Each Page, page 96

Print a Letter at the Top of Page 1 and Repeat Headings at the Top of Each Subsequent Page, page 97

How to Print Page Numbers at the Bottom of Each Page, page 98

How to Make a Wide Report Fit to One Page Wide by Many Pages Tall, page 100

New! Add a Printable Watermark, page 101

New! Print Multiple Ranges, page 102

Add a Page Break at Each Change in Customer, page 103

New! Save My Worksheet as a PDF File, page 106

Send an Excel File as an Attachment, page 107

Save Excel Data as a Text File, page 108

Close All Open Workbooks, page 110

Use a Laser Printer to Have Excel Calculate Faster, page 110

New for Excel 2010! I Just Closed an Unsaved Workbook, page 111

New for Excel 2010! Roll Back to an AutoSaved Version, page 113

Have Excel Talk to You, page 114

Enter Special Symbols, page 115

What Do All the Triangles Mean?, page 116

New! Why does Excel Insert Cell Addresses When I Edit In a RefEdit Box?, page 118

New for Excel 2010! Beware of Excel Starter, page 119

New for Excel 2010! Edit your Excel Workbooks on the Web, page 120

New for Excel 2010! Collaborate in an Excel Workbook, page 123

New for Excel 2010! Embed Your Dashboard in a Blog Post, page 123

New! Print all Excel Keyboard Shortcuts, page 125

Get Free Excel Help, page 126

Start a Formula with = or +, page 131

Three Methods of Entering Formulas, page 132

Use Parentheses to Control the  Order of Calculations, page 135

Long Formulas in the Formula Bar, page 137

Copy a Formula That Contains Relative References, page 138

Copy a Formula While Keeping One Reference Fixed, page 140

Create a Multiplication Table, page 141

Calculate a Sales Commission, page 142

Simplify the Entry of Dollar Signs in Formulas, page 144

Learn R1C1 Referencing to Understand Formula Copying, page 146

Create Easier-to-Understand Formulas with Named Ranges, page 149

Use Named Constants to Store Numbers, page 151

Total Without Using a Formula, page 152

Add or Multiply Two Columns Without Using Formulas, page 153

Join Two Text Columns, page 156

Join Text with a Date or Currency, page 158

Sort on One Portion of an Account ID, page 160

How to Isolate the Center Portion of an Account ID, page 161

How to Isolate Everything Before a Dash in a Column by Using Functions, page 162

How to Use Functions to Isolate Everything After a Dash in a Column, page 163

How to Use Functions to Isolate Everything After the Second Dash in a Column, page 164

How to Separate a Part Number into Three Columns, page 165

Excel Is Randomly Parsing Pasted Data, page 168

New! I Lose Leading Zeroes From CSV Files, page 169

New! Open CSV File With Dates in D/M/Y Format, page 169

New! Parse Data With Leader Lines, page 170

Change Smith, Jane to Jane Smith, page 172

New! Parse Multi-Line Cells, page 172

Convert Numbers to Text, page 174

Fill a Cell with Repeating Characters, page 175

New! CLEAN Hasnít Kept Up With The Times, page 176

Add the Worksheet Name as a Title, page 177

Use AutoSum to Quickly Enter a Total Formula, page 178

AutoSum Doesnít Always Predict My Data Correctly, page 179

Use the AutoSum Button to Enter Averages, Min, Max, and Count, page 182

New! Ditto The Formula Above, page 183

The Count Option of the AutoSum Dropdown Doesnít Appear to Work, page 184

New! Total the Red Cells, page 186

Automatically Number a List of Employees, page 188

Discover New Functions Using the fx Button, page 190

Get Help on Any Function While Entering a Formula, page 191

Yes, Formula Autocomplete Is Cool, if You Can Stop Entering the Opening Parentheses, page 192

Use F9 in the Formula Bar to Test a Formula, page 193

Quick Calculator, page 195

When Entering a Formula, You Get the Formula Instead of the Result, page 196

You Change a Cell in Excel but the Formulas Do Not Calculate, page 198

New! Calculate One Range, page 199

New! Why Use the Intersection Operator?, page 200

New! Find the Longest Win Streak, page 204

New! Add B5 On All Worksheets, page 206

New! Consider Formula Speed, page 209

New! Exact Formula Copy, page 210

Calculate a Loan Payment, page 212

Calculate Many Scenarios for Loan Payments, page 213

Back into an Answer Using Goal Seek, page 213

Create an Amortization Table, page 215

Do 40 What-if Analyses Quickly, page 217

New! What-If For 3 Or More Variables, page 218

Rank Scores, page 222

Rank a List Without Ties, page 223

Sorting with a Formula, page 224

New! Round Numbers, page 224

Round to the Nearest $0.05 with MROUND, page 225

Round Prices to the Next Highest $5, page 226

New! Round 0.5 towards Even Per ASTM-E29, page 226

New! Separate the Integer From the Decimals, page 227

Why Is This Price Showing $27.85000001 Cents?, page 228

Calculate a Percentage of Total, page 229

Calculate a Running Percentage of Total, page 231

Use the ^ Sign for Exponents, page 232

Raise a Number to a Fraction to Find the Square or Third Root, page 232

Calculate a Growth Rate, page 234

Find the Area of a Circle, page 235

Figure Out Lottery Probability, page 236

Help Your Kids with Their Math, page 238

Convert Units, page 240

Match Web Colors with HEX2DEC, page 240

New! Find the Second Largest Value, page 242

New! Format Every Other Row in Green, page 242

New! Use IF to Calculate a Bonus, page 246

New! IF with Two Conditions, page 246

New! Tiered Commission Plan with IF, page 249

New! Display Up/Down Arrows, page 251

New! Stop Showing Zeroes in Cell Links, page 254

Calculate Sales Over Quota, page 256

Count Records That Match a Criterion, page 256

Build a Table That Will Count by Criteria, page 258

New! Sum Records That Match a Criterion, page 258

Can the Results of a Formula Be Used in SUMIF?, page 259

Calculate Based on Multiple Conditions, page 260

New! Avoid Errors Using IFERROR, page 261

New! Multiple Conditions Using SUMPRODUCT, page 263

New! Use VLOOKUP to Join Two Tables, page 266

New! Every VLOOKUP Ends in False, page 268

New! Lookup Table Does Not Have to Be Sorted, page 268

New! Beware of #N/A from VLOOKUP, page 270

New! Add New Items to the Middle Of Your Lookup Table, page 270

Remove Leading and Trailing Spaces, page 271

New! Consider Naming the Lookup Table, page 271

New! Your Lookup Table Can Go Across, page 272

New! Copy a VLOOKUP Across Many Columns, page 273

New! INDEX Sounds Like AN Inane Function, page 274

New! You Already Know MATCH, Really!, page 275

New! INDEX Sounds Like an Inane Function - II, page 275

New! VLOOKUP Left, page 276

New! Fast Multi-Column VLOOKUP, page 277

New! Return the Next Larger Value in a LOOKUP, page 279

New! Two-Way Lookup, page 280

Combine Formulas into a Mega-Formula, page 282

Combine Two Lists Using VLOOKUP, page 284

Watch for Duplicates  When Using VLOOKUP, page 287

New! Return the Last Entry, page 288

New! Return the Last Matching Value, page 291

New! Sum All of the LOOKUPs, page 292

New! Embed a Small Lookup Table In Formula, page 294

I Donít Want to Use a Lookup Table to Choose One of Five Choices, page 295

New! Lookup Two Values, page 296

Add Comments to a Formula, page 300

Create Random Numbers, page 301

Randomly Sequence a List, page 302

Play Dice Games with Excel, page 303

New! Generate Random Without Repeats, page 304

Calculate a Moving Average, page 305

Calculate a Trendline Forecast, page 306

Build a Model to Predict Sales Based on Multiple Regression, page 308

Measure the Accuracy of a Sales Forecast, page 311

Switching Columns into Rows Using a Formula, page 313

New! SUM a Range that is C5 Rows Tall, page 315

New! Whatever Happened to the @@ Function?, page 318

Tables Are Like a Database in Excel, page 319

New! Dealing with Table Formulas, page 321

Rename Your Tables, page 322

New! Charts , VLOOKUP & Pivots Expand With The Table, page 322

Before Deleting a Cell, Find out if Other Cells Rely on It, page 323

Calculate a Formula in Slow Motion, page 325

Which Cells Flow into This Cell?, page 326

New! Color all Precedents or Dependents, page 327

Monitor Distant Cells, page 328

New! Use Real Dates, page 330

New! How Can I Tell If Have Real Dates?, page 332

New! Convert Text Dates to Real Dates, page 334

New! Format Dates, page 336

New! Format Dates As Quarters or Weeks, page 337

Display Monthly Dates, page 338

New! Add a Column to Show Month or Weekday, page 339

New! Calculate First of Month, page 340

Calculate the Last Day of the Month, page 341

New! Calculate Invoice Due Dates, page 343

New! Calculate Receivable Aging, page 343

Now, OR Today?, page 345

New! Find the Last Sunday Of the Month, page 346

Calculate Work Days, page 349

New! Calculate Age in Years, Months, Days, page 350

New! Coerce an Array of Dates from 2 Dates, page 352

New! Use Real Times, page 354

Strangeness of TIme Formatting, page 356

New! Convert Time to Decimal Hours, page 358

New! Calculate with Time, page 358

Enter Minutes and Seconds, page 359

Convert Text to Time, page 360

Can Excel Track Negative Time?, page 360

New! Fill Blanks With Value Above, page 362

How to Set up Your Data for Easy Sorting and Subtotals, page 367

How to Fit a Multiline Heading into One Cell, page 368

New! No Tiny Blank Columns Between Columns, page 370

How to Sort Data, page 372

Sort Days of the Week, page 374

Sort a Report into a Custom Sequence, page 375

Sort All Red Cells to the Top of a Report, page 377

New! Sort Pictures With Data, page 378

Quickly Filter a List to Certain Records, page 380

New! Use Search While Filtering, page 383

New! Filter by Selection, page 384

Use AutoSum After Filtering, page 386

New! Filter Only Some Columns, page 386

Find the Unique Values in a Column, page 387

Use Advanced Filter, page 389

Replace Multiple Filter Criteria with a Single Row of Formulas, page 392

Add Subtotals to a Data set, page 393

Use Group & Outline Buttons to Collapse Subtotaled Data, page 395

Copy Just Totals from Subtotaled Data, page 396

New! Manually Apply Groups, page 396

New! Sort Largest Customers to the Top, page 398

Enter a Grand Total of Data Manually Subtotaled, page 400

New! Select 100 Columns in Subtotals, page 400

Add Other Text to the Subtotal Lines, page 402

Subtotals by Product Within Region, page 403

Format the Subtotal Rows, page 406

My Manager Wants a Blank Line After Each Subtotal, page 406

Subtotal One Column and COUNT Another Column, page 409

New! Can You Get Medians?, page 411

New! Horizontal Subtotals, page 412

Be Wary, page 414

Send Error Reports, page 414

Help Make Excel 2014 Better, page 415

Remove Blank Rows from a Range, page 417

Remove Blanks from a Range While Keeping the Original Sequence, page 418

double Space Your Data Set, page 420

Use Find to Find an Asterisk, page 421

Use an Ampersand in a Header, page 422

Hide Zeros & Other Custom Number Formatting  Tricks, page 424

Use Consolidation to Combine Two Lists, page 426

Combine Four Quarterly Reports, page 430

Find Total Sales by Customer by Combining Duplicates, page 433

Remove Duplicates, page 435

Protect Cells That Contain Formulas, page 436

New! Find Differences In Two Lists, page 439

Number Each Record for a Customer, Starting at 1 for a New Customer, page 443

Add a Group Number  to Each Set of Records That Has a Unique Customer Number, page 444

Deal with Data in Which Each Record Takes Five Physical Rows, page 445

Add a Customer Number to Each Detail Record, page 450

Use a Built-in Data Entry Form, page 453

New! Cell AutoComplete Stopped Working, page 454

Use a Pivot Table to Summarize Detailed Data, page 456

Your Manager Wants Your Report Changed, page 460

Add or Remove Fields from an Existing Pivot Table, page 461

Summarize Pivot Table Data by Three Measures, page 462

Why Does the Pivot Table Field List Keep Disappearing?, page 464

Move or Change Part of a Pivot Table, page 466

See Detail Behind One Number in a Pivot Table, page 466

Use Multiple Value Fields as a Column or Row Field, page 467

Update Data Behind a Pivot Table, page 469

New! Convert Your Data to a Table Before Adding Records, page 470

New! Create a Flattened Pivot Table for Reuse, page 472

Replace Blanks in a Pivot Table with Zeros, page 474

Collapse and Expand Pivot Fields, page 476

Specify a Number Format for a Pivot Table Field, page 477

New! Preserve Column Widths, page 479

New! Show Yes/No in a Pivot Table, page 481

Format Pivot Tables with the Gallery, page 483

None of the 46,273 Built-In Styles Do What My Manager Asks For, page 485

Select Pivot Table Parts For Formatting, page 487

Apply Conditional Formatting to a Pivot Table, page 488

New! Can I Save Formatting in a Template?, page 490

Manually Re-sequence the Order of Data in a Pivot Table, page 494

Present a Pivot Table in High-to-Low Order by Revenue, page 495

Group Daily Dates by Month in a Pivot Table, page 497

New! Create a Year-Over-Year Report, page 500

Group by Week in a Pivot Table, page 501

Limit a Pivot Report to Show Just the Top 5 Customers, page 503

Build a Better Top Five Using Groups, page 505

New! Build a Better Top Five with A Filter Hack, page 507

Why Arenít the Cool New Filters Available in My Pivot Table?, page 509

Limit a Report to Just One Region, page 510

Create an Ad-Hoc Reporting Tool, page 511

Create a Report for Every Customer, page 512

Create Pivot Charts, page 513

New for Excel 2010! Add Visual Filters to a Pivot Table, page 515

New for Excel 2010! Run Many Pivot Tables From one SLicer, page 517

Group Employees Into Age Bands, page 518

New! Create a Frequency Distribution, page 520

New! Grouping 1 Pivot Table Groups Them All, page 521

New! Reduce Size 50% Before Sending, page 524

New! Drag Fields to the Pivot Table, page 526

Why Canít Co-Workers with Excel 2003 Use My Pivot Table?, page 528

Create a Report That Shows Count, Min, Max, Average, Etc., page 528

Better Calculations with Show Values As, page 530

New for Excel 2010! Pivot Ranks Donít Match RANK(), page 533

Calculated Fields in a Pivot Table, page 534

Add a Calculated Item to Group Items in a Pivot Table, page 536

Group Text Fields to BUild TerritoriesINstead of Using Calculated Items, page 539

New! Calculations Outside of Pivot Tables, page 543

New! Show Customer Account & Name, page 546

New! Show Months with Zero Sales, page 549

Create a Unique List of Customers with a Pivot Table, page 552

Use a Pivot Table to Compare Two Lists, page 552

Use a Pivot Table When There Is No Numeric Data, page 555

New! Report Text in a Pivot Table, page 556

New! Fix Misspelled Customer Names, page 558

Create a Pivot Table from Access Data, page 559

New! Whatever Happened to Multiple Consolidation Ranges in Pivot Tables?, page 560

New for Excel 2010 Power Pivot! Five Reasons to Use PowerPivot, page 562

New for Excel 2010 Power Pivot! Get the Correct Version of PowerPivot, page 562

New for Excel 2010 Power Pivot! Get Excel Data Into PowerPivot, page 564

New for Excel 2010 Power Pivot! Why Canít I Edit Data in PowerPivot?, page 566

New for Excel 2010 Power Pivot! Import External Data to PowerPivot, page 566

New for Excel 2010 Power Pivot! Define Relationships Between Tables, page 568

New for Excel 2010 Power Pivot! Adding Calculations In PowerPivot, page 569

New for Excel 2010 Power Pivot! Refer to a Related Table in a Formula, page 570

New for Excel 2010 Power Pivot! Hide Fields in PowerPivot, page 571

New for Excel 2010 Power Pivot! Missing the Point: Sorting & Filtering, page 572

New for Excel 2010 Power Pivot! Pivot Table, Pivot Charts, Tables & Charts, Charts & Charts, page 572

New for Excel 2010 Power Pivot! Using the PowerPivot Field List, page 573

New for Excel 2010 Power Pivot! PowerPivot Arranges Your Slicers, page 575

New for Excel 2010 Power Pivot! Month Names Donít Sort in PowerPivot, page 576

New for Excel 2010 Power Pivot! Feature X Wonít Work in PowerPivot, page 579

New for Excel 2010 Power Pivot! Using Calculations Other Than Sum, page 582

New for Excel 2010 Power Pivot! Replace Calculated Fields with DAX, page 582

New for Excel 2010 Power Pivot! Calculate() is Like SUMIFS(), page 584

New for Excel 2010 Power Pivot! Unapply a Filter Using DAX, page 585

New for Excel 2010 Power Pivot! Unfilter Using Time Intelligence, page 586

New for Excel 2010 Power Pivot! Using MAXX, MINX, and SUMX, page 588

New for Excel 2010 Power Pivot! Convert PowerPivot to Formulas, page 591

New for Excel 2010 Power Pivot! January Actuals and February Plan, page 592

Web Queries Fail with Web 2.0, page 595

New! Use Easy-XL for Data Wrangling, page 597

Create a Chart with One Click, page 601

Teach Excel Your Favorite Chart, page 602

Move a Chart, page 603

New! Copy a Chart Detached from the Data, page 604

Add New Data to a Chart, page 606

Begin Formatting The Chart on Design, page 608

New! Continue Formatting with Layout, page 609

Legend At the Top, page 610

Display an Axis in Millions Using the Layout Tabís Built-in Menus, page 612

Display an Axis in Trillions Using the More Options Choice, page 614

Select Anything on a Chart to Format, page 614

The Format Dialog Box Offers a New Trick, page 617

Use Meaningful Chart Titles, page 617

Avoid 3-D Chart Types, page 619

New! Prevent the Drop to Zero, page 621

Explode One Slice of the Pie, page 622

New! Chart On a Coin, page 623

New! Move Small Pie Slices To Second Chart, page 624

Add a Trendline to a Chart, page 627

New! See Detail on Large & Small Data Points, page 629

Chart Two Series with Differing Orders of Magnitude, page 631

New! Interactive Chart to SHow Next Customer, page 635

New! Tie the Chart Title to a Cell, page 638

New! Use an Invisible Series to Float COlumns, page 639

New! Use Rogue Series for Shading, page 641

New! Two Stacked, One Clustered Column, page 644

New! Conditional Format a Chart, page 646

New! Scatter Charts are Versatile But Require a Different Workflow, page 648

New! When do I Use Which Chart Type?, page 658

New for Excel 2010! Create Tiny Charts with Sparklines, page 661

New for Excel 2010! Sparklines Are Not Scaled Together, page 663

New for Excel 2010! What is the Win Loss Sparkline For?, page 664

New for Excel 2010! Labeling Sparklines, page 665

New for Excel 2010! Shade the Normal Range in a Sparkline, page 667

Convert a Table of Numbers to a Visualization, page 668

New! Control Values for Each Icon, page 672

Add Icons to Only the Good Cells, page 674

New for Excel 2010! Data Bars Options in Excel 2010, page 675

New for Excel 2010! Comparative Histogram, page 677

Select Every Kid in Lake Wobegon, page 678

Color All Sales Green for a Day if Total Sales Exceed $1,000, page 678

New! There is a Font Optimized for Excel, page 680

New! Show Checkmarks in Excel, page 681

Use the Border Tab in Format Cells, page 682

Double Underline a Grand Total, page 684

New! Where Are My Excel 2003 Colors?, page 687

Transform Black-and-White Spreadsheets to Color by Using a Table, page 690

Fit a Slightly Too-Large Value in a Cell, page 691

Turn Off Wrap Text in Pasted Data, page 692

Delete All Pictures in Pasted Data, page 692

New! Prevent Long Text from Spilling, page 693

New! Show Two Values in a Split Cell, page 694

For Each Cell in Column A, Have Three Rows in Column B, page 695

Show Results as Fractions, page 697

New! Better SCientific Notation, page 698

New! Fill a Cell with Asterisks, page 699

Use Cell Styles to Change Formats, page 700

New! I type 152 and get 1.52, page 700

New! Add Your Own Styles, page 701

New! Share Styles Between Workbooks, page 703

Move Columns by Sorting Left to Right, page 704

Move Columns Using Insert Cut Cells, page 706

Move Rows or Columns with Shift Drag, page 706

Change the Width of All Columns with One Command, page 707

New! Copy Column Widths to a New Range, page 708

New! Copy Row Heights, page 710

Use White Text to Hide Data, page 711

New! Hide Values Using a Number Format, page 712

Hide and Unhide Data, page 713

Group Columns Instead of Hiding Them, page 715

Hide Error Cells When Printing, page 715

New! Unhide All Sheets, page 717

Organize Your Worksheet Tabs with Color, page 718

Copy Formatting to a New Range, page 719

Copy Without Changing Borders, page 720

Power Up Format Painter, page 722

New! Fill FOrmatting, page 723

Change All Red Font Cells to Blue Font, page 724

New! Replace Partially Bold Cells, page 725

Change the Look of Your Workbook with Document Themes, page 727

New! Create Your Own Theme, page 729

Change the Background of a Worksheet, page 730

Add a Printable Background to a Worksheet, page 732

Remove Hyperlinks Automatically Inserted by Excel, page 733

New! Select a Hyperlink Cell Without Following the Hyperlink, page 733

New! Pasted URLs Donít Become Hyperlinks, page 733

Leave Helpful Notes with Cell Comments, page 735

Debug Using a Printed Spreadsheet, page 735

Change the Appearance of Cell Comments, page 738

Control How Your Name  Appears in Comments, page 741

Force Certain Comments to Be Always Visible to Provide a Help System to Users of Your Spreadsheet, page 743

Change the Comment Shape to a Star, page 745

Add a Pop-up Picture of an Item in a Cell, page 747

Add a Pop-up Picture to Multiple Cells, page 748

Build Complex Reports Where Columns in Section 1 Donít Line Up with Section 2, page 749

Paste a Live Picture of a Cell, page 751

Add Formatting to Pictures in Excel, page 752

New! Remove Picture Background, page 754

New! Inserting a Screen Shot in Excel 2010, page 756

New! Draw an Arrow to Visually Illustrate That Two Cells Are Connected, page 757

Add Connectors to Join Shapes, page 760

Circle a Cell on Your Worksheet, page 762

Draw Perfect Circles, page 764

Add Text to Any Closed Shape, page 765

Place Cell Contents in a Shape, page 766

Rotate a Shape, page 768

Create Dozens of Lightning Bolts, page 769

Make a Logo into a Shape, page 770

Draw Business Diagrams with Excel, page 773

Choose the Right Type of SmartArt, page 776

Use the Text Pane to Build SmartArt, page 779

Change a SmartArt Layout, page 781

Format SmartArt, page 783

Switch to the Format Tab to Format Individual Shapes, page 785

Use Cell Values as the Source for SmartArt Content, page 787

Add WordArt to a Worksheet, page 789

Chart and SmartArt Text Is Automatically WordArt, page 795

Use MapPoint to Plot Data on a Map, page 796

Add a Dropdown to a Cell, page 798

Configure Validation to ďEase upĒ, page 800

Use Validation to Create Dependent Lists, page 801

Add a ToolTip to a Cell to Guide the Person Using the Workbook, page 803

Product Details
Paperback: 806 Pages ; Dimensions (in inches): 9.375 x 7.25 x 1.8 inches
Publisher: Holy Macro! Books; Third edition (July 2011)
ISBN: 978-1-932802-44-3
Price: $39.95



There are over 150,000 pages at
Use Google
to search our site:


The MrExcel Library


Excel is a registered trademark of the Microsoftģ Corporation.
TM is a registered trademark of Tickling Keys, Inc.

All contents Copyright 1998-2011 by MrExcel Consulting | Domestic and International Copyright Applies | All Rights Reserved