Muutke küpsiste eelistusi

101 Excel 2013 Tips, Tricks and Timesavers [Pehme köide]

3.97/5 (55 hinnangut Goodreads-ist)
(J-Walk and Associates, Inc., San Diego, CA)
  • Formaat: Paperback / softback, 320 pages, kõrgus x laius x paksus: 231x185x20 mm, kaal: 454 g
  • Ilmumisaeg: 16-Jul-2013
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 111864218X
  • ISBN-13: 9781118642184
Teised raamatud teemal:
  • Pehme köide
  • Hind: 34,68 €*
  • * hind on lõplik, st. muud allahindlused enam ei rakendu
  • Tavahind: 40,80 €
  • Säästad 15%
  • Raamatu kohalejõudmiseks kirjastusest kulub orienteeruvalt 3-4 nädalat
  • Kogus:
  • Lisa ostukorvi
  • Tasuta tarne
  • Tellimisaeg 2-4 nädalat
  • Lisa soovinimekirja
101 Excel 2013 Tips, Tricks and TimesaversSuurem pilt
  • Formaat: Paperback / softback, 320 pages, kõrgus x laius x paksus: 231x185x20 mm, kaal: 454 g
  • Ilmumisaeg: 16-Jul-2013
  • Kirjastus: John Wiley & Sons Inc
  • ISBN-10: 111864218X
  • ISBN-13: 9781118642184
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9781118642184.html
Märksõnad:
Filled with exceptional advice from Mr. Spreadsheet himself, this resource will save Excel users time and help them avoid common spreadsheet pitfalls by sharing tips and tricks for dealing with function arguments, creating add-ins, using UserForms, working with dynamic chart data and more. Original.

Presents a collection of shortcuts and workarounds for Microsoft Excel 2013, covering such topics as formatting, formulas, working with data, tables and pivot tables, and charts and graphs.

Get the most out of Excel 2013 with this exceptional advice from Mr. Spreadsheet himself!

Excel 2013 is excellent, but there's lots to learn to truly excel at Excel! In this latest addition to his popular Mr. Spreadsheet's Bookshelf series, John Walkenbach, aka "Mr. Spreadsheet," shares new and exciting ways to accomplish and master all of your spreadsheet tasks. From taming the Ribbon bar to testing and tables, creating custom functions, and overcoming "impossible" charts, nixing nesting limits, and more, 101 Excel 2013 Tips, Tricks, & Timesavers will save you time and help you avoid common spreadsheet stumbling blocks.

  • Reveals ways to maximize the power of Excel to create robust applications
  • Draws on John Walkenbach's years of experience using Excel and writing more than 50 books
  • Shares tips and tricks for dealing with function arguments, creating add-ins, using UserForms, working with dynamic chart data, and changing data entry orientation
  • Provides shortcuts and helpful techniques for sorting more than three columns, entering fake data for testing purposes, and setting up powerful pivot tables

101 Excel 2013 Tips, Tricks, & Timesavers is packed with information that you need to know in order to confidently and seamlessly master the challenges that come with using Excel!

Introduction 1(6)
What You Should Know
1(1)
What You Should Have
1(1)
Conventions in This Book
2(2)
Formula listings
2(1)
Key names
2(1)
The Ribbon
2(1)
Functions, procedures, and named ranges
3(1)
Mouse conventions
3(1)
What the icons mean
3(1)
How This Book Is Organized
4(1)
How to Use This Book
4(1)
About the Power Utility Pak Offer
4(3)
Part I: Workbooks and Files
Tip 1 Changing the Look of Excel
7(3)
Cosmetic changes
7(1)
Hiding the Ribbon
8(1)
Using options on the View tab
8(1)
Hiding other elements
9(1)
Hiding the status bar
9(1)
Tip 2 Customizing the Quick Access Toolbar
10(4)
About the Quick Access toolbar
10(1)
Adding new commands to the Quick Access toolbar
10(3)
Performing other Quick Access toolbar actions
13(1)
Tip 3 Customizing the Ribbon
14(3)
How to customize the Ribbon
14(3)
Tip 4 Understanding Protected View
17(3)
What causes Protected View?
17(1)
Printing and copying
18(1)
Forcing a file to open in Normal view
18(2)
Tip 5 Understanding AutoRecover
20(2)
Recovering versions of the current workbook
20(1)
Recovering unsaved work
20(2)
Tip 6 Using a Workbook in a Browser
22(2)
Tip 7 Saving to a Read-Only Format
24(3)
Send a printed copy
24(1)
Send an electronic copy in the form of a PDF file
24(1)
Send an MHTML file
25(2)
Tip 8 Generating a List of Filenames
27(2)
Tip 9 Generating a List of Sheet Names
29(3)
Tip 10 Using Document Themes
32(5)
Applying a theme
34(1)
Customizing a theme
35(2)
Tip 11 Understanding Excel Compatibility Issues
37(2)
The Excel 2013 file formats
37(1)
The Office Compatibility Pack
37(1)
Checking compatibility
38(1)
Tip 12 Where to Change Printer Settings
39(4)
Part II: Formatting
Tip 13 Working with Merged Cells
43(5)
Other merge actions
44(1)
Potential problems with merged cells
44(1)
Locating all merged cells
45(1)
Unmerging all merged cells
46(1)
Alternatives to merged cells
47(1)
Tip 14 Indenting Cell Contents
48(2)
Tip 15 Using Named Styles
50(4)
Using the Style gallery
50(1)
Modifying an existing style
51(1)
Creating new styles
52(1)
Merging styles from other workbooks
53(1)
Tip 16 Creating Custom Number Formats
54(4)
Parts of a number format string
55(1)
Custom number format codes
55(3)
Tip 17 Using Custom Number Formats to Scale Values
58(2)
Tip 18 Creating a Bulleted List
60(2)
Using a bullet character
60(1)
Using SmartArt
61(1)
Tip 19 Shading Alternate Rows Using Conditional Formatting
62(3)
Displaying alternate row shading
62(1)
Creating checkerboard shading
63(1)
Shading groups of rows
64(1)
Tip 20 Formatting Individual Characters in a Cell
65(1)
Tip 21 Using the Format Painter
66(2)
Painting basics
66(1)
Format Painter variations
67(1)
Tip 22 Inserting a Watermark
68(2)
Tip 23 Showing Text and a Value in a Cell
70(2)
Using concatenation
70(1)
Using the TEXT function
71(1)
Using a custom number format
71(1)
Tip 24 Avoiding Font Substitution for Small Point Sizes
72(3)
Tip 25 Updating Old Fonts
75(6)
Part III: Formulas
Tip 26 Resizing the Formula Bar
81(2)
Tip 27 Monitoring Formula Cells from Any Location
83(2)
About the Watch Window
83(1)
Customizing the Watch Window
84(1)
Navigating with the Watch Window
84(1)
Tip 28 Learning Some AutoSum Tricks
85(2)
Tip 29 Knowing When to Use Absolute and Mixed References
87(3)
Using absolute references
87(1)
Using mixed references
88(2)
Tip 30 Avoiding Error Displays in Formulas
90(2)
Using the IFERROR function
90(1)
Using the ISERROR function
91(1)
Tip 31 Creating Worksheet-Level Names
92(2)
Tip 32 Using Named Constants
94(2)
Tip 33 Sending Personalized E-Mail from Excel
96(3)
About the HYPERLINK function
96(1)
A practical example using HYPERLINK
97(2)
Tip 34 Looking Up an Eicact Value
99(2)
Tip 35 Performing a Two-Way Lookup
101(2)
Using a formula
101(1)
Using implicit intersection
102(1)
Tip 36 Performing a Two-Column Lookup
103(2)
Tip 37 Calculating Holidays
105(3)
New Year's Day
105(1)
Martin Luther King Jr. Day
105(1)
Presidents' Day
106(1)
Easter
106(1)
Memorial Day
106(1)
Independence Day
106(1)
Labor Day
107(1)
Columbus Day
107(1)
Veterans Day
107(1)
Thanksgiving Day
107(1)
Christmas Day
107(1)
Tip 38 Calculating a Person's Age
108(2)
Method 1
108(1)
Method 2
108(1)
Method 3
108(2)
Tip 39 Working with Pre-1900 Dates
110(4)
Use three columns
110(1)
Use custom functions
111(2)
Use a different product
113(1)
Tip 40 Displaying a Live Calendar in a Range
114(2)
Tip 41 Returning the Last Nonblank Cell in a Column or Row
116(2)
Cell counting method
116(1)
Array formula method
117(1)
Standard formula method
117(1)
Tip 42 Various Methods of Rounding Numbers
118(3)
Rounding to the nearest multiple
118(1)
Rounding currency values
119(1)
Using the INT and TRUNC functions
119(1)
Rounding to n significant digits
120(1)
Tip 43 Converting Between Measurement Systems
121(2)
Tip 44 Counting Nonduplicated Entries in a Range
123(2)
Tip 45 Using the AGGREGATE Function
125(3)
Tip 46 Making an Exact Copy of a Range of Formulas
128(2)
Tip 47 Using the Background Error-Checking Features
130(2)
Tip 48 Using the Inquire Add-In
132(3)
Workbook analysis
132(1)
Diagram tools
133(1)
Compare files
133(1)
Other options
134(1)
Tip 49 Hiding and Locking Your Formulas
135(3)
Hiding and locking formula cells
135(1)
Unlocking nonformula cells
136(1)
Protecting the worksheet
136(2)
Tip 50 Using the INDIRECT Function
138(3)
Specifying rows indirectly
138(1)
Specifying worksheet names indirectly
139(1)
Making a cell reference unchangeable
140(1)
Tip 51 Formula Editing in Dialog Boxes
141(1)
Tip 52 Converting a Vertical Range to a Table
142(5)
Part IV: Working with Data
Tip 53 Selecting Cells Efficiently
147(4)
Selecting a range by using the Shift and arrow keys
147(1)
Selecting the current region
148(1)
Selecting a range by Shift+clicking
148(1)
Selecting noncontiguous ranges
148(1)
Selecting entire rows
149(1)
Selecting entire columns
149(1)
Selecting multisheet ranges
149(2)
Tip 54 Automatically Filling a Range with a Series
151(3)
Tip 55 Fixing Trailing Minus Signs
154(1)
Tip 56 Restricting Cursor Movement to Input Cells
155(2)
Tip 57 Transforming Data with and Without Using Formulas
157(3)
Transforming data without formulas
157(1)
Transforming data by using temporary formulas
158(2)
Tip 58 Creating a Drop-Down List in a Cell
160(2)
Tip 59 Comparing Two Ranges by Using Conditional Formatting
162(3)
Tip 60 Finding Duplicates by Using Conditional Formatting
165(3)
Tip 61 Working with Credit Card Numbers
168(2)
Entering credit card numbers manually
168(1)
Importing credit card numbers
169(1)
Tip 62 Identifying Excess Spaces
170(3)
Tip 63 Transposing a Range
173(3)
Using Paste Special
173(1)
Using the TRANSPOSE function
174(2)
Tip 64 Using Flash Fill to Extract Data
176(3)
Changing the case of text
176(1)
Extracting last names
177(1)
Extracting first names
177(1)
Extracting middle names
178(1)
Extracting domain names from URLs
178(1)
Potential problems
178(1)
Tip 65 Using Flash Fill to Combine Data
179(2)
Tip 66 Inserting Stock Information
181(3)
Hiding irrelevant rows and columns
182(1)
Behind the scenes
182(2)
Tip 67 Getting Data from a Web Page
184(4)
Pasting static information
184(1)
Pasting refreshable information
185(2)
Opening the web page directly
187(1)
Tip 68 Importing a Text File into a Worksheet Range
188(2)
Tip 69 Using the Quick Analysis Feature
190(2)
Tip 70 Filling the Gaps in a Report
192(2)
Tip 71 Performing Inexact Searches
194(2)
Tip 72 Proofing Your Data with Audio
196(2)
Adding speech commands to the Ribbon
196(1)
Using the speech commands
196(2)
Tip 73 Getting Data from a PDF File
198
Using copy and paste
198(2)
Using Word 2013 as an intermediary
200
Part V: Tables and Pivot Tables 1(238)
Tip 74 Understanding Tables
205(3)
Understanding what a table is
205(1)
Range versus table
206(1)
Limitations of using a table
207(1)
Tip 75 Using Formulas with a Table
208(4)
Working with the Total row
208(1)
Using formulas within a table
209(2)
Referencing data in a table
211(1)
Tip 76 Numbering Table Rows Automatically
212(2)
Tip 77 Identifying Data Appropriate for a Pivot Table
214(4)
Tip 78 Using a Pivot Table Instead of Formulas
218(4)
Inserting subtotals
218(2)
Using formulas
220(1)
Using Excel's PivotTable feature
220(2)
Tip 79 Controlling References to Cells Within a Pivot Table
222(2)
Tip 80 Creating a Quick Frequency Tabulation
224(3)
Tip 81 Grouping Items by Date in a Pivot Table
227(3)
Tip 82 Creating Pivot Tables with Multiple Groupings
230(2)
Tip 83 Using Pivot Table Slicers and Timelines
232(7)
Using slicers
232(2)
Using a timeline
234(5)
Part VI: Charts and Graphics
Tip 84 Understanding Recommended Charts
239(2)
Tip 85 Customizing Charts
241(2)
Adding or removing chart elements
241(1)
Modifying a chart style or colors
241(1)
Filtering chart data
242(1)
Tip 86 Making Charts the Same Size
243(2)
Tip 87 Creating a Chart Template
245(2)
Creating a template
245(1)
Using a template
246(1)
Tip 88 Creating a Combination Chart
247(3)
Inserting a preconfigured combination chart
247(1)
Customizing a combination chart
248(2)
Tip 89 Handling Missing Data in a Chart
250(2)
Tip 90 Using High-Low Lines in a Chart
252(1)
Tip 91 Using Multi-Level Category Labels
253(2)
Tip 92 Linking Chart Text to Cells
255(2)
Tip 93 Freezing a Chart
257(3)
Converting a chart into a picture
257(1)
Converting range references into arrays
258(2)
Tip 94 Creating a Chart Directly in a Range
260(4)
Using conditional formatting data bars
260(1)
Using formulas to display repeating characters
261(3)
Tip 95 Creating Minimalistic Charts
264(4)
Simple column charts
264(1)
Simple pie charts
264(1)
Simple line charts
265(1)
A gauge chart
266(2)
Tip 96 Applying Chart Data Labels from a Range
268(2)
Tip 97 Grouping Charts and Other Objects
270(3)
Grouping charts
270(1)
Grouping other objects
271(2)
Tip 98 Taking Pictures of Ranges
273(3)
Creating a static image of a range
273(1)
Creating a live image of a range
274(1)
Saving a range as a graphic image
275(1)
Tip 99 Changing the Look of Cell Comments
276(3)
Setting up your Quick Access toolbar
276(1)
Formatting a comment
276(1)
Changing the shape of a comment
277(1)
Adding an image to a cell comment
278(1)
Tip 100 Enhancing Images
279(2)
Tip 101 Saving Shapes, Charts, and Ranges as Images
281(2)
Index 283
John Walkenbach, arguably the foremost authority on Excel, has written 50+ books, including the bestselling Excel Bible, Excel Formulas, and Excel Power Programming with VBA, as well as more than 300 articles for publications such as PC World, InfoWorld, and Windows. He created the award-winning Power Utility Pak, and provides Excel information and insight at www.spreadsheetpage.com.