What’s Included
1 project file
11 chapter quizzes
Access on tablet and phone
Certificate of completion
Course Description
Advanced Excel Functions: Covers VLOOKUP, INDEX-MATCH, SUMIFS, PivotTables.Data Visualization: Includes sparklines, trendlines, and custom chart formatting.Automation: Explores macros, VBA, Power Query, and Power Pivot.Financial Modeling: Provides training in modeling, forecasting, and valuation. Advanced Data Analysis: Covers regression, correlation, and statistical functions.
Programming
C
Computer Programming
What You Learn
Advanced Excel Functions: Covers VLOOKUP, INDEX-MATCH, SUMIFS, PivotTables.
Learn the basics of Java programming language, including syntax, data types, variables, operators, and control flow statements.
Learn the basics of Java programming language, including syntax, data types, variables, operators, and control flow statements.
Learn the basics of Java programming language, including syntax, data types, variables, operators, and control flow statements.
Curriculum
Module 1: Mathematical & Statistical Functions
- Â SUM
- Â MAX
- Â MIN
- Â POWER
- Â SQRT
Module 2: Advanced Mathematical Functions
- Â SUMIF
- Â SUMIFS
- Â DSUM
- Â SUMPRODUCT
- Â LARGE
- Â SMALL
Module 3: Statistical Functions
- Â COUNT
- Â COUNTA
- Â COUNTBLANK
- Â COUNTIF
- Â COUNTIFS
Module 4: Text Manipulation Functions
- Â DGET
- Â DMAX
- Â DMIN
Module 5: Text Manipulation Functions
- Â PREFIX
- Â SUFFIX
- Â LEN
- Â SUBSTITUTE
- Â REPLACE
Module 6: Attendance Register
- Â Using Functions For Attendance Register
- Â Application Of Functions In Managing An Attendance Register.
Module 7: Text Functions And & Functions
- Â UPPER
- Â LOWER
- Â PROPER
- Â Text Extraction Functions
- Â LEFT
- Â RIGHT
- Â MID
- Â Combining and Concatenating Text
- Â COMMIT Operation
- Â & Functions (e.g., using “&” to join text)
- Â Data Transformation
- Â Flash Fill (Data cleaning and transformation)
- Â Text to Columns (Splitting text into columns)
Module 8: Logical Functions And Salary Sheet with Logics
- Â Basic Logical Function
- Â Combining Logical Tests
- Â IF AND
- Â IF OR
- Â Nested Logical Functions (1 hour)
- Â Nested IF
- Â Nested IF AND
- Â Nested IF AND IF OR
- Â Creating a Salary Sheet with Logics (1 hour)
Module 9: Financial Functions
- Â Basic Financial Calculations
- Â PMT (Payment function)
- Â IPMT (Interest payment function)
- Â PPMT (Principal payment function)
- Â AMOUNT PAYABLE (Total amount payable)
- Â Time Value of Money
- Â PV (Present Value)
- Â NPER (Number of periods)
- Â RATE (Interest rate)
Module 10: Data Validations And Invoice Creations
- Â Data Validation with Indirect Functions
- Â Exploring data validation with indirect functions for dynamic validations.
- Â No Duplicates Allowed
- Â Setting up data validation rules to prevent duplicate entries.
- Â Logical Functions
- Â IS EVEN
- Â IS ODD
- Â Using logical functions within data validation.
- Â Invoice Creation with GST and Data Validation Features
- Â Creating an invoice template with data validation features.
- Â Incorporating GST calculations and validations into invoices.
- Â Best practices for destructor implementation.
Module 11: IS Functions And Error Handling
- Â Testing for Blank Cells
- Â ISBLANK
- Â Identifying and handling blank cells.
- Â Error Checking Functions
- Â ISERROR
- Â ISERR
- Â ISNA
- Â Understanding and addressing various error types.
- Â Testing for Even and Odd Values
- Â ISEVEN
- Â ISODD
- Â Using IS functions to determine even and odd values.
- Â Checking for Formulas and Data Types
- Â ISFORMULA
- Â ISTEXT
- Â ISNUMBER
- Â ISLOGICAL
- Â ISNONTEXT
Module 12: Conditional Formattings And Advanced Formatting
- Â Advanced Conditional Formatting with Formulas
- Â Using formulas to create custom conditional formatting rules.
- Â Brief Overview of Conditional Formattings with Data Validations
- Â Highlighting the combination of data validations and conditional formatting for data control.
- Â Brief Introduction to Using a Search Box
- Â Explaining the concept of a search box in Excel.
- Â Demonstrating Implementation of a Search Box in Conditional Formattings
- Â Basic Filter Operations (15 minutes)
- Â Using standard filters for data manipulation.
- Â Advanced Filtering Techniques (20 minutes)
- Â Demonstrating advanced filtering methods to analyze data.
- Â Combining Filters with Macros (15 minutes)
- Â Integrating basic macros with filtering to automate tasks.
- Â Advanced Filtering (20 minutes).
- Â In-depth coverage of advanced filtering, including criteria and unique scenarios.
Module 13: VLOOKUP
- Â VLOOKUP Function.
- Â Normal VLOOKUP.
- Â VLOOKUP + MATCH Function.
- Â VLOOKUP + ROW Function.
- Â VLOOKUP + COLUMN Function
- Â VLOOKUP with Arrays and Indirect Functions
- Â VLOOKUP + ARRAY Function
- Â VLOOKUP + INDIRECT Function
- Â VLOOKUP + COLUMN Function
- Â VLOOKUP with Aggregations and Logical Functions
- Â VLOOKUP + Aggregations
- Â VLOOKUP + Logical Function
- Â VLOOKUP + IF Error Handling
Module 14: HLOOKUP
- Â HLOOKUP Function
- Â Introduction to HLOOKUP
- Â HLOOKUP + Array Function
- Â Other Lookup Functions.
- Â Lookup Formulas
- Â VLOOKUP with TRUE Functions
Module 15: LOOKUP
- Â Other Lookup Functions.
- Â Lookup Formulas
- Â VLOOKUP with TRUE Functions
Module 16: INDEX + MATCH
- Â Finding Data in a Table.
- Â Using INDEX + MATCH to retrieve specific data from a table.
- Â Example 2: Dynamic Data Retrieval
- Â Example 2: Demonstrating how INDEX + MATCH can be used to dynamically fetch data based on changing criteria.
- Â Example 3: Cross-Reference Data
- Â Showing how to cross-reference data from multiple tables using INDEX + MATCH.
- Â Example 4: Advanced Lookup Scenarios
- Â Addressing more complex lookup scenarios with INDEX + MATCH.
Module 17: Date Functions And Dynamic Calendar
- Â Weekday Function
- Â Understanding the WEEKDAY function for identifying the day of the week.
- Â Date and EOMONTH Functions
- Â Using the DATE function for date calculations.
- Â Applying the EOMONTH function to find the end of the month.
- Â TODAY and NOW Functions
- Â Using the TODAY function to display the current date.
- Â Utilizing the NOW function to show the current date and time.
- Â Dynamic Calendar Preparation
- Â Creating a fully dynamic calendar with date functions, including Weekday, Date, EOMONTH, TODAY, and NOW.
- Â Applying conditional formatting to highlight specific dates or events.
Module 18: Advanced Pivot Chart & Tables
- Â Advanced Pivot Chart & Tables
- Â Building Pivot Tables
- Â Creating pivot tables from raw data.
- Â Organizing and customizing pivot tables.
- Â Calculations in Pivot Tables.
- Â Adding calculated fields and items in pivot tables.
- Â Using functions and formulas for custom calculations..
- Â Advanced Pivot Chart Visualization.
- Â Creating advanced pivot charts.
- Â Customizing chart styles, labels, and colors.
- Â Visualizations in Pivot Charts
- Â Exploring different visualization options.
- Â Applying advanced charting techniques.
- Â Generating Reports.
- Â Using pivot tables and charts to generate insightful reports.
- Â Organizing and formatting reports for effective communication.
Module 19: Excel Tips And Tricks
- Â Keyboard Shortcuts
- Â Essential keyboard shortcuts for efficiency.
- Â Data Entry and Editing Tricks
- Â Time-saving techniques for data entry and editing.
- Â Calculations in Pivot Tables.
- Â Formatting and Styling Tips
- Â Quick formatting and styling tips to make your documents more appealing.
- Â Formula and Function Tricks
- Â Tips for working with formulas and functions effectively.
- Â Data Analysis Hacks
- Â Techniques for better data analysis and visualization.
- Â Customization and Productivity
- Â Customizing Excel settings and boosting your productivity.
