Learning Path
Excel Expert
Level
Advanced
Enrollment
Closed
Instructor-Led Live Session
Live Session Recording
Expert Support
Case Studies & Projects
Dedicated Resource Portal
05 weeks (Sat & Sun)
01:30 PM to 03:30 PM
Language
Urdu / Hindi
In this course, you will gain a better understanding of complex formulas and functions. You’ll be able to write advanced nested functions to produce solutions and results from your data sets.
Using the built-in functions in Excel, you will find quicker ways to customize formulas. When working with data-heavy spreadsheets, save time by learning more complex Excel Formula and auditing techniques.
Finally, you will be introduced to the most recent Excel 365 functions.
Course Content
You will get
- Exercise and Project files
- Quizzes and Homework assignments
- Pre and Post skill assessment
- 1-month access to the resource portal
- HD Recording of all live sessions
Certificate of Completion
You will be awarded a course completion E-Certificate by TechTAR Solutions after fulfilling the course requirements.
- Excel users who have advanced working knowledge and want to gain a better understanding of more advanced Excel formulas and functions.
- Analysts looking to extract insight from raw data, and develop expert-level formula skills
- Excel users who want to build interactive models or dashboards (forecasts, financial models, scenario tests, etc)
- Anyone seeking to improve productivity and work more efficiently with data
- You are familiar with Microsoft Excel.
- You can write basic formulas and are familiar with the Excel spreadsheet environment.
- Classes will be interactive throughout & LIVE on Zoom. You would be able to ask questions & share views with the Technical Assistant, Trainer & class other participants.
Recommendations:
- Computer with internet access, a webcam, microphone, and Zoom App.
- Microsoft Excel 2016+ or Office 365
Formulas & Functions
- Excel Formula Syntax
- Reference Types – Fixed and Relative Referencing
- Formula Operators
- Order of Calculation
- Common Errors
- Using Defined Names in a Workbook
- Wildcard Character in formulas
- Tips and Shortcuts
Logical & Information Functions
- IF Statement
- Nesting IF Statement VS IFS Function
- AND / OR / NOT Operators
- Fixing Errors with IFERROR
- Information Functions (ISBLANK / ISNUMBER / ISTEXT)
Text Functions
- CONCATENATE (&), TEXTJOIN, CONCAT
- LEN
- TRIM
- CLEAN
- EXACT
- VALUE
- UPPER
- LOWER
- PROPER
- LEFT
- MID
- RIGHT
- CODE
- CHAR
- TEXT
- SEARCH
- FIND
- SUBSTITUTE
- REPLACE
Date Functions
- DATE
- DATEVALUE
- TODAY()
- NOW()
- YEAR
- MONTH
- DAY
- HOUR
- MINUTE
- SECOND
- EOMONTH
- EDATE
- YEARFRAC
- WEEKDAY
- WORKDAY
- NETWORKDAY
- DATEDIF
Math Functions
- SUM
- SUBTOTAL
- ROUND
- ROUNDUP
- ROUNDDOWN
- RAND
- RANDBETWEEN
- SUMIF
- SUMIFS
- SUMPRODUCT
Statistics Functions
- AVERAGE
- MEDIAN
- MODE
- STANDARD DEVIATION
- VARIANCE
- PERCENTILE
- LARGE
- SMALL
- RANK
- PERCENTRANK
- COUNTIF
- COUNTIFS
- AVERAGEIF
- AVERAGEIFS
- MAXIFS
- MINIFS
Database Functions
- DSUM
- DAVERAGE
- DMAX
Legacy Array Formulas
- Array Functions – Intro
- Array Formula -Rules
- Array Constants
- Array Formulas Demo
Lookup / Reference Function
- VLOOKUP
- HLOOKUP
- MATCH
- VLOOKUP & MATCH
- ROW
- ROWS
- COLUMN
- COLUMNS
- INDEX
- INDEX & MATCH
- CHOOSE
- OFFSET
- INDIRECT
- XLOOKUP (EXCEL 365)
Formula Auditing
- Synchronous Scrolling
- Advanced Find and Search
- Inquire Add-in
- Formula Auditing Tools
- Precedents / dependent cells
- Show Formulas
- Errors Checking
- Evaluate Formula
- Watch Window
Formula Based Formatting
- Creating, Editing & Managing Rules
- Highlighting Every Other Row Using MOD
- Formatting Cells Based on the Value of Another Cell
- Formatting Cells Using Statistical Functions
- Formatting Cells Using Text & Logical Operators
-
Conditional Formatting with Dynamic Arrays
Protecting Formulas
- Validating Data Entry
- Viewing Formulas on a worksheet
- Finding Formula Cells
- Protecting the Formulas on a Worksheet
Dynamic Array Formulas (Office 365 Required)
- Legacy vs. Dynamic Excel
- Understanding Excel’s New Calculation Engine: Spill Error and @(at) sign
- Spill Range Properties
- Common Dynamic Array Functions
- SORT Function
- SORTBY Function
- FILTER Function
- UNIQUE Function
- SEQUENCE Function
- RANDARRAY Function
- FREQUENCY Function
- TRANSPOSE Functions
- Declaring Variable with LET
Individual Price
PKR 12,000
Per Person
Total charges for complete training
Group of Two
PKR 10,800
Per Person
Avail 10% off on a Group of two
Group of Three
PKR 10,200
Per Person
Avail 15% off on a Group of three
Group of Four or more
PKR 9,600
Per Person
Avail 20% off on a Group of four or more
Course Reviews
We have engaged TechTAR Solutions for a number of session in Excel and Power BI for our employees. Sohail Merchant is wonderful to deal with when making the bookings and the high level of experience the trainers hold is evident. We will definitely use them again in the future.!
I was tasked with seeking out a company to assist us in recreating our department’s appraisal Matrix and after meeting with TechTAR Solutions’ consultant, I knew they were the right fit to help get the job done! Their expertise and understanding of our needs made the whole process so simple.
Sohail Merchant from TechTAR Solutions’ [ explained the theory very well, also gave us sufficient room to ask questions and clarify our queries. Enough resources were provided to read and increase our knowledge on individual capacity..
Lots of useful information, Ideas & strategies with many practical examples. I learned a lot throughout the course & implementing it in my business. Sohail Merchant from TechTAR Solutions’ expertise gave me an amazing learning experience. Thank you.
The professionalism and Personalized service that we got from TechTAR Solutions was unlike any other company we worked with and they made us feel so comfortable by their natural communication and let’s do it attitude. Incredibly trustworthy and high value! Thankyou!
Please keep developing the products and solutions to make our lives more easier and work to be more focused efficient targeted and productive. Appreciate the contributions of the entire community in this.