7 October 2020

Better management of large databases in Excel

Peter Deegan Microsoft 365, Microsoft Excel, Office 2013, Office 2016, Office 2019, Office 365

Excel isn’t the best way to store a large database but it’s a great way to sort, filter, analyse, chart and PivotTable from it. Here’s the best way to import and update a large database into Excel.

The standard way to store a list in Excel is to import CSV’s or other data into a worksheet or table. That’s OK for smaller lists but, as the UK government has just discovered, it’s not good for larger lists or lists that are regularly updated.

The secret is the Excel Data Model. It’s an embedded and compressed database inside a workbook which can be updated automatically. A Data Model is indexed and highly optimized so Excel queries are processed very fast.

Excel Data Models are only available in Excel 2013 and later for Windows. Sorry Mac users, Excel for Mac doesn’t have PowerQuery let alone Data Models.

Make an Excel Data Model

Make an Excel Get and Transform data import from any data source you like. CSV file, web page, SQL Server, Grandma’s Shopping List, whatever.

If your data is updated with new files from other sources, check out Data | Get Data | From File … | From Folder. Then you can specify a folder from which data files will be scanned and imported into Excel with each refresh. Add new files to the folder (like CSV’s) and PowerQuery will add them in with a single click.

Once you’ve created the query, done any Transforms, Add Columns etc in PowerQuery do NOT close in the usual way.

Instead, pull-down the Close and Load menu and choose Close and Load to…

This is where you add the data into the Excel Data Model.

Choose any of the options: Table, PivotTable Report, PivotChart or Only Create Connection.

If you’re not sure which to choose ‘Only Create Connection’. That makes the Data Model but doesn’t put anything into the workbook (that’s the next step).

Click OK. Excel and PowerQuery will grab the data, index and create relationships automatically.

Now if you look in the Queries and Connections Pane there’s a nice, big, juicy Data Model ready for you.