Excel Filter: Tips and Shortcut

As Salesforce admin, sometimes we need to prepare data before load into Salesforce correctly. Recently, I need to clean and prepare some pretty big amount of raw data. Microsoft Excel apparently is one the easiest and best available tool to clean and prepare the data before loading into Salesforce.

In this blog, I would like to share some tips learned from the exercise.

TIPS
- Ctrl+Shift+L – toogle enable and disable filter, make sure cursor in the range of value
- Alt+A+C – clear ALL filters

- Place cursor at header, Alt+Down arrow to show drop down menu

- Place cursor at body, Alt+Down arrow to show drop down of available values (except number)
* the last one will work even no filter added

In drop down Filter menu (Alt+Down arrow)
- E key – type search
- C key – clear filter in current cursor column
- F+E key – select blank value
- F+N key – select non-blank value
- Up and Down arrow – move cursor, Space bar key to select, and Enter to perform action
- When cursor in range of values, Home – move to top value
- When cursor in range of values, End – move to bottom value
- Alt+Down Arrow+S – sort A to Z
- Alt+Down Arrow+O – sort Z to A
- Alt+Down Arrow+T – sort by Color sub menu
- Alt+Down Arrow+I – filter by Color sub menu
- Alt+Down Arrow+F – text or Date Filter sub menu

DO
1. Double click at bottom right of a cell will copy value to visible rows only

Double click bottom right corner to auto-fill value of C2 to visible rows below it (C4 and C6). When we clear the filter, only C4 and C6 is filled, while C3 and C5 is skipped. Value of C2 can be static or formula.

You can apply this to multiple columns too.

2. Similar to point 1, copy paste will to copy value to visible rows only

- Select range C2-D2 -- copy
- Select range C4-D6 -- paste
- Result: only value in C4, C6, D4, D6 will be copied, while row 3 & 5 skipped

** the same result if you select range C2-D6 for fill with a color, row 3 & 5 will not be colored


3. Copy paste will copy only from visible rows value 
- This is not applicable for manually hidden rows
- Select cells / range to copy, example: copy as below screenshot

Paste to new area, I put my cursor to cell A8 -- only visible cells are copied.

4. Deal with blank row
If you need to deal with blank row in filter, make sure to highlight/select the area (in sample below, select area A1-D10 or the whole A-D column), before hit Ctrl+Shift+L, otherwise filter will not include area below empty row (row 7 and below).

DON'T
1. Copy more than 1 row from source into target with filtered rows

Don't copy more than 1 row from source into target with filtered rows, this cause value in target hidden filtered rows will be overwritten, sample: copy 3 rows of "B" from source (B11-B13)

Paste it to target which is filtered rows, for this example: paste into cell C2

Instead of copy value "B" into C2, C4, C6 -- "B" will be copied into C2, C3, C4.

Summary, copy from multiple rows will skipped filtered target.

This action will only work well, if there is no skipped rows in the applied filter, example: Jawa in sample above is at continuous rows e.g. 2,3,4.


Excel Table
By using the Table features, you can manage the data in the table rows and columns independently from the data in other rows and columns on the worksheet.
- Ctrl+L – create Excel Table
- To delete Excel Table table without losing the data:
   - Select Convert to Range from DESIGN tab menu, or
   - Right-click on the table and click Convert to Range under Table menu
- Filtering controls are added to the table headers automatically
- Place cursor anywhere in table, Alt+Shift+Down arrow – show the drop down menu
- You can have filter for more than one range of data on a sheet