Excel 2013 Absolute Beginner's Guide

INTRODUCTION    1

Who Should Read This Book    3

How This Book Is Organized    3

Downloading the Example Files    3

Conventions Used In This Book    4

Special Elements    4

1  Understanding the Microsoft Excel Interface    5

Taking a Closer Look at the Excel Window    6

Customizing the Excel Window    12

Moving Around and Making Selections on a Sheet    19

Installing Optional Components    21

2  Working with Workbooks, Sheets, Rows, Columns, and Cells    27

Managing Workbooks    28

Using Templates to Quickly Create New Workbooks    33

Working with Sheets and Tabs    36

Working with Rows and Columns    40

Working with Cells    43

3  Getting Data onto a Sheet    47

Types of Data You Enter into Excel    48

Entering Different Types of Data into a Cell    48

Controlling the Next Cell Selection    50

Using Copy, Cut, Paste, Paste Special to Enter Data    52

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

Inserting Symbols and Equations into a Cell    58

Using Web Queries to Get Data onto a Sheet    59

Using Series to Quickly Fill a Range    60

Editing Data    62

Working with Tables    64

Fixing Numbers Stored as Text    67

Spellchecking Your Sheet    69

Finding Data on Your Sheet    69

Using Data Validation to Limit Data Entry in a Cell    71

4  Formatting Sheets and Cells    75

Adjusting Row Heights and Column Widths    76

Changing the Font Settings of a Cell    77

Aligning Text in a Cell    79

Reflowing Text in a Paragraph    83

Applying Number Formats with Format Cells    85

Creating a Custom Format    88

When Cell Formatting Doesn’t Seem to Be Working Right    95

The Number Group on the Ribbon    95

Adding a Border Around a Range    96

Coloring the Inside of a Cell    98

Creating Hyperlinks    99

Quick Formatting with the Format Painter    101

Dynamic Cell Formatting with Conditional Formatting    101

Using Cell Styles to Quickly Apply Formatting    109

Using Themes to Ensure Uniformity in Design    109

5  Using Formulas    113

The Importance of Laying Out Data Properly    114

Adjusting Calculation Settings    114

Viewing Formulas Versus Values    115

Entering a Formula into a Cell    116

Three Ways of Entering a Formula’s Cell References    118

Relative Versus Absolute Formulas    118

Using a Cell on Another Sheet in a Formula    119

Using R1C1 Notation to Reference Cells    119

Using F4 to Change the Cell Referencing    121

Mathematical Operators    122

Copying a Formula to Another Cell    123

Using Names to Simplify References    125

Inserting Formulas into Tables    128

Using Table Names in Table Formulas    129

Writing Table Formulas Outside the Table    130

Using Array Formulas    132

Converting Formulas to Values    136

Troubleshooting Formulas    137

6  Using Functions    143

Breaking Down a Function    144

Finding Functions    144

Entering Functions Using the Function Arguments Dialog Box    146

Entering Functions Using In-Cell Tips    147

Using the AutoSum Button    148

Using Lookup Functions to Match a Value and Return Another    152

SUMIFS    159

SUMPRODUCT    160

Logical Functions    162

Nested IF Statements    164

IFERROR    165

Date and Time Functions    166

Troubleshooting Dates and Times Stored as Strings    172

Goal Seek    173

Using the Function Arguments Dialog Box to Troubleshoot Formulas    174

7  Sorting Data    177

Preparing Data    178

Opening the Sort Dialog Box    178

Sorting by Values    178

Sorting by Color or Icon    180

Using the Quick Sort Buttons    182

Quick Sorting Multiple Columns    182

Randomly Sorting Data    183

Sorting with a Custom Sequence    184

Rearranging Columns Using the Sort Dialog Box    186

Rearranging Columns Using the Mouse    186

Fixing Sort Problems    187

8  Filtering and Consolidating Data    189

Preparing Data    190

Applying a Filter to a Data Set    190

Clearing a Filter    191

Reapplying a Filter    192

Turning Filtering On for One Column    192

Filtering for Listed Items    192

Filtering the Grouped Dates Listing    193

Using the Search Function to Filter for or Exclude Items    195

Using the Search Function on Grouped Dates    196

Using Text, Number, and Date Special Filters    198

Filtering by Color or Icon    200

Filtering by Selection    200

Allowing Filtering on a Protected Sheet    201

Using the Advanced Filter Option    202

Removing Duplicates from a Data Set    207

Consolidating Data    208

9  Distributing and Printing a Workbook     213

Using Cell Comments to Add Notes to Cells    214

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

Hiding and Unhiding Sheets    217

Locking Rows or Columns in Place    218

Creating Custom Views of Your Data    219

Configuring the Page Setup    .220

Creating a Custom Header or Footer    222

Using Page Break Preview to Set Page Breaks    225

Printing Sheets    226

Protecting Your Workbook from Unwanted Changes    227

Protecting the Data on a Sheet    228

Preventing Changes by Marking a File as Final    230

Restricting Access Using IRM    230

Certifying a Workbook with a Digital Signature    230

Sharing Files Between Excel Versions    231

Removing Hidden or Confidential Information    231

Recovering Lost Changes    232

Sending an Excel File as an Attachment    233

Sharing a File Online    234

10  Subtotals and Grouping    235

Using the SUBTOTAL Function    236

Summarizing Data Using the Subtotal Tool    237

Copying the Subtotals to a New Location    240

Formatting the Subtotals    241

Applying Different Subtotal Function Types    241

Combining Multiple Subtotal Results to One Row    243

Subtotaling by Multiple Columns    245

Sorting Subtotals    246

Adding Space Between Subtotaled Groups    246

Grouping and Outlining Rows and Columns    249

11  Creating Charts and Sparklines    253

Preparing Data    254

Elements of a Chart    254

Types of Charts    256

Adding a Chart to a Sheet    258

Adding, Removing, and Formatting Chart Elements    262

Editing and Formatting a Chart Title    262

Changing the Display Units in an Axis    263

Applying Chart Styles and Colors    264

Applying Chart Layouts    265

Moving or Resizing a Chart    265

Switching Rows and Columns    266

Changing an Existing Chart’s Type    266

Creating a Chart with Multiple Chart Types    266

Updating Chart Data    268

Creating Stock Charts    269

Creating Bubble Charts    269

Pie Chart Issue: Small Slices    270

Adding Sparklines to Data    272

Creating a Chart Using a User-Created Template    275

12  PivotTables and Slicers    277

Preparing Data for Use in a PivotTable    278

PivotTable Limitations    279

PivotTable Compatibility    279

PivotTable Field List    280

Creating a PivotTable    281

Changing How a PivotTable Appears on a Sheet    286

PivotTable Sorting    287

Expanding and Collapsing Fields    289

Viewing the Records Used to Calculate a Value    290

Grouping Dates    290

Filtering Data in a PivotTable    292

Creating a Calculated Field    296

Hiding Totals    298

Formatting Values    298

Slicers    299

13  Using Power View to Create Reports    301

Power View Requirements    302

Creating Reports    302

Inserting and Formatting a Report Title    305

Changing Data Visualizations    305

Combining Multiple Visualizations    309

Changing Colors    310

Sorting    311

Filtering    312

Sharing Power View Reports    315

14  Inserting SmartArt, WordArt, and Pictures    317

Working with SmartArt    318

Inserting WordArt    325

Inserting Pictures    326

15  An Introduction to Using Macros and UDFs    333

Enabling VBA Security    334

Developer Tab    335

Introduction to the Visual Basic Editor    336

Understanding How the Macro Recorder Works    338

Recording a Macro    342

Running a Macro    343

User-Defined Functions    347

16  Introducing the Excel Web App    355

Requirements    356

Acquiring a Microsoft Account    356

Uploading and Downloading Workbooks    358

Opening a Workbook    360

Creating a New Workbook Online    361

Saving a File to Your Local Drive    362

Sharing a Folder or Workbook    362

Interacting with a Sheet Online    366

Configuring Browser View Options    367

Setting Up a Survey    369

Index    373

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.