🗂Enter and revise data - 02
By using a similar tool, FillSeries, you can enter two values in a series and use the fill handle to extend the series in your worksheet.
You do have some control over how Excel extends the values in a series when you drag the fill handle.
If you drag the fill handle up (or to the left), Excel extends the series to include previous values.
If you enter January in a cell and then drag that cell’s fill handle up (or to the left), Excel places December in the first cell, November in the second cell, and so on.
Another way to control how Excel extends a data series is by holding down the Ctrl key while you drag the fill handle.
If you select a cell that contains the value January and then drag the fill handle down, Excel extends the series by placing February in the next cell, March in the cell after that, and so on.
If you hold down the Ctrl key while you drag the fill handle, however, Excel repeats the value January in each cell you add to the series.
By using a similar tool, FillSeries, you can enter two values in a series and use the fill handle to extend the series in your worksheet.
You do have some control over how Excel extends the values in a series when you drag the fill handle.
If you drag the fill handle up (or to the left), Excel extends the series to include previous values.
If you enter January in a cell and then drag that cell’s fill handle up (or to the left), Excel places December in the first cell, November in the second cell, and so on.
Another way to control how Excel extends a data series is by holding down the Ctrl key while you drag the fill handle.
If you select a cell that contains the value January and then drag the fill handle down, Excel extends the series by placing February in the next cell, March in the cell after that, and so on.
If you hold down the Ctrl key while you drag the fill handle, however, Excel repeats the value January in each cell you add to the series.
👍12❤7🔥2
🗂Manage data by using Flash Fill
When you manage data in Excel, you will often find that you want to combine values from several cells into a single value.
In the case above one common data configuration is to have a customer’s first name and last name in separate cells.
In this case, the contacts’names appear in three columns: LastName, FirstName, and Initial.
Note that not every contact has a middle initial.
You could combine the names manually or by creating a formula, but Flash Fill can figure out the pattern if you give it a few examples.
When you manage data in Excel, you will often find that you want to combine values from several cells into a single value.
In the case above one common data configuration is to have a customer’s first name and last name in separate cells.
In this case, the contacts’names appear in three columns: LastName, FirstName, and Initial.
Note that not every contact has a middle initial.
You could combine the names manually or by creating a formula, but Flash Fill can figure out the pattern if you give it a few examples.
👍10❤3
🗂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.
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.
👍9❤1
🗂 How to translate words using Excel
To translate a word from one language to another follow the below steps:
A. Click the cell that contains the word you want to translate.
B. In the Language group, click Translate.
C. If necessary, click Yes to send the text over the Internet.
D. Review the results.
E. Click the Close button to close the task pane.
To translate a word from one language to another follow the below steps:
A. Click the cell that contains the word you want to translate.
B. In the Language group, click Translate.
C. If necessary, click Yes to send the text over the Internet.
D. Review the results.
E. Click the Close button to close the task pane.
👍21❤2
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
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
👍23❤6🔥5
🗂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.
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.
👍15❤1
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
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
👍18❤4🔥2
🗂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
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
👍13❤2🔥2
🗂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.
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.
👍15❤5🔥5
🗂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.
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.
👍11❤1
🗂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.
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.
👍12❤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
👍22❤11
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
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
👍23❤12
🗂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).
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).
👍18❤3
MS Excel for Data Analysis
https://t.iss.one/udemy_free_courses_with_certi
I find this channel super useful for free Udemy Courses - there is also one for WhatsApp users
Hope it will help you too :)
Hope it will help you too :)
👍4❤2