Online Class: Advanced Excel 2013

Dive deep into Excel's advanced functionalities, including data validation, custom formatting, and powerful analytics tools like Goal Seek and Solver. Tailored for those with basic Excel knowledge, this course empowers users to manipulate and visualize data effectively.

COURSE CLOSED

We're sorry, this course is no longer open for enrollment.
 
  • 19
    Lessons
  • 39
    Exams &
    Assignments
  • 14
    Hours
    average time
  • 1.4
    CEUs
 
 
 

Course Description

This advanced Excel course goes beyond the basics of Excel and explores the advanced features that make this the most popular spreadsheet program available today. Picking up where our Excel 2013 online course leaves off, this course delves into Excel's world of functions and formulas, teaching you how to use Excel to enter, manage, calculate and display any type of data.  

You will learn how to:

·     Use outlining in Excel

·     Create templates

·     Work with sparklines

·     Create and manage scenarios

·     Use Goal Seek and Solver

·     Create, manage, and format pivot tables and pivot charts

·     Use the Excel mathematical functions

·     Perform data lookups

·     Create and use IF statements

·     Use Excel's data functions

·     Create and use data validation rules

·     Apply custom and prebuilt conditional formatting

·     Work with functions to manipulate strings of text and data

·     Create and use macros

·     Troubleshoot and audit formulas

·     And more

If you're not a regular Excel user, it's important that you take the time to brush up on your basic Excel skills before signing up.   This course makes the assumption that all students have at least a basic working knowledge of Excel before beginning the first lesson.  You do not need access to Excel 2013 to successfully complete this course; however, it is highly recommended.
  • Completely Online
  • Self-Paced
  • Printable Lessons
  • Full HD Video  
  • 6 Months to Complete
  • 24/7 Availability
  • Start Anytime
  • PC & Mac Compatible
  • Android & iOS Friendly
  • Accredited CEUs
Universal Class is an IACET Accredited Provider
 
 

Course Lessons

Lesson 1. Streamlining Data with Excel Outlining

Efficient data organization in Excel involves outlining, which can be created either automatically or manually, depending on the presence of subtotals. Custom views enhance this by allowing users to save and quickly apply various display settings, optimizing workflow. Additional lesson topics: Microsoft Excel 1PC/1User [Download]; Microsoft Office Home and Student 1PC/1User [Download]; Excel Templates 10 Total Points
  • Lesson 1 Video
  • Lesson discussions: Reasons for Taking this Course
  • Complete: Assignment 1
  • Assessment: Exam 1

Lesson 2. Sparklines: A Visual Data Story within Cells

Sparklines in Excel transform cell data into visual summaries, offering insights into patterns like employee sales. Master creating, modifying styles, addressing data omissions, grouping, comparing, and easily removing sparklines in your worksheets. Additional lesson topics: 11 Total Points
  • Lesson 2 Video
  • Complete: Assignment 2
  • Assessment: Exam 2

Lesson 3. Creating, Managing, and Leveraging Scenarios in Excel

Through Excel's Scenario Manager, create and assess multiple hypothetical scenarios—such as enlarging sales territories—without modifying original data, ensuring clarity in decision-making. This lesson teaches scenario creation, adjustments, and comparative summaries for enhanced analytical insights. Additional lesson topics: How to Use the Scenario Manager in Excel ; Excel Tutorial Using Scenario Manager Microsoft Training Video 11 Total Points
  • Lesson 3 Video
  • Complete: Assignment 3
  • Assessment: Exam 3

Lesson 4. Mastering Goal Seek and Solver Tools

Through Goal Seek, one can easily alter a singular cell in an Excel spreadsheet to meet a predefined target, albeit with simplicity. Conversely, Solver empowers users to handle multiple variables, employing diverse solving methods and constraints to reach more sophisticated, optimal solutions. Additional lesson topics: How to use the Goal Seek function in Excel; Define and solve a problem by using Solver 10 Total Points
  • Lesson 4 Video
  • Complete: Assignment 4
  • Assessment: Exam 4

Lesson 5. PivotTable Tactics for Efficient Data Analysis

Pivot tables unlock the potential of large data sets in Excel by providing functions like totals, averages, and sorting. This lesson equips users with skills to manage data through hands-on creation, filtering, and organization techniques, including suggestions for efficient table setup and maintenance. Additional lesson topics: Working with Pivot Tables in Excel ; Working with Pivot Tables in Excel 11 Total Points
  • Lesson 5 Video
  • Complete: Assignment 5
  • Assessment: Exam 5

Lesson 6. Mastering the Art of PivotTables: Going Beyond Basics in Excel

PivotTables empower Excel users by offering detailed data analysis and customization options to manage large data sets efficiently. The lesson highlights techniques like cell adjustment, grouping, timeline usage, and SQL server integration to enhance data insights. Additional lesson topics: Create a PivotChart 11 Total Points
  • Lesson 6 Video
  • Complete: Assignment 6
  • Assessment: Exam 6

Lesson 7. Creating and Managing PivotCharts in Excel

The lesson demonstrates transforming worksheet data into PivotCharts, offering a visual edge over traditional PivotTables. Key skills covered include creating, customizing, filtering, and managing PivotCharts for effective data presentation. Additional lesson topics: Excel Tutorial; Excel Tutorial 11 Total Points
  • Lesson 7 Video
  • Complete: Assignment 7
  • Assessment: Exam 7

Lesson 8. Navigating Excel's Mathematical Landscape

Excel 2013 offers essential mathematical functions that help users create formulas and analyze data, demonstrating the use of SUM and COUNT functions for tallying sales data over a period. The lesson also covers finding averages and extrema in datasets using AVERAGE, MIN, and MAX functions, along with practical steps for automating these calculations in spreadsheets. 11 Total Points
  • Lesson 8 Video
  • Complete: Assignment 8
  • Assessment: Exam 8

Lesson 9. Effortless Time and Loan Calculations

This lesson teaches how to manage time data in Excel using decimals, along with optimal formatting techniques for time calculations. Further, financial computations such as loan payments, interest rates, and investments are meticulously explained using functions like PMT, RATE, and PV. Additional lesson topics: LOOKUP function; How to use the LOOKUP Function in Excel 11 Total Points
  • Lesson 9 Video
  • Complete: Assignment 9
  • Assessment: Exam 9

Lesson 10. The Art of Fetching Data in Excel

Using a dialogue box when entering formulas in Excel provides guided steps to help users—especially beginners—reduce trial and error in creating functions. Additional lesson topics: Vlookup examples in Excel ; How to create relational databases in Excel 11 Total Points
  • Lesson 10 Video
  • Complete: Assignment 10
  • Assessment: Exam 10

Lesson 11. Using IF Functions to Manage Data Errors

Learning to harness the power of the IF function in Excel equips users to tailor data displays by setting specific conditions, with options to nest functions for added complexity. This lesson touches on related functions like IFNA and IFERROR, and logical constructs enabling complex, criteria-based calculations. Additional lesson topics: IF function; Excel IF Statement: How to Use Excel IF Function 11 Total Points
  • Lesson 11 Video
  • Complete: Assignment 11
  • Assessment: Exam 11

Lesson 12. Error Management and Lookup Functions in Excel

Excel's data functions, such as MATCH and INDEX, simplify data search by allowing precise location of information in extensive lists, eliminating tedious scrolling through rows. This lesson covers MATCH, INDEX, CHOOSE functions, and handling #REF errors, showing how wildcard and combined functions enhance data retrieval. Additional lesson topics: Excel Tutorial: An In-Depth Guide to Working with your Data, Building Formulas, and Using Functions 11 Total Points
  • Lesson 12 Video
  • Complete: Assignment 12
  • Assessment: Exam 12

Lesson 13. Mastering IS Functions: Error Checking in Excel

Learning IS, IFERROR, and OFFSET functions allows for error-handling and crafting adaptable spreadsheets that grow with your data. OFFSET's utility shines in dynamic ranges, while INDIRECT resolves complex cell referencing issues smoothly. Additional lesson topics: 10 new Excel functions that can save you time; OFFSET function 11 Total Points
  • Lesson 13 Video
  • Complete: Assignment 13
  • Assessment: Exam 13

Lesson 14. Mastering Data Validation in Excel: A Comprehensive Guide

Excel's data validation feature guards against erroneous data entry by defining allowable data types and facilitating error messages. Practical guidance in this lesson includes rule creation, invalid data identification, and managing validation dropdowns and message customization in worksheets. Additional lesson topics: Apply data validation to cells; Highline Excel Class Video 28: Excel Data Validation: List, Date, Time, Custom 22 Examples 11 Total Points
  • Lesson 14 Video
  • Complete: Assignment 14
  • Assessment: Exam 14

Lesson 15. Text Functions in Excel: A Comprehensive Study

Master Excel's capabilities beyond numbers by leveraging text functions such as LEFT, RIGHT, and TRIM to efficiently manipulate and clean your data. Harness the power of CONCATENATE and TEXT functions to seamlessly integrate and format text and numbers. 11 Total Points
  • Lesson 15 Video
  • Review Practice Worksheet: CHAR_functions.xlsx
  • Complete: Assignment 15
  • Assessment: Exam 15

Lesson 16. Bringing Data to Life: Mastering Conditional Formatting in Excel

Through conditional formatting, Excel allows for dynamic data visualization using colors and icons to better identify key insights and data patterns. This lesson provides hands-on guidance on applying top/bottom rules, leveraging formatting presets, and creating custom rules to optimize data presentation. Additional lesson topics: Excel formulas for conditional formatting based on another cell value; Highline Excel Class Video 40: Conditional Formatting Basic to Advanced Several Examples 10 Total Points
  • Lesson 16 Video
  • Complete: Assignment 16
  • Assessment: Exam 16

Lesson 17. Mastering Macros: Automation in Excel 2013

Excel macros enable users to efficiently execute multiple commands using keyboard shortcuts, saving significant time. The lesson details the steps to create, manage, and secure these macros, ensuring seamless data operations. Additional lesson topics: Create or delete a macro; Enable or disable macros in Office files 11 Total Points
  • Lesson 17 Video
  • Complete: Assignment 17
  • Assessment: Exam 17

Lesson 18. Macro Integration

Demystify macro editing with basic Visual Basic instructions to safely modify existing macros, adding functionality like confirmation boxes to avoid accidental data loss. Empower your worksheet automation with a curated selection of personalized macros readily available across all workbooks. Additional lesson topics: Highline Excel Class Video 51: Recorded Macro Basics, including Absolute and Relative References 11 Total Points
  • Lesson 18 Video
  • Complete: Assignment 18
  • Assessment: Exam 18

Lesson 19. Quick Tips for Excel 2013 Error Checking

Excel 2013 facilitates efficient troubleshooting through features like tracer arrows to trace relationships between cells, Error Checking for inconsistency resolutions, and the Watch Window functionality for real-time monitoring of data changes across multiple sheets. These tools aim to streamline error detection and correction, optimizing workflow productivity. 104 Total Points
  • Lesson 19 Video
  • Lesson discussions: How would you rate this course?; Course Comments; Program Evaluation Follow-up Survey (End of Course); Reasons for Taking this Course
  • Complete: Assignment 19
  • Assessment: Exam 19
  • Assessment: Final Exam
299
Total Course Points
 

Learning Outcomes

By successfully completing this course, students will be able to:
  • Identify outlines and templates.
  • Demonstrate working with sparklines.
  • Create scenarios.
  • Use goal seek and solver.
  • Demonstrate pivot table usage.
  • Demonstrate PivotCharts usage.
  • Demonstrate usage of mathematical functions.
  • Demonstrate data lookups.
  • Demonstrate the IF function usage.
  • Demonstrate data functions usage
  • Demonstrate data validation.
  • Demonstrate manipulating text.
  • Demonstrate conditional formatting usage
  • Demonstrate creating and using Macros
  • More Fun with Macros
  • Demonstrate troubleshooting and auditing techniques for Excel 2013.
  • Demonstrate mastery of lesson content at levels of 70% or higher.