Katz / Made Simple Learning Excel 2010 Made Simple

1. Auflage 2011
ISBN: 978-1-4302-3546-0
Verlag: APRESS
Format: PDF
Kopierschutz: Wasserzeichen (»Systemvoraussetzungen)

E-Book, Englisch, 363 Seiten, eBook

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.

Zielgruppe

Popular/general

Autoren/Hrsg.

Katz, Abbott

Made Simple Learning, MSL

Inhaltsverzeichnis

1;Cover;2
2;Title Page;2
3;Copyright Page;3
4;Contents at a Glance;4
5;Table of Contents;5
6;About the Author;11
7;About the Technical Reviewer;12
8;Acknowledgments;13
9;Quick Start Guide;14
9.1;The Excel Worksheet: What You’re Looking At;14
9.2;Key Tips: Accessing Buttons with the Keyboard;18
9.3;Contextual Tabs;19
9.4;A Visit Backstage;20
9.5;Customizing the Quick Access Toolbar;21
9.6;Where to Learn More;25
9.7;Excel Keyboard Equivalents;31
10;Chapter 1 Introducing Excel 2010;39
10.1;The Advantages of Learning More;39
10.2;Spreadsheets Defined;40
10.3;Excel Can Be Fun;42
10.4;Summary;42
11;Chapter 2 Getting Around the Worksheet and Data Entry;43
11.1;The Journey Starts Here;43
11.2;Looking Around;43
11.3;Getting Around a Worksheet;43
11.4;Selecting Multiple Cells;46
11.4.1;Selecting Cells Down and Across the Worksheet;47
11.4.2;Selecting Cells with the Keyboard;48
11.4.3;Selecting All the Cells;48
11.4.4;Still One More Selection Technique—The Name Box;49
11.5;Entering Text and Data;50
11.6;Aligning Your Data—Where It Appears in the Cell;51
11.7;Widening and Narrowing Columns;53
11.7.1;Altering a column manually;53
11.7.2;Using the Auto-fit Feature;54
11.8;Entering Numerical Data—How it’s Different;56
11.9;Entering Data into a Selected Range;57
11.10;Using Auto Fill to Speed Up Data Entry;60
11.10.1;Copying a Value with Auto Fill;60
11.10.2;Auto Filling a Numeric Sequence;60
11.10.3;Using Auto Fill with Text;62
11.10.4;Using the Auto Fill Option Button;62
11.10.5;Customizing Auto Fill Lists;65
11.11;Data Validation: Bringing Quality Control to the Worksheet;66
11.11.1;Making a List—Personalizing a Drop-Down Menu;69
11.11.2;Explaining Data Validation Errors with Error Alerts;71
11.12;Adding Data Entry Instructions with Input Messages;72
11.13;Summary;73
12;Chapter 3 Editing Data;74
12.1;Changing Your Data;74
12.1.1;Undoing an Edit;75
12.1.2;Undoing What You’ve Just Undone with the Redo Button;77
12.1.3;Deleting Cell Contents;77
12.2;Copying and Moving: Duplicating and Relocating Your Data;77
12.2.1;Copying Data;78
12.2.2;Moving Data;80
12.3;The Clipboard: The Storage Area for Copied and Cut Data;80
12.4;Summary;82
13;Chapter 4 Number Crunching 101: Functions, Formulas, and Ranges;83
13.1;Automatic Calculations with Functions;83
13.1.1;Adding a Column of Numbers;84
13.1.2;Selecting the Range You Need;86
13.1.3;Viewing and Editing Your Formula: Back to the Formula Bar;87
13.1.4;Calculating an Average;89
13.1.5;Displaying Values Based on a Certain Condition;90
13.1.6;Revisiting Function Structure;92
13.1.7;Locating Functions in the Function Library;92
13.2;Customizing the Worksheet with Formulas;94
13.2.1;Writing a Basic Formula;95
13.2.2;Working Out the Order of Operations in a Formula;96
13.2.3;Copying Formulas: More Than Just Duplication;97
13.2.3.1;The Classic Copy-and-Paste Method;98
13.2.3.2;Moving Formulas: An Important Difference;99
13.2.3.3;Keeping a Cell Reference Constant with Absolute Addressing;100
13.2.3.3.1;Absolute Addressing: When We Don’t Want It to Work;101
13.2.3.3.2;Introducing an Absolute Cell Address;102
13.2.3.4;Copying a Formula’s Result Only;103
13.3;Clarifying Cell References by Naming a Range;105
13.3.1;Naming a Range in the Name Box;105
13.3.2;Naming Ranges from the Data in Your Worksheet;109
13.3.3;Naming A Range Containing One Cell: Why Bother?;110
13.3.4;The Name Manager: Where They’re All Ar-ranged;111
13.4;Summary;112
14;Chapter 5 For Appearance’s Sake: Formatting Your Data;113
14.1;What Formatting Does (and Doesn’t Do);113
14.2;Basic Formatting;114
14.2.1;Changing the Font;114
14.2.2;Changing the Font Size;116
14.2.3;Using Bold, Italics, and Underline;117
14.2.4;Determining a Cell’s Formatting;118
14.2.5;Adding a Border;119
14.2.6;Adding Color to Your Cells;121
14.2.7;Adding Extra Formatting;123
14.3;Aligning (and Realigning) Your Data;124
14.3.1;Changing Horizontal Alignment;124
14.3.2;Changing Vertical Alignment;125
14.3.3;Changing Data Orientation;126
14.3.4;Indenting Data;129
14.3.5;Wrapping Text;129
14.3.6;Adding a Title with Merge and Center;130
14.4;Inserting, Deleting, and Hiding Columns and Rows;131
14.4.1;Inserting a Column or Row;132
14.4.2;Inserting Multiple Columns or Rows;133
14.4.3;What Inserting Does to Formulas;134
14.4.4;Deleting Columns and Rows;134
14.4.5;Hiding Rows and Columns;135
14.4.6;Unhiding Columns and Rows;137
14.4.7;Inserting and Deleting Cells;138
14.5;Formatting Values: Making the Numbers Look Good;139
14.5.1;Turning Values into Currency;139
14.5.2;Working with Percentages;142
14.5.3;Punctuating Values;142
14.5.4;Formatting Decimal Points;143
14.5.5;Working with Dates: Dates Are Numbers Too;144
14.6;Customizing Number Formats;147
14.6.1;The Special Formats Option;147
14.6.2;The Custom Option;148
14.7;Copying Formats (Not Data) with the Format Painter;150
14.8;Applying Ready-Made Formats with Styles;151
14.8.1;Customizing Your Own Style;152
14.9;Applying Styles Quickly: Another Way to Access Formatting Options;154
14.10;Conditional Formatting;155
14.10.1;Looking for Scores Equal to or Greater Than 90;159
14.10.2;An Alternative Approach to the Same Result;159
14.10.3;Some Additional Conditional Formatting Options;160
14.10.4;Turning Off Conditional Formatting;162
14.10.5;Using Data Bars: A Different Kind of Conditional Format;162
14.11;Summary;163
15;Chapter 6 Charting Your Data;164
15.1;Defining Chart Elements;165
15.2;Choosing a Chart Type;166
15.3;Creating a Column Chart;168
15.4;Creating a Chart in 2 Seconds;169
15.5;Changing a Chart;169
15.5.1;Moving and Resizing the Chart;169
15.5.2;Changing the Chart by Changing Its Data;171
15.5.3;Changing the Chart Type;172
15.5.4;Where’s the Pie Chart?;176
15.5.5;Changing the Default Chart;176
15.6;Switching Rows with Columns: A Different Kind of Change;177
15.7;Formatting Charts;178
15.7.1;Formatting with the Mini-Toolbar;179
15.7.2;Formatting with the Context Menu;180
15.7.3;Formatting with the Current Selection Button Group;180
15.7.4;Exploring the Format Dialog Box;181
15.7.5;Reformatting the Vertical Axis;184
15.7.6;Color-Coordinating Your Data Using Chart Styles;186
15.7.7;Formatting a Chart Object Using Shape Styles;187
15.7.8;Formatting Text Elements with WordArt;188
15.7.9;Adding Extra Chart Elements with Chart Layouts;189
15.7.10;Adding Extra Chart Elements with the Layout Tab;190
15.7.10.1;Chart Title;190
15.7.10.2;Axis Titles;192
15.7.10.3;Legend;193
15.7.10.4;Data Labels;193
15.7.10.5;Data Table;194
15.7.10.6;Axes;194
15.7.10.7;Plot Area;197
15.8;Introducing Sparklines: Mini-Charts Placed in Cells;198
15.8.1;Modifying Sparklines;200
15.8.2;Representing Binary Values with Sparklines;201
15.9;Summary;202
16;Chapter 7 Sorting and Filtering Your Data: Excel’s Database Features;203
16.1;Sorting Data: Instilling Order in Your Data;204
16.1.1;Sorting by Two Fields: The Hows and Whys;207
16.1.2;Sorting by Cell Format;209
16.2;Finding What You Want with Filters;210
16.2.1;Clearing a Filter;213
16.2.2;Text and Number Filters: Filters Within the Filter;213
16.2.3;Filtering Multiple Fields;216
16.3;Tables: Adding User-Friendliness to Your Database;217
16.3.1;Finding Duplicate Records in the Table (and Removing Them);224
16.3.2;Converting a Table to a Range;225
16.4;Summary;226
17;Chapter 8 PivotTables: Data Aggregation Without the Aggravation;227
17.1;Looking at Some PivotTables;227
17.2;Creating a PivotTable;232
17.2.1;Choosing Which Data to Work On;235
17.2.2;Getting the Fields Where You Want Them;236
17.2.3;Pivoting the Data Sideways Using the Column Labels Area;237
17.2.4;Filtering Items Using the Report Filter Area;237
17.2.5;Creating a Report Worksheet for Each Item in a Filter;239
17.2.6;Counting Records: A Way to Break Out Text Data;240
17.3;Grouping Related Items Using Two Fields;242
17.3.1;Using the Row and Column Value Areas to Group Items;245
17.3.2;Changing the Calculation;245
17.3.3;Grouping PivotTable Data: Organizing Your Time(s);247
17.3.4;Refreshing the PivotTable: Changing the Data;248
17.3.5;Adding New Records to a PivotTable;249
17.4;Viewing Which Records Are Filtered: Using the Slicer;250
17.4.1;How the Slicer Works;251
17.4.2;Restyling the Slicer;254
17.5;Formatting the PivotTable;255
17.6;Styling Your Report;257
17.6.1;Changing PivotTable Headers;258
17.6.2;Layout Options;259
17.7;Creating Charts from PivotTables Using PivotCharts;261
17.7.1;Filtering Data in the Chart with Field Buttons;264
17.7.2;Creating a PivotTable and PivotChart Together;266
17.8;Summary;267
18;Chapter 9 Managing Your Workbook;268
18.1;Adding Worksheets to Your Workbook;268
18.1.1;Clicking Through the Worksheets;269
18.1.2;Adding and Moving New Worksheets;270
18.1.3;Deleting Sheets;272
18.1.4;Copying a Sheet;273
18.1.5;Renaming and Recoloring the Worksheet Tabs;273
18.1.6;Hiding Sheets;274
18.2;Grouping Sheets: Changing Multiple Sheets at the Same Time;275
18.2.1;How to Group Sheets;276
18.2.2;Ungrouping the Sheets;276
18.2.3;Referring to Cells in Other Worksheets: Using Them in Formulas;277
18.2.4;Using Ranges on Other Sheets in Formulas;278
18.3;Using the View Context Tab to Show and Hide Basic Screen Elements;280
18.3.1;Showing Formulas in Cells;281
18.3.2;Hiding the Ribbon;282
18.3.3;Keeping Important Data in View with the Freeze Panes Option;283
18.3.4;Freezing Rows and Columns at the Same Time;285
18.4;Protecting the Worksheet and the Workbook;286
18.4.1;Protecting a Worksheet;287
18.4.2;Using a Password: Some Extra Protection;288
18.4.3;Unprotecting a Worksheet;289
18.4.4;Protecting Some, but Not All, of a Worksheet;289
18.4.5;Hiding Formulas;291
18.4.6;Protecting a Workbook;293
18.4.7;Unprotecting a Workbook;295
18.5;Summary;295
19;Chapter 10 Printing Your Worksheets: Hard Copies Made Easy;296
19.1;Deciding What You Want to Print;296
19.1.1;Printing the Entire Worksheet;296
19.1.2;Printing a Selection;297
19.2;Surveying Printing Options: The Print Backstage;301
19.3;Setting the Print Area;307
19.4;Customizing Your Printing;309
19.4.1;Working with Page Breaks;309
19.4.2;Previewing the Page Break: Getting a Bird’s-Eye View of the Printout;312
19.4.3;Printing Titles;315
19.5;Adding Headers and Footers;319
19.5.1;Adding Headers and Footers in the Page Layout View;319
19.5.2;Adding Headers and Footers Using the Page Setup Dialog Box;321
19.5.3;Adding Custom Headers and Footers;323
19.6;Printing the Gridlines and Headings;326
19.7;Summary;328
20;Chapter 11 Automating Your Work with Macros;329
20.1;The Two Kinds of Macros;329
20.2;Composing a Macro;330
20.3;About Saving a Workbook with a Macro;332
20.3.1;Playing Back the Macro;333
20.3.2;What We’ve Done;334
20.3.3;Relative References in a Macro;334
20.4;Saving a Macro to the Personal Macro Workbook;336
20.5;Deleting a Macro;338
20.6;Editing a Macro by Tweaking It in VBA;338
20.6.1;VBA: Written Behind the Scenes;339
20.6.2;Exposing the VBA Worksheet;339
20.7;Activating a Macro with a Keyboard Shortcut;341
20.8;A Note on Macro Security;343
20.9;Summary;344
21;Index;345

Über Autor(innen)

Abbott Katz, author of Beginning Microsoft Excel 2010, brings extensive Microsoft Office instructional experience to the task, having trained users of the suite s components in varied academic and corporate settings. He has a doctorate in sociology and wide-ranging writing experience as well. Abbott has composed, and continue to compose, numerous complex spreadsheets, twinning technical fluency with the imagination to realize Excel s potential in a diverse array of settings.