Excel Agility: SQL and Database Techniques

Excel provides native connectivity to SQL Server through Power Query (Data > Get Data > From Database > From SQL Server Database). Enter the server name and database name, choose your authentication method (Windows or SQL Server authentication), and select the tables or views you want to import. Power Query displays a preview of the data before loading, and the connection is saved as a refreshable query—updating the data in Excel simply requires clicking Refresh All. The Query Editor lets you filter rows, select specific columns, and apply transformations before loading data into Excel, reducing the size of the dataset to only what's needed for analysis. For advanced users, Microsoft Query (Data > Get Data > From Other Sources > From Microsoft Query in older Excel) supports writing custom SQL SELECT statements directly, giving full control over joins, WHERE clauses, and aggregations at the source. Connecting Excel to SQL Server rather than exporting CSV files eliminates a manual step in the data pipeline and ensures reports always reflect current database data. For finance and operations teams who receive SQL-based data exports from ERP or accounting systems, direct database connections via Power Query dramatically streamline the reporting workflow and reduce the risk of working with stale exported data.

Excel users who work with databases benefit most from understanding four core SQL concepts. SELECT specifies which columns to retrieve from a table—equivalent to choosing which columns to include in a Power Query result. WHERE filters rows based on conditions, similar to AutoFilter in Excel—for example, WHERE Amount > 1000 AND Region = 'East'. JOIN combines data from two or more related tables based on a common key column—equivalent to a VLOOKUP or Power Query Merge operation that combines sales data with a product reference table. GROUP BY aggregates data into summaries—equivalent to a pivot table, grouping transactions by account and summing their amounts. Understanding these four concepts helps Excel users work more effectively with databases because they can request the specific filtered, joined, and pre-aggregated data they need rather than pulling entire tables into Excel and processing everything locally. It also helps when using Power Query's Advanced Editor, which uses an M language but allows SQL-style transformations, and when working with database administrators to get the right data extracts. For business professionals working with ERP systems, CRM databases, or data warehouses, basic SQL literacy significantly improves their ability to get actionable data into Excel efficiently.

Power Query and SQL are complementary tools that serve overlapping but distinct purposes in Excel data workflows. SQL is a database query language that runs on the database server—it retrieves and filters data before it reaches Excel, reducing transfer volumes and leveraging the database's processing power for aggregations. Power Query is a client-side transformation tool that operates after data is retrieved, cleaning, reshaping, and combining it within Excel. When connecting Power Query to a SQL Server source, the tool generates SQL statements (called query folding) for filtering and joining operations that can be pushed back to the server, combining the benefits of both. For operations that cannot be folded (complex transformations, multi-source combines), Power Query handles them locally. Power Query's visual interface is more accessible than raw SQL for non-programmers—the same join, filter, and aggregate operations are available through point-and-click menus. SQL is more appropriate when you need complex multi-table JOINs, stored procedures, or database-enforced business logic. For most Excel-based reporting workflows, Power Query provides 90% of the data transformation capability needed without requiring SQL knowledge, while SQL knowledge allows users to optimize data retrieval from database sources more efficiently.

Microsoft Query is Excel's legacy tool for querying external databases using SQL syntax directly. To access it, go to Data > Get Data > From Other Sources > From Microsoft Query (in Excel 2016 and earlier, it was at Data > From Other Sources > From Microsoft Query). After selecting your data source and table, you can switch to SQL view and write a custom SELECT statement with JOINs, WHERE clauses, and ORDER BY to retrieve exactly the data you need. The results load into Excel as a refreshable external data range. While Microsoft Query has largely been superseded by Power Query for most use cases, it remains useful when you need to write a specific SQL query and have the results refresh automatically, or when your organization's data team provides specific SQL queries that you need to execute from Excel. For users who know SQL and prefer writing queries directly rather than using a visual interface, Microsoft Query provides that capability without leaving Excel. Power Query is generally recommended over Microsoft Query for new workflows due to its more powerful transformation capabilities, better error handling, and active development—Microsoft Query receives no new features. Together, both tools give Excel users flexible options for connecting to relational databases.

Excel can connect to and query Microsoft Access databases (.mdb and .accdb files) even if Access is not installed on the computer, using Power Query. Go to Data > Get Data > From Database > From Microsoft Access Database, navigate to the .accdb file, and select the tables or queries you want to import. Power Query loads the data into Excel as a refreshable connection. For pivot table analysis of Access data specifically, you can also create a pivot table directly from an Access connection via Insert > PivotTable > Use an External Data Source > Choose Connection > Browse for More. This approach is particularly useful for organizations that store data in Access databases but need Excel's analytical and charting tools for reporting. Power Query's Access connector supports filtering, selecting specific columns, and combining multiple Access tables before loading, reducing data volume. Note that 64-bit Excel requires a 64-bit Access ODBC driver, which may not be installed by default—if connection errors occur, installing the Microsoft Access Database Engine is the solution. For accounting professionals who receive Access databases containing detailed transaction data, these Excel connection methods provide pivot table and Power Query analysis without requiring an Access license, a technique covered in Aurora Training Advantage's Excel Agility: SQL and Database Techniques training.