Excel Advanced Course - Part 1 (Data Analysis)

Excel Advanced Course By Vatsal Saxena

Data Analysis Tools

1. Pivot Table & Chart
2. Power Query
3. What-If Analysis
4. Solver Add-in

1. Pivot Table & Chart 

Pivot Table - A Pivot table is a powerful tool in spreadsheet software such as Microsoft excel or Google Sheets that allows you to summarize, analyze and explore large amount of data efficiently.

(i). Dynamic Report - Pivot table me jarurat ke anusar fields add aur remove krte hai. Isme total sum bhi lgta hai.

(ii). Grouping - Pivot table me group selection se hum kisi data ka samuh bnate hai Reports dikhane ke liye.

(iii). Slicers - Excel me Slicer ek aisa tool hai jo pivot table table ya pivot chart me data ko chhatne or filter krne me help karta hai, Ise use karna bahut aasan hai. 

(iv). Pivot Chart - Pivot Chart ek aisa chart hota hai jo pivot table ke data ko visually represent krne ke liye use hota hai.

(v). Calculated Fields - Pivot Calculated field ek aisa feature hai jo Pivot table ke andar additional calculation krne ki facility deta hai.

Ex. - Total amount me GST add krna.

2. Power Query

What is Power Query? - Power Query is a preprocessor engine in MS Excel which is used to clean and transform the data easily.

Power Query Editor

IMP - Yaha  par "Ctrl + Z" kam nhi krta.

Power Query ek tool hai, jisse data ko clean aur tansform kiya jata hai.

Basic Steps for data cleaning and transformation:-

• Excel ki tarah isme bhi tabs aur ribbon hote hai. e.g. Home tab, Transform tab, Add column tab, View atb.
• Power Query me data import kiya jata hai excel, csv ya web se etc.
• In Excel - Excel me aap data tab me jakar "Get data" choose kre aur fir "From Range/Table" select kre. Isse aapka data jo Excel me hai vo Power Query Editor me open ho jayega.
• Data Cleaning - Data Cleaning ka ek example hai - "Removing Empty/Null Cells", Aap revoming column ya rows Options ko use krsakte hai. Filtering Rows bhi ek accha option hai, data clean krne ke liye.
• Data Transformation - Cells ke naam badalna, unko rearrange krna (Sort), Data Type ko badalna aur splitting column etc. options aate hai.
• Combining Data - Power Query me aap do Tables, Ranges ya data ko merge krsaktehai hai, iske liye Power Query me Append Queries or Merge Queries options hote hai.
• Applying Changes - Jab aap data ko clean ya transform krle tab aap Home Tab par Close and Load button ko click krke aapke Cleaned Data ko Excel me bhej sakte hai.

3. What-If Analysis

Ye ek Powerful tool hai jo Excel me Available isme aap Decision Making or Scenario Analysis perform krsakte hai. Ye tool aapko ye samjhane me help krta hai ki agar kisi specific Variable ya cells me koi badlav hota hai to aapke result par kya Effect padega.

Excel me kuch popular what-if analysis tools hai-

1. Scenario Manager
2. Data Table
3. Goal Seek

4. Solver Add-in

Ye ek powerful tool hai jo optimization problems solve krne ke liye use hota hai. Ye aapko kisi objective ko maximize ya minimize krne me madad karta hai, jab aapko constraints ke saath deal krna hota hai. Jaise agar aapko kisi product ki production ko maximize krna ho, ya fir cost ko minimize krna ho, to Solver ka use krke aap in problems ko efficiently solve krsakte hai.