डेटा साइंस और डेटा एनालिटिक्स में एल्गोरिद्म में क्या अंतर है?

डेटा साइंस अक्सर AI/ML model building और complex prediction के लिए Deep Learning/Ensemble algorithms का उपयोग करता है। डेटा एनालिटिक्स का फोकस business insights, reporting और predictive analytics (regression, ARIMA) पर होता है। दोनों में overlap है पर उद्देश्य अलग होता है।

Supervised और Unsupervised learning कब उपयोग करें?

अगर आपके पास labelled data है (target variable) तो Supervised methods (जैसे Regression, Decision Trees) use करें। अगर labels नहीं हैं और आप patterns/segments खोज रहे हैं तो Unsupervised methods (जैसे K-Means, PCA) बेहतर हैं।

Predictive Analytics के लिए सबसे अच्छा एल्गोरिद्म कौन सा है?

यह डेटा और समस्या पर निर्भर करता है। Time-series forecast के लिए ARIMA/Prophet बढ़िया हैं; structured tabular data पर Random Forest और Gradient Boosting (XGBoost/LightGBM/CatBoost) अक्सर top performers होते हैं।

Deep Learning कब ज़रूरी है?

जब आपके पास बहुत बड़ा और complex data हो — images, audio, text या sequence data — तब CNN, RNN/LSTM या Transformers use करना meaningful होता है। छोटा structured data के लिए classical ML ज्यादा efficient रहता है।

Ensemble methods क्यों use करते हैं?

Ensemble (Bagging/Boosting/Stacking) models कई weak learners combine कर accuracy और robustness बढ़ाते हैं — खासकर noisy real-world data में। उदाहरण: XGBoost अक्सर competitions और business problems में top आता है।

क्या मैं Data Analytics सीखकर Data Science roles पा सकता हूँ?

हाँ — Data Analytics से foundational stats, SQL, visualization और basic ML skills मिलते हैं। अगर आप advanced ML/deep learning सीखते हैं तो Data Scientist roles के लिए भी qualify कर सकते हैं।

Excel & Power Query Quiz for Data Analytics (Beginner to Advanced 2025)

📊 Excel & Power Query Quiz for Data Analytics (Beginner to Advanced 2025)

Table of Contents

Toggle

📊 Excel & Power Query Quiz for Data Analytics (Beginner to Advanced 2025)

Are you ready to test your Excel and Power Query skills? This interactive quiz is designed for Data Analytics beginners, intermediates, and advanced learners. From basic Excel formulas to advanced data transformations, these MCQs will help you practice real-world scenarios used by Data Analysts.

🔰 Beginner Friendly

Start with Excel basics like SELECT, formulas, and simple queries to build confidence step by step.

⚡ Power Query Essentials

Practice data cleaning, unpivoting, merging, and append queries with hands-on MCQs.

🚀 Advanced Analytics

Test your knowledge on Power Query M Language, advanced transformations, and automation techniques.

✅ At the end of the quiz, you’ll see your score, correct answers, and retake option to improve your skills.

🔰 Section 2: Beginner Level Quiz (Excel Basics Q1–Q25)

Test your knowledge of Excel fundamentals like formulas, charts, shortcuts, and basic data functions. Select the best answer for each question ⬇️

Which Excel function is used to calculate the sum of values in a range?
AVERAGE()
SUM()
COUNT()
ADD()
In Excel, which symbol is used to start a formula?
=
+
–
*
Which Excel feature is used to quickly summarize large amounts of data?
VLOOKUP
PivotTable
Data Validation
Sorting
What does the CONCATENATE (or CONCAT) function do?
Joins text from multiple cells
Finds duplicates
Splits text
Counts words
Shortcut for creating a new workbook?
Ctrl + N
Ctrl + O
Ctrl + S
Ctrl + W
Which Excel function returns the largest value?
MAX()
LARGE()
BIG()
TOP()
Which chart is best for trends over time?
Bar Chart
Line Chart
Pie Chart
Scatter Plot
What does Ctrl + F do in Excel?
Finds data
Formats cells
Freezes panes
Filters data
Which function counts only numeric values?
COUNT()
COUNTA()
COUNTIF()
SUM()
What does the IF function do?
Performs logical tests
Finds duplicates
Merges tables
Removes blanks
Which shortcut saves a file?
Ctrl + N
Ctrl + S
Ctrl + C
Ctrl + V
Which feature prevents duplicate values in a column?
Sorting
Data Validation
Autofill
Freeze Panes
Which function gives the average?
SUM()
COUNT()
AVERAGE()
MAX()
What does Ctrl + Z do?
Redo
Undo
Save
Close
Which chart shows proportions?
Pie Chart
Line Chart
Column Chart
Scatter Chart
Which function finds the smallest value?
MAX()
SMALL()
MIN()
LEAST()
Which key combo locks a cell reference?
Ctrl + $
F4
Shift + $
Alt + $
Which function counts blank cells?
COUNT()
COUNTBLANK()
COUNTA()
ISBLANK()
Which function looks up values vertically?
HLOOKUP
VLOOKUP
MATCH
INDEX
Which Excel feature splits data into multiple columns?
Text to Columns
Flash Fill
Data Validation
CONCAT
What is the default file extension in Excel (latest)?
.xls
.xlsx
.docx
.csv
Which feature allows freezing rows or columns?
Freeze Panes
Lock Cells
Protect Sheet
Data Validation
Which shortcut copies selected cells?
Ctrl + C
Ctrl + V
Ctrl + X
Ctrl + P
Which Excel function returns the current date?
NOW()
DATE()
TODAY()
TIME()
Which function finds the number of characters in text?
LEN()
FIND()
SEARCH()
LENGTH()

⚡ Section 3: Intermediate Quiz (Power Query Q26–Q50)

Now test your knowledge of Power Query in Excel – used for data cleaning, transformation, and automation. These are real-world scenario questions that every data analyst must know.

Which tab in Excel contains Power Query (Get & Transform Data)?
Insert
Data
Formulas
Review
Power Query primarily helps with:
Data Cleaning & Transformation
Creating PivotTables
Chart Formatting
VBA Macros
Which file formats can Power Query import?
Excel, CSV, JSON, Web, SQL
Only Excel
Only SQL
Only CSV
Which Power Query step removes unwanted columns?
Remove Columns
Split Columns
Merge Queries
Append Queries
Which step combines rows from two tables with the same structure?
Merge Queries
Append Queries
Group By
Unpivot Columns
Which step is used to combine two queries side by side (like JOIN)?
Append Queries
Merge Queries
Group By
Remove Columns
Which feature converts rows into columns in Power Query?
Group By
Transpose
Unpivot Columns
Split Column
What is the scripting language used by Power Query?
DAX
VBA
M Language
SQL
Which Power Query step removes duplicate records?
Group By
Remove Duplicates
Replace Values
Split Column
Which Power Query option changes data type of a column?
Change Type
Replace Values
Use First Row as Headers
Merge Queries
Which Power Query function extracts the first N rows?
Keep Top Rows
Remove Bottom Rows
Keep Range of Rows
Group By
Which step in Power Query flattens nested tables/lists?
Expand
Transpose
Replace Values
Split Column
Which Power Query feature fills blank cells with previous value?
Replace Values
Fill Down
Group By
Use First Row as Headers
Which Power Query step creates new calculated columns?
Custom Column
Duplicate Column
Replace Values
Conditional Column
What is “Unpivot Columns” used for?
Converting columns into attribute-value pairs
Converting rows into columns
Grouping data
Joining tables
Which feature detects and fixes data type errors?
Replace Values
Change Type with Locale
Error Handling
Keep Errors
Which Power Query step splits text using a delimiter?
Split Column
Replace Values
Group By
Unpivot Columns
What does “Close & Load To” allow you to do?
Save queries as workbook functions
Load data to Excel worksheet or Data Model
Save queries as VBA
Export queries as CSV only
Which Power Query step groups rows and applies aggregate functions?
Group By
Append Queries
Transpose
Unpivot Columns
Which feature detects column headers automatically?
Promote Headers
Use First Row as Headers
Rename Columns
Expand
Which Power Query action keeps only rows matching a condition?
Keep Rows
Filter Rows
Group Rows
Remove Errors
Which feature merges multiple CSV files from a folder?
Merge Queries
Append Queries
Combine Files
Expand Tables
Which Power Query action creates branching query steps?
Reference
Duplicate
Conditional Column
Merge Queries
Which Power Query step keeps only rows with errors for troubleshooting?
Keep Errors
Remove Errors
Replace Errors
Highlight Errors
Which option loads Power Query output directly to Power Pivot Data Model?
Close & Load
Close & Load To > Data Model
Export to CSV
Publish to SharePoint

🚀 Section 4: Advanced Quiz (Excel + Power Query Q51–Q75)

Time to challenge your knowledge of advanced Excel formulas, nested functions, and Power Query transformations. These are commonly used by analysts working with large datasets.

Which formula is best to replace VLOOKUP for more flexibility?
INDEX-MATCH
SUMIF
CONCATENATE
TEXTJOIN
Which function returns the column number of a reference?
ROW()
COLUMN()
COLUMNS()
ADDRESS()
Which Power Query step would convert rows into summary metrics?
Group By
Merge Queries
Append Queries
Replace Values
Which Excel function counts cells based on multiple criteria?
COUNTIF
COUNTIFS
SUMPRODUCT
FILTER
What is the M code syntax for creating a new column?
AddColumn()
Table.AddColumn()
Column.New()
Add.NewColumn()
Which Excel dynamic array formula returns unique values?
FILTER()
UNIQUE()
SORT()
RANDARRAY()
Which Power Query feature allows conditional branching?
Conditional Column
Group By
Transpose
Append Queries
Which Excel function combines values with a delimiter?
CONCATENATE()
TEXTJOIN()
CONCAT()
JOIN()
Which Power Query option handles null values?
Replace Values
Fill Down/Up
Remove Nulls
All of the above
Which Excel tool is best for “what-if analysis”?
Power Query
Goal Seek
Solver
Both Goal Seek and Solver
Which M function removes rows with errors?
Table.RemoveErrors()
Table.RemoveRows()
Table.ReplaceErrors()
Remove.Errors()
Which Excel formula extracts a portion of text?
LEFT()/RIGHT()/MID()
TEXT()
VALUE()
FIND()
Which Power Query feature allows multiple queries to be executed as one?
Combine Queries
Merge Queries
Query Dependencies
Query Folding
Which Excel formula is best for multi-condition lookups?
VLOOKUP
INDEX-MATCH-MATCH
XLOOKUP
OFFSET
Which M function renames a column?
Table.TransformColumnNames()
Table.ColumnRename()
Table.RenameColumns()
Column.Rename()
Which Excel formula returns the relative position of an item?
MATCH()
INDEX()
LOOKUP()
SEARCH()
Which feature in Power Query loads optimized queries to the source system?
Query Folding
Query Dependencies
Merge Queries
Reference Queries
Which Excel formula can dynamically filter a dataset?
FILTER()
SORT()
UNIQUE()
IF()
Which M function sorts a table?
Table.Sort()
Sort.Table()
Table.Order()
OrderBy()
Which Excel function finds errors in formulas?
IFERROR()
ISERROR()
ERROR()
ERRORTYPE()
Which M function merges queries with a join type?
Table.Join()
Table.NestedJoin()
Merge.Queries()
Query.Merge()
Which Excel function ranks numbers?
RANK()
RANK.EQ()
RANK.AVG()
All of the above
Which M function unpivots columns into rows?
Table.Unpivot()
Table.UnpivotColumns()
Table.ColumnsToRows()
Table.Pivot()
Which Excel feature creates scenarios with multiple variable inputs?
Solver
Scenario Manager
Data Tables
All of the above
Which M function combines queries vertically?
Table.Append()
Table.Combine()
Append.Tables()
Table.Join()

🏆 Section 5: Expert Level Quiz (Q76–Q100)

Welcome to the Expert Level. These 25 questions test your ability to apply Excel + Power Query in real-world data analytics projects. Be ready for case studies, automation, and scenario-based problem-solving.

You receive monthly sales data from 5 regions, each in separate Excel files. Which Power Query step combines them?
Merge Queries
Append Queries
Group By
Pivot
Which Excel feature is best for creating a dynamic dashboard linked with slicers?
Conditional Formatting
PivotTables + PivotCharts
Power Query
Data Validation
In Power Query, you want to apply the same cleaning steps every month automatically. What feature do you use?
Custom Column
Applied Steps
Query Parameters
Query Folding
You need to calculate a customer’s lifetime sales using multiple transactions. Which Excel feature is best?
VLOOKUP
SUMIFS
INDEX-MATCH
TEXTJOIN
Which M function creates conditional branching similar to IF…ELSE?
Table.If()
if … then … else
Table.Switch()
Case.When()
When connecting Excel with SQL Server via Power Query, which feature optimizes performance by pushing queries to the source?
Query Folding
Merge Queries
Append Queries
Data Types
You want to forecast monthly sales trends using Excel. Which combination is best?
TREND() + LINEST()
IFERROR() + VLOOKUP()
CONCAT() + UNIQUE()
INDEX() + MATCH()
Which Power Query transformation would you use to analyze year-over-year growth?
Add Custom Column with Date.Year()
Transpose
Append Queries
Replace Values
You need to create a KPI dashboard in Excel. Which combination works best?
PivotTables + Conditional Formatting
Merge Queries + Append
Solver + Scenario Manager
Charts + Macros
Which Power Query step is essential for automating monthly reporting?
Refresh All
Remove Duplicates
Replace Errors
Drill Down
In a dataset, you want to show only customers who purchased in 3 consecutive months. Which Excel function helps?
COUNTIFS()
UNIQUE()
FILTER()
SEQUENCE()
Which M function concatenates columns into one?
Text.Combine()
Column.Merge()
Table.CombineColumns()
Join.Text()
For scenario modeling in Excel, which two tools are used?
Solver & Scenario Manager
Filter & Sort
Power Query & Merge
COUNTIF & UNIQUE
You want to automate data refresh when a new file is dropped into a folder. Which Power Query connector helps?
Folder Connector
Web Connector
ODBC Connector
File Connector
Which Excel feature helps create interactive reports with drop-down filters?
Data Validation
Slicers
Solver
Flash Fill
Which Power Query transformation helps when columns represent months and need to become rows?
Unpivot Columns
Pivot Columns
Transpose
Merge Columns
You need to calculate moving averages in Excel. Which approach is best?
AVERAGE() with OFFSET()
VLOOKUP()
IF()
SEQUENCE()
Which M function duplicates a column?
Table.CopyColumn()
Table.DuplicateColumn()
Table.AddColumn()
Column.Clone()
You need to calculate retention rate using customer order data. Which Excel function combo helps?
COUNTIFS() + UNIQUE()
SUM() + FILTER()
MAX() + MIN()
CONCAT() + TEXTJOIN()
Which Excel feature lets you refresh Power Query outputs with a single click?
Refresh All
Solver
Scenario Manager
Flash Fill
Which M function extracts year from a date?
Date.Year()
Year.Date()
Extract.Year()
Table.Year()
You want to detect outliers in Excel sales data. Which functions help?
AVERAGE() + STDEV()
MAX() + MIN()
IFERROR() + VLOOKUP()
CONCAT() + TEXTJOIN()
Which M function removes duplicate rows?
Table.RemoveDuplicates()
Table.Clean()
Table.DropDuplicates()
Remove.Duplicates()
You want to create a fully automated reporting pipeline in Excel + Power Query. Which sequence is correct?
Connect Data → Clean Data → Transform → Load → Refresh
Clean → Connect → Transform → Load → Refresh
Connect → Transform → Load → Clean → Refresh
Connect → Load → Clean → Transform
Which Excel function helps calculate CAGR (Compound Annual Growth Rate)?
RATE()
IRR()
XIRR()
All of the above

🎯 Final Results & Next Steps

Congratulations on completing the 100-Question Excel & Power Query Quiz! Whether you scored high or low, remember: Data Analytics is about practice and consistency. Each quiz attempt builds stronger skills for real-world business analysis.

💡 Beginner (0–40)

Keep practicing the basics of Excel formulas, PivotTables, and Power Query imports. You’re on the right track!

⚡ Intermediate (41–70)

You’re ready for advanced queries, data transformations, and analytics use cases. Time to level up!

🏆 Expert (71–100)

Impressive! You’ve got strong data analytics skills. Apply them in business dashboards and projects.

🚀 Ready to Become a Professional Data Analyst?

Join Vista Academy’s SQL & Data Analytics Course in Dehradun and gain hands-on expertise in Excel, Power Query, SQL, Power BI, and Machine Learning. Learn step-by-step with real-world projects and become job-ready.

📘 Enroll Now – SQL & Data Analytics Mastery