ebook img

Advanced.Excel. Formulae & Calculations PDF

678 Pages·2017·47.294 MB·English
Save to my drive
Quick download
Download
Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.

Preview Advanced.Excel. Formulae & Calculations

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

See more

The list of books you might like

Most books are stored in the elastic cloud where traffic is expensive. For this reason, we have a limit on daily download.