How Excel-based Teams Can Report on Salesforce Org Data
Every Salesforce admin will know that Salesforce reports and dashboards training is an essential part of your role. While powerful and flexible, Salesforce reporting may not be intuitive for users who have spent their work-lives using Excel charts. When we compare Excel charts versus Salesforce reports, there is a new user interface to navigate, terminology to learn, and let’s not forget that the dataset itself is not exposed in Salesforce like it is in Excel.
These differences between Excel charts and Salesforce reports cause user frustration. There will be people in your organization who are not invested in mastering Salesforce reporting and need to pull reports quickly, with no hassle. Yes, I’m talking about the management team, who would rather use Excel to get the insight they need ad-hoc and not have you constantly on standby to pull Salesforce reports on their behalf. I have seen this pain point time again, and the typical answer is to cut users off from Excel. In this post, I will share the 3 steps you need to allow Excel users to report on Salesforce data, and hopefully, prove to you that allowing certain users to remain in Excel doesn’t spell disaster for your Salesforce data.
3 Steps to Allow Excel Users to Report on Salesforce Data
You have decided to enable some users to use the Excel charts they are familiar with. That’s enough to get an admin’s pulse racing because there are many data model and security considerations, which is why you shouldn’t simply allow users to export Salesforce report data to Excel. You should put in guardrails to ensure that the process is done correctly and smoothly.
To get these Excel-based teams reporting on Salesforce data, you need to do the following:
Connect the spreadsheet to Salesforce to pull your Salesforce report(s) data into Excel.
Build your Excel charts and dashboards using all the great tools Excel provides.
Share the Excel document with users who need to see the data.
So let’s now explore how to make these three steps happen, and ensure they happen properly.
Step 1: Connect Connect your spreadsheet to Salesforce to pull your Salesforce report(s) data into Excel. This becomes seamless with the help of a connector, such as XL-Connector. The connector establishes a defined data source, such as a Salesforce report or SOQL query, which can be refreshed, by users, with a click of a button from Excel. The Excel will be populated with the current Salesforce data.
As an admin, you’re in for a treat. By using a connector, you can define how the Excel sheet looks when it’s pulled from Salesforce to ensure it’s usable for your users, which is a real bonus. Your users can pull the data into the sheet with it looking consistent each time.
Using Salesforce data while offline is a gap that Salesforce are working on improving for their platform. There are already options on the market that allow users to work with Salesforce data via Excel even without a network connection. A scheduled job will pull the report from Salesforce and populate the spreadsheet with it whenever it runs (as little as every 5 minutes); the data the user sees in Excel will be as fresh as the last scheduled job run. The use cases that spring to my mind for this are field service reps, who have no guarantee of a good connection while onsite, but I suppose any executive would also find the offline capabilities invaluable while traveling.
Step 2: Build
Build your Excel charts and dashboards based on the refreshed data pulled from Salesforce using all the great tools Excel provides, such as pivot tables, vlookups, VBA scripts, and other formulas – functionality not available in Salesforce reports.
Some users will find they are far more productive using Excel charts, as I mentioned in the opening, it’s the user interface they know to navigate, familiar terminology, and let’s not forget that the dataset that the chart is based on is exposed in Excel for quick inspection and testing ‘what if’ scenarios.
Step 3: Share
Share the Excel document with users who need to see the data. Once the data is in Excel, you sync them to OneDrive or SharePoint.
By using OneDrive or SharePoint to distribute documents, security is completely under the admin’s control. Document sharing is controlled, so users who can’t have access – won’t have access. We heard many teams needed to populate Excel spreadsheets to quickly get data in front of executives who have their specific preferences. Yes, these users don’t necessarily need to have access to Salesforce to receive insights.
While powerful and flexible, Salesforce reporting may not be intuitive for users who have spent their work-lives using Excel charts, who are not invested in mastering Salesforce reporting, and who would rather use Excel to get the insight they need ad-hoc (such as the management team). The bonus for you, as an admin, is that you won’t have to be constantly on standby to pull Salesforce reports on their behalf.
Now you have seen what should be involved to successfully give Excel users Salesforce data that is up to date, free from formatting errors, and kept to your organization’s security requirements. Using a Salesforce to Excel connector, such as XL-Connector is the way to move forward with Excel reporting, with confidence.