Pages

Sunday, January 15, 2017

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.

DO1. 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

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'T1. 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

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