As a Salesforce Admin, you have to be responsible for creating Salesforce data, maintaining that data and even extracting it so it can be analyzed and used by others. Over the years, a number of Excel based tools have been created to help Admins (and others) perform these tasks. I often encounter Admins who don’t know any of these applications exist, so I thought it would be helpful to review the ones that I have used. Some of the older apps are no longer supported and are dependent on older Microsoft standards, but it’s still worth mentioning them.
The Excel Connector
As far as I know, this tool created by Ron Hess, is the first Excel tool designed to help Admins maintain their Salesforce data. It has to be manually installed into Excel and while I have it working on a Win10/Office2010 system, I haven’t been able to get it to work with Win10/Office 2013. It is no longer maintained but for you VBA gurus, it is an open source product.
It has a basic user interface that allows you to log into your Salesforce platform, create simple queries to download data and allows you to mass create, edit and delete records. It is limited to API 16 so it doesn’t recognize newer objects based on much newer versions of the API, and it can only update records in batches of 50.
I still use the Excel Connector for quick and simple data updates as it can be much quicker than extracting the data, performing updates and then uploading the data with existing data loader tools. I also like that when extracting data, if an object contains an Account or Contact column, I can choose to see either the actual Salesforce ID or the Name. To access a Sandbox, click the “Advanced” button and change the URL from www.salesforce.com to “test.salesforce.com”.
Salesforce Reports for Excel (Pilot)
Often confused with the “Excel Connector,” this tool started out as a part of a supported product, “Connect for Office,” that contained both MS Word and Excel add-ons. The original supported “Connect for Excel” product is not available anymore and was to be replaced by the in-pilot “Salesforce Reports for Excel.” This tool allows users to embed multiple Salesforce reports directly to an Excel workbook and afterward, refresh the reports directly from Excel. For organizations that need to format, analyze or manipulate their Salesforce data in Excel, using this tool is a lot more efficient than repeatedly manually running reports in Salesforce and clicking the Export details button. It is possible to add multiple reports to a single workbook and Salesforce Reports will refresh them all at once.
Current documentation says the tool requires Win7/Excel2010 but I have it working on a Win10/Office2013 system. For me, the tool only displays custom report folders; standard folders are not visible. Unfortunately, Salesforce will no longer be investing in the product. For those of you who don’t mind working with an unsupported app, the installation files will remain on the Developer site.
Featured Content
The Salesforce Einstein Analytics tool provides organizations with the ability to visualize the activity taking place within their unique Salesforce environment, offering invaluable insights and health checks on
Stanford SPARK was established in 2006 as a translational medicine program within the school of medicine at Stanford. Founded by Chemical and Systems Biology Professor Daria Mochly-Rosen, SPARK was created
Microsoft Power Query for Excel
As part of the announced Microsoft and Salesforce partnership, a Salesforce extension for Microsoft’s Power Query tool was announced. Power Query gives you the ability to download data directly into Excel by creating a query or by extracting data directly from a report. It combines some of the functionality of both the Excel Connector and the Reports for Excel tools. It is nice to have a new powerful tool that officially supports Win10 and newer Office versions. However, further testing of the tool uncovered two serious limitations. It is not currently possible to login to a Sandbox org with the Power Query tool. Additionally, when using a report as a data source, it has a maximum download limit of 2,000 rows. Removing the 2,000 row limit is on the Salesforce Product Roadmap, but it should not be expected in the next few releases.
Bulk Object Field Creator
I’m including the Bulk Object Field Creator by Tech9logy Creators application because it uses an Excel template to allow Admins to mass add fields to a Salesforce object. Initially a free product, it is now a paid app (the free trial provides limited capability). If you are implementing Salesforce for a new organization and you have multiple custom objects with many custom fields, this tool may save you some time.
Currently, Field labels become the API name, so if you want to add a prefix to your API name to identify fields created by you versus others, it won’t be possible. The tool doesn’t support Field Level Security (FLS), so you will still have to configure your profiles afterward to determine who can view and edit the fields. The vendor also has a much more powerful application called “Config Bench Pro” that will allow you to update FLS, user profiles and even validation rules.
Enabler4Excel
Originally a free app, Enabler4Excel is now a paid application. It is a true combination of the Excel Connector and the Salesforce for Excel applications. It allows users to embed one or more Salesforce reports into an Excel workbook or you can create your own queries to extract data directly into Excel. Once the data is in Excel, you can edit, delete or add new records and push those changes back to Salesforce. With more advanced versions of the application, Admins can control if a user can update their Salesforce data from Excel or just retrieve it. The tool also allows users to convert Salesforce IDs from 15 to 18 digits and convert page layouts to Visualforce pages.
Create SOQL Query to retrieve data
Retrieve data by accessing a Salesforce report
Apttus X-Author
X-Author is a suite of Excel based applications released by Apttus. They provide a set of pre-configured apps to allow you to manage your Accounts, Opportunities and Campaigns directly from Salesforce. More impressive, however, is that you can also configure powerful custom templates/applications to meet your specific processes. Templates can be configured to work with data presented in Excel rows, an Excel form (ex. an invoice or quote) or even grabbing data from a matrix grid and pushing it back to Salesforce. If you already have a complicated process designed around an Excel workbook, you could use X-Author to pull Salesforce data into the workbook and then push results back into Salesforce. The workbook could still be used by your users to manipulate the data, but now it would be centrally stored in Salesforce. The workbook could even leverage Salesforce’s data security model, validation rules and workflows.
The X-Author suite of products provides more features than I can possibly review here. So, if you are already using Excel to manage an extremely complicated process, have a look at X-Author to see if it can help bridge the gap between Excel and Salesforce.
You may also be interested in:
