Microsoft Excel connections to SQL databases – Part II: “Calculations”

In my last blog, we discussed creating, refreshing, and updating a Connection to a SQL database from Microsoft Excel.  In this blog, I wanted to expand on the connection options available for Microsoft Excel’s connections to a SQL database through the use of calculations.

When you create or refresh a data connection in Microsoft Excel, it utilizes the connection definition and column assignments to populate the data into the appropriate columns of the Excel spreadsheet as a filtered view.  It will then query the SQL database and utilize as many rows that it requires displaying the selected data based on your query.    In the example below, when the connection is refreshed, it will populate the data queried for DOCNUMBR, CUSTNMBR, ORTRXAMT, CURTRXAM, CSPORNBR, and DOCDATE from the SQL database and display the results starting in column B3 through G3.  It will then fill in the remaining rows 4,5,6.. based on the number of results from the query.

Now that we have the connection defined let’s build on the results from our SQL query.

Formulas

If you would like to perform further calculations on the results for the SQL query, you can add formulas to the first column to the right after the SQL results.  In this case, the column that we would like to start adding our formulas in is column H.  By using the first column after the SQL query; the formula will be copied down for each row that the query adds to the spreadsheet.   If we do not utilize the first column, the formula will not be copied down when the connection is refreshed.

For example, if we add a formula to H3 – =D3-E3.  This formula will be copied down from cell H3 to the last row the SQL query.  When we refresh the data, it will further recalculate the formula results.

We can then modify the column label to define what the results of the formula are.  In this case, the column list how much of the invoice has been paid.

We can keep adding formulas as additional columns as long as they are placed in the next column available.  For example, I have added another column called Days Late in column I.