Data Science & Analysis

Master Excel Like a Pro: From Basics to Advanced Techniques

Hi, I'm Ankit Khatri, Director with over 16+ years of experience in capital markets and technology. I specialize in the valuation of fixed income securities, automation, and data visualization using powerful tools like Excel, SQL, Power BI, Tableau, Jaspersoft, and Python. I am passionate about sharing my knowledge and training both students and professionals to help them gain the skills needed to land their dream jobs.

Are you ready to elevate your Excel skills to the next level? Whether you're a beginner or looking to master advanced Excel functionalities, I’m here to guide you through an in-depth exploration of Excel’s powerful features. Here's what you'll uncover in my comprehensive Excel walkthrough:

Excel Basics

Understanding Excel's use and scope

Columns, rows, cells & ranges fundamentals

Different types of data in Excel

Mastering Autofill & Flash Fill for efficiency

Essential shortcut & hotkeys

Data formatting & alignment techniques

How to remove duplicates effortlessly

Utilizing different views & reviews

Differences between comments & notes

Merge & wrap text functions

Sorting & filtering data like a pro

Splitting text into columns

Advanced Data Sorting, Formatting & Validation

Custom formatting for unique data sets

Applying conditional formatting

Data validation to ensure accuracy.

Functions & Formulas

Enhance your data analysis skills with powerful functions and formulas:

Logical Functions: AND, OR, NOT, IF, IFERROR, IFS, LAMBDA

Text Functions: CONCATENATE, CONCAT, TEXTJOIN, FIND, SEARCH, SUBSTITUTE, LEN, LEFT, RIGHT, MID, LOWER, UPPER, PROPER, TRIM, TEXTSPLIT

Lookup Functions: VLOOKUP, HLOOKUP, XLOOKUP, ROW, OFFSET, MATCH, INDEX, INDEX-MATCH, INDIRECT

Mathematical Functions: SUM, AVG, COUNT, COUNTA, COUNTBLANK, SUBTOTAL, CEILING, FLOOR, ROUND, SUMIF, SUMIFS, SUMPRODUCT, COUNTIF, COUNTIFS, MIN, MAX, LARGE, SMALL, RANK

Information Functions: ISERROR, ISEVEN, ISTEXT, ISODD

Evaluating formulas and error-checking mechanisms

Data Visualization & Reporting

Transform your data into insightful visuals with advanced charting techniques:

Creating and formatting charts: Column, Bar, Pie, Line, Combo, Scatterplot, Funnel, Waterfall

Mastering pivot tables and pivot charts

Imagine becoming so proficient in Excel that you can automate tasks, create dynamic dashboards, and visualize data effortlessly. With my expert guidance, that's exactly what you'll achieve. Start your journey towards Excel mastery today and take control of your data like never before.

Structured Query Language (SQL)

Unlock the power of Structured Query Language (SQL) and transform your data handling skills from basic to advanced with expert guidance, gain mastery over SQL, the language that underpins data management across industries.

Here's what you'll learn:

Introduction to SQL: Grasp the fundamentals and understand databases- DBMS, RDBMS.

Data Types & Entity Relationship Model: Learn about different data types, types of attributes, keys, and relationships.

SQL Commands: Get hands-on with DDL, DCL, and DML.

Constraints & Parsing: Master constraints like Not Null, Unique, Primary Key, Foreign Key, and understand Hard vs. Soft Parse.

SQL Order of Execution & Clauses: Understand the sequence and operations like Where, Order By, Having, Group By & Top.

Operators: Become proficient in Arithmetic, Comparison, Logical, and Compound operators

Functions: Work with Mathematical, Aggregate, Date & Time, String, Conversion, and Analytical functions

Expressions: Utilize Case statements, Coalesce, and Nullif expressions

Joins & Sub Queries: Learn to use Inner, Left, Right, Full & Natural joins, and master sub queries including Ordinary, In-line view, and Correlated sub queries.

Advanced Operators: Understand Union, Union All, Intercept, and Except operators.

Prerequisite: Install the latest SQL Server Management Studio to get started.

Power BI

Imagine being able to unlock invaluable insights from data, streamline your processes, and take your data management capabilities to the next level. Whether you're aspiring to secure your dream job or aiming to boost your professional skill set, this comprehensive course will provide you with the tools and knowledge needed to excel.

With my Power BI service, you will:

Visualize Complex Data: Learn to create stunning visualizations that make data interpretation simpler and more insightful.

Automate Reports: Discover techniques to automate your data reporting process, saving you valuable time and effort.

Optimize Decision-Making: Utilize Power BI to derive actionable insights that support strategic decision-making.

Enhance Your Skills: Gain hands-on experience and build your expertise, equipping you to stand out in the competitive job market.

Imagine mastering the art of data storytelling and making informed decisions with confidence and ease. Together, we’ll unleash the full potential of Power BI, turning raw data into valuable assets.

Embrace the future of data analytics and elevate your skillset to new heights. Start your journey with my Power BI service and take the first step towards achieving your professional goals.

Invest in your skills and let me guide you through this transformative learning experience.