Microsoft Excel 365 Bible_compresseddikompresi.pdf

Microsoft® Excel® 365 Bible Microsoft ® Excel 365 ® BIBLE Michael Alexander Dick Kusleika Copyright © 2022 by John Wiley & Sons, Inc. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada and the United Kingdom....

Microsoft® Excel® 365 Bible Microsoft ® Excel 365 ® BIBLE Michael Alexander Dick Kusleika Copyright © 2022 by John Wiley & Sons, Inc. All rights reserved. Published by John Wiley & Sons, Inc., Hoboken, New Jersey. Published simultaneously in Canada and the United Kingdom. 978-­1-­119-­83510-­3 978-­1-­119-­83522-­6 (ebk.) 978-­1-­119-­83523-­3 (ebk.) 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 Section 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, Inc., 222 Rosewood Drive, Danvers, MA 01923, (978) 750-­8400, fax (978) 750-­4470, or on the web at www.copyright.com. 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/ permission. 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 Website 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 the 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 or for technical support, please contact our Customer Care Department within the United States at (800) 762-­2974, outside the United States at (317) 572-­3993 or fax (317) 572-­4002. Wiley also publishes its books in a variety of electronic formats. Some content that appears in print may not be available in electronic formats. For more information about Wiley products, visit our web site at www.wiley.com. Library of Congress Control Number: 2021951349 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 in the United States and/or other countries. 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. Cover image: © aleksandarvelasevic/GettyImages Cover design: Wiley About the Authors Michael Alexander is a senior consultant at Slalom Consulting with more than 15 years’ experience in data management and reporting. He is the author of more than a dozen books on business analysis using Microsoft Excel and has been named Microsoft Excel MVP for his contributions to the Excel community. 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. v About the Technical Editor Joyce J. Nielsen has worked in the publishing industry for more than 30 years as a techni- cal writer/editor, development editor, and project manager, specializing in Microsoft Office, Windows, Internet, and general technology content for leading educational, retail, and online publishers. She is the author or co-­author of more than 50 computer books and 2,100 online articles. Joyce holds a bachelor of science degree in Quantitative Business Analysis from Indiana University’s Kelley School of Business in Bloomington, Indiana. She currently resides in Arizona. vii Acknowledgments O 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 Joyce Nielsen for suggesting numer- ous 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. ix Contents at a Glance Introduction............................................................................................................. xxxix Part I: Getting Started with Excel................................. 1 Chapter 1: Introducing Excel............................................................................................ 3 Chapter 2: Entering and Editing Worksheet Data................................................................27 Chapter 3: Performing Basic Worksheet Operations.............................................................53 Chapter 4: Working with Excel Ranges and Tables...............................................................73 Chapter 5: Formatting Worksheets.................................................................................. 121 Chapter 6: Understanding Excel Files and Templates......................................................... 157 Chapter 7: Printing Your Work....................................................................................... 177 Chapter 8: Customizing the Excel User Interface............................................................... 199 Part II: Working with Formulas and Functions...................... 209 Chapter 9: Introducing Formulas and Functions............................................................... 211 Chapter 10: Understanding and Using Array Formulas....................................................... 247 Chapter 11: Using Formulas for Common Mathematical Operations..................................... 271 Chapter 12: Using Formulas to Manipulate Text................................................................ 285 Chapter 13: Using Formulas with Dates and Times............................................................ 301 Chapter 14: Using Formulas for Conditional Analysis........................................................ 329 Chapter 15: Using Formulas for Matching and Lookups..................................................... 347 Chapter 16: Using Formulas with Tables and Conditional Formatting................................... 365 Chapter 17: Making Your Formulas Error-­Free................................................................... 379 Part III: Creating Charts and Other Visualizations................... 399 Chapter 18: Getting Started with Excel Charts................................................................. 401 Chapter 19: Using Advanced Charting Techniques............................................................. 441 Chapter 20: Creating Sparkline Graphics.......................................................................... 475 Chapter 21: Visualizing with Custom Number Formats and Shapes...................................... 489 Part IV: Managing and Analyzing Data........................... 519 Chapter 22: Importing and Cleaning Data........................................................................ 521 Chapter 23: Using Data Validation.................................................................................. 553 Chapter 24: Creating and Using Worksheet Outlines.......................................................... 567 Chapter 25: Linking and Consolidating Worksheets........................................................... 577 Chapter 26: Introducing PivotTables............................................................................... 595 xi Contents at a Glance Chapter 27: Analyzing Data with PivotTables................................................................... 617 Chapter 28: Performing Spreadsheet What-­If Analysis....................................................... 651 Chapter 29: Analyzing Data Using Goal Seeking and Solver............................................... 675 Chapter 30: Analyzing Data with the Analysis ToolPak...................................................... 697 Chapter 31: Protecting Your Work................................................................................... 709 Part V: Understanding Power Pivot and Power Query................. 721 Chapter 32: Introducing Power Pivot............................................................................... 723 Chapter 33: Working Directly with the Internal Data Model............................................... 747 Chapter 34: Adding Formulas to Power Pivot.................................................................... 757 Chapter 35: Introducing Power Query.............................................................................. 777 Chapter 36: Transforming Data with Power Query............................................................. 805 Chapter 37: Making Queries Work Together...................................................................... 837 Chapter 38: Enhancing Power Query Productivity............................................................. 855 Part VI: Automating Excel..................................... 867 Chapter 39: Introducing Visual Basic for Applications....................................................... 869 Chapter 40: Creating Custom Worksheet Functions........................................................... 899 Chapter 41: Creating UserForms...................................................................................... 913 Chapter 42: Using UserForm Controls in a Worksheet........................................................ 935 Chapter 43: Working with Excel Events........................................................................... 949 Chapter 44: Seeing Some VBA Examples.......................................................................... 963 Chapter 45: Creating Custom Excel Add-­Ins..................................................................... 979 Index.......................................................................................................................... 989 xii Contents Introduction......................................................... xxxix Part I: Getting Started with Excel 1 Chapter 1: Introducing Excel................................................ 3 Understanding What Excel Is Used For....................................................................... 3 Understanding Workbooks and Worksheets................................................................. 4 Moving around a Worksheet..................................................................................... 4 Navigating with your keyboard........................................................................ 7 Navigating with your mouse............................................................................ 8 Using the Ribbon.................................................................................................... 9 Ribbon tabs................................................................................................... 9 Contextual tabs............................................................................................11 Types of commands on the Ribbon..................................................................12 Accessing the Ribbon by using your keyboard...................................................12 Using Shortcut Menus............................................................................................15 Customizing Your Quick Access Toolbar.....................................................................16 Working with Dialog Boxes.....................................................................................18 Navigating dialog boxes.................................................................................18 Using tabbed dialog boxes..............................................................................19 Using Task Panes...................................................................................................20 Creating Your First Excel Workbook..........................................................................20 Getting started on your worksheet..................................................................20 Filling in the month names............................................................................21 Entering the sales data..................................................................................22 Formatting the numbers.................................................................................23 Making your worksheet look a bit fancier.........................................................23 Summing the values......................................................................................24 Creating a chart............................................................................................25 Printing your worksheet.................................................................................25 Saving your workbook...................................................................................26 Chapter 2: Entering and Editing Worksheet Data................................27 Exploring Data Types.............................................................................................27 Numeric values.............................................................................................27 Text entries..................................................................................................28 xiii Contents Formulas......................................................................................................29 Error values..................................................................................................29 Entering Text and Values into Your Worksheets..........................................................30 Entering numbers..........................................................................................30 Entering text................................................................................................30 Using Enter mode..........................................................................................31 Entering Dates and Times into Your Worksheets.........................................................31 Entering date values......................................................................................31 Entering time values......................................................................................32 Modifying Cell Contents..........................................................................................32 Deleting the contents of a cell........................................................................32 Replacing the contents of a cell......................................................................33 Editing the contents of a cell..........................................................................33 Learning some handy data-­entry techniques....................................................35 Automatically moving the selection after entering data............................35 Selecting a range of input cells before entering data.................................36 Using Ctrl+Enter to place information into multiple cells simultaneously.....36 Changing modes...................................................................................36 Entering decimal points automatically....................................................36 Using AutoFill to enter a series of values.................................................37 Using AutoComplete to automate data entry............................................37 Forcing text to appear on a new line within a cell....................................38 Using AutoCorrect for shorthand data entry.............................................39 Entering numbers with fractions.............................................................40 Using a form for data entry...................................................................40 Entering the current date or time into a cell............................................41 Applying Number Formatting..................................................................................42 Using automatic number formatting................................................................43 Formatting numbers by using the Ribbon.........................................................43 Using shortcut keys to format numbers............................................................43 Formatting numbers by using the Format Cells dialog box..................................45 Adding your own custom number formats........................................................47 Using Excel on a Tablet..........................................................................................47 Exploring Excel’s tablet interface.....................................................................48 Entering formulas on a tablet.........................................................................49 Introducing the Draw Ribbon..........................................................................49 Chapter 3: Performing Basic Worksheet Operations..............................53 Learning the Fundamentals of Excel Worksheets........................................................53 Working with Excel windows...........................................................................53 Moving and resizing windows.................................................................54 Switching among windows.....................................................................55 Closing windows...................................................................................55 Activating a worksheet..................................................................................56 Adding a new worksheet to your workbook......................................................56 xiv  Contents Deleting a worksheet you no longer need.........................................................57 Changing the name of a worksheet..................................................................57 Changing a sheet tab color.............................................................................58 Rearranging your worksheets..........................................................................58 Hiding and unhiding a worksheet...................................................................60 Controlling the Worksheet View...............................................................................60 Zooming in or out for a better view.................................................................60 Viewing a worksheet in multiple windows........................................................61 Comparing sheets side by side........................................................................62 Splitting the worksheet window into panes......................................................63 Keeping the titles in view by freezing panes.....................................................63 Monitoring cells with a Watch Window.............................................................65 Working with Rows and Columns.............................................................................66 Selecting rows and columns............................................................................66 Inserting rows and columns............................................................................66 Deleting rows and columns.............................................................................68 Changing column widths and row heights........................................................68 Changing column widths.......................................................................69 Changing row heights............................................................................69 Hiding rows and columns...............................................................................70 Chapter 4: Working with Excel Ranges and Tables...............................73 Understanding Cells and Ranges..............................................................................73 Selecting ranges............................................................................................74 Selecting complete rows and columns..............................................................75 Selecting noncontiguous ranges......................................................................75 Selecting multi-­sheet ranges..........................................................................76 Selecting special types of cells........................................................................79 Selecting cells by searching............................................................................81 Copying or Moving Ranges......................................................................................83 Copying by using Ribbon commands................................................................84 Copying by using shortcut menu commands.....................................................85 Copying by using shortcut keys.......................................................................85 Copying or moving by using drag-­and-­drop......................................................87 Copying to adjacent cells................................................................................88 Copying a range to other sheets......................................................................89 Using the Office Clipboard to paste..................................................................89 Pasting in special ways..................................................................................91 Using the Paste Special dialog box..................................................................92 Performing mathematical operations without formulas..............................94 Skipping blanks when pasting................................................................94 Transposing a range..............................................................................94 Using Names to Work with Ranges...........................................................................95 Creating range names in your workbooks.........................................................96 Using the Name box..............................................................................96 xv Contents Using the New Name dialog box..............................................................96 Using the Create Names from Selection dialog box....................................97 Managing names...........................................................................................99 Adding Comments to Cells..................................................................................... 100 Showing comments...................................................................................... 101 Replying to comments................................................................................. 102 Editing comments and replies....................................................................... 102 Deleting comments and replies..................................................................... 103 Resolving comment threads.......................................................................... 103 Adding Notes to Cells........................................................................................... 104 Showing notes............................................................................................ 105 Formatting notes........................................................................................ 106 Editing notes.............................................................................................. 108 Deleting notes............................................................................................ 108 Working with Tables............................................................................................. 108 Understanding a table’s structure.................................................................. 108 The header row................................................................................... 109 The data body.................................................................................... 109 The total row..................................................................................... 109 The resizing handle............................................................................. 110 Creating a table.......................................................................................... 110 Adding data to a table................................................................................. 111 Sorting and filtering table data..................................................................... 111 Sorting a table................................................................................... 112 Filtering a table................................................................................. 114 Filtering a table with slicers................................................................. 116 Changing the table’s appearance................................................................... 117 Chapter 5: Formatting Worksheets..........................................121 Getting to Know the Formatting Tools.................................................................... 121 Using the formatting tools on the Home tab................................................... 122 Using the Mini toolbar................................................................................. 123 Using the Format Cells dialog box................................................................. 124 Formatting Your Worksheet................................................................................... 124 Using fonts to format your worksheet............................................................ 124 Changing text alignment.............................................................................. 127 Choosing horizontal alignment options................................................. 127 Choosing vertical alignment options..................................................... 129 Wrapping or shrinking text to fit the cell.............................................. 129 Merging worksheet cells to create additional text space........................... 129 Displaying text at an angle.................................................................. 131 Using colors and shading............................................................................. 131 Adding borders and lines.............................................................................. 132 Using Conditional Formatting................................................................................ 135 xvi  Contents Specifying conditional formatting................................................................. 135 Using graphical conditional formats.............................................................. 135 Using data bars.................................................................................. 135 Using color scales............................................................................... 137 Using icon sets................................................................................... 138 Creating formula-­based rules........................................................................ 139 Understanding relative and absolute references...................................... 141 Conditional formatting formula examples....................................................... 142 Identifying weekend days.................................................................... 142 Highlighting a row based on a value..................................................... 142 Displaying alternate-­row shading......................................................... 143 Creating checkerboard shading............................................................. 144 Shading groups of rows....................................................................... 144 Working with conditional formats................................................................. 144 Managing rules................................................................................... 145 Copying cells that contain conditional formatting.................................. 145 Deleting conditional formatting........................................................... 146 Locating cells that contain conditional formatting................................. 146 Using Named Styles for Easier Formatting............................................................... 146 Applying styles........................................................................................... 147 Modifying an existing style.......................................................................... 147 Creating new styles..................................................................................... 149 Merging styles from other workbooks............................................................ 150 Controlling styles with templates.................................................................. 150 Understanding Document Themes.......................................................................... 150 Applying a theme........................................................................................ 152 Customizing a theme................................................................................... 153 Chapter 6: Understanding Excel Files and Templates............................157 Creating a New Workbook..................................................................................... 157 Opening an Existing Workbook.............................................................................. 158 Filtering filenames...................................................................................... 160 Choosing your file display preferences........................................................... 161 Saving a Workbook.............................................................................................. 161 Using AutoRecover............................................................................................... 163 Recovering versions of the current workbook.................................................. 164 Recovering unsaved work............................................................................. 164 Configuring AutoRecover.............................................................................. 165 Password-­Protecting a Workbook........................................................................... 165 Organizing Your Files........................................................................................... 166 Other Workbook Info Options................................................................................ 166 Protect Workbook options............................................................................ 166 Check for Issues options.............................................................................. 167 Version History........................................................................................... 167 xvii Contents Manage Workbook options............................................................................ 167 Browser View options................................................................................... 168 Compatibility Mode section.......................................................................... 168 Closing Workbooks............................................................................................... 168 Safeguarding Your Work....................................................................................... 168 Working with Templates....................................................................................... 169 Exploring Excel templates............................................................................ 169 Viewing templates.............................................................................. 169 Creating a workbook from a template.................................................... 170 Modifying a template.......................................................................... 172 Using default templates............................................................................... 172 Using the workbook template to change workbook defaults..................... 173 Creating a worksheet template............................................................. 174 Editing your template......................................................................... 174 Resetting the default workbook............................................................ 174 Using custom workbook templates................................................................ 174 Creating custom templates................................................................... 174 Saving your custom templates.............................................................. 175 Using custom templates...................................................................... 176 Chapter 7: Printing Your Work.............................................177 Doing Basic Printing............................................................................................ 177 Changing Your Page View...................................................................................... 179 Normal view............................................................................................... 179 Page Layout view........................................................................................ 180 Page Break Preview...................................................................................... 181 Adjusting Common Page Setup Settings.................................................................. 183 Choosing your printer.................................................................................. 184 Specifying what you want to print................................................................ 184 Changing page orientation........................................................................... 185 Specifying paper size................................................................................... 185 Printing multiple copies of a report............................................................... 186 Adjusting the page margins.......................................................................... 186 Understanding page breaks........................................................................... 187 Inserting a page break........................................................................ 187 Removing manual page breaks.............................................................. 188 Printing row and column titles..................................................................... 188 Scaling printed output................................................................................. 189 Printing cell gridlines.................................................................................. 189 Printing row and column headers.................................................................. 190 Using a background image............................................................................ 190 Adding a Header or a Footer to Your Reports........................................................... 192 Selecting a predefined header or footer.......................................................... 192 Understanding header and footer element codes............................................. 192 Exploring other header and footer options..................................................... 194 xviii  Contents Exploring Other Print-­Related Topics...................................................................... 194 Copying Page Setup settings across sheets...................................................... 195 Preventing certain cells from being printed.................................................... 195 Preventing objects from being printed........................................................... 196 Creating custom views of your worksheet....................................................... 197 Creating PDF files........................................................................................ 198 Chapter 8: Customizing the Excel User Interface...............................199 Customizing the Quick Access Toolbar.................................................................... 199 About the Quick Access Toolbar.................................................................... 200 Adding new commands to the Quick Access Toolbar......................................... 201 Other Quick Access Toolbar actions................................................................ 203 Customizing the Ribbon........................................................................................ 204 Why you may want to customize the Ribbon.................................................. 205 What can be customized.............................................................................. 205 How to customize the Ribbon....................................................................... 205 Creating a new tab.............................................................................. 206 Creating a new group.......................................................................... 206 Adding commands to a new group........................................................ 207 Resetting the Ribbon................................................................................... 208 Part II: Working with Formulas and Functions 209 Chapter 9: Introducing Formulas and Functions................................211 Understanding Formula Basics............................................................................... 211 Using operators in formulas.......................................................................... 213 Understanding operator precedence in formulas.............................................. 214 Using functions in your formulas.................................................................. 216 Examples of formulas that use functions............................................... 216 Function arguments............................................................................ 217 More about functions.......................................................................... 218 Entering Formulas into Your Worksheets................................................................. 218 Entering formulas by pointing...................................................................... 220 Pasting range names into formulas................................................................ 220 Inserting functions into formulas.................................................................. 221 Function entry tips..................................................................................... 223 Editing Formulas................................................................................................. 224 Using Cell References in Formulas.......................................................................... 225 Using relative, absolute, and mixed references................................................ 225 Changing the types of your references........................................................... 227 Referencing cells outside the worksheet......................................................... 228 Referencing cells in other worksheets................................................... 228 Referencing cells in other workbooks.................................................... 228 Introducing Formula Variables............................................................................... 229 xix Contents Understanding the LET function.................................................................... 230 Formula variables in action........................................................................... 231 Using Formulas in Tables...................................................................................... 232 Summarizing data in a table......................................................................... 232 Using formulas within a table....................................................................... 234 Referencing data in a table........................................................................... 235 Correcting Common Formula Errors........................................................................ 237 Handling circular references......................................................................... 238 Specifying when formulas are calculated........................................................ 238 Using Advanced Naming Techniques....................................................................... 239 Using names for constants............................................................................ 240 Using names for formulas............................................................................. 240 Using range intersections............................................................................. 241 Applying names to existing references........................................................... 243 Working with Formulas......................................................................................... 244 Not hard-­coding values................................................................................ 244 Using the Formula bar as a calculator............................................................ 244 Making an exact copy of a formula................................................................ 244 Converting formulas to values....................................................................... 245 Chapter 10: Understanding and Using Array Formulas...........................247 Understanding Legacy Array Formulas.................................................................... 248 Example of a legacy array formula................................................................. 248 Editing legacy array formulas....................................................................... 249 Introducing Dynamic Arrays.................................................................................. 250 Understanding spill ranges........................................................................... 252 Referencing spill ranges............................................................................... 254 Exploring Dynamic Array Functions....................................................................... 255 The SORT function...................................................................................... 256 The SORTBY function................................................................................... 257 The UNIQUE function................................................................................... 258 The RANDARRAY function............................................................................ 259 The SEQUENCE function............................................................................... 260 The FILTER function.................................................................................... 262 Using multiple conditions with the FILTER function................................ 263 Filtering records that contain a search term.......................................... 264 The XLOOKUP function................................................................................. 265 XLOOKUP with wildcards...................................................................... 268 Chapter 11: Using Formulas for Common Mathematical Operations.................271 Calculating Percentages........................................................................................ 271 Calculating percent of goal........................................................................... 271 Calculating percent variance......................................................................... 272 Calculating percent variance with negative values........................................... 273 Calculating a percent distribution................................................................. 274 xx  Contents Calculating a running total........................................................................... 275 Applying a percent increase or decrease to values........................................... 276 Dealing with divide-­by-­zero errors................................................................. 277 Rounding Numbers............................................................................................... 278 Rounding numbers using formulas................................................................. 279 Rounding to the nearest penny..................................................................... 279 Rounding to significant digits....................................................................... 280 Counting Values in a Range................................................................................... 282 Using Excel’s Conversion Functions........................................................................ 283 Chapter 12: Using Formulas to Manipulate Text................................285 Working with Text............................................................................................... 285 Using Text Functions............................................................................................ 286 Joining text strings..................................................................................... 286 Setting text to sentence case........................................................................ 288 Removing spaces from a text string............................................................... 289 Extracting parts of a text string.................................................................... 290 Finding a particular character in a text string................................................ 291 Finding the second instance of a character..................................................... 292 Substituting text strings.............................................................................. 293 Counting specific characters in a cell............................................................. 294 Adding a line break within a formula............................................................. 295 Cleaning strange characters from text fields................................................... 296 Padding numbers with zeros......................................................................... 297 Formatting the numbers in a text string........................................................ 297 Using the DOLLAR function.......................................................................... 299 Chapter 13: Using Formulas with Dates and Times..............................301 Understanding How Excel Handles Dates and Times.................................................. 301 Understanding date serial numbers............................................................... 301 Entering dates............................................................................................ 302 Understanding time serial numbers............................................................... 303 Entering times............................................................................................ 304 Formatting dates and times.......................................................................... 305 Problems with dates.................................................................................... 306 Excel’s leap year bug........................................................................... 306 Pre-­1900 dates................................................................................... 306 Inconsistent date entries..................................................................... 307 Using Excel’s Date and Time Functions................................................................... 307 Getting the current date and time................................................................. 308 Calculating age........................................................................................... 308 Calculating the number of days between two dates......................................... 309 Calculating the number of workdays between two dates................................... 310 Using NETWORKDAYS.INTL................................................................... 310 xxi Contents Generating a list of business days excluding holidays...................................... 311 Extracting parts of a date............................................................................. 313 Calculating number of years and months between dates................................... 314 Converting dates to Julian date formats......................................................... 315 Calculating the percent of year completed and remaining................................ 316 Returning the last date of a given month....................................................... 317 Using the EOMONTH function............................................................... 318 Calculating the calendar quarter for a date..................................................... 318 Calculating the fiscal quarter for a date......................................................... 319 Returning a fiscal month from a date............................................................ 320 Calculating the date of the Nth weekday of the month.................................... 321 Calculating the date of the last weekday of the month.................................... 322 Extracting parts of a time............................................................................. 323 Calculating elapsed time.............................................................................. 324 Rounding time values.................................................................................. 325 Converting decimal hours, minutes, or seconds to a time................................. 326 Adding hours, minutes, or seconds to a time.................................................. 326 Chapter 14: Using Formulas for Conditional Analysis............................329 Understanding Conditional Analysis....................................................................... 329 Checking if a simple condition is met............................................................ 329 Checking for multiple conditions................................................................... 330 Validating conditional data.......................................................................... 331 Looking up values.............................................................................. 332 Checking if Condition1 AND Condition2 are met.............................................. 333 Referring to logical conditions in cells.................................................. 334 Checking if Condition1 OR Condition2 are met................................................ 335 Performing Conditional Calculations....................................................................... 336 Summing all values that meet a certain condition........................................... 336 Summing greater than zero................................................................. 338 Summing all values that meet two or more conditions..................................... 339 Summing if values fall between a given date range......................................... 340 Using SUMIFS..................................................................................... 341 Getting a count of values that meet a certain condition.................................. 341 Getting a count of values that meet two or more conditions............................. 342 Finding nonstandard characters............................................................ 343 Getting the average of all numbers that meet a certain condition..................... 344 Getting the average of all numbers that meet two or more conditions............... 344 Chapter 15: Using Formulas for Matching and Lookups..........................347 Introducing Lookup Formulas................................................................................ 347 Leveraging Excel’s Lookup Functions...................................................................... 348 Looking up an exact value based on a left lookup column................................ 348 Looking up an exact value based on any lookup column.................................. 351 Looking up values horizontally..................................................................... 352 xxii  Contents Hiding errors returned by lookup functions.................................................... 353 Finding the closest match from a list of banded values.................................... 354 Finding the closest match with the INDEX and MATCH functions.............. 356 Looking up values from multiple tables......................................................... 357 Looking up a value based on a two-­way matrix............................................... 359 Using default values for match............................................................. 360 Finding a value based on multiple criteria...................................................... 361 Returning text with SUMPRODUCT........................................................ 362 Finding the last value in a column................................................................ 362 Finding the last number using LOOKUP................................................. 363 Chapter 16: Using Formulas with Tables and Conditional Formatting................365 Highlighting Cells That Meet Certain Criteria........................................................... 365 Highlighting cells based on the value of another cell...................................... 367 Highlighting Values That Exist in List1 but Not List2............................................... 369 Highlighting Values That Exist in List1 and List2..................................................... 371 Highlighting Based on Dates................................................................................. 372 Highlighting days between two dates............................................................ 374 Highlighting dates based on a due date......................................................... 376 Chapter 17: Making Your Formulas Error-­Free.................................379 Finding and Correcting Formula Errors.................................................................... 379 Mismatched parentheses.............................................................................. 380 Cells are filled with hash marks.................................................................... 381 Blank cells are not blank............................................................................. 381 Extra space characters................................................................................. 382 Formulas returning an error......................................................................... 382 #DIV/0! errors.................................................................................... 383 #N/A errors....................................................................................... 383 #NAME? errors.................................................................................... 384 #NULL! errors..................................................................................... 384 #NUM! errors...................................................................................... 384 #REF! errors....................................................................................... 385 #SPILL! errors.................................................................................... 385 #VALUE! errors................................................................................... 386 Operator precedence problems...................................................................... 386 Formulas are not calculated.......................................................................... 387 Problems with decimal precision................................................................... 387 “Phantom link” errors.................................................................................. 388 Using Excel Auditing Tools.................................................................................... 388 Identifying cells of a particular type............................................................. 388 Viewing formulas........................................................................................ 389 Tracing cell relationships............................................................................. 389 Identifying precedents........................................................................ 390 Identifying dependents....................................................................... 390 xxiii Contents Tracing error values..................................................................................... 391 Fixing circular reference errors..................................................................... 391 Using the background error-­checking feature................................................. 391 Using Formula Evaluator.............................................................................. 392 Searching and Replacing....................................................................................... 393 Searching for information............................................................................ 393 Replacing information................................................................................. 394 Searching for formatting.............................................................................. 395 Spell-­checking your worksheets.................................................................... 396 Using AutoCorrect................................................................................................ 396 Part III: Creating Charts and Other Visualizations 399 Chapter 18: Getting Started with Excel Charts.................................401 What Is a Chart?.................................................................................................. 401 How Excel handles charts............................................................................. 402 Embedded charts......................................................................................... 403 Chart sheets............................................................................................... 404 Parts of a chart........................................................................................... 405 Chart limitations......................................................................................... 408 Basic Steps for Creating a Chart............................................................................. 408 Creating the chart....................................................................................... 408 Switching the row and column orientation..................................................... 410 Changing the chart type.............................................................................. 410 Applying a chart layout............................................................................... 412 Applying a chart style................................................................................. 413 Adding and deleting chart elements.............................................................. 413 Formatting chart elements........................................................................... 413 Modifying and Customizing Charts......................................................................... 414 Moving and resizing a chart......................................................................... 414 Converting an embedded chart to a chart sheet.............................................. 415 Copying a chart.......................................................................................... 416 Deleting a chart.......................................................................................... 416 Adding chart elements................................................................................. 416 Moving and deleting chart elements.............................................................. 416 Formatting chart elements........................................................................... 416 Copying a chart’s formatting......................................................................... 417 Renaming a chart........................................................................................ 418 Printing charts............................................................................................ 418 Understanding Chart Types................................................................................... 419 Choosing a chart type.................................................................................. 419 Column charts............................................................................................ 421 Bar charts.................................................................................................. 423 Line charts................................................................................................. 424 xxiv  Contents Pie charts................................................................................................... 426 XY (scatter) charts...................................................................................... 427 Area charts................................................................................................. 428 Radar charts............................................................................................... 429 Surface charts............................................................................................. 430 Bubble charts............................................................................................. 430 Stock charts............................................................................................... 431 Newer Chart Types for Excel.................................................................................. 431 Histogram charts......................................................................................... 431 Pareto charts.............................................................................................. 433 Waterfall charts.......................................................................................... 434 Box & whisker charts................................................................................... 434 Sunburst charts.......................................................................................... 436 Treemap charts........................................................................................... 437 Funnel charts............................................................................................. 437 Map charts................................................................................................. 438 Chapter 19: Using Advanced Charting Techniques..............................441 Selecting Chart Elements...................................................................................... 441 Selecting with the mouse............................................................................. 442 Selecting with the keyboard......................................................................... 443 Selecting with the Chart Elements control...................................................... 443 Exploring the User Interface Choices for Modifying Chart Elements............................ 444 Using the Format task pane.......................................................................... 444 Using the chart customization buttons.......................................................... 445 Using the Ribbon........................................................................................ 446 Using the Mini toolbar................................................................................. 446 Modifying the Chart Area...................................................................................... 447 Modifying the Plot Area........................................................................................ 448 Working with Titles in a Chart............................................................................... 449 Working with a Legend......................................................................................... 450 Working with Gridlines......................................................................................... 452 Modifying the Axes.............................................................................................. 452 Modifying the value axis.............................................................................. 452 Modifying the category axis......................................................................... 456 Working with Data Series...................................................................................... 460 Deleting or hiding a data series.................................................................... 461 Adding a new data series to a chart............................................................... 462 Changing data used by a series..................................................................... 462 Changing the data range by dragging the range outline........................... 463 Using the Edit Series dialog box........................................................... 463 Editing the Series formula................................................................... 464 Displaying data labels in a chart................................................................... 465 Handling missing data................................................................................. 467 xxv Contents Adding error bars........................................................................................ 468 Adding a trendline...................................................................................... 468 Creating combination charts......................................................................... 470 Displaying a data table................................................................................ 472 Creating Chart Templates...................................................................................... 473 Chapter 20: Creating Sparkline Graphics.....................................475 Sparkline Types................................................................................................... 475 Creating Sparklines.............................................................................................. 477 Customizing Sparklines........................................................................................ 480 Sizing Sparkline cells................................................................................... 480 Handling hidden or missing data.................................................................. 480 Changing the Sparkline type........................................................................ 481 Changing Sparkline colors and line width....................................................... 481 Highlighting certain data points................................................................... 481 Adjusting Sparkline axis scaling.................................................................... 482 Faking a reference line................................................................................. 483 Specifying a Date Axis......................................................................................... 484 Auto-­Updating Sparklines..................................................................................... 486 Displaying a Sparkline for a Dynamic Range............................................................ 486 Chapter 21: Visualizing with Custom Number Formats and Shapes.................489 Visualizing with Number Formatting...................................................................... 489 Doing basic number formatting..................................................................... 489 Using shortcut keys to format numbers................................................. 490 Using the Format Cells dialog box to format numbers.............................. 491 Getting fancy with custom number formatting................................................ 492 Formatting numbers in thousands and millions...................................... 494 Hiding and suppressing zeros............................................................... 495 Applying custom format colors............................................................. 496 Formatting dates and times................................................................. 497 Using symbols to enhance reporting.............................................................. 498 Using Shapes and Icons as Visual Elements............................................................. 502 Inserting a shape........................................................................................ 502 Inserting SVG icon graphics.......................................................................... 504 Inserting 3D models.................................................................................... 504 Formatting shapes and icons........................................................................ 506 Enhancing Excel reports with shapes............................................................. 507 Creating visually appealing containers with shapes................................. 507 Layering shapes to save space.............................................................. 509 Constructing your own infographic widgets with shapes.......................... 509 Creating dynamic labels............................................................................... 510 Creating linked pictures............................................................................... 510 xxvi  Contents Using SmartArt and WordArt................................................................................. 513 SmartArt basics.......................................................................................... 513 WordArt basics............................................................................................ 514 Working with Other Graphics Types........................................................................ 515 About graphics files.................................................................................... 515 Inserting screenshots................................................................................... 516 Displaying a worksheet background image...................................................... 516 Using the Equation Editor..................................................................................... 516 Part IV: Managing and Analyzing Data 519 Chapter 22: Importing and Cleaning Data....................................521 Importing Data.................................................................................................... 521 Importing from a file................................................................................... 522 Spreadsheet file formats...................................................................... 522 Database file formats.......................................................................... 522 Text file formats................................................................................. 523 HTML files......................................................................................... 523 XML files........................................................................................... 524 Importing vs. opening................................................................................. 524 Importing a text file.................................................................................... 525 Copying and pasting data............................................................................. 528 Cleaning Up Data................................................................................................. 529 Removing duplicate rows.............................................................................. 529 Identifying duplicate rows............................................................................ 530 Splitting text.............................................................................................. 531 Using Text to Columns......................................................................... 532 Using Flash Fill.................................................................................. 533 Changing the case of text............................................................................ 536 Removing extra spaces................................................................................. 537 Removing strange characters........................................................................ 538 Converting values....................................................................................... 538 Classifying values........................................................................................ 538 Joining columns......................................................................................... 540 Rearranging columns................................................................................... 541 Randomizing the rows................................................................................. 541 Extracting a filename from a URL.................................................................. 541 Matching text in a list................................................................................. 542 Changing vertical data to horizontal data...................................................... 543 Filling gaps in an imported report................................................................. 545 Checking spelling........................................................................................ 547 Replacing or removing text in cells................................................................ 547 Adding text to cells..................................................................................... 548 xxvii Contents Fixing trailing minus signs........................................................................... 549 Following a data cleaning checklist............................................................... 549 Exporting Data.................................................................................................... 550 Exporting to a text file................................................................................ 550 CSV files............................................................................................ 550 TXT files............................................................................................ 550 PRN files............................................................................................ 551 Exporting to other file formats..................................................................... 551 Chapter 23: Using Data Validation..........................................553 About Data Validation.......................................................................................... 553 Specifying Validation Criteria................................................................................ 554 Types of Validation Criteria You Can Apply.............................................................. 555 Creating a Drop-­Down List.................................................................................... 557 Using Formulas for Data Validation Rules................................................................ 558 Understanding Cell References............................................................................... 559 Data Validation Formula Examples......................................................................... 560 Accepting text only..................................................................................... 561 Accepting a larger value than the previous cell............................................... 561 Accepting nonduplicate entries only............................................................. 561 Accepting text that begins with a specific character........................................ 561 Accepting dates by the day of the week......................................................... 562 Accepting only values that don’t exceed a total.............................................. 563 Creating a dependent list............................................................................. 563 Using Data Validation without Restricting Entry...................................................... 564 Showing an input message........................................................................... 564 Making suggested entries............................................................................. 564 Chapter 24: Creating and Using Worksheet Outlines............................567 Introducing Worksheet Outlines............................................................................. 567 Creating an Outline.............................................................................................. 570 Preparing the data...................................................................................... 571 Creating an outline automatically................................................................. 572 Creating an outline manually........................................................................ 572 Working with Outlines.......................................................................................... 574 Displaying levels......................................................................................... 574 Adding data to an outline............................................................................ 575 Removing an outline................................................................................... 575 Adjusting the outline symbols...................................................................... 575 Hiding the outline symbols.......................................................................... 575 Chapter 25: Linking and Consolidating Worksheets.............................577 Linking Workbooks.............................................................................................. 577 Creating External Reference Formulas..................................................................... 578 xxviii  Contents Understanding link formula syntax............................................................... 578 Creating a link formula by pointing............................................................... 579 Pasting links............................................................................................... 580 Working with External Reference Formulas............................................................. 580 Creating links to unsaved workbooks............................................................. 580 Opening a workbook with external reference formulas..................................... 581 Changing the startup prompt........................................................................ 582 Updating links............................................................................................ 582 Changing the link source............................................................................. 583 Severing links............................................................................................. 583 Avoiding Potential Problems with External Reference Formulas.................................. 583 Renaming or moving a source workbook........................................................ 584 Using the Save As command......................................................................... 584 Modifying a source workbook....................................................................... 584 Using Intermediary links.............................................................................. 585 Consolidating Worksheets..................................................................................... 585 Consolidating worksheets by using formulas................................................... 587 Consolidating worksheets by using Paste Special............................................. 587 Consolidating worksheets by using the Consolidate dialog box.......................... 588 Viewing a workbook consolidation example.................................................... 590 Refreshing a consolidation........................................................................... 592 Learning more about consolidation............................................................... 593 Chapter 26: Introducing PivotTables........................................595 About PivotTables................................................................................................ 595 A PivotTable example................................................................................... 596 Data appropriate for a PivotTable.................................................................. 598 Creating a PivotTable Automatically....................................................................... 600 Creating a PivotTable Manually.............................................................................. 602 Specifying the data..................................................................................... 602 Specifying the location for the PivotTable...................................................... 603 Laying out the PivotTable............................................................................ 603 Formatting the PivotTable............................................................................ 607 Modifying the PivotTable.............................................................................. 609 Seeing More PivotTable Examples........................................................................... 611 What is the daily total new deposit amount for each branch?........................... 611 Which day of the week accounts for the most deposits?................................... 611 How many accounts were opened at each branch, broken down by account type?.................................................................................... 613 How much money was used to open the accounts?.......................................... 613 What types of accounts do tellers open most often?........................................ 614 In which branch do tellers open the most checking accounts for new customers?...................................................................................... 615 Learning More..................................................................................................... 616 xxix Contents Chapter 27: Analyzing Data with PivotTables..................................617 Working with Non-Numeric Data............................................................................ 617 Grouping PivotTable Items.................................................................................... 619 Grouping items manually............................................................................. 619 Grouping items automatically....................................................................... 621 Grouping by date................................................................................ 621 Grouping by time................................................................................ 625 Using a PivotTable to Create a Frequency Distribution.............................................. 626 Creating a Calculated Field or Calculated Item......................................................... 628 Creating a calculated field............................................................................ 630 Inserting a calculated item......................................