© 2017 by Bill Jelen
Contents
Why Does Office 365 Have Better Features?.................................................................................. 3
Which Version of Office 365 Has Power Pivot?............................................................................... 3
Why Do I Have to Sign in to Excel?............................................................................................... 3
How Can I Use Excel on Dual Monitors?........................................................................................ 4
How Can I Open The Same Workbook Twice?............................................................................... 4
Find Icons on the Ribbon............................................................................................................. 4
Where is File, Exit?...................................................................................................................... 4
Where Are My Macros?................................................................................................................ 5
Customizing the Ribbon............................................................................................................... 6
Go Wide..................................................................................................................................... 8
Minimize the Ribbon to Free Up a Few More Rows......................................................................... 9
Use a Wheel Mouse to Scroll Through The Ribbon Tabs................................................................ 10
Why Do The Charting Ribbon Tabs Keep Disappearing?................................................................ 10
Use Dialog Launchers For More Choices....................................................................................... 10
Icon, Dropdowns, and Hybrids.................................................................................................... 10
Zoom is at the Bottom................................................................................................................ 11
Make Your Most-Used Icons Always Visible.................................................................................. 12
The Excel 2003 Alt Keystrokes Still Work (If You Type Them Slowly Enough)................................. 14
Use Keyboard Shortcuts to Access the Ribbon.............................................................................. 15
Why Do I Have Only 65,536 Rows?............................................................................................. 15
Which File Format Should I Use?................................................................................................ 16
Why Does The File Menu Cover The Entire Screen?..................................................................... 17
How Do I Close The File Menu?.................................................................................................. 17
Increase the Number of Workbooks in the Recent Files List.......................................................... 18
Change All Print Settings in Excel............................................................................................... 18
I Just Want The Old Print Preview Back...................................................................................... 19
Get Quick Access to Formatting Options Using the Mini Toolbar................................................... 19
What Is Protected Mode?.......................................................................................................... 20
Use a Trusted Location to Prevent Excel’s Constant Warnings...................................................... 21
My Manager Wants Me to Create a New Expense Report from Scratch......................................... 22
Open a Copy of a Workbook..................................................................................................... 22
Open Excel with Ctrl+Alt+X...................................................................................................... 23
Have Excel Always Open Certain Workbook(s)........................................................................... 23
Set up Excel Icons to Open a Specific File on Startup................................................................. 24
Use a Macro to Customize Startup............................................................................................ 24
Control Settings for Every New Workbook and Worksheet.......................................................... 25
Excel Says I Have Links, But I Can Not Find Them..................................................................... 26
Automatically Move the Cell Pointer After Enter......................................................................... 27
Return to the First Column After Typing the Last Column........................................................... 27
Enter Data in a Circle (Or Any Pattern)..................................................................................... 28
How to See Headings as You Scroll Around a Report................................................................. 28
How to See Headings and Row Labels as You Scroll Around a Report......................................... 30
Why is the Scrollbar Slider Suddenly Tiny?................................................................................ 30
Why Won’t My Scrollbar Scroll to My Charts?............................................................................ 31
Jump to the Edge of the Data.................................................................................................. 31
Jump to Next Corner of Selection............................................................................................. 32
Ctrl+Backspace brings the Active Cell into View........................................................................ 32
Zoom with the Wheel Mouse................................................................................................... 33
Copy a Formula to All Data Rows............................................................................................. 33
Copy the Characters from a Cell Instead of Copying an Entire Cell.............................................. 33
A Faster Way To Paste Values.................................................................................................. 34
Quickly Turn a Range on Its Side............................................................................................. 35
Quickly Rearrange Rows Or Columns........................................................................................ 36
Quickly Copy Worksheets......................................................................................................... 37
Use Group Mode to Change All Worksheets............................................................................... 37
Find Text Numbers.................................................................................................................. 37
Why Can’t Excel Find a Number?.............................................................................................. 38
Mix Formatting In A Single Cell................................................................................................ 40
Enter a Series of Months, Days, or More by Using the Fill Handle............................................... 40
Have the Fill Handle Fill Your List of Part Numbers.................................................................... 42
Teach Excel to Fill A, B, C........................................................................................................ 43
Add Total to the End of Jan, Feb, ... Dec.................................................................................. 43
Put Date & Time in a Cell........................................................................................................ 44
Use Excel as a Word Processor................................................................................................ 45
Add Excel to Word.................................................................................................................. 45
Use Hyperlinks to Create an Opening Menu for a Workbook....................................................... 46
Spell check a Region............................................................................................................... 47
Stop Excel from AutoCorrecting Certain Words.......................................................................... 47
Use AutoCorrect to Enable a Shortcut....................................................................................... 48
Why Won’t the Track Changes Feature Work in Excel?............................................................... 49
Simultaneously Collaborate on a Workbook with Excel Web App................................................. 49
How to Print Titles at the Top of Each Page.............................................................................. 50
Print a Letter at the Top of Page 1 Repeat Headings at the Top of Each Subsequent Page........... 51
How to Print Page Numbers at the Bottom of Each Page........................................................... 51
How to Make a Wide Report Fit to One Page Wide by Many Pages Tall...................................... 52
Add a Printable Watermark..................................................................................................... 53
Print Multiple Ranges.............................................................................................................. 54
Add a Page Break at Each Change in Customer........................................................................ 54
Save My Worksheet as a PDF File............................................................................................ 55
Send an Excel File as an Attachment....................................................................................... 56
Save Excel Data as a Text File................................................................................................ 57
Close All Open Workbooks...................................................................................................... 58
I Just Closed an Unsaved Workbook........................................................................................ 58
Roll Back to an AutoSaved Version.......................................................................................... 60
Have Excel Talk to You........................................................................................................... 60
Enter Special Symbols............................................................................................................ 61
What Do All the Triangles Mean?............................................................................................ 61
Why does Excel Insert Cell Addresses When I Edit In a RefEdit Box?........................................ 63
F4 Repeats Last Command.................................................................................................... 63
Print all Excel Keyboard Shortcuts.......................................................................................... 64
Create a Personal Macro Workbook........................................................................................ 64
Macro to Toggle Positive to Negative...................................................................................... 65
Assign a Macro to a Shortcut Key........................................................................................... 66
Assign a Macro to a Toolbar Icon........................................................................................... 66
Use a Macro to Change Case to Upper, Lower, or Proper......................................................... 66
Get Free Excel Help.............................................................................................................. 67
Start a Formula with = or +.................................................................................................. 71
Three Methods of Entering Formulas..................................................................................... 71
Why Does Excel 2013 Look Like A Slot Machine?.................................................................... 73
Use Parentheses to Control the Order of Calculations.............................................................. 74
Long Formulas in the Formula Bar......................................................................................... 74
Copy a Formula That Contains Relative References................................................................. 75
Copy a Formula While Keeping One Reference Fixed............................................................... 76
Create a Multiplication Table.................................................................................................. 77
Calculate a Sales Commission................................................................................................ 78
Simplify the Entry of Dollar Signs in Formulas......................................................................... 79
Learn R1C1 Referencing to Understand Formula Copying........................................................ 80
Create Easier-to-Understand Formulas with Named Ranges..................................................... 82
See All Named Ranges at 39% Zoom..................................................................................... 83
Use Named Constants to Store Numbers................................................................................ 83
Total Without Using a Formula.............................................................................................. 84
Add or Multiply Two Columns Without Using Formulas............................................................ 85
Type 123 to Enter 1.23......................................................................................................... 86
Join Two Text Columns......................................................................................................... 86
Concatenate Several Cells..................................................................................................... 88
Join Text with a Date or Currency.......................................................................................... 88
Break Data Apart Using Flash Fill........................................................................................... 89
Parse Data using Text to Columns......................................................................................... 91
Excel Is Randomly Parsing Pasted Data................................................................................. 92
I Lose Leading Zeroes From CSV Files................................................................................... 93
Open CSV File With Dates in D/M/Y Format........................................................................... 93
Handle Dates in YYYYMMDD format...................................................................................... 94
My G/L Software Uses a Trailing Minus for Negative Numbers................................................. 94
Parse Data With Leader Lines............................................................................................... 94
Parse Multi-Line Cells........................................................................................................... 96
Change Smith, Jane to Jane Smith........................................................................................ 96
Convert Numbers to Text..................................................................................................... 97
Fill a Cell with Repeating Characters..................................................................................... 97
CLEAN Hasn’t Kept Up With The Times................................................................................. 98
Add the Worksheet Name as a Title...................................................................................... 98
Use AutoSum to Quickly Enter a Total Formula...................................................................... 99
AutoSum Doesn’t Always Predict My Data Correctly.............................................................. 100
Use the AutoSum Button to Enter Averages, Min, Max, and Count......................................... 102
Ditto The Formula Above.................................................................................................... 102
The Count Option of the AutoSum Dropdown Doesn’t Appear to Work................................... 103
Total the Red Cells............................................................................................................. 105
Automatically Number a List of Employees........................................................................... 106
Automatically Number the Visible Rows............................................................................... 107
Discover New Functions Using the fx Button........................................................................ 108
Get Help on Any Function While Entering a Formula............................................................. 108
Formula Autocomplete Is Cool,if You Can Stop Entering the Opening Parentheses.................. 109
Use F9 in the Formula Bar to Test a Formula....................................................................... 110
Quick Calculator................................................................................................................ 111
When Entering a Formula, You Get the Formula Instead of the Result................................... 112
Highlight All Formula Cells Using Conditional Formatting....................................................... 113
You Change a Cell in Excel but the Formulas Do Not Calculate.............................................. 113
Calculate One Range.......................................................................................................... 114
Why Use the Intersection Operator?.................................................................................... 115
Understand Implicit Intersection.......................................................................................... 118
Find the Longest Win Streak................................................................................................ 119
Add B5 On All Worksheets................................................................................................... 120
Consider Formula Speed...................................................................................................... 121
Exact Formula Copy............................................................................................................ 122
Calculate a Loan Payment................................................................................................... 124
Calculate Many Scenarios for Loan Payments....................................................................... 124
Back into an Answer Using Goal Seek.................................................................................. 125
Create an Amortization Table.............................................................................................. 125
Do 40 What-if Analyses Quickly........................................................................................... 127
Random Walk Down Wall Street.......................................................................................... 128
What-If For 3 Or More Variables.......................................................................................... 129
Rank Scores....................................................................................................................... 131
Rank a List Without Ties..................................................................................................... 132
Sorting with a Formula....................................................................................................... 132
Round Numbers................................................................................................................. 133
Round to the Nearest $0.05 with MROUND.......................................................................... 134
Round Prices to the Next Highest $5................................................................................... 134
Round 0.5 towards Even Per ASTM-E29.............................................................................. 134
Separate the Integer From the Decimals............................................................................. 135
Why Is This Price Showing $27.85000001 Cents?................................................................ 135
Calculate a Percentage of Total.......................................................................................... 136
Calculate a Running Percentage of Total............................................................................. 137
Use the ^ Sign for Exponents............................................................................................ 138
Raise a Number to a Fraction to Find the Square or Third Root............................................ 138
Calculate a Growth Rate.................................................................................................... 139
Find the Area of a Circle.................................................................................................... 140
Figure Out Lottery Probability............................................................................................ 141
Help Your Kids with Their Math.......................................................................................... 142
Convert Units................................................................................................................... 144
XOR Only Works Correctly for Two Values.......................................................................... 145
Find the Second Largest Value........................................................................................... 145
Format Every Other Row in Green...................................................................................... 145
\Use IF to Calculate a Bonus.............................................................................................. 148
IF with Two Conditions...................................................................................................... 148
Tiered Commission Plan with IF.......................................................................................... 150
Display Up/Down Arrows.................................................................................................... 151
Stop Showing Zeroes in Cell Links....................................................................................... 154
Count Records That Match a Criterion................................................................................. 155
Build a Table That Will Count by Criteria............................................................................. 156
Sum Records That Match a Criterion................................................................................... 156
Can the Results of a Formula Be Used in SUMIF?................................................................. 156
Calculate Based on Multiple Conditions................................................................................ 157
Avoid Errors Using IFERROR............................................................................................... 158
Use VLOOKUP to Join Two Tables....................................................................................... 159
Every VLOOKUP Ends in False............................................................................................ 160
Lookup Table Does Not Have to Be Sorted.......................................................................... 161
Beware of #N/A from VLOOKUP......................................................................................... 162
Add New Items to the Middle Of Your Lookup Table............................................................ 162
Consider Naming the Lookup Table.................................................................................... 162
Remove Leading and Trailing Spaces.................................................................................. 162
Your Lookup Table Can Go Across...................................................................................... 163
Copy a VLOOKUP Across Many Columns............................................................................. 164
INDEX Sounds Like an Inane Function................................................................................ 165
You Already Know MATCH, Really!..................................................................................... 165
INDEX Sounds Like an Inane Function - II.......................................................................... 165
VLOOKUP Left.................................................................................................................. 166
Fast Multi-Column VLOOKUP............................................................................................. 167
Speed Up Your VLOOKUP ................................................................................................ 168
Return the Next Larger Value in a Lookup......................................................................... 169
Two-Way Lookup............................................................................................................ 170
Combine Formulas into a Mega-Formula........................................................................... 170
Combine Two Lists Using VLOOKUP.................................................................................. 172
Watch for Duplicates When Using VLOOKUP..................................................................... 174
Return the Last Entry...................................................................................................... 174
Return the Last Matching Value....................................................................................... 176
Sum All of the Lookups................................................................................................... 177
Embed a Small Lookup Table In Formula......................................................................... 178
I Don’t Want to Use a Lookup Table to Choose One of Five Choices.................................. 179
Is there Something More Flexible than CHOOSE?............................................................. 180
Lookup Two Values........................................................................................................ 181
Add Comments to a Formula........................................................................................... 183
Create Random Numbers................................................................................................ 184
Randomly Sequence a List.............................................................................................. 185
Play Dice Games with Excel............................................................................................ 185
Generate Random Without Repeats................................................................................ 186
Calculate a Trendline Forecast........................................................................................ 186
Forecast Data with Seasonality....................................................................................... 188
Build a Model to Predict Sales Based on Multiple Regression............................................. 188
Switching Columns into Rows Using a Formula................................................................ 191
SUM a Range that is C5 Rows Tall Using OFFSET............................................................ 192
Replace Volatile OFFSET with INDEX.............................................................................. 194
How Can You Test for Volatility?.................................................................................... 195
Whatever Happened to the @@ Function?..................................................................... 195
Tables Are Like a Database in Excel............................................................................... 196
Dealing with Table Formulas......................................................................................... 197
Rename Your Tables.................................................................................................... 198
Charts , VLOOKUP & Pivots Expand With The Table........................................................ 198
Before Deleting a Cell, Find out if Other Cells Rely on It.................................................. 199
Calculate a Formula in Slow Motion............................................................................... 200
Which Cells Flow into This Cell?.................................................................................... 200
Color all Precedents or Dependents............................................................................... 200
Monitor Distant Cells.................................................................................................... 201
Auditing Worksheets with Inquire................................................................................. 202
Use Real Dates............................................................................................................ 203
How Can I Tell If Have Real Dates?............................................................................... 204
Convert Text Dates to Real Dates.................................................................................. 205
Format Dates............................................................................................................... 206
Format Dates As Quarters or Weeks.............................................................................. 207
Display Monthly Dates.................................................................................................. 207
Add a Column to Show Month or Weekday.................................................................... 208
Calculate First of Month............................................................................................... 208
Calculate the Last Day of the Month............................................................................. 209
Calculate Invoice Due Dates........................................................................................ 211
Calculate Receivable Aging.......................................................................................... 211
NOW, or TODAY?....................................................................................................... 212
Find the Last Sunday Of the Month.............................................................................. 213
Calculate Work Days................................................................................................... 215
Calculate Work Days for a Farmers Market................................................................... 216
Calculate Age in Years, Months, Days.......................................................................... 216
Coerce an Array of Dates from 2 Dates........................................................................ 218
Use Real Times.......................................................................................................... 219
Strangeness of Time Formatting.................................................................................. 220
Convert Time to Decimal Hours................................................................................... 221
Calculate with Time.................................................................................................... 221
Enter Minutes and Seconds......................................................................................... 222
Convert Text to Time.................................................................................................. 222
Can Excel Track Negative Time?.................................................................................. 223
Fill Blanks With Value Above....................................................................................... 224
See Formulas in Excel 2013........................................................................................ 225
Create a Bell Curve in Excel........................................................................................ 225
Change from Lower to Upper Case in Excel................................................................. 226
Spell Out Numbers in Excel........................................................................................ 227
Copy Macro Code from the Internet Into an Add-In..................................................... 227
Return Data from a Webservice in Excel 2013............................................................. 228
Add New Functions to Excel with Fast Excel SpeedTools Extra...................................... 229
=SUM(B1:B5) is Better Than =B1+B2+B3+B4+B5...................................................... 229
How to Set up Your Data for Easy Sorting and Subtotals.............................................. 233
How to Fit a Multiline Heading into One Cell................................................................ 233
No Tiny Blank Columns Between Columns................................................................... 234
How to Sort Data....................................................................................................... 235
Sort Days of the Week............................................................................................... 237
Sort a Report into a Custom Sequence........................................................................ 237
Sort All Red Cells to the Top of a Report..................................................................... 239
Sort Pictures With Data.............................................................................................. 240
Quickly Filter a List to Certain Records........................................................................ 240
Use Search While Filtering......................................................................................... 243
Filter by Selection..................................................................................................... 243
Use AutoSum After Filtering...................................................................................... 245
Filter Only Some Columns......................................................................................... 245
Find the Unique Values in a Column........................................................................... 246
Use Advanced Filter.................................................................................................. 247
Replace Multiple Filter Criteria with a Single Row of Formulas...................................... 248
Add Subtotals to a Data set....................................................................................... 249
Use Group & Outline Buttons to Collapse Subtotaled Data............................................ 251
Manually Apply Groups.............................................................................................. 251
Group Report Sections.............................................................................................. 252
Copy Just Totals from Subtotaled Data...................................................................... 253
Sort Largest Customers to the Top............................................................................ 254
Select 100 Columns in Subtotals................................................................................ 255
Enter a Grand Total of Data Manually Subtotaled....................................................... 255
Add Other Text to the Subtotal Lines........................................................................ 256
Subtotals by Product Within Region...........................................................................257
Format the Subtotal Rows.........................................................................................259
My Manager Wants a Blank Line After Each Subtotal...................................................260
Subtotal One Column and Count Another Column.......................................................261
Can You Get Medians?..............................................................................................262
Horizontal Subtotals.................................................................................................263
Be Wary..................................................................................................................264
Send Error Reports..................................................................................................264
Help Make Excel Better............................................................................................265
Remove Blank Rows from a Range...........................................................................265
Remove Blanks from a Range While Keeping the Original Sequence............................266
Double Space Your Data Set....................................................................................268
Use Find to Find an Asterisk.....................................................................................269
Use an Ampersand in a Header................................................................................270
Hide Zeros & Other Custom Number Formatting Tricks...............................................270
Use Consolidation to Combine Two Lists...................................................................272
Combine Four Quarterly Reports...............................................................................274
Find Total Sales by Customer by Combining Duplicates...............................................275
Remove Duplicates...................................................................................................277
Preview Remove Duplicates Without Removing Them.................................................278
Protect Cells That Contain Formulas...........................................................................280
Find Differences In Two Lists.....................................................................................281
Number Each Record, Starting at 1 for a New Customer..............................................284
Add a Group Number to Each Set of Records That Has a Unique Number......................285
Deal with Data in Which Each Record Takes Five Physical Rows....................................285
Add a Customer Number to Each Detail Record............................................................288
Use a Built-in Data Entry Form....................................................................................290
Cell AutoComplete Stopped Working............................................................................291
Data Cleansing with Flash Fill in Excel 2013..................................................................292
Flash Fill was there and is now Gone............................................................................292
Flash Fill Was Not Perfect............................................................................................293
Flash Fill Won’t Fill Numbers........................................................................................293
Flash Fill and Dates.....................................................................................................293
Flash Fill and Ambiguous Data.....................................................................................294
Use a Pivot Table to Summarize Detailed Data..............................................................294
Your Manager Wants Your Report Changed..................................................................296
Add or Remove Fields from an Existing Pivot Table.......................................................297
Summarize Pivot Table Data by Three Measures...........................................................297
Why Does the Pivot Table Field List Keep Disappearing?.................................................299
Move or Change Part of a Pivot Table............................................................................300
See Detail Behind One Number in a Pivot Table.............................................................301
Use Multiple Value Fields as a Column or Row Field........................................................301
Update Data Behind a Pivot able...................................................................................302
Why do I Get a Count Instead of a Sum?......................................................................302
Convert Your Data to a Table Before Adding Records.....................................................303
Create a Flattened Pivot Table for Reuse.......................................................................305
Replace Blanks in a Pivot Table with Zeros.....................................................................306
Collapse and Expand Pivot Fields...................................................................................307
Specify a Number Format for a Pivot Table Field............................................................308
Preserve Column Widths..............................................................................................308
Show Yes/No in a Pivot Table.......................................................................................310
Pivot Table Format Defaults.........................................................................................311
Format Pivot Tables with the Gallery............................................................................311
None of the 46,273 Built-In Styles Do What My Manager Asks For.................................313
Select Pivot Table Parts For Formatting........................................................................314
Apply Conditional Formatting to a Pivot Table...............................................................316
Can I Save Formatting in a Template?..........................................................................317
Manually Re-sequence the Order of Data in a Pivot Table..............................................320
Present a Pivot Table in High-to-Low Order by Revenue................................................321
Excel 2016 Sometimes Auto-Groups Daily Dates to Month.............................................322
Group Daily Dates by Month in a Pivot Table................................................................323
Create a Year-Over-Year Report..................................................................................325
Group by Week in a Pivot Table...................................................................................326
Limit a Pivot Report to Show Just the Top 5 Customers.................................................327
Build a Better Top Five Using Groups...........................................................................328
Build a Better Top Five with A Filter Hack.....................................................................329
Build a Better Top 5 Using the Data Model....................................................................331
Limit a Report to Just One Region................................................................................332
Create an Ad-Hoc Reporting Tool.................................................................................333
Create a Report for Every Customer.............................................................................333
Create Pivot Charts.....................................................................................................334
Add Visual Filters to a Pivot Table or Regular able.........................................................335
Run Many Pivot Tables From one Slicer........................................................................337
Filter Dates Using a Timeline in Excel 2013 & Newer.....................................................338
Group Employees Into Age Bands................................................................................339
Create a Frequency Distribution...................................................................................339
Grouping 1 Pivot Table Groups Them All.......................................................................341
Reduce Size 50% Before Sending.................................................................................343
Drag Fields to the Pivot Table.......................................................................................345
Create a Report That Shows Count, Min, Max, Average, Etc............................................346
Better Calculations with Show Values As........................................................................347
Pivot Ranks Don’t Match RANK()....................................................................................349
Calculated Fields in a Pivot Table...................................................................................350
Add a Calculated Item to Group Items in a Pivot Table....................................................351
Group Text Fields to Build Territories..............................................................................353
Calculations Outside of Pivot Tables...............................................................................355
Show Customer Account & Name...................................................................................358
Show Months with Zero Sales........................................................................................360
Create a Unique List of Customers with a Pivot Table......................................................361
Use a Pivot Table to Compare Two Lists.........................................................................362
Use a Pivot Table When There Is No Numeric Data..........................................................363
Fix Misspelled Customer Names......................................................................................364
Create a Pivot Table from Access Data............................................................................365
What Happened to Multiple Consolidation Ranges in Pivot Tables?....................................365
What are the Products in Power BI And How Can I Get Them?.........................................367
Know if You Have 32-Bit or 64-Bit Excel..........................................................................367
Load and Clean Data with Power Query..........................................................................368
Power Query is Easier to Learn than VBA Macros............................................................368
I Have More than 1,048,576 Rows of Data.....................................................................368
Load a List of File Names into Excel................................................................................369
Load a Folder of CSV Files into a Single Excel Worksheet.................................................369
My Headings Repeat Every 60 Rows...............................................................................370
Fill in Blanks with Values from Above.............................................................................371
Use Power Query to Clean Data Already in Excel.............................................................371
Pivot from Multiple Tables in Excel 2013 Data Model.......................................................372
Reporting from the Smaller Side of the Relationship........................................................373
Use Joiner Tables Between Tables.................................................................................374
Five Reasons to Use Power Pivot...................................................................................375
Why Isn’t Power Pivot Tab in the Ribbon?......................................................................375
Get Excel Data Into Power Pivot....................................................................................375
Open the Power Pivot Window.......................................................................................376
Define Relationships Between Tables..............................................................................376
Sort Month Name by Month Number...............................................................................377
Create a Calendar Table.................................................................................................378
The Formulas are called DAX..........................................................................................378
Adding Calculations In the Power Pivot Grid.....................................................................378
Refer to a Related Table in a Formula..............................................................................379
Creating the Power Pivot Table.......................................................................................380
Building the Pivot Table..................................................................................................380
Feature X Won’t Work in Power Pivot...............................................................................380
Replace Calculated Fields with DAX..................................................................................381
Calculate() is Like SUMIFS()............................................................................................382
Unapply a Filter Using DAX.............................................................................................382
Unfilter Using Time Intelligence.......................................................................................384
Convert Power Pivot to Formulas.....................................................................................384
January Actuals and February Plan..................................................................................384
Power View Is Replaced with Power BI............................................................................386
Power BI Transforms Excel Data into Interactive Dashboards............................................386
First POwer BI Step: Get a Free Account for Power BI......................................................386
Optional Step 1: Take the Sample Dashboards for a Spin..................................................387
Use Q&A to Investigate Your Data...................................................................................388
Click a Dashboard Element to Open the Underlying Report................................................389
Click in one Chart to Filter Other Charts...........................................................................389
Enable Drill-Down Mode.................................................................................................390
Sorting is Hidden Behind More Options............................................................................391
Optional Step 2: Connect to an Existing Service...............................................................391
Download Power BI Desktop to Your Computer...............................................................392
Prepare Your Excel Data for Power BI.............................................................................392
Add a Calendar Table.....................................................................................................393
Three Icons in Power BI Desktop.....................................................................................394
In Power BI Desktop, the Power Query Tools Are Called Get Data......................................394
Load Your Excel Data to Power BI Desktop.......................................................................395
In Power BI Desktop, the Power Pivot tools are called Modeling.........................................396
Define Relationships in Power BI Desktop.........................................................................396
Classify Your Geography Fields in Power BI Desktop..........................................................397
Prevent Power BI from Adding Up Year Fields...................................................................398
Sort This by That............................................................................................................399
Define Synonyms in Power BI Desktop.............................................................................399
Hide Columns So They Can't Be Chosen...........................................................................400
Three Ways to BUild Visualizations in a Report.................................................................401
FOrmatting a Chart........................................................................................................402
Add Lines using Analytics...............................................................................................403
Adding More Visualizations to a Page..............................................................................403
It is Insanely Easy to Build a Hierarchy...........................................................................403
Open Source Visualizations............................................................................................404
Every Chart is a Slicer For Other Charts..........................................................................406
A Report Can Have Multiple Pages.................................................................................406
Using Maps in Power BI Desktop...................................................................................406
Three Types of Filters..................................................................................................407
Where is the Top 10 Filter?..........................................................................................407
Add Titles, Logos, & Embelishments.............................................................................408
Don't Forget to Save Your File!.....................................................................................408
Share Your Dashboard to Power BI in the Cloud............................................................408
Use Quick Insights......................................................................................................409
View Your Report in a Browser.....................................................................................409
Q&A Works on Dashboards, Not Reports.......................................................................409
Share a Dashboard With Your Manager.........................................................................410
Wait a Second - a Sharing Link? Isn't this Dangerous?....................................................411
Consume Your Dashboard Using Power BI Mobile...........................................................411
What Does All Of This Cost?..........................................................................................411
How Do I Update My Dashboard Every Day?..................................................................412
That's All For Power BI In This Book..............................................................................412
Put a Pivot Table on a Map In Excel 2013 Power Map......................................................412
Tricks for Navigating the Map in Power Map...................................................................413
Fine-Tuning Power Map.................................................................................................413
Creating a Video from Power Map...................................................................................414
Use an Alternate Map for Power Map..............................................................................415
Filtering in Power Map...................................................................................................416
Excel Data to Mailing Labels in Word..............................................................................416
Excel 2013 Allows Slicers on Regular Tables...................................................................417
Use Power Query to Load Many Web Pages...................................................................418
Create a Chart with One Click........................................................................................423
Teach Excel Your Favorite Chart.....................................................................................423
Move a Chart.................................................................................................................424
Copy a Chart Detached from the Data.............................................................................425
Add New Data to a Chart................................................................................................426
Excel 2013 Offers Easy Chart Formatting .........................................................................427
Begin Excel 2010 Formatting on Design............................................................................428
The Chart Layout Tab Is Missing in Excel 2013..................................................................428
Formatting Charts in Excel 2010 with Layout.....................................................................429
Legend At the Top..........................................................................................................430
The 2010 Format Dialog Box is a Task Pane in 2013.........................................................430
Display an Axis in Millions................................................................................................431
Select Anything on a Chart to Format...............................................................................432
The Format Dialog Box Offers a New Trick........................................................................433
Use Meaningful Chart Titles.............................................................................................433
Avoid 3-D Chart Types....................................................................................................434
Prevent the Drop to Zero................................................................................................435
Explode One Slice of the Pie............................................................................................436
Move Small Pie Slices To Second Chart.............................................................................437
Add a Trendline to a Chart..............................................................................................438
See Detail on Large & Small Data Points..........................................................................439
Chart Two Series with Differing Orders of Magnitude........................................................440
Hide Subtotals From Chart in Excel 2013.........................................................................442
Create Pivot Charts from Detail Data in Excel 2013...........................................................443
Use Formulas for Chart Labels in Excel 2013....................................................................444
Interactive Chart to Show One Customer.........................................................................444
Tie the Chart Title to a Cell.............................................................................................446
Excel 2016 Adds a Waterfall Chart..................................................................................446
Use an Invisible Series to Float Columns..........................................................................447
Use Rogue Series for Shading.........................................................................................449
Two Stacked, One Clustered Column...............................................................................451
Conditional Format a Chart.............................................................................................452
Scatter Charts are Versatile But Require a Different Workflow...........................................454
When do I Use Which Chart Type?..................................................................................459
Track Sales Leads with a Funnel Chart.............................................................................461
Create Tiny Charts with Sparklines...................................................................................462
Sparklines Are Not Scaled Together.................................................................................463
What is the Win Loss Sparkline For?................................................................................464
Labeling Sparklines.........................................................................................................465
Shade the Normal Range in a Sparkline...........................................................................466
Convert a Table of Numbers to a Visualization..................................................................466
Control Values for Each Icon............................................................................................469
Add Icons to Only the Good Cells.....................................................................................470
Use the SIGN Function for Up/Flat/Down Icon Set.............................................................470
Data Bars Options...........................................................................................................471
Comparative Histogram...................................................................................................472
Select Every Kid in Lake Wobegon....................................................................................472
There is a Font Optimized for Excel...................................................................................472
Show Checkmarks in Excel...............................................................................................473
Add Bullets to Excel.........................................................................................................473
Use the Border Tab in Format Cells...................................................................................474
Remove Borders from Filled Cells......................................................................................476
Double Underline a Grand Total........................................................................................476
Why Did the Colors Change in Excel 2013?........................................................................477
Where Are My Excel 2003 Colors?.....................................................................................478
Transform Black-and-White Spreadsheets to Color by Using a Table...................................478
Fit a Slightly Too-Large Value in a Cell..............................................................................479
Turn Off Wrap Text in Pasted Data...................................................................................480
Delete All Pictures in Pasted Data.....................................................................................480
Prevent Long Text from Spilling.......................................................................................481
Show Two Values in a Split Cell.......................................................................................482
For Each Cell in Column A, Have Three Rows in Column B.................................................482
Show Results as Fractions...............................................................................................483
Better Scientific Notation................................................................................................484
Fill a Cell with Asterisks..................................................................................................485
I type 152 and get 1.52..................................................................................................485
Use Cell Styles to Change Formats...................................................................................486
Add Your Own Styles......................................................................................................487
Share Styles Between Workbooks....................................................................................487
Move Columns by Sorting Left to Right............................................................................488
Move Columns Using Insert Cut Cells...............................................................................489
Move Rows or Columns with Shift Drag............................................................................489
Select All Cells Using the Keyboard...................................................................................489
Change the Width of All Columns with One Command.......................................................490
Copy Column Widths to a New Range..............................................................................490
Copy Row Heights...........................................................................................................491
Use White Text to Hide Data............................................................................................491
Hide Values Using a Number Format.................................................................................492
Hide and Unhide Data......................................................................................................492
Group Columns Instead of Hiding Them............................................................................493
Hide Error Cells When Printing.........................................................................................494
Unhide All Sheets...........................................................................................................494
Very Hide a Worksheet...................................................................................................495
Organize Your Worksheet Tabs with Color........................................................................495
Copy Formatting to a New Range....................................................................................496
Copy Without Changing Borders......................................................................................497
Power Up Format Painter................................................................................................498
Fill Formatting................................................................................................................498
Change All Red Font Cells to Blue Font.............................................................................499
Replace Partially Bold Cells..............................................................................................500
Change the Look of Your Workbook with Document Themes.............................................501
Create Your Own Theme.................................................................................................502
Bring Back the Office 2010 Colors And Shiny Objects........................................................503
Change the Background of a Worksheet...........................................................................504
Add a Printable Background to a Worksheet.....................................................................504
Remove Hyperlinks Automatically Inserted by Excel..........................................................505
Select a Hyperlink Cell Without Following the Hyperlink.....................................................505
Pasted URLs Don’t Become Hyperlinks.............................................................................506
Debug Using a Printed Spreadsheet.................................................................................506
Leave Helpful Notes with Cell Comments..........................................................................507
Change the Appearance of Cell Comments........................................................................508
Control How Your Name Appears in Comments..................................................................510
Force Some Comments to ALWAYS Be Visible to Provide a Help System...............................510
Change the Comment Shape to a Star...............................................................................511
Add a Pop-up Picture of an Item in a Cell...........................................................................513
Add a Pop-up Picture to Multiple Cells................................................................................513
Build Reports Where Columns in Each Section 1 Don’t Line Up.............................................514
Paste a Live Picture of a Cell..............................................................................................515
Add Formatting to Pictures in Excel....................................................................................516
Remove Picture Background..............................................................................................517
Inserting a Screen Clipping................................................................................................517
Draw an Arrow to Visually Illustrate That Two Cells Are Connected.......................................518
Add Connectors to Join Shapes...........................................................................................519
Circle a Cell on Your Worksheet..........................................................................................520
Draw Perfect Circles...........................................................................................................521
Add Text to Any Closed Shape............................................................................................522
Place Cell Contents in a Shape............................................................................................522
Rotate a Shape..................................................................................................................523
Create Dozens of Lightning Bolts.........................................................................................524
Make a Logo into a Shape...................................................................................................525
Draw Business Diagrams with Excel.....................................................................................527
Choose the Right Type of SmartArt......................................................................................528
Use the Text Pane to Build SmartArt....................................................................................529
Change a SmartArt Layout..................................................................................................530
Format SmartArt................................................................................................................531
Switch to the Format Tab to Format Individual Shapes.........................................................533
Use Cell Values as the Source for SmartArt Content..............................................................534
Add WordArt to a Worksheet..............................................................................................536
Chart and SmartArt Text Is Automatically WordArt...............................................................536
Excel 2013 Offers an Excel App Store..................................................................................537
Add a Dropdown to a Cell...................................................................................................537
Configure Validation to “Ease up”........................................................................................538
Use Validation to Prevent Duplicate Data Entry....................................................................540
Use Validation to Create Dependent Lists............................................................................540
Add a ToolTip to a Cell to Guide the Person Using the Workbook..........................................542
Combine Validation with AutoComplete...............................................................................542
Afterword.........................................................................................................................543
Index...............................................................................................................................544
