👍5❤2
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
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
👍29❤8😁1
💥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
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
👍24❤16🔥2👎1😁1
🗂Working with data and Excel tables
In this chapter You will learn how to,
- Enter and revise data
- Manage data by using Flash Fill
- Move data within a workbook
- Find and replace data
- Correct and expand upon data
- Define Excel tables
With Excel, you can visualize and present information effectively by using charts, graphics, and formatting, but the data is the most important part of any workbook.
By learning to enter data efficiently, you will make fewer data entry errors and give yourself more time to analyze your data so you can make decisions about your organization’s performance and direction.
In this chapter You will learn how to,
- Enter and revise data
- Manage data by using Flash Fill
- Move data within a workbook
- Find and replace data
- Correct and expand upon data
- Define Excel tables
With Excel, you can visualize and present information effectively by using charts, graphics, and formatting, but the data is the most important part of any workbook.
By learning to enter data efficiently, you will make fewer data entry errors and give yourself more time to analyze your data so you can make decisions about your organization’s performance and direction.
👍35❤13🏆3👎1😁1
🗂Enter and revise data - 01
The simplest way to enter data is to click a cell and type a value.
This method works very well when you’re entering a few pieces of data, but it is less than ideal when you’re entering long sequences or series of values.
In theexample above, you could create a worksheet tracking each customer’s monthly program savings.
Repeatedly entering the sequence January, February, March, and so on can be handled by copying and pasting the first occurrence of the sequence, but there’s an easier way to do it: use AutoFill.
With AutoFill, you enter the first element in a recognized series, and then drag the fill handle in the lower-right corner of the cell until the series extends far enough to accommodate your data.
The simplest way to enter data is to click a cell and type a value.
This method works very well when you’re entering a few pieces of data, but it is less than ideal when you’re entering long sequences or series of values.
In theexample above, you could create a worksheet tracking each customer’s monthly program savings.
Repeatedly entering the sequence January, February, March, and so on can be handled by copying and pasting the first occurrence of the sequence, but there’s an easier way to do it: use AutoFill.
With AutoFill, you enter the first element in a recognized series, and then drag the fill handle in the lower-right corner of the cell until the series extends far enough to accommodate your data.
👍10❤4🔥1🥰1
🗂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