Excel for Data Analysis

Roadmap to learn EXCEL

Step 1 - Master Basic Formulas
Step 2 - Data Visualization
Step 3 - Pivot Tables and Analysis
Step 4 - Advanced Functions
Step 5 - Automation with Macros
Step 6 - Power Query and Power Pivot
Step 7 - Collaboration and Sharing
Step 8 - Excel Tips and Tricks

.....read more

#excel

📌Save Your time with Short Cuts

Ctrl + A - Select All
Ctrl + B - Bold
Ctrl + C - Copy
Ctrl + D - Fill
Ctrl + F - Find
Ctrl + G - Find next instance of text
Ctrl + H - Replace
Ctrl + I - Italic
Ctrl + K - Insert a hyperlink
Ctrl + N - New workbook
Ctrl + O - Open
Ctrl + P - Print
Ctrl + R - Nothing right
Ctrl + S - Save
Ctrl + U - Underlined
Ctrl + V - Paste
Ctrl W - Close
Ctrl + X - Cut
Ctrl + Y - Repeat
Ctrl + Z - Cancel
F1 - Help
F2 - Edition
F3 - Paste the name
F4 - Repeat the last action
F5 - Goto
F6 - Next Pane
F7 - Spell Check
F8 - Extension of the mode
F9 - Recalculate all workbooks
F10 - Activate Menubar
F11 - New graph
F12 - Save As
Shift + F1 - What is it?
Shift + F2 - Edit cell comment
Shift + F3 - Paste the function into the formula
Shift + F4 - Search Next
Shift + F5 - Find
Shift + F6 - Previous Panel
Shift + F8 - Add to the selection
Shift + F9 - Calculate the active worksheet
Shift + F10 - Popup menu display
Shift + F11 - New spreadsheet
Shift + F12 - Save
Ctrl + F3 - Set name
Ctrl + F4 - Close
Ctrl + F5 - XL, size of the restore window
Ctrl + F6 - Next Workbook Window
Shift + Ctrl + F6 - Previous Workbook Window
Ctrl + F7 - Move window
Ctrl + F8 - Resize Window
Ctrl + F9 - Minimize the workbook
Ctrl + F10 - Maximize or Restore Window
Ctrl + F11 - Inset 4.0 Macro sheet
Ctrl + F1 - Open File
Alt + F1 - Insert a graph
Alt + F2 - Save As
Alt + F4 - Output
Alt + F8 - Macro dialog
Alt + F11 - Visual Basic Editor
Ctrl + Shift + F3 - Create a name using the names of row and column labels
Ctrl + Shift + F6 - Previous Window
Ctrl + Shift + F12 - Printing
Alt + Shift + F1 - New spreadsheet
Alt + Shift + F2 - Save
Alt + = - AutoSum
Ctrl + `- Toggle value / display of the formula
Ctrl + Shift + A - Insert the argument names in the formula
Alt + down arrow - automatic view list
Alt + '- Format Style Dialog
Ctrl + Shift + ~ - General Format

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

Hope this helps you 😊

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.

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

Hope this helps you 😊

Best Power BI DAX Cheat Sheet
👇👇
https://whatsapp.com/channel/0029Vai1xKf1dAvuk6s1v22c

Top 10 Excel Interview Questions with Answers 😄👇

Free Resources to learn Excel: https://t.me/excel_analyst

1. Question: What is the difference between CONCATENATE and "&" in Excel?

Answer: CONCATENATE and "&" both combine text, but "&" is more concise. For example, =A1&B1 achieves the same result as =CONCATENATE(A1, B1).

2. Question: How can you freeze rows and columns simultaneously in Excel?

Answer: Use the "Freeze Panes" option under the "View" tab. Select the cell below and to the right of the rows and columns you want to freeze, and then click on "Freeze Panes."

3. Question: Explain the VLOOKUP function and when would you use it?

Answer: VLOOKUP searches for a value in the first column of a range and returns a corresponding value in the same row from another column. It's useful for looking up information in a table based on a specific criteria.

4. Question: What is the purpose of the IFERROR function?

Answer: IFERROR is used to handle errors in Excel formulas. It returns a specified value if a formula results in an error, and the actual result if there's no error.

5. Question: How do you create a PivotTable, and what is its purpose?

Answer: To create a PivotTable, select your data, go to the "Insert" tab, and choose "PivotTable." It summarizes and analyzes data in a spreadsheet, allowing you to make sense of large datasets.

6. Question: Explain the difference between relative and absolute cell references.

Answer: Relative references change when you copy a formula to another cell, while absolute references stay fixed. Use a $ symbol to make a reference absolute (e.g., $A$1).

7. Question: What is the purpose of the INDEX and MATCH functions?

Answer: INDEX returns a value in a specified range based on the row and column number, while MATCH searches for a value in a range and returns its relative position. Combined, they provide a flexible way to look up data.

8. Question: How can you find and remove duplicate values in Excel?

Answer: Use the "Remove Duplicates" feature under the "Data" tab. Select the range containing duplicates, go to "Data" -> "Remove Duplicates," and choose the columns to check for duplicates.

9. Question: Explain the difference between a workbook and a worksheet.

Answer: A workbook is the entire Excel file, while a worksheet is a single sheet within that file. Workbooks can contain multiple worksheets.

10. Question: What is the purpose of the COUNTIF function?

Answer: COUNTIF counts the number of cells within a range that meet a specified condition. For example, =COUNTIF(A1:A10, ">50") counts the cells in A1 to A10 that are greater than 50.

Share with credits: https://t.me/sqlspecialist

Hope it helps :)

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 ❤️

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

Hope this helps you 😊

Hi Everyone 👋, I have brought you Great Opportunity Today

𝐋𝐞𝐚𝐫𝐧 𝐂𝐨𝐝𝐢𝐧𝐠 𝐅𝐫𝐨𝐦 𝐒𝐜𝐫𝐚𝐭𝐜𝐡😍 | Get Placed In Top Tech Companies

8 Days Of Demo Classes By Industry Experts 

Enhance your career with industry-relevant skills in:

* Data Analytics/Data Science & AI
* Cybersecurity
* Full Stack Development

Eligibility: BE/BTech / BCA / BSc / MCA

🌟 Trusted by 7000+ Students
🤝 450+ Hiring Partners
💼 Avg. Rs. 7.2 LPA
🚀 41 LPA Highest Package

𝐑𝐞𝐠𝐢𝐬𝐭𝐞𝐫 𝐍𝐨𝐰 👇:-

https://bit.ly/3AQRTRN

🎯 Don’t miss out! Limited seats are available. 🌟

Complete #Excel Topic-Wise Guide to Crack Any Excel Interview for a Data Analyst Role:-

Data Management & Cleaning:-

➡️ Removing Duplicates
➡️ Text to Columns
➡️ Data Validation
➡️ Flash Fill

Formula Proficiency:-

➡️ SUMIFS, COUNTIFS, AVERAGEIFS
➡️ VLOOKUP, HLOOKUP
➡️ INDEX, MATCH, INDEX & MATCH
➡️ IF, AND, OR, NOT
➡️ Nested Functions
➡️ Array Formulas
➡️ XLOOKUP, LET
➡️ SUMPRODUCT, INDIRECT
➡️ CHOOSE, OFFSET

Data Analysis & Reporting:-

➡️ Pivot Tables & Pivot Charts
➡️ Data Sorting and Filtering
➡️ Subtotals
➡️ Data Tables, Scenarios (What-If Analysis)
➡️ Goal Seek and Solver

Visualization Mastery:-

➡️ Conditional Formatting
➡️ Basic to Advanced Charting
➡️ Creating Dynamic Dashboards
➡️ Sparklines

Efficiency Boosters:-

➡️ Keyboard Shortcuts
➡️ Basic Macros and VBA (Optional)
➡️ Data Consolidation Techniques
➡️ Error Checking and Auditing Tools

Advanced Excel Features:-

➡️ Power Query for Data Transformation
➡️ Data Model & Power Pivot
➡️ Advanced Filter
➡️ Slicers and Timelines in Pivot Tables

I have curated best 80+ top-notch Data Analytics Resources 👇👇
https://topmate.io/analyst/861634

Hope this helps you 😊

Tips for working with Microsoft
Word

✅ To quickly insert the current date, press SHIFT + ALT + D, and to insert the time, SHIFT + ALT + T.

✅ If you accidentally typed a text with the CAPS LOCK key enabled, then select the text in uppercase and press SHIFT + F3.

✅ To speed up the cursor movement through the text, hold down CTRL and use the arrows.

✅ By holding CTRL, you can select all the parts of the text you are interested in, even if they are not arranged in a row.

✅ The F4 key repeats the last command used.

✅ Place the cursor at the beginning of the text selection and click at the end of the desired text segment with the SHIFT key held down for quick selection.

✅ Press CTRL + ENTER to quickly create a new sheet.

#lifehack #windows

5️⃣ Pivot Tables:

Pivot Tables aren’t a function but are an essential Excel tool for data analysts. They enable quick summarization, aggregation, and exploration of large datasets, allowing analysts to generate insights without complex formulas.

Most Used Excel Functions

4️⃣ COUNTIF / COUNTIFS:

COUNTIF and COUNTIFS are similar to SUMIF but are used for counting cells that meet specific criteria. These functions are helpful for calculating frequencies, such as counting occurrences of a certain value in a dataset.

Most Used Excel Functions

𝐓𝐨𝐩 𝐌𝐍𝐂𝐬 𝐇𝐢𝐫𝐢𝐧𝐠😍

Roles:- Data Analyst, Data Scientist, Data Engineer &Software Developer

Openings:- 100+ 

Qualification:- Graduate 

Salary :- 6 To 25LPA

𝐔𝐩𝐥𝐨𝐚𝐝 𝐘𝐨𝐮𝐫 𝐑𝐞𝐬𝐮𝐦𝐞 👇:-  

https://bit.ly/47FVWg1

Select the company name, and role and apply to the job

Once you get shortlisted, you will receive a call from HR

3️⃣ SUMIF / SUMIFS:

SUMIF and SUMIFS allow summing values based on single or multiple conditions, making it easy to analyze specific segments of data, such as summing revenue by region or time period.

Most Used Excel Functions

2️⃣ INDEX-MATCH:

The INDEX-MATCH combination is often preferred over VLOOKUP for more flexibility. INDEX retrieves a value from a specified cell range, while MATCH identifies its position. Together, they allow more complex lookups, especially when the lookup column isn’t the leftmost column.

Most Used Excel Functions

5 Most Used Excel Functions by Data Analysts

🧵⬇️

1️⃣ VLOOKUP / XLOOKUP:

VLOOKUP is used to look up values in a table or range by row, making it useful for merging datasets or retrieving specific data.

XLOOKUP (newer and more versatile) allows searching both horizontally and vertically and supports approximate matches.

Like for more ✅

Me: = 4/5

Excel: = ok yeah I got you 4/5/2024

Me: No I mean like make it a percent with 2 decimal places

Excel: yeah ok like 8000%

Me: No I mean like 80%

Excel: Oh like $80

Me: I'll just do it myself