Excel 2016 in easy steps

by Michael Price, Mike McGrath

Overview

Excel 2016 in easy steps will help you get to grips with the latest version of this popular spreadsheet application. Areas covered include:

Creating, editing and manipulating worksheets
Formulas, Functions and Pivot Tables
Handy templates to give you a head start
Macros for everyday tasks to save time
Charts to get an overview of your data
Linking workbooks for automatic updates
Using the Excel Online app to share and collaborate on spreadsheets and workbooks
Adding images and illustrations to your spreadsheet to impress your colleagues!


Whether you’re upgrading to Excel 2016 or new to the spreadsheet concept, use this guide to learn the key features constructively and get more out of Excel 2016 – in easy steps!

Product Details

ISBN-13:	9781840786514
Publisher:	In Easy Steps Limited
Publication date:	02/23/2016
Series:	In Easy Steps
Pages:	192
Product dimensions:	7.20(w) x 8.80(h) x 0.40(d)

About the Author

Michael Price is an accomplished In Easy Steps author, IT journalist and systems consultant with a wide experience of computing systems ranging from mainframes to personal computers. He lives in Ascot, U.K.

Table of Contents

1 Introduction 7

The Spreadsheet Concept 8

Microsoft Excel 10

Microsoft Office 2016 11

System Requirements 12

Getting Office 2016 13

Excel 2016 and Windows 10 14

The Office 2016 Ribbon 16

Exploring Excel 2016 18

Excel Online App 20

2 Begin with Excel 21

The Excel Window 22

Create a Workbook 24

Add Data to the Worksheet 25

Build the Worksheet 26

Fill Cells 28

Complete the Worksheet 29

Format the Text 30

Format the Numbers 31

Print the Worksheet 32

Insert, Copy and Paste 34

Excel Help 35

Contextual Help 36

Excel File Formats 37

3 Manage Data 39

Use Existing Data 40

Import Data 42

Navigate the Worksheet 44

Scroll with the Wheel Mouse 46

Keystrokes and Touch 47

Sort Rows 48

Find Entries 49

Filter Information 50

Remove Duplicate Entries 52

Check Spelling 53

Freeze Headers and Labels 54

Hide Columns or Rows 55

Protect a Worksheet 56

4 Formulas and Functions 57

Number Formats 58

Text Formats 60

Relative References 61

Absolute References 62

Name References 63

Operators 64

Calculation Sequence 65

Functions 66

AutoSum 68

Formula Errors 69

Add Comments 70

5 Excel Tables 71

Create an Excel Table 72

Edit Tables 74

Table Styles 75

Table Totals 76

Count Unique Entries 77

Structured References 78

Calculated Columns 79

Insert Rows 80

Custom Sort 82

Print a Table 83

Summarize a Table 84

Convert to a Range 86

6 Advanced Functions 87

Function Library 88

Logical Functions 89

Lookup/Reference Functions 90

Financial Functions 92

Date & Time Functions 94

Text Functions 96

Math SL Trig Functions 98

Random Numbers 100

Other Functions: Statistical 101

Other Functions: Engineering 102

Excel Add-ins 103

Evaluate Formula 104

7 Control Excel 105

Audit Formulas 106

Protect Formulas 108

Check for Errors 109

Backup 111

AutoSave and AutoRecover 112

Startup Switches 113

Create a Shortcut 114

Ribbon Key Tips 115

Using Key Tips 116

Collapse the Ribbon 118

Quick Access Toolbar 119

Mini Toolbar 120

Print Worksheets 121

8 Charts 123

Create a Chart 124

Default Chart Type 125

Change Chart Layout 127

Legend and Data Table 128

Change Chart Type 129

Pie Chart 130

3-D Pie Chart 132

3-D Column Chart 133

Share Price Data 134

Line Chart 135

Stock Chart 136

Mixed Types 137

Print Charts 138

9 Macros in Excel 139

Macros 140

Create Macros 141

Record a Macro 142

Apply the Macro 144

View the Macro 145

Macro to Make a Table 146

Edit the Macro 148

Use the Macro 149

Create Macros with VBA 150

Add Macros to the Toolbar 152

Debug Macros 154

10 Templates and Scenarios 155

Templates 156

Online Templates 158

More Excel Resources 160

What-If Analysis 162

Summary Reports 164

Goal Seek 166

Optimization 167

Project Worksheet 168

Solver 169

11 Links and Connections 171

Link to Workbooks 172

Create External References 174

Styles of Reference 176

Source Workbook Changes 177

Apply the Updates 178

Turn Off the Prompt 179

Save Workbook Online 180

Using the Excel Online App 182

Excel in Word 184

Publish as PDF (or XPS) 186

Index 187