Excel Tips/Tricks You Wish You Knew Earlier #excel

1. Autofit All Columns Alt + H + O + I
2. Flash Fill - CTRL+E
3. Pivot Table - Alt + N + V
4. Conditional Formatting - Alt + H + L
5. Auto Spell - F7

🗂Performing calculations on data

Excel workbooks gives you a handy place to store and organize your data, but you can also do a lot more with your data in Excel.

One important task you can perform is to calculate totals for the values in a series of related cells.

You can also use Excel to discover other information about the data you select, such as the maximum or minimum value in a group of cells.

Regardless of your needs, Excel gives you the ability to find the information you want. And if you make an error, you can find the cause and correct it quickly.

Often, you can’t access the information you want without referencing more than one cell, and it’s also often true that you’ll use the data in the same group of cells for more than one calculation.

Excel makes it easy to reference several cells at the same time, so that you can define your calculations quickly.

Keep tuned as i guide you through procedures related to streamlining references to groups of data on your worksheets and creating and correcting formulas that summarize an organization’s business operations.

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_analyst

🗂Time Saving Keyboard Shortcuts

-Practice using keyboard shortcuts
-saves time
-works better than to navigate looking for options

🗂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

🗂How To Use The "NOW" Function

The NOW function displays the time at which Excel updated the workbook’s formulas, so the value will change every time the workbook recalculates.

💥Note:
The proper form for this function is =NOW().

You could, for example, use the NOW function to calculate the elapsed time from when you started a process to the present time.

🗂Creating Formulas to Calculate Values

In Excel you can use the names of any ranges you defined to supply values for a formula.

In the table above, if the named range NortheastLastDay refers to cells C4:I4, you can calculate the average of cells C4:I4 with the formula =AVERAGE(NortheastLastDay).

With Excel, you can add functions, named ranges, and table references to your formulas more efficiently by using the Formula AutoComplete capability.

Just as AutoComplete offers to fill in a cell’s text value when Excel recognizes that the value you’re typing matches a previous entry.

💥Note:
Formula AutoComplete offers to help you fill in a function, named range, or table reference while you create a formula.

🗂Conditional Formulas

You can use formulas to display messages when certain conditions are met.

This kind of formula is called a conditional formula; one way to create a conditional formula in Excel is to use the IF function.

Clicking the Insert Function button next to the formula bar and then choosing the IF function displays the Function Arguments dialog box with the fields required to create an IF formula.

When you work with an IF function, the Function Arguments dialog box has three boxes:

- Logical_test,
- Value_if_true,
-Value_if_false.

The Logical_test box holds the condition you want to check. Now you need to have Excel display messages that indicate whether the condition is met or not.

To have Excel print a message from an IF function, you enclose the message in quotes in the Value_if_true or Value_if_false box.

Excel also includes several other conditional functions you can use to summarize your data, as shown in the below lesson.

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

If you want to be an Advanced Excel User, do this...

1. Autofit All Columns Alt + H + O + I
2. Flash Fill - CTRL+E
3. Pivot Table - Alt + N + V
4. Conditional Formatting - Alt + H + L
5. Auto Spell - F7

Free MS Excel Resources posted here
👇👇
https://t.iss.one/excel_data/3

Hope it helps :)

🗂The order of operations used in MS Excel while evaluating formulas

MS Excel follows a standard math protocol to evaluate a formula.

This protocol is called “order of operations” – PEMDAS –

~Parentheses
~Exponents
~Multiplication
~Division
~Addition
~Subtraction

MS Excel also applies some customization to handle the formula syntax.

The order in which MS Excel performs calculations can affect the return value of the formula.

First of all, Excel evaluates any expressions in parentheses.

As we have seen in mathematical formulae too, parentheses essentially override the normal order of operations. It prioritizes certain operations.

Next, Excel resolves cell references like A1 (cell address). It evaluates range references like A1:A10, making them arrays of values. 

It also performs range operations like a union (comma) and an intersection (space).

I find this channel super useful for free Udemy Courses - there is also one for WhatsApp users

Hope it will help you too :)

Excel Cheatsheet

Excel Cheat Sheet

Many people pay too much to learn Excel, but my mission is to break down barriers. I have shared complete learning series to learn Excel from scratch.

Here are the links to the Excel series

Complete Excel Topics for Data Analyst: https://t.iss.one/sqlspecialist/547

Part-1: https://t.iss.one/sqlspecialist/617

Part-2: https://t.iss.one/sqlspecialist/620

Part-3: https://t.iss.one/sqlspecialist/623

Part-4: https://t.iss.one/sqlspecialist/624

Part-5: https://t.iss.one/sqlspecialist/628

Part-6: https://t.iss.one/sqlspecialist/633

Part-7: https://t.iss.one/sqlspecialist/634

Part-8: https://t.iss.one/sqlspecialist/635

Part-9: https://t.iss.one/sqlspecialist/640

Part-10: https://t.iss.one/sqlspecialist/641

Part-11: https://t.iss.one/sqlspecialist/644

Part-12:
https://t.iss.one/sqlspecialist/646

Part-13: https://t.iss.one/sqlspecialist/650

Part-14: https://t.iss.one/sqlspecialist/651

Part-15: https://t.iss.one/sqlspecialist/654

Part-16: https://t.iss.one/sqlspecialist/655

Part-17: https://t.iss.one/sqlspecialist/658

Part-18: https://t.iss.one/sqlspecialist/660

Part-19: https://t.iss.one/sqlspecialist/661

Part-20: https://t.iss.one/sqlspecialist/662

Bonus: https://t.iss.one/sqlspecialist/663

I saw a lot of big influencers copy pasting my content after removing the credits. It's absolutely fine for me as more people are getting free education because of my content.

But I will really appreciate if you share credits for the time and efforts I put in to create such valuable content. I hope you can understand.

You can join this telegram channel for more Excel Resources: https://t.iss.one/excel_analyst

Python Learning Series: https://t.iss.one/sqlspecialist/615

Complete SQL Topics for Data Analysts: https://t.iss.one/sqlspecialist/523

Complete Power BI Topics for Data Analysts: https://t.iss.one/sqlspecialist/588

I'll now start with learning series on SQL Interviews & Tableau.

Thanks to all who support our channel and share the content with proper credits. You guys are really amazing.

Hope it helps :)

How to become a Data Analyst in 2025

If you dream of becoming a data analyst, let 2025 be the year you make it happen.

Work hard, stay focused, and change your life.

Happy New Year! May this year bring you success and new opportunities 💪

Top 10 Excel Interview Questions with Answers 😄👇

Free Resources to learn Excel: https://t.iss.one/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.iss.one/sqlspecialist

Hope it helps :)