Microsoft EXCEL lessons for beginners
Contents at a Glance
Introduction . 1 Part I: Getting In on the Ground Floor 9
Chapter 1: The Excel 2010 User Experience ..11
Chapter 2: Creating a Spreadsheet from Scratch ..49
Part II: Editing without Tears . 95
Chapter 3: Making It All Look Pretty ..97
Chapter 4: Going Through Changes .145
Chapter 5: Printing the Masterpiece 175
Part III: Getting Organized and Staying That Way .. 199
Chapter 6: Maintaining the Worksheet ..201
Chapter 7: Maintaining Multiple Worksheets .229
Part IV: Digging Data Analysis 253
Chapter 8: Doing What-If Analysis .255
Chapter 9: Playing with Pivot Tables ..267
Part V: Life beyond the Spreadsheet .. 283
Chapter 10: Charming Charts and Gorgeous Graphics 285
Chapter 11: Getting on the Data List 319
Chapter 12: Linking, Automating, and Sharing Spreadsheets .345
Part VI: The Part of Tens 363
Chapter 13: Top Ten Features in Excel 2010 ..365
Chapter 14: Top Ten Beginner Basics .369
Chapter 15: The Ten Commandments of Excel 2010 .371 Index . 373
Table of Contents
Introduction .. 1
About This Book .1
How to Use This Book .2
What You Can Safely Ignore 2
Foolish Assumptions 2
How This Book Is Organized ..3 Part I: Getting In on the Ground Floor .3
Part II: Editing without Tears 3
Part III: Getting Organized and Staying That Way ..3
Part IV: Digging Data Analysis ..4
Part V: Life beyond the Spreadsheet 4
Part VI: The Part of Tens ..4 Conventions Used in This Book 4
Icons Used in This Book ..6
Where to Go from Here ..6
Part I: Getting In on the Ground Floor . 9
Chapter 1: The Excel 2010 User Experience . . . . . . . . . . . . . . . . . . . . . .11
Excel’s Ribbon User Interface .12 Going Backstage via File .13
Bragging about the Ribbon ..14
Customizing the Quick Access toolbar 18
Having fun with the Formula bar .22
What to do in the Worksheet area ..23
Showing off the Status bar 27
Launching and Quitting Excel .28 Starting Excel from the Start menu .28
Starting Excel from the Windows XP Start menu ..29
Pinning a Microsoft Excel 2010 option
on your Windows Start menu ..29
Adding a Microsoft Excel 2010 shortcut
to your Windows desktop 30 Adding Excel to the Windows Quick Launch toolbar .31 Pinning an Excel icon to the Windows 7 taskbar ..31
Exiting Excel ..32 Help Is on the Way ..32
Migrating to Excel 2010 from Earlier Versions
Using Pull-down Menus ..33
Cutting the Ribbon down to size ..34
Finding the Standard toolbar buttons equivalents ..39
Finding the Formatting toolbar buttons equivalents .42
Putting the Excel Quick Access toolbar
to good use during the transition ..43 Getting good to go with Excel 2010 47
Chapter 2: Creating a Spreadsheet from Scratch. . . . . . . . . . . . . . . . . .49
So What Ya Gonna Put in That New Workbook of Yours- ..50
The ins and outs of data entry ..50
You must remember this . . . ..51 Doing the Data-Entry Thing ..51
It Takes All Types 53 The telltale signs of text .54
How Excel evaluates its values .55
Fabricating those fabulous formulas! 62
If you want it, just point it out 64
Altering the natural order of operations 65
Formula fl ub-ups .66
Fixing Those Data Entry Flub-Ups 67 You really AutoCorrect that for me 68
Cell editing etiquette 69
Taking the Drudgery out of Data Entry .71 I’m just not complete without you ..71
Fill ’er up with AutoFill 72
Inserting special symbols .78
Entries all around the block 79
Data entry express .80
How to Make Your Formulas Function Even Better .80
Inserting a function into a formula with
the Insert Function button ..81 Editing a function with the Insert Function button .84
I’d be totally lost without AutoSum 85
Making Sure That the Data Is Safe and Sound .87 The Save As dialog box in Windows 7 and Windows Vista 88
The Save As dialog box in Windows XP ..89
Changing the default fi le location 90
The difference between the XLSX and XLS fi le format ..90 Saving the Workbook as a PDF File .91
Document Recovery to the Rescue .92
Part II: Editing without Tears .. 95
Chapter 3: Making It All Look Pretty. . . . . . . . . . . . . . . . . . . . . . . . . . . . .97
Choosing a Select Group of Cells ..98 Point-and-click cell selections 99
Keyboard cell selections .102 Having Fun with the Format as Table Gallery 105 Cell Formatting from the Home Tab .107 Formatting Cells Close to the Source with the Mini-Toolbar ..111
Using the Format Cells Dialog Box 112 Getting comfortable with the number formats 113
The values behind the formatting 118
Make it a date! 120
Ogling some of the other number formats .121
Calibrating Columns 122 Rambling rows 123
Now you see it, now you don’t 123 Futzing with the Fonts 125
Altering the Alignment ..127 Intent on indents ..128
From top to bottom 129
Tampering with how the text wraps ..130
Reorienting cell entries 132
Shrink to fi t ..134
Bring on the borders! 134
Applying fi ll colors, patterns, and gradient effects to cells .136
Do It in Styles ..137 Creating a new style for the gallery .138
Copying custom styles from one workbook into another 138
Fooling Around with the Format Painter ..139
Conditional Formatting .140
Conditionally formatting values with
sets of graphic scales and markers .141
Highlighting cells according to what
ranges the values fall into .142 Chapter 4: Going Through Changes. . . . . . . . . . . . . . . . . . . . . . . . . . . . .145
Opening the Darned Thing Up for Editing 146 Operating the Open dialog box ..146
Opening more than one workbook at a time .148
Opening recently edited workbooks .149
When you don’t know where to fi nd them .150
Opening fi les with a twist 151
Much Ado about Undo ..152 Undo is Redo the second time around ..152
What ya gonna do when you can’t Undo- ..153
Doing the Old Drag-and-Drop Thing .153 Copies, drag-and-drop style .155
Insertions courtesy of drag and drop 156
Formulas on AutoFill ..157 Relatively speaking .157
Some things are absolutes! 158
Cut and paste, digital style 161
Paste it again, Sam . . . ..162
Keeping pace with Paste Options .162
Paste it from the Clipboard task pane 164
So what’s so special about Paste Special- ..165
Let’s Be Clear about Deleting Stuff 167 Sounding the all clear! ..167
Get these cells outta here! .168 Staying in Step with Insert ..169
Stamping Out Your Spelling Errors ..170
Stamping Out Errors with Text to Speech 171
Chapter 5: Printing the Masterpiece. . . . . . . . . . . . . . . . . . . . . . . . . . . .175
Taking a Gander at the Pages in Page Layout View ..176
Checking and Printing a Report from the Print Panel ..177
Printing Just the Current Worksheet ..180
My Page Was Set Up! ..181 Using the buttons in the Page Setup group 182
Using the buttons in the Scale to Fit group 188
Using the Print buttons in the Sheet Options group 188
From Header to Footer ..189 Adding an Auto Header or Auto Footer 189
Creating a custom header or footer 191 Solving Page Break Problems 195
Letting Your Formulas All Hang Out 198
Part III: Getting Organized and Staying That Way 199
Chapter 6: Maintaining the Worksheet . . . . . . . . . . . . . . . . . . . . . . . . .201
Zeroing In with Zoom .202
Splitting the Difference .204
Fixed Headings Courtesy of Freeze Panes 207
Electronic Sticky Notes .209 Adding a comment to a cell ..210
Comments in review ..211
Editing the comments in a worksheet 212
Getting your comments in print .213
The Cell Name Game ..213 If I only had a name . . . 214
Name that formula! .215
Naming constants 216 Seek and Ye Shall Find . . . ..217
You Can Be Replaced! 220
Do Your Research .222
You Can Be So Calculating .223
Putting on the Protection 224
Chapter 7: Maintaining Multiple Worksheets . . . . . . . . . . . . . . . . . . .229
Juggling Worksheets ..229 Sliding between the sheets 230
Editing en masse ..233
Don’t Short-Sheet Me! .234 A worksheet by any other name . . . 235
A sheet tab by any other color . . . ..236
Getting your sheets in order 236 Opening Windows on Your Worksheets 238
Comparing Two Worksheets Side by Side 243
Moving and Copying Sheets to Other Workbooks .245
To Sum Up . . . .248
Part IV: Digging Data Analysis 253
Chapter 8: Doing What-If Analysis . . . . . . . . . . . . . . . . . . . . . . . . . . . . .255
Playing What-If with Data Tables 255 Creating a one-variable data table 256
Creating a two-variable data table 259 Playing What-If with Goal Seeking .261
Examining Different Cases with Scenario Manager 263 Setting up the various scenarios ..263
Producing a summary report ..265
Chapter 9: Playing with Pivot Tables . . . . . . . . . . . . . . . . . . . . . . . . . . .267
Pivot Tables: The Ultimate Data Summary .267
Producing a Pivot Table ..268
Formatting a Pivot Table .271 Refi ning the Pivot Table style ..272
Formatting the values in the pivot table ..272
Sorting and Filtering the Pivot Table Data ..273 Filtering the report .273
Filtering individual column and row fi elds .274
Filtering with slicers ..275
Sorting the pivot table ..276
Modifying a Pivot Table 277 Modifying the pivot table fi elds .277
Pivoting the table’s fi elds 278
Modifying the table’s summary function .278
Get Smart with a Pivot Chart .280 Moving a pivot chart to its own sheet 280
Filtering a pivot chart 281
Formatting a pivot chart .282
Part V: Life beyond the Spreadsheet .. 283
Chapter 10: Charming Charts and Gorgeous Graphics . . . . . . . . . . . .285
Making Professional-Looking Charts 285 Creating a new chart .286
Moving and resizing an embedded chart in a worksheet .288
Moving an embedded chart onto its own chart sheet 288
Customizing the chart type and style from the Design tab .289
Customizing chart elements from the Layout tab .291
Editing the titles in a chart 293
Formatting chart elements from the Format tab 294
Adding Great Looking Graphics ..297 Sparking up the data with sparklines .298
Telling all with a text box 299
The wonderful world of clip art .302
Inserting pictures from graphics fi les 304
Editing clip art and imported pictures ..305
Formatting clip art and imported pictures .305
Adding preset graphic shapes 307
Working with WordArt .308
Make mine SmartArt ..310
Screenshots anyone- .313
Theme for a day 314
Controlling How Graphic Objects Overlap ..315 Reordering the layering of graphic objects 315
Grouping graphic objects ..316
Hiding graphic objects .316
Printing Just the Charts 317
Chapter 11: Getting on the Data List . . . . . . . . . . . . . . . . . . . . . . . . . . . .319
Creating a Data List .319
Adding records to a data list 321
Sorting Records in a Data List ..329 Sorting records on a single fi eld 330
Sorting records on multiple fi elds 331
Filtering the Records in a Data List ..333 Using ready-made number fi lters ..335
Using ready-made date fi lters ..336
Getting creative with custom fi ltering 336
Importing External Data 340 Querying an Access database table .340
Performing a New Web query .342
Chapter 12: Linking, Automating, and Sharing Spreadsheets . . . . . .345
Using Add-Ins in Excel 2010 346
Adding Hyperlinks to a Worksheet ..347
Automating Commands with Macros ..350 Recording new macros .351
Running macros 355
Assigning macros to the Ribbon and
the Quick Access toolbar ..356
Sharing Worksheets 358 Sending a workbook via e-mail 358
Sharing a workbook on a SharePoint Web site 359
Uploading workbooks to your SkyDrive and
editing them with the Excel Web App ..360
Part VI: The Part of Tens . 363
Chapter 13: Top Ten Features in Excel 2010 . . . . . . . . . . . . . . . . . . . . .365
Chapter 14: Top Ten Beginner Basics . . . . . . . . . . . . . . . . . . . . . . . . . .369
Chapter 15: The Ten Commandments of Excel 2010. . . . . . . . . . . . . . .371
Index .. 373
xviii Excel 2010 For Dummies
Introduction
|
I |
’m very proud to present you with Excel 2010 For Dummies, the latest version of everybody’s favorite book on Microsoft Office Excel for readers with no intention whatsoever of becoming spreadsheet gurus.
Excel 2010 For Dummies covers all the fundamental techniques you need to know in order to create, edit, format, and print your own worksheets. In addition to showing you around the worksheet, this book also exposes you to the basics of charting, creating data lists, and performing data analysis. Keep in mind, though, that this book just touches on the easiest ways to get a few things done with these features — I don’t attempt to cover charting, data lists, or data analysis in the same definitive way as spreadsheets: This book concentrates on spreadsheets because spreadsheets are what most regular folks create with Excel.
About This Book
This book isn’t meant to be read cover to cover. Although its chapters are loosely organized in a logical order (progressing as you might when studying Excel in a classroom situation), each topic covered in a chapter is really meant to stand on its own.
Each discussion of a topic briefly addresses the question of what a particular feature is good for before launching into how to use it. In Excel, as with most other sophisticated programs, you usually have more than one way to do a task. For the sake of your sanity, I have purposely limited the choices by usually giving you only the most efficient ways to do a particular task. Later, if you’re so tempted, you can experiment with alternative ways of doing a task. For now, just concentrate on performing the task as I describe.
As much as possible, I’ve tried to make it unnecessary for you to remember anything covered in another section of the book. From time to time, however, you will come across a cross-reference to another section or chapter in the book. For the most part, such cross-references are meant to help you get more complete information on a subject, should you have the time and interest. If you have neither, no problem. Just ignore the cross-references as if they never existed.
How to Use This Book
This book is similar to a reference book. You can start by looking up the topic you need information about (in either the Table of Contents or the index) and then refer directly to the section of interest. I explain most topics conversationally (as though you were sitting in the back of a classroom where you can safely nap). Sometimes, however, my regiment-commander mentality takes over, and I list the steps you need to take to accomplish a particular task in a particular section.
What You Can Safely Ignore
When you come across a section that contains the steps you take to get something done, you can safely ignore all text accompanying the steps (the text that isn’t in bold) if you have neither the time nor the inclination to wade through more material.
Whenever possible, I have also tried to separate background or footnotetype information from the essential facts by exiling this kind of junk to a sidebar (look for blocks of text on a gray background). Often, these sections are flagged with icons that let you know what type of information you will encounter there. You can easily disregard text marked this way. (I’ll scoop you on the icons I use in this book a little later.)
Foolish Assumptions
I’m going to make only one assumption about you (let’s see how close I get): You have access to a PC (at least some of the time) that is running Windows 7, Windows Vista, or Windows XP and on which Microsoft Office Excel 2010 is installed. Having said that, I don’t assume that you’ve ever launched Excel 2010, let alone done anything with it.
This book is intended for users of Microsoft Office Excel 2010. If you’re using Excel for Windows version Excel 97 through 2003, the information in this book will only confuse and confound you because only Excel 2007 works similar to the 2010 version that this book describes.
If you’re working with a version of Excel earlier than Excel 2007, please put this book down slowly and pick up a copy of Excel 2003 For Dummies instead.
How This Book Is Organized
This book is organized in six parts (which gives you a chance to see at least six of those great Rich Tennant cartoons!). Each part contains two or more chapters (to keep the editors happy) that more or less go together (to keep you happy). Each chapter is divided further into loosely related sections that cover the basics of the topic at hand. However, don’t get hung up on following the structure of the book; ultimately, it doesn’t matter whether you find out how to edit the worksheet before you learn how to format it, or whether you figure out printing before you learn editing. The important thing is that you find the information — and understand it when you find it — when you need to perform a particular task.
In case you’re interested, a synopsis of what you find in each part follows.
Part I: Getting In on the Ground Floor
As the name implies, in this part I cover such fundamentals as how to start the program, identify the parts of the screen, enter information in the worksheet, save a document, and so on. If you’re starting with absolutely no background in using spreadsheets, you definitely want to glance at the information in Chapter 1 to discover the secrets of the Ribbon interface before you move on to how to create new worksheets in Chapter 2.
Part II: Editing without Tears
In this part, I show you how to edit spreadsheets to make them look good, including how to make major editing changes without courting disaster. Peruse Chapter 3 when you need information on formatting the data to improve the way it appears in the worksheet. See Chapter 4 for rearranging, deleting, or inserting new information in the worksheet. Read Chapter 5 for the skinny on printing your finished product.
Part III: Getting Organized and Staying That Way
Here I give you all kinds of information on how to stay on top of the data that you’ve entered into your spreadsheets. Chapter 6 is full of good ideas on how to keep track of and organize the data in a single worksheet. Chapter 7 gives you the ins and outs of working with data in different worksheets in the same workbook and gives you information on transferring data between the sheets of different workbooks.
Part IV: Digging Data Analysis
This part consists of two chapters. Chapter 8 introduces performing various types of what-if analysis in Excel, including setting up data tables with one and two inputs, performing goal seeking, and creating different cases with Scenario Manager. Chapter 9 introduces Excel’s vastly improved pivot table and pivot chart capabilities that enable you to summarize and filter vast amounts of data in a worksheet table or data list in a compact tabular or chart format.
Part V: Life beyond the Spreadsheet
In Part V, I explore some of the other aspects of Excel besides the spreadsheet. In Chapter 10, you find out just how ridiculously easy it is to create a chart using the data in a worksheet. In Chapter 11, you discover just how useful Excel’s data list capabilities can be when you have to track and organize a large amount of information. In Chapter 12, you find out about using add-in programs to enhance Excel’s basic features, adding hyperlinks to jump to new places in a worksheet, to new documents, and even to Web pages, as well as how to record macros to automate your work.
Part VI: The Part of Tens
As is the tradition in For Dummies books, the last part contains lists of the top ten most useful and useless facts, tips, and suggestions. In this part, you find three chapters. Chapter 13 provides my top ten list of the best new features in Excel 2010 (and boy was it hard keeping it to just ten). Chapter 14 gives you the top ten beginner basics you need to know as you start using this program. Chapter 15 gives you the King James Version of the Ten Commandments of Excel 2010. With this chapter under your belt, how canst thou goest astray?
Conventions Used in This Book
The following information gives you the lowdown on how things look in this book. Publishers call these items the book’s conventions (no campaigning, flag-waving, name-calling, or finger-pointing is involved, however).
Throughout the book, you’ll find Ribbon command sequences (the name on the tab on the Ribbon and the command button you select) separated by a command arrow, as in:
Home?Copy
This shorthand is the Ribbon command that copies whatever cells or graphics are currently selected to the Windows Clipboard. It means that you click the Home tab on the Ribbon (if it isn’t displayed already) and then click the Copy button (that sports the traditional side-by-side page icon).
Some of the Ribbon command sequences involve not only selecting a command button on a tab but then also selecting an item on a drop-down menu. In this case, the drop-down menu command follows the name of the tab and command button, all separated by command arrows, as in:
Formulas Calculation Options- Manual
This shorthand is the Ribbon command sequence that turns on manual recalculation in Excel. It says that you click the Formulas tab (if it isn’t displayed already) and then click the Calculation Options button followed by the Manual drop-down menu option.
Although you use the mouse and keyboard shortcut keys to move your way in, out, and around the Excel worksheet, you do have to take some time to enter the data so that you can eventually mouse around with it. Therefore, this book occasionally encourages you to type something specific into a specific cell in the worksheet. Of course, you can always choose not to follow the instructions. When I tell you to enter a specific function, the part you should type generally appears in bold type. For example, =SUM(A2:B2) means that you should type exactly what you see: an equal sign, the word SUM, a left parenthesis, the text A2:B2 (complete with a colon between the letternumber combos), and a right parenthesis. You then, of course, have to press Enter to make the entry stick.
Occasionally, I give you a hot key combination that you can press in order to choose a command from the keyboard rather than clicking buttons on the Ribbon with the mouse. Hot key combinations are written like this: Alt+FS or Ctrl+S (both of these hot key combos save workbook changes).
With the Alt key combos, you press the Alt key until the hot key letters appear in little squares all along the Ribbon. At that point, you can release the Alt key and start typing the hot key letters (by the way, you type all lowercase hot key letters — I only put them in caps to make them stand out in the text).
Hot key combos that use the Ctrl key are of an older vintage and work a little bit differently. You have to hold down the Ctrl key while you type the hot key letter (though again, type only lowercase letters unless you see the Shift key in the sequence, as in Ctrl+Shift+C).
Excel 2010 uses only one pull-down menu (File) and one toolbar (the Quick Access toolbar). You open the File pull-down menu by clicking the File tab or pressing Alt+F. The Quick Access toolbar with its four buttons appears to the immediate right of the File tab.
Finally, if you’re really observant, you may notice a discrepancy in how the names of dialog box options (such as headings, option buttons, and check boxes) appear in the text and how they actually appear in Excel on your computer screen. I intentionally use the convention of capitalizing the initial letters of all the main words of a dialog box option to help you differentiate the name of the option from the rest of the text describing its use.
Icons Used in This Book
The following icons are placed in the margins to point out stuff you may or may not want to read.
This icon alerts you to nerdy discussions that you may well want to skip (or read when no one else is around).
This icon alerts you to shortcuts or other valuable hints related to the topic at hand.
This icon alerts you to information to keep in mind if you want to meet with a modicum of success.
This icon alerts you to information to keep in mind if you want to avert complete disaster.
Where to Go from Here
If you’ve never worked with a computer spreadsheet, I suggest that, right after getting your chuckles with the cartoons, you first go to Chapter 1 and find out what you’re dealing with. If you’re someone with some experience with earlier versions of Excel, I want you to head directly to the section, “Migrating to Excel 2010 from Earlier Versions Using Pull-down Menus” in Chapter 1, where you find out how to stay calm as you become familiar and, yes, comfortable with the Ribbon user interface.
Then, as specific needs arise (such as, “How do I copy a formula?” or “How do I print just a particular section of my worksheet?”), you can go to the Table of Contents or the index to find the appropriate section and go right to that section for answers.
Chapter 1 The Excel 2010 User Experience
In This Chapter
- Getting familiar with the Excel 2010 program window and Backstage View
- Selecting commands from the Ribbon
- Customizing the Quick Access toolbar
- Methods for starting Excel 2010
- Surfing an Excel 2010 worksheet and workbook
- Getting some help with using this program
- Quick start for users migrating to Excel 2010 from earlier versions using pull-down menus
|
T |
he Excel 2010 user interface, like Excel 2007, scraps its reliance on a series of pull-down menus, task panes, and multitudinous toolbars. Instead, it uses a single strip at the top of the worksheet called the Ribbon that puts the bulk of the Excel commands you use at your fingertips at all times.
Add to the Ribbon a File tab and a Quick Access toolbar — along with a few remaining task panes (Clipboard, Clip Art, and Research) — and you end up with the handiest way to crunch your numbers, produce and print polished financial reports, as well as organize and chart your data. In other words, to do all the wonderful things for which you rely on Excel.
Best of all, this new and improved Excel user interface includes all sorts of graphical improvements. Foremost is Live Preview that shows you how your actual worksheet data would appear in a particular font, table formatting, and so on before you actually select it. Additionally, Excel 2010 supports an honest to goodness Page Layout View that displays rulers and margins along with headers and footers for every worksheet and has a zoom slider at the bottom of the screen that enables you to zoom in and out on the spreadsheet data instantly. Finally, Excel 2010 is full of pop-up galleries that make spreadsheet formatting and charting a real breeze, especially in tandem with Live Preview.
Excel’s Ribbon User Interface
When you launch Excel 2010, the program opens the first of three new worksheets (named Sheet1) in a new workbook file (named Book1) inside a program window like the one shown in Figure 1-1.
The Excel program window containing this worksheet of the workbook contains the following components:
- File tab that when clicked opens the new Backstage View — a menu on the left that contains all the document- and file-related commands, including Info (selected by default), Save, Save As, Open, Close, Recent, New, Print, and Save & Send. Additionally, there’s a Help option with add-ins, an Options item that enables you to change many of Excel’s default settings, and an Exit option to quit the program.
- Customizable Quick Access toolbar that contains buttons you can click to perform common tasks, such as saving your work and undoing and redoing edits.
- Ribbon that contains the bulk of the Excel commands arranged into a series of tabs ranging from Home through View.
- Formula bar that displays the address of the current cell along with the contents of that cell.
- Worksheet area that contains the cells of the worksheet identified by column headings using letters along the top and row headings using numbers along the left edge; tabs for selecting new worksheets; a horizontal scroll bar to move left and right through the sheet; and a vertical scroll bar to move up and down through the sheet.
- Status bar that keeps you informed of the program’s current mode and any special keys you engage, and enables you to select a new worksheet view and to zoom in and out on the worksheet.