This Reporting tips training module has been designed to provide you with several tips to help you use the features of reporting easier and more efficiently.
On multi-select picklists in Salesforce (e.g. Main Activities, Neighbourhoods) it is possible to select more than one value. Because multiple values are stored in a single field, this means that when the report is exported to Excel, all of the values will appear in a single cell, delineated by a semicolon.
This is fine for a summary of the details in the field, but if you want to do more with the data, it’s handy to separate these values into separate cells.
Excel has a handy feature that lets you do just that with its Convert Text to Columns wizard. Just follow these steps:
- Export your report to Excel
- Select the column with the delineated values you want to separate (Main Activities in this example):
- Click on the DATA menu on the ribbon
- Click on Text to Columns
- Then simply follow the wizard instructions:
You can click next on most of the defaults, but make sure ‘Semicolon’ is selected in step 2, as that’s how the values are delineated in Excel:
Click Finish, and the individual values from the multi-select field should now be spaced out in separate cells:
All you have to do now is add relevant headers to your new columns, e.g. Main Activity 1, Main Activity 2…