Slager David. Essential Excel 2016: A Step-by-Step Guide

Apress, 2016. — 673 p. — ISBN: 978-1484221600.This 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, Essential Excel 2016 makes it easy for you to get to grips with this powerful software and what it can do.What You'll LearnHow to create amazing visualizations of your data
Easy and accurate methods for entering data
How to extract just the information you want from imported data, and manipulate it and format it the way you want
Export your results to other programs or share with others in the cloud
Organize, Summarize, Filter, and Consolidate your Data in almost any way imaginable
Audit, Validate and Protect your DataWho This Book Is ForAnyone new to Excel, or looking to take better advantage of the platform and find out its latest features.Becoming Acquainted with Excel
What Is Excel?
History of SpreadsheetsThis BookExcel Navigation Basics
Creating, Saving, and Opening Workbooks
Getting to Know the Ribbon
Ribbon Contextual Tabs
Resizing the Ribbon
Using Dialog Box Launchers
Minimizing and Hiding the Ribbon
Using Ribbon Shortcuts
Quick Access Toolbar
Switch Between Touch and Mouse Mode
Identifying the Current Cell
Entering Data into a Worksheet
Getting Help
Screen Tips
Excel’s Tell Me What You Want to Do FeatureNavigating and Working with Worksheets
Moving Between Cells Using the Keyboard
Selecting Cells
Selecting Cells Using a Mouse
Selecting Cells Using a Keyboard
Select Cells by Using Their Cell References in the Name Box
Going Directly to Any Cell
Worksheets
Naming Worksheets
Adding and Removing Worksheets
Changing a Worksheet Tab Color
Selecting Multiple Worksheets
Hiding and Unhiding Worksheets
Reordering and Copying Worksheets
Using Tab Buttons to Move Through the WorksheetsBest Ways to Enter and Edit Data
Data Types
Inserting Special Characters
How to Change Column Widths
Automatically Resize Column Widths to Fit Number of Characters in the Cell
Changing the Column Width for Multiple Columns
How to Change Row Heights
Correcting Typing Mistakes
Changing Specifi c Characters
Returning a Cell to Its Original Value
Clearing the Contents of a Cell That Has Already Been Accepted
Shortcuts for Entering and Correcting Data
Using the AutoCorrect Feature
Using AutoComplete to Enter Data
Pick from Drop-down List
AutoFill
Creating, Viewing, Editing, Deleting, and Formatting Cell Comments
How to Add a Comment
How to View a Comment
How to View all the Comments at the Same Time
How to Change the Default Name for Comments
Editing and Deleting Comments
Printing a CommentFormatting and Aligning Data
Formatting Your Text Using the Font Group
Using Bold, Italics, Underline, and Double Underline
Changing the Font and Its Size
Using the Font Group’s Dialog Box Launcher
Formatting with Color
Check Which Formats Have Been Applied to the Current Cell
Cell Borders
Formatting Numeric Data Using the Number Group
Using Default Formats
Formatting Monetary Values
Converting Values to Percent Style
Converting Values to Comma Style
Changing the Number of Decimal Places
Accessing the Format Cell Dialog Box
Aligning Data Using the Alignment Group
Fitting More Text into a Cell
Aligning and Indenting Text in a Cell
Align Text Vertically and Horizontally
Rotating Text
Using Format Painter to Copy Formatting
Using the Mini-Toolbars and the Context Menu
Inserting, Deleting, Hiding, and Unhiding Rows and Columns
Hiding and Unhiding Columns and Rows
Inserting Columns and Rows
Deleting Columns and Rows
Inserting and Deleting CellsDifferent Ways of Viewing and Printing Your Workbook
Views
Page Break Preview
Page Layout View
Printing
Creating a Print Area
Adding Additional Cells to the Print Area
Removing the Print Area
Using Paste Special for Printing
Dividing the Excel Window into Panes
Freezing Rows and Columns
Synchronizing Scrolling
Custom Views How to Create, Show, and DeleteUnderstanding Backstage
Backstage Overview
Info Group–Viewing, Adding, and Editing Information About the Workbook
Properties Pane
Protect Workbook Options
Check for Issues
New Group — Creating a New Workbook
Open Group — Open a Workbook
Opening an Existing Workbook
Options Affecting the Open Group
Save and Save As groups — Saving a Workbook Using Save or Save As
Document Recovery
Saving Workbooks with Protections: Backups and Limiting Changes
Print Group — Printing a Workbook
Selecting a Printer
Printer Settings
Share Group — Sharing Workbooks
Sharing Online with OneDrive
Sharing Files Using E-mail
Account GroupCreating and Using Formulas
Formulas
Introducing Formulas
Entering Formulas
Copying Formulas
AutoCalculate Tools
AutoSum
Average, Count Numbers, Max, Min
Viewing Formulas
Creating Named Ranges and Constants
Naming Ranges
Naming Noncontiguous Ranges
Naming Constants
Name Manager
Using Column or Row Headings for Range Names
Selecting Named Ranges Rather Than Typing Them into Formulas
Absolute Cell References
Mixed Cell References
Order of PrecedenceExcel’s Pre-existing Functions
Excel’s Built-in Functions
Function Construction
Functions That Sum Values
SUM Function
Using the Insert Function Option
SUMIF — Adds the Cells That Meet a Specifi ed Criteria
SUMIFS — Adds the Cells That Meet Multiple Criteria
IF — Returns Different Values Depending upon If a Condition Is True or False
AND - Returns TRUE if All of Its Arguments Are TRUE
OR — Returns TRUE If Any Argument Is TRUE
Nested Functions
Date Functions
TODAY Function — Returns the Current Date
NOW Function — Returns the Current Date and Time
DATE Function — Returns the Serial Number of the DATE
MONTH, DAY, and YEAR Functions
DAYS — Returns the Number of Days Between Two DatesAuditing, Validating, and Protecting Your Data
Validating Your Data and Preventing Errors
Data Validation
Evaluating Formulas
Using IFERROR
Correcting Circular References
Formula Auditing
Tracing Precedents and Dependents
Using the Watch Window
Using the Evaluate Formula Feature to Evaluate a Nested Function One Step at a Time
Proofreading Cell Values — Have Excel Read Back Your Entries
Spell Checking
Thesaurus
Protect Worksheets and Cells from Accidental or Intentional Changes
Protect Your Data at the Worksheet Level
Protect Your Data at the Cell LevelUsing Hyperlinks, Combining Text, and Working with the
Status Bar
Working with Hyperlinks
Concatenation and Flash Fill
Using the Status Bar
Cell Mode
Flash Fill Blank Cells and Flash Fill Changed Cells
Caps Lock, Num Lock
Scroll Lock
Fixed Decimal
Overtype Mode
End Mode
Macro Recording
Selection Mode
Page Number
Average, Count, Numerical Count, Minimum, Maximum, Sum
View Shortcuts
Zoom and Zoom SliderTransferring and Duplicating Data to Other Locations
Moving and Copying Data
Moving and Copying Cells Using the Drag-and-Drop Method
Moving and Copying Cells Using the Cut and Copy Buttons
Moving and Copying Cells Using the Keyboard
Paste Button Gallery
Copy Data to Other Worksheets Using Fill Across Worksheets
Copy Data from One Workbook to Another
Paste Special
Using Paste Special
Using Paste Special to Transpose Rows and Columns
Using Paste Special to Perform Calculations
Inserting Copied or Moved Cells
Insert Copied Cells
Insert Cut Cells
Using the Microsoft Offi ce Clipboard
Entering Data into Multiple Worksheets at the Same TimeWorking with Tables
Creating and Formatting Tables
Sort and Filter a Table
Adding to the Excel Table
Filtering Data with a Slicer
Using Themes
Applying and Defi ning Cell Styles
Conditional FormattingWorking with Charts
Chart Types
Creating and Modifying Charts
Pie Charts
The Standard Pie Chart
Pie of Pie Subtype
Combination Chart
Hierarchical Charts
Treemap Chart
Sunburst Chart
SparklinesImporting Data
Importing Data into Excel
Importing Text Files
Delimited Text Files
Fixed-Width Text Files
Importing Data from an Access Database
Importing Data from a Web Site
Importing Data Using a QueryUsing PivotTables and PivotCharts
Working with PivotTables
Creating a PivotChart
Creating PivotTable on a Relational DatabaseEnhancing Workbooks with Multimedia
Adding Pictures to the Worksheet
Using Screenshot
Working with WordArt
Adding and Modifying Shapes
Using SmartArt
Inserting Sound into a Worksheet
Inserting Video into a Worksheet.
Using Bing MapsAutomating Task with Macros
Creating (Recording) a Macro
The Problem with Absolute Cell References
Saving a Macro-Enabled Workbook
Creating a Macro Using Relative Cell References
Adding Macros to the Quick Access Toolbar and Other Objects
Sharing the Personal Workbook with Others
Looking at VBA Code
Creating Macros from Code