Online Class: Advanced Excel 2019
Excel is one of the most powerful tools in Microsoft Office. You can import, query, analyze and manipulate data to create powerful reports that can be used by individuals and enterprise businesses alike. This course explores some of the more advanced features of Microsoft Excel to give you a more powerful experience when you need the most out of spreadsheet software.
- Outlines, Custom Views, and Templates
- Mathematical Functions
- Data Functions and Data Lookups
- The IF Function
- Manipulating Text
- Conditional Formatting
- Importing, Cleaning and Validating Data
- Pivot Tables and Pivot Charts
- The What-If Analysis
- Goal Seek and Solver
- The Analysis ToolPak
- Using Get & Transform to Perform Queries
- And Much More!
Pivot tables are one of the most beneficial tools in Excel, and this course covers pivot tables both basic and advanced features. It then moves on to pivot charts, which are graphs that can be used with pivot table data.
Analysis tools in Excel 2019 are some of the most advanced and useful to users that want to run complex reports. Excel has three What-If analysis tools that can be run on your data and provide reports on goals that give answers to your problems. The Solver and Analysis Toolpak are two add-ins that expand on these tools, and they are also covered in this course.
The final lessons cover data validation, troubleshooting, querying data and macros. These lessons provide ways to work with data and clean up unstructured information. This course fully covers advanced features in Excel so that you can create complex spreadsheets for business or individual use.
Lesson 1: Outlines, Custom Views, and TemplatesMicrosoft includes numerous templates that you can choose from, and many times these templates include formulas and outlines that you can use to build a spreadsheet.
Lesson 2: Mathematical FunctionsMathematical functions are a part of the spreadsheet creation process, but Excel has several that do the calculations for you. You can manually create your own functions or use the pre-created Excel functions.
Lesson 3: Data FunctionsData functions (also called information functions) work with providing feedback about cell data based on the function used and the condition criteria. These functions include error information, numerical information or other information that tells you the type of data contained in a cell.
Lesson 4: Data LookupsExcel has three ways to search for data within a spreadsheet: vertical, horizontal and both. This lesson will focus on these different search methods.
Lesson 5: The IF FunctionThis lesson will focus on how to use the IF Function. An IF statement returns 'true' or 'false' as a result, and you can display data based on this result. Within an IF statement, you can have other functions and formulas to create complex, embedded calculations that display data based on evaluation of several rows and columns in your spreadsheet.
Lesson 6: Manipulating TextExcel has numerous numerical and logical functions, but text functions used to manipulate text are often used. These functions are usually used in combination with formatting functions to automatically and dynamically stylize the way information displays in a cell.
Lesson 7: Exploring Conditional FormattingWith conditional formatting, you can change colors and styles of a cell based on the results of a formula. Formulas return values, and Excel's conditional formatting features will take these results and change a cell's style based on a true or false value.
Lesson 8: Working with SparklinesOne reason to use Excel over other spreadsheet software is for its numerous graphing and visual data representation tools. Sparklines are one graphing tool that allows you to display a visual representation of data within one cell.
Lesson 9: Linking To and Consolidating WorksheetsExcel has functions that let you retrieve and consolidate data across multiple sheets and multiple workbooks. These features let you calculate data in different workbooks and worksheets without the need to copy and paste data to the same location.
Lesson 10: Importing and Cleaning DataThis lesson will focus on how to import and use many forms of data.
Lesson 11: Pivot TablesPivot tables have a bad reputation of being complex and difficult to understand, but recent versions of Excel have made working with pivot tables much easier.
Lesson 12: Continuing to Work with Pivot TablesThis lesson will continue exploring various uses of Pivot Tables
Lesson 13: Pivot ChartsA pivot chart displays data in various ways so that you can review and define the story that your information tells you about your business. A pivot chart complements this data by giving you a visual representation of the information in your pivot table.
Lesson 14: The What-if AnalysesIn business, you must make projections and changes that could increase revenue. Before you make any changes, you should analyze data. A What-If analysis lets you create scenarios where you can ask a question from your data and get results based on the data and the scenario that you input.
Lesson 15: Goal Seek and SolverThe Goal Seek tool in Excel helps you find answers to possible outcomes. You control these outcomes using variables and values until you find a number that matches your goals.
Lesson 16: The Analysis ToolPakThe Solver tool offers ways to find solutions to your problems, but the Analysis Toolpak takes analysis a step further and lets you perform evaluations on complex statistical and engineering problems.
Lesson 17: All About Data ValidationYou can control the type of data entered into a cell by using Excel's data validation tools. These tools will block other formats and reduce the number of mistakes made when you rely on others to enter the right type of data into a record.
Lesson 18: Using Get & Transform to Perform QueriesThe 'Get & Transform' feature imports data from a third-party source and pours the data into a worksheet. This could be a dozen records or thousands of records.
Lesson 19: Troubleshooting and Auditing in Excel 2019Excel has some internal features that help you find dependent cells and troubleshoot issues.
Lesson 20: Exploring Macros in Excel 2019Macros are useful for repetitive steps you perform on every spreadsheet. You can record macros in the main Excel interface or create highly complex automated macros in Microsoft's proprietary Visual Studio for Applications (VBA).
- Demonstrate working with outlines, custom views, and templates in Excel 2019.
- Demonstrate using mathematical functions.
- Demonstrate using data functions, data lookups, and the IF function.
- Demonstrate using conditional formatting.
- Demonstrate working Sparklines.
- Demonstrate linking to and consolidating worksheets.
- Describe importing and cleaning data.
- Demonstrate creating and using Pivot Tables and Pivot Charts.
- Demonstrate creating and using the What-if Analyses.
- Demonstrate creating and using the Goal Seek and Solver.
- Demonstrate creating and using the the Analysis ToolPak.
- Demonstrate creating and using the using get & transform to perform queries.
- Demonstrate mastery of lesson content at levels of 70% or higher.
Additional Course Information
- Document Your Lifelong Learning Achievements
- Earn an Official Certificate Documenting Course Hours and CEUs
- Verify Your Certificate with a Unique Serial Number Online
- View and Share Your Certificate Online or Download/Print as PDF
- Display Your Certificate on Your Resume and Promote Your Achievements Using Social Media
- "i found this course to be very thorough in explanation. The detail and explanation was very easy to follow and look back at." -- Patricia A.
- "Some parts were easier than others but I was able to learn many new things in Excel that I was not aware of previously. I look forward to using these in my job." -- Patti M.
- "This course was well put together and helped me grasp all concepts offered." -- David G.
- "Thank you for going the extra mile. Loved the course!" -- Sean B.
- "I think the course is very helpful. I learned a lot of things I did not know." -- Myrtal W.
- "Very helpful! Thank you." -- Kathleen P.
- "Excellent Course." -- Evelyn E.
- "This course if very helpful for an analyst." -- Brittany H.
- "Great! I learned a lot." -- Deborah H.
- 16 hours 1.6 CEUs Advanced Excel 2019 + More Info
- 4 hours 0.4 CEUs Slack + More Info
- 6 hours 0.6 CEUs Microsoft Publisher 2019 + More Info
- 6 hours 0.6 CEUs Google Docs + More Info
- 6 hours 0.6 CEUs Microsoft Visio 2019 + More Info
- 6 hours 0.6 CEUs Computer Literacy Level 3 - Living and Working Online + More Info
- 5 hours 0.5 CEUs Social Media Marketing: An Introduction + More Info
- 8 hours 0.8 CEUs Microsoft Access 2019 + More Info
- 7 hours 0.7 CEUs Understanding Financial Statements + More Info
- 7 hours 0.7 CEUs PowerPoint 2021 + More Info
- 7 hours 0.7 CEUs Computer Literacy Level 1 - Computer Basics + More Info
- 5 hours 0.5 CEUs SalesForce 101 + More Info
- 5 hours 0.5 CEUs Outlook 2021 + More Info
- 6 hours 0.6 CEUs Computer Literacy Level 2 - Internet Basics + More Info
- 14 hours 1.4 CEUs QuickBooks Online + More Info
- 7 hours 0.7 CEUs Financial Analysis 101: Planning and Control + More Info
- 7 hours 0.7 CEUs Google Slides + More Info
- 8 hours 0.8 CEUs Quicken Tutorial: All Versions + More Info
- 15 hours 1.5 CEUs Python Programming 101 + More Info
- 7 hours 0.7 CEUs Microsoft Word 2021 + More Info
- 8 hours 0.8 CEUs MySQL + More Info
- 8 hours 0.8 CEUs Adobe InDesign 101 + More Info
- 7 hours 0.7 CEUs Google Sheets + More Info
- 6 hours 0.6 CEUs Cybersecurity 101 + More Info
- 32 hours 3.2 CEUs Accounting & Bookkeeping 101 for Everyone + More Info