Excel 2010 - No Problem!

Deanna Reynolds (Bellingham, WA) (MCTS) is an author and technical instructor who has taught on a multitude of software programs, including the entire Microsoft Office suite, project management, desktop publishing, and database development. Throughout her career, Deanna has led hundreds of computer productivity training sessions, from beginner through advanced levels.

How to use this book xiii Learn your way xiv

This book is like a recipe xiv

Active learning helps you remember xv

Link new knowledge to what you already know xvi

Mysteries are memorable xvii

Why `take a break’ before the end of each chapter? xviii

Learn. Review. Repeat. xix

Review xix

1 Get your information in there—fast! 1

It’s like Excel 2010 received an extreme makeover 2

Comfortable . . . like your favorite pair of jeans 4

Why type it twice when you could do it just once? 5

Make quick work of lists 6

It’s time to do some math 9

Common calculations everyone should know 13

Review 16

Experiment 18

2 Filter and sort your data 21

Organize lots of data 22

Does your list follow the rules? 22

Convert your list into a table 24

AutoFilter your table data 25

Have lots of info? Organize it! 28

Sort your data 28

Filter your data 29

Review 34

Experiment 35

3 Get your Excel file show-ready, fast 37

Save time with a style guide 38

Dress up your plain text 39

Format a whole row 39

Format part of a cell 40

Format non-adjacent cells 41

It’s all about the symbols 41

Make room for the unexpected 42

Deal with long column entries manually 43

Deal with long column entries automatically 44

Set a fixed column width 44

That’s a wrap 46

Give your worksheet a title 48

Delete it, hide it, or show it? 49

Hide non-essential rows 50

Add a single cell 52

Cut and paste information into a new workbook 53

Last-minute changes 53

Find and Replace 54

Format Painter 55

Share your workbook 56

Perfectly printed 57

PDF—everybody’s doing it 57

Send an Excel file by e-mail 59

Review 60

Experiment 62

4 Take a walk on the 3D side 65

There’s no need to reinvent the wheel 66

Follow the strictest company guidelines 67

Create custom number formats 68

Custom number symbol cheat sheet 69

Create a custom number format from scratch 74

It’s 3D without the big glasses 77

Set it up, then lock it up 80

Keep a copy for reference 82

Review 83

Experiment 85

5 Create the perfect invoice 87

Put it all together and see what you’ve got 88

Indent to impress 91

Excel 2010—No Problem!

Interactive invoicing 92

Add hyperlinks to your invoice 92

Create a template 94

Digital signatures 97

Review 100

Experiment 101

6 If you had to grade 133 students 103

Make your text fit another way 104

A special kind of paste 106

Watch for trends or problems 107

Add comments 108

Calculate the final grade 110

Can IF help you work out the final grades? 114

Review 118

Experiment 119

7 Create a project status report 121

Highlight key information 122

Borders and shading make cells stand out 123

Say it anywhere 125

Enhance your visual perception 128

Show it with a picture 131

Review 134

Experiment 135

8 Track customer billable hours by company 139

Bring in data stored elsewhere 140

Import .csv files 141

Import data from other Excel files 144

Import data from text files 147

Color makes tabs easier to find 151

View more than one worksheet at the same time 152

Create totals for billing 155

Automatic subtotals help you bill regularly 155

Review 158

Experiment 160

9 Create an interactive data view 163

Share and share alike 164

Share it as a different file type 164

Change the Print options 166

Make it interactive for even a novice Excel user 171

Validate your data 172

Create PivotTables first 173

Review 180

Experiment 181

10 Fix someone else’s worksheet 185

You can’t calculate what isn’t there 186

Two ways to print a selection 191

What’s that? You need a mailing list? 192

Find your page breaks 195

Add, then save a theme 199

Review 202

Experiment 204

11 Determine sales commissions 207

Absolute cell references 208

Get a handle on the view 211

DSUM 214

Review 219

Experiment 221

12 Cut a list down to size 223

Garbage in, garbage out 224

Find and remove duplicates 224

Grab what you need and leave the rest 226

Use an Advanced Filter 227

What if there are multiple criteria? 229

Pull out specific data with a condition and the right operator 230

Your results are as pretty as a picture 235

Review 236

Experiment 237

Excel 2010—No Problem!

13 Chart your data 239

A picture’s worth a thousand words 240

Add a pie chart 240

Get it print ready 246

Review 249

Experiment 250

14 It’s all about the sales 253

“What if . . .?” 254

Use the Scenario Manager to track scenarios 255

Create a PivotChart 257

Keep an eye on things 261

Look it up, vertically 262

Review 269

Experiment 270

15 Create a client proposal 273

Bring all the data together 274

Hold it! 277

Give your workbooks some style 280

Add a flow chart 283

Make it available for all to see 286

Review 289

Experiment 290

16 Track expenses across a team 293

Do you validate? 294

Prevent errors before they have the chance to happen 297

Shared files let team members enter their own updates 299

See who did what and where 301

Review 305

Experiment 306

Answers from chapter 1 (page 19) 307

Answers from chapter 2 (page 35) 307

Answers from chapter 10 (page 195) 308

Index 309