دانلود کتاب Mastering Excel 2019 An All-in-One Tutorial Resource

فهرست مطالب :

Contents
Title
Copyright
Introduction
How This Book Is Organized
How to Use This Book
Assumptions
Practice Files
Book 1: Excel Basics
Chapter 1: Getting Started with Excel
1.1 Creating a New Excel Workbook
1.2 The Excel User Interface
1.3 Using AutoSave
1.4 Customising the Ribbon
1.5 Getting Help in Excel
Chapter 2: Entering and Editing Data
2.1 Entering and Editing Data Manually
2.2 Using AutoFill
2.3 Using Flash Fill
Chapter 3: Design and Organize Workbooks
3.1 Adding New Worksheets
3.2 Moving and Copying Worksheets
3.3 Freezing Rows and Columns
3.4 Applying Themes to Your Worksheet
Chapter 4: Organizing Your Data
4.1 Copying, Moving and Deleting Data
4.2 Find and Replace Data
4.3 Sorting Data
4.4 Filtering Data
Applying a Custom Filter
Changing the Sort Order of a Filtered List
Chapter 5: Formatting Cells
5.1 Arrange Cells, Rows and Columns
5.2 Applying Number Formats
5.3 Copy Cell Formatting
5.4 Conditional Formatting
Chapter 6: Performing Calculations with Formulas
6.1 Operators in Excel
6.2 Entering a Formula
6.3 Calculating Percentages
6.4 The AutoSum Tool
6.5 Quick Sum with the Status Bar
6.6 Calculating Date and Time
6.7 Relative and Absolute Cell Reference
6.8 Using Data from Other Worksheets
Chapter 7: Use Drop-down Lists and Validation Rules
7.1 How to Create a Drop-down List
7.2 Creating Data Validation Rules
Chapter 8: Named Ranges
8.1 What is a Named Range?
8.2 Creating a Named Range
8.3 Editing a Named Range
8.4 How to Use a Named Range
Chapter 9: Working with Tables
9.1 Preparing Your Data
9.2 Create an Excel Table
9.3 Choosing a Table Style
9.4 Sorting Data in a Table
9.5 Filtering Table Data
9.6 Adding a Totals Row to Your Table
9.7 Removing Table Attributes
Chapter 10: Creating Charts
10.1 Creating a Chart via the Quick Analysis Tool
10.2 Creating a Chart via the Excel Ribbon
10.3 Customising Charts
Switching the X and Y Axes
Change the Data Source
Adding Axis Titles
Chart Styles
10.4 Creating Sparkline Charts
Chapter 11: Printing Your Worksheet
Page Setup
Setting the Print Area
Preview and Print Your Worksheet
Book 2: Excel Advanced Topics
Chapter 1: Working with Multiple Workbooks
1.1 Managing Multiple Windows
1.2 Moving Data Between Workbooks
1.3 Moving Worksheets Between Workbooks
Chapter 2: Transform Data with Data Tools
2.1 Remove Duplicates
2.2 Delete Blank Rows
2.3 Convert Text to Columns
2.4 Data Consolidation
Chapter 3: Using External Data
3.1 Importing Data from Microsoft Access
3.2 Importing Text files
Using the Convert Text to Columns Wizard
3.3 Importing Data from a Website
3.4 Other Database Sources
Chapter 4: Troubleshoot and Fix Formula Errors
4.1 Trace Precedents and Dependents
4.2 Evaluate a Formula
4.3 Using the Watch Window
Chapter 5: Use Macros to Automate Excel Tasks
5.1 First Things First
The Three Ways to Start the Macro Recorder
5.2 Recording a Macro
5.3 Running a Macro
5.4 Add a Macro Button to the Ribbon
5.5 Assign a Macro to a Button on the Quick Access Toolbar
5.6 Assign a Macro to a Graphic Object
5.7 Macro Security
Trust Center Macro Settings
Trusted Locations
5.8 Editing Recorded Macros
Editing the Macro in the Visual Basic Editor
Chapter 6: Analyze Alternate Data Sets with What-If Analysis
6.1 Data Tables
Creating a One-Variable Data Table
Creating a Two-Variable Data Table
6.2 Scenario Manager
6.3 Goal Seeking
6.4 The Solver Add-in
Solver Results
Solver Options
Saving and Loading Solver Problem Models
Solver Reports
Chapter 7: Analyze Data Dynamically with PivotTables and PivotCharts
7.1 Creating PivotTables
Preparing Your Data
Create a Pivot Table with the Quick Analysis Tool
Create a Recommended PivotTable
Creating a PivotTable Manually
Summarizing Data by Date
Applying Formatting
Filter and Sort a PivotTable
7.2 Create Dynamic Charts with PivotCharts
Filtering a Pivot Chart
Moving the Pivot Chart
Generate a PivotTable and a PivotChart Simultaneously
Chapter 8: Protect Workbooks, Worksheets, and Ranges
8.1 Password-Protect Your Workbook
8.2 Set Different Access Levels
8.3 Protect the Workbook Structure
8.4 Protect Worksheets
8.5 Protect Specific Ranges
Book 3: Excel Functions
Chapter 1: How to Enter a Function
Using the Insert Function Dialog Box
Chapter 2: Lookup and Reference Functions
Find Data with XLOOKUP
Vertical Lookup
Horizontal Lookup
Simultaneous Vertical and Horizontal Lookup
Return Multiple Values with Horizontal Spill
Return Multiple Values with Vertical Spill
Common XLOOKUP Errors and Solutions
Find Data with VLOOKUP
Standard VLOOKUP Example
Finding an Approximate Match with VLOOKUP
Using VLOOKUP and the CHOOSE Function for Left Lookups
VLOOKUP and Error Handling
VLOOKUP and Wildcards
Common VLOOKUP Errors and Solutions
Find Data with HLOOKUP
MATCH Function
CHOOSE Function
TRANSPOSE Function
ADDRESS function
FORMULATEXT Function
COLUMNS Function
ROWS Function
INDEX Function
XMATCH Function
Using INDEX with XMATCH to Find Data
Chapter 3: Logical Functions
IF Function
Nested IF Functions
Advanced IF Functions
IFS Function
SWITCH Function
IFERROR Function
AND Function
OR Function
Chapter 4: Math Functions
SUM Function
SUMIF Function
SUMIFS Function
AGGREGATE Function
MOD Function
RANDBETWEEN Function
ROUND Function
ROUNDUP Function
ROUNDDOWN Function
SQRT Function
Chapter 5: Statistical Functions
COUNT Function
COUNTIF Function
COUNTIFS Function
COUNTA Function
COUNTBLANK Function
AVERAGE Function
AVERAGEIF Function
AVERAGEIFS Function
MAX, MIN, MEDIAN Functions
MAXIFS, MINIFS Functions
Chapter 6: Date and Time Functions
Date Formats
DAY, MONTH, YEAR Functions
DATE Function
DATEDIF Function
DAYS Function
EDATE Function
DATEVALUE Function
NETWORKDAYS Function
NOW Function
TODAY Function
TIME Function
Chapter 7: Text Functions
FIND Function
FINDB
CONCAT Function
TEXTJOIN Function
TRIM Function
UPPER, LOWER Functions
LEN Function
MID Function
PROPER Function
LEFT, RIGHT Functions
LEFTB, RIGHTB Functions
Chapter 8: Financial Functions
Definitions
PV Function
FV Function
NPV Function
PMT Function
SLN Function
SYD Function
DB Function
DDB Function
Afterword: Next Steps
Appendix: Keyboard Shortcuts (Excel for Windows)
Glossary
Other Books by Author
Leave a Review