The Microsoft Excel Advance workshop provides advance understanding of the Excel environment and the ability to guide others in the proper use of features in Excel. It will enable students to demonstrate the use of advanced and specialized features within the application
You Will Learn How To
The course objectives and learning outcomes by the end of the course will enable participants to be able to create, manage, and distribute professional spreadsheets for a variety of specialized purposes and situations, and they should know how to customize their Excel environment to meet needs and enhance productivity. Workbook examples include custom business templates, multiple axis financial charts, amortization tables, and inventory schedules.
Importance of this Course
The student will be able to learn to create, manage and integrate different excel skills that will be used for variety of specialized purposed and situations that will enhance the productivity in work or other field.
Course Outline
‘- Referencing, copying, updating, and converting formulas
– Using the logical functions and creating compound logic tests
– Searching for and matching data based on specific criteria
– Reconfiguring cell data using text functions
– Analyzing mathematical and financial data
– Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
– Working with rounding functions
– Returning cell references
‘- Referencing, copying, updating, and converting formulas
– Using the logical functions and creating compound logic tests
– Searching for and matching data based on specific criteria
– Reconfiguring cell data using text functions
– Analyzing mathematical and financial data
– Using the power functions, COUNTIFS, SUMIFS, and AVERAGEIFS
– Working with rounding functions
– Returning cell references
Module 2 : The Basics of Data for Analytics
‘- Aggregating data in Excel
– Statistical Functions
– Data Relation Analysis
– Regression Analysis
– Analyzing data in Time
– Trends/Regression line
– Comparing Population
– Difference between mean of Two Population
– Test Hypothesis Concerning the Variance of Two Population
– Forecasting
Module 3 : Charts In Depth
‘- Selecting the right chart type
– Choosing data to display as a chart
– Creating charts fast with the Quick Analysis tool
– Choosing a chart layout
– Changing the location of the source data
– Dealing with empty and hidden cells
– Moving and resizing charts
– Adding data labels
– Analyzing existing and future data with trend lines
– Working with column, bar, line, pie, and area chart
– Working with specialized chart types: Gantt, Pareto, and Frequency charts
– Creating dynamic charts
Module 4 : Pivot Tables in Depth
‘- Creating a PivotTable
– Summarizing multiple data fields
– Managing subtotals and grand totals
– Grouping PivotTable fields
– Filtering with selections, rules, slicers, and search filters
– Applying PivotTable styles
– Formatting cells
– Creating Pivot Charts
– Enabling PowerPivot
– Building charts and maps
Module 5 : Up and Running What-If Analysis
‘- Defining data tables
– Finding a target value with Goal Seek
– Defining constraints and running Solver
– Creating a configurable model
– Manipulating your model
Running a Monte Carlo simulation
Module 6 : Power BI Features in Depth
Searching for online data with Power Query
– Shaping data in the Query Editor
– Understanding data analysis and business intelligence
– Connecting to data sources
– Modeling data with Power Pivot
– Enhancing PivotTables and Pivot Charts with PowerPoint
– Visualizing geospatial data with Power Map
– Creating and formatting Power View reports
– Sharing your data using Power BI for Office 365
Module 7 : Excel Data-Mining Fundamentals
‘- Solving business problems with data mining
– Exploring Excel’s data-mining algorithms
– Data mining with Excel SQL Server Analysis Services
Module 8 : Data Visualization for Data Analysts
‘- Why visual communications matter, and how they work
– Communicating via story
– Communicating with color
– Using legends and sources
– Sketching and wire framing
– Rethinking slides, charts, and diagrams
– Rethinking your templates and brand guidelines
– Foundations of Business Analytics
Module 9 : Using Office Shapes and SmartArt to Enhance Business Documents
‘- Inserting and altering shapes
– Resizing and aligning shapes
– Saving and printing documents with shapes
– Adding fills, patterns, shadows, and other effects to
shapes
– Inserting text in artwork
– Applying 3D effects
– Working with SmartArt
– Animating SmartArt
– Converting SmartArt