Course Overview:
The Data Analysis Using Advanced Excel course is designed to equip participants with the skills and techniques required to perform advanced data analysis tasks using Microsoft Excel. Through this course, participants will learn how to harness the power of Excel’s advanced features and functions to manipulate, analyze, and visualize data effectively. From data cleaning and transformation to complex formulas and statistical analysis, this course provides a comprehensive understanding of Excel’s capabilities for data analysis.
Learning Objectives:
- Master advanced Excel functions: Learn how to leverage advanced Excel functions such as VLOOKUP, INDEX-MATCH, SUMIFS, COUNTIFS, and more to efficiently process and manipulate data.
- Perform data cleaning and transformation: Explore techniques to clean and transform raw data into a suitable format for analysis. Learn how to handle missing values, remove duplicates, and reshape data using Excel’s built-in features.
- Analyze data with complex formulas: Understand how to use array formulas, nested functions, and logical operators to perform complex calculations and derive insights from data.
- Apply statistical analysis techniques: Gain proficiency in statistical analysis using Excel, including descriptive statistics, regression analysis, hypothesis testing, and data visualization techniques.
- Create interactive dashboards and visualizations: Learn how to build interactive dashboards and visualizations using Excel’s features like pivot tables, slicers, and conditional formatting. Present data in a visually appealing and easily understandable manner.
Target Audience:
- Business professionals and analysts who work extensively with data and want to enhance their data analysis skills using Excel.
- Data analysts and researchers seeking to strengthen their proficiency in Excel for data manipulation and analysis.
- Students or individuals looking to acquire practical data analysis skills using a widely available tool like Excel.
- Anyone interested in gaining a solid foundation in data analysis using a popular spreadsheet software.
Prerequisites:
- Basic understanding of Excel functionalities such as formulas, functions, and formatting.
- Familiarity with data entry and basic data manipulation in Excel.
- No prior experience in advanced Excel or data analysis is required, although basic knowledge will be helpful.
Unit 1: Basic Principles of Software “Ms-EXCEL”
- Periodic Table with the main EXCEL Shortcuts (239 EXCEL KEYBOARD SHORTCUTS)
- Naming Range / Management (Define Name/Name Manager)
- Data Validation / Multiple Choice Lists (Data Validation / Drop Down Lists)
- Conversion/Transfer of cell content to columns (Text to Columns – Delimited/Fixed Width)
- Data Reorganization and Search Functions
- Data search based on criteria (Filtering)
- Advanced data search under Conditions/Criteria (AND/OR Condition)
- Practical Exercise on all the above topics
- Problem Solving and Question Solving
Unit 2: Data Formatting in Cells Under Conditions/Criteria Using Formulas
- Data formatting under conditions/criteria (Conditional Formatting)
- Advanced Data Formatting (Advanced Conditional Formatting)
- Automatic cell filling (AUTOFILL WITH NUMBERS)
- Display Formula Instead of Value
- Practical Exercise on all the above topics
- Problem Solving and Question Solving
Unit 3: Data Editing in Cells – SPECIAL AUTOFILL, GRAPHICAL REPRESENTATIONS – CHARTS
- Hypothetical Statements/Functions (IF and Nested IF Statements, IFS)
- Graphical Representations – Charts – (Editing/Formatting)
- Automatic cell filling (AUTOFILL WITH NUMBERS)
- Examples with Function Combinations (Using Combination of Formulas)
- Practical Exercise on all the above topics
- Problem Solving and Question Solving
Unit 4: FUNCTIONS – CALCULATIONS – OPERATIONS
- Important Functions/Tools/Operations/Calculations (Functions f(x) – (SUMIF, SUMIFS, COUNTIF, COUNTIFS, XLOOKUP, XMATCH)
- Important Data Search Functions Calculations (Functions f(x))
- Practical Exercise on all the above topics
- Problem Solving and Question Solving
Unit 5: SPECIALIZED FUNCTIONS – STATISTICAL FUNCTIONS
- Standard Deviation
- Variance
- Specialized Functions – Functions f(x):
- DATE FUNCTIONS, INDIRECT, DATEVALUE, IFNA, TEXT
- Using LEFT, MID, RIGHT to correct incorrect date formats
- Practical Exercise on all the above topics
- Problem Solving and Question Solving
Unit 6: PROBLEM SOLVER – STATISTICAL STATEMENTS
- Activation and Utilization of Tools in the “ADD-INS” Section (FILE > OPTIONS > ADD-INS)
- Problem Solver – (SOLVER)
- Moving Average – (Moving Average)
- Descriptive Statistics – (Descriptive Statistics)
- Histograms / Graphical Representations – (HISTOGRAM)
- Practical Application Exercises
- Question Solving and Discussion
Unit 7: GOAL SEEKING – SOLUTION SCENARIOS
- Data Tables with 1 or 2 Variables
- Combination of Data from “PIVOT TABLES”
- Creating Dashboards
- Practical Exercises Covering the Above Topics
- Problem Solving and Question Solving
Feedback
Rafael Pourgourides
“In every course I attended, they all helped and made sure that I was going to get where I needed to go,”. “From the director all the way to the teachers, they all want you to succeed.”
Andreas Petrou
I couldn’t imagine how much attending the Ethical Hacking course at SCP Academy would help me in my career. After that I asked for more and I knew who could help me. Having also completed the Java and WordPress courses I want to say that learning never stops and this is what SCP Academy has taught me.
Marios Zindilis
SCP Academy was the key for me to open new doors in my career, leading me down paths I would never have thought of. Apart from Knowledge I have been taught to value persistence, to be curious and to focus on specific and timely goals, both in career as well as in life.
Marios Kairinos
I attended my first course in 2015 with the dream of becoming an IT professional. I have registered for the MCSA course and since then I have never stop learning. I have completed successfully the Azure and Office 365 courses which helped me find my dream job. Now I can ask for more and I know who can help me take them.
Evagoras Pourgourides
SCP Academy helped me learn more than I could ever imagine. The courses enriched me with knowledge and professional skills that i use in my job
Alexandros Louca
Following my passion for technology was one of the best decisions I ever made. In SCP Academy I have found and attended courses that advanced my career to the next level.
Giota Achilleos
SCP academy was the key for me to step to success. Regarding the knowledge that I gained from the university the academy offered to me more skills to succeed my goals and my dreams. SCP academy has offered to me professional courses that helped me to start my new career in Malta.
Course Features
- Lectures 0
- Quizzes 0
- Duration 60 hours
- Skill level All levels
- Language English
- Students 10
- Certificate No
- Assessments Yes
