Excel 2019 Bible by Michael Alexander, Richard Kusleika, John Walkenbach

ExcelÂŽ 2019

BIBLE Michael Alexander Dick Kusleika Previously by John Walkenbach

ExcelŽ 2019 Bible Published by John Wiley & Sons, Inc. 10475 Crosspoint Boulevard Indianapolis, IN 46256 www.wiley.com Copyright Š 2019 by John Wiley & Sons, Inc., Indianapolis, Indiana Published simultaneously in Canada ISBN: 978-1-119-51478-7 ISBN: 978-1-119-51477-0 (ebk) ISBN: 978-1-119-51476-3 (ebk) Manufactured in the United States of America 10 9 8 7 6 5 4 3 2 1 No part of this publication may be reproduced, stored in a retrieval system or transmitted in any form or by any means, electronic, mechanical, photocopying, recording, scanning or otherwise, except as permitted under Sections 107 or 108 of the 1976 United States Copyright Act, without either the prior written permission of the Publisher, or authorization through payment of the appropriate per-copy fee to the Copyright Clearance Center, 222 Rosewood Drive, Danvers, MA 01923, (978) 750-8400, fax (978) 646-8600. Requests to the Publisher for permission should be addressed to the Permissions Department, John Wiley & Sons, Inc., 111 River Street, Hoboken, NJ 07030, (201) 748-6011, fax (201) 748-6008, or online at http://www.wiley.com/go/ permissions.

Limit of Liability/Disclaimer of Warranty: The publisher and the author make no representations or warranties with respect to the accuracy or completeness of the contents of this work and specifically disclaim all warranties, including without limitation warranties of fitness for a particular purpose. No warranty may be created or extended by sales or promotional materials. The advice and strategies contained herein may not be suitable for every situation. This work is sold with the understanding that the publisher is not engaged in rendering legal, accounting, or other professional services. If professional assistance is required, the services of a competent professional person should be sought. Neither the publisher nor the author shall be liable for damages arising herefrom. The fact that an organization or Web site is referred to in this work as a citation and/ or a potential source of further information does not mean that the author or the publisher endorses the information the organization or website may provide or recommendations it may make. Further, readers should be aware that Internet websites listed in this work may have changed or disappeared between when this work was written and when it is read.

For general information on our other products and services please contact our Customer Care Department within the United States at (877) 762-2974, outside the United States at (317) 572-3993 or fax (317) 572-4002.

Wiley publishes in a variety of print and electronic formats and by print-on-demand. Some material included with standard print versions of this book may not be included in e-books or in print-on-demand. If this book refers to media such as a CD or DVD that is not included in the version you purchased, you may download this material at http://booksupport.wiley.com. For more information about Wiley products, visit www.wiley .com.

Library of Congress Control Number: 2018954472 Trademarks: Wiley and the Wiley logo are trademarks or registered trademarks of John Wiley & Sons, Inc. and/or its affiliates, in the United States and other countries, and may not be used without written permission.

Microsoft and Excel are registered trademarks of Microsoft Corporation. All other trademarks are the property of their respective owners. John Wiley & Sons, Inc. is not associated with any product or vendor mentioned in this book.

About the Authors Michael Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Microsoft Excel. He has more than 20 years of experience consulting and developing Microsoft Office solutions.

Mike has been named a Microsoft MVP for his ongoing contributions to the Excel community. You can find Mike at www.datapigtechnologies.com.

Dick Kusleika is a 12-time Microsoft Excel MVP and has been working with Microsoft Office for more than 20 years. Dick develops Access- and Excel-based solutions for his clients and has conducted training seminars on Office products in the United States and Australia. Dick also writes a popular Excel-related blog at www.dailydoseofexcel.com.

John Walkenbach is a best-selling Excel author who has published more than 50 spreadsheet books. He lives amid the saguaros, javelinas, rattlesnakes, bobcats, and Gila monsters in Southern Arizona, but the critters are mostly scared away by his clawhammer banjo playing. For more information, Google him.

We are making a service for people who want a MS office 365 student A1 account with lifetime licence. If you are interested on this service check the link: https://www.fiverr.com/share/emV5Ek If you want this ebook just contact us in the previous link and you will get it with your MS accounts for 5 dollars (both book+ MS account).

Note: I can’t share all the ebook here on this website, because of Copyright so I will share with you just the summary of the content.

About the Technical Editors Jordan Goldmeier is an internationally recognized analytics professional and data visualization expert, author, speaker, and CEO. He is the owner of Cambia Factor, a data consulting agency, and the author of Advanced Excel Essentials (Apress, 2014) and co-author of Dashboards for Excel (Apress, 2015), both on developing advanced analytics with Excel.

He has consulted with and provided training for the NATO Training Mission, Pentagon, Air Force and Navy, Financial Times, and the University of Cincinnati, among others. His work has been cited by and quoted in the Associated Press, Bloomberg BusinessWeek, Dice News, and American Express OPEN Forum. He holds the prestigious MVP award from Microsoft since 2013, and he is an owner and producer of Excel.TV, an online community devoted to sharing stories, setbacks, and lessons from experts and practitioners in Excel, business intelligence, big data, and analytics. He has provided technical editing and review for Wiley, O’Reilly, and others.

Joyce J. Nielsen has worked in the publishing industry for more than 25 years as an author, development editor, technical editor, and project manager specializing in Microsoft Office, Windows, Internet, and general technology titles for leading educational and retail publishers. She is the author or co-author of more than 40 computer books and has edited several hundred IT publications and more than 2,000 online articles.

Joyce holds a bachelor of science degree in quantitative business analysis from Indiana University’s Kelley School of Business in Bloomington. Joyce currently resides in Arizona.

Doug Holland is an architect evangelist at Microsoft Corporation, working with partners to drive digital transformation through technologies such as the Microsoft Cloud, Office 365, and HoloLens. He holds a master’s degree in software engineering from Oxford University and lives in Northern California with his wife and five children.

Guy Hart-Davis is the author of an improbable number of computer books on a bizarre range of topics. If you had been wondering who was responsible for the Word 2000 Developer’s Handbook, AppleScript: A Beginner’s Guide, iMac Portable Genius, or Samsung Galaxy S8 Maniac’s Guide, you need wonder no more.

Credits Project Editor Gary Schwartz

Marketing Manager Christie Hilbrich

Technical Editors Jordan Goldmeier Joyce J. Nielsen Doug Holland Guy Hart-Davis

Associate Publisher Jim Minatel

Production Editor Barath Kumar Rajasekaran

Proofreader Amy J. Schneider

Copy Editor Kim Wimpsett

Indexer John Sleeva

Production Manager Kathleen Wisor

Cover Designer Wiley

Content Enablement and Operations Manager Pete Gaughan

Cover Image iStockphoto.com/Aleksandar

Project Coordinator, Cover Brent Savage

Acknowledgments ur deepest thanks to the professionals at John Wiley & Sons for all the hours of work put into bringing this book to life. Thanks also to Jordan Goldmeier, Joyce Nielsen, Doug Holland, and Guy Hart-Davis for suggesting numerous improvements to the examples and text in this book. A special thank-you goes out to our families for putting up with all the time spent locked away on this project. Finally, we’d like to thank John Walkenbach for his many years of work on the previous editions of this book. His efforts in curating Excel knowledge have been instrumental, not only in helping millions of Excel users to achieve their learning goals but in inspiring countless Excel MVPs to share their expertise with the Excel community.

O

ix

Contents at a Glance Introduction ...................................................................................... xli

Part I: Getting Started with Excel ......................................................... 1 Chapter 1: Introducing Excel ..................................................................... 3 Chapter 2: Entering and Editing Worksheet Data ............................................ 29 Chapter 3: Performing Basic Worksheet Operations ........................................ 51 Chapter 4: Working with Excel Ranges and Tables........................................... 71 Chapter 5: Formatting Worksheets ........................................................... 117 Chapter 6: Understanding Excel Files and Templates ...................................... 153 Chapter 7: Printing Your Work ................................................................ 173 Chapter 8: Customizing the Excel User Interface ........................................... 195

Part II: Working with Formulas and Functions .................................. 205

Chapter 9: Introducing Formulas and Functions ......................................... 207 Chapter 10: Using Formulas for Common Mathematical Operations ...................... 239 Chapter 11: Using Formulas to Manipulate Text ............................................ 253 Chapter 12: Using Formulas with Dates and Times ...................................... 269 Chapter 13: Using Formulas for Conditional Analysis .................................... 295 Chapter 14: Using Formulas for Matching and Lookups .................................... 315 Chapter 15: Using Formulas for Financial Analysis....................................... 333 Chapter 16: Using Formulas for Statistical Analysis ..................................... 365 Chapter 17: Using Formulas with Tables and Conditional Formatting ................... 385 Chapter 18: Understanding and Using Array Formulas ..................................... 399 Chapter 19: Making Your Formulas Error-Free .............................................. 423

Part III: Creating Charts and Other Visualizations ............................ 443 Chapter 20: Getting Started with Excel Charts .......................................... 445 Chapter 21: Using Advanced Charting Techniques ......................................... 489 Chapter 22: Creating Sparkline Graphics .................................................... 523 Chapter 23: Visualizing with Custom Number Formats and Shapes....................... 537 Chapter 24: Implementing Excel Dashboarding Best Practices .......................... 567

xi

Part IV: Managing and Analyzing Data ............................................. 577

Chapter 25: Importing and Cleaning Data ....................................................579 Chapter 26: Using Data Validation ............................................................613 Chapter 27: Creating and Using Worksheet Outlines ....................................... 627 Chapter 28: Linking and Consolidating Worksheets .........................................637 Chapter 29: Introducing PivotTables......................................................... 655 Chapter 30: Analyzing Data with PivotTables .............................................. 679 Chapter 31: Performing Spreadsheet What-If Analysis ................................... 715 Chapter 32: Analyzing Data Using Goal Seeking and Solver ............................... 733 Chapter 33: Analyzing Data with the Analysis ToolPak .................................... 755 Chapter 34: Protecting Your Work ............................................................769

Part V: Understanding Power Pivot and Power Query ...................... 781 Chapter 35: Introducing Power Pivot ........................................................ 783 Chapter 36: Working Directly with the Internal Data Model .............................. 807 Chapter 37: Adding Formulas to Power Pivot ................................................817 Chapter 38: Introducing Power Query..................................................... 835 Chapter 39: Transforming Data with Power Query .......................................859 Chapter 40: Making Queries Work Together ................................................ 887 Chapter 41: Enhancing Power Query Productivity........................................903

Part VI: Automating Excel................................................................. 915

Chapter 42: Introducing Visual Basic for Applications ......................................917 Chapter 43: Creating Custom Worksheet Functions ...................................... 947 Chapter 44: Creating UserForms...............................................................961 Chapter 45: Using UserForm Controls in a Worksheet ...................................... 983 Chapter 46: Working with Excel Events ...................................................... 997 Chapter 47: Seeing Some VBA Examples .................................................... 1011 Chapter 48: Creating Custom Excel Add-Ins................................................ 1027 Index ............................................................................................ 1037

xii

Contents Introduction

xli

Part I: Getting Started with Excel

1

Chapter 1: Introducing Excel

3

Understanding What Excel Is Used For ................................................... 3 Looking at What’s New in Excel 2019 ....................................................... 4 Understanding Workbooks and Worksheets .............................................. 4 Moving around a Worksheet................................................................ 7 Navigating with your keyboard ..................................................... 8 Navigating with your mouse ......................................................... 9 Using the Ribbon .......................................................................... 10 Ribbon tabs.......................................................................... 10 Contextual tabs ..................................................................... 12 Types of commands on the Ribbon ............................................... 12 Accessing the Ribbon by using your keyboard .................................. 14 Using Shortcut Menus ..................................................................... 16 Customizing Your Quick Access Toolbar................................................ 17 Working with Dialog Boxes ............................................................... 18 Navigating dialog boxes ........................................................... 19 Using tabbed dialog boxes......................................................... 20 Using Task Panes .......................................................................... 20 Creating Your First Excel Workbook .................................................... 22 Getting started on your worksheet .............................................. 22 Filling in the month names........................................................ 23 Entering the sales data ............................................................ 23 Formatting the numbers........................................................... 24 Making your worksheet look a bit fancier ..................................... 25 Summing the values ................................................................ 26 Creating a chart ................................................................ 26 Printing your worksheet ........................................................... 26 Saving your workbook .............................................................. 27

Chapter 2: Entering and Editing Worksheet Data

29

Exploring Data Types ..................................................................... 29 Numeric values ..................................................................... 29

xiii

Contents

Text entries ..................................................................... 30 Formulas ............................................................................. 31 Entering Text and Values into Your Worksheets ....................................... 31 Entering numbers ................................................................... 31 Entering text ........................................................................ 32 Using Enter mode ................................................................... 33 Entering Dates and Times into Your Worksheets ....................................... 33 Entering date values ................................................................ 33 Entering time values................................................................ 34 Modifying Cell Contents .................................................................. 34 Deleting the contents of a cell .................................................... 34 Replacing the contents of a cell .................................................. 35 Editing the contents of a cell ...................................................... 35 Learning some handy data-entry techniques ................................... 37 Automatically moving the selection after entering data ................ 37 Selecting a range of input cells before entering data .................... 37 Using Ctrl+Enter to place information into multiple cells simultaneously ............................................................. 37 Changing modes .............................................................. 38 Entering decimal points automatically .................................... 38 Using AutoFill to enter a series of values .................................. 39 Using AutoComplete to automate data entry ........................... 40 Forcing text to appear on a new line within a cell ....................... 41 Using AutoCorrect for shorthand data entry ............................ 41 Entering numbers with fractions ........................................ 42 Using a form for data entry .............................................. 42 Entering the current date or time into a cell ............................. 44 Applying Number Formatting......................................................... 44 Using automatic number formatting........................................... 46 Formatting numbers by using the Ribbon ........................................ 46 Using shortcut keys to format numbers .......................................... 46 Formatting numbers by using the Format Cells dialog box .................... 48 Adding your own custom number formats ....................................... 50

Chapter 3: Performing Basic Worksheet Operations

51

Learning the Fundamentals of Excel Worksheets...................................... 51 Working with Excel windows ................................................... 51 Moving and resizing windows ............................................ 52 Switching among windows ............................................... 53 Closing windows .......................................................... 54 Activating a worksheet............................................................. 54 Adding a new worksheet to your workbook ...................................... 55 Deleting a worksheet you no longer need........................................ 55 Changing the name of a worksheet ............................................... 56 Changing a sheet tab color ........................................................ 57 Rearranging your worksheets ..................................................... 57 Hiding and unhiding a worksheet ................................................. 58

xiv

Contents

Controlling the Worksheet View......................................................... 59 Zooming in or out for a better view ........................................... 59 Viewing a worksheet in multiple windows .................................... 60 Comparing sheets side by side .................................................... 61 Splitting the worksheet window into panes .................................... 62 Keeping the titles in view by freezing panes ................................... 62 Monitoring cells with a Watch Window .......................................... 65 Working with Rows and Columns ........................................................ 65 Inserting rows and columns ....................................................... 66 Deleting rows and columns ........................................................ 67 Changing column widths and row heights ....................................... 67 Changing column widths ................................................. 68 Changing row heights........................................................ 68 Hiding rows and columns .......................................................... 69

Chapter 4: Working with Excel Ranges and Tables

71

Understanding Cells and Ranges ........................................................ 71 Selecting ranges .................................................................... 72 Selecting complete rows and columns ........................................... 73 Selecting noncontiguous ranges .................................................. 73 Selecting multisheet ranges ...................................................... 74 Selecting special types of cells................................................... 77 Selecting cells by searching....................................................... 79 Copying or Moving Ranges ............................................................... 81 Copying by using Ribbon commands ............................................. 82 Copying by using shortcut menu commands .................................... 83 Copying by using shortcut keys ................................................... 83 Copying or moving by using drag-and-drop ..................................... 85 Copying to adjacent cells ......................................................... 86 Copying a range to other sheets .................................................. 87 Using the Office Clipboard to paste.............................................. 87 Pasting in special ways ............................................................ 89 Using the Paste Special dialog box ............................................... 90 Performing mathematical operations without formulas ................ 92 Skipping blanks when pasting ........................................... 92 Transposing a range ......................................................... 92 Using Names to Work with Ranges ...................................................... 93 Creating range names in your workbooks........................................ 94 Using the Name box ......................................................... 94 Using the New Name dialog box............................................ 94 Using the Create Names from Selection dialog box ...................... 95 Managing names .................................................................... 97 Adding Comments to Cells ............................................................... 98 Formatting comments ............................................................. 99 Changing a comment’s shape ...................................................... 100

xv

Contents

Reading comments ................................................................ 101 Hiding and showing comments .................................................. 101 Editing comments ................................................................. 101 Deleting comments ............................................................... 102 Working with Tables ..................................................................... 102 Understanding a table’s structure ............................................102 The header row ............................................................. 103 The data body .............................................................. 103 The total row ............................................................... 103 The resizing handle ........................................................ 104 Creating a table ................................................................... 104 Adding data to a table ........................................................... 105 Sorting and filtering table data ................................................. 105 Sorting a table .............................................................. 106 Filtering a table ............................................................ 108 Filtering a table with slicers ............................................... 110 Changing the table’s appearance ................................................112

Chapter 5: Formatting Worksheets

117

Getting to Know the Formatting Tools................................................. 117 Using the formatting tools on the Home tab ................................. 118 Using the Mini toolbar .............................................................118 Using the Format Cells dialog box ...............................................119 Formatting Your Worksheet ............................................................ 120 Using fonts to format your worksheet .......................................... 120 Changing text alignment ......................................................... 122 Choosing horizontal alignment options .................................. 123 Choosing vertical alignment options .................................... 125 Wrapping or shrinking text to fit the cell ............................... 125 Merging worksheet cells to create additional text space .............. 125 Displaying text at an angle................................................ 127 Using colors and shading ...................................................... 127 Adding borders and lines ......................................................128 Using Conditional Formatting ....................................................... 131 Specifying conditional formatting............................................ 131 Using graphical conditional formats ............................................ 131 Using data bars ............................................................. 131 Using color scales........................................................... 133 Using icon sets .............................................................. 134 Creating formula-based rules ................................................... 137 Understanding relative and absolute references ....................... 137 Conditional formatting formula examples..................................... 138 Identifying weekend days ................................................. 139

xvi

Contents

Highlighting a row based on a value ...................................... 139 Displaying alternate-row shading ...................................... 140 Creating checkerboard shading ........................................ 140 Shading groups of rows ..................................................... 141 Working with conditional formats ............................................... 141 Managing rules .............................................................. 142 Copying cells that contain conditional formatting .................... 142 Deleting conditional formatting ....................................... 142 Locating cells that contain conditional formatting ................... 142 Using Named Styles for Easier Formatting ......................................... 143 Applying styles................................................................. 143 Modifying an existing style ................................................... 144 Creating new styles ........................................................... 145 Merging styles from other workbooks ........................................... 146 Controlling styles with templates ............................................... 146 Understanding Document Themes ..................................................... 147 Applying a theme .................................................................. 148 Customizing a theme ............................................................. 149

Chapter 6: Understanding Excel Files and Templates

153

Creating a New Workbook ............................................................... 153 Opening an Existing Workbook ......................................................... 154 Filtering filenames ................................................................ 156 Choosing your file display preferences ......................................... 157 Saving a Workbook ....................................................................... 157 Using AutoRecover ....................................................................... 159 Recovering versions of the current workbook ................................. 159 Recovering unsaved work ........................................................ 160 Configuring AutoRecover ......................................................... 160 Password-Protecting a Workbook ...................................................... 161 Organizing Your Files .................................................................... 162 Other Workbook Info Options ........................................................... 162 Protect Workbook options ........................................................ 162 Check for Issues options .......................................................... 163 Manage Workbook options ........................................................ 163 Browser View options ............................................................. 163 Compatibility Mode section ................................................... 164 Closing Workbooks ....................................................................... 164 Safeguarding Your Work ................................................................. 164 Working with Templates................................................................. 165 Exploring Excel templates ........................................................ 165 Viewing templates .......................................................... 165 Creating a workbook from a template.................................... 166 Modifying a template....................................................... 168

xvii

Contents

Using default templates .......................................................... 168 Using the workbook template to change workbook defaults............169 Creating a worksheet template............................................170 Editing your template.......................................................170 Resetting the default workbook ...........................................170 Using custom workbook templates...............................................170 Creating custom templates ................................................170 Saving your custom templates .............................................171 Using custom templates ....................................................172

Chapter 7: Printing Your Work

173

Doing Basic Printing ................................................................. 173 Changing Your Page View ................................................................ 174 Normal view.................................................................... 175 Page Layout view .............................................................. 175 Page Break Preview ............................................................ 176 Adjusting Common Page Setup Settings ............................................ 178 Choosing your printer ............................................................ 180 Specifying what you want to print ...........................................180 Changing page orientation ....................................................... 181 Specifying paper size ..........................................................181 Printing multiple copies of a report ............................................ 181 Adjusting the page margins ...................................................181 Understanding page breaks ...................................................... 183 Inserting a page break ..................................................... 183 Removing manual page breaks ........................................... 183 Printing row and column titles .................................................. 183 Scaling printed output ........................................................... 185 Printing cell gridlines .........................................................185 Printing row and column headers ............................................... 185 Using a background image ....................................................... 185 Adding a Header or a Footer to Your Reports ........................................ 187 Selecting a predefined header or footer ....................................... 188 Understanding header and footer element codes ............................. 189 Exploring other header and footer options .................................... 190 Exploring Other Print-Related Topics ................................................. 190 Copying Page Setup settings across sheets .................................... 190 Preventing certain cells from being printed ...................................191 Preventing objects from being printed .........................................191 Creating custom views of your worksheet ..................................... 192 Creating PDF files ................................................................. 194

Chapter 8: Customizing the Excel User Interface

195

Customizing the Quick Access Toolbar ............................................... 195 About the Quick Access toolbar ................................................. 196

xviii

Contents

Adding new commands to the Quick Access toolbar .......................... 197 Other Quick Access toolbar actions .......................................... 199 Customizing the Ribbon ................................................................. 201 Why you may want to customize the Ribbon .................................. 201 What can be customized...................................................... 201 How to customize the Ribbon.................................................... 202 Creating a new tab ...................................................... 202 Creating a new group....................................................... 203 Adding commands to a new group ........................................ 203 Resetting the Ribbon ............................................................. 204

Part II: Working with Formulas and Functions Chapter 9: Introducing Formulas and Functions

205 207

Understanding Formula Basics ...................................................... 207 Using operators in formulas ...................................................... 208 Understanding operator precedence in formulas .............................. 209 Using functions in your formulas ................................................ 211 Examples of formulas that use functions .............................. 212 Function arguments .................................................... 213 More about functions ................................................... 214 Entering Formulas into Your Worksheets .............................................. 214 Entering formulas manually ...................................................... 216 Entering formulas by pointing ................................................... 216 Pasting range names into formulas.............................................. 216 Inserting functions into formulas ............................................... 217 Function entry tips ................................................................ 219 Editing Formulas ......................................................................... 219 Using Cell References in Formulas ..................................................... 220 Using relative, absolute, and mixed references ............................... 221 Changing the types of your references ......................................... 223 Referencing cells outside the worksheet ....................................... 223 Referencing cells in other worksheets ................................... 223 Referencing cells in other workbooks .................................... 224 Using Formulas in Tables ................................................................ 225 Summarizing data in a table ..................................................... 225 Using formulas within a table ................................................... 227 Referencing data in a table ...................................................... 228 Correcting Common Formula Errors ................................................... 229 Handling circular references..................................................... 230 Specifying when formulas are calculated ...................................... 231 Using Advanced Naming Techniques .................................................. 232 Using names for constants ....................................................... 233 Using names for formulas ........................................................ 233

xix

Contents

Using range intersections ........................................................

Applying names to existing references .........................................

Working with Formulas..................................................................

Not hard-coding values ...........................................................

Using the Formula bar as a calculator ..........................................

Making an exact copy of a formula .............................................

Converting formulas to values ..................................................

Chapter 10: Using Formulas for Common Mathematical Operations

234 236 236 236 237 237 238

239

Calculating Percentages ................................................................ 239 Calculating percent of goal ...................................................... 239 Calculating percent variance ................................................. 241 Calculating percent variance with negative values........................... 242 Calculating a percent distribution ........................................... 243 Calculating a running total ...................................................... 244 Applying a percent increase or decrease to values ........................... 244 Dealing with divide-by-zero errors ............................................. 245 Rounding Numbers........................................................................247 Rounding numbers using formulas ...............................................247 Rounding to the nearest penny...................................................247 Rounding to significant digits ................................................... 248 Counting Values in a Range............................................................. 250 Using Excel’s Conversion Functions ................................................. 251

Chapter 11: Using Formulas to Manipulate Text

253

Working with Text ....................................................................... 253 Using Text Functions ................................................................254 Joining text strings ............................................................254 Setting text to sentence case .................................................256 Removing spaces from a text string .......................................... 257 Extracting parts of a text string ..............................................258 Finding a particular character in a text string ..............................259 Finding the second instance of a character .................................. 261 Substituting text strings ......................................................262 Counting specific characters in a cell .......................................... 263 Adding a line break within a formula........................................... 264 Cleaning strange characters from text fields ................................. 265 Padding numbers with zeros ..................................................... 266 Formatting the numbers in a text string .....................................266 Using the DOLLAR function ...................................................268

Chapter 12: Using Formulas with Dates and Times

269

Understanding How Excel Handles Dates and Times ...............................269 Understanding date serial numbers ............................................ 269 Entering dates ......................................................................270 Understanding time serial numbers .............................................271

xx

Contents

Entering times ................................................................. 272 Formatting dates and times .................................................. 273 Problems with dates............................................................... 273 Excel’s leap year bug ....................................................... 274 Pre-1900 dates .............................................................. 274 Inconsistent date entries ............................................... 275 Using Excel’s Date and Time Functions ............................................ 275 Getting the current date and time ........................................... 275 Calculating age .................................................................... 276 Calculating the number of days between two dates .......................... 277 Calculating the number of workdays between two dates..................... 277 Using NETWORKDAYS.INTL ............................................. 278 Generating a list of business days excluding holidays ........................ 278 Extracting parts of a date ........................................................ 280 Calculating number of years and months between dates ..................... 282 Converting dates to Julian date formats ....................................... 282 Calculating the percent of year completed and remaining ................. 284 Returning the last date of a given month ...................................... 285 Using the EOMONTH function ........................................... 286 Calculating the calendar quarter for a date ................................... 286 Calculating the fiscal quarter for a date ....................................... 287 Returning a fiscal month from a date ........................................... 288 Calculating the date of the Nth weekday of the month ...................... 289 Calculating the date of the last weekday of the month ...................... 290 Extracting parts of a time .................................................... 290 Calculating elapsed time ..................................................... 291 Rounding time values ............................................................. 292 Converting decimal hours, minutes, or seconds to a time................... 293 Adding hours, minutes, or seconds to a time ................................ 294

Chapter 13: Using Formulas for Conditional Analysis

295

Understanding Conditional Analysis ................................................ 295 Checking if a simple condition is met .......................................... 295 Checking for multiple conditions ................................................ 296 Validating conditional data ...................................................... 298 Looking up values ........................................................... 298 Checking if Condition1 AND Condition2 are met .............................. 299 Referring to logical conditions in cells ................................... 301 Checking if Condition1 OR Condition2 are met ................................ 302 Performing Conditional Calculations .................................................. 303 Summing all values that meet a certain condition ............................ 303 Summing greater than zero ............................................... 305 Summing all values that meet two or more conditions ....................... 306 Summing if values fall between a given date range ........................... 307

xxi

Contents

Using SUMIFS.............................................................308 Getting a count of values that meet a certain condition .................... 309 Getting a count of values that meet two or more conditions ................ 310 Finding nonstandard characters ........................................ 311 Getting the average of all numbers that meet a certain condition ...........311 Getting the average of all numbers that meet two or more conditions ...... 312

Chapter 14: Using Formulas for Matching and Lookups

315

Introducing Lookup Formulas .......................................................... 315 Leveraging Excel’s Lookup Functions ............................................... 316 Looking up an exact value based on a left lookup column ....................316 Looking up an exact value based on any lookup column ..................... 318 Looking up values horizontally .................................................. 320 Hiding errors returned by lookup functions ..................................321 Finding the closest match from a list of banded values ...................... 323 Finding the closest match with the INDEX and MATCH functions ..... 324 Looking up values from multiple tables ........................................ 325 Looking up a value based on a two-way matrix ..............................328 Using default values for match ........................................... 329 Finding a value based on multiple criteria ...................................330 Returning text with SUMPRODUCT ....................................... 331 Finding the last value in a column .............................................. 331 Finding the last number using LOOKUP ................................ 332

Chapter 15: Using Formulas for Financial Analysis

333

Performing Common Business Calculations .......................................... 333 Calculating gross profit margin and gross profit margin percent ............ 333 Calculating markup ........................................................ 334 Calculating EBIT and EBITDA ................................................. 335 Calculating cost of goods sold ................................................... 336 Calculating return on assets .................................................. 337 Calculating return on equity ........................................... 337 Calculating break even ........................................................... 338 Calculating customer churn ................................................. 340 Calculating annual churn rate ............................................ 341 Calculating average customer lifetime value ................................. 341 Calculating employee turnover ...............................................342 Leveraging Excel’s Financial Functions ........................................... 343 Converting interest rates ........................................................ 343 Computing effective rate with FV ......................................345 Creating a loan payment calculator ............................................ 345 Creating an amortization schedule ...................................... 346 Creating a variable-rate mortgage amortization schedule .................. 348 Using dates instead of payment numbers................................ 350

xxii

Contents

Calculating depreciation ......................................................... 351 Calculating accelerated depreciation .................................... 352 Calculating present value ........................................................ 354 Calculating the present value of future payments ..................... 355 Calculating net present value.................................................... 356 Calculating positive and negative cash flows ........................... 357 Calculating an internal rate of return ....................................... 358 Calculating nonperiodic future cash flows .............................. 359 Performing financial forecasting ................................................ 360

Chapter 16: Using Formulas for Statistical Analysis

365

Working with Weighted Averages ...................................................... 365 Smoothing Data with Moving Averages ................................................ 367 Applying exponential smoothing to volatile data ............................. 369 Using Functions to Create Descriptive Statistics .................................. 371 Getting the largest or smallest value ........................................... 372 Getting the Nth largest or smallest value ...................................... 373 Calculating mean, median, and mode .......................................... 375 Bucketing Data into Percentiles........................................................ 377 Identifying Statistical Outliers with an Interquartile Range ....................... 379 Creating a Frequency Distribution ................................................. 383 An alternative to the FREQUENCY function ................................. 384

Chapter 17: Using Formulas with Tables and Conditional Formatting

385

Highlighting Cells That Meet Certain Criteria ..................................... 385 Highlighting cells based on the value of another cell ......................... 387 Highlighting Values That Exist in List1 but Not List2 .............................. 389 Highlighting Values That Exist in List1 and List2 .................................. 391 Highlighting Based on Dates ............................................................ 393 Highlighting days between two dates........................................... 395 Highlighting dates based on a due date ........................................ 397

Chapter 18: Understanding and Using Array Formulas

399

Understanding Array Formulas ......................................................... 399 A multicell array formula ......................................................... 400 A single-cell array formula ....................................................... 401 Creating an Array Constant ............................................................. 403 Understanding the Dimensions of an Array ........................................ 404 One-dimensional horizontal arrays .......................................... 404 One-dimensional vertical arrays ............................................. 405 Two-dimensional arrays ...................................................... 405 Naming Array Constants ................................................................. 407 Working with Array Formulas ........................................................... 408 Entering an array formula ........................................................ 408 Selecting an array formula range ............................................... 408

xxiii

Contents

Editing an array formula ......................................................... 409 Expanding or contracting a multicell array formula ........................... 410 Using Multicell Array Formulas.......................................................... 411 Creating an array from values in a range ....................................... 411 Creating an array constant from values in a range ............................ 411 Performing operations on an array ........................................... 412 Using functions with an array ................................................ 413 Transposing an array .......................................................... 414 Generating an array of consecutive integers ...................................415 Using Single-Cell Array Formulas ....................................................... 416 Counting characters in a range .................................................. 416 Summing the three smallest values in a range ................................. 417 Counting text cells in a range ....................................................418 Eliminating intermediate formulas .......................................... 419 Using an array instead of a range reference ................................... 421

Chapter 19: Making Your Formulas Error-Free

423

Finding and Correcting Formula Errors ............................................... 423 Mismatched parentheses......................................................... 424 Cells are filled with hash marks ..............................................425 Blank cells are not blank ......................................................425 Extra space characters ....................................................... 426 Formulas returning an error ..................................................... 426 #DIV/0! errors ............................................................... 427 #N/A errors .................................................................. 428 #NAME? errors............................................................... 428 #NULL! errors ............................................................... 428 #NUM! errors ................................................................ 429 #REF! errors ................................................................. 429 #VALUE! errors .............................................................. 430 Operator precedence problems.................................................. 430 Formulas are not calculated ..................................................... 431 Problems with decimal precision ............................................... 431 “Phantom link� errors ............................................................ 432 Using Excel Auditing Tools .............................................................. 432 Identifying cells of a particular type ........................................ 432 Viewing formulas ................................................................. 433 Tracing cell relationships ........................................................ 433 Identifying precedents .................................................... 434 Identifying dependents .................................................... 434 Tracing error values .............................................................. 435 Fixing circular reference errors ................................................. 435 Using the background error-checking feature ................................. 435 Using Formula Evaluator ......................................................... 436

xxiv

Contents

Searching and Replacing ................................................................ 438 Searching for information .................................................... 438 Replacing information ........................................................ 439 Searching for formatting...................................................... 439 Spell-checking your worksheets ................................................. 440 Using AutoCorrect........................................................................ 441

Part III: Creating Charts and Other Visualizations Chapter 20: Getting Started with Excel Charts

443 445

What Is a Chart? ..................................................................... 445 How Excel handles charts ..................................................... 446 Embedded charts .............................................................. 447 Chart sheets........................................................................ 449 Parts of a chart ................................................................ 450 Chart limitations .............................................................. 452 Basic Steps for Creating a Chart .................................................... 453 Creating the chart ............................................................ 453 Switching the row and column orientation .................................... 454 Changing the chart type ...................................................... 456 Applying a chart layout ........................................................... 456 Applying a chart style ......................................................... 456 Adding and deleting chart elements ............................................ 457 Formatting chart elements ...................................................... 458 Modifying and Customizing Charts ................................................. 459 Moving and resizing a chart .................................................. 460 Converting an embedded chart to a chart sheet .............................. 460 Copying a chart ............................................................... 461 Deleting a chart ............................................................... 461 Adding chart elements............................................................ 461 Moving and deleting chart elements ............................................ 462 Formatting chart elements ...................................................... 462 Copying a chart’s formatting ...................................................... 463 Renaming a chart ............................................................. 463 Printing charts ................................................................ 464 Understanding Chart Types ............................................................. 464 Choosing a chart type ......................................................... 465 Column charts ................................................................. 467 Bar charts...................................................................... 469 Line charts..................................................................... 469 Pie charts ...................................................................... 471 XY (scatter) charts ............................................................ 474 Area charts .................................................................... 474 Radar charts ................................................................... 476

xxv

Contents

Surface charts ................................................................. 477 Bubble charts .................................................................. 477 Stock charts .................................................................... 477 New Chart Types for Excel...............................................................479 Histogram charts .............................................................. 479 Pareto charts ..................................................................481 Waterfall charts ...............................................................481 Box & whisker charts ..........................................................482 Sunburst charts ................................................................483 Treemap charts ............................................................... 484 Funnel charts ................................................................. 484 Map charts .................................................................... 486

Chapter 21: Using Advanced Charting Techniques

489

Selecting Chart Elements .............................................................. 489 Selecting with the mouse ........................................................ 490 Selecting with the keyboard .....................................................491 Selecting with the Chart Elements control .....................................491 Exploring the User Interface Choices for Modifying Chart Elements ...............492 Using the Format task pane ......................................................493 Using the chart customization buttons ........................................ 494 Using the Ribbon .................................................................. 494 Using the Mini toolbar .............................................................495 Modifying the Chart Area................................................................495 Modifying the Plot Area................................................................. 496 Working with Titles in a Chart ...................................................... 497 Working with a Legend .................................................................. 498 Working with Gridlines ..............................................................500 Modifying the Axes ...................................................................... 501 Modifying the value axis ......................................................501 Modifying the category axis...................................................506 Working with Data Series ............................................................509 Deleting or hiding a data series .............................................. 510 Adding a new data series to a chart .......................................... 510 Changing data used by a series ............................................... 511 Changing the data range by dragging the range outline ................ 511 Using the Edit Series dialog box ...........................................512 Editing the Series formula .................................................512 Displaying data labels in a chart ............................................. 513 Handling missing data .............................................................515 Adding error bars .................................................................. 516 Adding a trendline ................................................................. 517 Creating combination charts ................................................. 518 Displaying a data table ........................................................... 521 Creating Chart Templates .............................................................. 522

xxvi

Contents

Chapter 22: Creating Sparkline Graphics

523

Sparkline Types ........................................................................... 523 Creating Sparklines....................................................................... 525 Customizing Sparklines .................................................................. 527 Sizing Sparkline cells .............................................................. 528 Handling hidden or missing data ................................................. 528 Changing the Sparkline type .................................................. 529 Changing Sparkline colors and line width ..................................... 529 Highlighting certain data points ................................................. 529 Adjusting Sparkline axis scaling............................................... 530 Faking a reference line ........................................................ 531 Specifying a Date Axis ............................................................... 533 Auto-Updating Sparklines ................................................................ 535 Displaying a Sparkline for a Dynamic Range ........................................... 535

Chapter 23: Visualizing with Custom Number Formats and Shapes

537

Visualizing with Number Formatting ................................................ 537 Doing basic number formatting................................................ 537 Using shortcut keys to format numbers ................................... 538 Using the Format Cells dialog box to format numbers .................... 539 Getting fancy with custom number formatting ............................... 540 Formatting numbers in thousands and millions ........................ 542 Hiding and suppressing zeros .............................................. 543 Applying custom format colors ............................................. 544 Formatting dates and times ............................................. 545 Using symbols to enhance reporting ............................................. 546 Using Shapes and Icons as Visual Elements ............................................. 550 Inserting a shape................................................................... 550 Inserting SVG icon graphics ....................................................... 551 Formatting shapes and icons...................................................... 552 Enhancing Excel reports with shapes ............................................ 554 Creating visually appealing containers with shapes...................... 554 Layering shapes to save space.............................................. 556 Constructing your own infographic widgets with shapes ................ 556 Creating dynamic labels ....................................................... 557 Creating linked pictures ....................................................... 558 Using SmartArt and WordArt ............................................................ 561 SmartArt basics ................................................................ 561 WordArt basics ................................................................. 562 Working with Other Graphics Types ..................................................... 563 About graphics files ................................................................ 563 Inserting screenshots .............................................................. 564 Displaying a worksheet background image ...................................... 564 Using the Equation Editor ................................................................ 565

xxvii

Contents

Chapter 24: Implementing Excel Dashboarding Best Practices

567

Preparing for a Dashboard Project ....................................................... 567 Establishing the audience and purpose for the dashboard ......................568 Delineating the measures for the dashboard .....................................568 Cataloging the required data sources ............................................. 569 Defining the dimensions and filters for the dashboard .......................... 569 Determining the need for drill-down features ................................... 569 Establishing the refresh schedule ................................................. 570 Implementing Dashboard Modeling Best Practices ...................................... 570 Separating data, analysis, and presentation ..................................... 570 Starting with appropriately structured data ..................................... 571 Spreadsheet reports make for ineffective data models ..................... 571 Flat data files lend themselves nicely to data models..................... 572 Avoiding turning your data model into a database ............................ 572 Documenting and organizing your data model ................................... 573 Implementing Dashboard Design Best Practices ..................................... 574 Keep it simple ................................................................ 574 Don’t turn your dashboard into a data repository ......................... 574 Avoid the fancy formatting .............................................. 575 Limit each dashboard to one printable page .............................. 575 Format numbers effectively ..................................................... 576 Use titles and labels effectively ......................................... 576

Part IV: Managing and Analyzing Data

577

Chapter 25: Importing and Cleaning Data

579

Importing Data ............................................................................ 579 Importing from a file .............................................................. 580 Spreadsheet file formats ................................................... 580 Database file formats ........................................................580 Text file formats .............................................................581 HTML files ....................................................................582 XML files ......................................................................582 Importing vs. opening..............................................................582 Importing a text file................................................................583 Copying and pasting data .......................................................... 587 Cleaning Up Data .......................................................................... 587 Removing duplicate rows .......................................................... 587 Identifying duplicate rows ........................................................589 Splitting text .......................................................................589 Using Text to Columns ....................................................... 591 Using Flash Fill ............................................................... 592 Changing the case of text.......................................................... 595

xxviii

Contents

Removing extra spaces ........................................................... 596 Removing strange characters................................................. 597 Converting values ................................................................. 597 Classifying values ................................................................. 598 Joining columns.................................................................... 599 Rearranging columns .............................................................. 600 Randomizing the rows ............................................................ 601 Extracting a filename from a URL ............................................ 601 Matching text in a list ........................................................ 601 Changing vertical data to horizontal data ..................................... 603 Filling gaps in an imported report .............................................. 606 Checking spelling .................................................................. 607 Replacing or removing text in cells ............................................. 607 Adding text to cells ............................................................... 608 Fixing trailing minus signs ....................................................... 609 Following a data cleaning checklist .......................................... 609 Exporting Data............................................................................ 610 Exporting to a text file ........................................................... 610 CSV files ...................................................................... 610 TXT files...................................................................... 610 PRN files ..................................................................... 611 Exporting to other file formats .................................................. 611

Chapter 26: Using Data Validation

613

About Data Validation ................................................................... 613 Specifying Validation Criteria ...................................................... 614 Types of Validation Criteria You Can Apply ........................................... 615 Creating a Drop-Down List .......................................................... 617 Using Formulas for Data Validation Rules ............................................. 619 Understanding Cell References ......................................................... 619 Data Validation Formula Examples..................................................... 621 Accepting text only ............................................................... 621 Accepting a larger value than the previous cell ............................... 621 Accepting nonduplicate entries only ............................................ 622 Accepting text that begins with a specific character ....................... 623 Accepting dates by the day of the week........................................ 623 Accepting only values that don’t exceed a total ................................. 623 Creating a dependent list ..................................................... 623 Using Data Validation without Restricting Entry .................................. 625 Showing an input message........................................................ 625 Making suggested entries ..................................................... 626

Chapter 27: Creating and Using Worksheet Outlines

627

Introducing Worksheet Outlines ........................................................ 627 Creating an Outline ................................................................. 631

xxix

Contents

Preparing the data................................................................ 631 Creating an outline automatically .............................................. 632 Creating an outline manually ................................................... 632 Working with Outlines .................................................................. 634 Displaying levels .................................................................. 634 Adding data to an outline ........................................................ 635 Removing an outline .............................................................. 635 Adjusting the outline symbols ................................................ 635 Hiding the outline symbols....................................................636

Chapter 28: Linking and Consolidating Worksheets

637

Linking Workbooks........................................................................637 Creating External Reference Formulas ............................................... 638 Understanding link formula syntax ...........................................638 Creating a link formula by pointing ............................................ 639 Pasting links .................................................................. 640 Working with External Reference Formulas .......................................... 640 Creating links to unsaved workbooks ........................................... 640 Opening a workbook with external reference formulas ...................... 641 Changing the startup prompt.................................................... 642 Updating links ................................................................ 643 Changing the link source ......................................................... 643 Severing links ..................................................................643 Avoiding Potential Problems with External Reference Formulas................... 644 Renaming or moving a source workbook ....................................... 644 Using the Save As command ..................................................... 644 Modifying a source workbook .................................................... 644 Using Intermediary links ..................................................... 646 Consolidating Worksheets .............................................................. 646 Consolidating worksheets by using formulas .................................. 647 Consolidating worksheets by using Paste Special ............................. 648 Consolidating worksheets by using the Consolidate dialog box ............. 649 Viewing a workbook consolidation example ................................... 650 Refreshing a consolidation ...................................................... 653 Learning more about consolidation............................................. 654

Chapter 29: Introducing PivotTables About PivotTables .......................................................................

A PivotTable example ............................................................

Data appropriate for a PivotTable ..............................................

Creating a PivotTable Automatically ..................................................

Creating a PivotTable Manually........................................................

Specifying the data ...............................................................

Specifying the location for the PivotTable ....................................

Laying out the PivotTable .......................................................

xxx

655 655 656 659 662 663 663 664 664

Contents

Formatting the PivotTable ....................................................... 668 Modifying the PivotTable ........................................................ 670 Seeing More PivotTable Examples ..................................................... 672 What is the daily total new deposit amount for each branch? ................ 672 Which day of the week accounts for the most deposits? ...................... 672 How many accounts were opened at each branch, broken down by account type? ........................................................ 674 How much money was used to open the accounts? ............................ 674 What types of accounts do tellers open most often? .......................... 675 In which branch do tellers open the most checking accounts for new customers?............................................................. 676 Learning More ............................................................................ 677

Chapter 30: Analyzing Data with PivotTables

679

Working with Non-numeric Data........................................................ 679 Grouping PivotTable Items .............................................................. 681 A manual grouping example ...................................................... 681 Automatic grouping examples .................................................... 683 Grouping by date ............................................................ 683 Grouping by time ........................................................ 688 Using a PivotTable to Create a Frequency Distribution ........................... 689 Creating a Calculated Field or Calculated Item ...................................... 691 Creating a calculated field ....................................................... 693 Inserting a calculated item ...................................................... 695 Filtering PivotTables with Slicers .................................................. 698 Filtering PivotTables with a Timeline .............................................. 700 Referencing Cells within a PivotTable ................................................ 701 Creating PivotCharts ................................................................ 703 A PivotChart example............................................................. 703 More about PivotCharts ....................................................... 706 Using the Data Model .................................................................... 707

Chapter 31: Performing Spreadsheet What-If Analysis

715

Looking at a What-If Example .......................................................... 715 Exploring Types of What-If Analyses ................................................... 717 Performing manual what-if analysis ......................................... 717 Creating data tables .............................................................. 717 Creating a one-input data table .......................................... 718 Creating a two-input data table .......................................... 721 Using Scenario Manager .......................................................... 725 Defining scenarios ...................................................... 725 Displaying scenarios .................................................... 728 Modifying scenarios..................................................... 729 Merging scenarios ....................................................... 729 Generating a scenario report ............................................. 730

xxxi

Contents

Chapter 32: Analyzing Data Using Goal Seeking and Solver

733

Exploring What-If Analysis, in Reverse .................................................. 733 Using Single-Cell Goal Seeking ....................................................... 734 Looking at a goal-seeking example ...............................................734 Learning more about goal seeking ............................................. 736 Introducing Solver ........................................................................ 737 Looking at appropriate problems for Solver ...................................... 737 Seeing a simple Solver example ...................................................738 Exploring Solver options ........................................................... 743 Seeing Some Solver Examples ............................................................ 744 Solving simultaneous linear equations ........................................... 745 Minimizing shipping costs ......................................................... 747 Allocating resources................................................................ 750 Optimizing an investment portfolio ........................................... 751

Chapter 33: Analyzing Data with the Analysis ToolPak

755

The Analysis ToolPak: An Overview .................................................. 755 Installing the Analysis ToolPak Add-In.................................................. 756 Using the Analysis Tools .................................................................. 756 Introducing the Analysis ToolPak Tools ................................................. 757 Analysis of variance............................................................ 757 Correlation ................................................................................ 758 Covariance .......................................................................... 759 Descriptive statistics ............................................................... 759 Exponential smoothing ............................................................ 759 F-test (two-sample test for variance) ............................................. 760 Fourier analysis ................................................................ 760 Histogram ...................................................................... 761 Moving average ..................................................................... 761 Random number generation ....................................................... 763 Rank and percentile ................................................................ 765 Regression .......................................................................... 765 Sampling ............................................................................ 766 T-test ................................................................................ 766 Z-test (two-sample test for means)................................................ 767

Chapter 34: Protecting Your Work

769

Types of Protection ....................................................................... 769 Protecting a Worksheet ................................................................... 770 Unlocking cells ..................................................................... 770 Sheet protection options .......................................................... 772 Assigning user permissions .................................................... 773 Protecting a Workbook ................................................................... 774 Requiring a password to open a workbook ....................................... 774 Protecting a workbook’s structure ................................................ 776

xxxii

Contents

Protecting a VBA Project ............................................................... 777 Related Topics ............................................................................ 778 Saving a worksheet as a PDF file ................................................ 778 Marking a workbook as final...................................................... 778 Inspecting a workbook ............................................................ 779 Using a digital signature .......................................................... 780 Getting a digital ID ......................................................... 780 Signing a workbook ......................................................... 780

Part V: Understanding Power Pivot and Power Query

781

Chapter 35: Introducing Power Pivot

783

Understanding the Power Pivot Internal Data Model ................................ 783 Activating the Power Pivot Ribbon .............................................. 783 Linking Excel tables to Power Pivot ............................................. 785 Preparing your Excel tables ............................................... 786 Adding your Excel tables to the data model ............................. 787 Creating relationships between your Power Pivot tables .............. 788 Managing existing relationships .......................................... 790 Using Power Pivot data in reporting ...................................... 792 Loading Data from Other Data Sources ................................................ 793 Loading data from relational databases ..................................... 793 Loading data from SQL Server ......................................... 793 Loading data from other relational database systems ................ 797 Loading data from flat files ...................................................... 798 Loading data from external Excel files .................................. 798 Loading data from text files............................................... 800 Loading data from the clipboard ......................................... 801 Refreshing and managing external data connections ........................ 803 Manually refreshing your Power Pivot data ............................. 803 Setting up automatic refreshing ....................................... 803 Editing your data connection ............................................. 805

Chapter 36: Working Directly with the Internal Data Model

807

Directly Feeding the Internal Data Model ............................................. 807 Managing Relationships in the Internal Data Model.................................. 813 Removing a Table from the Internal Data Model ..................................... 814

Chapter 37: Adding Formulas to Power Pivot

817

Enhancing Power Pivot Data with Calculated Columns .............................. 817 Creating your first calculated column .......................................... 818 Formatting your calculated columns ........................................... 819 Referencing calculated columns in other calculations ....................... 820 Hiding calculated columns from end users ..................................... 821

xxxiii

Contents

Utilizing DAX to Create Calculated Columns ......................................... 822 Identifying DAX functions safe for calculated columns ...................... 822 Building DAX-driven calculated columns ...................................... 824 Month sorting in Power Pivot–driven PivotTables ...................... 826 Referencing fields from other tables ........................................... 827 Nesting functions ..............................................................829 Understanding Calculated Measures .................................................. 829 Editing and deleting calculated measures ..................................... 832 Using Cube Functions to Free Your Data .............................................. 833

Chapter 38: Introducing Power Query

835

Understanding Power Query Basics ................................................. 835 Understanding query steps ...................................................... 842 Viewing the Advanced Query Editor ............................................ 844 Refreshing Power Query data ................................................... 844 Managing existing queries ....................................................... 845 Understanding column-level actions ........................................ 846 Understanding table actions ................................................. 849 Getting Data from External Sources ................................................... 851 Importing data from files ........................................................ 852 Getting data from Excel workbooks...................................... 852 Getting data from CSV and text files .................................... 853 Importing data from database systems ......................................855 Importing data from relational and OLAP databases ..................855 Importing data from Azure databases .................................855 Importing data using ODBC connections to nonstandard databases ...855 Getting Data from Other Data Systems ............................................... 855 Managing Data Source Settings......................................................856 Editing data source settings .................................................. 857

Chapter 39: Transforming Data with Power Query

859

Performing Common Transformation Tasks ........................................859 Removing duplicate records ..................................................... 859 Filling in blank fields ............................................................. 862 Filling in empty strings ....................................................... 862 Concatenating columns .......................................................... 863 Changing case..................................................................865 Finding and replacing specific text ............................................. 865 Trimming and cleaning text ..................................................... 866 Extracting the left, right, and middle values ................................. 868 Extracting first and last characters ..........................................869 Extracting middle characters ................................................. 870 Splitting columns using character markers.................................... 870 Unpivoting columns ............................................................... 873 Unpivoting other columns ......................................................... 874 Pivoting columns .................................................................. 875

xxxiv

Contents

Creating Custom Columns ............................................................... 877 Concatenating with a custom column .......................................... 878 Understanding data type conversions........................................... 879 Spicing up custom columns with functions .................................. 881 Adding conditional logic to custom columns ................................... 883 Grouping and Aggregating Data......................................................... 884

Chapter 40: Making Queries Work Together

887

Reusing Query Steps ..................................................................... 887 Understanding the Append Feature.................................................... 891 Creating the needed base queries ............................................... 892 Appending the data ............................................................... 893 Understanding the Merge Feature ..................................................... 896 Understanding Power Query joins ............................................... 896 Merging queries .................................................................... 897

Chapter 41: Enhancing Power Query Productivity

903

Implementing Some Power Query Productivity Tips ................................. 903 Getting quick information about your queries ................................ 903 Organizing queries in groups..................................................... 904 Selecting columns in your queries faster .................................... 906 Renaming query steps ............................................................ 906 Quickly creating reference tables ............................................... 907 Copying queries to save time ................................................. 908 Setting a default load behavior .................................................. 908 Preventing automatic data type changes ...................................... 909 Avoiding Power Query Performance Issues ........................................... 910 Using views instead of tables .................................................... 910 Letting your back-end database servers do some crunching ................ 911 Upgrading to 64-bit Excel......................................................... 911 Disabling privacy settings to improve performance ......................... 912 Disabling relationship detection ................................................ 912

Part VI: Automating Excel Chapter 42: Introducing Visual Basic for Applications

915 917

Introducing VBA Macros ................................................................. 917 Displaying the Developer Tab........................................................... 918 Learning about Macro Security ..................................................... 919 Saving Workbooks That Contain Macros ............................................... 920 Looking at the Two Types of VBA Macros ............................................. 921 VBA Sub procedures ............................................................... 921 VBA functions .................................................................. 922 Creating VBA Macros ..................................................................... 924

xxxv

Contents

Recording VBA macros............................................................ 924 Recording your actions to create VBA code: the basics................925 Recording a macro: a simple example ................................... 926 Examining the macro ...................................................... 926 Testing the macro .......................................................... 928 Editing the macro .......................................................... 928 Absolute versus relative recording ....................................... 929 Another example ........................................................... 929 Running the macro ......................................................... 930 Examining the macro ...................................................... 930 Rerecording the macro .................................................... 931 Testing the macro .......................................................... 932 More about recording VBA macros .............................................. 933 Storing macros in your Personal Macro Workbook ...................... 933 Assigning a macro to a shortcut key ..................................... 933 Assigning a macro to a button ............................................ 934 Adding a macro to your Quick Access toolbar........................... 935 Writing VBA code ................................................................. 935 The basics: entering and editing code................................... 936 The Excel object model.....................................................937 Objects and collections ....................................................937 Properties................................................................938 Methods ...................................................................... 939 The Range object ........................................................... 940 Variables .....................................................................941 Controlling execution .......................................................941 A macro that can’t be recorded .............................................944 Learning More ............................................................................ 945

Chapter 43: Creating Custom Worksheet Functions

947

Introducing VBA Functions .......................................................... 947 Seeing a Simple Example ............................................................... 948 Creating a custom function ...................................................948 Using the function in a worksheet .............................................. 949 Analyzing the custom function ............................................... 949 Learning about Function Procedures ................................................. 950 Executing Function Procedures ........................................................ 952 Calling custom functions from a procedure ................................... 952 Using custom functions in a worksheet formula .............................. 952 Using Function Procedure Arguments .............................................. 953 Creating a function with no arguments ........................................ 953 Creating a function with one argument ........................................ 954 Creating another function with one argument ................................ 954 Creating a function with two arguments ...................................... 956 Creating a function with a range argument ................................... 956 Creating a simple but useful function ........................................ 957

xxxvi

Contents

Debugging Custom Functions ....................................................... 958 Inserting Custom Functions ......................................................... 958 Learning More ............................................................................ 960

Chapter 44: Creating UserForms

961

Understanding Why to Create UserForms ............................................. 961 Exploring UserForm Alternatives ....................................................... 962 Using the InputBox function .................................................. 962 Using the MsgBox function .................................................... 963 Creating UserForms: An Overview .................................................. 966 Working with UserForms .......................................................... 967 Adding controls .................................................................... 968 Changing the properties of a control ........................................... 969 Handling events ................................................................... 970 Displaying a UserForm ............................................................ 970 Looking at a UserForm Example ........................................................ 971 Creating the UserForm............................................................ 971 Testing the UserForm ............................................................. 972 Creating an event handler procedure ........................................... 973 Looking at Another UserForm Example ................................................ 974 Creating the UserForm............................................................ 974 Creating event handler procedures ............................................. 976 Showing the UserForm ............................................................ 978 Testing the UserForm ............................................................. 978 Making the macro available from a worksheet button ........................ 979 Making the macro available on your Quick Access toolbar ................... 979 Enhancing UserForms .................................................................... 980 Adding accelerator keys .......................................................... 980 Controlling tab order ............................................................. 980 Learning More ............................................................................ 981

Chapter 45: Using UserForm Controls in a Worksheet

983

Understanding Why to Use Controls on a Worksheet ................................. 983 Using Controls ............................................................................ 986 Adding a control ................................................................... 986 Learning about Design mode ..................................................... 986 Adjusting properties .............................................................. 987 Using common properties ........................................................ 988 Linking controls to cells .......................................................... 989 Creating macros for controls ..................................................... 989 Reviewing the Available ActiveX Controls ............................................ 991 CheckBox ........................................................................... 991 ComboBox .......................................................................... 991 CommandButton ................................................................... 992 Image................................................................................ 992

xxxvii

Contents

Label ................................................................................

ListBox ........................................................................

OptionButton ......................................................................

ScrollBar ...........................................................................

SpinButton .........................................................................

TextBox.............................................................................

ToggleButton.......................................................................

Chapter 46: Working with Excel Events

992 993 993 994 995 995 996

997

Understanding Events ................................................................... 997 Entering Event-Handler VBA Code ..................................................... 998 Using Workbook-Level Events .......................................................... 999 Using the Open event ............................................................ 1000 Using the SheetActivate event ................................................. 1001 Using the NewSheet event ...................................................... 1002 Using the BeforeSave event ..................................................... 1002 Using the BeforeClose event .................................................... 1002 Working with Worksheet Events ...................................................... 1003 Using the Change event ......................................................... 1003 Monitoring a specific range for changes ...................................... 1004 Using the SelectionChange event .............................................. 1005 Using the BeforeRightClick event .............................................. 1006 Using Special Application Events ..................................................... 1006 Using the OnTime event......................................................... 1007 Using the OnKey event .......................................................... 1008

Chapter 47: Seeing Some VBA Examples

1011

Working with Ranges ................................................................... 1011 Copying a range .................................................................. 1012 Copying a variable-size range .................................................. 1013 Selecting to the end of a row or column ...................................... 1014 Selecting a row or column ...................................................... 1014 Moving a range ................................................................... 1015 Looping through a range efficiently............................................ 1015 Prompting for a cell value ...................................................... 1016 Determining the type of selection............................................. 1018 Identifying a multiple selection ............................................... 1018 Counting selected cells ......................................................... 1019 Working with Workbooks ............................................................... 1020 Saving all workbooks............................................................. 1020 Saving and closing all workbooks............................................... 1020 Working with Charts ............................................................... 1021 Modifying the chart type ................................................... 1021 Modifying chart properties ..................................................... 1022 Applying chart formatting .................................................. 1022

xxxviii

Contents

VBA Speed Tips ..........................................................................1023 Turning off screen updating ....................................................1023 Preventing alert messages ......................................................1023 Simplifying object references .................................................. 1024 Declaring variable types ..................................................... 1024

Chapter 48: Creating Custom Excel Add-Ins

1027

Understanding Add-Ins ................................................................ 1027 Working with Add-Ins .................................................................. 1028 Understanding Why to Create Add-Ins ............................................... 1029 Creating Add-Ins ........................................................................ 1030 Looking at an Add-In Example ......................................................... 1031 Learning about Module1 ......................................................... 1032 Learning about the UserForm ................................................... 1032 Testing the workbook ............................................................ 1032 Adding descriptive information ............................................. 1033 Creating the user interface for your add-in macro .......................... 1034 Protecting the project .......................................................... 1034 Creating the add-in .............................................................. 1035 Installing the add-in ............................................................. 1035

Index

1037

Xxxix

We are making a service for people who want a MS office 365 student A1 account with lifetime licence. If you are interested on this service check the link: https://www.fiverr.com/share/emV5Ek If you want this ebook just contact us in the previous link and you will get it with your MS accounts for 5 dollars (both book+ MS account).

Note: I can’t share all the ebook here on this website, because of Copyright so I will share with you just the summary of the content.