3 ページ
Essential Excel 2016: A Step-by-Step Guide David Slager Fort Wayne, Indiana USA ISBN-13 (pbk): 978-1-4842-2160-0 ISBN-13 (electronic): 978-1-4842-2161-7 DOI 10.1007/978-1-4842-2161-7 Library of Congress Control Number: 2016959787 Copyright © 2016 by David Slager This work is subject to copyright. All rights are reserved by the Publisher, whether the whole or part of the material is concerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproduction on microfilms or in any other physical way, and transmission or information storage and retrieval, electronic adaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed. Exempted from this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material supplied specifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaser of the work. Duplication of this publication or parts thereof is permitted only under the provisions of the Copyright Law of the Publisher's location, in its current version, and permission for use must always be obtained from Springer. Permissions for use may be obtained through RightsLink at the Copyright Clearance Center. Violations are liable to prosecution under the respective Copyright Law. Trademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with every occurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashion and to the benefit of the trademark owner, with no intention of infringement of the trademark. The use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identified as such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rights. While the advice and information in this book are believed to be true and accurate at the date of publication, neither the authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that may be made. The publisher makes no warranty, express or implied, with respect to the material contained herein. Managing Director: Welmoed Spahr Acquisitions Editor: Gwenan Spearing Development Editor: Chris Nelson Technical Reviewer: Ramona Gault and Fabio Claudio Ferracchiati Editorial Board: Steve Anglin, Pramila Balen, Laura Berendson, Aaron Black, Louise Corrigan, James DeWolf, Jonathan Gennick, Todd Green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James Markham, Susan McDermott, Matthew Moodie, Natalie Pao, Gwenan Spearing Coordinating Editor: Nancy Chen Copy Editor: Lori Jacobs Compositor: SPi Global Indexer: SPi Global Artist: SPi Global, cover image courtesy of Freepik. Distributed to the book trade worldwide by Springer Science+Business Media New York, 233 Spring Street, 6th Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax (201) 348-4505, e-mail orders-ny@springer-sbm.com, or visit www.springer.com. Apress Media, LLC is a California LLC and the sole member (owner) is Springer Science + Business Media Finance Inc (SSBM Finance Inc). SSBM Finance Inc is a Delaware corporation. For information on translations, please e-mail rights@apress.com, or visit www.apress.com. Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional use. eBook versions and licenses are also available for most titles. For more information, reference our Special Bulk Sales–eBook Licensing web page at www.apress.com/bulk-sales. Any source code or other supplementary materials referenced by the author in this text is available to readers at www.apress.com. For detailed information about how to locate your book’s source code, go to www.apress.com/source-code/. Printed on acid-free paper
4 ページ
I am dedicating this book to my parents, Andrew and Carrie (Sterk) Slager. My parents sacrificed so much for their children. My mother was the world’s best listener. I am also dedicating it to my wife, Annette Slager, who helped me with editing the book, and my two wonderful children, Rhianna and Marten.
5 ページ
Contents at a Glance About the Author ...................................................................................................xvii About the Technical Reviewers ..............................................................................xix Acknowledgments ..................................................................................................xxi ■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 v
6 ページ
■ CONTENTS AT A GLANCE ■Chapter 15: Using PivotTables and PivotCharts ................................................ 541 ■Chapter 16: Enhancing Workbooks with Multimedia ........................................ 591 ■Chapter 17: Automating Task with Macros ....................................................... 643 Index..................................................................................................................... 665 vi
7 ページ
Contents About the Author ...................................................................................................xvii About the Technical Reviewers ..............................................................................xix Acknowledgments ..................................................................................................xxi ■Chapter 1: Becoming Acquainted with Excel......................................................... 1 What Is Excel? ................................................................................................................. 1 History of Spreadsheets ................................................................................................... 2 This Book.......................................................................................................................... 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
8 ページ
■ 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
9 ページ
■ 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
10 ページ
■ 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
11 ページ
■ 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
12 ページ
■ 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
13 ページ
■ 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
14 ページ
■ 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 Offi ce 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 Defi ning Cell Styles................................................................................. 429 Conditional Formatting................................................................................................. 432 Summary...................................................................................................................... 444 xiv
15 ページ
■ 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
16 ページ
■ 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
17 ページ
About the Author David Slager’s career has involved working with technology, creating documentation, authoring, planning, programming, and training. He enjoys working with analytics and solving problems. He has taught students of all ages: elementary, middle school, high school, and college students; adult education; and corporate training. As a consultant, he developed major e-learning training projects for agriculture and steel businesses. He designed a simulation program that trained feed market managers to use analytics to improve their position in the market. Steel mill executives, managers, staff, and floor workers used his training program to learn the Order Fulfillment System. He consulted with and trained school officials and teachers from dozens of private and public schools on using technology in the classroom. David has a Master of Science in Education, specializing in Instructional Media Development, a bachelor’s degree in Organizational Management, and an associate’s degree in Accounting. He is A+ and Network+ certified. He is a CDP (Certified Data Processor) and a CCP (Certified Computer Professional). He is a Master Certified Microsoft Office User Specialist certified in all Microsoft Office Products. He was a computer programmer and involved in computer operations for many years. He was also the computer department head of a college for many years. David created the web site www.professoroffice.com. xvii
18 ページ
About the Technical Reviewers Fabio Claudio Ferracchiati is a Senior Consultant and a Senior Analyst/Developer using Microsoft technologies. He works at BluArancio S.p.A (www.bluarancio.com) as Senior Analyst/Developer and Microsoft Dynamics CRM Specialist. He is a Microsoft Certified Solution Developer for .NET, a Microsoft Certified Application Developer for .NET, a Microsoft Certified Professional, and a prolific author and technical reviewer. Over the past ten years, he has written articles for Italian and international magazines and co-authored more than ten books on a variety of computer topics. Ramona Gault is a freelance writer and editor who worked for over a decade as a technical editor in the Microsoft Office group in Redmond, Washington, where she became a fierce advocate for the end user. xix
19 ページ
Acknowledgments I would like to extend a special thank you to Chris Nelson, the development editor, for helping me prepare this book. xxi
20 ページ
CHAPTER 1 Becoming Acquainted with Excel Excel is a powerful and versatile spreadsheet program that can be used for both business and personal needs. It has amazing capabilities that you can use to make any type of data you record more streamlined and productive. In the first chapter, you’ll learn the basics of creating worksheets, and how to use the Ribbon, a feature which drives the user-friendly resources in Excel. After reading and working through this chapter you should be able to • Know what Excel is and know some of its capabilities • Create, save, and open a workbook • Identify the current cell • Use the Ribbon • Use and customize the Quick Access Toolbar • Enter data in a worksheet • Get help by using Screen Tips and the Tell me what you want to do features What Is Excel? Excel is an electronic spreadsheet program. A spreadsheet is a grid of cells organized into rows and columns in which you enter and store your data. Excel can meet both your personal and professional needs. Using Excel, you can do all of the following: • Create, edit, sort, analyze, summarize, and format data as well as graph it. • Keep budgets and handle payroll • Track investments, loans, sales, inventory, etc. • Perform What-If Analysis to determine such things as “if the price of gas went up 20 cents per gallon” by how much would that decrease my profit, or “if I extend my loan payments from 15 years to 20 years” by how much will that affect my monthly payments, total payments, and total interest. One can improve the appearance of a spreadsheet or better convey what you want a spreadsheet to say by adding pictures, clip art, shapes, smart art, video, and audio. Electronic supplementary material The online version of this chapter (doi:10.1007/978-1-4842-2161-7_1) contains supplementary material, which is available to authorized users. © David Slager 2016 1 D. Slager, Essential Excel 2016, DOI 10.1007/978-1-4842-2161-7_1
コメント (0)
まだコメントはありません。最初のコメントを書きましょう！