ADVANCED DATA ANALYSIS
Duration: 2 Days
Most organizations rely heavily on advanced features of Microsoft Excel 2016 to consolidate, analyze, and report financial information. If you have chosen to take this workshop, you use Excel a lot in your business role. By now, you're familiar with Excel 2016, its functions and formulas, a lot of its features and functionality, and its powerful data analysis tools. You are likely called upon to analyze and report on data frequently, work in collaboration with others to deliver actionable organizational intelligence, and keep and maintain workbooks for all manner of purposes.
We?ll start off with a quick review of what was covered in Introduction to Data Analysis, the prerequisite for this class. As the workshop progresses you?ll see how to work with the additional data analysis tools provided by Excel 2016 add-ins and become skilled in using validation to protect the integrity of your worksheets from less experienced users.
You?ll learn how to add functional and eye-catching custom controls to any worksheet and how to use scenarios and data tables to quickly perform what-if data analyses. You'll discover advanced techniques for PivotTables, like creating Timelines, calculated fields, and calculated items. This training class is designed for students to gain the skills necessary to audit and analyze worksheet data, utilize data tools, collaborate with others, and create and manage macros and use the Visual Basic for Applications (VBA) language.
Attendees will look at working with scenarios, using Solver, the Analysis ToolPak, PivotTable features and developing macros. Other features such as conditional formatting, slicers, the Inquire Add-In, and tracking changes are also covered as well as a lesson on Goal Seek, Solver, PowerPivot and PowerView.
You'll learn how to use Excel's consolidation function to efficiently summarize data from multiple sources, become adept at importing external data, and master the art of conditional formatting to highlight duplicate entries and other common worksheet problems. As you become proficient, you'll be able to accomplish just about anything Microsoft Excel 2016 has to offer!
Most of the time when you run statistics, you want to use statistical software. These tools are built to do calculations like t-tests, chi-square tests, correlations, and so on. Excel is not really meant for data analysis. But that doesn?t mean you can?t do it.
Unfortunately, Excel?s statistical functions aren?t always intuitive. And they usually give you esoteric results. So instead of using stats functions, we?re going to use the Excel statistics add-in: the Data Analysis ToolPak. 2
Whether it is daily life thing or any day at work, we are always comparing to see what?s good and what is not so good. This differential analysis has a popular name, ?ANOVA.? Analysis of Variance (ANOVA) is a statistical method used to test differences between two or more means. We will learn how to use various multiple comparison tests within Excel.
Another statistical technique we will use is Regression Analysis, an important tool for modelling and analyzing data. Multiple regression is an extension of simple linear regression. It is used when we want to predict the value of a variable based on the value of two or more other variables.
We?ll look at the difference between parametric statistics and non-parametric methods and get some experience using various non-parametric tests in Excel. Nonparametric methods are useful when the normality assumption does not hold and your sample size is small.
Topics Covered In This Course
Introduction to Data Analysis: Review
Advanced Functions and Formulas
Lookups and Data Tables
Advanced Data Management
Advanced PivotTables and PivotCharts
Exporting and Importing Data
Working with Scenarios
Using Goal Seek, Solver and VLookup
Macros and Visual Basic
Working with Slicers
Using the Inquire Add-In
Publishing an Excel Workbook
Excel Data Analysis Tools
Regression Analysis: How to Use the Regression Data Analysis Tool in Excel
ANOVA Test: Definition, Types, Examples
Multiple comparison procedures
Who Should Take This Course
Students who have taken this vendor?s Introduction to Data Analysis workshop, or those who have intermediate skills with Microsoft Excel 2016 and who want to learn more advanced statistical and data analysis skills.
Students should have taken an Introduction to Data Analysis workshop and have an intermediate to advanced Excel 2016 skills level, knowledge of pivot tables and other advanced techniques.
Instructor led, group-paced, classroom-delivery learning model with structured hands-on activities.
Every student attending a Verhoef Training class will receive a certificate good for $100 toward their next public class taken within a year.
You can also buy "Verhoef Vouchers" to get a discounted rate for a single student in any of our public or web-based classes. Contact your account manager or our sales office for details.
Can't find the course you want?
Call us at 800.533.3893, or
email us at [email protected]