TIP - 44 : Advanced filter in excel

Advanced filter in excel – unique records only

This feature is very helpful to get the unique records out of the total list of records and used to get the criteria-based records as well.

Step 1: Click on Advanced filter from Data Tab

Step 2: From the dialogue box Advanced filter, choose “copy to another location” (it is always better to choose new location)

Step 3: Select list range from your data base or table, criteria range keep it as blank and copy to select the cells where you want to place your data

Step 4: Click on check box “Unique records only”, Then click on OK

Only unique records will be published.

When you want a criteria-based, just use criteria range and select data as shown below –

So you can filter data records based on conditions aswell.

Comments

Hold Ctrl Key in Formula ( no typing of comma)

Hold Ctrl Key in Formula (no typing of comma) There is no need for us to select one cell then type comma (,) and again another cell and typing comma (,) and repeat it. It is easy to select required cell in formula by using keyboard Ctrl key , so that we can save a time. Example: =Sum(J3,J7,J11)

Blank Cell - Color code and formatting

Blank Cell - Color code and formatting This feature is mostly used to identify blank cells and if any time some one has accidentally deleted any value in the cell which can be identified with this trick. How to do this – Step 1 - Please click on new rule (conditional formatting) under Home tab Step 2 - Select rule type as “Format only cells that contains” Step 3 - Under edit rule section choose blanks from the drop down and click on format and select fill with color of your choice, then click on OK , again click on OK

Conditional formatting - Highlight Duplicate Values

Conditional formatting - Highlight Duplicate Values This feature is mostly used to identify duplicate values in a range of cells or array, it also identify the any error of data of unique records like ID numbers, serial numbers etc . How to do this – Step 1 - First select the range of cells, then click on Highlight cell rules (conditional formatting) under Home tab Step 2 - Select rule type as “Duplicate Values” Step 3 - Then click on OK

BEST EXCEL TIPS AND TRICKS | TOP 100 EXCEL TIPS AND TRICKS

Search This Blog