The Complete Excel Guide: Beginners to Advanced

The Complete Excel Guide: Beginners to Advanced

by Simon Sez IT
Released March 2021
Publisher(s): Packt Publishing
ISBN: 9781801075763

Watch it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Video description

Everyone knows that the Microsoft Office suite is used by millions worldwide. Unlocking its full potential and learning it thoroughly will improve your productivity drastically. This course will help you grasp all the essential and advanced skills for Microsoft Excel 2019 with the help of interesting activities.

We’ll start with the basic topics and see what’s new in Excel 2019. Then we will create workbooks; enter data into; explore page setup and print options; work with rows, columns, and cells; learn how the Cut, Copy, and Paste option works; understand functions and formulas; and also get introduced to PivotTables, logical, and lookup formulas.

After this, we will move towards advanced concepts such as lookup and advanced lookup functions, financial functions including calculating interest and depreciation, statistical functions, learning how to connect to other workbooks and datasets outside of Excel, and creating awesome visualizations using sparklines and data bars. We’ll also cover some advanced charts such as Surface, Radar, Bubble, and Stock Charts.

By the end of this course, you’ll become proficient in using Microsoft Excel for your professional and personal needs.

What You Will Learn

  • Grasp techniques to back up and recover your Excel spreadsheets
  • Discover the essentials of using formulas and functions in Excel
  • Discover what’s new or different in Excel 2019
  • Learn to effectively use autofill and flashfill
  • Discover how to create stunning charts and graphs in Excel
  • Learn practical methods to sort and filter your data in Excel

Audience

This course is designed for anyone who is interested in mastering Microsoft Excel 2019 and becoming an expert in Microsoft Excel. To get started with this course, you need to have basic computer skills and Microsoft Office installed on your computer.

About The Author

Simon Sez IT: Simon Sez IT has offered technical courses for individuals, small businesses, and Fortune 500 companies since 2008, with thousands of employees who can benefit from the easy-to-learn and hands-on software training. It offers over 8,000 video tutorials on a range of software programs.

Simon Sez IT ensures stress-free eLearning and enhanced employee productivity—whether you implement new software or a technological upgrade in your work environment. With over 600,000 students from 180 countries, Simon Sez IT is the preferred online learning choice for individuals and businesses worldwide.

Table of contents

  1. Chapter 1 : Excel 2019: Introduction
    1. Introduction to Excel
    2. What's New in Excel 2019
    3. Course Overview
    4. Overview of the Excel Window
    5. Mouse Features
    6. Backstage View
  2. Chapter 2 : Excel 2019 Beginner: Creating Workbooks
    1. Entering Text and Numbers
    2. Creating Basic Formulas
    3. Relative References
    4. Order of Operations
    5. Working with Ranges
    6. Module 2 - Practice Exercise
  3. Chapter 3 : Excel 2019 Beginner: Saving Workbooks
    1. Working with the Save and Save As Commands
    2. File Extensions, Share, Export, and Publish Files
    3. Module 3 - Practice Exercise
  4. Chapter 4 : Excel 2019 Beginner: Navigating Workbooks
    1. Opening a File
    2. Working with Larger Files
    3. Freeze Panes Option
    4. Split Screen Option
    5. Module 4 - Practice Exercise
  5. Chapter 5 : Excel 2019 Beginner: Page Setup and Print Options
    1. Headers and Footers
    2. Print Titles
    3. Comments
    4. Page Setup Options
    5. Fit to Print on One Page
    6. Printing Workbooks
    7. Module 5 - Practice Exercise
  6. Chapter 6 : Excel 2019 Beginners: Working with Rows, Columns, and Cells
    1. Adding and Deleting Rows, Columns, and Cells
    2. Changing Column and Row Widths
    3. Module 6 - Practice Exercise
  7. Chapter 7 : Excel 2019 Beginners: Moving Data
    1. Cut, Copy, and Paste
    2. Copying Formulas
    3. Module 7 - Practice Exercise
  8. Chapter 8 : Excel 2019 Beginners: Formulas and Functions
    1. Overview of Formulas
    2. Create Formulas Using Functions: Part 1
    3. Create Formulas Using Functions: Part 2
    4. Absolute Values
    5. Module 8 - Practice Exercise
  9. Chapter 9 : Excel 2019 Beginners: Working with Sheets
    1. Adding, Deleting, and Renaming Sheets
    2. Additional Sheet Tab Options
    3. Dimensional Formulas
    4. Module 9 - Practice Exercise
  10. Chapter 10 : Excel 2019 Beginners: Formatting Worksheets
    1. Formatting Cells: Part 1
    2. Formatting Cells: Part 2
    3. Formatting Numbers
    4. Borders and Shading
    5. Format as Table
    6. Using Styles
    7. Using Format Painter
    8. Protecting Sheets
    9. Fill Handle and Custom Lists
    10. Module 10 - Practice Exercise
  11. Chapter 11 : Excel 2019 Beginners: Charts
    1. Creating Charts
    2. Types of Charts
    3. Editing Charts: Part 1
    4. Editing Charts: Part 2
    5. Using Graphics to Enhance Charts
    6. Linking Chart Titles
    7. Module 11 - Practice Exercise
  12. Chapter 12 : Excel 2019 Beginners: Working with Names
    1. Creating Range Names
    2. Managing Names
    3. Using Names in Formulas
    4. Module 12 - Practice Exercise
  13. Chapter 13 : Excel 2019 Beginners: Sorting and Filtering
    1. Remove Duplicates
    2. Sort Data
    3. Filter Data
    4. Advanced Filters
    5. Module 13 - Practice Exercise
  14. Chapter 14 : Excel 2019 Beginners: Outlining and Subtotaling
    1. Creating an Outline
    2. Subtotals
    3. Module 14 - Practice Exercise
  15. Chapter 15 : Excel 2019 Beginners: Working with Views
    1. Arranging Files
    2. New Window Option
    3. Custom Views
    4. Module 15 - Practice Exercise
  16. Chapter 16 : Excel 2019 Beginners: Linking Files
    1. Creating Formulas Between Files
    2. Working with Links
    3. Consolidating Data
    4. Module 16 - Practice Exercise
  17. Chapter 17 : Excel 2019 Beginners: Advanced Formula Creation
    1. If Statements
    2. VLOOKUPS
    3. Data Validation
    4. Formula Auditing
    5. Module 17 - Practice Exercise
  18. Chapter 18 : Excel 2019 Beginners: PivotTables
    1. PivotTables
    2. Pivot Charts
    3. Module 18 - Practice Exercise
  19. Chapter 19 : Excel 2019 Beginners: Additional Excel Features
    1. Slicers
    2. Goal Seeker
    3. Text to Columns
    4. Watch Window
    5. Customizing Ribbons and Tabs
    6. Module 19 - Practice Exercise
  20. Chapter 20 : Excel 2019 Beginners: Shortcuts
    1. Excel Shortcuts Every User Should Know
  21. Chapter 21 : Excel 2019 Beginners: Closing
    1. Summary
  22. Chapter 22 : Excel 2019 Advanced: Introduction to Advanced Course
    1. Introduction
    2. Course Structure
  23. Chapter 23 : Excel 2019 Advanced: Functions
    1. Functions - Part 1
    2. Functions - Part 2
    3. Autosum
  24. Chapter 24 : Excel 2019 Advanced: Date and Time Functions
    1. How Date and Time Works
    2. Basic Date and Time Functions
    3. Complex Date and Time Functions
  25. Chapter 25 : Excel 2019 Advanced: Text Functions
    1. Using Text Functions - Part 1
    2. Using Text Functions - Part 2
  26. Chapter 26 : Excel 2019 Advanced: Logical Functions
    1. Logical Functions
    2. Exercise 01
  27. Chapter 27 : Excel 2019 Advanced: Lookup Functions
    1. Lookup Functions - Part 1
    2. Lookup Functions - Part 2
    3. Exercise 02
  28. Chapter 28 : Excel 2019 Advanced: Financial Functions
    1. Financial Functions and Terminology
    2. Personal Financial Functions
    3. Principal and Interest Payments
    4. Depreciation
    5. Exercise 03
  29. Chapter 29 : Excel 2019 Advanced: Statistical Functions
    1. Statistical Functions for Description - Part 1
    2. Statistical Functions for Description - Part 2
    3. Statistical Functions for Forecasting - Part 1
    4. Statistical Functions for Forecasting - Part 2
    5. Exercise 04
    6. One Click Forecasting
    7. Statistical Functions for Inference
  30. Chapter 30 : Excel 2019 Advanced: Connecting to External Data
    1. Connecting to Other Workbooks
    2. Connecting to Access Databases
    3. Connecting Web Data Sources
    4. Get and Transform
    5. Exercise 05
  31. Chapter 31 : Excel 2019 Advanced: Tables
    1. Introduction to Tables
    2. Working with Tables
    3. Table References
    4. Table Styles
    5. Exercise 6
  32. Chapter 32 : Excel 2019 Advanced: PivotTables
    1. Introduction to PivotTables
    2. Working with PivotTables
    3. Filters and Slicers
    4. Pivot and Charts
    5. Exercise 07
  33. Chapter 33 : Excel 2019 Advanced: Data Analysis
    1. What If Analysis
    2. Scenario Manager
    3. Goal Seek
    4. Solver
    5. Exercise 08
  34. Chapter 34 : Excel 2019 Advanced: Graphs and Charts
    1. Area Charts
    2. Surface Charts
    3. Radar Charts
    4. Bubble Charts
    5. Sparklines
    6. Stock Charts
    7. Exercise 9
  35. Chapter 35 : Excel 2019 Advanced: Excel Web App
    1. Web App - Part 1
    2. Web App - Part 2
  36. Chapter 36 : Excel 2019 Advanced: Course Conclusion
    1. Summary

Product information

  • Title: The Complete Excel Guide: Beginners to Advanced
  • Author(s): Simon Sez IT
  • Release date: March 2021
  • Publisher(s): Packt Publishing
  • ISBN: 9781801075763