My Excel 2013

Michael Alexander has been named a Microsoft MVP for his ongoing contributions to the Excel community. A Microsoft Certified Application Developer (MCAD), he has authored several books on advanced business analysis with Excel and Access, including Easy Microsoft Excel 2010. He has 15+ years’ experience developing Office solutions, and runs the free Excel and Access tutorial and tips site datapigtechnologies.com.

Introduction   xi
Chapter 1  Working with Excel’s Ribbon Menus   3
Familiarizing Yourself with the Ribbon Tabs   4
Understanding Workbooks and Worksheets   7
   Explore Worksheets   7
   Explore Columns, Rows, and Cells   8
Understanding Contextual Tabs   9
   Work with Contextual Tabs   10
Customizing the Quick Access Toolbar   10
   Add a Command to the Quick Access Toolbar   11
   Add Other Commands to the Quick Access Toolbar   12
   Add Hidden Commands to the Quick Access Toolbar   13

Chapter 2  Managing Workbooks and Worksheets   15
Opening an Excel Workbook   16
Closing an Excel Workbook   17
Saving a Workbook   17
Switching Between Open Workbooks   18
Switching Between Worksheets   20
Viewing Multiple Workbooks   20
Inserting and Deleting Worksheets   22
Renaming Worksheets   23
Coloring Worksheet Tabs   23
Moving Worksheets Within a Workbook   24
Copying Worksheets Between Workbooks   25
Password Protect a Workbook   26
Protecting a Worksheet   28

Chapter 3  Entering and Managing Data   31
Entering Data   32
Editing and Deleting Existing Data   33
Zooming into Your Data   34
Undoing and Redoing Changes   35
Copying and Pasting Data 36
   Cut and Paste Data   37
Freezing Rows and Columns   38
Splitting a Worksheet   39
Inserting Cells   40
Deleting Cells   41
Inserting and Deleting Rows   42
Inserting and Deleting Columns   43
Moving Data   44
Finding Data   46
Replacing Data 47
Applying a Data Filter   48
Sorting Data   49
Adding and Managing Cell Comments   50

Chapter 4  Formatting Worksheet Data   53
Changing the Font and Font Size   54
Changing Column Width   55
Changing the Color of the Cell Background and Cell Text   57
Formatting the Display of Numeric Data   58
   Use a General Format   60
   Use a Number Format   61
   Use a Currency Format   62
   Use a Date Format   63
   Use a Text Format 64
Applying Bold, Italic, and Underline   65
Using Merge and Center on Cells   66
Changing Horizontal Data Alignment   68
Changing Row Height   69
Changing Vertical Data Alignment 70
Changing Cell Orientation   71
Wrapping Data in a Cell   72
Changing Borders    72
Indenting Entries in a Cell    73
Clearing Formatting   74
Hiding and Unhiding Rows   76
Hiding and Unhiding Columns   78
Hiding and Unhiding a Worksheet   80
Using Format as Table   81
Copying Formatting   82
Creating and Applying a Formatting Style   84
Using Conditional Formatting   85

Chapter 5  Working with Formulas and Functions   89
Using AutoSum Calculations   89
   Find a Cell Average (AVERAGE)   91
   Find the Largest Cell Amount (MAX)   92
   Find the Smallest Cell Amount (MIN)   93
   Count the Number of Cells (COUNT)   94
Entering a Formula   95
Editing a Formula or Function   96
Copying a Formula   97
Assigning Names to a Cell or Range   99
Referencing Names in a Function   100
Using Functions Across Worksheets   101
Using Auto-Calculate   104
Finding and Using Excel Functions   105
   Calculate a Loan Payment (PMT)   107
   Perform a Logical Test Function (IF)   110
   Conditionally Sum a Range (SUMIF)   113
   Find the Future Value of an Investment (FV)   115
Recognizing and Fixing Errors   118
   Fix the #DIV/0! Error   119
   Fix the #NAME? Error   120
   Fix the #VALUE! Error 122
   Recognize the #REF! Error   124
Recognizing Circular References   126
Checking for Formula References (PRECEDENTS)   128
Checking for Cell References (DEPENDENTS)   130

Chapter 6  Working with Charts   133
Creating a Chart   134
Changing the Chart Type   136
Altering the Source Data Range   137
Altering Chart Options   138
Formatting the Plot Area   142
Formatting the Chart Area   144
Formatting the Axis Scale   146
Altering the Original Data   147
Adding Data to Charts   149
Adding a Legend   150

Chapter 7  Working with Graphics   153
Using Drawing Tools   153
Inserting Clip Art   156
Inserting a Picture from File   157
Using AutoShapes   158
Inserting WordArt   160
Using Smart Art in Excel   161
Inserting a Diagram   163
Inserting Objects   164
Working with Inserted Objects   166
   Format an Object   167
   Move an Object   168
   Resize an Object   170
   Delete an Object   171

Chapter 8  Printing in Excel   173
Using Print Preview   174
Setting the Print Area   175
Adjusting Page Margins   177
Inserting Page Breaks   178
Working in Page Break Preview Mode   181
Printing a Worksheet on One Page   182
Printing in Portrait or Landscape Orientation   184
Centering a Worksheet on a Page   185
Printing Gridlines and Row/Column Headers   186
Printing Cell Comments   187
Printing Cell Error Indicators   188
Printing Repeating Row and Column Titles   189
Adding Headers and Footers   190
Printing Your Worksheets   191

Chapter 9  Working with Pivot Tables   195
Creating a Pivot Table   196
Rearranging a Pivot Table   199
Adding a Report Filter   201
Refreshing Pivot Table Data   203
Adding Pivot Table Data 204
Customizing Field Names   206
Applying Numeric Formats to Data Fields   207
Changing Summary Calculations   209
Showing and Hiding Data Items   210
Sorting Your Pivot Table   211

Chapter 10  Using SkyDrive to Store and Share Excel Files   215
Signing Up For SkyDrive    215
Signing into SkyDrive   217
Saving a Workbook to SkyDrive   218
Opening a SkyDrive Workbook   220
Downloading a Workbook from SkyDrive   222
Sharing Your SkyDrive Workbooks with Others   223
Managing SkyDrive Workbooks   224

Appendix A  Excel Shortcut Keys   227
Using the Excel Shortcut Key Reference Table   227

Index 231