Excel 2010 Made Simple

Get the most out of Excel 2010 with Excel 2010 Made Simple—learn the key features, understand what’s new, and utilize dozens of time-saving tips and tricks to get your job done. Over 500 screen visuals and clear-cut instructions guide you through the features of Excel 2010, from formulas and charts to navigating around a worksheet and understanding Visual Basic for Applications (VBA) and macros. Excel 2010 Made Simple takes a practical and highly effective approach to using Excel 2010, showing you the best way to complete your most common spreadsheet tasks. You'll learn how to input, format, sort, and filter your data to find out what you want to know. You'll see how to place your data in tables and named ranges for easy access, all of which will get you working efficiently and productively. Excel 2010 Made Simple also covers the new features introduced in Excel 2010. For instance, it shows you how to use Sparklines for data comparison and the Backstage view for printing and sharing your spreadsheets, so you can carry out your tasks with minimum fuss. The hands-on focus on tasks means you'll see how to actually use Excel 2010 to suit your needs. What you’ll learn How to do efficient and complex data entry How to format your data How to write basic—and not so basic—formulas How to use Excel's database capabilities, including PivotTables How to create colorful, meaningful charts How to create basic macros and how to use them How to use new Excel 2010 features, such as Sparklines and the Backstage view Who this book is for Excel 2010 Made Simple is for newcomers to Excel. Table of Contents Introducing Excel 2010 Getting Around the Worksheet and Data Entry Editing Data Number Crunching 101: Functions, Formulas, and Ranges For Appearance's Sake: Formatting Your Data Charting Your Data Sorting and Filtering Your Data: Excel's Database Features PivotTables: Data Aggregation Without the Aggravation Managing Your Workbook Printing Your Worksheets: Hard Copies Made Easy Automating Your Work with Macros Cover......Page 1 Contents at a Glance......Page 3 Contents......Page 356 About the Author......Page 362 About the Technical Reviewer......Page 363 Acknowledgments......Page 364 The Excel Worksheet: What You’re Looking At......Page 4 Key Tips: Accessing Buttons with the Keyboard......Page 8 Contextual Tabs......Page 9 A Visit Backstage......Page 10 Customizing the Quick Access Toolbar......Page 11 Where to Learn More......Page 15 Excel Keyboard Equivalents......Page 21 The Advantages of Learning More......Page 29 Spreadsheets Defined......Page 30 Summary......Page 32 Getting Around a Worksheet......Page 33 Selecting Multiple Cells......Page 36 Selecting Cells Down and Across the Worksheet......Page 37 Selecting All the Cells......Page 38 Still One More Selection Technique—The Name Box......Page 39 Entering Text and Data......Page 40 Aligning Your Data—Where It Appears in the Cell......Page 41 Altering a column manually......Page 43 Using the Auto-fit Feature......Page 44 Entering Numerical Data—How it’s Different......Page 46 Entering Data into a Selected Range......Page 47 Auto Filling a Numeric Sequence......Page 50 Using the Auto Fill Option Button......Page 52 Customizing Auto Fill Lists......Page 55 Data Validation: Bringing Quality Control to the Worksheet......Page 56 Making a List—Personalizing a Drop-Down Menu......Page 59 Explaining Data Validation Errors with Error Alerts......Page 61 Adding Data Entry Instructions with Input Messages......Page 62 Summary......Page 63 Changing Your Data......Page 64 Undoing an Edit......Page 65 Copying and Moving: Duplicating and Relocating Your Data......Page 67 Copying Data......Page 68 The Clipboard: The Storage Area for Copied and Cut Data......Page 70 Summary......Page 72 Automatic Calculations with Functions......Page 73 Adding a Column of Numbers......Page 74 Selecting the Range You Need......Page 76 Viewing and Editing Your Formula: Back to the Formula Bar......Page 77 Calculating an Average......Page 79 Displaying Values Based on a Certain Condition......Page 80 Locating Functions in the Function Library......Page 82 Customizing the Worksheet with Formulas......Page 84 Writing a Basic Formula......Page 85 Working Out the Order of Operations in a Formula......Page 86 Copying Formulas: More Than Just Duplication......Page 87 Naming a Range in the Name Box......Page 95 Naming Ranges from the Data in Your Worksheet......Page 99 Naming A Range Containing One Cell: Why Bother?......Page 100 The Name Manager: Where They’re All Ar-ranged......Page 101 Summary......Page 102 What Formatting Does (and Doesn’t Do)......Page 103 Changing the Font......Page 104 Changing the Font Size......Page 106 Using Bold, Italics, and Underline......Page 107 Determining a Cell’s Formatting......Page 108 Adding a Border......Page 109 Adding Color to Your Cells......Page 111 Adding Extra Formatting......Page 113 Changing Horizontal Alignment......Page 114 Changing Vertical Alignment......Page 115 Changing Data Orientation......Page 116 Wrapping Text......Page 119 Adding a Title with Merge and Center......Page 120 Inserting, Deleting, and Hiding Columns and Rows......Page 121 Inserting a Column or Row......Page 122 Inserting Multiple Columns or Rows......Page 123 Deleting Columns and Rows......Page 124 Hiding Rows and Columns......Page 125 Unhiding Columns and Rows......Page 127 Inserting and Deleting Cells......Page 128 Turning Values into Currency......Page 129 Punctuating Values......Page 132 Formatting Decimal Points......Page 133 Working with Dates: Dates Are Numbers Too......Page 134 The Special Formats Option......Page 137 The Custom Option......Page 138 Copying Formats (Not Data) with the Format Painter......Page 140 Applying Ready-Made Formats with Styles......Page 141 Customizing Your Own Style......Page 142 Applying Styles Quickly: Another Way to Access Formatting Options......Page 144 Conditional Formatting......Page 145 An Alternative Approach to the Same Result......Page 149 Some Additional Conditional Formatting Options......Page 150 Using Data Bars: A Different Kind of Conditional Format......Page 152 Summary......Page 153 Charting Your Data......Page 154 Defining Chart Elements......Page 155 Choosing a Chart Type......Page 156 Creating a Column Chart......Page 158 Moving and Resizing the Chart......Page 159 Changing the Chart by Changing Its Data......Page 161 Changing the Chart Type......Page 162 Changing the Default Chart......Page 166 Switching Rows with Columns: A Different Kind of Change......Page 167 Formatting Charts......Page 168 Formatting with the Mini-Toolbar......Page 169 Formatting with the Current Selection Button Group......Page 170 Exploring the Format Dialog Box......Page 171 Reformatting the Vertical Axis......Page 174 Color-Coordinating Your Data Using Chart Styles......Page 176 Formatting a Chart Object Using Shape Styles......Page 177 Formatting Text Elements with WordArt......Page 178 Adding Extra Chart Elements with Chart Layouts......Page 179 Adding Extra Chart Elements with the Layout Tab......Page 180 Introducing Sparklines: Mini-Charts Placed in Cells......Page 188 Modifying Sparklines......Page 190 Representing Binary Values with Sparklines......Page 191 Summary......Page 192 Sorting and Filtering Your Data: Excel’s Database Features......Page 193 Sorting Data: Instilling Order in Your Data......Page 194 Sorting by Two Fields: The Hows and Whys......Page 197 Sorting by Cell Format......Page 199 Finding What You Want with Filters......Page 200 Text and Number Filters: Filters Within the Filter......Page 203 Filtering Multiple Fields......Page 206 Tables: Adding User-Friendliness to Your Database......Page 207 Finding Duplicate Records in the Table (and Removing Them)......Page 214 Converting a Table to a Range......Page 215 Summary......Page 216 Looking at Some PivotTables......Page 217 Creating a PivotTable......Page 222 Choosing Which Data to Work On......Page 225 Getting the Fields Where You Want Them......Page 226 Filtering Items Using the Report Filter Area......Page 227 Creating a Report Worksheet for Each Item in a Filter......Page 229 Counting Records: A Way to Break Out Text Data......Page 230 Grouping Related Items Ussing Two Fields......Page 232 Changing the Calculation......Page 235 Grouping PivotTable Data: Organizing Your Time(s)......Page 237 Refreshing the PivotTable: Changing the Data......Page 238 Adding New Records to a PivotTable......Page 239 Viewing Which Records Are Filtered: Using the Slicer......Page 240 How the Slicer Works......Page 241 Restyling the Slicer......Page 244 Formatting the PivotTable......Page 245 Styling Your Report......Page 247 Changing PivotTable Headers......Page 248 Layout Options......Page 249 Creating Charts from PivotTables Using PivotCharts......Page 251 Filtering Data in the Chart with Field Buttons......Page 254 Creating a PivotTable and PivotChart Together......Page 256 Summary......Page 257 Adding Worksheets to Your Workbook......Page 258 Clicking Through the Worksheets......Page 259 Adding and Moving New Worksheets......Page 260 Deleting Sheets......Page 262 Renaming and Recoloring the Worksheet Tabs......Page 263 Hiding Sheets......Page 264 Grouping Sheets: Changing Multiple Sheets at the Same Time......Page 265 Ungrouping the Sheets......Page 266 Referring to Cells in Other Worksheets: Using Them in Formulas......Page 267 Using Ranges on Other Sheets in Formulas......Page 268 Using the View Context Tab to Show and Hide Basic Screen Elements......Page 270 Showing Formulas in Cells......Page 271 Hiding the Ribbon......Page 272 Keeping Important Data in View with the Freeze Panes Option......Page 273 Freezing Rows and Columns at the Same Time......Page 275 Protecting the Worksheet and the Workbook......Page 276 Protecting a Worksheet......Page 277 Using a Password: Some Extra Protection......Page 278 Protecting Some, but Not All, of a Worksheet......Page 279 Hiding Formulas......Page 281 Protecting a Workbook......Page 283 Summary......Page 285 Printing the Entire Worksheet......Page 286 Printing a Selection......Page 287 Surveying Printing Options: The Print Backstage......Page 291 Setting the Print Area......Page 297 Working with Page Breaks......Page 299 Previewing the Page Break: Getting a Bird’s-Eye View of the Printout......Page 302 Printing Titles......Page 305 Adding Headers and Footers in the Page Layout View......Page 309 Adding Headers and Footers Using the Page Setup Dialog Box......Page 311 Adding Custom Headers and Footers......Page 313 Printing the Gridlines and Headings......Page 316 Summary......Page 318 The Two Kinds of Macros......Page 319 Composing a Macro......Page 320 About Saving a Workbook with a Macro......Page 322 Playing Back the Macro......Page 323 Relative References in a Macro......Page 324 Saving a Macro to the Personal Macro Workbook......Page 326 Editing a Macro by Tweaking It in VBA......Page 328 Exposing the VBA Worksheet......Page 329 Activating a Macro with a Keyboard Shortcut......Page 331 A Note on Macro Security......Page 333 Summary......Page 334 A......Page 335 C......Page 336 D......Page 339 F......Page 340 G......Page 342 K......Page 343 M......Page 344 P......Page 345 Q......Page 347 S......Page 348 V......Page 350 W......Page 351 Y, Z......Page 353

Get the most out of Excel 2010 with Excel 2010 Made Simple—learn the key features, understand what’s new, and utilize dozens of time-saving tips and tricks to get your job done. Over 500 screen visuals and clear-cut instructions guide you through the features of Excel 2010, from formulas and charts to navigating around a worksheet and understanding Visual Basic for Applications (VBA)and macros.

Excel 2010 Made Simple takes a practical and highly effective approach to using Excel 2010, showing you the best way to complete your most common spreadsheet tasks. You'll learn how to input, format, sort, and filter your data to find out what you want to know. You'll see how to place your data in tables and named ranges for easy access, all of which will get you working efficiently and productively.

Excel 2010 Made Simple also covers the new features introduced in Excel 2010. For instance, it shows you how to use Sparklines for data comparison and the Backstage view for printing and sharing your spreadsheets, so you can carry out your tasks with minimum fuss. The hands-on focus on tasks means you'll see how to actually use Excel 2010 to suit your needs.

What you’ll learn

• How to do efficient and complex data entry
• How to format your data
• How to write basic—and not so basic—formulas
• How to use Excel's database capabilities, including PivotTables
• How to create colorful, meaningful charts
• How to create basic macros and how to use them
• How to use new Excel 2010 features, such as Sparklines and the Backstage view

Who this book is for

Excel 2010 Made Simple is for newcomers to Excel.

Table of Contents

1. Introducing Excel 2010
2. Getting Around the Worksheet and Data Entry
3. Editing Data
4. Number Crunching 101: Functions, Formulas, and Ranges
5. For Appearance's Sake: Formatting Your Data
6. Charting Your Data
7. Sorting and Filtering Your Data: Excel's Database Features
8. PivotTables: Data Aggregation Without the Aggravation
9. ManagingYour Workbook
10. PrintingYour Worksheets: Hard Copies Made Easy
11. Automating Your Work with Macros

Annotation Get the most out of Excel 2010 withExcel 2010 Made Simplelearn the key features, understand whats new, and utilize dozens of time-saving tips and tricks to get your job done. Over 500 screen visuals and clear-cut instructions guide you through the features of Excel 2010, from formulas and charts to navigating around a worksheet and understanding Visual Basic for Applications (VBA)and macros. Excel 2010 Made Simpletakes a practical and highly effective approach to using Excel 2010, showing you the best way to complete your most common spreadsheet tasks. You'll learn how to input, format, sort, and filter your data to find out what you want to know. You'll see how to place your data in tables and named ranges for easy access, all of which will get you working efficiently and productively. Excel 2010 Made Simplealso covers the new features introduced in Excel 2010. For instance, it shows you how to use Sparklines for data comparison and the Backstage view for printing and sharing your spreadsheets, so you can carry out your tasks with minimum fuss. The hands-on focus on tasks means you'll see how to actually use Excel 2010 to suit your needs. What youll learnHow to do efficient and complex data entryHow to format your dataHow to write basicand not so basicformulasHow to use Excel's database capabilities, including PivotTablesHow to create colorful, meaningful chartsHow to create basic macros and how to use themHow to use new Excel 2010 features, such as Sparklines and the Backstage viewWho this book is forExcel 2010 Made Simpleis for newcomers to Excel. Table of ContentsIntroducing Excel 2010Getting Around the Worksheet and Data EntryEditing DataNumber Crunching 101: Functions, Formulas, and RangesFor Appearance's Sake: Formatting Your DataCharting Your DataSorting and Filtering Your Data: Excel's Database FeaturesPivotTables: Data Aggregation Without the AggravationManagingYour WorkbookPrintingYour Worksheets: Hard Copies Made EasyAutomating Your Work with Macros Get the most out of Excel 2010 with Excel 2010 Made Simple --learn the key features, understand what's new, and utilize dozens of time-saving tips and tricks to get your job done. Over 500 screen visuals and clear-cut instructions guide you through the features of Excel 2010, from formulas and charts to navigating around a worksheet and understanding Visual Basic for Applications (VBA) and macros. Excel 2010 Made Simple takes a practical and highly effective approach to using Excel 2010, showing you the best way to complete your most common spreadsheet tasks. You'll learn how to input, format, sort, and filter your data to find out what you want to know. You'll see how to place your data in tables and named ranges for easy access, all of which will get you working efficiently and productively. Excel 2010 Made Simple also covers the new features introduced in Excel 2010. For instance, it shows you how to use Sparklines for data comparison and the Backstage view for printing and sharing your spreadsheets, so you can carry out your tasks with the minimum of fuss. The hands-on focus on tasks means you'll see how to actually use Excel 2010 to suit your needs. What you'll learn How to do efficient and complex data entry How to format your data How to write basic--and not so basic--formulas How to use Excel's database capabilities, including PivotTables How to create colorful, meaningful charts How to create basic macros and how to use them How to use new Excel 2010 features, such as Sparklines and the Backstage view Who this book is for Excel 2010 Made Simple is for newcomers to Excel