A Comprehensive Guide
Advanced
Excel MICROSOFT
Formulae & Calculations
George Walter
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.
Contents at a Glance ■ Chapter 1: Becoming Acquainted with Excel ...................................................1 ■ Chapter 2: Navigating and Working with Worksheets............................. 27 ■ Chapter 3: Best Ways to Enter and Edit Data ................................................. 49 ■ Chapter 4: Formatting and Aligning Data ................................................ 89 ■ Chapter 5: Different Ways of Viewing and Printing Your Workbook ........... 145 ■ Chapter 6: Understanding Backstage .......................................................... 173 ■ Chapter 7: Creating and Using Formulas ..................................................... 219 ■ Chapter 8: Excel’s Pre-existing Functions ............................................ 265 ■ Chapter 9: Auditing, Validating, and Protecting Your Data .................... 303 ■ Chapter 10: Using Hyperlinks, Combining Text, and Working with the
Status Bar ................................................................................................ 343 ■ Chapter 11: Transferring and Duplicating Data to Other Locations ............ 373 ■ Chapter 12: Working with Tables................................................................ 411 ■ Chapter 13: Working with Charts ........................................................... 445 ■ Chapter 14: Importing Data .................................................................... 503 ■ Chapter 15: Using PivotTables and PivotCharts ..........................................541 ■ Chapter 16: Enhancing Workbooks with Multimedia ............................ 591 ■ Chapter 17: Automating Task with Macros ............................................ 643
Index ........................................................................................................... 665
v
Contents ■ Chapter 1: Becoming Acquainted with Excel ...................................................1
What Is Excel? .......................................................................................................1 History of Spreadsheets.........................................................................................2 Excel Navigation Basics .........................................................................................2 Creating, Saving, and Opening Workbooks ...........................................................4 Getting to Know the Ribbon ...................................................................................9 Ribbon Contextual Tabs ..................................................................................................................... 9 Resizing the Ribbon ............................................................................................................... 10 Using Dialog Box Launchers .................................................................................................. 11 Minimizing and Hiding the Ribbon .......................................................................................... 12 Using Ribbon Shortcuts.......................................................................................................... 13
Quick Access Toolbar ..................................................................................................15 Switch Between Touch and Mouse Mode ............................................................................... 16
Identifying the Current Cell. ................................................................................ 19 Entering Data into a Worksheet .......................................................................... 19 Getting Help ........................................................................................................ 21 Screen Tips ............................................................................................................................ 22 Excel’s Tell Me What You Want to Do Feature ........................................................................ 22
Summary............................................................................................................. 26
vii
■ CONTENTS
■ Chapter 2: Navigating and Working with Worksheets .............................27
Moving Between Cells Using the Keyboard ......................................................... 27 Selecting Cells ..................................................................................................... 30 Selecting Cells Using a Mouse .............................................................................................. 31 Selecting Cells Using a Keyboard .......................................................................................... 33 Select Cells by Using Their Cell References in the Name Box ................................................ 35 Going Directly to Any Cell ...................................................................................................... 37
Worksheets.......................................................................................................... 38 Naming Worksheets............................................................................................................... 39 Adding and Removing Worksheets ........................................................................................ 39 Changing a Worksheet Tab Color .......................................................................................... 41 Selecting Multiple Worksheets ............................................................................................... 42 Hiding and Unhiding Worksheets ........................................................................................... 42 Reordering and Copying Worksheets .................................................................................... 43 Using Tab Buttons to Move Through the Worksheets ............................................................. 44
Summary ............................................................................................................. 48 ■ Chapter 3: Best Ways to Enter and Edit Data ................................................. 49
Data Types ....................................................................................................................49 Inserting Special Characters ............................................................................... 51 How to Change Column Widths ........................................................................... 55 Automatically Resize Column Widths to Fit Number of Characters in the Cell ......................... 58 Changing the Column Width for Multiple Columns ................................................................. 58
How to Change Row Heights............................................................................... 61 Correcting Typing Mistakes ................................................................................. 63 Changing Specific Characters ............................................................................................... 63 Returning a Cell to Its Original Value...................................................................................... 63 Clearing the Contents of a Cell That Has Already Been Accepted .......................................... 64
viii
■ CONTENTS
Shortcuts for Entering and Correcting Data ........................................................ 65 Using the AutoCorrect Feature ............................................................................................... 65 Using AutoComplete to Enter Data......................................................................................... 70 Pick from Drop-down List ....................................................................................................... 71 AutoFill ................................................................................................................................... 73
Creating, Viewing, Editing, Deleting, and Formatting Cell Comments .................. 82 How to Add a Comment.......................................................................................................... 82 How to View a Comment. ....................................................................................................... 83 How to View all the Comments at the Same Time................................................................... 83 How to Change the Default Name for Comments ................................................................... 83 Editing and Deleting Comments ............................................................................................. 84 Printing a Comment................................................................................................................ 84
Summary............................................................................................................. 88 ■ Chapter 4: Formatting and Aligning Data ................................................ 89
Formatting Your Text Using the Font Group ........................................................ 89 Using Bold, Italics, Underline, and Double Underline .............................................................. 90 Changing the Font and Its Size .............................................................................................. 90 Using the Font Group’s Dialog Box Launcher ......................................................................... 93 Formatting with Color .................................................................................................. 93 Check Which Formats Have Been Applied to the Current Cell ................................................ 96 Cell Borders ........................................................................................................................... 99
Formatting Numeric Data Using the Number Group ......................................... 110 Using Default Formats.......................................................................................................... 111 Formatting Monetary Values ................................................................................................ 112 Converting Values to Percent Style ...................................................................................... 113 Converting Values to Comma Style ...................................................................................... 114 Changing the Number of Decimal Places ............................................................................. 114 Accessing the Format Cell Dialog Box ................................................................................. 114
Aligning Data Using the Alignment Group ......................................................... 118 Fitting More Text into a Cell .................................................................................................. 119 Aligning and Indenting Text in a Cell ..................................................................................... 123 ix
■ CONTENTS
Align Text Vertically and Horizontally .................................................................................... 123 Rotating Text.................................................................................................................................... 124
Using Format Painter to Copy Formatting ......................................................... 129 Using the Mini-Toolbars and the Context Menu ................................................. 132 Inserting, Deleting, Hiding, and Unhiding Rows and Columns ........................... 134 Hiding and Unhiding Columns and Rows ............................................................................. 134 Inserting Columns and Rows ............................................................................................... 139 Deleting Columns and Rows ................................................................................................ 139
Inserting and Deleting Cells ............................................................................... 140 Summary ........................................................................................................... 143 ■ Chapter 5: Different Ways of Viewing and Printing Your Workbook ............ 145
Views ................................................................................................................. 145 Page Break Preview............................................................................................................. 146 Page Layout View ................................................................................................................ 151
Printing............................................................................................................... 157 Creating a Print Area ............................................................................................................ 157 Adding Additional Cells to the Print Area .............................................................................. 158 Removing the Print Area ...................................................................................................... 158 Using Paste Special for Printing ........................................................................................... 160
Dividing the Excel Window into Panes ............................................................... 162 Freezing Rows and Columns............................................................................. 164 Synchronizing Scrolling ..................................................................................... 165 Custom Views How to Create, Show, and Delete ............................................... 168 Summary ........................................................................................................... 172 ■ Chapter 6: Understanding Backstage ........................................................... 173
Backstage Overview .......................................................................................... 173 Info Group–Viewing, Adding, and Editing Information About the Workbook......... 174 Properties Pane ................................................................................................................... 175 Protect Workbook Options ................................................................................................... 178 Check for Issues .................................................................................................................. 180 x
■ CONTENTS
New Group—Creating a New Workbook .......................................................... 185 Open Group—Open a Workbook ...................................................................... 188 Opening an Existing Workbook ............................................................................................ 188 Options Affecting the Open Group........................................................................................ 191
Save and Save As groups—Saving a Workbook Using Save or Save As............ 192 Document Recovery............................................................................................................. 194 Saving Workbooks with Protections: Backups and Limiting Changes ................................... 201
Print Group—Printing a Workbook .................................................................... 205 Selecting a Printer ................................................................................................................ 206 Printer Settings..................................................................................................................... 207
Share Group—Sharing Workbooks .................................................................. 213 Sharing Online with OneDrive .............................................................................................. 214 Sharing Files Using E-mail ................................................................................................... 217
Account Group .................................................................................................. 218 Summary........................................................................................................... 218 ■ Chapter 7: Creating and Using Formulas ..................................................... 219
Formulas ........................................................................................................... 219 Introducing Formulas ........................................................................................................... 219 Entering Formulas ................................................................................................................ 221 Copying Formulas ................................................................................................................ 226
AutoCalculate Tools .................................................................................................. 229 AutoSum .............................................................................................................................. 229 Average, Count Numbers, Max, Min ..................................................................................... 233
Viewing Formulas ............................................................................................. 237 Creating Named Ranges and Constants .......................................................... 238 Naming Ranges ................................................................................................................... 238 Naming Noncontiguous Ranges........................................................................................... 239 Naming Constants................................................................................................................ 240 Name Manager .................................................................................................................... 241 Using Column or Row Headings for Range Names .............................................................. 242 Selecting Named Ranges Rather Than Typing Them into Formulas ..................................... 244 xi
■ CONTENTS
Absolute Cell References .................................................................................. 254 Mixed Cell References....................................................................................... 259 Order of Precedence ............................................................................................................ 263
Summary ........................................................................................................... 264 ■ Chapter 8: Excel’s Pre-existing Functions ............................................265
Excel’s Built-in Functions ................................................................................... 265 Function Construction ........................................................................................ 267 Functions That Sum Values ............................................................................... 267 SUM Function ...................................................................................................................... 268 Using the Insert Function Option .......................................................................................... 270 SUMIF—Adds the Cells That Meet a Specified Criteria ........................................................ 276 SUMIFS—Adds the Cells That Meet Multiple Criteria........................................................... 278
IF—Returns Different Values Depending upon If a Condition Is True or False ...... 282 AND - Returns TRUE if All of Its Arguments Are TRUE ...................................... 286 OR—Returns TRUE If Any Argument Is TRUE .................................................. 286 Nested Functions ............................................................................................... 287 Date Functions................................................................................................... 292 TODAY Function—Returns the Current Date ....................................................................... 293 NOW Function—Returns the Current Date and Time ........................................................... 295 DATE Function—Returns the Serial Number of the DATE......................................................... 297 MONTH, DAY, and YEAR Functions..................................................................................... 298 DAYS—Returns the Number of Days Between Two Dates ................................................... 300
Summary ........................................................................................................... 302 ■ Chapter 9: Auditing, Validating, and Protecting Your Data ....................303
Validating Your Data and Preventing Errors ...................................................... 303 Data Validation ..................................................................................................................... 304
Evaluating Formulas .......................................................................................... 319 Using IFERROR .................................................................................................................... 319 Correcting Circular References ............................................................................................ 320
xii
■ CONTENTS
Formula Auditing ............................................................................................... 322 Tracing Precedents and Dependents ................................................................................... 322 Using the Watch Window...................................................................................................... 325 Using the Evaluate Formula Feature to Evaluate a Nested Function One Step at a Time ......... 327
Proofreading Cell Values—Have Excel Read Back Your Entries ....................... 331 Spell Checking .................................................................................................. 333 Thesaurus ......................................................................................................... 334 Protect Worksheets and Cells from Accidental or Intentional Changes .............. 336 Protect Your Data at the Worksheet Level ............................................................................ 336 Protect Your Data at the Cell Level ....................................................................................... 340
Summary........................................................................................................... 341 ■ Chapter 10: Using Hyperlinks, Combining Text, and Working with the
Status Bar ................................................................................................ 343 Working with Hyperlinks ............................................................................ 343 Concatenation and Flash Fill ............................................................................ 356 Using the Status Bar. ........................................................................................ 364 Cell Mode ............................................................................................................................. 366 Flash Fill Blank Cells and Flash Fill Changed Cells .............................................................. 366 Caps Lock, Num Lock .......................................................................................................... 366 Scroll Lock............................................................................................................................ 366 Fixed Decimal ...................................................................................................................... 367 Overtype Mode..................................................................................................................... 367 End Mode ............................................................................................................................. 367 Macro Recording .................................................................................................................. 367 Selection Mode .................................................................................................................... 367 Page Number ....................................................................................................................... 367 Average, Count, Numerical Count, Minimum, Maximum, Sum .............................................. 367 View Shortcuts ..................................................................................................................... 367 Zoom and Zoom Slider ......................................................................................................... 368
Summary........................................................................................................... 371 xiii
■ CONTENTS
■ Chapter 11: Transferring and Duplicating Data to Other Locations............. 373
Moving and Copying Data ................................................................................. 373 Moving and Copying Cells Using the Drag-and-Drop Method............................................... 374 Moving and Copying Cells Using the Cut and Copy Buttons ................................................. 376 Moving and Copying Cells Using the Keyboard.................................................................... 377 Paste Button Gallery ............................................................................................................ 379 Copy Data to Other Worksheets Using Fill Across Worksheets ............................................ 385 Copy Data from One Workbook to Another .......................................................................... 387
Paste Special ..................................................................................................... 390 Using Paste Special ............................................................................................................. 391 Using Paste Special to Transpose Rows and Columns ........................................................ 393 Using Paste Special to Perform Calculations. ...................................................................... 395
Inserting Copied or Moved Cells ........................................................................ 400 Insert Copied Cells ............................................................................................................... 400 Insert Cut Cells .................................................................................................................... 403
Using the Microsoft Office Clipboard ................................................................. 404 Entering Data into Multiple Worksheets at the Same Time ................................ 405 Summary ........................................................................................................... 409 ■ Chapter 12: Working with Tables ............................................................... 411
Creating and Formatting Tables .............................................................................. 411 Sort and Filter a Table ............................................................................................... 413 Adding to the Excel Table ......................................................................................... 414 Filtering Data with a Slicer .........................................................................422 Using Themes .................................................................................................... 424 Applying and Defining Cell Styles ...................................................................... 429 Conditional Formatting ...................................................................................... 432 Summary ........................................................................................................... 444
xiv
■ CONTENTS
■ Chapter 13: Working with Charts ........................................................... 445
Chart Types ................................................................................................................ 445 Creating and Modifying Charts ......................................................................... 448 Pie Charts ......................................................................................................... 465 The Standard Pie Chart........................................................................................................ 465 Pie of Pie Subtype. ............................................................................................................... 469
Combination Chart ............................................................................................ 477 Hierarchical Charts ........................................................................................... 481 Treemap Chart ..................................................................................................................... 481 Sunburst Chart ..................................................................................................................... 487
Sparklines ......................................................................................................... 495 Summary........................................................................................................... 501 ■ Chapter 14: Importing Data .................................................................... 503
Importing Data into Excel .................................................................................. 503 Importing Text Files ........................................................................................... 504 Delimited Text Files .............................................................................................................. 504 Fixed-Width Text Files .......................................................................................................... 518
Importing Data from an Access Database......................................................... 521 Importing Data from a Web Site ........................................................................ 524 Importing Data Using a Query........................................................................... 527 Summary........................................................................................................... 539 ■ Chapter 15: Using PivotTables and PivotCharts ......................................... 541
Working with PivotTables.................................................................................. 541 Creating a PivotChart ........................................................................................ 569 Creating PivotTable on a Relational Database ................................................. 579 Summary ........................................................................................................... 589
xv
■ CONTENTS
■ Chapter 16: Enhancing Workbooks with Multimedia ............................591
Adding Pictures to the Worksheet ...................................................................... 591 Using Screenshot .............................................................................................. 599 Working with WordArt ........................................................................................ 603 Adding and Modifying Shapes ........................................................................... 609 Using SmartArt .................................................................................................. 625 Inserting Sound into a Worksheet ...................................................................... 631 Inserting Video into a Worksheet. ...................................................................... 633 Using Bing Maps ................................................................................................ 634 Summary ........................................................................................................... 641 ■ Chapter 17: Automating Task with Macros ............................................643
Creating (Recording) a Macro ........................................................................... 643 The Problem with Absolute Cell References...................................................... 646 Saving a Macro-Enabled Workbook .................................................................. 648 Creating a Macro Using Relative Cell References ............................................. 650 Adding Macros to the Quick Access Toolbar and Other Objects ......................... 652 Sharing the Personal Workbook with Others ..................................................... 658 Looking at VBA Code ......................................................................................... 658 Creating Macros from Code. .............................................................................. 661 Summary ........................................................................................................... 664 Index ...........................................................................................................665
xvi