Excel 2019 Advanced Topics: Leverage More Powerful Tools to Enhance Your Productivity (Excel 2019 Mastery)

Description

Gain expert skills in Excel 2019 and easily perform complex tasks Whether you have basic Excel skills or you're a power user, Excel 2019 Advanced Topics is full of tips, techniques and tools that will save you time and effort in performing challenging tasks in Excel. When building complicated spreadsheets solutions, using the right tool in Excel can increase the accuracy and efficiency of your work. This book does not only show you how to use specific features but also in what context those features need to be used. Get real-world solutions for your data Excel 2019 Advanced Topics focuses on a selection of topics relevant to real-world productivity tasks you’re likely to perform at home or at work requiring more than a basic knowledge of Excel. Even if you’re an Excel power user, you’ll find lots of ideas and tips in this book that will save your time and effort. Includes tons of examples and readymade formulas that you can copy and use in your own worksheets. The downloadable Excel practice files will also save you typing if you want to practice as you follow the examples detailed in the book. In this book, you'll learn how to: Remove duplicate values or rows from a data list. Delete blank rows in your data list. Consolidate data from different worksheets or workbooks. Import and use data from external sources like Microsoft Access databases, CSV text files, and Internet web pages. Troubleshoot and fix errors in formulas with various debugging tools. Use functions like IFS and SUMIFS to create simpler but more efficient conditional formulas in place of convoluted nested IF functions. Manipulate text with LEFT, RIGHT or MID functions. Carry out financial calculations with functions like PV, FV and NPV. Use Macros to automate Excel tasks. Analyze different data scenarios and create projections with What-If Analysis tools. Dynamically analyze large sets of data with Pivot Tables and Pivot Charts. Protect your workbook, worksheets, or specific cells in your worksheets from unauthorised or accidental changes. 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