Excel tricks and tips-2020 | Techmon66

 EXCEL HACKS 2020 :(part-1)

  The tricks that became you the pro in the M.S.Excel .To work like professional these tricks will helpful a lot. 

 Time is more valuable so, want work in less time and to get output of better and for data entry also these are very helpful.

 Excel is sheet of storing huge amount of data that we can access any time .so, in that way we want use tricks to complete the work.

To work efficiently and professionally, here are the Excel tricks and tips that may help you with your work. working with duplicate content is more irritation. so this tips will help you very much.

 (Part-I)

>> MOVE DATA:

 1. Just highlight the cells and grab the border anywhere around the outside and drag it and drop it where you want it to go. 

2. You can do an entire column or you can do an entire row. 

3. No need to copy/paste and then delete the original. 

>> XLOOKUP():

 1. XLOOKUP() is a powerful new function added to excel in late 2019 and designed to replace VLOOKUP and HLOOKUP. 

>>FILTER LIST:

1. Want an easy way to manage a large table of data.

2. Click anywhere inside the table, go to the Data tab and click on theFilter icon and it creates a filter at the top of each column in your table.

 3. Now you can select one and choose from the list the values that you want to see. It filters that data out. 

4. You can use multiple columns and filter out just the data you want to see. It's a great way to manage large lists of data. 

>> IDEAS:

 1. If you're looking for suggestions on how best to display your Excel data use Ideas for inspiration. 

2. Just click anywhere inside of a table, go to the Home tab, and choose Ideas. This brings up a list of graphs and charts based on that data. 

3. You can hit "+" to add them to your spreadsheet. You can even choose which fields interest you the most and in this example,

>> REMOVE BLANKS: 

 1. If you want to remove a bunch of empty cells in a set of data .

2. Just highlight that whole list, go to the Home tab, and choose Find and Select from the menu. 

3. Choose Go to Special, and check this box for blanks. Hit OK. It's now highlighted all the blank cells in my data set. 

4. Just right-click on any one of them, choose delete, and choose Shift Cells Left. Hit OK.

5. It just cleaned up that list.

 >> QUICK ANALYSIS TOOL:  

 1. Whenyou highlight a table in your spreadsheet in the bottom-right corner there's an icon. 

 2. This is the Quick Analysis Tool. When you click on that icon it brings up a wealth of information that you can select from. 

 3. You can pick Sums of the columns, Sums of the rows, get a Running Total, or Percentage.

 4. You can select Charts and insert them into your spreadsheet. You can set the formatting so that you have data bars that represent the value or you can change the color scheme for the valuesets.

 5. Have visual representation of your data as an icon, mark the top 10%. You can add sparklines. That shows you a mini line graph of your data. 

 6.There's so much available here without having to go through the menus to manually create each one of these items, so look for that icon in the bottom-right corner of any

of your tables. 

>> AUTOFIT COLUMN WIDTH: 

 1. This is by far the quickest way to adjust the width of your columns. Just go to the space in between any columns until your cursor changes and double-click. 

 2. It'll automatically readjust the size of the column to fit the widest point of the data anywhere in that entire column. 

 3. You can also highlight multiple columns, double-click on any one of them, and it'll do multiple columns at the same time.

                                  Or

>> ABSOLUTE CELL REFERENCE:

 1. Excel uses two types of referencing - Relative an Absolute, or a mixture of the two. 

>> PASTE SPECIAL VALUES :

 1. Sometimes you want to eliminate formulas and copy just the data from cells. In this example, I have a first and last name combined

  1. Just highlight the rows that you want, selectable, go to the Data tab, choose Data Validation from this list, change this to List, make sure this checkbox for In-cell Drop-down is checked, and for the source you want to choose your options available in the list, which is going to be these four. 

  2. Hit OK and now in each one of these rows you have an arrow that you can choose from that list.

  3. You can still type in data but if you try to enter it, it gives you an error because the value doesn't match the data validation list that you created. 

 4. You can go back to the list, you can change the input message prompting you to enter the data, and you can also edit and create an error alert whenever you type data that doesn't validate. 

  5. So if you want to make it easy to select data from a list and validate it, use the Drop-Down List. 

 >>FILTER():

 1. FILTER() is a new dynamic array function added to Excel in 2020.

 2. One of the challenges with XLOOKUP and other lookup functions is that they can't return multiple values from a list but FILTER() does. 

 3. FILTER() is a powerful new dynamic array function that solves many issues with previous lookup functions. Look for it in the 2020 or later versions of Excel. 

>>REMOVE DUPLICATES: 

 1. If you have a list of data in Excel and you want to remove all the duplicates sets of information.

 2.Just highlight it, go to the Data tab, select Remove Duplicates from the menu, and in this case, it defaults to all three headings - First Name, Last Name, and Amount.

 3. Where all three match I'm going to remove one duplicate and it took that row from the list. 

 >>DIFFERENCE BETWEEN LISTS. 

 1.Sometimes you want to see the difference between two lists of data.

 2. This could be a monthly budget report or other similar list. If you want to see the difference between two lists highlight the first one, hold down the Ctrl key, and highlight the second one. 

 3. The second one can be on a different sheet if you want to, just hold the Ctrl key down when you click on it, then go to the Home tab, select Conditional Formatting, Highlight Cells Rules, and Duplicate Values.

 4. This shows the duplicates between the two lists. But we can make it the Unique. Hit OK and now you've highlighted the differences between those two lists. 

>>FLASH FILL:

 1. Flash Fill is an automatic entry built into Excel to make you more efficient.

>>AUTOFILL:

 1. Excel can complete patterns for you with AutoFill. So if I were to enter a 1 and a 2, I highlight those and drag them down, it's going to fill in the remainder of those numbers in sequence. 

 2. I can do the same thing with dates. You can use combination of words and numbers and it goes horizontal as well as vertical.

 3. AutoFill can use dates, times, weekdays, months, or any kind of sequential numbering scheme that you come up with and is great at saving you time in data entry.