Nicholas A. Drouillard

Nicholas Drouillard

Overview

Below you will find samples of my work. I currently focus on data analysis for small buisness applications. Many of my work products feature advanced Excel applications. See Below:

***Hover your mouse over the images below to enlarge (supported by most browsers)

Excel Samples

Power Query

The Microsoft Power Query tool is a perhaps the most undervalued feature of Excel. Similar to an SQL application, Power Queries are used to obtain and manipulate data into useful information. I have found that an efficient PQ can accurately process several million rows of data at a time. Results from the queries can be added to the workbook data model and used in standard / pivot tables. Right: a sample of a query I designed recently:

Excel Power Query
Figure 1: Power Query Code Sample

Pivot Tables

Pivot tables are an essential tool that makes summarizing large data sets a breeze. Right: a simple pivot table example summarizing hospital shipping stats.

Pivot Table
Figure 2: Basic Pivot Table Sample

Pivot Tables in Action

Most often, I use pivot tabes as an intermediate step as part of broad scope reports.

Figure 3 displays a set of 8 pivot tables. The settings of these tables are set and the values update as source data is added.

Pivot Table Static Set
Figure 3: Static Pivot Table Group

Pivot Tables in Action - continued

Figure 4 shows the final report for the end users. In this case a series of nested index-matching formulas were used to locate the information needed to complete the report.

Report Example
Figure 4: Report Example Summary of Figure 3 Tables

Visual Basic for Applications

VBA is a robust object oriented programming language. Integrating VBA into your workbook can transform a boring spreadsheet into a dynamic work application.

Figure 5 is an example VBA subroutine that is used to to modify a filter for the 8 pivot tables in Figure 3. The buttons on the left trigger the appropriate subroutine when clicked.

Pivot Table
Figure 5: VBA Sample