Essential Excel 2016: A Step by Step Guide

Essential Excel 2016: A Step-by-StepEnglish | 15 Dec. 2016 | ISBN: 1484221605 | 700 Pages | PDF | 35,55 MBThis book shows you how easy it is to create, edit, sort, analyze, summarize and format data as well as graph it. Loaded with screen shots, step-by-step instructions, and reader exercises, EsEssential Excel 2016: A Step-by-Step guideDavid slagerFort Wayne, IndianaUSAISBN-13(pbk:978-1-4842-2160-0ISBN-13( electronic:978-1-4842-2161-7DOI10.1007/978-1-4842-2161-7Library of congress Control Number: 2016959787Copyright o 2016 by David SlagerThis work is subject to copyright All rights are reserved by the Publisher, whether the whole or part of the material isconcerned, specifically the rights of translation, reprinting, reuse of illustrations, recitation, broadcasting, reproductionon microfilms or in any other physical way, and transmission or information storage and retrieval, electronicadaptation, computer software, or by similar or dissimilar methodology now known or hereafter developed Exemptedfrom this legal reservation are brief excerpts in connection with reviews or scholarly analysis or material suppliedspecifically for the purpose of being entered and executed on a computer system, for exclusive use by the purchaserof the work. Duplication of this publication or parts thereof is permitted only under the provisions of the CopyrightLaw 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 Rights Link at the Copyright Clearance Center. Violations are liable toprosecution under the respective Copyright Lawrademarked names, logos, and images may appear in this book. Rather than use a trademark symbol with everyoccurrence of a trademarked name, logo, or image we use the names, logos, and images only in an editorial fashionand to the benefit of the trademark owner, with no intention of infringement of the trademarkThe use in this publication of trade names, trademarks, service marks, and similar terms, even if they are not identifiedas such, is not to be taken as an expression of opinion as to whether or not they are subject to proprietary rightsWhile the advice and information in this book are believed to be true and accurate at the date of publication, neitherthe authors nor the editors nor the publisher can accept any legal responsibility for any errors or omissions that maybe made. The publisher makes no warranty, express or implied, with respect to the material contained hereinManaging Director: Welmoed SpahrAcquisitions Editor: Gwenan SpearingDevelopment Editor: Chris NelsonTechnical Reviewer: Ramona gault and Fabio claudio ferracchiatiEditorial Board: Steve Anglin, Pramila Balen, Laura Berendson, Aaron Black, Louise Corrigan, James De WolfJonathan gennick, Todd green, Robert Hutchinson, Celestin Suresh John, Nikhil Karkal, James MarkhamSusan McDermott, Matthew Moodie, Natalie Pao, Gwenan SpearingCoordinating Editor: Nancy ChenCopy Editor: Lori JacobsCompositor: SPi globaIndexer: SPi globalArtist: SPi Global, cover image courtesy of FreepikDistributed 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 comorvisitwww.springercomApressMedia,LlcisaCaliforniaLLcandthesolemember(owner)isSpringerScience+ Business Media Finance Inc(SSBM Finance Inc). SSBM Finance Inc is a Delaware corporationorinformationontranslationspleasee-mailrightsqapress.comorvisitwww.apress.comApress and friends of ed books may be purchased in bulk for academic, corporate, or promotional usee Book versions and licenses are also available for most titles For more information reference our Special bulkSales-ebookLicensingwebpageatwww.apress.com/bulk-salesAny source code or other supplementary materials referenced by the author in this text is available to readersatwww.apress.comFordetailedinformationabouthowtolocateyourbookssourcecodegotowww.apress.com/source-code/.Printed on acid-free paperI am dedicating this book to my parents, Andrew and carrie sterk) SlagerMy parents sacrificed so much for their children. My mother was the worlds 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 martenContents at a glanceAbout the author■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■国■■国■■a■■■■■About the technical reviewers uxixAcknowledgments mmmmmaamaanmaanmmaanmmmamammmmammaammmaanmannmmaammaa XXiChapter 1: Becoming acquainted with Excel■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■Chapter 2: Navigating and Working with Worksheets■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■27Chapter 3: Best Ways to Enter and Edit Data aan49Chapter 4: Formatting and Aligning Data mmammaamanamaaammn. 89Chapter 5: Different Ways of viewing and Printing Your Workbook mm 145Chapter 6: Understanding Backstage■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■口■■■■■■■■■■■■■■■■■■■■口■■■■■■■■173Chapter 7: Creating and Using Formulasammmmmmmmmmmmm 219Chapter 8: Excels Pre-existing Functions RBBRIIRRRRIIRRIRIIRIIIIIRRRIan 265Chapter 9: Auditing, Validating, and Protecting Your Data mamammmanmnann 303Chapter 10: Using HyperlinkS, Combining Text, and Working with theStatus bar343Chapter 11: Transferring and duplicating Data to other Locations■■■■■■■■■国■■■■■■■n373Chapter 12: Working with Tables.mamaamaammam am 411Chapter 13: Working with charts aaastaasaamaamaamamammamaaaaaamanttaaaasaanan 445Chapter 14: Importing Data503CONTENTS AT A GLANCEChapter 15: Using PivotTables and Pivot charts mmm mamaaanmnn 541Chapter 16: Enhancing Workbooks with Multimedia mmamammaaann■■■■■■■■■■圆■■■■■a■■■圆591Chapter 17: Automating Task with Macros643Indexuu665ContentsAbout the author■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■国■■国■■a■■■■■About the technical reviewers uxixAcknowledgmentsChapter 1: Becoming Acquainted with Excel.■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■What is excel?History of SpreadsheetsThis book2EXcel Navigation Basics eeeeeeeetaeCreating, Saving, and opening WorkbooksGetting to Know the ribbonRibbon contextual tabs24990Resizing the ribbonUsing Dialog BoX Launchers.........................................11Minimizing and Hiding the ribbon.……,…12Using Ribbon ShortcutsQuick Access Toolbar15Switch between touch and mouse mode.……16Identifying the Current Cell19Entering data into a Worksheet……19Getting Help21Screen Tips22Excels Tell Me What you want to do featureSummary…,,,,26CONTENTSChapter 2: Navigating and Working with Worksheetsu27Moving between Cells Using the Keyboard27Selecting Cells30Selecting Cells Using a mouse ................................................................31Selecting Cells Using a KeyboardSelect Cells by Using Their Cell References in the Name Box35Going Directly to Any cell37Worksheets38Naming WorksheetsAdding and removing Worksheets39Changing a Worksheet Tab ColorSelecting Multiple WorksheetsHiding and Unhiding WorksheetsReordering and Copying WorksheetsUsing Tab Buttons to Move Through the WorksheetsSummary48Chapter 3: Best Ways to Enter and Edit Data■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■a■國■a■a49Data Types49Inserting Special Characters…51How to Change Column Widths55Automatically Resize Column Widths to Fit Number of Characters in the cell58Changing the Column Width for Multiple columnsHow to Change row heightsn61Correcting Typing mistakes.................. 63Changing Specific CharactersReturning a cell to its original valueClearing the Contents of a Cell That Has already Been AcceptedV11INTENTSShortcuts for Entering and correcting data65Using the auto Correct Feature.Using auto complete to Enter Data..............................70Pick from Drop-down ListAuto FillCreating, Viewing, Editing, Deleting, and Formatting Cell Comments82How to add a comment.…ow to view a c0 mment,,,,……………How to view all the comments at the same timeHow to Change the Default Name for comments.Editing and Deleting Comments….…Printing aCommentSummary……88Chapter 4: Formatting and Aligning DataFormatting Your Text Using the Font Group9Using bold, Italics, Underline, and Double Underline90Changing the Font and its size.......dUsing the Font Group' S Dialog B0 x Launcher…......…………93Formatting with Color…日日面日自日日a日自日面日日自日面日自日面日日自日日面日自日面日日自日1日面日自日面日日自日日面日自日日日自日自日面日自日自日Check Which Formats Have Been applied to the current cell96Cell bordersFormatting Numeric Data Using the Number Group .Using default Formats111Formatting Monetary Values.........................................112Converting values to Percent Style113Converting values to Comma style114Changing the Number of Decimal Places...................................114Accessing the Format Cell Dialog box日面日面日面日日日日日日日自日日自日日自日自日日自日日自日日自日日自日日自日日自日日自日日面114Aligning Data Using the Alignment Group…………,….118Fitting more Text into a cell119Aligning and Indenting Text in a cell...IXCONTENTSAlign Text Vertically and horizontally.………123Rotating Text.....,,,,,…,…,,……124Using Format Painter to Copy Formatting129Using the Mini-Toolbars and the context Menu132Inserting, Deleting, Hiding, and Unhiding rows and columns134Hiding and Unhiding Columns and Rows134Inserting columns and rows139Deleting Columns and Rows139Inserting and Deleting cellsSummar143Chapter 5: Different Ways of viewing and Printing Your Workbook145Views145Page Break Preview146Page Layout view151Printing157Creating a Print Area...……157Adding Additional cells to the Print Area.Removing the Print AreaUsing Paste Special for Printing..160Dividing the excel window into panes162Freezing Rows and columns164Synchronizing Scrolling,165Custom Views how to create show and delete168Summary172Chapter 6: Understanding Backstage mammmmmmmmmm 173Backstage Overview173Info Group-viewing, Adding, and Editing Information about the Workbool174Properties PaneProtect Workb0 ok options…...,…….………………,178Check for issues180