Muutke küpsiste eelistusi

Microsoft Excel Inside Out (Office 2021 and Microsoft 365) [Pehme köide]

4.33/5 (5 hinnangut Goodreads-ist)
  • Formaat: Paperback / softback, 848 pages, kõrgus x laius x paksus: 232x186x52 mm, kaal: 1540 g
  • Sari: Inside Out
  • Ilmumisaeg: 13-Jan-2022
  • Kirjastus: Addison Wesley
  • ISBN-10: 0137559534
  • ISBN-13: 9780137559534
Teised raamatud teemal:
Microsoft Excel Inside Out (Office 2021 and Microsoft 365)Suurem pilt
  • Formaat: Paperback / softback, 848 pages, kõrgus x laius x paksus: 232x186x52 mm, kaal: 1540 g
  • Sari: Inside Out
  • Ilmumisaeg: 13-Jan-2022
  • Kirjastus: Addison Wesley
  • ISBN-10: 0137559534
  • ISBN-13: 9780137559534
Teised raamatud teemal:
Püsilink: https://www.kriso.ee/db/9780137559534.html
Märksõnad:

Renowned Excel expert Bill Jelen offers a complete tour of Excel in Office 365, from efficient interface customizations to advanced analysis, visualizations, and dashboards.Topics include:

  • Customizing the Excel workspace
  • Best practices for designing and managing worksheets
  • Creating formulas and functions
  • Performing statistical, what-if, and other data analysis
  • Core to advanced charting techniques
  • Using graphics and sparklines
  • Managing databases and tables
  • Automating Excel with macros and custom functions
  • Collaborating in Excel online, in the cloud, and more
  • Extending Excel


Master proven processes for improving development with Scrum and Azure DevOps

This guide can help any development team plan, track, and manage work far more effectively, by combining today's leading agile framework (Scrum) and Microsoft's ALM/DevOps toolset (Azure DevOps). Renowned Scrum expert Richard Hundhausen thoroughly covers team formation, backlogs, Sprints, test plans, collaboration, flow, continuous improvement, Azure Boards, Azure Test Plans, and the real-world tradeoffs associated with DevOps. Throughout, you'll find practical, in-the-trenches tips from experienced Professional Scrum Developers. To make this guide even more valuable, Hundhausen has organized it to complement Scrum.org's popular Professional Scrum Developer (PSD) program, which he created with Scrum.org's Ken Schwaber, author of this book's Foreword.


Professional Scrum Trainer Richard Hundhausen shows how to:
  •  Deepen your understanding of the Scrum framework and Professional Scrum as based on the 2020 Scrum Guide.
  • Provide proven work item planning and tracking, and quickly drive value from Azure Boards
  • Improve your Scrum “pre-game”: the tasks you'll perform before your first Sprint
  • Use Azure DevOps to create and manage backlogs, plan Sprints, and collaborate throughout them
  • Improve at scale with Scaled Professional Scrum and the Nexus scaled Scrum framework
  • Recognize which practices are still most efficiently performed without tools
  • Define and optimize team flow, overcome common dysfunctions, and evolve into a high-performance Professional Scrum Team

About This Book

  • For everyone who works with or relies on Scrum, including developers, designers, architects, testers, business analysts, Product Owners, Scrum Masters, managers, and other stakeholders
  • Focuses primarily on using Scrum for software products, but can support development of adaptive solutions for any complex problem performance Professional Scrum Team

Introduction xxvii
Who this book is for xxvii
Assumptions about you xxvii
How this book is organized xxvii
About the companion content xxvii
Acknowledgments xxviii
Support and feedback xxix
Errata, updates, and book support xxix
Stay in touch xxix
Part I The Excel interface
Chapter 1 What's new in Microsoft 365 Excel
3(1)
Excel opens faster
3(1)
Unhide multiple worksheets
3(1)
Performance improvements
4(1)
Stock data automatic refresh every five minutes
5(1)
Show changes from last 60 days
6(1)
Browse during Save As
7(1)
Searching while opening workbooks
8(1)
Find dialog box shows all options on open
8(1)
Smooth scrolling for tall or wide cells
9(1)
Arrange All in Windows 11
10(1)
Collapsible tasks panes now support pivot tables
10(1)
Accessibility tab in ribbon and the navigation pane
11(1)
New Lambda helper functions
12(2)
LAMBDA functions now support optional arguments
14(1)
The ribbon has rounded edges
14(1)
Cut-out people
14(1)
Image transparency
15(1)
Save any object as picture
16(1)
Write data using the Action Pen
16(1)
New features introduced tomorrow
17(1)
Other new features
18(3)
Chapter 2 Using the Excel interface
21(28)
Using the ribbon
21(6)
Using flyout menus and galleries
22(1)
Rolling through the ribbon tabs
22(1)
Revealing more commands using dialog box launchers, task panes, and "More" commands
22(1)
Using collapsing task panes
23(1)
Resizing Excel changes the ribbon
24(1)
Activating the Developer tab
24(1)
Activating contextual ribbon tabs
25(1)
Finding lost commands on the ribbon
25(1)
Shrinking the ribbon
26(1)
Using the Quick Access Toolbar
27(3)
Adding icons to the QAT
27(1)
Removing commands from the QAT
28(1)
Customizing the QAT
28(1)
Formatting superscripts and subscripts
29(1)
Using the full-screen File menu
30(2)
Pressing the Esc key to close Backstage view
32(1)
Using the new Home screen
32(3)
Recovering unsaved workbooks
32(1)
Clearing the Recent Workbooks list
32(1)
Getting information about the current workbook
33(1)
Marking a workbook as final to prevent editing
33(1)
Finding hidden content using the Document Inspector
34(1)
Avoiding nagging about CSV files
34(1)
Adding whitespace around icons using Touch mode
35(1)
Using the new Sheet icon to add worksheets
35(1)
Navigating through many worksheets using the controls in the lower left
35(1)
Using the mini toolbar to format selected text
36(1)
Expanding the formula bar
36(1)
Zooming in and out on a worksheet
37(1)
Using the status bar to add numbers
38(1)
Switching between Normal view, Page Break preview, and Page Layout view modes
39(1)
Unselecting a cell with Ctrl+click
39(1)
Cleaning data with Flash Fill
40(3)
Coaching Flash Fill with a second example
41(1)
Flash Fill will not automatically fill in numbers
41(1)
Using formatting with dates
41(1)
Troubleshooting Flash Fill
41(2)
Sorting data
43(6)
Sorting by color or icon
43(1)
Factoring case into a sort
44(1)
Reordering columns with a left-to-right sort
44(1)
Sorting into a unique sequence by using custom lists
45(1)
One-click sorting
46(1)
Fixing sort problems
46(3)
Chapter 3 Customizing Excel
49(14)
Performing a simple ribbon modification
49(2)
Adding a new ribbon tab
51(1)
Sharing customizations with others
52(1)
Questions about ribbon customization
53(1)
Using the Excel Options dialog box
53(6)
Getting help with a setting
54(1)
Recent new options in Excel
55(3)
Using AutoRecover options
58(1)
Controlling image sizes
58(1)
Working with protected view for files originating from the Internet
59(1)
Working with Trusted Document settings
59(1)
Options to consider
59(1)
Five Excel oddities
60(3)
Chapter 4 Keyboard shortcuts
63(30)
Using keyboard accelerators
63(5)
Selecting icons on the ribbon
64(1)
Selecting options from a gallery
65(1)
Navigating within drop-down menu lists
66(1)
Backing up one level through a menu
66(1)
Dealing with keyboard accelerator confusion
66(2)
Selecting from legacy dialog boxes
68(1)
Using the shortcut keys
68(9)
Using my favorite shortcut keys
77(2)
Quickly move between worksheets
77(1)
Jumping to the bottom of data with Ctrl+arrow
77(1)
Selecting the current region with Ctrl+*
77(1)
Jumping to the next corner of a selection
78(1)
Pop open the right-click menu using Shift+F10
78(1)
Crossing tasks off your list with Ctrl+5
78(1)
Date stamp or time stamp using Ctrl+; or Ctrl+
78(1)
Repeating the last task with F4
78(1)
Adding dollar signs to a reference with F4
79(1)
Choosing items from a slicer
79(1)
Finding the one thing that takes you too much time
79(1)
Using Excel 2003 keyboard accelerators
79(14)
Invoking an Excel 2003 Alt shortcut
80(1)
Determining which commands work in legacy mode
81(12)
Part II Calculating with Excel
Chapter 5 Understanding formulas
93(22)
Getting the most from this chapter
93(1)
Introduction to formulas
94(1)
Formulas versus values
94(1)
Entering your first formula
95(8)
Building a formula
95(1)
The relative nature of formulas
96(1)
Overriding relative behavior: absolute cell references
96(2)
Using mixed references to combine features of relative and absolute references
98(1)
Using the F4 key to simplify dollar sign entry
99(2)
Using F4 after a formula is entered
101(1)
Using F4 on a rectangular range
101(2)
Three methods of entering formulas
103(3)
Enter formulas using the mouse method
103(1)
Entering formulas using the arrow key method
104(2)
Entering the same formula in many cells
106(2)
Copying a formula by using Ctrl+Enter
106(1)
Copying a formula by dragging the fill handle
107(1)
Double-click the fill handle to copy a formula
107(1)
Use the Table tool to copy a formula
108(2)
Entering one formula and spilling many results
110(5)
Understanding the #SPILL! error
111(1)
Using implicit intersection is more complicated than before
111(1)
Referring to an entire array with the # operator
112(3)
Chapter 6 Controlling formulas
115(18)
Formula operators
115(3)
Order of operations
116(1)
Unary minus example
116(1)
Addition and multiplication example
117(1)
Stacking multiple parentheses
117(1)
Understanding error messages in formulas
118(3)
Using formulas to join text
121(1)
Joining text and a number
121(1)
Copying versus cutting a formula
122(2)
Automatically formatting formula cells
124(1)
Specifying implicit intersection using the @ operator
124(1)
Using date math
125(1)
Troubleshooting formulas
126(7)
Seeing all formulas
126(1)
Highlighting all formula cells
126(1)
Editing a single formula to show direct precedents
126(1)
Using formula auditing arrows
127(1)
Tracing dependents
128(1)
Using the Watch Window
128(1)
Evaluate a formula in slow motion
129(1)
Evaluating part of a formula
130(3)
Chapter 7 Understanding functions
133(14)
Working with functions
133(3)
The Formulas tab in Excel
134(1)
Finding the function you need
135(1)
Using Tab to AutoComplete functions
135(1)
Using the Insert Function dialog box to find functions
136(1)
Getting help with Excel functions
136(4)
Using on-grid ToolTips
137(1)
Using the Function Arguments dialog box
137(2)
Using Excel Help
139(1)
Using AutoSum
140(7)
Potential problems with AutoSum
141(1)
Special tricks with AutoSum
142(2)
Using AutoAverage or AutoCount
144(1)
Function reference chapters
145(2)
Chapter 8 Using everyday functions: math, date and time, and text functions
147(64)
Examples of math functions
147(24)
Using SUM to add numbers
147(2)
Using AGGREGATE to ignore error cells or filtered rows
149(4)
Rounding numbers
153(2)
Using SUBTOTAL instead of SUM with multiple levels of totals
155(1)
Totaling visible cells using SUBTOTAL
156(1)
Using RAND, RANDARRAY, and RANDBETWEEN to generate random numbers and data
156(2)
Using - ROMAN to finish movie credits and - ARABIC to convert back to digits
158(1)
Using ABS to figure out the magnitude of error
159(1)
Using GCD and LCM to perform seventh-grade math
159(1)
Using MOD to find the remainder portion of a division problem
160(2)
Using SQRT and POWER to calculate square roots and exponents
162(1)
Using SUMIFS, AVERAGEIFS, COUNTIFS, MAXIFS, and MINIFS to conditionally calculate
163(2)
Dates and times in Excel
165(3)
Understanding Excel date and time formats
168(3)
Examples of date and time functions
171(16)
Using NOW and TODAY to calculate the current date and time or current date
171(2)
Using YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND to break a date/time apart
173(1)
Using DATE to calculate a date from year, month, and day
173(2)
Using TIME to calculate a time
175(1)
Using DATEVALUE to convert text dates to real dates
176(1)
Using TIMEVALUE to convert text times to real times
177(1)
Using WEEKDAY to group dates by day of the week
178(1)
Using WEEKNUM or ISOWEEKNUM to group dates into weeks
179(1)
Calculating elapsed time
180(3)
Using EOMONTH to calculate the end of the month
183(1)
Using WORKDAY or NETWORKDAYS or their international equivalents to calculate workdays
183(2)
Using international versions of WORKDAY or NETWORKDAYS
185(2)
Examples of text functions
187(24)
Joining text with TEXTJOIN
187(1)
Using LOWER, UPPER, or PROPER to convert text case
188(1)
Using TRIM to remove leading and trailing spaces
189(4)
Using the CHAR or UNICHAR function to generate any character
193(2)
Using the CODE or UNICODE function to learn the character number for any character
195(1)
Using LEFT, MID, or RIGHT to split text
195(3)
Using LEN to find the number of characters in a text cell
198(1)
Using SEARCH or FIND to locate characters in a particular cell
198(4)
Using SUBSTITUTE to replace characters
202(1)
Using REPT to repeat text multiple times
202(2)
Using EXACT to test case
204(1)
Using TEXT to format a number as text
204(3)
Using the T and VALUE functions
207(1)
Introducing TEXTSPLIT and other text manipulation functions
207(4)
Chapter 9 Using powerful functions: logical, lookup, and database functions
211(42)
Examples of logical functions
211(8)
Using the IF function to make a decision
211(2)
Using the AND function to check for two or more conditions
213(1)
Using OR to check whether one or more conditions are met
214(3)
Using the NOT function to simplify the use of AND and OR
217(1)
Using the IFERROR or IFNA function to simplify error checking
217(2)
Examples of information functions
219(4)
Using the ISFORMULA function with conditional formatting to mark formula cells
219(1)
Using IS functions to test for types of values
220(1)
Using the N function to add a comment to a formula
221(1)
Using the NA function to force charts to not plot missing data
221(2)
Using the CELL function to return the worksheet name
223(1)
Examples of lookup and reference functions
223(20)
Using the CHOOSE function for simple lookups
224(1)
Moving from VLOOKUP to XLOOKUP
225(7)
Using FORMULATEXT to document a worksheet
232(2)
Using numbers with OFFSET to describe a range
234(3)
Using INDIRECT to build and evaluate cell references on the fly
237(2)
Using the HYPERLINK function to add hyperlinks quickly
239(1)
Using the TRANSPOSE function to formulaically turn data
240(1)
Using GETPIVOTDATA to retrieve one cell from a pivot table
241(2)
Examples of database functions
243(10)
Using DSUM to conditionally sum records from a database
245(5)
Using the DGET function
250(3)
Chapter 10 Using names, LET, LAMBDA, and Data Types in Excel
253(30)
Using names in Excel
253(7)
Using the Name Box for quick navigation
255(1)
Avoiding problems by using worksheet-level scope
256(1)
Using named ranges to simplify formulas
257(1)
Retroactively applying names to formulas
258(1)
Adding many names at once from existing labels and headings
259(1)
Using a name to avoid an absolute reference
260(1)
Storing intermediate formula results using LET
260(4)
Storing logic in LAMBDA functions
264(5)
Creating a simple LAMBDA function for the hypotenuse of a right triangle
265(2)
Sharing LAMBDA functions with other workbooks
267(2)
Using LAMBDA helper functions
269(6)
Performing calculations for each row or column
269(1)
Performing calculations for each item in an array
270(1)
Performing calculations for each item in an array and returning a single value
271(2)
Performing calculations for each item in an array and returning each intermediate value
273(1)
Making an array using MAKEARRAY
273(2)
Testing for optional arguments using ISOMITTED
275(1)
Using Data Types in Excel
275(8)
Dealing with Data Type formulas that return an array
277(1)
Drilling down through the data card to find an array
278(2)
Using stock or currency Data Types
280(1)
Retrieving historical stock prices using STOCKHISTORY
281(2)
Chapter 11 Connecting worksheets and workbooks
283(14)
Connecting two worksheets
283(1)
Excel in practice: seeing two worksheets of the same workbook side by side
284(2)
Creating links between worksheets
286(5)
Creating links using the Paste Options menu
286(1)
Creating links using the right-drag menu
287(2)
Building a link by using the mouse
289(1)
Links to external workbooks default to absolute references
290(1)
Building a formula by typing
291(1)
Creating links to unsaved workbooks
291(1)
Using the Links tab on the Trust Center
292(1)
Opening workbooks with links to closed workbooks
293(1)
Dealing with missing linked workbooks
293(1)
Updating links when a workbook is renamed or moved
293(4)
Preventing the Update Links dialog box from appearing
294(3)
Chapter 12 Dynamic array formulas and names in Excel
297(24)
Using dynamic array functions to return many results
297(16)
Sorting with a formula
301(4)
Filtering with a formula
305(2)
Using FILTER to select a subset of columns
307(1)
Extracting unique values with a formula
308(2)
Generating a sequence of numbers
310(3)
Generating an array of random numbers with a formula
313(1)
Refer to the entire array
313(1)
Learning about new functions and features
313(1)
Using power formula techniques
313(8)
Using 3D formulas to spear through many worksheets
314(2)
Referring to the previous worksheet
316(5)
Part III Data analysis with Excel
Chapter 13 Transforming data with Power Query
321(18)
Using Power Query
321(9)
Establishing a workflow
322(1)
Loading data using Power Query
322(1)
Loading data from a single Excel workbook
323(1)
Transforming data in Power Query
324(2)
Unpivoting data in Power Query
326(2)
Adding columns in Power Query
328(1)
Reviewing the query
328(1)
Loading and refreshing the data
329(1)
Splitting each delimiter to a new row
330(1)
Appending one worksheet from every workbook in a folder
331(1)
Appending worksheets from one workbook
332(3)
Creating a custom data type in Excel
335(4)
Chapter 14 Summarizing data using subtotals or filter
339(30)
Adding automatic subtotals
339(2)
Working with the subtotals
341(6)
Showing a one-page summary with only the subtotals
342(1)
Sorting the collapsed subtotal view with the largest customers at top
342(2)
Copying only the subtotal rows
344(1)
Formatting the subtotal rows
345(1)
Removing subtotals
346(1)
Subtotaling multiple fields
347(1)
Subtotaling daily dates by month
348(1)
Filtering records
348(12)
Using a filter
349(2)
Selecting one or multiple items from the filter drop-down
351(1)
Identifying which columns have filters applied
352(1)
Combining filters
352(1)
Clearing filters
353(1)
Refreshing filters
353(1)
Resizing the filter drop-down
353(1)
Filtering by selection--hard way
353(1)
Filtering by selection--easy way
354(2)
Filtering by color or icon
356(1)
Handling date filters
357(1)
Using special filters for dates, text, and numbers
358(2)
Totaling filtered results
360(1)
Formatting and copying filtered results
360(1)
Using the Advanced Filter command
360(2)
Excel in practice: using formulas for Advanced Filter criteria
362(2)
Advanced Filter criteria
362(1)
Replacing Advanced Filter with a dynamic array formula
363(1)
Using Remove Duplicates to find unique values
364(2)
Combining duplicates and adding values
366(3)
Chapter 15 Using pivot tables to analyze data
369(24)
Creating your first pivot table
370(6)
Using artificial intelligence for inspiration with pivot tables
371(2)
Starting with a blank pivot table
373(1)
Adding fields to your pivot table using the field list
373(1)
Changing the pivot table report by using the field list
374(2)
Dealing with the compact layout
376(1)
Rearranging a pivot table
377(1)
Finishing touches: numeric formatting and removing blanks
378(2)
Three things you must know when using pivot tables
379(1)
Your pivot table is in manual calculation mode until you click Refresh!
380(1)
If you click outside the pivot table, all the pivot table tools disappear
380(1)
You cannot change, move a part of, or insert cells in a pivot table
380(9)
Calculating and roll-ups with pivot tables
381(1)
Grouping daily dates to months, quarters, and years
381(1)
Adding calculations outside the pivot table
382(1)
Changing the calculation of a field
383(2)
Showing percentage of total using Show Value As settings
385(1)
Showing running totals and rank
386(2)
Using a formula to add a field to a pivot table
388(1)
Formatting a pivot table
389(1)
Setting defaults for future pivot tables
390(2)
Finding more information on pivot tables
392(1)
Chapter 16 Using slicers and filtering a pivot table
393(16)
Filtering using the row label filter
393(6)
Clearing a filter
395(1)
Filtering using the check boxes
395(1)
Filtering using the Label Filters fly-out
396(1)
Filtering using the date filters
397(1)
Filtering to the top 10
398(1)
Filtering using slicers
399(6)
Adding slicers
399(1)
Arranging the slicers
400(1)
Using the slicers in Excel
401(4)
Filtering dates
405(1)
Filtering oddities
405(2)
Autofiltering a pivot table
405(2)
Replicating a pivot table for every customer
407(1)
Sorting a pivot table
407(2)
Chapter 17 Mashing up data with Power Pivot
409(20)
Joining multiple tables using the Data Model
410(4)
Preparing data for use in the Data Model
410(1)
Creating a relationship between two tables in Excel
411(1)
Creating a relationship using Diagram view
412(1)
Building a pivot table from the Data Model
413(1)
Unlocking hidden features with the Data Model
414(10)
Counting distinct in a pivot table
414(2)
Including filtered items in totals
416(2)
Creating Median in a pivot table using DAX Measures
418(3)
Time intelligence using DAX
421(2)
Converting your pivot table to formulas for use on a dashboard
423(1)
Overcoming limitations of the Data Model
424(5)
Enjoying other benefits of Power Pivot
426(1)
Learning more
426(3)
Chapter 18 Using What-lf, Scenario Manager, Goal Seek, and Solver
429(16)
Using What-lf
429(6)
Creating a two-variable What-lf table
431(2)
Modeling a random scenario using a data table
433(2)
Using Scenario Manager
435(3)
Creating a Scenario Summary report
438(1)
Adding multiple scenarios
438(1)
Using Goal Seek
438(2)
Using Solver
440(5)
Installing Solver
441(1)
Solving a model using Solver
441(4)
Chapter 19 Automating repetitive functions using VBA macros
445(40)
Checking security settings before using macros
446(1)
Recording a macro
446(1)
Case study: macro for formatting for a mail merge
447(7)
How not to record a macro: the default state of the macro recorder
449(1)
Relative references in macro recording
450(1)
Starting the macro recorder
451(2)
Running a macro
453(1)
Everyday-use macro example: formatting an invoice register
454(4)
Using the Ctrl+Down-Arrow key to handle a variable number of rows
455(1)
Making sure you find the last record
455(1)
Recording the macro in a blank workbook
455(2)
Editing a macro
457(1)
Understanding VBA code--an analogy
458(5)
Comparing object.method to nouns and verbs
459(1)
Comparing collections to plural nouns
459(1)
Comparing parameters to adverbs
460(2)
Accessing VBA help
462(1)
Comparing adjectives to properties
462(1)
Using the analogy while examining recorded code
463(1)
Using simple variables and object variables
463(4)
Using R1C1-style formulas
464(2)
Entering spillable formulas in macros
466(1)
Fixing Autosum errors in macros
466(1)
Customizing the everyday-use macro example: GetOpenFileName and GetSaveAsFileName
467(1)
From-scratch macro example: loops, flow control, and referring to ranges
468(9)
Finding the last row with data
468(1)
Looping through all rows
469(1)
Referring to ranges
470(1)
Combining a loop with Final Row
471(1)
Making decisions by using flow control
471(2)
Putting together the from-scratch example: testing each record in a loop
473(1)
A special case: deleting some records
474(3)
Combination macro example: creating a report for each customer
477(8)
Using the Advanced Filter for unique records
479(2)
Using AutoFilter
481(1)
Selecting Visible Cells Only
482(1)
Combination macro example: putting it all together
483(2)
Chapter 20 More tips and tricks for Excel
485(14)
Watching the results of a distant cell
485(1)
Calculating a formula in slow motion
486(1)
Inserting a symbol in a cell
487(1)
Editing an equation
487(1)
Protecting a worksheet
488(1)
Repeat the last command with F4
489(1)
Bring the active cell back into view with Ctrl+Backspace
489(1)
Separating text based on a delimiter
489(2)
Auditing worksheets using Inquire
491(1)
Inserting and exploring 3D models
491(2)
Using the inking tools and the Action Pen
493(2)
Seeing Workbooks Statistics and Smart Lookup
495(4)
Part IV Excel visuals
Chapter 21 Formatting worksheets
499(44)
Why format worksheets?
499(2)
Using traditional formatting
501(24)
Changing numeric formats by using the Home tab
503(2)
Changing numeric formats by using built-in formats in the Format Cells dialog box
505(1)
Using numeric formatting with thousands separators
506(1)
Displaying currency
506(1)
Displaying dates and times
507(1)
Displaying fractions
507(1)
Displaying ZIP Codes, telephone numbers, and social security numbers
508(1)
Changing numeric formats using custom formats
509(1)
Using the four zones of a custom number format
510(1)
Controlling text and spacing in a custom number format
511(1)
Controlling decimal places in a custom number format
511(1)
Using conditions and color in a custom number format
512(1)
Using dates and times in a custom number format
512(1)
Displaying scientific notation in custom number formats
513(1)
Aligning cells
514(1)
Changing font size
515(1)
Changing font typeface
515(1)
Applying bold, italic, and underline
516(1)
Using borders
517(1)
Coloring cells
517(2)
Adjusting column widths and row heights
519(1)
Using merge and center
520(3)
Rotating text
523(2)
Formatting with styles
525(2)
Understanding themes
527(4)
Choosing a new theme
528(1)
Creating a new theme
529(1)
Reusing another theme's effects
530(1)
Saving a custom theme
530(1)
Using a theme on a new document
531(1)
Sharing a theme with others
531(1)
Other formatting techniques
531(7)
Formatting individual characters
531(1)
Changing the default font
532(1)
Wrapping text in a cell
533(1)
Justifying text in a range
534(1)
Adding cell notes
535(3)
Copying formats
538(5)
Pasting formats
538(1)
Pasting conditional formats
539(1)
Using the Format Painter
539(1)
Copying formats to a new worksheet
540(3)
Chapter 22 Using data visualizations and conditional formatting
543(30)
Using data bars to create in-cell bar charts
544(5)
Creating data bars
545(1)
Customizing data bars
546(2)
Showing data bars for a subset of cells
548(1)
Using color scales to highlight extremes
549(1)
Using icon sets to segregate data
550(4)
Setting up an icon set
551(1)
Moving numbers closer to icons
552(2)
Mixing icons or hiding icons
554(1)
Using the top/bottom rules
554(2)
Using the highlight cells rules
556(6)
Highlighting cells by using greater than and similar rules
557(2)
Comparing dates by using conditional formatting
559(1)
Identifying duplicate or unique values by using conditional formatting
560(1)
Using conditional formatting for text containing a value
561(1)
Tweaking rules with advanced formatting
562(6)
Using a formula for rules
564(1)
Getting to the formula box
564(1)
Working with the formula box
564(1)
Finding cells within three days of today
565(1)
Finding cells containing data from the past 30 days
565(1)
Highlighting data from specific days of the week
566(1)
Highlighting an entire row
566(1)
Highlighting every other row without using a table
567(1)
Combining rules
568(1)
Extending the reach of conditional formats
569(1)
Special considerations for pivot tables
570(3)
Chapter 23 Graphing data using Excel charts
573(14)
Choosing from recommended charts
573(4)
Using the paintbrush icon for styles
574(1)
Deleting extraneous data using the funnel
575(1)
Changing chart options using the plus icon
576(1)
Easy combo charts
577(1)
Creating a frequency distribution with a histogram chart
577(2)
Showing financial data with a waterfall chart
579(1)
Mapping geographic data with a filled map chart
580(1)
Saving time with charting tricks
580(7)
Adding new data to a chart by pasting
580(3)
Dealing with small pie slices
583(1)
Saving a favorite chart style as a template
584(3)
Chapter 24 Using 3D Maps
587(20)
Examples of 3D Maps
587(7)
Adding color information for categories
589(1)
Zooming in
589(2)
Animating over time
591(1)
Going ultra-local
592(2)
Getting your data into a 3D Map
594(4)
Techniques when using 3D Maps
598(5)
Tipping, rotating, and zooming the map
598(1)
Adding a photo to a point
598(1)
Combining layers
599(1)
Changing column size or color
599(1)
Resizing the various panes
600(1)
Adding a satellite photograph
600(1)
Showing the whole earth
600(1)
Understanding the time choices
601(1)
Controlling map labels
602(1)
Building a tour and creating a video
603(1)
Using an alternate map
603(4)
Preparing the store image
603(1)
Specifying a custom map
604(3)
Chapter 25 Using sparklines
607(16)
Fitting a chart into the size of a cell with sparklines
607(1)
Understanding how Excel maps data to sparklines
608(15)
Creating a group of sparklines
610(2)
Built-in choices for customizing sparklines
612(2)
Controlling axis values for sparklines
614(2)
Setting up Win/Loss sparklines
616(2)
Showing detail by enlarging the sparkline and adding labels
618(2)
Other sparkline options
620(3)
Chapter 26 Formatting spreadsheets for presentation
623(28)
Using SmartArt
624(6)
Elements common in most SmartArt
625(1)
Inserting SmartArt
626(1)
Changing existing SmartArt to a new style
627(1)
Adding images to SmartArt
628(1)
Special considerations for organizational charts and hierarchical SmartArt
629(1)
Using shapes to display cell contents
630(2)
Working with shapes
632(1)
Using WordArt for interesting titles and headlines
632(3)
Using text boxes to flow long text passages
633(2)
Using pictures and clip art
635(2)
Getting your picture into Excel
635(1)
Inserting a picture from your computer
635(1)
Inserting multiple pictures at once
635(1)
Inserting a picture from stock images
636(1)
Adjusting the picture using the ribbon tab
637(8)
Resizing the picture to fit
637(2)
Adjusting the brightness and contrast
639(1)
Adjusting picture transparency so cell values show through
640(1)
Adding interesting effects using the picture styles gallery
640(2)
Applying artistic effects
642(1)
Removing the background
642(3)
Inserting screen clippings
645(1)
Selecting and arranging pictures
645(2)
Inserting icons, stickers, illustrations, and cut-out people
647(1)
Examining 3D models
648(3)
Chapter 27 Printing
651(22)
Printing in one click
651(1)
Finding print settings
652(2)
Previewing the printed report
654(6)
Using the Print Preview on the print panel
654(2)
Using full-screen Print Preview
656(1)
Making the report fit on the page
656(1)
Setting worksheet paper size
657(1)
Adjusting worksheet orientation
657(1)
Adjusting worksheet margins
657(1)
Repeating the headings on each page
658(1)
Excluding part of your worksheet from the print range
659(1)
Forcing more data to fit on a page
660(1)
Working with page breaks
660(2)
Manually adding page breaks
660(1)
Manual versus automatic page breaks
661(1)
Using Page Break Preview to make changes
661(1)
Removing manual page breaks
661(1)
Adding headers or footers to the printed report
662(4)
Adding an automatic header
662(1)
Adding a custom header
663(1)
Inserting a picture or a watermark in a header
663(1)
Using different headers and footers in the same document
664(1)
Scaling headers and footers
665(1)
Printing from the File menu
666(1)
Choosing a printer
666(1)
Choosing what to print
667(1)
Changing printer properties
668(1)
Changing some of the Page Setup settings
668(1)
Using Page Layout view
668(1)
Exploring other page setup options
669(4)
Printing gridlines and headings
669(1)
Centering a small report on a page
669(1)
Replacing error values when printing
670(1)
Printing comments
670(1)
Controlling the first page number
670(3)
Chapter 28 Collaborating in Excel
673(26)
The rise of working remotely in 2020
673(1)
Storing documents in the cloud
674(1)
Sharing documents with others
675(5)
Sharing during the initial save
675(1)
Sharing using the Share button
676(1)
Sharing by mentioning in a modern comment
677(1)
Seeing who has access to the workbook
677(1)
Knowing when someone shares a workbook with you
678(2)
Editing the same workbook at the same time
680(1)
Seeing who else is editing using presence
680(3)
Etiquette for editing at the same time
681(1)
Avoiding the dreaded veto
681(2)
Filtering and sorting with Sheet Views
683(3)
Using threaded comments, mentions, and assigning tasks
686(4)
Tracking who did what in the workbook
690(9)
Seeing who changed what with Show Changes
690(2)
Catching up
692(1)
Rolling back using Version History
692(7)
Part V Appendixes
Appendix A Excel functions
699(56)
Financial functions in Excel
699(5)
Logical functions in Excel
704(1)
Text functions
705(3)
Text functions for double-byte languages
708(1)
Date and time functions
709(2)
Lookup and reference functions
711(3)
Math and trig functions
714(5)
Statistical functions
719(10)
Engineering functions
729(2)
Cube functions
731(1)
Information functions
732(2)
Web functions
734(1)
Database functions
735(1)
Add-in functions
736(1)
Compatibility functions
736(2)
Alphabetical cross-reference
738(17)
Appendix B Excel functions with DAX or Power Query equivalents
755(10)
Excel functions and DAX equivalents
755(5)
Excel functions with Power Query M equivalents
760(5)
Appendix C Adding functionality to Excel with add-ins
765(12)
Understanding three types of add-ins and how to activate
765(3)
Find optimal solutions with Solver
768(1)
Audit workbooks with Inquire
768(1)
Get descriptive statistics with the Analysis ToolPak
769(2)
Find near matches with Fuzzy Lookup
771(1)
Perform science projects with Data Streamer
772(1)
Perform Euro conversions with Euro Currency Tools
772(1)
Generate diagrams from data with Visio Data Visualizer
772(1)
Perform sentiment analysis using Azure Machine Learning
772(1)
Generate a chart with people using People Graph
773(1)
Generate fake data
773(1)
Test brilliant new functions for Excel with Fast Excel
774(3)
Index 777