|
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
|
