A Step by Step Guide to Use Salesforce Excel Connector: Features, Use Cases, Limitation & Alternative

Welcome to our guide on how to use the Salesforce Excel Connector! 

‍

The Salesforce Excel Connector is a powerful tool that allows you to easily connect your Salesforce account with Microsoft Excel, enabling you to import and export data between the two platforms with ease. 

‍

This can be especially useful for those who regularly work with large amounts of data and want to streamline their workflow. This guide will walk you through setting up and using the Salesforce Excel Connector, including connecting to your Salesforce account, importing data into Excel, and exporting data back to Salesforce. 

‍

By the end of this guide, you will be a pro at using the Salesforce Excel Connector to manage your data and take control of your sales and customer relationship management processes. 

‍

Let's get started!

‍

Determine your Excel Version

Before using the Salesforce Excel Connector, you will need to ensure that you have the correct version of Microsoft Excel installed on your computer. 

‍

You have to check whether you have excel is a 32-bit installation or a 64-bit installation.

‍

If you are not sure which version of Excel you have, you can check by following these steps:

‍

• Open Excel on your computer.

• Click on the "File" menu in the top left corner of the Excel window.

• Click on the "Account" option.

• In the "Product Information" section, you should see the version of Excel that you have installed.

Once you have determined that you have one of the compatible versions of Excel, you can set up the Salesforce Excel Connector.

‍

How to use the Salesforce Excel Connector

The Excel to Salesforce connector can import Salesforce data into Excel.

‍

Step 1: Open Excel, and from the Data tab, select Data > New Query > Other Sources.

‍

Step 2: Next, select the Salesforce object from the drop-down menu. Choose one of two options: Production or Custom. If prompted, enter your Salesforce credentials.

‍

Step 3: Navigator allows the user to select an SF object, load it to a table, or create a connection using the 'load to' option.

‍

You’ll have two choices here.

‍

1st option:

Create a new table to copy the data and perform some Excel calculations. This new table will contain all data snapshots taken from SF at the time of the snapshot.

‍

2nd option:

Create a connection to load the most recently updated data from SF and create graphs/calculations. Connect gives you the latest data whenever you visit SF. Therefore, your charts will be drawn based on the most recent data retrieved from Salesforce.

‍

The above approach using Salesforce and Excel Integration may have some limitations and challenges.

‍

Features of Excel Connector

XL-Connector's primary goal is to give a secure and user-friendly experience for exporting, importing, and refreshing Salesforce data using Excel. 

‍

This section will explain the effectiveness of this tool in automating data between Excel and Salesforce and show how easy it is to pull and push data (including creation, change, removal, and restoration). Additionally, we will discuss the administrator tools and other noteworthy features.

‍

Access Salesforce Data from Excel

Using XL-Connector 365, you can log in to Salesforce from the side panel. Once you have authenticated, you will also find the XL-Connector's functionalities on this panel. You can choose between a sandbox environment and your production environment when using the XL-Connector 365.

‍

XL-connector flows

“Automate Almost Any Operation with XL-Connector Flows"

‍

The XL-Connector Flows feature allows you to automate a wide range of operations within the XL-Connector, including creating and updating records and running VBA macros. 

‍

Each sheet in Excel can have multiple flow steps, giving you even more flexibility and control over your data management processes. With XL-Connector Flows, you can streamline your workflow and save time by automating tasks that would otherwise need to be done manually.

‍

Boltic, a simpler alternative to integrate your data for analysis

Welcome to Boltic, the more straightforward alternative to integrate your data for analysis! 

‍

We know how difficult it can be to work with data and how overwhelming it can feel when you have so many different sources to integrate. That’s why we created Boltic. 

‍

Boltic is a powerful integration platform that combines, cleanses, and analyses data from multiple sources. Connecting to all your databases can quickly turn raw data into powerful insights. This helps you make more informed decisions and drive better outcomes for your business. 

‍

At Boltic, we strive to make data integration and analysis more straightforward and efficient. We are committed to helping you get the most out of your data and enabling you to make informed decisions that drive value for your business. 

‍

So, if you’re looking for a simpler alternative to integrate and analyse your data, look no further than Boltic. 

‍

Get started today and see how we can help you unlock the power of your data. You can check some powerful and excellent features of Boltic below.

Completely automated:

Boltic is an entirely automated system that enables businesses to streamline operations, reduce costs, and maximise efficiency without manual intervention. It is a revolutionary new way to optimise and automate business operations.‍

Real-time data transfer:

Boltic provides real-time data transfer that is secure, accurate, and fast. Our simple and reliable solution allows businesses to quickly and easily transfer data in real-time.

100% Complete & Accurate data transfer:

Boltic provides 100% complete and accurate data transfer, ensuring a smooth, efficient process with no costly errors.

Scalable infrastructure:

Boltic provides scalable infrastructure solutions that enable businesses to scale their operations quickly and efficiently. Boltic's solutions are designed to flexibly meet the needs of companies, allowing them to stay ahead of the competition.

24/7 live support:

Boltic provides 24/7 Live Support, giving customers the peace of mind that help is always available when needed.

Schema management:

Boltic provides an easy-to-use and efficient schema management solution that enables organisations to quickly and accurately manage their data. 

‍

Our solution simplifies the process of defining and managing data schemas, providing organisations with a powerful, centralised system to store and access all their data.

Live monitoring:

Boltic provides Live Monitoring, an easy-to-use platform that allows businesses to monitor their assets and operations in real time, ensuring better visibility and control.

‍

That’s all. Let’s get back to our topic.

‍

Pull data

Easily Pull Salesforce Data into Excel with XL-Connector

‍

One of the standout features of XL-Connector is its ability to import data from Salesforce into Excel easily. You can either pull in an existing Salesforce report or build your SOQL query directly in Excel.

‍

I chose to use an existing Opportunities report for this demonstration. When importing data, you can select which report columns to export into the spreadsheet by scrolling through the list of fields or searching for specific ones. 

‍

This gives you complete control over the imported data and allows you to customise the process to meet your specific needs.

‍

One of our favourite features is the ability to automatically create drop-down menus for picklist columns. 

‍

This allows you to restrict the options in the spreadsheet to the picklist values available in Salesforce, essentially creating a validation rule within the Excel sheet. 

‍

This is just one of the many options that become available once you have selected a report to import into Excel with XL-Connector.

‍

In addition to pulling in existing Salesforce reports, XL-Connector allows you to build and use SOQL queries to import data into Excel. 

‍

This option offers more flexibility and complexity regarding the logic you can use to retrieve data from Salesforce. Once you have set your SOQL query, extra options are available to help you customise the data import process. 

‍

You can include dynamic text from a spreadsheet in your SOQL queries by using a special syntax.

‍

Push Data

Once you have the Salesforce data in a spreadsheet (or multiple spreadsheets), you can make any necessary changes to the data and update the records using the following methods:

‍

• Create/Modify

• Delete/Restore

Create/Modify

XL-Connector can quickly update large numbers of Salesforce records, with a maximum capacity of up to 1 million records. This means that, in most cases, updates will be reflected in your CRM instance within a few minutes.

‍

For example, let's say you have an Opportunities report in XL-Connector and want to update the 'Stage' column for a few Opps. 

‍

To do this, you can create dropdowns for the picklist fields, allowing you to easily select the exact values found on the Salesforce picklist field. This ensures that your updates will be correctly reflected in Salesforce and helps to avoid any issues with invalid data or mismatched values.

‍

As an admin, it's essential to be careful when updating Salesforce data, as even a small typo can result in extra time spent fixing mistakes. 

‍

In this case, we are updating the 'Stage' field for Opportunities, which is a standard field with a picklist of allowed values. Note that the picklist values for this field cannot be restricted.

‍

Once you have reviewed and verified the data that you want to update in Salesforce, you can proceed with the mass update process. To do this, you will need to choose the object that you want to update, review the mapping between the spreadsheet and Salesforce fields (making sure that XL-Connector has correctly identified the correct fields), and select the cell range that contains the data that you want to push into Salesforce.

‍

Before completing the update, you will also need to define a "flow step" name, which becomes a reusable process for future data updates. You can choose from extra options, such as the batch size, which determines how many records are updated at a time. This helps optimize the performance of the update process and ensure that it runs smoothly.

‍

After you run the update process, a new column will be added to the end of the spreadsheet, which displays the results of the update operation. If validation rules are triggered, or any other errors occur during the update, error messages will appear in this column.

‍

You will also notice that the "flow step" you defined earlier will now be reused. This means that you can easily re-run the update process at any time.

Delete/Restore

Deleting or restoring records in Salesforce using XL-Connector is a simple and efficient process. To delete or restore records, you must include at least the record ID column in your spreadsheet. You can then run the operation.

‍

Salesforce administrator tools

XL-Connector offers a wide range of features that can be useful for business and technical tasks. In this section, we will explore two of these features in more detail:

‍

• Convert Ids

• Analyse Layouts

Convert Ids

Salesforce uses two different types of record IDs: 15-character case-sensitive IDs and 18-character case-insensitive IDs. It can be confusing to track which type of ID is used in different contexts, and it's common to run into issues when using the wrong type of ID.

‍

One standard solution to this problem is to create a formula field in Salesforce that returns the correct ID type, and then use this field wherever you need to reference the ID. This works well in most cases, but it can be slow and cumbersome if you need to quickly access the IDs in a spreadsheet.

‍

It can help you quickly convert between 15-character and 18-character IDs in Excel. To use this tool, select all cells containing the IDs you want to convert, click the appropriate button, and XL-Connector will automatically convert the IDs to the desired format. 

Analyse layouts

Once you have selected the object, you will be prompted to select the layouts you are interested in importing.

‍

Once you have selected, XL-Connector will create two spreadsheets containing detailed information about the fields used and not used on the layouts.

‍

The first spreadsheet will contain a list of all fields used on the layout, along with various details about each field, such as the picklist values (if relevant), the API name, and whether the field is required or not. 

‍

The second spreadsheet will contain a list of all fields not used on the layout, along with similar details.

‍

Automatic data refresh

XL-Connector 365 includes a feature that allows you to schedule automatic data to refresh your Excel workbooks. You must save your workbook to OneDrive or SharePoint to use this feature.

‍

Once your workbook is saved to OneDrive or SharePoint, you can use it to schedule automatic data to refresh at regular intervals. 

‍

The data refresh can be scheduled to run as often as every 5 minutes, provided that the file does not exceed 5MB (a limit set by Microsoft for documents that can be opened online). 

‍

This means you can keep your workbook up-to-date with the latest data from Salesforce, even when you are not actively using it.

‍

Email notifications

It allows you to set up email notifications for the success or failure of your data refresh so that you can stay informed about the status of your data sources.

‍

This can be especially useful if you have many pivot tables that rely on data from Salesforce, as it can help you ensure that your data is always up-to-date and accurate.

‍

Use cases

There are many potential use cases for XL-Connector, depending on your needs and goals. 

Mass convert Salesforce leads

By default, leads in Salesforce must be converted one by one, which can be time-consuming and labour-intensive. 

‍

While automating lead conversion in Salesforce using custom Apex code and clear criteria is possible, some businesses may have better solutions.

‍

XL-Connector offers an alternative solution that allows you to convert up to 1 million records simultaneously, provided that the required data points are contained in the Excel file. This can be a much faster and more efficient way to convert leads, saving a lot of time and effort compared to converting leads manually.

‍

Additionally, XL-Connector allows you to create Opportunities and update existing Contacts and Accounts as needed, depending on your business requirements.

Mass update field-level security

As an admin, you may be familiar with the struggle of having to check and modify field-level security in various profiles for multiple fields. This can be a time-consuming and error-prone process, especially if you have a large number of profiles and fields to manage.

‍

Salesforce recommends using a Permission Set or Permission Set Group model to manage field-level security, but many organisations still use profiles for this purpose. If you are using profiles to manage field-level security, XL-Connector can help to make the process faster and more efficient.

‍

With the latest release, XL-Connector now includes a feature that allows you to 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 a free tool that allows you to access and manipulate data in Salesforce directly from your spreadsheets. The package is available for both Salesforce Classic and Lightning Experience and can be installed easily from the AppExchange.

‍

Once the package is installed, step-by-step instructions are provided to help you get started. With the XL-Connector add-in installed on your spreadsheet, you can download files from Salesforce using the "Get XL-File" button or by accessing the component directly.

‍

Impact

User adoption

While Salesforce is a powerful and versatile platform, it may need to be more intuitive for users who are more accustomed to working with Excel. This can lead to a lack of user adoption, which can cause Salesforce to fail within an organisation. 

‍

This can create a vicious cycle, as the failure of Salesforce can further discourage users from adopting the platform.

‍

One way to address this issue is to consider using Excel as a reporting tool for some of your colleagues.

Data quality

Maintaining the accuracy and timeliness of data in Salesforce is critical for ensuring data quality. One way to improve the quality of your data is to make it easier for users to update records in Salesforce. By removing friction from the update process, you can ensure that data is kept up-to-date and accurate.

Admin speed, alleviate admin burden

One traditional method for updating data in Salesforce is to create a report with all the required fields, export it locally, make the necessary changes and then import it back into Salesforce using tools like Data Loader, Workbench, or the Import Wizard. 

‍

While this method can be effective, it can be time-consuming and may need to be better suited to fast-paced environments.

‍

XL-Connector (and XL Connector 365) offers an alternative, more efficient and flexible solution. Because it does not live in Salesforce, XL-Connector does not require any configuration by an admin and empowers users to work at their own pace. 

‍

This can be especially useful for users who need to update data quickly and efficiently.

‍

Setup

Installing and accessing XL-Connector is quick and easy. The process of downloading and installing the XL-Connector locally on your machine should take no longer than a few minutes.

Documentation and Support

Xappex provides comprehensive documentation for all of its products, including XL-Connector and XL-Connector 365. The documentation is easy to understand and regularly maintained, so you can find the information you need quickly and easily.

Pricing

When it comes to pricing, some functionality in XL-Connector is available for free. This includes the ability to pull unlimited reports and queries from Salesforce locally.

‍

If you need additional functionality, Xappex offers paid options starting at $99/user/year.

‍

Google Sheets alternative

There are several options available if you are looking for an alternative to XL-Connector that works with Google Sheets. Here are a few possibilities to consider:

‍

Google Sheets Connector: This is a native Google Sheets integration that allows you to access data from Salesforce directly within Google Sheets. It can retrieve, insert, update, and delete records in Salesforce.

‍

Salesforce ODBC Driver: This tool allows you to connect to Salesforce using the Open Database Connectivity (ODBC) standard. It can be used with Google Sheets to access data from Salesforce and perform various operations, such as inserting, updating, and deleting records.

‍

Overall, several options are available for accessing Salesforce data within Google Sheets, and the right choice for you will depend on your specific needs and preferences.

‍

Limitations of Excel Connector

‍

• To use XL-Connector with most versions of Microsoft Office, you will need to install the Force.com Office Toolkit and then install the Excel Connector XLA.

• Excel and other Microsoft Office tools, such as Access and PowerPoint, use an ODBC driver to access Salesforce data. You will need API access enabled on your Salesforce account to use this driver.

• It's worth noting that Excel and other tools like Access have a maximum size limit, so if your Salesforce data is large or your Salesforce objects are big, they may not accommodate the data.

•  In this case, linking to the data might be slower than importing it due to the absence of advanced pipeline capabilities in these tools.

• Additionally, these tools were not designed for ETL purposes, so the back-and-forth movement of live data could be slow at times.

‍

An alternative approach - Conclusion

Boltic is a low-code platform that allows users to connect easily to and integrate with various data sources, including Salesforce.

‍

It can be used to extract, transform, and load data from Salesforce to other systems or to extract data from other systems and load it into Salesforce.

‍

One of the critical advantages of Boltic is its ease of use. Users can quickly and easily connect to data sources and build integration flows without coding or technical expertise with a simple, intuitive interface.

‍