Excel 2019 Advanced Topics · Leverage More Powerful Tools to Enhance Your Productivity (Excel 2019 Mastery) by George, Nathan -- Read -- Imperial Library of Trantor

Index

Contents Title Copyright Introduction

Who Is This Book For? How to Use This Book Assumptions Practice Files Improvements in Excel 2019

Chapter 1: Working with Multiple Workbooks

Managing Multiple Windows Moving Data Between Workbooks Moving Worksheets Between Workbooks

Chapter 2: Transform Data with Data Tools

Remove Duplicates Delete Blank Rows Convert Text to Columns Data Consolidation

Chapter 3: Using External Data

Importing Data from Microsoft Access Importing Text files Using the Convert Text to Columns Wizard Importing Data from a Website Other Database Sources

Chapter 4: Troubleshoot and Fix Formula Errors

Trace Precedents and Dependents Evaluate a Formula Using the Watch Window

Chapter 5: Create Advanced Formulas with Functions

Advanced IF Functions

IFS Function SUMIF Function SUMIFS Function COUNTIF Function COUNTIFS Function AVERAGEIF Function AVERAGEIFS Function MAXIFS, MINIFS Functions IFERROR Function

Math Functions

RAND function RANDBETWEEN Function ROUND, ROUNDUP and ROUNDOWN

Manipulate Text with Functions

FIND Function LEFT, RIGHT Functions MID Function PROPER Function

Financial Functions

Definitions PV Function FV Function NPV Function PMT Function Installing the Analysis ToolPak

Chapter 6: Use Macros to Automate Excel Tasks

How to Start the Macro Recorder Recording a Macro Running a Macro Add a Macro Button to the Ribbon Assign a Macro to a Button on the Quick Access Toolbar Assign a Macro to a Graphic Object Macro Security

Trust Center Macro Settings Trusted Locations

Editing Recorded Macros

Editing the Macro in the Visual Basic Editor

Chapter 7: Analyze Alternative Data Sets with What-If Analysis

Data Tables Scenario Manager Goal Seeking The Solver Add-in

Chapter 8: Analyze Data Dynamically with PivotTables and PivotCharts

Preparing Your Data Create a PivotTable with the Quick Analysis Tool Create a Recommended PivotTable Creating a PivotTable Manually Present Data with PivotCharts

Filtering a PivotChart Moving the PivotChart Generate a Pivot Table and a PivotChat Simultaneously

Chapter 9: Protect Workbooks, Worksheets, and Ranges

Password-Protect Your Workbook Set Different Access Levels Protect the Workbook Structure Protect Worksheets Protect Specific Ranges

Afterword: Next Steps Appendix: Keyboard Shortcuts (Excel for Windows)

Access Keys for Ribbon Tabs

Glossary Leave a Review Other Books by Author