Excel Portable Genius

YOUR GENIUS-LEVEL GUIDE TO EXCEL EXCELLENCE

Excel Portable Genius covers the key features of Microsoft Excel in a concise, fast-paced, portable format that provides the perfect quick and easy-to-follow reference for novices and experienced users alike. Written by seasoned tech expert and trainer Lisa A. Bucki, it answers the nine key questions Excel users are most likely to face in their work and adds some hot tips and advice on this incredibly useful—but sometimes difficult-to-learn—program's lesser-known features.

* Build formulas to perform powerful calculations
* Format data to make it easy to find interpret and to highlight key results
* Sort and filter to organize or display specific information
* Chart data to pinpoint changes and identify trends
* Communicate results by printing and sharing

Complete with a comprehensive index and extensive illustrations complementing straightforward instructions, this is the ideal guide to mastery of the world's most popular spreadsheet program.

LISA A. BUCKI is an author, trainer, and content creator who's been educating others about computers and software since 1990. She's written dozens of books, provided marketing and training services to her clients, and written online tutorials. She is the co-founder of 1x1 Media.

Fun, hip, and straightforward, the Portable Genius series gives forward-thinking computer users useful information in handy, compact books that are easy to navigate and don't skimp on the essentials. Collect the whole series and make the most of your digital lifestyle.

Acknowledgments vii

Introduction xiv

CHAPTER 1 HOW DO I START USING EXCEL? 2

Starting and Exiting Excel 4

Taking a Look Around 5

Reviewing key screen features 5

Workbooks versus worksheets 8

Changing views 8

Zooming 10

Working with Files 11

Creating a blank file 11

Exploring templates 12

Opening an existing file 13

Switching to another file 14

Making Your First Cell Entries 16

Moving around the sheet 16

Text and values 17

Dates and times 19

Number formatting on the fly 20

Making Selections 21

Understanding cells and ranges 22

Selecting a range 22

Selecting a row or column 23

Working with Sheets 24

Adding, renaming, and jumping to a sheet 25

Moving or copying a sheet 26

Saving and Closing a File 27

CHAPTER 2 HOW DO I MAKE CHANGES TO MY SHEET? 30

Cell Editing Basics 32

Making changes 32

Commenting when editing 33

Clearing cell contents 33

Using Undo and Redo 34

Spell-checking your work 35

Copying or Moving a Range 35

Using Paste Special 37

Using Auto Fill and Filling Series 38

Inserting and Deleting Rows, Columns, and Cells 41

Creating Range Names 44

Using the Name box 45

Using the New Name dialog box 46

Assigning multiple range names at once 47

Deleting a range name 48

Using Find and Replace 50

Freezing Rows and Columns On-Screen 51

CHAPTER 3 HOW DO I ADD UP THE NUMBERS WITH FORMULAS? 54

Entering a Basic Formula 56

Typing the formula 56

Cell and range references in formulas 57

Using the mouse to save time 58

Dealing with minor errors 60

Learning More About Operators 60

Understanding Order of Precedence 63

How order of precedence works 63

Using parentheses in formulas 64

Making a Cell or Range Reference Absolute Rather Than Relative 66

Changing the reference type in a formula 66

How referencing works when filling, copying, or moving a formula 67

Showing Formulas in Cells 69

Using Other Formula Review Techniques 70

CHAPTER 4 WHEN DO I NEED TO INCLUDE A FUNCTION? 72

Understanding Functions 74

Using AutoSum on the Home or Formulas Tab 74

Typing a Function in a Formula 77

Using the Formulas Tab to Insert a Function 79

Choosing from the Function Library 79

Using Insert Function to find a function 82

Using Range Names in Formulas 83

Reviewing Some Essential Functions 84

CHAPTER 5 HOW DO I USE FORMATTING TO ENHANCE MY WORKSHEET? 90

Changing the Number or Date Format 92

Using number formats 94

Using date and time formats 96

Using special and custom formats 96

Changing Cell Formatting 99

Applying font formatting 100

Applying alignment formatting 103

Working with Borders and Shading 105

Using Styles 107

Clearing Formatting 109

Understanding Themes 109

Reviewing elements of a theme 109

Changing themes 110

Changing theme colors, fonts, and effects 111

Working with Column Width and Row Height 113

Using Conditional Formatting to Highlight Information 115

CHAPTER 6 HOW DO I USE GRAPHICS IN EXCEL? 118

Inserting Simple Graphics 120

Shapes 120

Pictures 122

Icons 126

Selecting and Formatting Graphics 127

Adding text to shapes 128

Changing styles 129

Changing sizing and position 132

Working with layering, alignment, and rotation 134

Inserting WordArt 136

Combining and Creating Your Own Graphics 138

Creating and Working with SmartArt 141

Adding a SmartArt graphic 141

Editing and rearranging shapes 143

Changing the graphic type and formatting 144

Deleting a Graphic 145

Adding a Background Image 146

CHAPTER 7 HOW DO I MANAGE LISTS OF INFORMATION? 148

Arranging a List in Excel 150

Cleaning Up Data 152

Using Flash Fill 152

Using Text to Columns 155

Removing duplicates and consolidating 157

Sorting, Filtering, and Subtotaling Lists of Information 159

Sorting and filtering a list 160

Subtotaling a sorted list 162

Understanding Excel’s Table Feature 163

Converting a range to a table 164

Importing or connecting to a list of data 166

Changing table styles 168

Resizing a table 169

Renaming a table 170

Sorting and Filtering a Table 170

Using Table Calculations 170

Adding a calculated column 171

Adding a total row 173

Converting a Table Back to a Range of Regular Cells 174

CHAPTER 8 HOW DO I PRESENT MY DATA IN CHARTS? 176

Charts and Their Role in Your Workbooks 178

Chart Types 179

Creating a Chart 180

Changing the Chart Type and Layout 184

Formatting Chart Elements 186

Changing the Charted Data 189

Moving a Chart to Its Own Sheet and Deleting a Chart 190

Using Special Charts: Sparklines, PivotTables, and Maps 192

CHAPTER 9 HOW DO I PRINT AND SHARE MY CONTENT? 194

Creating Headers and Footers 196

Changing Page Settings 199

Margins 199

Orientation and paper size 200

Scaling 201

Changing Sheet Settings 201

Adding print titles 201

Controlling whether gridlines and other features print 203

Working with Page Breaks 203

Setting a print area 204

Viewing and moving breaks 204

Previewing a Printout and Printing 206

Other Ways to Share Information 207

Exporting a PDF 208

Using OneDrive 209

Index 212