Data Analytics with Advanced Excel
Scholics Data Analytics with Advanced Excel training program offers comprehensive training in leveraging Excel for advanced data analysis. Participants learn to manipulate data, create complex formulas, and visualize insights using advanced features like pivot tables and Power Query. The course enhances proficiency in Excel for efficient data-driven decision-making.
Why this Course is important
This course is important because it equips individuals with essential skills for harnessing the power of data analysis using Excel. In today’s data-driven world, proficiency in Excel’s advanced features is invaluable for making informed decisions, improving efficiency, and unlocking insights crucial for organizational success across various industries.
Skills You Will Learn after the Completion
STATISTICS– The STATISTICS module is one of the most densely packed modules in this course. we provide a deep dive into the underlying data of statistics, as well as how to use different statistical tests depending on the circumstances. LECTURES AND EXERCISES IN THE MODULE INCLUDE ANOVA, T-T, CHI-SQUARED, NORMALITY TESTS, AND REGRESSION ANALYSIS.
DATA VISUALIZATION-When analyzing and presenting data, visualizing it is important, as is explaining and presenting it. LECTURES IN THIS COURSE INCLUDED HOW TO CRAFT AND USE BAR CHARTS, LINE CHARTS, RADIAL CHARTS, HISTOGRAMS, BOX AND WHISKER CHARTS, PIE CHARTS, CONDITIONAL FORMATTING AND SPARKLINES.
PIVOT TABLES- EXCEL’S PIVOT TABLES ARE SO SIMPLE TO USE AND POWERFUL. LECTURES INCLUDE HOW TO STRUCTURE DATA TO GET THE MOST OUT OF PIVOT TABLES, AND THEN A FEW USE CASES INCLUDING ROOT CAUSE ANALYSIS, COMPARATIVE ANALYSIS, AND A FEW VISUALIZATION TOOLS BUILT TO PIVOT TABLES.
EXCEL ADVANCED TOOLS AND DASHBOARDS- Several useful Excel tools do not fit neatly into any of the other modules. The last module of this course explores the applications of these tools. Specifically, a number of archetypal problems are discussed and solved with the MISCELLANEOUS DATA ANALYSIS TOOLS in Excel, including Excel Solver.
Course Content
Introduction to Statistics
Collection of data
Classification and tabulation of data
Types of data: Primary data, Secondary data
Presentation of data
Diagrammatic and Graphical Representation
Histogram, frequency curve, frequency polygon, Ogive curves, stem and leaf chart
Arithmetic Mean, Mode, Median, Partition Values
Quartiles, Deciles and Percentiles, Box Plot, Percentile ranks
Means of transformed data, Variance and Standard Deviation
Basic Math Functions in Excel
MIN, MAX, and AVERAGE Functions
Logical Operators and Functions
Array Functions
Bivariate normal distribution
Types, importance Methods of measuring correlation-scatter diagram
Coefficient of Correlation and Spearman’s rank Correlation.
Regression lines, Difference between regression and correlation, uses of Regression.
Methods of sampling (Description only): Simple random sampling with and without replacement
(SRSWR and SRWOR) stratified random sampling, systematic sampling.
Tests of significance –
z, t, chi-square and F tests
Structuring Data in Excel
Intermediate Excel Functions
Customizing Excel, Paste Formats, Values
Formulas, Comment, Validations
Custom Formatting
Conditional Formatting, Customized Conditional Formatting
SORTING AND FILTERING
Advanced Sorting
Text to column
Flash Fill
Text and List Validation
Custom validations based on formula for a cell
Dynamic Dropdown List Creation using Data Validation
Importing Data from multiple data sources
Hyperlink, Data Consolidation
Goal Seek
Scenario Manager
Statistical Functions
Database Functions
ADVANCED ARRAY FUNCTIONS
Vlookup, HLookup ,Lookup ,XLookup,
Match , Index & Match
Conditional Formatting
Slicers and PivotTables
