Many Salesforce admins will know you can take users out of Excel, but you can’t take the love for excel out of some users.
While powerful and modern, Salesforce may not be intuitive for those users who have spent their work-lives using Excel.
There’s a new user interface to navigate, terminology to learn, and let’s not forget that updating Salesforce record-by-record isn’t as easy as it is in Excel.
Enter XL-Connector, which does what it says: syncs Salesforce data to Excel, securely. Not only does this connector keep users happy, but admins can mass update records fast, without battling with data loaders.
This in-depth review will dive into XL-Connector’s features, ideal use cases, setup effort, and how this app could be a great addition to your Salesforce org.
Features
XL-Connector’s main objective is to ensure that you can securely export, import, and automatically refresh Salesforce data using an Excel user experience.
The review will introduce how XL-Connector is so powerful in automating data operations between Excel and Salesforce. From there, I will show you how it’s easy to:
- Pull data
- Push data (including create, modify, delete, restore)
Finally, we will investigate the Salesforce administrator tools, and other notable features.
Note: there are two products we will focus on (XL-Connector and XL-Connector 365). While the XL-Connector is the first version, available for Windows only, the latter expands the functionality to either Windows or Mac, available for both working with Excel locally or online.
Access Salesforce Data from Excel
If you’ve chosen XL-Connector 365, the login to Salesforce will be available on the side panel. Once authenticated, you will find the functionalities there too.
You can also choose between a sandbox and your production environment:
XL-Connector Flows
XL-Connector Flows automate pretty much any operation possible in XL-Connector, from creating and updating records, to running VBA macros. Each sheet can have multiple flow steps.
Pull Data
The ability to seamlessly pull Salesforce data into spreadsheets is what defines XL-Connector the best. You can choose between pulling an existing Salesforce Report into Excel, or building your own SOQL query directly in Excel.
I chose to go with an existing Salesforce Opportunities report. As you’ll see below, you can choose which report columns to export into the spreadsheet, either by scrolling through the list of fields, or searching for specific ones:
Since XL-Connector is all about empowering you to control how and where the data is displayed, there are extra options that become available once the report is selected.
My personal favorite is the ability to automatically create drop-downs for picklist columns; this way, the options will be restricted to the picklist values available in Salesforce (essentially, a validation rule baked into the Excel spreadsheet):
The benefit of the other way to pull data, respectively SOQL queries, is that you can build and use a much more complex logic in comparison with the standard Salesforce reports possibilities.
Extra options are available for the SOQL route, too, once the query is set. For example, you can stamp the exact moment when the data was last refreshed, to ensure the information is still accurate, or may need to be updated from Salesforce once again.
You also have an option to use dynamic text from the spreadsheet in your SOQL queries using special syntax:
Push Data
Once the Salesforce data is in one (or more) spreadsheets, it’s time to make the changes we need to the data, and update the records using:
- Create/Modify
- Delete/Restore
Create/Modify
XL-Connector can update up to 1 million Salesforce records! As most cases won’t even get close to that number, the updates will be reflected in your CRM instance in a matter of a few minutes.
Going back to the Opportunities report example, do you remember how we chose to create dropdowns for the picklist fields? I’ve easily updated the ‘Stage’ column for a few Opps while ensuring I have the exact values that can are found on the Salesforce picklist field:
As an admin, one small typo could have resulted in spending extra time fixing a mistake that can be easily prevented. (Note: Opportunity ‘Stage’ is a standard field, hence the picklist values cannot be restricted).
Once the data is satisfactory, we can proceed with the mass update to Salesforce.
You will be prompted to choose the object, review the mapping (XL-Connector got it correct in my case, but it’s always safe to double check!), then ensure you have selected the correct cell range to push into Salesforce.
Before completing the update, you must define a “flowstep” name, which becomes a reusable process for future data updates. Choose from extra options, such as the batch size:
Once the update runs, a new column will be created at the end of the sheet, highlighting the Operation results. If any validation rules are triggered, for example, this is where the error messages will appear.
You will notice that the “flowstep” will now appear, which can be re-run whenever needed!
Delete/Restore
Similar to the update operation we explored, deleting or restoring records is really intuitive, and fast.
This time, the minimum column you need to include is record ID, and you’re ready to run the operation!
Salesforce Administrator Tools
There are multiple XL-Connector features that admins are bound to come back to, over again; however, I will deep dive into two, which I believe cover both business as well as technical needs:
- Convert Ids
- Analyze Layouts
Convert Ids
We have all had our fair share of fun when discovering the difference between the case sensitive 15 character Ids versus the 18 character ones!
The Salesforce suggestion is, as we all know, to create a formula field in order to obtain the Ids and call it a day. This works in most cases, but what if we need an 18 character Role Id in a spreadsheet really quick? In XL-Connector, this is literally one button away.
Simply select all cells containing the Id, click the button, and voila!
Analyze Layouts
Once clicked, you will be prompted to select the object and then import the layouts of interest from Salesforce (s).
After deciding on the object and layout(s) you want to analyze, two spreadsheets will be created to highlight the fields used and not used on layouts, as well as various details about each of them: picklist values (if relevant), API Names, if the field is required or not, etc. This makes it extremely easy to get a complete overview in no time!
Automatic Data Refresh
One powerful feature of XL-Connector 365 is to schedule the automatic data refresh, even when you’re not around. In order to benefit from this, the document needs to be saved in OneDrive or SharePoint.
The data can be refreshed as often as every 5 minutes (providing the file does not exceed 5MB, a limit set by Microsoft for documents that can be opened online).
Google Sheets alternative
G-Connector is definitely worth mentioning for the Admins out there who prefer Google Sheets rather than Excel. Even though the interface and user experience is slightly different, the main functionality remains available across all your sheets.
Email Notifications
Set up email notifications for either the success or failure of your data refresh, to be consistently informed about all the data sources for those pivot tables!
You can even attach the data to the email, or sharing a link to the file. Since Salesforce users absolutely love receiving reports via email (but can only subscribe to 5 of them), this is one awesome workaround to that limitation.
Use Cases
Xappex is keen on ensuring that the XL-Connector will offer a productivity boost to any user while they’re completing the day-to-day tasks.
Mass Convert Salesforce Leads
One use for XL-Connector which will definitely be appreciated by any Business Development or Sales team!
Salesforce Leads have to be converted one by one, which can take a lot of time (while it’s possible to automate lead conversion in Salesforce using clear criteria and custom APEX, it’s not what every business wants to do).
As long as the data points (shown in the image below) are contained in the Excel file, up to 1 million records can be converted at once.
An Opportunity may or may not be created, and existing Contacts or Accounts can be updated accordingly (depending on your business’ requirements).
Mass Update Field-level Security
Now, something special for admins, too. We all know the struggle of having to check/modify the field-level security in various profiles, for multiple fields. While Salesforce’s recommendation is for orgs to use a Permission Set/Permission Set Group model, profiles are still commonly used.
With the latest release, XL-Connector now makes it possible to assign proper access to the fields faster. It can download and update field-level security settings for any object-profile combination within your org.
Download Data from Salesforce Records
The XL-Connector managed package is free to use, and it does wonders when used in combination with the XL-Connector add-in from your spreadsheets. The package contains components for both Salesforce Classic and Lightning Experience.
Once installed, step-by-step instructions are available during the configuration step to get the components up and running:
Users will be able to download files either from the ‘Get XL-File’ button, or from the component.
By leveraging the mail merge syntax in the templates, predefined fields will be automatically populated with data from the respective record. Users can then make updates and import back into Salesforce, directly from Excel.
Impact
User Adoption
As we said in the opening: you can take users out of Excel, but you can’t take the love for excel out of some users. Salesforce may not be intuitive for those users who have spent their work-lives using Excel, which will negatively impact user adoption, and ultimately, lead to Salesforce failing your organization. It’s a vicious cycle.
This guide covers why Excel could be the most suitable reporting tool for some of your colleagues: How Excel-based Teams Can Report on Salesforce Org Data.
Data Quality
Up to date data is a key dimension of data quality, so if you remove the friction in updating Salesforce records, you will maintain data recency.
As an admin, one small typo could result in spending extra time fixing a mistake that could have been easily prevented. XL-Connector’s validation features keep data input by users into Excel on track.
Admin Speed, Alleviate Admin Burden
There is always the option to create a Salesforce report with all the fields required, export it locally, make the changes, and then eventually import it back to Salesforce (using either Data Loader, Workbench or the good old Import Wizard). While it’s an option, it’s time consuming and not ideal in a fast paced environment most companies operate in.
One key advantage of the XL-Connector (and XL Connector 365) is that it doesn’t live in Salesforce, hence it takes away the need for an admin to configure the tool but empowers users to go at their own pace.
From a security perspective, once the user logs in with one of the below options, the Salesforce permissions are mirrored – there is no need for an integration user or special permission sets for the tool to be used.
Setup
From downloading and installing the XL-Connector locally on your machine, to accessing all the functionality, will take no longer than a few minutes of your time.
XL-Connector 365 can be installed directly from the Office Store (again, in no time) and will become available as an Add-In in good ol’ Excel (Windows or Mac), as well as in the online version.
Documentation and Support
The documentation Xappex provides for all of their products is simple, straightforward and regularly maintained. From the very start, I could easily find all the information that I needed about XL-Connector and any other product, such as the difference between the initial version and the newer XL-Connector 365.
In case a question specific to your use case comes up, there’s always the possibility to reach the Xappex support team at support@xappex.com, who know the tool inside and out. One to one sessions can be scheduled for a more customized deep dive on the product, tailored to your needs.
Pricing
In terms of pricing, the first thing to highlight is some functionality is available for free: the ability to pull unlimited reports and queries from Salesforce locally.
Xappex does offer paid options starting at $99/user/year, depending on what you need to use the XL-Connector for. This makes it quite an affordable tool, considering the amount of hours it can help everyone save.
Summary
With so many features to make use of constantly, XL-Connector as well as G-Connector have tremendous potential to become a one-stop-shop for managing Salesforce.com data for both users and admins.
The tools have a 30-day trial you can make use of and benefit from the entire list of functionalities at no cost, so why not go for both?
Regardless if you continue to use the free version or commit to a paid license, XL-Connector is sure to become one of your favourite tools to explore every day!
Comments: