In today’s data-driven environment, combining data from various file formats such as XLSX and CSV is a common task for Excel users. In a corporate environment, the data you need to combine is often stored in Cloud platforms like SharePoint or Microsoft Teams, rather than on a local hard drive. Note – the Files section of Teams essentially acts as a front-end for a SharePoint Library.

For those using Excel on Windows, the process to merge data from these sources is quite straightforward. You would typically navigate through the following sequence: Data \> Get Data \> From File \> From SharePoint Folder. However, Excel for Mac users face a challenge here as this specific functionality is not available in their version of Excel as of the time this tutorial was written.

This limitation doesn’t mean that Mac users are at a complete disadvantage. In this tutorial, I’ll demonstrate a practical workaround that enables Excel for Mac users to access and consolidate files from SharePoint as seamlessly as their Windows counterparts.

The need to combine data from different sources is more than a matter of convenience; it is essential for thorough analysis and decision-making. Consider a scenario where you are a Sales Manager overseeing a team. Each member’s monthly sales data is stored in individual Excel files on SharePoint/Teams. By merging these datasets, you can obtain a comprehensive view of sales performance across the business.

By following the steps outlined in this tutorial, you’ll be equipped to navigate and overcome the technical nuances of Excel for Mac, ensuring that your data works for you no matter where it’s stored.