Easy Microsoft Excel 2010

Mike Alexander is a Microsoft Certified Application Developer (MCAD) and author of several books on advanced business analysis with Microsoft Access and Excel. He has more than 15 years experience consulting and developing Office solutions. Michael has been named a Microsoft MVP for his ongoing contributions to the Excel community. In his spare time, he runs a free tutorial site, www.datapigtechnologies.com, where he shares basic Access and Excel tips to the Office community.  

Introduction     x

CHAPTER 1  WORKING WITH THE EXCEL USER INTERFACE     2

The Ribbon User Interface     2

The Anatomy of the Ribbon Interface     3

Familiarizing Yourself with the Ribbon Tabs     4

Understanding Contextual Tabs     6

Understanding Workbooks and Worksheets     7

Customizing the Quick Access Toolbar     8

Adding Commands to the Quick Access Toolbar     9

Adding Hidden Commands to the Quick Access Toolbar     10

 

CHAPTER 2  MANAGING WORKBOOKS AND WORKSHEETS     12

Opening an Excel Workbook     14

Closing an Excel Workbook     15

Saving a Workbook     16

Switching Between Open Workbooks     17

Switching Between Worksheets     18

Viewing Multiple Workbooks     19

Inserting and Deleting Worksheets     20

Renaming Worksheets     21

Coloring Worksheet Tabs     22

Moving Worksheets Within a Workbook     23

Copying Worksheets Between Workbooks     24

Password Protect a Workbook     25

Protecting a Worksheet     26

 

CHAPTER 3  ENTERING AND MANAGING DATA     28

Entering Data     30

Editing and Deleting Existing Data     31

Zooming Into Your Data     32

Undoing and Redoing Changes     33

Copying and Pasting Data     34

Freezing Rows and Columns     36

Splitting a Worksheet     37

Inserting Cells     38

Deleting Cells     39

Inserting and Deleting Rows     40

Inserting and Deleting Columns     41

Moving Data     42

Finding Data     43

Replacing Data     44

Applying a Data Filter     45

Sorting Data     46

Adding and Managing Cell Comments     48

 

CHAPTER 4  FORMATTING WORKSHEET DATA     50

Changing the Font and Font Size     52

Changing Column Width     53

Changing the Color of the Cell Background and Cell Text     54

Formatting the Display of Numeric Data     55

Using a General Format     56

Using a Number Format     57

Using a Currency Format     58

Using a Date Format     59

Using a Text Format     60

Applying Bold, Italic, and Underline     61

Using Merge and Center on Cells     62

Changing Horizontal Data Alignment     63

Changing Row Height     64

Changing Vertical Data Alignment     65

Changing Cell Orientation     66

Wrapping Data in a Cell     67

Changing Borders     68

Indenting Entries in a Cell     69

Clearing Formatting     70

Hiding and Unhiding Rows     71

Hiding and Unhiding Columns     72

Hiding and Unhiding a Worksheet     73

Using Format As Table     74

Copying Formatting     75

Creating and Applying a Formatting Style     76

Using Conditional Formatting     78

 

CHAPTER 5  WORKING WITH FORMULAS AND FUNCTIONS     80

Using AutoSum (SUM)     82

Finding a Cell Average (AVERAGE)     83

Finding the Largest Cell Amount (MAX)     84

Finding the Smallest Cell Amount (MIN)     85

Counting the Number of Cells (COUNT)     86

Entering a Formula     87

Editing a Formula or Function     88

Copying a Formula     89

Assigning Names to a Cell or Range     90

Referencing Names in a Function     91

Using Functions Across Worksheets     92

Using Auto-Calculate     94

Finding Functions     95

Calculating a Loan Payment (PMT)     96

Performing a Logical Test Function (IF)     98

Conditionally Summing a Range (SUMIF)     100

Finding the Future Value of an Investment (FV)     102

Fixing the #### Error     104

Fixing the #DIV/0! Error     105

Fixing the #NAME? Error     106

Fixing the #VALUE! Error     107

Recognizing the #REF! Error     108

Recognizing Circular References     109

Checking for Formula References (Precedents)     110

Checking for Cell References (Dependents)     111

 

CHAPTER 6  WORKING WITH CHARTS     112

The Chart Tools Contextual Tab     113

Creating a Chart     114

Changing the Chart Type     116

Altering the Source Data Range     117

Altering Chart Options     118

Formatting the Plot Area     120

Formatting the Chart Area     121

Formatting the Axis Scale     122

Altering the Original Data     123

Adding Data to Charts     124

Adding a Legend     125

 

CHAPTER 7  WORKING WITH GRAPHICS     126

Using Drawing Tools     128

Inserting Clip Art     130

Inserting a Picture from File     131

Using AutoShapes     132

Inserting WordArt     133

Using Smart Art in Excel     134

Inserting a Diagram     135

Inserting an Object     136

Selecting an Object     137

Formatting an Object     138

Moving an Object     139

Resizing an Object     140

Deleting an Object     141

 

CHAPTER 8  PRINTING IN EXCEL     142

Using Print Preview     144

Setting the Print Area     146

Adjusting Page Margins     148

Inserting Page Breaks     150

Working in Page Break Preview Mode     152

Printing a Worksheet on One Page     154

Printing in Portrait or Landscape Orientation     155

Centering a Worksheet on a Page     156

Printing Gridlines and Row/Column Headers     157

Printing Cell Comments     158

Printing Cell Error Indicators     159

Printing Repeating Row and Column Titles     160

Adding Headers and Footers     161

Printing Worksheets     162

 

CHAPTER 9  WORKING WITH EXCEL PIVOT TABLES     164

Creating a Pivot Table     166

Rearranging a Pivot Table     168

Refreshing Pivot Table Data     169

Adding a Report Filter     170

Adding Pivot Table Data     172

Changing the Pivot Table Layout     173

Customizing Field Names     174

Applying Numeric Formats to Data Fields     175

Changing Summary Calculations     176

Showing and Hiding Data Items     177

Sorting Your Pivot Table     178

 

APPENDIX  EXCEL SHORTCUT KEYS     180

INDEX     184