Advanced Excel: Data Analysis and Visualization Techniques

About Advanced Excel: Data Analysis and Visualization Techniques

This course is designed for professionals who want to master the advanced features and functions of Microsoft Excel. You will learn how to use formulas, functions, charts, pivot tables, macros, data analysis tools, and more to create dynamic and interactive spreadsheets that can automate tasks, enhance productivity, and present data in a clear and compelling way. You will also learn how to collaborate with others using Excel online and share your workbooks securely. By the end of this course, you will be able to: - Apply advanced formulas and functions to perform complex calculations and data analysis - Create and customize charts, graphs, sparklines, and slicers to visualize and summarize data - Use pivot tables and pivot charts to manipulate and analyze large data sets - Record, and run macros to automate repetitive tasks and simplify workflows - Use data analysis tools such as Goal Seek, Solver, Scenario Manager, and Data Tables to solve problems and optimize decisions Section 0- Revision - Functions, Pivot tables, linking sheets, conditional formating, subtotal and data analysis. Section 1- Functions - Ifs, countifs, sumifs, averageifs, index, match and forecast.ets Section 2 - Data Analysis - Scenarios, Goal seek, Data tables, Solver and Forecast Sheets Section 3 - Power Query, Power Pivot and Pivot table calculations Section 4 - Text Functions - Concatenate, search, len, right, mid, iferror and database functions Section 5 - Vlookup - Web links, advanced vlookup features Section 6 - Macros - Record macros, add macros to command buttons Section 7 - Form Controls - How to create form controls without using VBA Section 8 - Data validation - How to create interdependent lists, how to use Indirect Section 9 - Final consolidation exercise