Better management of large databases in Excel

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

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…

image 46 298x296 - Better management of large databases in Excel

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

image 47 - Better management of large databases in Excel

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.

image 48 - Better management of large databases in Excel

There can only be one Data Model per workbook.  To use multiple data models, keep each in a separate workbook and link to each.

Using the Excel Data Model

Whenever you make a PivotTable, PivotChart or analysis there’s an option “Use this workbook’s Data Model.

image 49 - Better management of large databases in Excel

What happen with UK COVID tracking and Excel?
Excel – a history of rows and columns
Sparklines make simple graphs for Excel

subs profile e1563205311409 - Better management of large databases in Excel
Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address