Do this to become an advanced excel user
• Auto Spell - F7
• Flash Fill - CTRL + E
• Pivot Table - Alt + N + V
• Autofit All Columns - Alt + H + O + I
• Conditional Formatting - Alt + H + L
These shortcuts will come in handy.
#excel
• Auto Spell - F7
• Flash Fill - CTRL + E
• Pivot Table - Alt + N + V
• Autofit All Columns - Alt + H + O + I
• Conditional Formatting - Alt + H + L
These shortcuts will come in handy.
#excel
❤8👍1
7 Lookup functions in Excel 🔍
A lookup function allows you to get an exact value from a range, based off of inputs.
This is one of the most popular function types in excel, and there are many different methods available…
Let’s explore the 7 most common:
1️⃣ =VLOOKUP
Everyone has heard of a VLOOKUP function…
and many are still using it…even though there are much better functions available
This works by taking a range of data, and finding your lookup value by analyzing a VERTICAL range
I’m not a fan of VLOOKUP because you can only search in one direction (vertically), and need to hardcode in the column index…but it’s still a popular one.
2️⃣ =HLOOKUP
This is pretty much the same as VLOOKUP, only you can now search HORIZONTALLY.
just like VLOOKUP, I feel that HLOOKUP is unnecessary, and limited in it’s functionality
3️⃣ =XLOOKUP
Over the last few years Microsoft released XLOOKUP - your solution to all of your woes with VLOOKUP and HLOOKUP.
Now, instead of being bound to just one direction, you can specify a range on both an X and Y axis to find your lookup value.
XLOOKUP is the new kid on the block and is highly encouraged to use instead of VLOOKUP and HLOOKUP…
just need to watch out for those who are still using an older version of Microsoft Excel, as you’ll run into a compatibility issue (since it’s a new function)
4️⃣ =GETPIVOTDATA
Many aren’t familiar with this one…but it’s super useful.
Here, you can specify the inputs to dynamically pull out your value from a Pivot Table.
If you’re unsure of how to use this, just point your = sign to a cell in a Pivot Table, and excel will automatically populate your formula
5️⃣ =INDEX
The INDEX function is one of my favorite…
and it’s really simple to understand.
Here you can point to a range with both an X and Y axis…
and simply input the coordinates for what value you want to find (ex: 3rd row, 2nd column)
6️⃣ =MATCH
The MATCH function is also a really good one…
it allows you to get the POSITION of a value in a range, as compared to the position of another value in a range.
I love using this one when getting the difference in dates from one period to another.
7️⃣ =INDEX/MATCH
OK…here’s my favorite method for looking up a value.
It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs.
Many feel that XLOOKUP is superior, and I won’t fight it - it’s all a matter of personal preference!
===
Whatever function you use for looking up a value, keep in mind the following:
→ use a function that allows you to search both an X and Y axis, instead of just one direction
→ set things up so that your inputs are dynamic, and can change, yielding different outputs
→ keep your syntax clean & easy to understand
Those are my tips for lookup functions, and 7 ones to use.
A lookup function allows you to get an exact value from a range, based off of inputs.
This is one of the most popular function types in excel, and there are many different methods available…
Let’s explore the 7 most common:
1️⃣ =VLOOKUP
Everyone has heard of a VLOOKUP function…
and many are still using it…even though there are much better functions available
This works by taking a range of data, and finding your lookup value by analyzing a VERTICAL range
I’m not a fan of VLOOKUP because you can only search in one direction (vertically), and need to hardcode in the column index…but it’s still a popular one.
2️⃣ =HLOOKUP
This is pretty much the same as VLOOKUP, only you can now search HORIZONTALLY.
just like VLOOKUP, I feel that HLOOKUP is unnecessary, and limited in it’s functionality
3️⃣ =XLOOKUP
Over the last few years Microsoft released XLOOKUP - your solution to all of your woes with VLOOKUP and HLOOKUP.
Now, instead of being bound to just one direction, you can specify a range on both an X and Y axis to find your lookup value.
XLOOKUP is the new kid on the block and is highly encouraged to use instead of VLOOKUP and HLOOKUP…
just need to watch out for those who are still using an older version of Microsoft Excel, as you’ll run into a compatibility issue (since it’s a new function)
4️⃣ =GETPIVOTDATA
Many aren’t familiar with this one…but it’s super useful.
Here, you can specify the inputs to dynamically pull out your value from a Pivot Table.
If you’re unsure of how to use this, just point your = sign to a cell in a Pivot Table, and excel will automatically populate your formula
5️⃣ =INDEX
The INDEX function is one of my favorite…
and it’s really simple to understand.
Here you can point to a range with both an X and Y axis…
and simply input the coordinates for what value you want to find (ex: 3rd row, 2nd column)
6️⃣ =MATCH
The MATCH function is also a really good one…
it allows you to get the POSITION of a value in a range, as compared to the position of another value in a range.
I love using this one when getting the difference in dates from one period to another.
7️⃣ =INDEX/MATCH
OK…here’s my favorite method for looking up a value.
It in essence combines both the Index and the Match value, allowing you to have complete flexibility over your range with dynamic inputs.
Many feel that XLOOKUP is superior, and I won’t fight it - it’s all a matter of personal preference!
===
Whatever function you use for looking up a value, keep in mind the following:
→ use a function that allows you to search both an X and Y axis, instead of just one direction
→ set things up so that your inputs are dynamic, and can change, yielding different outputs
→ keep your syntax clean & easy to understand
Those are my tips for lookup functions, and 7 ones to use.
👍13❤3😁2
Learning Excel for data analytics can be a valuable skill. Here are some steps you can take to learn Excel topics for data analytics:
1. Take an online course: There are many online courses available that specifically focus on Excel for data analytics. Look for courses on platforms like Coursera, Udemy, or LinkedIn Learning.
2. Practice with datasets: The best way to learn Excel is by practicing with real-world datasets. You can find datasets online on websites like Kaggle or data.gov. Practice manipulating and analyzing the data using Excel functions and tools.
3. Learn important functions: Familiarize yourself with important Excel functions for data analysis such as VLOOKUP, INDEX-MATCH, SUMIFS, AVERAGEIFS, COUNTIFS, and PivotTables.
4. Master data visualization: Excel offers powerful tools for data visualization such as charts and graphs. Learn how to create visually appealing and informative charts to present your data effectively.
5. Explore advanced features: Excel has many advanced features that can be useful for data analytics, such as Power Query, Power Pivot, and macros. Take the time to explore these features and understand how they can enhance your data analysis capabilities.
6. Join online communities: Join online forums and communities dedicated to Excel and data analytics. This can be a great way to ask questions, share knowledge, and learn from others who are also interested in data analytics.
7. Practice regularly: Like any skill, learning Excel for data analytics requires regular practice. Set aside time each week to practice your Excel skills and work on different data analysis projects.
Join for more excel resources: https://t.iss.one/excel_data
1. Take an online course: There are many online courses available that specifically focus on Excel for data analytics. Look for courses on platforms like Coursera, Udemy, or LinkedIn Learning.
2. Practice with datasets: The best way to learn Excel is by practicing with real-world datasets. You can find datasets online on websites like Kaggle or data.gov. Practice manipulating and analyzing the data using Excel functions and tools.
3. Learn important functions: Familiarize yourself with important Excel functions for data analysis such as VLOOKUP, INDEX-MATCH, SUMIFS, AVERAGEIFS, COUNTIFS, and PivotTables.
4. Master data visualization: Excel offers powerful tools for data visualization such as charts and graphs. Learn how to create visually appealing and informative charts to present your data effectively.
5. Explore advanced features: Excel has many advanced features that can be useful for data analytics, such as Power Query, Power Pivot, and macros. Take the time to explore these features and understand how they can enhance your data analysis capabilities.
6. Join online communities: Join online forums and communities dedicated to Excel and data analytics. This can be a great way to ask questions, share knowledge, and learn from others who are also interested in data analytics.
7. Practice regularly: Like any skill, learning Excel for data analytics requires regular practice. Set aside time each week to practice your Excel skills and work on different data analysis projects.
Join for more excel resources: https://t.iss.one/excel_data
❤5👍5
Here are some Excel shortcuts that are commonly used by data analysts:
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i
👍10❤1
Why is Excel Often the Starting Point for SQL ?
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
👍10❤2
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 ❤️
Hope this helps you 😊
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 ❤️
Hope this helps you 😊
👍11❤2
Data Analyst Interview Questions
[Python, SQL, PowerBI]
1. Is indentation required in python?
Ans: Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.
2. What are Entities and Relationships?
Ans:
Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.
Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.
3. What are Aggregate and Scalar functions?
Ans: An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. A scalar function returns a single value based on the input value.
4. What are Custom Visuals in Power BI?
Ans: Custom Visuals are like any other visualizations, generated using Power BI. The only difference is that it develops the custom visuals using a custom SDK. The languages like JQuery and JavaScript are used to create custom visuals in Power BI
ENJOY LEARNING 👍👍
[Python, SQL, PowerBI]
1. Is indentation required in python?
Ans: Indentation is necessary for Python. It specifies a block of code. All code within loops, classes, functions, etc is specified within an indented block. It is usually done using four space characters. If your code is not indented necessarily, it will not execute accurately and will throw errors as well.
2. What are Entities and Relationships?
Ans:
Entity: An entity can be a real-world object that can be easily identifiable. For example, in a college database, students, professors, workers, departments, and projects can be referred to as entities.
Relationships: Relations or links between entities that have something to do with each other. For example – The employee’s table in a company’s database can be associated with the salary table in the same database.
3. What are Aggregate and Scalar functions?
Ans: An aggregate function performs operations on a collection of values to return a single scalar value. Aggregate functions are often used with the GROUP BY and HAVING clauses of the SELECT statement. A scalar function returns a single value based on the input value.
4. What are Custom Visuals in Power BI?
Ans: Custom Visuals are like any other visualizations, generated using Power BI. The only difference is that it develops the custom visuals using a custom SDK. The languages like JQuery and JavaScript are used to create custom visuals in Power BI
ENJOY LEARNING 👍👍
👍7❤4
Essentials of MS Excel to learn for a Data Analytics role:
Data Management & Cleaning
- Removing Duplicates: Eliminate repeated entries to ensure data accuracy.
- Text to Columns: Split text into multiple columns for better organization.
- Data Validation: Set rules to maintain data integrity.
- Flash Fill: Automatically fill data based on patterns.
Formula Mastery
- SUMIFS, COUNTIFS, AVERAGEIFS: Aggregate data with multiple criteria.
- VLOOKUP, HLOOKUP: Search for data vertically or horizontally.
- INDEX, MATCH, INDEX & MATCH: Combine functions for powerful lookups.
- IF, AND, OR, NOT: Perform logical operations.
- Nested Functions: Use multiple functions within one another.
- Array Formulas: Handle multiple values at once.
- XLOOKUP, LET: Modern functions for efficient lookups and variable definitions.
- SUMPRODUCT, INDIRECT: Advanced functions for complex calculations.
- CHOOSE, OFFSET: Select data and reference ranges dynamically.
- LEFT, RIGHT: Extract specific characters from a string.
Data Analysis & Reporting
- Pivot Tables & Pivot Charts: Summarize and visualize large datasets.
- Data Sorting and Filtering: Organize and find data efficiently.
- Subtotals: Calculate subtotals within your data sets.
- Data Tables, Scenarios (What-If Analysis): Explore different data scenarios.
- Goal Seek and Solver: Find optimal solutions for your data problems.
Visualization Expertise
- Conditional Formatting: Highlight data based on conditions.
- Basic to Advanced Charting: Create various types of charts.
- Creating Dynamic Dashboards: Build interactive and real-time dashboards.
- Sparklines: Embed mini-charts within cells for quick insights.
Efficiency Enhancers
- Keyboard Shortcuts: Speed up your workflow.
- Basic Macros and VBA (Optional): Automate repetitive tasks.
- Data Consolidation Techniques: Combine data from different sources.
- Error Checking and Auditing Tools: Ensure data accuracy and troubleshoot issues.
Advanced Excel Capabilities
Power Query for Data Transformation: Clean and transform data efficiently.
Data Model & Power Pivot: Handle complex data relationships.
Advanced Filter: Perform advanced data filtering.
Slicers and Timelines in Pivot Tables: Enhance pivot table interactivity.
I have curated top-notch Excel Resources 👇👇
https://t.iss.one/excel_data
Data Management & Cleaning
- Removing Duplicates: Eliminate repeated entries to ensure data accuracy.
- Text to Columns: Split text into multiple columns for better organization.
- Data Validation: Set rules to maintain data integrity.
- Flash Fill: Automatically fill data based on patterns.
Formula Mastery
- SUMIFS, COUNTIFS, AVERAGEIFS: Aggregate data with multiple criteria.
- VLOOKUP, HLOOKUP: Search for data vertically or horizontally.
- INDEX, MATCH, INDEX & MATCH: Combine functions for powerful lookups.
- IF, AND, OR, NOT: Perform logical operations.
- Nested Functions: Use multiple functions within one another.
- Array Formulas: Handle multiple values at once.
- XLOOKUP, LET: Modern functions for efficient lookups and variable definitions.
- SUMPRODUCT, INDIRECT: Advanced functions for complex calculations.
- CHOOSE, OFFSET: Select data and reference ranges dynamically.
- LEFT, RIGHT: Extract specific characters from a string.
Data Analysis & Reporting
- Pivot Tables & Pivot Charts: Summarize and visualize large datasets.
- Data Sorting and Filtering: Organize and find data efficiently.
- Subtotals: Calculate subtotals within your data sets.
- Data Tables, Scenarios (What-If Analysis): Explore different data scenarios.
- Goal Seek and Solver: Find optimal solutions for your data problems.
Visualization Expertise
- Conditional Formatting: Highlight data based on conditions.
- Basic to Advanced Charting: Create various types of charts.
- Creating Dynamic Dashboards: Build interactive and real-time dashboards.
- Sparklines: Embed mini-charts within cells for quick insights.
Efficiency Enhancers
- Keyboard Shortcuts: Speed up your workflow.
- Basic Macros and VBA (Optional): Automate repetitive tasks.
- Data Consolidation Techniques: Combine data from different sources.
- Error Checking and Auditing Tools: Ensure data accuracy and troubleshoot issues.
Advanced Excel Capabilities
Power Query for Data Transformation: Clean and transform data efficiently.
Data Model & Power Pivot: Handle complex data relationships.
Advanced Filter: Perform advanced data filtering.
Slicers and Timelines in Pivot Tables: Enhance pivot table interactivity.
I have curated top-notch Excel Resources 👇👇
https://t.iss.one/excel_data
👍6🔥2
Last month, 12 candidates were placed at Galytix and now AccioJob is back with another walk-in drive with Galytix! ✨
💼 Position: Data Analyst Intern
💰 CTC: INR 20k/month for 6 months and then 5-6 LPA
📍 Work Location: Remote or Gurgaon (Based on discussion with company)
Don't miss out! 🚀
👉🏻 For more details, eligibility, apply now: https://links.acciojob.com/4gIw4mh
💼 Position: Data Analyst Intern
💰 CTC: INR 20k/month for 6 months and then 5-6 LPA
📍 Work Location: Remote or Gurgaon (Based on discussion with company)
Don't miss out! 🚀
👉🏻 For more details, eligibility, apply now: https://links.acciojob.com/4gIw4mh
Acciojob
AccioJob's Placement Portal - Access 60+ Monthly Offline Job Drives
Access 60+ offline job drives monthly via AccioJob's Placement Portal & crack Full Stack Development, Data Analytics and Data Science roles.
👍6
📌10 intermediate-level SQL interview questions
1. How would you find the nth highest salary in a table?
2. What is the difference between JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
3. How would you calculate cumulative sum in SQL?
4. How do you identify duplicate records in a table?
5. Explain the concept of a window function and give examples.
6. How would you retrieve records between two dates in SQL?
7. What is the difference between UNION and UNION ALL?
8. How can you pivot data in SQL?
9. Explain the use of CASE statements in SQL.
10. How do you use common table expressions (CTEs)?
#sql
1. How would you find the nth highest salary in a table?
2. What is the difference between JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN?
3. How would you calculate cumulative sum in SQL?
4. How do you identify duplicate records in a table?
5. Explain the concept of a window function and give examples.
6. How would you retrieve records between two dates in SQL?
7. What is the difference between UNION and UNION ALL?
8. How can you pivot data in SQL?
9. Explain the use of CASE statements in SQL.
10. How do you use common table expressions (CTEs)?
#sql
👍2❤1
Here are some Excel shortcuts that are commonly used by data analysts:
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel
1. Ctrl + C: Copy
2. Ctrl + V: Paste
3. Ctrl + X: Cut
4. Ctrl + Z: Undo
5. Ctrl + Y: Redo
6. Ctrl + S: Save
7. Ctrl + F: Find
8. Ctrl + H: Replace
9. Ctrl + Arrow Keys: Navigate to the edge of data
10. Ctrl + Shift + Arrow Keys: Select data range
11. Ctrl + Home: Go to cell A1
12. Ctrl + End: Go to last cell with data
13. Ctrl + Shift + L: Toggle filters
14. Alt + ; : Select visible cells only
15. F2: Edit active cell
16. Ctrl + Shift + Enter: Enter an array formula
17. Ctrl + D: Fill down
18. Ctrl + R: Fill right
19. Ctrl + 1: Format cells dialog box
20. Ctrl + Shift + 1, 2, 3, etc.: Apply different number formats
These shortcuts can significantly increase your efficiency when working with Excel as a data analyst.
One of the very important tool that a data analyst must be aware of is Excel
👍13❤2
Why is Excel Often the Starting Point for SQL ?
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
Here's how Excel can help you before you dive into SQL:
✔️ 𝐕𝐋𝐎𝐎𝐊𝐔𝐏 = 𝐒𝐐𝐋 𝐉𝐎𝐈𝐍𝐒
In Excel, we use VLOOKUP to bring together data from different sheets. It's just like using JOINS in SQL to get data from more than one table.
✔️ 𝐒𝐔𝐌 𝐚𝐧𝐝 𝐂𝐎𝐔𝐍𝐓 𝐟𝐨𝐫 𝐒𝐐𝐋 𝐐𝐮𝐞𝐫𝐢𝐞𝐬
Excel's SUM and COUNT functions are like practice for SQL queries. They help you add up and count things, which is what you often do in SQL.
✔️ 𝐅𝐈𝐋𝐓𝐄𝐑 𝐒𝐭𝐚𝐭𝐞𝐦𝐞𝐧𝐭𝐬 & 𝐖𝐇𝐄𝐑𝐄 𝐢𝐧 𝐒𝐐𝐋
Excel's 𝐅𝐈𝐋𝐓𝐄𝐑 statements let you make choices with your data. This is similar to using WHERE in SQL to pick specific data.
✔️ 𝐇𝐚𝐧𝐝𝐥𝐢𝐧𝐠 𝐃𝐚𝐭𝐞𝐬 𝐚𝐧𝐝 𝐓𝐞𝐱𝐭
Both Excel and SQL have ways to work with dates and text. Learning these in Excel first can make it easier when you switch to SQL.
✔️ 𝐏𝐢𝐯𝐨𝐭 𝐓𝐚𝐛𝐥𝐞𝐬 & 𝐆𝐑𝐎𝐔𝐏 𝐁𝐘 𝐢𝐧 𝐒𝐐𝐋
Ever used pivot tables in Excel? They're a good start for understanding the GROUP BY function in SQL, which helps you organize and summarize data.
✔️ 𝐗𝐋𝐎𝐎𝐊𝐔𝐏 & 𝐇𝐲𝐩𝐞𝐫𝐥𝐢𝐧𝐤𝐬
Excel's XLOOKUP and hyperlinks are like SQL's ways of finding and linking data. They give you a peek into how SQL finds and connects information.
Learning Excel first makes SQL easier to understand. It's not just about learning a tool, it's about getting ready for the bigger world of data!
You will be asked questions on SQL in interviews for sure! Make sure to practice 2-3 questions daily, it can't be mastered overnight!
Share our channel link with your true friends: https://t.iss.one/excel_analyst
Hope this helps you 😊
👍10