Refreshable Excel spreadsheets

Most extracts offer you the option to download an alternative refreshable version by clicking on Download as a refreshable spreadsheet instead. This is a powerful new feature we're beginning to roll out.

Where standard extracts are pre-filled with the data you've requested, refreshable spreadsheets are initially empty. After some setup, described below, you can pre-filter and refresh the data in these spreadsheets whenever you wish without having to export from snapPacking.

This enables you to customise the layout of the pivot table, or add additional pivot tables with different layouts, and then refresh the data without downloading an entirely new spreadsheet.

Getting started

When you open a refreshable spreadsheet, Excel may present you with a series of notifications and forms to complete. Some of these will only appear the first time you connect to your database and others will show up each time you open the workbook.

Following are some screen shots of these notifications with the recommended action circled in blue. The specific forms may vary a little depending on the version of Microsoft Excel you have installed.

Pre-filters and refreshing your data

On the Pre-filters sheet you'll find a list of filter options. These are useful to reduce the amount of data and time it takes to refresh your spreadsheet.

For many pre-filters you can select an option from a list of values, or simply enter a relevant code if you know it. It is also often possible to include multiple filter values by entering codes separated by commas, eg varieties = 039,036

Warning

Data sensitivity may be another important reason to consider using pre-filters. If you intend to send the spreadsheet to an external party, you'll want to ensure no-one else's sensitive data is included unintentionally. While the standard pivot filters allow you to hide data, it is still included in the spreadsheet and can easily be shown again - pre-filters provide the solution.

Once you've got any pre-filters set up, change over to the Pivot sheet. Right-click anywhere in the main part of the pivot table and select Refresh from the popup menu to refresh the data.

Tip

For longer queries you may notice a message such as Connecting to datasource near the bottom right corner of the Excel window while your data is being retrieved.

You can change the format of your pivot table by adding/removing columns, adding extra pivot tables, graphs, etc. Save the spreadsheet to your local drive.

The next time you open this spreadsheet, review the filters and refresh your pivot table (right-click anywhere on the table and select Refresh). Your pivot table will remain in the format you have saved, the data will just be refreshed.