Important Excel, Tableau, Statistics, SQL related Questions with answers
1. What are the common problems that data analysts encounter during analysis?
The common problems steps involved in any analytics project are:
Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues
2. Explain the Type I and Type II errors in Statistics?
In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.
A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.
3. How do you make a dropdown list in MS Excel?
First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.
4. How do you subset or filter data in SQL?
To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.
5. What is a Gantt Chart in Tableau?
A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project
1. What are the common problems that data analysts encounter during analysis?
The common problems steps involved in any analytics project are:
Handling duplicate data
Collecting the meaningful right data at the right time
Handling data purging and storage problems
Making data secure and dealing with compliance issues
2. Explain the Type I and Type II errors in Statistics?
In Hypothesis testing, a Type I error occurs when the null hypothesis is rejected even if it is true. It is also known as a false positive.
A Type II error occurs when the null hypothesis is not rejected, even if it is false. It is also known as a false negative.
3. How do you make a dropdown list in MS Excel?
First, click on the Data tab that is present in the ribbon.
Under the Data Tools group, select Data Validation.
Then navigate to Settings > Allow > List.
Select the source you want to provide as a list array.
4. How do you subset or filter data in SQL?
To subset or filter data in SQL, we use WHERE and HAVING clauses which give us an option of including only the data matching certain conditions.
5. What is a Gantt Chart in Tableau?
A Gantt chart in Tableau depicts the progress of value over the period, i.e., it shows the duration of events. It consists of bars along with the time axis. The Gantt chart is mostly used as a project management tool where each bar is a measure of a task in the project
👍5❤1🔥1
Must important topics to look before any excel interview for Data/Business Analyst role :-
Data Handling: Cell formatting, rows/columns, basic functions (SUM, AVERAGE, COUNT etc).
Data Management Mastery: Sorting, filtering, data validation, diverse cell references. Function Proficiency: Explore SUMIF, (V & X)LOOKUP, INDEX, MATCH, IF, and advanced function nesting.
Advanced Analytics: Master PivotTables for dynamic data analysis and various chart creation.
Advanced Analysis Techniques: Conditional formatting, goal-seeking, in-depth what-if analysis.
Advanced Functions: COUNTIF/IFS, SUMIFS, AVERAGEIF/IFS, CONCATENATE, date/time functions.
These are the most important one's which I tried to summarise in the best possible way, please let me know in the comments if I have missed something important.
Data Handling: Cell formatting, rows/columns, basic functions (SUM, AVERAGE, COUNT etc).
Data Management Mastery: Sorting, filtering, data validation, diverse cell references. Function Proficiency: Explore SUMIF, (V & X)LOOKUP, INDEX, MATCH, IF, and advanced function nesting.
Advanced Analytics: Master PivotTables for dynamic data analysis and various chart creation.
Advanced Analysis Techniques: Conditional formatting, goal-seeking, in-depth what-if analysis.
Advanced Functions: COUNTIF/IFS, SUMIFS, AVERAGEIF/IFS, CONCATENATE, date/time functions.
These are the most important one's which I tried to summarise in the best possible way, please let me know in the comments if I have missed something important.
👍5
Essential Excel Concepts for Beginners
1. VLOOKUP: VLOOKUP is a popular Excel function used to search for a value in the first column of a table and return a corresponding value in the same row from another column. It is commonly used for data lookup and retrieval tasks.
2. Pivot Tables: Pivot tables are powerful tools in Excel for summarizing and analyzing large datasets. They allow you to reorganize and summarize data, perform calculations, and create interactive reports with ease.
3. Conditional Formatting: Conditional formatting allows you to format cells based on specific conditions or criteria. It helps highlight important information, identify trends, and make data more visually appealing and easier to interpret.
4. INDEX-MATCH: INDEX-MATCH is an alternative to VLOOKUP that combines the INDEX and MATCH functions to perform more flexible and powerful lookups in Excel. It is often preferred over VLOOKUP for its versatility and robustness.
5. Data Validation: Data validation is a feature in Excel that allows you to control what type of data can be entered into a cell. You can set rules, create drop-down lists, and provide error messages to ensure data accuracy and consistency.
6. SUMIF: SUMIF is a function in Excel that allows you to sum values in a range based on a specific condition or criteria. It is useful for calculating totals based on certain criteria without the need for complex formulas.
7. CONCATENATE: CONCATENATE is a function in Excel used to combine multiple text strings into one. It is helpful for creating custom labels, joining data from different cells, and formatting text in a desired way.
8. Goal Seek: Goal Seek is a built-in tool in Excel that allows you to find the input value needed to achieve a desired result in a formula. It is useful for performing reverse calculations and solving what-if scenarios.
9. Data Tables: Data tables in Excel allow you to perform sensitivity analysis by calculating multiple results based on different input values. They help you analyze how changing variables impact the final outcome of a formula.
10. Sparklines: Sparklines are small, simple charts that provide visual representations of data trends within a single cell. They are useful for quickly visualizing patterns and trends in data without the need for larger charts or graphs.
1. VLOOKUP: VLOOKUP is a popular Excel function used to search for a value in the first column of a table and return a corresponding value in the same row from another column. It is commonly used for data lookup and retrieval tasks.
2. Pivot Tables: Pivot tables are powerful tools in Excel for summarizing and analyzing large datasets. They allow you to reorganize and summarize data, perform calculations, and create interactive reports with ease.
3. Conditional Formatting: Conditional formatting allows you to format cells based on specific conditions or criteria. It helps highlight important information, identify trends, and make data more visually appealing and easier to interpret.
4. INDEX-MATCH: INDEX-MATCH is an alternative to VLOOKUP that combines the INDEX and MATCH functions to perform more flexible and powerful lookups in Excel. It is often preferred over VLOOKUP for its versatility and robustness.
5. Data Validation: Data validation is a feature in Excel that allows you to control what type of data can be entered into a cell. You can set rules, create drop-down lists, and provide error messages to ensure data accuracy and consistency.
6. SUMIF: SUMIF is a function in Excel that allows you to sum values in a range based on a specific condition or criteria. It is useful for calculating totals based on certain criteria without the need for complex formulas.
7. CONCATENATE: CONCATENATE is a function in Excel used to combine multiple text strings into one. It is helpful for creating custom labels, joining data from different cells, and formatting text in a desired way.
8. Goal Seek: Goal Seek is a built-in tool in Excel that allows you to find the input value needed to achieve a desired result in a formula. It is useful for performing reverse calculations and solving what-if scenarios.
9. Data Tables: Data tables in Excel allow you to perform sensitivity analysis by calculating multiple results based on different input values. They help you analyze how changing variables impact the final outcome of a formula.
10. Sparklines: Sparklines are small, simple charts that provide visual representations of data trends within a single cell. They are useful for quickly visualizing patterns and trends in data without the need for larger charts or graphs.
👍4❤1
📊 Excel Hack of the Week
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
Did you know you can use Flash Fill in Excel to automatically clean and format data without writing formulas?
📝 How to Use Flash Fill?
1️⃣ Type the first correct value manually in the adjacent column.
2️⃣ Press Ctrl + E (or go to Data > Flash Fill).
3️⃣ Excel will recognize the pattern and fill in the rest automatically!
🔍 Example:
✅ Extract first names from "John Doe" → Type "John" → Press Ctrl + E → Done!
✅ Format phone numbers from "1234567890" to "(123) 456-7890" in seconds!
✅ Convert dates from "01-02-2024" to "February 1, 2024" instantly!
📌 Bonus: Try using Flash Fill for splitting names, fixing email formats, or even extracting numbers from text.
You can join @excel_data for free Excel Resources.
Like this post for more data analytics tricks 👍♥️
Share with credits: https://t.iss.one/sqlspecialist
Hope it helps :)
❤4👍2
You don't need to know everything about every data tool. Focus on what will help land you your job.
For Excel:
- IFS (all variations)
- XLOOKUP
- IMPORTRANGE (in GSheets)
- Pivot Tables
- Dynamic functions like TODAY()
For SQL:
- Sum
- Group By
- Window Functions
- CTEs
- Joins
For Tableau:
- Calculated Columns
- Sets
- Groups
- Formatting
For Power BI:
- Power Query for data transformation
- DAX (Data Analysis Expressions) for creating custom calculations
- Relationships between tables
- Creating interactive and dynamic dashboards
- Utilizing slicers and filters effectively
You can now find Data Analytics Resources on WhatsApp 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
For Excel:
- IFS (all variations)
- XLOOKUP
- IMPORTRANGE (in GSheets)
- Pivot Tables
- Dynamic functions like TODAY()
For SQL:
- Sum
- Group By
- Window Functions
- CTEs
- Joins
For Tableau:
- Calculated Columns
- Sets
- Groups
- Formatting
For Power BI:
- Power Query for data transformation
- DAX (Data Analysis Expressions) for creating custom calculations
- Relationships between tables
- Creating interactive and dynamic dashboards
- Utilizing slicers and filters effectively
You can now find Data Analytics Resources on WhatsApp 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope this helps you 😊
👍5
1. How many report formats are available in Excel?
There are three report formats available in Excel; they are:
1. Compact Form
2. Outline Form
3. Tabular Form
2. What are sets in Tableau?
Sets are custom fields that define a subset of data based on some conditions. A set can be based on a computed condition, for example, a set may contain customers with sales over a certain threshold. Computed sets update as your data changes. Alternatively, a set can be based on specific data point in your view.
3. What is the difference between DROP and TRUNCATE commands?
DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.
4. What is slicing in Python?
Ans: Slicing is used to access parts of sequences like lists, tuples, and strings. The syntax of slicing is-[start:end:step]. The step can be omitted as well. When we write [start:end] this returns all the elements of the sequence from the start (inclusive) till the end-1 element. If the start or end element is negative i, it means the ith element from the end.
5. What is the map() and filter() function in Python?
The map() function is a higher-order function. This function accepts another function and a sequence of ‘iterables’ as parameters and provides output after applying the function to each iterable in the sequence. The filter() function is used to generate an output list of values that return true when the function is called.
There are three report formats available in Excel; they are:
1. Compact Form
2. Outline Form
3. Tabular Form
2. What are sets in Tableau?
Sets are custom fields that define a subset of data based on some conditions. A set can be based on a computed condition, for example, a set may contain customers with sales over a certain threshold. Computed sets update as your data changes. Alternatively, a set can be based on specific data point in your view.
3. What is the difference between DROP and TRUNCATE commands?
DROP command removes a table and it cannot be rolled back from the database whereas TRUNCATE command removes all the rows from the table.
4. What is slicing in Python?
Ans: Slicing is used to access parts of sequences like lists, tuples, and strings. The syntax of slicing is-[start:end:step]. The step can be omitted as well. When we write [start:end] this returns all the elements of the sequence from the start (inclusive) till the end-1 element. If the start or end element is negative i, it means the ith element from the end.
5. What is the map() and filter() function in Python?
The map() function is a higher-order function. This function accepts another function and a sequence of ‘iterables’ as parameters and provides output after applying the function to each iterable in the sequence. The filter() function is used to generate an output list of values that return true when the function is called.
❤3👍2
In Excel, you can create 𝗰𝗼𝗹𝗼𝗿-𝗰𝗼𝗱𝗲𝗱 𝗰𝗵𝗲𝗰𝗸𝗯𝗼𝘅𝗲𝘀 𝘂𝘀𝗶𝗻𝗴 𝗖𝗼𝗻𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗙𝗼𝗿𝗺𝗮𝘁𝘁𝗶𝗻𝗴 𝗮𝗻𝗱 𝗙𝗼𝗿𝗺 𝗖𝗼𝗻𝘁𝗿𝗼𝗹𝘀. Here’s how:
𝗦𝘁𝗲𝗽 𝟭: 𝗜𝗻𝘀𝗲𝗿𝘁 𝗖𝗵𝗲𝗰𝗸𝗯𝗼𝘅𝗲𝘀
1. Go to the Developer tab (if not enabled, go to File → Options→ Customize Ribbon → Enable Developer).
2. Click Insert → Choose Check Box (Form Control).
3. Click anywhere in a cell to place the checkbox.
4. Right-click the checkbox → Select Edit Text → Delete the default text (optional).
5. Copy-paste the checkbox to other cells as needed.
𝗦𝘁𝗲𝗽 𝟮: 𝗟𝗶𝗻𝗸 𝗖𝗵𝗲𝗰𝗸𝗯𝗼𝘅𝗲𝘀 𝘁𝗼 𝗖𝗲𝗹𝗹𝘀
1. Right-click a checkbox → Select Format Control.
2. In the Control tab, set the Cell link to a nearby cell (e.g.,
3. Repeat for all checkboxes (or drag to autofill if sequential).
4. The linked cell will show TRUE (checked) or FALSE (unchecked).
𝗦𝘁𝗲𝗽 𝟯: 𝗔𝗽𝗽𝗹𝘆 𝗖𝗼𝗻𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗙𝗼𝗿𝗺𝗮𝘁𝘁𝗶𝗻𝗴 𝗳𝗼𝗿 𝗖𝗼𝗹𝗼𝗿 𝗖𝗼𝗱𝗶𝗻𝗴.
1. Select the cells where you want the color to appear (e.g.,
2. Go to Home →Conditional Formatting→ New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter the formula:
- For Green (Checked):
- For Red (Unchecked):
5. Click Format, choose a Fill Color (Green for checked, Red for unchecked), then OK.
6. Click OK again to apply the rule.
Now, when you check a box, the corresponding cell changes color! 🚀 Let me know if you need more details!
Join for more: 👇
https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
𝗦𝘁𝗲𝗽 𝟭: 𝗜𝗻𝘀𝗲𝗿𝘁 𝗖𝗵𝗲𝗰𝗸𝗯𝗼𝘅𝗲𝘀
1. Go to the Developer tab (if not enabled, go to File → Options→ Customize Ribbon → Enable Developer).
2. Click Insert → Choose Check Box (Form Control).
3. Click anywhere in a cell to place the checkbox.
4. Right-click the checkbox → Select Edit Text → Delete the default text (optional).
5. Copy-paste the checkbox to other cells as needed.
𝗦𝘁𝗲𝗽 𝟮: 𝗟𝗶𝗻𝗸 𝗖𝗵𝗲𝗰𝗸𝗯𝗼𝘅𝗲𝘀 𝘁𝗼 𝗖𝗲𝗹𝗹𝘀
1. Right-click a checkbox → Select Format Control.
2. In the Control tab, set the Cell link to a nearby cell (e.g.,
B2 if the checkbox is in A2).3. Repeat for all checkboxes (or drag to autofill if sequential).
4. The linked cell will show TRUE (checked) or FALSE (unchecked).
𝗦𝘁𝗲𝗽 𝟯: 𝗔𝗽𝗽𝗹𝘆 𝗖𝗼𝗻𝗱𝗶𝘁𝗶𝗼𝗻𝗮𝗹 𝗙𝗼𝗿𝗺𝗮𝘁𝘁𝗶𝗻𝗴 𝗳𝗼𝗿 𝗖𝗼𝗹𝗼𝗿 𝗖𝗼𝗱𝗶𝗻𝗴.
1. Select the cells where you want the color to appear (e.g.,
A2:A10).2. Go to Home →Conditional Formatting→ New Rule.
3. Choose "Use a formula to determine which cells to format".
4. Enter the formula:
- For Green (Checked):
=B2=TRUE- For Red (Unchecked):
=B2=FALSE5. Click Format, choose a Fill Color (Green for checked, Red for unchecked), then OK.
6. Click OK again to apply the rule.
Now, when you check a box, the corresponding cell changes color! 🚀 Let me know if you need more details!
Join for more: 👇
https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
❤4👍4
Advanced Questions Asked by Big 4
📊 Excel Questions
1. How do you use Excel to forecast future trends based on historical data? Describe a scenario where you built a forecasting model.
2. Can you explain how you would automate repetitive tasks in Excel using VBA (Visual Basic for Applications)? Provide an example of a complex macro you created.
3. Describe a time when you had to merge and analyze data from multiple Excel workbooks. How did you ensure data integrity and accuracy?
🗄 SQL Questions
1. How would you design a database schema for a new e-commerce platform to efficiently handle large volumes of transactions and user data?
2. Describe a complex SQL query you wrote to solve a business problem. What was the problem, and how did your query help resolve it?
3. How do you ensure data integrity and consistency in a multi-user database environment? Explain the techniques and tools you use.
🐍 Python Questions
1. How would you use Python to automate data extraction from various APIs and combine the data for analysis? Provide an example.
2. Describe a machine learning project you worked on using Python. What was the objective, and how did you approach the data preprocessing, model selection, and evaluation?
3. Explain how you would use Python to detect and handle anomalies in a dataset. What techniques and libraries would you employ?
📈 Power BI Questions
1. How do you create interactive dashboards in Power BI that can dynamically update based on user inputs? Provide an example of a dashboard you built.
2. Describe a scenario where you used Power BI to integrate data from non-traditional sources (e.g., web scraping, APIs). How did you handle the data transformation and visualization?
3. How do you ensure the performance and scalability of Power BI reports when dealing with large datasets? Describe the techniques and best practices you follow.
💡 Tips for Success:
Understand the business context: Tailor your answers to show how your technical skills solve real business problems.
Provide specific examples: Highlight your past experiences with concrete examples.
Stay updated: Continuously learn and adapt to new tools and methodologies.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
📊 Excel Questions
1. How do you use Excel to forecast future trends based on historical data? Describe a scenario where you built a forecasting model.
2. Can you explain how you would automate repetitive tasks in Excel using VBA (Visual Basic for Applications)? Provide an example of a complex macro you created.
3. Describe a time when you had to merge and analyze data from multiple Excel workbooks. How did you ensure data integrity and accuracy?
🗄 SQL Questions
1. How would you design a database schema for a new e-commerce platform to efficiently handle large volumes of transactions and user data?
2. Describe a complex SQL query you wrote to solve a business problem. What was the problem, and how did your query help resolve it?
3. How do you ensure data integrity and consistency in a multi-user database environment? Explain the techniques and tools you use.
🐍 Python Questions
1. How would you use Python to automate data extraction from various APIs and combine the data for analysis? Provide an example.
2. Describe a machine learning project you worked on using Python. What was the objective, and how did you approach the data preprocessing, model selection, and evaluation?
3. Explain how you would use Python to detect and handle anomalies in a dataset. What techniques and libraries would you employ?
📈 Power BI Questions
1. How do you create interactive dashboards in Power BI that can dynamically update based on user inputs? Provide an example of a dashboard you built.
2. Describe a scenario where you used Power BI to integrate data from non-traditional sources (e.g., web scraping, APIs). How did you handle the data transformation and visualization?
3. How do you ensure the performance and scalability of Power BI reports when dealing with large datasets? Describe the techniques and best practices you follow.
💡 Tips for Success:
Understand the business context: Tailor your answers to show how your technical skills solve real business problems.
Provide specific examples: Highlight your past experiences with concrete examples.
Stay updated: Continuously learn and adapt to new tools and methodologies.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Hope it helps :)
❤2👍2🤗1
Essential Excel Functions for Data Analysts 🚀
1️⃣ Basic Functions
SUM() – Adds a range of numbers. =SUM(A1:A10)
AVERAGE() – Calculates the average. =AVERAGE(A1:A10)
MIN() / MAX() – Finds the smallest/largest value. =MIN(A1:A10)
2️⃣ Logical Functions
IF() – Conditional logic. =IF(A1>50, "Pass", "Fail")
IFS() – Multiple conditions. =IFS(A1>90, "A", A1>80, "B", TRUE, "C")
AND() / OR() – Checks multiple conditions. =AND(A1>50, B1<100)
3️⃣ Text Functions
LEFT() / RIGHT() / MID() – Extract text from a string.
=LEFT(A1, 3) (First 3 characters)
=MID(A1, 3, 2) (2 characters from the 3rd position)
LEN() – Counts characters. =LEN(A1)
TRIM() – Removes extra spaces. =TRIM(A1)
UPPER() / LOWER() / PROPER() – Changes text case.
4️⃣ Lookup Functions
VLOOKUP() – Searches for a value in a column.
=VLOOKUP(1001, A2:B10, 2, FALSE)
HLOOKUP() – Searches in a row.
XLOOKUP() – Advanced lookup replacing VLOOKUP.
=XLOOKUP(1001, A2:A10, B2:B10, "Not Found")
5️⃣ Date & Time Functions
TODAY() – Returns the current date.
NOW() – Returns the current date and time.
YEAR(), MONTH(), DAY() – Extracts parts of a date.
DATEDIF() – Calculates the difference between two dates.
6️⃣ Data Cleaning Functions
REMOVE DUPLICATES – Found in the "Data" tab.
CLEAN() – Removes non-printable characters.
SUBSTITUTE() – Replaces text within a string.
=SUBSTITUTE(A1, "old", "new")
7️⃣ Advanced Functions
INDEX() & MATCH() – More flexible alternative to VLOOKUP.
TEXTJOIN() – Joins text with a delimiter.
UNIQUE() – Returns unique values from a range.
FILTER() – Filters data dynamically.
=FILTER(A2:B10, B2:B10>50)
8️⃣ Pivot Tables & Power Query
PIVOT TABLES – Summarizes data dynamically.
GETPIVOTDATA() – Extracts data from a Pivot Table.
POWER QUERY – Automates data cleaning & transformation.
You can find Free Excel Resources here: https://t.iss.one/excel_data
Hope it helps :)
#dataanalytics
1️⃣ Basic Functions
SUM() – Adds a range of numbers. =SUM(A1:A10)
AVERAGE() – Calculates the average. =AVERAGE(A1:A10)
MIN() / MAX() – Finds the smallest/largest value. =MIN(A1:A10)
2️⃣ Logical Functions
IF() – Conditional logic. =IF(A1>50, "Pass", "Fail")
IFS() – Multiple conditions. =IFS(A1>90, "A", A1>80, "B", TRUE, "C")
AND() / OR() – Checks multiple conditions. =AND(A1>50, B1<100)
3️⃣ Text Functions
LEFT() / RIGHT() / MID() – Extract text from a string.
=LEFT(A1, 3) (First 3 characters)
=MID(A1, 3, 2) (2 characters from the 3rd position)
LEN() – Counts characters. =LEN(A1)
TRIM() – Removes extra spaces. =TRIM(A1)
UPPER() / LOWER() / PROPER() – Changes text case.
4️⃣ Lookup Functions
VLOOKUP() – Searches for a value in a column.
=VLOOKUP(1001, A2:B10, 2, FALSE)
HLOOKUP() – Searches in a row.
XLOOKUP() – Advanced lookup replacing VLOOKUP.
=XLOOKUP(1001, A2:A10, B2:B10, "Not Found")
5️⃣ Date & Time Functions
TODAY() – Returns the current date.
NOW() – Returns the current date and time.
YEAR(), MONTH(), DAY() – Extracts parts of a date.
DATEDIF() – Calculates the difference between two dates.
6️⃣ Data Cleaning Functions
REMOVE DUPLICATES – Found in the "Data" tab.
CLEAN() – Removes non-printable characters.
SUBSTITUTE() – Replaces text within a string.
=SUBSTITUTE(A1, "old", "new")
7️⃣ Advanced Functions
INDEX() & MATCH() – More flexible alternative to VLOOKUP.
TEXTJOIN() – Joins text with a delimiter.
UNIQUE() – Returns unique values from a range.
FILTER() – Filters data dynamically.
=FILTER(A2:B10, B2:B10>50)
8️⃣ Pivot Tables & Power Query
PIVOT TABLES – Summarizes data dynamically.
GETPIVOTDATA() – Extracts data from a Pivot Table.
POWER QUERY – Automates data cleaning & transformation.
You can find Free Excel Resources here: https://t.iss.one/excel_data
Hope it helps :)
#dataanalytics
👍5❤2
Excel tips to help in interviews! 🌟
1. Practice core features: Be comfortable with formulas (SUM, IF, VLOOKUP), pivot tables, charts, and conditional formatting.
2. Know your references: Understand the difference between relative, absolute, and mixed cell references—they often come up.
3. Showcase real examples: Be ready to explain how you’ve used Excel to solve problems or improve processes, like automating reports with macros or cleaning data with filters.
4. Data validation & error handling: Mention using data validation to restrict inputs and functions like COUNTIF or ISBLANK to spot errors or missing data.
5. Communicate clearly: Practice explaining complex Excel data or dashboards in simple terms for non-technical audiences.
6. Stay updated: Mention any recent Excel features you know, like XLOOKUP or Power Query, to show you’re keeping up.
React ❤️ for more
1. Practice core features: Be comfortable with formulas (SUM, IF, VLOOKUP), pivot tables, charts, and conditional formatting.
2. Know your references: Understand the difference between relative, absolute, and mixed cell references—they often come up.
3. Showcase real examples: Be ready to explain how you’ve used Excel to solve problems or improve processes, like automating reports with macros or cleaning data with filters.
4. Data validation & error handling: Mention using data validation to restrict inputs and functions like COUNTIF or ISBLANK to spot errors or missing data.
5. Communicate clearly: Practice explaining complex Excel data or dashboards in simple terms for non-technical audiences.
6. Stay updated: Mention any recent Excel features you know, like XLOOKUP or Power Query, to show you’re keeping up.
React ❤️ for more
❤5👍2
Questions & Answers for Data Analyst Interview
Question 1: Describe a time when you used data analysis to solve a business problem.
Ideal answer: This is your opportunity to showcase your data analysis skills in a real-world context. Be specific and provide examples of your work. For example, you could talk about a time when you used data analysis to identify customer churn, improve marketing campaigns, or optimize product development.
Question 2: What are some of the challenges you have faced in previous data analysis projects, and how did you overcome them?
Ideal answer: This question is designed to assess your problem-solving skills and your ability to learn from your experiences. Be honest and upfront about the challenges you have faced, but also focus on how you overcame them. For example, you could talk about a time when you had to deal with a large and messy dataset, or a time when you had to work with a tight deadline.
Question 3: How do you handle missing values in a dataset?
Ideal answer: Missing values are a common problem in data analysis, so it is important to know how to handle them properly. There are a variety of different methods that you can use, depending on the specific situation. For example, you could delete the rows with missing values, impute the missing values using a statistical method, or assign a default value to the missing values.
Question 4: How do you identify and remove outliers?
Ideal answer: Outliers are data points that are significantly different from the rest of the data. They can be caused by data errors or by natural variation in the data. It is important to identify and remove outliers before performing data analysis, as they can skew the results. There are a variety of different methods that you can use to identify outliers, such as the interquartile range (IQR) method or the standard deviation method.
Question 5: How do you interpret and communicate the results of your data analysis to non-technical audiences?
Ideal answer: It is important to be able to communicate your data analysis findings to both technical and non-technical audiences. When communicating to non-technical audiences, it is important to avoid using jargon and to focus on the key takeaways from your analysis. You can use data visualization tools to help you communicate your findings in a clear and concise way.
In addition to providing specific examples and answers to the questions, it is also important to be enthusiastic and demonstrate your passion for data analysis. Show the interviewer that you are excited about the opportunity to use your skills to solve real-world problems.
Question 1: Describe a time when you used data analysis to solve a business problem.
Ideal answer: This is your opportunity to showcase your data analysis skills in a real-world context. Be specific and provide examples of your work. For example, you could talk about a time when you used data analysis to identify customer churn, improve marketing campaigns, or optimize product development.
Question 2: What are some of the challenges you have faced in previous data analysis projects, and how did you overcome them?
Ideal answer: This question is designed to assess your problem-solving skills and your ability to learn from your experiences. Be honest and upfront about the challenges you have faced, but also focus on how you overcame them. For example, you could talk about a time when you had to deal with a large and messy dataset, or a time when you had to work with a tight deadline.
Question 3: How do you handle missing values in a dataset?
Ideal answer: Missing values are a common problem in data analysis, so it is important to know how to handle them properly. There are a variety of different methods that you can use, depending on the specific situation. For example, you could delete the rows with missing values, impute the missing values using a statistical method, or assign a default value to the missing values.
Question 4: How do you identify and remove outliers?
Ideal answer: Outliers are data points that are significantly different from the rest of the data. They can be caused by data errors or by natural variation in the data. It is important to identify and remove outliers before performing data analysis, as they can skew the results. There are a variety of different methods that you can use to identify outliers, such as the interquartile range (IQR) method or the standard deviation method.
Question 5: How do you interpret and communicate the results of your data analysis to non-technical audiences?
Ideal answer: It is important to be able to communicate your data analysis findings to both technical and non-technical audiences. When communicating to non-technical audiences, it is important to avoid using jargon and to focus on the key takeaways from your analysis. You can use data visualization tools to help you communicate your findings in a clear and concise way.
In addition to providing specific examples and answers to the questions, it is also important to be enthusiastic and demonstrate your passion for data analysis. Show the interviewer that you are excited about the opportunity to use your skills to solve real-world problems.
👍13
Data analytics is not about the the tools you master but about the people you influence.
I see many debates around the best tools such as:
- Excel vs SQL
- Python vs R
- Tableau vs PowerBI
- ChatGPT vs no ChatGPT
The truth is that business doesn't care about how you come up with your insights.
All business cares about is:
- the story line
- how well they can understand it
- your communication style
- the overall feeling after a presentation
These make the difference in being perceived as a great data analyst...
not the tools you may or may not master 😅
I see many debates around the best tools such as:
- Excel vs SQL
- Python vs R
- Tableau vs PowerBI
- ChatGPT vs no ChatGPT
The truth is that business doesn't care about how you come up with your insights.
All business cares about is:
- the story line
- how well they can understand it
- your communication style
- the overall feeling after a presentation
These make the difference in being perceived as a great data analyst...
not the tools you may or may not master 😅
👍5❤2
Essential Excel Concepts for Beginners
1. VLOOKUP: VLOOKUP is a popular Excel function used to search for a value in the first column of a table and return a corresponding value in the same row from another column. It is commonly used for data lookup and retrieval tasks.
2. Pivot Tables: Pivot tables are powerful tools in Excel for summarizing and analyzing large datasets. They allow you to reorganize and summarize data, perform calculations, and create interactive reports with ease.
3. Conditional Formatting: Conditional formatting allows you to format cells based on specific conditions or criteria. It helps highlight important information, identify trends, and make data more visually appealing and easier to interpret.
4. INDEX-MATCH: INDEX-MATCH is an alternative to VLOOKUP that combines the INDEX and MATCH functions to perform more flexible and powerful lookups in Excel. It is often preferred over VLOOKUP for its versatility and robustness.
5. Data Validation: Data validation is a feature in Excel that allows you to control what type of data can be entered into a cell. You can set rules, create drop-down lists, and provide error messages to ensure data accuracy and consistency.
6. SUMIF: SUMIF is a function in Excel that allows you to sum values in a range based on a specific condition or criteria. It is useful for calculating totals based on certain criteria without the need for complex formulas.
7. CONCATENATE: CONCATENATE is a function in Excel used to combine multiple text strings into one. It is helpful for creating custom labels, joining data from different cells, and formatting text in a desired way.
8. Goal Seek: Goal Seek is a built-in tool in Excel that allows you to find the input value needed to achieve a desired result in a formula. It is useful for performing reverse calculations and solving what-if scenarios.
9. Data Tables: Data tables in Excel allow you to perform sensitivity analysis by calculating multiple results based on different input values. They help you analyze how changing variables impact the final outcome of a formula.
10. Sparklines: Sparklines are small, simple charts that provide visual representations of data trends within a single cell. They are useful for quickly visualizing patterns and trends in data without the need for larger charts or graphs.
1. VLOOKUP: VLOOKUP is a popular Excel function used to search for a value in the first column of a table and return a corresponding value in the same row from another column. It is commonly used for data lookup and retrieval tasks.
2. Pivot Tables: Pivot tables are powerful tools in Excel for summarizing and analyzing large datasets. They allow you to reorganize and summarize data, perform calculations, and create interactive reports with ease.
3. Conditional Formatting: Conditional formatting allows you to format cells based on specific conditions or criteria. It helps highlight important information, identify trends, and make data more visually appealing and easier to interpret.
4. INDEX-MATCH: INDEX-MATCH is an alternative to VLOOKUP that combines the INDEX and MATCH functions to perform more flexible and powerful lookups in Excel. It is often preferred over VLOOKUP for its versatility and robustness.
5. Data Validation: Data validation is a feature in Excel that allows you to control what type of data can be entered into a cell. You can set rules, create drop-down lists, and provide error messages to ensure data accuracy and consistency.
6. SUMIF: SUMIF is a function in Excel that allows you to sum values in a range based on a specific condition or criteria. It is useful for calculating totals based on certain criteria without the need for complex formulas.
7. CONCATENATE: CONCATENATE is a function in Excel used to combine multiple text strings into one. It is helpful for creating custom labels, joining data from different cells, and formatting text in a desired way.
8. Goal Seek: Goal Seek is a built-in tool in Excel that allows you to find the input value needed to achieve a desired result in a formula. It is useful for performing reverse calculations and solving what-if scenarios.
9. Data Tables: Data tables in Excel allow you to perform sensitivity analysis by calculating multiple results based on different input values. They help you analyze how changing variables impact the final outcome of a formula.
10. Sparklines: Sparklines are small, simple charts that provide visual representations of data trends within a single cell. They are useful for quickly visualizing patterns and trends in data without the need for larger charts or graphs.
❤5👍2
Data Analyst Interview Questions
1. What do Tableau's sets and groups mean?
Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.
2.What in Excel is a macro?
An Excel macro is an algorithm or a group of steps that helps automate an operation by capturing and replaying the steps needed to finish it. Once the steps have been saved, you may construct a Macro that the user can alter and replay as often as they like.
Macro is excellent for routine work because it also gets rid of mistakes. Consider the scenario when an account manager needs to share reports about staff members who owe the company money. If so, it can be automated by utilising a macro and making small adjustments each month as necessary.
3.Gantt chart in Tableau
A Tableau Gantt chart illustrates the duration of events as well as the progression of value across the period. Along with the time axis, it has bars. The Gantt chart is primarily used as a project management tool, with each bar representing a project job.
4.In Microsoft Excel, how do you create a drop-down list?
Start by selecting the Data tab from the ribbon.
Select Data Validation from the Data Tools group.
Go to Settings > Allow > List next.
Choose the source you want to offer in the form of a list array.
1. What do Tableau's sets and groups mean?
Data is grouped using sets and groups according to predefined criteria. The primary distinction between the two is that although a set can have only two options—either in or out—a group can divide the dataset into several groups. A user should decide which group or sets to apply based on the conditions.
2.What in Excel is a macro?
An Excel macro is an algorithm or a group of steps that helps automate an operation by capturing and replaying the steps needed to finish it. Once the steps have been saved, you may construct a Macro that the user can alter and replay as often as they like.
Macro is excellent for routine work because it also gets rid of mistakes. Consider the scenario when an account manager needs to share reports about staff members who owe the company money. If so, it can be automated by utilising a macro and making small adjustments each month as necessary.
3.Gantt chart in Tableau
A Tableau Gantt chart illustrates the duration of events as well as the progression of value across the period. Along with the time axis, it has bars. The Gantt chart is primarily used as a project management tool, with each bar representing a project job.
4.In Microsoft Excel, how do you create a drop-down list?
Start by selecting the Data tab from the ribbon.
Select Data Validation from the Data Tools group.
Go to Settings > Allow > List next.
Choose the source you want to offer in the form of a list array.
❤9👍1
What seperates a good 𝗗𝗮𝘁𝗮 𝗔𝗻𝗮𝗹𝘆𝘀𝘁 from a great one?
The journey to becoming an exceptional data analyst requires mastering a blend of technical and soft skills.
☑ Technical skills:
- Querying Data with SQL
- Data Visualization (Tableau/PowerBI)
- Data Storytelling and Reporting
- Data Exploration and Analytics
- Data Modeling
☑ Soft Skills:
- Problem Solving
- Communication
- Business Acumen
- Curiosity
- Critical Thinking
- Learning Mindset
But how do you develop these soft skills?
◆ Tackle real-world data projects or case studies. The more complex, the better.
◆ Practice explaining your analysis to non-technical audiences. If they understand, you’ve nailed it!
◆ Learn how industries use data for decision-making. Align your analysis with business outcomes.
◆ Stay curious, ask 'why,' and dig deeper into your data. Don’t settle for surface-level insights.
◆ Keep evolving. Attend webinars, read books, or engage with industry experts regularly.
The journey to becoming an exceptional data analyst requires mastering a blend of technical and soft skills.
☑ Technical skills:
- Querying Data with SQL
- Data Visualization (Tableau/PowerBI)
- Data Storytelling and Reporting
- Data Exploration and Analytics
- Data Modeling
☑ Soft Skills:
- Problem Solving
- Communication
- Business Acumen
- Curiosity
- Critical Thinking
- Learning Mindset
But how do you develop these soft skills?
◆ Tackle real-world data projects or case studies. The more complex, the better.
◆ Practice explaining your analysis to non-technical audiences. If they understand, you’ve nailed it!
◆ Learn how industries use data for decision-making. Align your analysis with business outcomes.
◆ Stay curious, ask 'why,' and dig deeper into your data. Don’t settle for surface-level insights.
◆ Keep evolving. Attend webinars, read books, or engage with industry experts regularly.
❤5👍2
Amazon Data Analyst Interview Questions for 1-3 years of experience role :-
A. SQL:
1. You have two tables: Employee and Department.
- Employee Table Columns: Employee_id, Employee_Name, Department_id, Salary
- Department Table Columns: Department_id, Department_Name, Location
Write an SQL query to find the name of the employee with the highest salary in each location.
2. You have two tables: Orders and Customers.
- Orders Table Columns: Order_id, Customer_id, Order_Date, Amount
- Customers Table Columns: Customer_id, Customer_Name, Join_Date
Write an SQL query to calculate the total order amount for each customer who joined in the current year. The output should contain Customer_Name and the total amount.
B. Python:
1. Basic oral questions on NumPy (e.g., array creation, slicing, broadcasting) and Matplotlib (e.g., plot types, customization).
2. Basic oral questions on pandas (like: groupby, loc/iloc, merge & join, etc.)
2. Write the code in NumPy and Pandas to replicate the functionality of your answer to the second SQL question.
C. Leadership or Situational Questions:
(Based on the leadership principle of Bias for Action)
- Describe a situation where you had to make a quick decision with limited information. How did you proceed, and what was the outcome?
(Based on the leadership principle of Dive Deep)
- Can you share an example of a project where you had to delve deeply into the data to uncover insights or solve a problem? What steps did you take, and what were the results?
(Based on the leadership principle of Customer Obsession)
- Tell us about a time when you went above and beyond to meet a customer's needs or expectations. How did you identify their requirements, and what actions did you take to deliver exceptional service?
D. Excel:
Questions on advanced functions like VLOOKUP, XLookup, SUMPRODUCT, INDIRECT, TEXT functions, SUMIFS, COUNTIFS, LOOKUPS, INDEX & MATCH, AVERAGEIFS. Plus, some basic questions on pivot tables, conditional formatting, data validation, and charts.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Like if it helps :)
A. SQL:
1. You have two tables: Employee and Department.
- Employee Table Columns: Employee_id, Employee_Name, Department_id, Salary
- Department Table Columns: Department_id, Department_Name, Location
Write an SQL query to find the name of the employee with the highest salary in each location.
2. You have two tables: Orders and Customers.
- Orders Table Columns: Order_id, Customer_id, Order_Date, Amount
- Customers Table Columns: Customer_id, Customer_Name, Join_Date
Write an SQL query to calculate the total order amount for each customer who joined in the current year. The output should contain Customer_Name and the total amount.
B. Python:
1. Basic oral questions on NumPy (e.g., array creation, slicing, broadcasting) and Matplotlib (e.g., plot types, customization).
2. Basic oral questions on pandas (like: groupby, loc/iloc, merge & join, etc.)
2. Write the code in NumPy and Pandas to replicate the functionality of your answer to the second SQL question.
C. Leadership or Situational Questions:
(Based on the leadership principle of Bias for Action)
- Describe a situation where you had to make a quick decision with limited information. How did you proceed, and what was the outcome?
(Based on the leadership principle of Dive Deep)
- Can you share an example of a project where you had to delve deeply into the data to uncover insights or solve a problem? What steps did you take, and what were the results?
(Based on the leadership principle of Customer Obsession)
- Tell us about a time when you went above and beyond to meet a customer's needs or expectations. How did you identify their requirements, and what actions did you take to deliver exceptional service?
D. Excel:
Questions on advanced functions like VLOOKUP, XLookup, SUMPRODUCT, INDIRECT, TEXT functions, SUMIFS, COUNTIFS, LOOKUPS, INDEX & MATCH, AVERAGEIFS. Plus, some basic questions on pivot tables, conditional formatting, data validation, and charts.
I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02
Like if it helps :)
❤11👍2