The best and most common way to connect Excel to a Postgres database is with the Postgres ODBC driver. Once the driver is installed and configured, data can be imported from Postgres to Excel using several different methods. This post covers step-by-step instructions for connecting Postgres to Excel and the 3 top ways to import Postgres data. We also review the pros and cons of connecting a Postgres database to Excel.
Table of Contents:
- Connect Excel to a Postgres Database with an ODBC driver
- The top 3 ways to import data from a Postgres database to Excel:
- What is Postgres?
- The pros and cons of connecting Excel to a Postgres Database
- Pros vs Cons
- Row Zero
- Conclusion
Connect Excel to Postgres Database with an ODBC Driver
The first step in connecting Microsoft Excel to a Postgres database is downloading and installing the PostgreSQL ODBC driver. The purpose of the PostgreSQL ODBC driver is to provide a standardized interface for applications to access Postgres databases regardless of the programming language or operating system used by the application.
1. Download the PostgreSQL ODBC Driver
In order to download the driver, visit https://odbc.postgresql.org, click on the link to go to the download site, click on 'msi' and then download the most recent version that matches the version of windows running on your computer (x32, x64, or x86). You can also click on the link here to go straight to the ODBC driver downloads page.
2. Install the PostgreSQL ODBC Driver
Once you've downloaded the correct drive version, unzip or decompress the file in your downloads folder. Inside, you will find an executable file type (e.g. psqlodbc_x64.msi). Double click on the executable to install the driver and follow the steps in the installation wizard. 
3. Configure and Add the Postgres ODBC Driver in Windows
In Windows, go to the start menu and search for 'ODBC Data Sources.' Select and open the ODBC Data Source Administrator. 
Navigate to the 'Drivers' tab and look for your PostgreSQL driver in the list. If you don't see it, it has not been installed correctly and you should try repeating the previous step. Assuming your driver is visible in the list, click on the 'User DSN' or 'System DSN' tabs, click 'Add,' select the PostgreSQL driver from the list, and click 'Finish.' 
Next add the connection credentials for your postgreSQL database. You will need to match the settings on your database. For example, if the database allows encryption, you will need to toggle the 'SSL Mode' value to match it. Once credentials have been entered, click 'Test' to test the connection. If the Test is successful, click 'Save.' The PostgreSQL driver will now show up in either System DSN or User DSN lists. 
The Top 3 Ways to Import from Postgres to Excel
1. Import a Postgres Database Into Excel Spreadsheet Cells
In Excel, click on Data > Get Data > From Other Sources > From ODBC. Then select the PostgreSQL option from the drop down menu and click 'ok.' Enter the user name and password and click 'connect.' 
Next navigate to the table you wish to import in the menu on the left. Excel will try to display a preview of the data but this can be slow to load. If you aren't familiar with Excel's performance issues, you can read more about them in Why is Excel slow. Once the preview loads or you are ready, click 'Load.' 
Depending on the size of your Postgres data set, loading the data might take some time. If your data set is bigger than the Excel row limit, Excel will alert you with an error message and only load the first 1,048,576 rows. There are other options in Excel for bigger data sets that are covered in subsequent sections. 
2. Import Postgres to Excel with Microsoft Query
In Excel, click Data > Get Data > From Other Sources > From Microsoft Query. Excel will then display a dialog box in which you can select the Postgres data source and click 'ok.' 
The Microsoft Query editor will then open and display a list of tables. Select the table you wish to query. Use the Microsoft Query editor to write a query or select the tables to be imported. Once the data is selected, click File > Return Data to Microsoft Excel to import the data to Excel. Then choose whether to import the data as a Table, Pivot Table, or Pivot Chart, and the location. 
3. Import From Postgres to Excel With Power Pivot
In Excel, Click on Power Pivot in the top line menu and then click 'Manage' in the upper left corner. Then click Get External Data > From Other Sources. When the Table Import Wizard Opens, select 'Others (OLEDB/ODBC) and hit 'Next.' 
When the Specify a Connection String window opens, click on 'Build.' Under the 'Provider' tab, select 'Microsoft OLE DB Provider for ODBC Drivers' and hit 'Next.' Then on the 'Connection' tab, select PostgreSQL from the drop down and login credentials. Test the connection before clicking 'OK.' 
Back on the Table Import Wizard, click 'Next,' choose the method of import by either selecting from a list of tables or writing a query. Then click 'Finish.' Select the method of import and data will be loaded into Excel.
What is Postgres?
Postgres, short for PostgreSQL, is an open source object-relational database management system (ORDBMS). It was developed at the University of California Berkeley in 1986. PostgreSQL is popular because it is open source (free) and runs on all major operating systems, including Linux, Unix, Mac OS, and Windows. It supports text, image, sound, and video files. Lastly, it has programming interfaces for many of the more popular languages, like C/C++, Java, Perl, Python, Ruby, and others. You can find all the details about PostgreSQL at postgresql.org.
The Pros and Cons of Connecting Excel to a Postgres Database
Pros
Real-time Data
Connecting Microsoft Excel to Postgres database enables financial and accounting models to be built on production or operational real-time data stored in Postgres. Rather than static models or analysis that are stale as soon as they are built, connecting to a Postgres database ensures models are always up-to-date.
Refresh Live Data Models
Once you connect Postgres to Excel, you have a live connection and it is easy to re-run or refresh the Postgres query to pull the latest dataset into the spreadsheet without having to recreate another static analysis. Live data models are time savers and keep your analyses fresh.
Dashboards
When you connect Postgres to Excel, it is easy to turn a simple analysis into a dashboard or live model. Simply refresh the queried data from Postgres and all graphs, pivot tables, and spreadsheet models will update.
Cons
Security
Importing data from Postgres to Excel poses an information security risk because the data is moving from a cloud data warehouse where activity is traceable and behind layers of security to a locally stored Excel file stored on a computer. Once the data is in Excel, that data goes wherever the computer goes. The computer could be lost, stolen, or shared. The file could also be emailed to personal who wouldn't otherwise have access to the data. A more secure option is a cloud based spreadsheet, like Row Zero
Data Set Size
While Microsoft Excel is a great tool it cannot easily support large data sets. Microsoft Excel will often slow down or crash as data set sizes reach tens or hundreds of thousands rows Excel has a 1,048,576 row limit, which we've written about here. It is common for data sets stored in databases to be quite large. To import data from Postgres to Microsoft Excel, the postgres query will need to trim the data down to a size Microsoft Excel can manage. There are options within Microsoft Excel, like data model and power query, to enable work with large data sets but they don't allow users to see all the data or use the same spreadsheet flexibility Excel supports with smaller data sets. If the data set is large and a spreadsheet UI is the preferred way to analyze data, try Row Zero, a blazingly fast spreadsheet designed for big data.
Sharing
Microsoft Excel is offered in two versions, desktop and Excel 365. Desktop Excel is the traditional version everyone thinks of. The program runs on local computers and is fast and performant. Desktop Excel is difficult to share due to the fact that it runs on users' local computers. It is common for teams to email around multiple different versions creating static one off analyses that need to be updated and versioned. The workbooks can get out of sync if two users make updates to the same version. Companies often rely on cloud storage repositories like Sharepoint to manage all the files with checkout and check-in logic that preserve spreadsheet integrity. Excel 365 is the new version of Excel that makes sharing easy, though it is not commonly used for big models or data analysis because it is slower than desktop Excel. If a fast powerful spreadsheet with the ability to share across team members is desired, Row Zero is powerful spreadsheet that runs in the cloud and can be shared with anyone
Complexity
Connecting data sources to Microsoft Excel is not as easy and intuitive as one might think. There are often complications that can be hard to resolve. The information below does its best to help prevent errors that will inevitably occur and there is plenty of content online to help resolve each one.
Row Zero
Connect Postgres to a Row Zero Spreadsheet
Row Zero is a new spreadsheet designed for modern data analysis. It can process 100M+ rows of data, connect to any data source, and support real-time collaboration. To connect your own PostgreSQL database to Row Zero, check out our instructions here. Row Zero makes it much easier to get data from a postgres database into a spreadsheet and has more power than Excel to easily handle big data sets and complex analyses. To get started, watch the video below and connect your Postgres instance to Row Zero for free.
Conclusion
In this post we reviewed the 3 most common ways to import data from a Postgres database to Microsoft Excel. The main benefits are the ability to build models off of production data and to refresh the data import without having to re-build the model. The main issue to consider when importing any data into Excel is the data set size. Importing data straight into the spreadsheet will be limited to 1,048,576 rows and even with Power Pivot and Microsoft Query's abilities to handle bigger data sets, Excel will run slower, the bigger the data gets. Once the data is in, it is not easy to share analysis with team members with Excel. Excel 365, the web based version of Excel, has better sharing features but is even less powerful than desktop Excel. Additionally, Excel's big data features don't allow for the usual flexibility and complexity a spreadsheet affords because they limit interactions to data tables and pivot table type analysis. Row Zero is a better option for connecting a spreadsheet to a database and working with a big data set.
If you use another cloud data warehouse or blob storage, Row Zero also connects to Snowflake, Redshift, and S3.