When most people think of Microsoft Excel, they think of graphs, formulas, financials, etc.  But by scratching slightly below the surface, one will notice that Excel is extremely powerful at more than just the everyday crunching and presentation of data.

In working with databases on a regular basis, there are tedious tasks that can take valuable time away from expanding, solidifying and tuning the database itself.  Of course there are 3rd party tools that can be used for database administration.  However, for data related issues that require large numbers of SQL statements to insert or update records, or to perform in-depth analysis of large amounts of data, I find that there is no better tool than Microsoft Excel.

SQL Statement Creation
Using simple cell formulas in Excel, complex SQL statements can be created for hundreds or even thousands of lines of specific data.  On a recent project, there were a total of approximately 71 reference (lookup) tables with a combined total over 2900 distinct reference (lookup) records of various column types and column counts.  These "static" data tables varied in number of records from 2 to over 300.  Aside from the size of the reference (lookup) data set, it was in constant flux throughout the project.  Using Excel, the task of creating and maintaining the SQL statements for this data set was made relatively simple with a high confidence level in its accuracy.  Examples of this SQL Statement creation will be introduced in PART 2 of this continuing-series.

Data Analysis
When analyzing data output from queries in a database, SQL statements can be written that will perform this task well.  However, sometimes, data analysis is an on-the-fly task where the next step in the analysis is drawn from the current view of the data.  The option to write follow-on SQL statements to further parse, sort, or pare down the data is definitely an option.  By using Excel though, a broad scope of data can be drawn into the spreadsheet and modified, colored, combined or split to find meaningful results in very short periods of time – without having to go back to the SQL editor.  Of course, this data set size is limited to 65536 rows of data- the maximum that an Excel spreadsheet can hold.  Examples of Data Analysis will be introduced in PART 3 of this continuing-series.

Custom Excel Functions
Along with the intrinsic functionality of Excel, there is a powerful back-end to Excel that is seldom used.  When in an Excel spreadsheet, try pressing Alt+F11, you will be brought into a development environment for Visual Basic for Applications (VBA).  This language, although not a mainstream development language now, is a simple solution to create custom cell functions to augment the existing toolset already in Excel.  Many people have used =Sum(A1:A77), =Avg(G3:K3), =Min(F7:L44), =Max(C2:C2000), etc.  Well using the VBA environment, custom functions can be created that perform exactly to your needs and will be called in a similar fashion to those functions shown above.

As an example of this powerful functionality, try the following:
1. Open an Excel spreadsheet

2. press Alt+F11 (VBA environment then opens)

3. in the explorer window (typically on the top left), right click on “Microsoft Excel Objects” and select “Insert>Module” (blank module opens)

4. <optional>  enter the following phrase on the top line in the module:
                       Option Explicit
(this will prevent coding errors caused by misspelled variables )

5. Copy and paste the following code into your new Excel module workspace (do this somewhere below the "Option Explicit" line).  Note that it will color code for easier reading once inside Excel:

'===============================================
Public Function getSchoolLevel(intAge As Integer) As String

'Comments start with a simple single quote on the line

'Redirect Errors to the error handling section of the function
On Error GoTo errHandler '(optional - for simple functions, not necessary)

'declare variables (necessary if "Option Explicit" is within the module)
Dim strReturnVal As String

'Simple IF statements
If intAge < 5 Then
    strReturnVal = "Pre-School"
ElseIf intAge < 13 Then
    strReturnVal = "Elementary School"
ElseIf intAge < 15 Then
    strReturnVal = "Middle School"
ElseIf intAge < 18 Then
    strReturnVal = "High School"
Else
    strReturnVal = "College (hopefully)"
End If

'Set the function name to the variable that holds the return value
getSchoolLevel = strReturnVal

Exit Function 'Exit function prior to the error handling section
errHandler:
    '<optional> - Return a value even when errors are encountered,
    'else execution will stop and the user will be redirected to this module
    '(which is often preferable for debugging purposes)
    getSchoolLevel = "Unknown"
End Function
'===============================================

6. Click the Save button and close this code window.

7. In the Excel spreadsheet, type the values 1 to 20 in column A, rows 1 to 20

8. In cell B1, type the following and then press enter: 
=getSchoolLevel(A1)
 (note that “Pre-School” should appear in cell B1)

9. Single click again in cell B1, select copy.

10. Single click in cell B2.

11. While holding the Shift key, single click in B20 (the entire set of cells should highlight).

12. Select paste.

13. Note that there should now be values generated by your custom function in cells B1 thru B20.

You can dispute the school levels generated (trivial point!), but you surely cannot dispute the power of Excel as a customizable tool.  Some people may look at this and say, this is just VBA, nothing special.  Aside from easing your data burdens, I am sure that people looking over your shoulder will ask, “How’d you do that?” followed by, “Wow – that would work great for my task of …”.  It happens all the time!

In Parts 2 (SQL Statement Creation) and 3 (Data Analysis) of this continuing series, this customizable functionality will be put to use in database specific tasks.