Mastering Excel 2019 An All-in-One Tutorial Resource

Description

Learn Excel Fast in Simple and Easy Steps Mastering Excel 2019 is your all-in-one, step by step, tutorial to the latest and most powerful version of Excel from Microsoft 365. This book contains everything you need to know to master the basics of Excel and a selection of advanced topics relevant to real-world productivity tasks you'll encounter in your job, business or home. This guide has been designed to be a resource for you whether you're an Excel beginner, intermediate user, or a power user. You will learn not only how to use specific features, but in what context those features need to be used. Excel Made Easy Without the Jargon This book is concise and to the point, using clear and practical examples that you can adapt to your own needs. The aim is to show you how to perform tasks in Excel as quickly as possible without getting bogged down with the unnecessary filler text you may find in some other Excel books. Three Books in One! Mastering Excel 2019 is made up of Excel 2019 Basics, Excel 2019 Advanced Topics, and Excel 2019 Functions. Now brought together and organized as an all-in-one powerful guide. This book brings you all the powerful material in those books and more. Mastering Excel 2019 also comes with downloadable sample files to enable you to follow the examples in the book hands-on.By reading this book, you will Master all the fundamentals to quickly get up to speed with creating solutions for common Excel tasks. Create basic and advanced formulas for different types of calculations. Use Excel tables to easily manage and manipulate your data. Filter, sort, and apply styles to data lists and Excel tables. Analyze your data visually with charts and sparklines. Use data from external sources like Access databases, CSV text files, and websites. Deploy macros to automate repetitive tasks and increase your efficiency. Create dynamic summaries of your data with pivot tables and pivot charts. Analyze different data scenarios and projections with What-If Analysis tools. Password-protect workbooks, worksheets, ranges, or even specific cells. Create formulas with over 70 of the most useful and powerful Excel functions, including new dynamic array functions like XLOOKUP, XMATCH and FILTER. 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