My Excel 2016: Includes Content Update Program

Купить бумажную книгу и читать

Название: My Excel 2016: Includes Content Update Program

Автор:Jitendra Patel

Издательство: Que Publishing

Год: 2015

Страниц: 480

Язык: English

Формат: epub

Размер: 38,2Mb

My Excel 2016 is your must-have companion for getting most out of Excel 2016. This friendly, quick, full-color, 100% practical tutorial walks you through every task you'll want to do with Excel 2016

Get productive fast with Excel 2016’s updated interface

Save time and make your data easier to work with

Efficiently enter, insert, move, and manage data

Use templates to reuse work and get a jumpstart on new projects

Format worksheets and charts to make them clearer and more useful

Use intuitive sparkline charts that fit in a single cell

Build custom formulas using powerful built-in functions

Sort, filter, and consolidate data and eliminate duplication

Instantly group data and generate subtotals

Quickly summarize huge data sets with PivotTables

Find, fix, and avoid errors that break spreadsheets or cause inaccuracies

Communicate more powerfully using visual tools such as SmartArt and WordArt

Precisely control what you print and how it looks

Securely share and distribute your workbooks

Use Excel on the Web wherever you have Internet access

Every task is presented step-by-step, using carefully annotated, colorful screenshots, all numbered so there's no chance of getting lost or confused. Everything's clearly organized in modular, self-contained chapters designed to help you get started quickly. Throughout, the book is packed with helpful tips, lists, and quick solutions to the problems you're most likely to encounter.

In addition, this book is part of Que’s exciting new Content Update Program. As Microsoft updates features of Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection.

How to access the web edition: Follow the instructions in the book to learn how to register your book to access the FREE Web Edition.

Table of Contents

Introduction 1

What’s in This Book 2

Guidance for Beginners 2

Chapter 1: Understanding the Microsoft Excel Interface 5

Identifying Parts of the Excel Window 6

Using the Built-in Help 8

Perform a Search 9

Making Selections from the Ribbon 10

Customizing the Ribbon 10

Minimize the Ribbon Size 11

Add More Commands to the Ribbon 12

Customizing the QAT 15

Move the QAT to a New Location 15

Add More Commands to the QAT 15

Viewing Multiple Sheets at the Same Time 16

Arrange Multiple Sheets 17

Scroll Two Sheets Side by Side 18

Changing the Zoom on a Sheet 19

Use Excel’s Zoom Controls 19

Moving Around on a Sheet 20

Keyboard Shortcuts for Quicker Navigation 20

Selecting a Range of Cells 21

Select a Range Using the Mouse 21

Chapter 2: Working with Workbooks and Templates 25

Managing Workbooks 25

Create a New Workbook 26

Open an Existing Workbook 26

Use the Recent Workbooks List 27

Save a Workbook 28

Close a Workbook 30

Using Templates to Quickly Create New Workbooks 30

Use Microsoft’s Online Templates 31

Save a Template 32

Open a Locally Saved Template to Enter Data 33

Edit the Design of a Locally Saved Template 34

Change Personal Templates Location 35

Chapter 3: Working with Sheets 37

Adding and Deleting Sheets 37

Add a New Sheet 38

Delete a Sheet 38

Navigating and Selecting Sheets 39

Activate Another Sheet 39

Select Multiple Sheets 40

Moving or Copying Sheets 40

Move or Copy a Sheet in the Same Workbook 41

Move or Copy a Sheet Between Workbooks 42

Renaming a Sheet 43

Change a Sheet’s Name 43

Chapter 4: Getting Data onto a Sheet 45

Entering Different Types of Data into a Cell 46

Type Numbers or Text into a Cell 46

Enter Numbers as Text 47

Type Dates and Times into a Cell 48

Undo an Entry 49

Using Lists to Quickly Fill a Range 50

Extend a Series Containing Text 50

Extend a Numerical Series 51

Create Your Own List 53

Using Paste Special 55

Paste Values Only 55

Combine Multiple Paste Special Options 57

Multiply the Range by a Specific Value 60

Use Paste to Merge a Noncontiguous Selection 63

Using Text to Columns to Separate Data in a Single Column 64

Work with Delimited Text 64

Using Data Validation to Limit Data Entry in a Cell 67

Limit User Entry to a Selection from a List 67

Using Web Queries to Get Data onto a Sheet 70

Insert a Web Query 70

Editing Data 72

Modify Cell Data 72

Clearing the Contents of a Cell 73

Clear Only Data from a Cell 73

Clearing an Entire Sheet 74

Clear an Entire Sheet 74

Working with Tables 75

Define a Table 75

Add a Total Row to a Table 77

Change the Total Row Function 77

Expand a Table 78

Fixing Numbers Stored as Text 79

Use Convert to Number on Multiple Cells 80

Use Paste Special to Force a Number 81

Spell Checking a Sheet 83

Finding Data on a Sheet 83

Perform a Search 84

Perform a Wildcard Search 86

Replace Data on a Sheet 87

Chapter 5: Selecting and Moving Data on a Sheet 91

Working with Rows and Columns 91

Select a Row or Column 92

Insert a New Row or Column 93

Delete a Row or Column 95

Move Rows or Columns by Dragging 96

Move Rows or Columns by Cutting 97

Copy Rows or Columns 99

Working with Cells 101

Select a Cell Using the Name Box 101

Select Noncontiguous Cells and Ranges 102

Insert Cells 103

Delete Cells 104

Move Cells 105

Chapter 6: Formatting Sheets and Cells 107

Changing the Font Settings of a Cell 108

Select a New Font Typeface 109

Increase and Decrease the Font Size 110

Apply Bold, Italic, and Underline to Text 111

Apply Strikethrough, Superscript, and Subscript 111

Change the Font Color 113

Format a Character or Word in a Cell 114

Format Quickly with the Format Painter 114

Adjusting the Row Height 115

Modify the Row Height by Dragging 116

Modify the Row Height by Entering a Value 117

Use Font Size to Automatically Adjust the Row Height 118

Adjusting the Column Width 119

Modify the Column Width by Dragging 119

Modify the Column Width by Entering a Value 120

Aligning Text in a Cell 121

Change Text Alignment 121

Merging Two or More Cells 121

Merge and Center Data 122

Merge Across Columns 123

Unmerge Cells 123

Centering Text Across Multiple Cells 124

Center Text Without Merging 124

Wrapping Text in a Cell to the Next Line 125

Wrap Text in a Cell 125

Reflowing Text in a Paragraph 126

Fit Text to a Specific Range 126

Indenting Cell Contents 127

Indent Data 127

Applying Number Formats 128

Modify the Number Format 128

Change the Format of Negative Numbers 129

Apply a Currency Symbol 130

Format Dates and Times 131

Format as Percentage 132

Format as Text 133

Apply the Special Number Format 134

Adding a Border Around a Range 135

Format a Range with a Thick Outer Border and Thin Inner Lines 136

Add a Colored Border 137

Coloring the Inside of a Cell 138

Apply a Two-Color Gradient to a Cell 139

Chapter 7: Advanced Formatting 143

Creating Custom Number Formats 143

The Four Sections of a Custom Number Format 144

Optional Versus Required Digits 145

Use the Thousands Separator, Color Codes, and Text 146

Line Up Decimals 148

Fill Leading and Trailing Spaces 149

Show More Than 24 Hours in a Time Format 150

Creating Hyperlinks 152

Create a Hyperlink to Another Sheet 152

Link to a Web Page 154

Dynamic Cell Formatting with Conditional Formatting 154

Use Icons to Mark Data 155

Highlight the Top 10 157

Highlight Duplicate or Unique Values 159

Create a Custom Rule 161

Clear Conditional Formatting 163

Edit Conditional Formatting 164

Using Cell Styles to Apply Cell Formatting 166

Apply a Style 166

Create a Custom Style 167

Using Themes to Ensure Uniformity in Design 169

Apply a New Theme 169

Create a New Theme 170

Share a Theme 173

Chapter 8: Using Formulas 175

Entering a Formula into a Cell 175

Calculate a Formula 176

View All Formulas on a Sheet 177

Relative Versus Absolute Referencing 178

Lock the Row When Copying a Formula Down 179

Copying Formulas 180

Copy and Paste Formulas 180

Copy by Dragging the Fill Handle 181

Copy Rapidly Down a Column 182

Copy Between Workbooks Without Creating a Link 183

Converting Formulas to Values 184

Paste as Values 184

Select and Drag 185

Using Names to Simplify References 186

Create a Named Cell 186

Use a Name in a Formula 187

Inserting Formulas into Tables 189

Write a Formula in a Table 189

Write Table Formulas Outside the Table 191

Using Array Formulas 193

Enter an Array Formula 193

Delete a Multicell Array Formula 194

Working with Links 195

Control the Prompt 196

Refresh Data 196

Change the Source Workbook 197

Break the Link 198

Troubleshooting Formulas 198

Fix ###### in a Cell 199

Understand a Formula Error 199

Use Trace Precedents and Dependents 201

Track Formulas on Other Sheets with Watch Window 203

Use the Evaluate Formula Dialog Box 204

Evaluate with F9 206

Adjusting Calculation Settings 207

Set Calculations to Manual 207

Chapter 9: Using Functions 209

Understanding Functions 209

Look Up Functions 210

Use the Function Arguments Dialog Box 211

Enter Functions Using Formula Tips 214

Using the AutoSum Button 214

Calculate a Single Range 215

Sum Rows and Columns at the Same Time 216

Quick Calculations 217

Calculate Results Quickly 217

Using Quick Analysis Functions 219

Using Lookup Functions 219

Use CHOOSE to Return the nth Value from a List 219

Use VLOOKUP to Return a Value from a Table 221

Use INDEX and MATCH to Return a Value from the Left 223

Using SUMIFS to Sum Based on Multiple Criteria 225

Sum a Column Based on Two Criteria 225

Using IF Statements 228

Compare Two Values 228

Hiding Errors with IFERROR 229

Hide a #DIV/0! Error 230

Understanding Dates and Times 231

Return a New Date X Workdays from Date 231

Calculate the Number of Days Between Dates 233

Using Goal Seek 234

Calculate the Best Payment 234

Using the Function Arguments Dialog Box to Troubleshoot Formulas 235

Narrow Down a Formula Error 236

Chapter 10: Sorting Data 239

Using the Sort Dialog Box 239

Sort by Values 240

Sort by Color or Icon 242

Doing Quick Sorts 244

Quick Sort a Single Column 244

Quick Sort Multiple Columns 245

Performing Custom Sorts 246

Perform a Random Sort 246

Sort with a Custom Sequence 247

Rearranging Columns 249

Sort Columns with the Sort Dialog Box 249

Fixing Sort Problems 251

Chapter 11: Filtering and Consolidating Data 253

Using the Filter Tool 253

Apply a Filter 254

Clear a Filter 256

Reapply a Filter 257

Turn the Filter On for One Column 257

Filtering Grouped Dates 258

Turn On Grouped Dates 258

Filter by Date 259

Using Special Filters 260

Filter for Items that Include a Specific Term 260

Filter for Values Within a Range 261

Filter for the Top 25 Items 262

Filter Dates by Quarter 263

Filtering by Color or Icon 263

Filtering by Selection 264

Allowing Users to Filter a Protected Sheet 264

Filter a Protected Sheet 265

Using the Advanced Filter 266

Reorganize Columns 266

Create a List of Unique Items 268

Filter Records Using Criteria 269

Use Formulas as Criteria 272

Removing Duplicates 273

Delete Duplicate Rows 273

Consolidating Data 274

Merge Values from Two Datasets 274

Merge Data Based on Matching Labels 276

Chapter 12: Distributing and Printing a Workbook 279

Using Cell Comments to Add Notes to Cells 280

Insert a New Cell Comment 280

Edit a Cell Comment 281

Format a Cell Comment 282

Insert an Image into a Cell Comment 284

Resize a Cell Comment 287

Show and Hide Cell Comments 287

Delete a Cell Comment 288

Allowing Multiple Users to Edit a Workbook at the Same Time 289

Share a Workbook 289

Hiding and Unhiding Sheets 291

Hide a Sheet 291

Unhide a Sheet 292

Using Freeze Panes 292

Lock the Top Row 293

Lock Multiple Rows and Columns 294

Configuring the Page Setup 295

Set Paper Size, Margins, and Orientation 295

Set the Print Area 296

Set Page Breaks .297

Scale the Data to Fit a Printed Page 299

Repeat Specific Rows on Each Printed Page 300

Creating a Custom Header or Footer 300

Add an Image to the Header or Footer 301

Add Page Numbering to the Header and Footer 303

Printing Sheets 304

Configure Print Options 304

Protecting a Workbook from Unwanted Changes 306

Set File-Level Protection 306

Set Workbook-Level Protection 307

Protecting the Data on a Sheet 308

Protect a Sheet 308

Unlock Cells 309

Allow Users to Edit Specific Ranges 310

Preventing Changes by Marking a File as Final 312

Mark a Workbook as Final 312

Sharing Files Between Excel Versions 313

Check Version Compatibility 313

Recovering Lost Changes 314

Configure Backups 314

Recover a Backup 315

Recover Unsaved Files 316

Sending an Excel File as an Attachment 316

Email a Workbook 317

Sharing a File Online 318

Save to OneDrive 318

Chapter 13: Inserting Subtotals and Grouping Data 321

Using the SUBTOTAL Function 321

Calculate Visible Rows 322

Summarizing Data Using the Subtotal Tool 323

Apply a Subtotal 323

Expand and Collapse Subtotals 325

Remove Subtotals or Groups 325

Sort Subtotals 326

Copying the Subtotals to a New Location 327

Copy Subtotals 328

Applying Different Subtotal Function Types 329

Create Multiple Subtotal Results on Multiple Rows 329

Combine Multiple Subtotal Results to One Row 331

Adding Space Between Subtotaled Groups 332

Separate Subtotaled Groups for Print 333

Separate Subtotaled Groups for Distributed Files 334

Grouping and Outlining Rows and Columns 336

Apply Auto Outline 336

Group Data Manually 337

Chapter 14 :Creating Charts and Sparklines 341

Adding a Chart 341

Add a Chart with the Quick Analysis Tool 342

Preview All Charts 343

Switch Rows and Columns 344

Apply Chart Styles or Colors 345

Apply Chart Layouts 346

Resizing or Moving a Chart 346

Resize a Chart 346

Move to a New Location on the Same Sheet 347

Relocate to Another Sheet 347

Editing Chart Elements 348

Use the Format Task Pane 348

Edit the Chart or Axis Titles 349

Change the Display Units in an Axis 351

Customize a Series Color 352

Changing an Existing Chart’s Type 353

Change the Chart Type 353

Creating a Chart with Multiple Chart Types 354

Insert a Multiple Type Chart 354

Add a Secondary Axis 356

Updating Chart Data 356

Change the Data Source 357

Adding Special Charts 358

Create a Stock Chart 358

Create a Bubble Chart 359

Pie Chart Issue: Small Slices 360

Rotate the Pie 360

Create a Bar of Pie Chart 361

Using a User-Created Template 363

Save a Chart Template 363

Use a Chart Template 364

Adding Sparklines to Data 364

Insert a Sparkline 365

Emphasize Points on a Sparkline 365

Space Markers by Date 366

Delete Sparklines 367

Chapter 15: Summarizing Data with PivotTables 369

Creating a PivotTable 370

Use the Quick Analysis Tool 371

Create a PivotTable from Scratch 372

Change the Calculation Type of a Field Value 375

Format Values 376

Changing the PivotTable Layout 377

Choose a New Layout 378

PivotTable Sorting 378

Click and Drag 378

Use Quick Sort 379

Expanding and Collapsing Fields 380

Expand and Collapse a Field 380

Grouping Dates 381

Group by Week 381

Group by Month and Year 382

Filtering Data in a PivotTable 383

Filter for Listed Items 383

Clear a Filter 384

Creating a Calculated Field 384

Add a Calculated Field 384

Hiding Totals 385

Hide Totals 386

Hide Subtotals 386

Viewing the Records Used to Calculate a Value 387

Unlinking PivotTables 388

Unlink a PivotTable Report 388

Refreshing the PivotTable 389

Refresh on Open 390

Refresh After Adding New Data 390

Refresh After Editing the Data Source 391

Working with Slicers 392

Create a Slicer 392

Use a Slicer 93

Chapter 16: Inserting SmartArt, WordArt, and Pictures 395

Working with SmartArt 395

Insert a SmartArt Graphic 396

Insert Images into SmartArt 398

Move and Resize SmartArt 399

Reorder Placeholders 401

Change the Layout 402

Change an Individual Shape 402

Working with WordArt 403

Insert WordArt 403

Inserting Pictures 404

Insert a Picture 404

Resize and Crop a Picture 405

Apply Corrections, Color, and Artistic Effects 407

Reduce a File’s Size 409

Chapter 17: Introducing the Excel Web App 411

Acquiring a Microsoft Account 411

Create an Account 412

Uploading a Workbook 413

Upload Through OneDrive 413

Save from Excel 414

Delete a File from OneDrive 415

Opening a Workbook Online or Locally 416

Open a Workbook 417

Download a Workbook 417

Creating a New Workbook Online 418

Create a Workbook 419

Rename the New Workbook 419

Sharing a Folder or Workbook 420

Create a View-Only Folder 420

Remove Sharing 422

Edit Simultaneously 424

Configuring Browser View Options 425

Create an Online Form 425

Designing a Survey Through the Web App 428

Create a Survey 428

Index 430