In this post, I highlight the 10 Excel features I use often when formatting record inserts/updates and doing analysis on metadata.
Special thanks to Scott Lewis, an Excel wizard who had the misfortune of being my cube neighbor for a couple years and enduring countless “How do I do this in Excel” questions from me.
1) VLOOKUP: search & match data
- Use Case: Comparing two lists or appending data, obtaining User ID’s for a file containing only user names.
- How To: In this example, I pulled a Salesforce report of all Users with ID’s and placed them on the 2nd tab.
- Create blank column where you want the matches to be.
- Insert VLOOKUP formula into first cell
- Lookup_value: the first cell of the column you need to match, in this case “A2”
- Table_Array: The source columns you’re using to match, you can just highlight them.
- Col_Index_Num: In your array, which column are you bringing?
- Range_lookup: I always put “False”
- Copy the formula for the entire column, it will automatically update cell numbers.
**WARNING** VLookup is not case sensitive, so using 15 digit ID’s can create mistakes. Always use the 18 digit ID’s when working with records in Excel.
2) CONCATENATE: Combining Values from multiple cells into one
- Use Case: Creating a file of test records, loading multi select picklist fields.
- How To: Formulas – Text – Concatenate. Enter Cell followed by a symbol or space, the quotes are automatically added for you.
3) SUM Formula: Counts & Totals
- Use Case: Formatting test records. You need to load 1,000 test records, but each one must contain a unique ID.
- How To: Formulas – AutoSum – Sum. In this example, I’m starting with 111 and inserting the Sum formula directly below it to create a set of unique numbers in increments of 10.
4) Text To Columns: Breaking one cell into multiple cells (the opposite of Concatenate)
- Use Case: Singling out one relevant part of a string. You pulled a report or copy/pasted a webpage and need to remove the junk.
- How To: Highlight Column – Data – Text To Columns – Delimited – Choose Symbol, punctuation, or space – Next – Finish
Note: Insert blank column to the right, otherwise you could overwrite data in neighboring columns.
5) Removing Objects: Highlight and delete all objects on sheet
- Use Case: Cleaning up a list that was copy and pasted form a source containing objects, If you copy and paste an object field list, you may end up with check boxes that need to be removed. (This can be avoided by pasting to match destination formatting instead of Source formatting)
- How To: Find & Select – Go To Special – Objects. The objects will then be selected, then hit Delete.
6) Highlight Cells Rule: Highlights cells that are duplicates or contain specified text
- Use Case: Finding duplicates or cells that contain a certain value, a business unit name perhaps. If your report is not easily filtered in Salesforce, you can use Highlight Cells Rules to further break down your data.
- How To: Conditional Formatting – Highlight Cells Rules – Text That Contains/Duplicate Values
7) Removing Duplicates
- Use Case: Drilling down a large report to only see the unique values.
- How To: Data – Remove Duplicates – select one or multiple columns.
8) Double Click Auto Fill
- How To: Double Click lower right cell corner to auto fill to end of rows. NOTE: Cells ending in numbers can sometimes autosum. Example, a cell ending in 12 would end in 13, 14, 15, and so on.
9) Changing Formula Values To Text
- Use Case: After using a formula, you’ll need to change those cells to text before inserting/updating in Salesforce.
- How To: Select Column – Copy – Right Click – Paste Special – Paste Values – “123”
10) KeyBoard Shortcuts That Save Scroll Time
- CTRL + Up or Down Arrow: Goes to last row
- CTRL + Right or Left Arrow: Goes to last column
- Shift + CTRL + Up or Down Arrow: Goes to last row and selects all
- Shift + CTRL + Right or Left Arrow: Goes to last column and selects all