Top Data Visualization Tools

Excel Cheat Sheet for Aspiring Data Analysts 📊

As an aspiring data analyst, mastering Excel is like having a superpower!

Here’s a cheat sheet to get you started:

✔️ VLOOKUP & HLOOKUP: Quickly find and retrieve data across your spreadsheets.

✔️ Pivot Tables: Summarize and analyze large data sets with ease.

✔️ IF Statements: Make decisions in your sheets with logical tests

✔️ INDEX & MATCH: More flexible data lookup compared to VLOOKUP.

✔️ Conditional Formatting: Highlight important data trends and outliers.

✔️ Data Validation: Control the type of data entered into your sheets.

✔️ SUMIF & COUNTIF: Add and count cells that meet specific criteria.

✔️ Macros: Automate repetitive tasks to save time.

With these tools in your arsenal, you’ll be able to turn raw data into actionable insights effortlessly.

Join for more Resources : https://t.iss.one/excel_data

Hope this helps you 😊

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 😊

AI in Excel 😂

💥TOOL TIPS:

HOW TO,

A. To insert multiple columns:

1. Select a number of column headers equal to the number of columns you want to insert.

2. Right-click any selected column header, and then click Insert.

B. To insert a row

1. Right-click a row header, and then click Insert.

C. To insert multiple rows

1. Select a number of row headers equal to the number of rows you want to insert.
2. Right-click any selected row header, and then click Insert.

D. To delete one or more columns

1. Select the column headers of the columns you want to delete.
2. Right-click any selected column header, and then click Delete.

E. To delete one or more rows

1. Select the row headers of the rows you want to delete.
2. Right-click any selected row header, and then click Delete.

F. To hide one or more columns

1. Select the column headers of the columns you want to hide
2. Right-click any selected column header, and then click Hide.

G. To hide one or more rows

1. Select the row headers of the rows you want to hide.
2. Right-click any selected row header, and then click Hide.

H. To unhide one or more columns

1. Select the column headers to the immediate left and right of the column or columns you want to unhide.
2. Right-click any selected column header, and then click Unhide.

Alternatively,
1. Press Ctrl+A to select the entire worksheet.
2. Right-click anywhere in the worksheet, and then click Unhide.

I. To unhide one or more rows

1. Select the row headers immediately above and below the row or rows you want to unhide.
2. Right-click any selected column header, and then click Unhide.

Alternatively,
1. Press Ctrl + A to select the entire worksheet
2. Right-click anywhere in the worksheet, and then click Unhide

Useful Excel Shortcuts:

● Ctrl + A: Select all contents of the page.
● Ctrl + B: Bold highlighted selection.
● Ctrl + C: Copy selected text.
● Ctrl + X: Cut selected text.
● Ctrl + P: Open the print window.
● Ctrl + F: Open find box.
●Ctrl + I: Italic highlighted selection.
● Ctrl + K: Insert link (Shortcut Keys and their Functions).
● Ctrl + U: Underline highlighted selection.
● Ctrl + V: Paste.
● Ctrl + Y: Redo the last action performed.
● Ctrl + Z: Undo last action.
● Ctrl + L: Aligns the line or selected text to the left of the screen.
● Ctrl + E: Aligns the line or selected text to the center of the screen.
● Ctrl + R: Aligns the line or selected text to the right of the screen.
● Ctrl + M: Indent the paragraph.
● Ctrl + Shift + F: Change the font.
● Ctrl + Shift + >: Increase selected font +1pts up to 12pt and then increases font +2pts.
● Ctrl + ]: Increase selected font +1pts.
● Ctrl + Shift + <: Decrease selected font -1pts if 12pt or lower, if above 12 decreases font by +2pt.
● Ctrl + [: Decrease selected font -1pts.
● Ctrl + Shift + *: View or hide non printing characters.
● Ctrl + L: Moves one word to the left.
● Ctrl + R: Moves one word to the right.
● Ctrl + : Moves to the beginning of the line or paragraph.
● Ctrl + : Moves to the end of the paragraph.
● Ctrl + Del: Deletes word to right of cursor.
● Ctrl + Backspace: Deletes word to left of cursor.
● Ctrl + End: Moves the cursor to the end of the
document.
● Ctrl + Home: Moves the cursor to the beginning of the document.
● Ctrl + Spacebar: Reset highlighted text to the default font.
● Ctrl + 1: Single-space lines.
● Ctrl + 2: Double-space lines.
● Ctrl + 5: 1.5-line spacing.
● Ctrl + Alt + 1: Changes text to heading 1.
● Ctrl + Alt + 2: Changes text to heading 2.
● Ctrl + Alt + 3: Changes text to heading 3.
● Alt + Shift + D: Insert the current date.
● Alt + Shift + T: Insert the current time.

◉ Alt + F: File menu options in current program.
◉ Alt + E: Edit options in current program.
◉ F1: Universal Help in almost every Windows program.
◉ Ctrl + A: Select all text.
◉ Ctrl + X: Cut
◉ Shift + Del: Cut selected item.
◉ Ctrl + C: Copy selected item.
◉ Ctrl + Ins: Copy selected item.
◉ Ctrl + V: Paste selected item.
◉ Shift + Ins: Paste.
◉ Home: Goes to beginning of current line.
◉ Ctrl + Home: Goes to beginning of document.
◉ End: Goes to end of current line.
◉ Ctrl + End: Goes to end of document.
◉ Shift + Home: Highlights from current position to beginning of line.
◉ Shift + End: Highlights from current position to end of line.
◉ Ctrl + Left arrow: Moves one word to the left at a time.
◉ Ctrl + Right arrow: Moves one word to the right at a time.

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 👍👍

2 VERY IMPORTANT MISAKES to avoid for job seekers
Trying or struggling to get Interview Calls

Let me summarise.

Many job applicants for analytics roles (also applicable for other roles) often get frustrated with receiving no interview calls DESPITE putting a lot of good projects, certifications and even their prior experience.

There are probably 2 key yet common mistakes you could be making during your application:

𝟏. 𝐘𝐨𝐮𝐫 𝐑𝐞𝐬𝐮𝐦𝐞 𝐈𝐬𝐧'𝐭 𝐓𝐚𝐢𝐥𝐨𝐫𝐞𝐝 𝐅𝐨𝐫 𝐓𝐡𝐞 𝐑𝐨𝐥𝐞
- Companies use an ATS to scan for relevant profiles amongst 100 of applications based on finding relevant key words.
- Ensure you update your resume to include the skills they're looking for.
- This will increase the chance of the ATS picking up on your resume.

𝟐. 𝐁𝐮𝐢𝐥𝐝 𝐘𝐨𝐮𝐫 𝐋𝐢𝐧𝐤𝐞𝐝𝐈𝐧 𝐏𝐫𝐨𝐟𝐢𝐥𝐞 & 𝐀𝐜𝐭𝐢𝐯𝐢𝐭𝐲- - - - - If your resume reaches the technical/hiring team - they'll want to get more information about you.
- Their Next Stop - YOUR LINKEDIN PROFILE
- Update your certifications/skills & upload your key projects.
- Be Active and Share Your Learnings.
- This builds your credibility in their eyes

Remember....
You're competing against large pool of equally or more talented individuals like yourself.

On A Technical And Accomplishment level, you might on par with others.

Then it goes down to who can stand out from the rest.

Luck can play a huge role, but so can being strategic in your application.

Leave no stone unturned.

Join our WhatsApp channel: https://whatsapp.com/channel/0029VaI5CV93AzNUiZ5Tt226

Dear Data Analyst:

If you are learning Excel

Use this:

Excel Roadmap in 2025 ✅

Week 1: Basic formulas are your foundation
🔸 SUM, AVERAGE, COUNT mastery
🔸 Keyboard shortcuts that save 2hrs/day
🔸 Professional formatting techniques
🔸 Navigation speed tricks

Week 2: VLOOKUP revolution
🔸 Automate 80% of manual work
🔸 Dynamic reporting basics
🔸 Pivot Tables in 60 minutes
🔸 First team recognition

Week 3: Advanced formulas
🔸 INDEX/MATCH mastery
🔸 Dashboard creation
🔸 Power Query foundations
🔸 Template building

Week 4: Career-changing skills
🔸 Automated reporting
🔸 KPI tracking systems
🔸 VBA automation basics
🔸 Portfolio projects

For data analysts, mastering these top 10 Excel concepts is crucial:

1. Formulas and Functions: Understand basic to advanced functions like SUM, AVERAGE, VLOOKUP, INDEX-MATCH, and IF statements.

2. PivotTables: Learn to summarize, analyze, and visualize data efficiently using PivotTables.

3. Data Cleaning and Formatting: Familiarize yourself with tools and techniques for cleaning and formatting messy data, such as text-to-columns, remove duplicates, and conditional formatting.

4. Charts and Graphs: Explore various chart types (e.g., bar, line, scatter) and understand when to use each for effective data visualization.

5. Data Validation: Implement data validation rules to ensure data integrity and accuracy, such as drop-down lists and input restrictions.

6. Data Analysis Tools: Utilize Excel's built-in data analysis tools like Goal Seek, Solver, and Data Tables for scenario analysis and optimization.

7. Conditional Formatting: Apply formatting based on specific conditions to highlight trends, outliers, or anomalies in data.

8. Named Ranges: Organize data efficiently by assigning meaningful names to ranges, making formulas more readable and easier to manage.

9. Data Tables and What-If Analysis: Use data tables to perform sensitivity analysis and scenario modeling for decision-making.

10. Power Query and Power Pivot: Explore advanced data manipulation and analysis capabilities using Excel's Power Query for data extraction, transformation, and loading (ETL) and Power Pivot for data modeling and analysis.

Free Excel Resources: https://whatsapp.com/channel/0029VaifY548qIzv0u1AHz3i

ENJOY LEARNING 👍👍

You don't need to know all of Excel to become a data analyst

You don't even need to know 95% of it

Focus on these:

1. SUMIFS
2. XLOOKUP
3. COUNTIFS
4. UNIQUE
5. PIVOT TABLES

A mix of these helped me get through every Excel technical I have had

Learn the basics
→ So you understand what you are capable of

KNOW the above 5 by heart

This way you can put more energy into understanding other tools such as SQL or Tableau/Power BI

🗂How to create Formulas To Calculate Values

Entering the cell references for 15 or 20 cells in a calculation would be tedious, but in Excel you can easily enter complex calculations by using the Insert Function dialog box.

The Insert Function dialog box includes a list of functions, or predefined formulas, from which you can choose.

-Average = finds the average of the numbers in the specified cells

-Sum = finds the total/sum of the numbers in the specified cells

-Count = finds the number of entities in the specified cells

-Max = finds the largest value in the specified cells

-Min = finds the smallest values in the specified cells

🗂Find and replace data

Excel worksheets can hold more than one million rows of data.

So in large data collections it’s unlikely that you would have the time to move through a worksheet one row at a time to locate the data you want to find.

You can locate specific data in an Excel worksheet by using the Find And Replace dialog box, which has two tabs (one named Find, the other named Replace) that you can use to search for cells that contain particular values.

Using the controls on the Find tab identifies cells that contain the data you specify; by using the controls on the Replace tab, you can substitute one value for another.

💥Tools Tip:

To display the Find tab of the Find And Replace dialog box by using a keyboard shortcut, press Ctrl+F.

Press Ctrl+H to display the Replace tab of the Find And Replace dialog box.

🗂 Find And Replace Data Dialog Box Functions & Elemnts

- The above table summarizes the elements of the Find And Replace dialog box.

Excel interview questions for both data analysts and business analysts

1) What are the basic functions of Microsoft Excel?
2) Explain the difference between a workbook and a worksheet.
3) How would you freeze panes in Excel?
4) Can you name some common keyboard shortcuts in Excel?
5) What is the purpose of VLOOKUP and HLOOKUP?
7) How do you remove duplicate values in Excel?
8) Explain the steps to filter data in Excel.
9) What is the significance of the "IF" function in Excel, and can you provide an example of its use?
10) How would you create a pivot table in Excel?
11) Explain the use of the CONCATENATE function in Excel.
12) How do you create a chart in Excel?
13) Explain the difference between a line chart and a scatter plot.
14) What is conditional formatting, and how can it be applied in Excel?
15) How would you create a dynamic chart that updates with new data?
16) What is the INDEX-MATCH function, and how is it different from VLOOKUP?
17) Can you explain the concept of "PivotTables" and when you would use them?
18) How do you use the "COUNTIF" and "SUMIF" functions in Excel?
19) Explain the purpose of the "What-If Analysis" tools in Excel.
20) What are array formulas, and can you provide an example of their use?

Business Analysis Specific:

1) How would you analyze a set of sales data to identify trends and insights?
2) Explain how you might use Excel to perform financial modeling.
3) What Excel features would you use for forecasting and budgeting?
4) How do you handle large datasets in Excel, and what tools or techniques do you use for optimization?
5) What are some common techniques for cleaning and validating data in Excel?
6) How do you identify and handle errors in a dataset using Excel?

Scenario-based Questions:

1) Imagine you have a dataset with missing values. How would you approach this problem in Excel?
2) You are given a dataset with multiple sheets. How would you consolidate the data for analysis?

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

Hope this helps you 😊