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

Here are 7 Excel hacks that may not be commonly known but can be very useful:

1. Flash Fill: Excel's Flash Fill feature allows you to automatically fill in values based on a pattern you establish. Simply start typing a pattern in a column adjacent to the data you want to fill, then press Ctrl + E to apply the Flash Fill feature.

2. Custom Views: You can save different views of your data by using the Custom Views feature. This is useful when you want to quickly switch between different layouts or settings. Go to View > Custom Views to set up and manage custom views.

3. Data Validation Lists: You can create drop-down lists in cells to restrict input options. To do this, select the cells where you want the drop-down list, go to Data > Data Validation, choose "List" as the validation criteria, and enter the list items.

4. Quick Analysis Tool: Excel's Quick Analysis tool provides a quick way to analyze and visualize your data. Simply select a range of data, and a small icon will appear at the bottom right corner. Click on it to access various analysis options.

5. Text to Columns: If you have data that needs to be split into separate columns (e.g., first name and last name), you can use the Text to Columns feature. Go to Data > Text to Columns and follow the instructions to split the data based on delimiters or fixed widths.

6. Conditional Formatting with Formulas: While conditional formatting is commonly used, you can take it a step further by applying formatting based on specific formulas. This allows for more customized formatting rules. Go to Home > Conditional Formatting > New Rule and select "Use a formula to determine which cells to format."

7. Dynamic Arrays: Excel's dynamic array functions (available in newer versions) allow you to work with arrays of data more efficiently. Functions like FILTER, SORT, and UNIQUE can help you manipulate data dynamically without needing to enter array formulas.

These Excel hacks can help you work more effectively with your data and take advantage of some lesser-known features in Excel.

Credits: https://t.iss.one/free4unow_backup

ENJOY LEARNING 👍👍

Struggling with messy data in Excel? Here’s how to clean it up:

If you’re dealing with unorganized data in Excel, here are some quick steps to clean your sheets:

1️⃣ Trim Function: Eliminate leading & trailing spaces easily.
2️⃣ Remove Duplicates: Use the Data tab feature to delete duplicate rows.
3️⃣ Text to Columns: Separate combined information into different cells.
4️⃣ Filter Blank Cells: Quickly find and manage empty cells.
5️⃣ Clean Function: Remove non-printable characters for a tidier dataset.

Like for more ❤️

Excel Basic Concepts asked in Data Analyst Interviews 👇👇

1. Excel Interface and Navigation: Familiarize yourself with Excel’s user interface, including the ribbon, worksheet tabs, and the formula bar. Learn keyboard shortcuts to efficiently navigate and perform tasks within Excel.

2. Data Entry and Formatting: Understand how to enter data, adjust cell formats (e.g., text, numbers, dates), and use formatting options like bold, italics, cell borders, and background colors to enhance readability.

3. Basic Formulas: Learn essential Excel formulas such as:
- SUM(): Adds up a range of numbers.
- AVERAGE(): Calculates the mean of a range.
- MIN() and MAX(): Find the smallest and largest values in a range.
- COUNT() and COUNTA(): Count the number of numeric and non-empty cells in a range.

4. Cell References: Understand the difference between relative, absolute, and mixed cell references (e.g., A1, $A$1, A$1) and how they behave when copying formulas across cells.

5. Conditional Formatting: Learn how to apply conditional formatting to highlight cells that meet certain criteria, such as coloring cells with values above a certain threshold or marking duplicate values.

6. Basic Data Manipulation: Get comfortable with basic data manipulation techniques:
- Sorting: Arrange data in ascending or descending order.
- Filtering: Use AutoFilter to display only the rows that meet certain criteria.
- Find and Replace: Quickly locate and replace text or numbers within a worksheet.

7. Working with Tables: Learn how to convert a range of data into an Excel table, which provides easier sorting, filtering, and formatting options, along with the ability to use structured references in formulas.

8. Basic Charts: Create and customize basic charts (e.g., bar, line, pie charts) to visually represent data. Understand how to add chart titles, labels, and legends to make your charts clear and informative.

9. Basic Text Functions: Use essential text functions to manipulate and clean data:
- CONCATENATE() or TEXTJOIN(): Combine text from multiple cells.
- LEFT(), RIGHT(), MID(): Extract parts of a text string.
- LEN(): Count the number of characters in a cell.
- TRIM(): Remove extra spaces from text.

10. IF Function: Master the IF() function to create simple conditional statements. For example, =IF(A1>100, "High", "Low") assigns "High" if the value in A1 is greater than 100 and "Low" otherwise.

11. Date and Time Functions: Learn how to work with dates and times in Excel:
- TODAY(): Returns the current date.
- NOW(): Returns the current date and time.
- DATEDIF(): Calculates the difference between two dates in days, months, or years.

12. Basic Error Handling: Understand how to handle errors in formulas using functions like IFERROR() to replace errors with a user-friendly message or alternative value.

13. Working with Multiple Sheets: Learn how to reference data across multiple sheets in a workbook, use 3D references, and organize large workbooks with multiple tabs.

14. Basic Data Validation: Implement data validation rules to control what users can enter into a cell, such as restricting input to a list of values or setting a range for numeric entries.

15. Print Settings: Master Excel’s print settings, including setting print areas, adjusting page layout, using headers and footers, and scaling content to fit on a page for better printouts.

16. Basic Lookup Functions: Learn basic lookup functions like VLOOKUP() and HLOOKUP() to search for specific data in a table and return a corresponding value from another column.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://t.iss.one/DataSimplifier

Like this post for more content like this 👍♥️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

50 essential Excel formulas

SUM: =SUM(A1:A5)
AVERAGE: =AVERAGE(A1:A10)
VLOOKUP: =VLOOKUP(B1, A2:D10, 3, FALSE)
IF: =IF(A1 > 10, "Yes", "No")
CONCATENATE (or CONCAT): =CONCATENATE(A1, " ", B1)
COUNT: =COUNT(A1:A10)
MAX: =MAX(A1:A10)
MIN: =MIN(A1:A10)
ROUND: =ROUND(A1, 2)
TRIM: =TRIM(A1)
LOWER: =LOWER(A1)
UPPER: =UPPER(A1)
LEFT: =LEFT(A1, 5)
RIGHT: =RIGHT(A1, 5)
MID: =MID(A1, 2, 3)
LEN: =LEN(A1)
FIND: =FIND("search_text", A1)
REPLACE: =REPLACE(A1, 3, 2, "new_text")
SUBSTITUTE: =SUBSTITUTE(A1, "old_text", "new_text")
INDEX: =INDEX(A1:A10, 3)
MATCH: =MATCH(B1, A1:A10, 0)
OFFSET: =OFFSET(A1, 1, 2)
SUMIF: =SUMIF(A1:A10, ">5")
COUNTIF: =COUNTIF(A1:A10, "apple")
AVERAGEIF: =AVERAGEIF(A1:A10, "<>0")
SUMIFS: =SUMIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
COUNTIFS: =COUNTIFS(A1:A10, ">5", B1:B10, "apple")
AVERAGEIFS: =AVERAGEIFS(A1:A10, B1:B10, "apple", C1:C10, ">5")
IFERROR: =IFERROR(A1/B1, "Error")
AND: =AND(A1>5, A1<10)
OR: =OR(A1="apple", A1="banana")
NOT: =NOT(A1="apple")
DATE: =DATE(2022, 12, 31)
TODAY: =TODAY()
NOW: =NOW()
DATEDIF: =DATEDIF(A1, A2, "D")
YEAR: =YEAR(A1)
MONTH: =MONTH(A1)
DAY: =DAY(A1)
EOMONTH: =EOMONTH(A1, 3)
NETWORKDAYS: =NETWORKDAYS(A1, A2)
WEEKDAY: =WEEKDAY(A1)
HLOOKUP: =HLOOKUP(B1, A1:D10, 3, FALSE)
MATCH: =MATCH(B1, A1:A10, 0)
INDEX-MATCH: =INDEX(A1:A10, MATCH(B1, C1:C10, 0))
TRANSPOSE: =TRANSPOSE(A1:D10)
PIVOT TABLE: =PIVOT_TABLE(A1:D10, "Sales", "Region", "Sum")
RANK: =RANK(A1, A1:A10, 1)
RAND: =RAND()
CHOOSE: =CHOOSE(B1, "Option 1", "Option 2", "Option 3")

Share our channel link with your true friends: https://t.iss.one/excel_analyst

Hope this helps you 😊

Excel Formulas for Accountants ✅

Top Excel Formulas Every Data Analyst Should Know

SUM():

Purpose: Adds up a range of numbers.

Example: =SUM(A1:A10)


AVERAGE():

Purpose: Calculates the average of a range of numbers.

Example: =AVERAGE(B1:B10)


COUNT():

Purpose: Counts the number of cells containing numbers.

Example: =COUNT(C1:C10)


IF():

Purpose: Returns one value if a condition is true, and another if false.

Example: =IF(A1 > 10, "Yes", "No")


VLOOKUP():

Purpose: Searches for a value in the first column and returns a value in the same row from another column.

Example: =VLOOKUP(D1, A1:B10, 2, FALSE)


HLOOKUP():

Purpose: Searches for a value in the first row and returns a value in the same column from another row.

Example: =HLOOKUP("Sales", A1:F5, 3, FALSE)


INDEX():

Purpose: Returns the value of a cell based on row and column numbers.

Example: =INDEX(A1:C10, 2, 3)


MATCH():

Purpose: Searches for a value and returns its position in a range.

Example: =MATCH("Product B", A1:A10, 0)


CONCATENATE() or CONCAT():

Purpose: Joins multiple text strings into one.

Example: =CONCATENATE(A1, " ", B1)


TEXT():

Purpose: Formats numbers or dates as text.

Example: =TEXT(A1, "dd/mm/yyyy")

Excel Resources: t.iss.one/excel_data

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://whatsapp.com/channel/0029VaGgzAk72WTmQFERKh02

Like this post for more content like this 👍♥️

Share with credits: https://t.iss.one/sqlspecialist

Hope it helps :)

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

📊 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 :)