If you work with SharePoint data in Excel you agree that performance is the key. It is easy to import large lists and connect them but the trick is to keep refresh time at a good level (minutes). Unfortunately, default Excel settings can cause serious performance issues, and figuring out why is not so simple and obvious. In this post, I compared 2 methods of loading SPO lists into Excel so you can avoid performance issues.
To import data from the SharePoint list to Excel you should use the dedicated Excel connector – From SharePoint list. This allows importing multiple lists from SharePoint Online into an Excel file. You can modify data in Excel as you wish and keep data up to date using refresh options (Excel will refresh data directly from SharePoint). You can even expand lookups columns into specific information (for example, get the e-mail address of an author, lookup id, etc.). It is an easy process but using default settings can cause serious refreshing problems.
Default import options
Let’s check default import options and how they impact refresh performance.
Open an Excel file -> go to Data tab -> select Get Data -> select From Other Sources -> click on From SharePoint List
Enter the SharePoint site address and click the OK button (we use Implementation 1.0 – default setting).
Now you can select one or multiple lists from your site. I selected Twitter List with 8000 items. At this step, you can load data to an Excel sheet or modify it before loading – let’s do this and click Transform Data.
Now you can modify columns, data types, etc. You can also expand complex columns (for example, Author) to extract specific data (by default it shows only that it is a Record). To do this just click the expand icon and select the columns you want.
I selected Title which displays the full name of an author (Created by column in SharePoint list).
You can do this operation for any complex columns. It is very useful for lookups – you can extract ids or names and create an advanced relationship between lists in Excel.
When you finish just click Close & Load to apply all changes and load data to the Excel sheet.
Now your list will be refreshed… My Twitter list refreshed for 36 minutes! Imagine that you have 10 lists with expanded lookups… It will take forever to refresh it! What to do?
Easy fix – do not use expand lookups columns. Lists will refresh very fast but you will lose the awesome feature. For me – it is not a fix. I want all features and fast refresh.
Fast refresh with lookups
The real fix is to use other options during importing the SharePoint list. Instead of Implementation 1.0 we use Implementation 2.0 (Beta). This feature is fast and has some advanced options as well. At this step, you can select if you want to import all data or only data from the default view.
Select your list(s) and click Transform Data.
Now you work with columns! This time when you click on the expand icon on complex columns (lookup) you get only 2 options – Expand to New Rows or Extract Values.
To get specific data from the lookup (e.g. email, full name) we must convert the List type to the Record type. To do this add a Custom Column and select your lookup column (I used Created By). At the end of the column name add {0} which creates a Record.
Now click on expand icon on this new Custom column and you will get all possible information to extract. This time there are only 7 pieces of information for Created By columns (the Implementation 1.0 offered about 40). This method load only minimum information which should be faster.
I selected the title which stands for Full name.
Now we can Close & Load this transformation to our Excel file and check how fast it will be loaded.
This time my test Twitter list (about 8000 items) refreshed in 10 seconds. TEN SECONDS! All with an expanded lookup column. The performance is incredible compared to the old method (and still default settings in Excel).
If you work with SharePoint data in Excel files – this is the only choice and method you should use. Avoid Implementation 1.0 because it’s extremely slow with expanded lookups and you can spend a lot of time trying to understand what’s going on. By default method 1.0 is selected and it’s easy to forget about it.
