Tasuta saatmine! | Klienditugi: 7440010 (E-R 10-18)

Abi | Registreeri | Logi sisse

E-raamat: John Walkenbach's Favorite Excel 2010 Tips and Tricks [Wiley Online]

John Walkenbach

Formaat: 504 pages, ill
Sari: Mr. Spreadsheet's Bookshelf
Ilmumisaeg: 09-Jul-2010
Kirjastus: John Wiley & Sons Ltd
ISBN-10: 1118257642
ISBN-13: 9781118257647

Teised raamatud teemal:

Excel

Wiley Online
Hind: 42,28 €*
* hind, mis tagab piiramatu üheaegsete kasutajate arvuga ligipääsu piiramatuks ajaks

Formaat: 504 pages, ill
Sari: Mr. Spreadsheet's Bookshelf
Ilmumisaeg: 09-Jul-2010
Kirjastus: John Wiley & Sons Ltd
ISBN-10: 1118257642
ISBN-13: 9781118257647

Teised raamatud teemal:

Excel

Rohkem infot Wiley Online kohta

Raamatu kodulehekülg: https://onlinelibrary.wiley.com/doi/book/10.1002/9781118257647

Presents a collection of shortcuts and workarounds for Microsoft Excel 2010, covering such topics as creating charts, using formulas, running macros, and creating custom add-ins.

With this collection of Mr. Spreadsheet's favorite Excel tips and tricks, you get a unique look at ways to make Excel 2010 more efficient than you've ever imagined.

The book is filled with easy-to-understand suggestions that touch on all aspects of Excel 2010, including how to work with the Backstage View and how to use the new Aggregate function. This hands-on resource will help you maximize the power of Excel to create robust applications.

With John Walkenbach's Favorite Excel 2010 Tips & Tricks, you'll get a jump-start on mastering the extensive changes to the newest version of Excel.

Build robust Excel 2010 apps quickly and efficiently

Known as "Mr. Spreadsheet," John Walkenbach's name is synonymous with excellence in computer books that explain the complexities of various topics. With this collection of favorite Excel tips and tricks, you get a unique look at ways to make Excel 2010 more efficient than you ever imagined.

Packed with easy-to-understand advice regarding all aspects of Excel, this book shares improved ways of speeding up application development with Excel and maximizing the power of Excel to create robust applications.

Addresses the extensive changes to the 2010 version of Excel and shares tricks and shortcuts for making your Excel experience as successful and efficient as possible
Reveals ways to deal with function arguments, create "impossible" charts, and tame the Ribbon bar
Discusses absolute vs. relative references, change data entry orientation, and sort more than three columns
Demonstrates ways to enter fake data for testing purposes

With John Walkenbach's Favorite Excel 2010 Tips and Tricks, you'll get a jump start on mastering the extensive changes to the 2010 version of Excel.

Part I Basic Excel Usage

Tip 1 Understanding Excel Versions

(3)

Tip 2 Maximizing Ribbon Efficiency

(3)

Tip 3 Understanding Protected View

(2)

Tip 4 Selecting Cells Efficiently

(3)

Selecting a range by using the shift and arrow keys

(1)

Selecting the current region

(1)

Selecting a range by Shift+clicking

(1)

Selecting noncontiguous ranges

(1)

Selecting entire rows

(1)

Selecting entire columns

(1)

Selecting multisheet ranges

(1)

Tip 5 Making "Special" Range Selections

(2)

Tip 6 Undoing, Redoing, and Repeating

(3)

Undoing

(1)

Redoing

(1)

Repeating

(2)

Tip 7 Discovering Some Useful Shortcut Keys

(1)

Tip 8 Navigating Sheets in a Workbook

(1)

Tip 9 Resetting the Used Area of a Worksheet

(1)

Tip 10 Understanding Workbooks versus Windows

(2)

Tip 11 Customizing the Quick Access Toolbar

(4)

About the Quick Access toolbar

(1)

Adding new commands to the Quick Access toolbar

(3)

Performing other Quick Access toolbar actions

(1)

Tip 12 Customizing the Ribbon

(3)

Tip 13 Accessing the Ribbon with Your Keyboard

(1)

Tip 14 Recovering Your Work

(2)

Recovering versions of the current workbook

(1)

Recovering unsaved work

(2)

Tip 15 Customizing the Default Workbook

(2)

Changing defaults in the Excel Options dialog box

(1)

Creating a book.xltx template

(2)

Tip 16 Using Document Themes

(5)

Applying a theme

(1)

Customizing a theme

(2)

Tip 17 Hiding User Interface Elements

(3)

From the Ribbon

(1)

From the keyboard

(1)

From the Excel Options dialog box

(2)

A setting that requires a macro

(1)

Tip 18 Hiding Columns or Rows

(2)

Hiding

(1)

Unhiding

(2)

Tip 19 Hiding Cell Contents

(1)

Tip 20 Taking Pictures of Ranges

(2)

Creating a static image of a range

(1)

Creating a live image of a range

(1)

Tip 21 Performing Inexact Searches

(2)

Tip 22 Replacing Formatting

(3)

Tip 23 Changing the Excel Color Scheme

(1)

Tip 24 Limiting the Usable Area in a Worksheet

(3)

Setting the ScrollArea property

(1)

Using worksheet protection

(2)

Tip 25 Using an Alternative to Cell Comments

(1)

Tip 26 Understanding the Excel Help System

(3)

Tip 27 Making a Worksheet "Very Hidden"

(2)

Tip 28 Working with the Backstage View

(3)

Part II Data Entry

Tip 29 Understanding the Types of Data

(4)

Entering values

(1)

Entering dates and times

(1)

Entering text

(1)

Entering formulas

(1)

Tip 30 Moving the Cell Pointer after Entering Data

(1)

Tip 31 Selecting a Range of Input Cells before Entering Data

(1)

Tip 32 Using AutoComplete to Automate Data Entry

(2)

Tip 33 Removing Duplicate Rows

(2)

Tip 34 Keeping Titles in View

(1)

Tip 35 Automatically Filling a Range with a Series

(3)

Tip 36 Working with Fractions

(2)

Tip 37 Resizing the Formula Bar

(2)

Tip 38 Proofing Your Data with Audio

(2)

Adding speech commands to the Ribbon

(1)

Using the speech commands

(2)

Tip 39 Controlling Automatic Hyperlinks

(2)

Overriding an automatic hyperlink

(1)

Turning off automatic hyperlinks

(1)

Removing existing hyperlinks

(1)

Tip 40 Entering Credit Card Numbers

(1)

Tip 41 Using the Excel Built-In Data Entry Form

(2)

Tip 42 Customizing and Sharing AutoCorrect Entries

(2)

Tip 43 Restricting Cursor Movement to Input Cells

100

(2)

Tip 44 Controlling the Office Clipboard

102

(2)

Tip 45 Creating a Drop-Down List in a Cell

104

(5)

Part III Formatting

Tip 46 Using the Mini Toolbar

109

(1)

Tip 47 Indenting Cell Contents

110

(2)

Tip 48 Quick Number Formatting

112

(1)

Tip 49 Creating Custom Number Formats

113

(4)

Parts of a number format string

114

(1)

Custom number format codes

115

(2)

Tip 50 Using Custom Number Formats to Scale Values

117

(2)

Tip 51 Using Custom Date and Time Formatting

119

(1)

Tip 52 Examining Some Useful Custom Number Formats

120

(3)

Hiding zeros

120

(1)

Displaying leading zeros

120

(1)

Formatting percentages

120

(1)

Displaying fractions

121

(1)

Repeating text

121

(1)

Displaying a negative sign on the right

122

(1)

Suppressing certain types of entries

122

(1)

Tip 53 Updating Old Fonts

123

(2)

Tip 54 Understanding Conditional Formatting Visualization

125

(3)

Data bars

125

(1)

Color scales

126

(1)

Icon sets

126

(2)

Tip 55 Showing Text and a Value in a Cell

128

(2)

Using concatenation

128

(1)

Using the Text function

128

(1)

Using a custom number format

129

(1)

Tip 56 Merging Cells

130

(1)

Tip 57 Formatting Individual Characters in a Cell

131

(1)

Tip 58 Displaying Times That Exceed 24 Hours

132

(2)

Tip 59 Fixing Non-Numeric Numbers

134

(1)

Tip 60 Adding a Frame to a Range

135

(1)

Tip 61 Dealing with Gridlines, Borders, and Underlines

136

(2)

Tip 62 Inserting a Watermark

138

(2)

Tip 63 Adding a Background Image to a Worksheet

140

(1)

Tip 64 Wrapping Text in a Cell

141

(2)

Tip 65 Seeing All Characters in a Font

143

(2)

Tip 66 Entering Special Characters

145

(2)

Tip 67 Using Named Styles

147

(6)

Using the Style Gallery

147

(1)

Modifying an existing style

148

(1)

Creating new styles

149

(1)

Merging styles from other workbooks

150

(3)

Part IV Basic Formulas and Functions

Tip 68 Using Formula AutoComplete

153

(2)

Tip 69 Knowing When to Use Absolute References

155

(2)

Tip 70 Knowing When to Use Mixed References

157

(2)

Tip 71 Changing the Type of a Cell Reference

159

(1)

Tip 72 Converting a Vertical Range to a Table

160

(2)

Tip 73 AutoSum Tricks

162

(2)

Tip 74 Using the Status Bar Selection Statistics Feature

164

(2)

Tip 75 Converting Formulas to Values

166

(1)

Tip 76 Transforming Data without Using Formulas

167

(1)

Tip 77 Transforming Data by Using Temporary Formulas

168

(2)

Tip 78 Deleting Values While Keeping Formulas

170

(1)

Tip 79 Summing Across Sheets

171

(2)

Tip 80 Dealing with Function Arguments

173

(2)

Tip 81 Annotating a Formula without Using a Comment

175

(1)

Tip 82 Making an Exact Copy of a Range of Formulas

176

(2)

Tip 83 Monitoring Formula Cells from Any Location

178

(1)

Tip 84 Displaying and Printing Formulas

179

(2)

Tip 85 Avoiding Error Displays in Formulas

181

(2)

Using the Iserror function

181

(1)

Using the Iferror function

182

(1)

Tip 86 Using Goal Seeking

183

(2)

Tip 87 Understanding the Secret about Names

185

(2)

Tip 88 Using Named Constants

187

(2)

Tip 89 Using Functions in Names

189

(2)

Tip 90 Creating a List of Names

191

(2)

Tip 91 Using Dynamic Names

193

(3)

Tip 92 Creating Worksheet-Level Names

196

(2)

Tip 93 Working with Pre-1900 Dates

198

(2)

Tip 94 Working with Negative Time Values

200

(5)

Part V Useful Formula Examples

Tip 95 Calculating Holidays

205

(3)

New Year's Day

205

(1)

Martin Luther King Jr. Day

205

(1)

Presidents' Day

205

(1)

Easter

206

(1)

Memorial Day

206

(1)

Independence Day

206

(1)

Labor Day

206

(1)

Columbus Day

207

(1)

Veterans Day

207

(1)

Thanksgiving Day

207

(1)

Christmas Day

207

(1)

Tip 96 Calculating a Weighted Average

208

(1)

Tip 97 Calculating a Person's Age

209

(2)

Method 1

209

(1)

Method 2

209

(1)

Method 3

209

(2)

Tip 98 Ranking Values

211

(2)

Tip 99 Converting Inches to Feet and Inches

213

(1)

Tip 100 Using the Datedif Function

214

(2)

Tip 101 Counting Characters in a Cell

216

(2)

Counting all characters in a cell

216

(1)

Counting specific characters in a cell

216

(1)

Counting the occurrences of a substring in a cell

216

(2)

Tip 102 Numbering Weeks

218

(2)

Tip 103 Using a Pivot Table Instead of Formulas

220

(4)

Inserting subtotals

220

(2)

Using formulas

222

(1)

Using a pivot table

222

(2)

Tip 104 Expressing a Number as an Ordinal

224

(2)

Tip 105 Extracting Words from a String

226

(2)

Extracting the first word of a string

226

(1)

Extracting the last word of a string

226

(1)

Extracting all except the first word of a string

227

(1)

Tip 106 Parsing Names

228

(2)

Tip 107 Removing Titles from Names

230

(1)

Tip 108 Generating a Series of Dates

231

(2)

Using AutoFill

231

(1)

Using formulas

231

(2)

Tip 109 Determining Specific Dates

233

(3)

Determining the day of the year

233

(1)

Determining the day of the week

233

(1)

Determining the date of the most recent Sunday

234

(1)

Determining the first day of the week after a date

234

(1)

Determining the nth occurrence of a day of the week in a month

234

(1)

Determining the last day of a month

235

(1)

Determining a date's quarter

235

(1)

Tip 110 Displaying a Calendar in a Range

236

(1)

Tip 111 Various Methods of Rounding Numbers

237

(3)

Rounding to the nearest multiple

237

(1)

Rounding currency values

238

(1)

Using the INT and TRUNC functions

238

(1)

Rounding to n significant digits

239

(1)

Tip 112 Rounding Time Values

240

(1)

Tip 113 Using the New Aggregate Function

241

(3)

Tip 114 Returning the Last Nonblank Cell in a Column or Row

244

(2)

Tip 115 Using the Countif Function

246

(1)

Tip 116 Counting Cells That Meet Multiple Criteria

247

(4)

Using "And" criteria

247

(2)

Using "Or" criteria

249

(1)

Combining "And" and "Or" criteria

250

(1)

Tip 117 Counting Nonduplicated Entries in a Range

251

(1)

Tip 118 Calculating Single-Criterion Conditional Sums

252

(2)

Summing only negative values

252

(1)

Summing values based on a different range

253

(1)

Summing values based on a text comparison

253

(1)

Summing values based on a date comparison

253

(1)

Tip 119 Calculating Multiple-Criterion Conditional Sums

254

(2)

Using "And" criteria

254

(1)

Using "Or" criteria

255

(1)

Using "And" and "Or" criteria

255

(1)

Tip 120 Looking Up an Exact Value

256

(2)

Tip 121 Performing a Two-Way Lookup

258

(2)

Using a formula

258

(1)

Using implicit intersection

259

(1)

Tip 122 Performing a Two-Column Lookup

260

(2)

Tip 123 Performing a Lookup by Using an Array

262

(2)

Using a lookup table

262

(1)

Using an array

262

(2)

Tip 124 Using the Indirect Function

264

(3)

Specifying rows indirectly

264

(1)

Specifying worksheet names indirectly

265

(1)

Making a cell reference unchangeable

265

(2)

Tip 125 Creating Megaformulas

267

(6)

Part VI Conversions and Mathematical Calculations

Tip 126 Converting Between Measurement Systems

273

(2)

Tip 127 Converting Temperatures

275

(1)

Tip 128 Solving Simultaneous Equations

276

(2)

Tip 129 Solving Recursive Equations

278

(2)

Tip 130 Generating Random Numbers

280

(2)

Using the Rand function

280

(1)

Using the Randbetween function

280

(1)

Using the Analysis Toolpak add-in

281

(1)

Tip 131 Calculating Roots

282

(1)

Tip 132 Calculating a Remainder

283

(4)

Part VII Charts and Graphics

Tip 133 Creating a Text Chart Directly in a Range

287

(3)

Tip 134 Selecting Elements in a Chart

290

(3)

Selecting with the mouse

290

(1)

Selecting with the keyboard

291

(1)

Selecting with the Chart Element control

291

(2)

Tip 135 Creating a Self-Expanding Chart

293

(1)

Tip 136 Creating Combination Charts

294

(3)

Tip 137 Creating a Gantt Chart

297

(2)

Tip 138 Creating a Gauge Chart

299

(2)

Tip 139 Using Pictures in Charts

301

(2)

Tip 140 Plotting Mathematical Functions

303

(3)

Plotting single-variable mathematical functions

303

(1)

Plotting two-variable mathematical functions

304

(2)

Tip 141 Using High-Low Lines in a Chart

306

(1)

Tip 142 Linking Chart Text to Cells

307

(1)

Tip 143 Creating a Chart Template

308

(1)

Tip 144 Saving a Chart as a Graphics File

309

(2)

Method 1 Paste the chart into a graphics program

309

(1)

Method 2 Save as an HTML file

309

(1)

Method 3 Use a VBA macro

310

(1)

Tip 145 Saving a Range as a Graphic Image

311

(1)

Tip 146 Making Charts the Same Size

312

(2)

Tip 147 Resetting All Chart Formatting

314

(2)

Tip 148 Freezing a Chart

316

(2)

Converting a chart into a picture

316

(1)

Converting range references into arrays

316

(2)

Tip 149 Creating Picture Effects with a Chart

318

(1)

Tip 150 Creating Sparkline Graphics

319

(2)

Tip 151 Selecting Objects on a Worksheet

321

(2)

Ctrl+click

321

(1)

The Selection and Visibility pane

321

(1)

The Go to Special dialog box

321

(1)

The Select Object tool

322

(1)

Tip 152 Making a Greeting Card

323

(2)

Tip 153 Enhancing Text Formatting in Shapes

325

(2)

Tip 154 Using Images as Line Chart Markers

327

(2)

Tip 155 Changing the Shape of a Cell Comment

329

(1)

Tip 156 Adding an Image to a Cell Comment

330

(1)

Tip 157 Enhancing Images

331

(4)

Part VIII Data Analysis and Lists

Tip 158 Using the Table Feature

335

(3)

Understanding what a table is

335

(1)

Range versus table

335

(1)

Creating a table

336

(2)

Tip 159 Working with Tables

338

(3)

Navigating in a table

338

(1)

Selecting parts of a table

338

(1)

Adding new rows or columns

338

(1)

Deleting rows or columns

339

(1)

Moving a table

339

(1)

Sorting and filtering a table

339

(2)

Tip 160 Using Formulas with a Table

341

(4)

Working with the total row

341

(1)

Using formulas within a table

342

(2)

Referencing data in a table

344

(1)

Tip 161 Numbering Rows in a Table

345

(2)

Tip 162 Using Custom Views with Filtering

347

(2)

Tip 163 Putting Advanced Filter Results on a Different Sheet

349

(1)

Tip 164 Comparing Two Ranges by Using Conditional Formatting

350

(3)

Tip 165 Randomizing a List

353

(2)

Tip 166 Filling the Gaps in a Report

355

(2)

Tip 167 Creating a List from a Summary Table

357

(3)

Tip 168 Finding Duplicates by Using Conditional Formatting

360

(2)

Tip 169 Creating a Quick Frequency Tabulation

362

(3)

Tip 170 Controlling References to Cells within a Pivot Table

365

(1)

Tip 171 Grouping Items by Date in a Pivot Table

366

(3)

Tip 172 Unlinking a Pivot Table from Its Source

369

(2)

Tip 173 Using Pivot Table Slicers

371

(6)

Part IX Working with Files

Tip 174 Understanding the New Excel File Formats

377

(2)

Recognizing the new Excel file formats

377

(1)

The Office compatibility pack

377

(1)

Saving a file for use with an older version of Excel

378

(1)

Tip 175 Importing a Text File into a Worksheet Range

379

(2)

Tip 176 Getting Data from a Web Page

381

(4)

Pasting static information

381

(1)

Pasting refreshable information

381

(2)

Opening the Web page directly

383

(2)

Tip 177 Displaying a Workbook's Full Path

385

(2)

Go backstage

385

(1)

Use a formula

385

(1)

Display the Document panel

386

(1)

Use a macro

386

(1)

Tip 178 Using Document Properties

387

(2)

Tip 179 Inspecting a Workbook

389

(2)

Tip 180 Finding the Missing No to All Button When Closing Files

391

(1)

Tip 181 Getting a List of Filenames

392

(2)

Tip 182 Using Workspace Files

394

(3)

Part X Printing

Tip 183 Controlling What Gets Printed

397

(2)

Displaying the Quick Print button

397

(1)

Adjusting common page setup settings

397

(2)

Tip 184 Displaying Repeated Rows or Columns on a Printout

399

(1)

Tip 185 Printing Noncontiguous Ranges on a Single Page

400

(3)

Breaking out the Camera tool

400

(1)

Shooting with the Camera

401

(2)

Tip 186 Preventing Objects from Printing

403

(1)

Tip 187 Page-Numbering Tips

404

(2)

Applying basic page numbering

404

(1)

Changing the starting page number

404

(2)

Tip 188 Adding and Removing Page Breaks

406

(1)

Forcing a page break to appear where you want it

406

(1)

Removing page breaks you've added

406

(1)

Tip 189 Saving to a PDF File

407

(1)

Tip 190 Making Your Printout Fit on One Page

408

(2)

Tip 191 Printing the Contents of a Cell in a Header or Footer

410

(2)

Tip 192 Copying Page Setup Settings Across Sheets

412

(1)

Tip 193 Printing Cell Comments

413

(1)

Tip 194 Printing a Giant Banner

414

(5)

Part XI Spotting, Fixing, and Preventing Errors

Tip 195 Using the Excel Error-Checking Features

419

(2)

Tip 196 Identifying Formula Cells

421

(3)

Using Go to Special

421

(1)

Using conditional formatting

421

(3)

Tip 197 Dealing with Floating-Point Number Problems

424

(2)

Tip 198 Removing Excess Spaces

426

(2)

Tip 199 Viewing Names Graphically

428

(1)

Tip 200 Locating Phantom Links

429

(1)

Tip 201 Understanding Displayed versus Actual Values

430

(1)

Tip 202 Tracing Cell Relationships

431

(4)

Identifying precedents

431

(1)

Identifying dependents

432

(3)

Part XII Basic VBA and Macros

Tip 203 Learning about Macros and VBA

435

(2)

What is a macro?

435

(1)

What can a macro do?

435

(2)

Tip 204 Recording a Macro

437

(2)

Creating the macro

437

(1)

Examining the macro

437

(1)

Testing the macro

438

(1)

Tip 205 Executing Macros

439

(3)

Use the Macro dialog box

439

(1)

Use the Visual Basic Editor window

439

(1)

Use a shortcut key

439

(1)

Assign the macro to a button

439

(1)

Assign the macro to a shape

440

(1)

Add a button to your Quick Access toolbar

440

(1)

Add a button to the Ribbon

440

(2)

Tip 206 Understanding Functions Versus Subs

442

(2)

VBA Sub procedures

442

(1)

VBA functions

442

(2)

Tip 207 Creating Simple Worksheet Functions

444

(3)

Returning the user name

444

(1)

Determine whether a cell contains a formula

444

(1)

Returning a worksheet name

445

(1)

Returning a workbook name

445

(1)

Reversing a string

445

(1)

Extracting the nth element from a string

445

(2)

Tip 208 Describing Function Arguments

447

(2)

Tip 209 Making Excel Talk

449

(1)

Tip 210 Understanding Custom Function Limitations

450

(1)

Tip 211 Executing a Ribbon Command with a Macro

451

(2)

Tip 212 Understanding Security Issues Related to Macros

453

(2)

Tip 213 Using a Personal Macro Workbook

455

(2)

Index

457

John Walkenbach, arguably the foremost authority on Excel, has written hundreds of articles and created the award-winning Power Utility Pak. His 50-plus books include Excel 2010 Power Programming with VBA, Excel 2010 Formulas, and the Excel 2010 Bible, all published by Wiley. Visit his popular Spreadsheet Page at spreadsheetpage.com.

Püsilink: https://www.kriso.ee/db/9781118257647_pe.html

Märksõnad:

Electronic spreadsheets

E-raamat: John Walkenbach's Favorite Excel 2010 Tips and Tricks [Wiley Online]

Konto & seaded

Otsing

Otsingu andmebaas

Filtreeri tulemusi

Teemad Kirjastuste teemad

Vali ostukorv