Advanced.Excel. Formulae & Calculations

678 Pages·2017·47.294 MB·English

by  George Walter

A Comprehensive Guide
Advanced
Excel
MICROSOFT
Formulae & Calculations
George Walter

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 Specifi c 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 Specifi ed 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