Admins / Users

How Excel-based Teams Can Report on Salesforce Org Data

By Alex Tarasevich

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:

  1. Connect the spreadsheet to Salesforce to pull your Salesforce report(s) data into Excel.
  2. Build your Excel charts and dashboards using all the great tools Excel provides.
  3. 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 travelling.

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.

Summary

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.

The Author

Alex Tarasevich

Founder and CEO of Xappex, the provider of Salesforce integration solutions for Microsoft Excel and Google Sheets (XL-Connector and G-Connector).

Comments:

    Ingo ter Meulen
    December 07, 2020 11:25 am
    While this looks for sure good, I'm still missing the free sforce connector (https://code.google.com/archive/p/excel-connector/). Wisely used you could create reporting depth beyond the capabilities of SF. And use this to manipulate and update the data with ease, no matter how much. But with the retirement of TLS1.0 it stopped working and nobody updated it to TLS1.2. Apptus picked it up and created a product (or as a part of one) https://apttus.com/solutions/salesforce-excel-connector/, but it is not for free anymore. For me the sforce connector made the clumsy data loader obsolete.
    Allan Pearson
    December 07, 2020 6:55 pm
    I agree with much of what is written here. Excel allows for a wider range of visualisations than Salesforce and it allows data to be be combined and transformed in ways that are not possible within Salesforce. However, the Power Query function in Excel allows a user to extract data from Salesforce, transform it and load it into Excel without the addition of a third party connector. Since Power Query does not allow a user to insert records it is arguably safer than giving users access to tools such as data loader. Power Query allows access to data either through a Salesforce report, or access to Salesforce Objects. Salesforce Reports use the Reports API and are limited to 2,000 records.
    Alexander Tarasevich
    December 07, 2020 6:59 pm
    Ingo, the XL-Connector mentioned here is not the native "sforce connector" that doesn't work any more. It's a new cross-platform app by Xappex (www.xappex.com). Full disclosure: I work for Xappex.

Leave a Reply