Move beyond the basics of spreadsheet management to analyze, organize, and share diverse data types, from personal budgeting to professional financial planning and scheduling. In this intermediate-level course, you will enhance your spreadsheets through strategic use of templates, visual charts, professional graphics, and powerful formulas. This class uses Microsoft Excel 2019 as the primary platform, but is compatible with multiple Excel versions and Office 365 users.
Prerequisite: Completion of Excel Level 1 or equivalent experience with spreadsheet fundamentals
Course overview: You will master the creation of sophisticated formulas while organizing data into functional tables. Discover the analytical power of PivotTables and PivotCharts, and learn how slicers simplify data filtering with just a few clicks.
Course objectives: Upon completion, you will possess a deep understanding of Excel's advanced capabilities. You will confidently create complex formulas, perform data analysis using logical and lookup functions, organize worksheet data with structured tables, create compelling data visualizations through charts, analyze information with PivotTables and PivotCharts, incorporate graphic objects, and improve overall workbook functionality and appearance.
Intended audience: This course is designed for professionals who already possess foundational Excel skills and are prepared to expand their data analysis expertise through advanced Excel 2019 functionality, including formula creation, table management, chart development, PivotTable analysis, and PivotChart visualization.
Detailed course content:
Lesson 1: Working with Functions
- Managing and optimizing ranges
- Using specialized functions for unique calculations
- Applying logical functions for conditional analysis
- Working with date and time functions
- Manipulating text with text functions
Lesson 2: Organizing and Working with Lists
- Sorting data for better organization
- Filtering data to find specific information
- Querying data using database functions
- Creating outlines and applying subtotals
Lesson 3: Analyzing Data
- Creating and modifying data tables
- Applying intermediate conditional formatting techniques
- Using advanced conditional formatting for visual analysis
Lesson 4: Visualizing Data with Charts
- Creating effective charts from your data
- Modifying and formatting charts for maximum impact
- Leveraging advanced chart features
Lesson 5: Implementing PivotTables and PivotCharts
- Creating PivotTables for data summarization
- Analyzing PivotTable data for insights
- Presenting data through PivotCharts
- Filtering data using timelines and slicers for interactive exploration
