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.me/excel_data

Like this post for more content like this 👍♥️

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)

📘 SQL Challenges for Data Analytics – With Explanation 🧠

(Beginner ➡️ Advanced)

1️⃣ Select Specific Columns

SELECT name, email FROM users;

This fetches only the name and email columns from the users table.

✔️ Used when you don’t want all columns from a table.


2️⃣ Filter Records with WHERE

SELECT * FROM users WHERE age > 30;

The WHERE clause filters rows where age is greater than 30.

✔️ Used for applying conditions on data.


3️⃣ ORDER BY Clause

SELECT * FROM users ORDER BY registered_at DESC;

Sorts all users based on registered_at in descending order.
✔️ Helpful to get latest data first.


4️⃣ Aggregate Functions (COUNT, AVG)

SELECT COUNT(*) AS total_users, AVG(age) AS avg_age FROM users;

Explanation:
- COUNT(*) counts total rows (users).
- AVG(age) calculates the average age.
✔️ Used for quick stats from tables.


5️⃣ GROUP BY Usage

SELECT city, COUNT(*) AS user_count FROM users GROUP BY city;

Groups data by city and counts users in each group.

✔️ Use when you want grouped summaries.


6️⃣ JOIN Tables

SELECT users.name, orders.amount  
FROM users  
JOIN orders ON users.id = orders.user_id;

Fetches user names along with order amounts by joining users and orders on matching IDs.
✔️ Essential when combining data from multiple tables.


7️⃣ Use of HAVING

SELECT city, COUNT(*) AS total  
FROM users  
GROUP BY city  
HAVING COUNT(*) > 5;

Like WHERE, but used with aggregates. This filters cities with more than 5 users.
✔️ **Use HAVING after GROUP BY.**


8️⃣ Subqueries

SELECT * FROM users  
WHERE salary > (SELECT AVG(salary) FROM users);

Finds users whose salary is above the average. The subquery calculates the average salary first.

✔️ Nested queries for dynamic filtering9️⃣ CASE Statementnt**

SELECT name,  
  CASE  
    WHEN age < 18 THEN 'Teen'  
    WHEN age <= 40 THEN 'Adult'  
    ELSE 'Senior'  
  END AS age_group  
FROM users;

Adds a new column that classifies users into categories based on age.
✔️ Powerful for conditional logic.

🔟 Window Functions (Advanced)

SELECT name, city, score,  
  RANK() OVER (PARTITION BY city ORDER BY score DESC) AS rank  
FROM users;

Ranks users by each city.

React ♥️ for more

SQL Joins — A Practical Cheatsheet for Professionals

If you’re working with relational data — whether you’re a business analyst, backend dev, or aspiring data scientist — mastering SQL joins isn’t optional. It’s fundamental.

Here’s a concise guide to the most important join types, with real-world use cases:


INNER JOIN

Returns records with matching keys from both tables.
Use case: Show only customers who’ve placed at least one order.


LEFT JOIN (OUTER)

Returns all rows from the left table, and matched rows from the right.
Use case: List all customers, including those with zero orders.


RIGHT JOIN (OUTER)

Returns all rows from the right table. Rarely used, but powerful.
Use case: Show all orders, even if the customer was deleted.


FULL OUTER JOIN

Returns all records from both tables.
Use case: Capture everything — matched and unmatched.


CROSS JOIN

Returns the cartesian product.
Use case: Generate every possible product/supplier combo.


SELF JOIN

Joins a table to itself.
Use case: Show employees and their reporting managers.


Best Practices

Use aliases (A, B) for clean code
Prefer JOIN ON over WHERE for clarity
Always test joins with LIMIT to prevent overloads

If I had to start learning data analyst all over again, I'd follow this:

1- Learn SQL:

---- Joins (Inner, Left, Full outer and Self)
---- Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)
---- Group by and Having clause
---- CTE and Subquery
---- Windows Function (Rank, Dense Rank, Row number, Lead, Lag etc)

2- Learn Excel:

---- Mathematical (COUNT, SUM, AVG, MIN, MAX, etc)
---- Logical Functions (IF, AND, OR, NOT)
---- Lookup and Reference (VLookup, INDEX, MATCH etc)
---- Pivot Table, Filters, Slicers

3- Learn BI Tools:

---- Data Integration and ETL (Extract, Transform, Load)
---- Report Generation
---- Data Exploration and Ad-hoc Analysis
---- Dashboard Creation

4- Learn Python (Pandas) Optional:

---- Data Structures, Data Cleaning and Preparation
---- Data Manipulation
---- Merging and Joining Data (Merging and joining DataFrames -similar to SQL joins)
---- Data Visualization (Basic plotting using Matplotlib and Seaborn)

Hope this helps you 😊

You're STILL a data analyst even if...

- you only use Excel
- you forgot the SQL syntax
- you bombed the big interview
- you don't know how to program
- you did an analysis completely wrong
- you can't remember the right function name
- you have to Google how to do something easy you've done before

You're NOT a data analyst when...
- you give up

SO DON'T GIVE UP! KEEP GOING!

10 Must-Have Habits for Data Analysts 📊🧠

1️⃣ Develop strong Excel & SQL skills
2️⃣ Master data cleaning — it’s 80% of the job
3️⃣ Always validate your data sources
4️⃣ Visualize data clearly (use Power BI/Tableau)
5️⃣ Ask the right business questions
6️⃣ Stay curious — dig deeper into patterns
7️⃣ Document your analysis & assumptions
8️⃣ Communicate insights, not just numbers
9️⃣ Learn basic Python or R for automation
🔟 Keep learning: analytics is always evolving

💬 Tap ❤️ for more!

15 SQL interview questions for freshers

1) What is SQL and what is it used for?
Answer: SQL is a language for managing and querying relational databases. It’s used to retrieve, insert, update, delete data and to manage schema and permissions.

2) What are the different types of SQL statements?
Answer: DDL (Data Definition Language), DML (Data Manipulation Language), DCL (Data Control Language), and DTL (Transaction Control Language).

3) What is a primary key?
Answer: A unique identifier for each row in a table; cannot be NULL.

4) What is a foreign key?
Answer: A field that creates a link between two tables, enforcing referential integrity.

5) What is the difference between INNER JOIN and LEFT JOIN?
Answer: INNER JOIN returns matching rows from both tables; LEFT JOIN returns all rows from the left table and matched rows from the right table (NULL if no match).

6) What is normalization?
Answer: Organizing data to reduce redundancy by dividing into related tables and defining relationships.

7) What is a database index?
Answer: A data structure that improves the speed of data retrieval; can be on one or more columns.

8) What is GROUP BY and HAVING?
Answer: GROUP BY aggregates rows by column(s); HAVING filters groups after aggregation (unlike WHERE which filters rows before aggregation).

9) What is a subquery?
Answer: A query nested inside another query, used to perform operations that depend on another query’s result.

10) What is a view?
Answer: A saved query that presents data as a virtual table; does not store data itself.

11) What is transaction management?
Answer: Ensuring data integrity using ACID properties; COMMIT to save, ROLLBACK to undo, and SAVEPOINT to set a point to roll back to.

12) What are SQL constraints?
Answer: Rules like PRIMARY KEY, FOREIGN KEY, NOT NULL, UNIQUE, CHECK, and DEFAULT to enforce data integrity.

13) What is the difference between WHERE and HAVING?
Answer: WHERE filters rows before grouping; HAVING filters groups after aggregation.

14) What is a stored procedure?
Answer: A precompiled set of SQL statements stored in the database, can be executed with parameters.

15) What is the difference between UNION and UNION ALL?
Answer: UNION removes duplicates between results; UNION ALL keeps all rows, including duplicates.

💬 React with ❤️ for more! 😊

Hi Everyone!

Just a reminder - Don’t pay a single rupee to anyone asking you for anything during your job search journey.

No company asks for a single rupee neither during an interview process, not even during visa sponsorship.

So don’t pay a single rupee to anyone asking you for any interview or any verification process.

Save yourself from getting scammed!

Save your and your parents hard earned money.

You already have the skills and expertise in Data Analytics tools like SQL, Power BI, Tableau, and Python. 𝐍𝐨𝐰, 𝐡𝐨𝐰 𝐝𝐨 𝐲𝐨𝐮 𝐟𝐢𝐧𝐝 𝐚 𝐣𝐨𝐛?

1. Tailor your LinkedIn profile to highlight your Data Analyst skills and experience.

2. Make a list of companies that hire Data Analysts and follow them on LinkedIn to stay updated on job openings. (Ex- McKinsey & Company, BCG, Bain & Company, Google, Amazon, Microsoft, IBM, Goldman Sachs, JPMorgan Chase, Walmart, Target)

3. Follow HRs from your target companies on LinkedIn and reach out to them for job openings or whenever they post about job openings, send your resume to them within 2-3 hours via LinkedIn or email if available.

4. Connect with Managers or Senior Managers in Data Analyst roles at your target companies on LinkedIn and ask if they are hiring for their team or would be willing to refer you for any relevant Data analyst role.

5. Apply for jobs on LinkedIn, Naukri, and directly on the company's website.

📊 Aggregate Functions (COUNT, SUM, AVG, MIN, MAX)

Aggregate functions are used to perform calculations on multiple rows of a table and return a single value. They're mostly used with GROUP BY, but also work standalone.

1. COUNT()
Returns the number of rows.

Example:

SELECT COUNT(*) FROM employees;

Counts all employees in the table.

You can also count only non-null values in a column:

SELECT COUNT(email) FROM customers;


2. SUM()
Adds up all the values in a numeric column.

Example:

SELECT SUM(salary) FROM employees;

Gives you the total salary payout.


3. AVG()
Calculates the average value of a numeric column.

Example:

SELECT AVG(price) FROM products;

Finds the average product price.


4. MIN()
Returns the lowest value.

Example:

SELECT MIN(salary) FROM employees;

Finds the smallest salary.


5. MAX()
Returns the highest value.

Example:

SELECT MAX(salary) FROM employees;

Finds the highest salary in the table.


Bonus Example:

SELECT
COUNT(*) AS total_orders,
SUM(amount) AS total_revenue,
AVG(amount) AS avg_order_value
FROM orders;

This gives you a quick business summary: number of orders, total revenue, and average order value.


React with ❤️ for more.

Share with credits: https://news.1rj.ru/str/sqlspecialist

Hope it helps :)