This course will provide learners with a better understanding of the higher functions of Excel. Users will be able to perform the most advanced calculations and functions, manipulate data using pivot tables and 'advanced' analysis, and record macros.
Overview of Course:
Upon successful completion of this course, learners will be able to:
- Getting the Most from Your Data: To begin, participants will learn about Excel's automatic grouping, outlining, and subtotaling features. Participants will also explore the Scenario Manager, data analysis tools, add-ins, one and two data input tables, goal seek, and Solver. The section will conclude with a discussion on hyperlinks.
- Pivoting Data: This section focuses on one of Excel's most powerful tools: PivotTables. Participants will explore all aspects of PivotTables, including creating a PivotTable; using the contextual tabs; pivoting, summarizing, and analyzing data; formatting the PivotTable; and using external data with a PivotTable. This section also includes information on Slicers (a new feature that lets users analyze and interpret pivoted data with ease) and the classic PivotTable frame (used in Excel 2003 and earlier). The session will conclude with a comprehensive introduction to integrating data with PowerPivot and creating a PivotTable from that data.
- Charting Pivoted Data: In this section, participants will learn how to create, use, edit, and format PivotCharts. Participants will also get experience with advanced PivotChart tasks, including creating a PivotChart from PowerPivot data and using PivotCharts to display scenario results.
- Advanced Excel Tasks: The next section of this manual will explore a few Excel features that we find handy for everyday chores. First, advanced functions (PMT, FV, VLOOKUP, and HLOOKUP) and logical functions (IF, IFERROR, AND, OR, NOT, FALSE, and TRUE) will be covered. Then, AutoFill lists (which can save time when entering data) will be discussed. The section will conclude with information on linking, consolidating, and combining Excel data, with a final lesson on using the classic PivotTable and PivotChart wizard to pivot consolidated data.
- Macros, Visual Basic, and Excel Programming: The final section of this manual will explore how to automate Excel tasks with macros. We will cover recording, running, and editing both basic and relative reference macros. Information on assigning a keyboard shortcut to a macro and managing macro security is also included. Then, Visual Basic tasks will be covered, including copying macros, using the Visual Basic Editor, declaring variables, iterating over a range, prompting for user input, and using conditional (If…Then…Else) statements.
Learners should have a good working knowledge and understanding of the concepts covered in the Excel Foundation and Excel Intermediate courses.