Most books covering Excel Pivot Tables pretty much focus on construction of Pivots. I use Pivots every day to organize and manage data critical to planning
and operation in my function. That data changes constantly, new kinds of data are added, and new reporting is needed. I've found there is a level of design above the
technical construction of the Pivot Table that helps make the Pivot Tables themselves more straightforward to create, and helps with better evolution and reporting. I
think this embodies the Zen of Excel Pivot Tables.
Excel can be used as a data source, and also as a reporting client with a database such as Microsoft Access, Microsoft SQL Server,
or MySQL. This lets you combine data from multiple ranges in many ways, greatly extending the capabilities of an Excel solution. In addition, data
can be pulled from a database query or database table into Excel as a table for analysis by Excel, or to use Excel's Pivot Tables or Pivot Charts
for an excellent reporting capability. There are several ways to do this.
First, an article on this website showing a method using built-in capabilities of Excel and MS Access as illustrated above. This requires little or no
programming. The approach is extensible, and some code snippets are provided to automate certain functions.
Linking Excel and Access using native capabilities in both.
Excel-Access assessment: Live access between Excel and MS Access allowing cascaded JOINS among Excel ranges, and with SQL tables in Access. Excel Structured Tables can be mapped to named ranges using VB on cell change, so Excel tables can also effectively be used in JOINs. Supports basic SQL SELECT, inner and outer JOIN, UNION etc; but not full T-SQL. Access is a desktop database and must be installed on the user's system (available component of MS Office), although the database file may be stored on a shared server. Might become interesting to see how Microsoft evolves support of MS Access and MS Query, as it evolves MS SQL Server, Azure SQL Database, and Power Pivot.
Microsoft Office has added focus to "Power BI", placing Business Intelligence and Self-Service BI directly in the hands of its users. These tools link
Excel with databases using methods similar to those described on this website. Excel provides native tools to create pivot tables and to create Excel data models
with relationships among data elements. An add-in tool called Power Pivot provides capabilities to manage more complex data relationships. More add-ins
to Excel provide additional capability: Power Query provides access to public and internal cloud sources and to local databases including MySQL, Microsoft SQL Server, and
Microsoft Access; and Power View and Power Map along with Power Pivot provide data analysis and display. Availability of function among the various Office configurations
requires a decoder ring, below. The short version is that full functionality is achievable using Office 365 Pro Plus, Office 2013 Professional Plus, or Standalone Excel 2013.
is an add-in for Office 365 Pro Plus, Office 2013 Professional Plus, and Standalone Excel 2013.
Power Pivot is available as an add-in for Excel 2010,
although there are some incompatibilities with Excel 2013 versions.
is an add-in for Office 365 Pro Plus, Office 2013 Professional Plus, Standalone Excel 2013 and Sharepoint Server.
Power Query is an add-in for
any Office 365 version or Excel 2013 version and supports Excel data access from many data sources.
Power Map is in Office 365 Pro Plus.
Power Map Preview is available as an un-supported add-in for Office 2013 Professional Plus or Standalone Excel 2013.
Book references below.
PowerPivot assessment: Inclusion of Excel Tables in queries currently appears much more limited than use of MS Access with SQL inner and outer
joins and cascaded queries. Capability to push Excel data to SQL Server either directly or via a linked server seems currently broken at least with x64 SQL Server,
there are many reports on the internet of users with problems as of early 2015. PowerPivot requires learning a complex proprietary system. Possibly pushing data to
SQL Server via ADO will be a path, and will allow use of T-SQL in SQL Server, but implementation is far more complex than linking through MS Access, see below.
MySQL for Excel, is an Excel add-in provided by MySQL to import data into Excel.
Power Query also provides access to MySQL
databases from Excel.
MySQL for Excel assessment: supports import, but not live linkage to and from the SQL database. The add-in also adds hidden names into Excel that
are interpreted as Excel 4 macro references and interfere with pivot table refresh and xlsx file saves.
Microsoft ADO gives full power to link Excel with an ODBC or OleDb database such as SQL Server. Both Visual Basic programming in Excel, and SQL
programming at the database are required. The www.excel-sql-server.com website provides very straightforward
code for this, using VB code running in Excel to update database tables and to perform database queries. The site also provides an add-in, with both a free
Community edition and a paid Commercial edition.
Connection to ODBC database using ADO from Excel.
ADO code from Erlandsen Data Consulting.
ADO assessment: Full live linkage can be achieved. Using ADO requires substantial code development described in the references in this paragraph.
The reason you might choose this path is to be able to use SQL Server, and T-SQL within it.
The website SQL Server Helper provides code for a SQL database to query an Excel worksheet, running SQL
code in the database.
Part 1: Querying an Excel Worksheet
Part 2: Querying an Excel Worksheet Without Header and With Mixed Column Data Type
Part 3: Importing an Excel Worksheet
Transfer between Excel and Access, by Ken Snell.
Using MSQuery to transfer between Excel and any ODBC database
, by Wojciech Gardziński and Krzysztof Rumiński. The translation can be a little rough, but the example
is understandable enough.
SQL Server links to Excel files via Microsoft Jet 4.0 OLE DB Provider, similar to MSAccess table links
Microsoft Project is widely used by Program Managers to represent a project execution plan and to quantify a schedule. Project can output dates that can be integrated into a Build Plan
to tie together dates with quantities and configurations, or into a Budget plan with date-dependent records describing expenditures, suppliers, build-items and so forth.
Article: Project Links.
Index/Match Instead of Vlookup
On an Excel worksheet, the Match function can be used with the Index function to look up a value faster and with more flexibility than the Vlookup function. Basically, Match is used to determine the row and/or column,
as arguments to the Index function. For example, =Index(searchTable, match(searchColumnVector, searchRowValue, searchType), 2) is roughly equivalent to =Vlookup(searchRowValue, searchTable, 2). The searchType
argument allows for exact or close match, with greater flexibility than Vlookup. Match could also be used to determine the column, or both row and column. Take care to adjust the row or column value
returned by Match, to account for title rows or columns if they are included in the searchColumnVector range.
A limitation of Vlookup is that it can only offset to the right, and that search values must be ordered low to high. In the examples above, a custom Vlookup is provided in VBA that allows an exact match to be
found within an un-ordered search vector, and a value picked from a left or right offset anywhere on the worksheet. Can Index and Match be used to provide the same functionality, with faster execution? Certainly can.
First, in a searchVector find a matching searchValue: searchRow = MATCH(searchValue, searchVector, searchType). Set searchType to 0 for exact match. The searchVector is assumed to be vertical, in a column.
Next, get a reference to the cell containing the searchValue: searchCellRef = INDEX(searchVector, searchRow,0). The column argument is set to 0, instructing the Index function to return a reference, not the cell value.
That will allow it to be used in an Offset function to select the offset cell we want to return the value from.
Then choose the offset cell. Using the Offset function will return a reference to that cell, not its value but we'll extract its value in the next step. So offsetRef =
OFFSET(searchCellRef, sameRow, colOffset, sizeRows, sizeCols). The
argument sameRow is set to 0 so that the offset cell will be in the same row that the searchValue was found. the colOffset argument states how many columns to the left (negative) or right (positive) the offsetCell
is located from the searchVector. The sizeRows and sizeCols arguments are each set to 1, to return a 1x1 range, a single cell.
The Offset function returns a reference to the cell we're looking for; if the value will be numeric, get the value: searchOffsetValue = VALUE(offsetRef). If the lookup value in the OFFSET cell is a string (for example, a task name),
then this VALUE function call should be eliminated.
Handle errors: the value may not be found. searchOffsetValueProtected = IFERROR(searchOffsetValue, errorReturn). Set errorReturn to "" or 0 or "-" or NA(), an error value meaningful to the worksheet using this function
in case of an error.
Putting all those together, general form is
searchOffsetValueProtected =IFERROR(VALUE(OFFSET(INDEX(searchVector, MATCH(searchValue, searchVector, exactMatch), colRef), sameRow, colOffset, sizeRows,sizeCols)), errorReturn) to return a numeric value, or
searchOffsetValueProtected =IFERROR(OFFSET(INDEX(searchVector, MATCH(searchValue, searchVector, exactMatch), colRef), sameRow, colOffset, sizeRows, sizeCols), errorReturn) to return a text value.
Using with the code examples above
=IFERROR(VALUE(OFFSET(INDEX(uniqueIDindex, MATCH(uniqueIDvalue, uniqueIDindex, 0), 0), 0,uniqueToStart-1, 1, 1)), "") to return the start-date value, numeric,
for the task associated with uniqueIDvalue, or
=IFERROR(OFFSET(INDEX(uniqueIDindex, MATCH(uniqueIDvalue, uniqueIDindex, 0), 0), 0, uniqueToName-1, 1, 1), "") to return a task name as text.
In the example, UniqueIDindex is the range name of a vertical searchVector containing mpp task UniqueID values, and UniqueIDvalue refers to a cell containing the UniqueID of the mpp task whose information is sought. UniqueToStart
is the column offset from the UniqueIDindex vector to the cell in the same row as the searchValue containing the Start Date of the UniqueID specified by the searchValue. The UniqueIDindex and UniquetoStart are named values in Excel
set up in the code importing the mpp data, referenced above under "Integrating Microsoft Project with Excel and Visio".
Now you have the skills to put together really powerful and customizable tools to manage project logistics and financials, using Excel with a database
to use joins linking Excel tables with data from other programs. As the database develops and changes to reflect your execution history and plan adjustments, you will find that you can use data-mining
to get information to better manage.
This comes in the form of well-designed pivots to organize and summarize data; SQL SELECT and JOIN to combine related datasets and perform
complicated data selection and organization. And Data Visualization which will help you understand the data as you develop management structure, and to
explain the data-driven management structure to others.
On Data Visualization, I gave a reference to Tufte below, a key figure in Data Visualization. Also see Excelcharts.com by Jorge Camoes, and
John Walkenbach's The Spreadsheet Page. Stephen Few is a key mentor in data visualization and runs Perceptual Edge, a blog focusing on
Visual Business Intelligence.
My charts haven't appeared yet on Kaiser Fung's Junkcharts blog. But you should go there, to see many examples and discussion of charts that do or don't work. I'm sure
I will get there, though, because I have certainly used pie charts, which are not popular among Visualization experts. Just make sure your analysis remains faithful to the data and that the data, rather than your presentation
method, remains the story. And even pie charts have their use, depending on the point you are making. Pies are good at illustrating relative proportions of component parts within a whole collection of parts.
Having said that, and for proper use only, here is the One Pie To Rule All Pies. This Pie Chart is done in Excel, but you won't find it in the menus. Nobody else will have these graphics,
and the brilliance of your analysis will pop right off the page! Article: Pie Chart.
You can put a label on an Excel chart tied to data and computation on a spreadsheet. For example, you can reference assumptions, or a
total that may not be displayed explicitly in the chart data such as a total on a pie chart. Article: Connected Chart Labels.
Using Excel Tables in VBA
Results of a database query may be returned to an Excel Table to be processed by VBA code running in Excel. Jan Karel Pieterse has a very informative article on his JKP Application Development Services
site describing code for using Excel Tables, followed by useful Q&A. See The Spreadsheet Page.
Data retrieved from a database is in the form of multiple records which may represent a set of sequenced actions. The State Machine design pattern provides a
well-structured way of scanning and acting on sequential records.
Time sequenced, time-stamped, sampled data from electrical equipment.
Edge detection, filtering and noise rejection, and pulse detection.
Calculation of current, impedance, charge accumulation.
Mechanical acceleration, force, or flow analysis.
Financial or Inventory.
Excel 2007 VBA
John Green, Stephen Bullen, Rob Bovey, Michael Alexander
Copyright © 2007 Wiley Publishing, Inc.
Published by Wrox an imprint of Wiley Publishing, Inc.
Best programming reference I've found. In depth, advanced. Focused on VBA programming.
Professional Excel Development
Rob Bovey, Dennis Wallentin, Stephen Bullen, John Green
Copyright © 2009 Pearson Education, Inc.
Published by Addison-Wesley
Definitive on design of Excel applications including VBA and Excel constructs. Best description of many
useful techniques such Dynamic Lists.
Building Data Models With Power Pivot for Excel 2013
PowerPivot for Excel 2010
Copyright © 2011, 2013 Marco Russo and Alberto Ferrari
Marco Russo and Alberto Ferrari
Published by Microsoft Press
PowerPivot for Excel packages a solution for integration of Excel with relational databases including SQL Server, MS Access and others, in a manner very much like the technique described on this website.
Pivot Table Data Crunching
(versions for 2007, 2005)
Bill Jelen and Michael Alexander
Copyright © 2007 Pearson Education, Inc.
Published by Que Publishing
Pivot tables and VB are the most powerful features of Excel...
LinkedIn Microsoft Excel Users
LinkedIn Excel Developers
XML Application Development with MSXML 4.0
Stephen Mohr, Steven Livingstone, Darshan Singh, Danny
Ayres, Michael Coming
Copyright © 2002 Wrox Press, Inc.
Old, and XML is now implemented in .NET, but this is a good
overall reference and much translates to use in .NET.
Microsoft Office Project 2007 Inside Out
Teresa S. Stover
Copyright © 2004 Teresa Stover
Published by Microsoft Press
Excel-based Gantt by Craig Hatmaker.
The Visual Display of Quantitative Data
Edward R. Tufte
Copyright © 2001 Graphics Press
The point of a quantitative graphic: the revelation of the complex.
Charting complex information can be an art form, quantitatively illustrating regular structure of diverse phenomena. This is what shows true understanding, much more powerful than just illustration or enumeration.
Also see Excelcharts.com for a blog and tutorial on Excel data visualization using charts and dashboards. Tufte meets Excel, as they say on the site.
Monte Carlo Simulation
It is frequently useful to build a model using stochastic variables, and to use Monte Carlo simulation to create and analyze the range of probable outcomes. Following are add-ons for use with Excel and Project.
Oracle Crystal Ball for Excel
Crystal Ball was originally developed by Decisioneering.
Palisade @RISK for Excel and Project
Palisade provides several more analysis tools for Excel including StatTools, Evolver, and PrecisionTree.
Access is well-integrated into MS Office and is fully-capable for many user database applications. Includes full UI support and VB.
You can use SQL, but for many applications it is not needed. Access
is a member of Microsoft Office.
Access 2007 Inside Out
John L. Viescas, Jeff Conrad
Copyright © 2007 Microsoft Press
Access 2003 Inside Track
John Pierce, Paul Pardi
Copyright © 2004 Microsoft Press
This one is a non-intimidating introduction.
Microsoft Excel and Access Integration: With Microsoft Office 2007
Michael Alexander and Geoffrey Clark
Copyright © 2007 Wiley Publishing, Inc.
Microsoft SQL Server
SQL Server is a full-function database. It can be integrated with Excel, or most frequently used with a programmed client such as a .NET program.
You need SQL for this. SQL Server is a separate product, but SQL Server Express is free.
Beginning T-SQL with Microsoft SQL Server 2005 and 2008
Paul Turley, Dan Wood
Copyright © 2009 Wiley Publishing, Inc.
T-SQL, an extension of ANSI SQL implemented in SQL Server but not in Access, adds significant power to SQL Server.
Microsoft SQL Server 2008 Programming
Copyright © 2009 Wiley Publishing, Inc.
Published by Wrox an imprint of Wiley Publishing, Inc.
ADO.NET provides services for programmed connection between a database (Access, SQL Server, or other) and a client (Excel, or a .NET client program).
Murach's ADO.NET 3.5, LINQ and the Entity Framework with VB 2008
Copyright © 2009 Mike Murach & Associates, Inc.
Programming Languages: C/C++
A little off-topic, but as long as we're recommending books, here are two books on C/C++ for hardcore programmers. I have read quite a few books on
C/C++ and these were the best by far. Read the books, stick with it, do the problems, and you WILL know how to use the languages. These are later editions than I used.
Applications Programming in ANSI C
Richard Johnsonbaugh and Martin Kalin
Copyright © 1996 Prentice Hall
Object-Oriented Programming in C++
Richard Johnsonbaugh and Martin Kalin
Copyright © 2007 Pearson Custom Publishing; Custom Editions for Northeastern University
Standing on the shoulders of those who have been there before you:
Design Patterns: Elements of Reusable Object-Oriented Software
Erich Gamma, Richard Helm, Ralph Johnson, John Vlissides
Copyright © 1994 Addison-Wesley Professional
Web Design Environments
Development Tools and Client Side
References and Tutorials
- Udemy 30,000 courses online including HTML, Java, Python, many more.
- Video2brain Website, design and creative courses.
- W3 Validators check compliance of your website pages with HTML and CSS.
Microsoft Visual Studio (Community Edition is free)
Microsoft Visual Studio is a comprehensive development environment: all languages, client-side, server-side, database. HTML, CSS, PHP, LESS, CoffeeScript, Python, Django, T-SQL, VB, C/C++/C#, F#;
- Visual Studio Community Edition is free for personal use. It is full-function Visual Studio 2015, the current edition.
- Visual Studio supports plug-in tools for development of PHP, Python, SQL and more, including syntax coloring, Intellisense, debug etc. (Community edition licensing does not allow some plug-in extensions, however.)
- Adobe Dreamweaver is a leading website designer, focused toward creative design of client-side.
Microsoft Expression Web 4 (free)
- Wordpress is leading example of a website Content Management System. Wordpress.org provides tutorials, software, plug-ins, themes etc.
Many add-on themes and plug-ins are available for Wordpress, both free and commercial. Many are listed at Wordpress.org.
ThinkupThemes is a good example of a provider of commercial Wordpress site themes.
- Wordpress.com provides hosting for Wordpress sites/blogs. You can also host a Wordpress client site on most ISP or cloud host sites.
Other popular CMSs (See extensive list) include
Drupal, Joomla!, and WiX.These systems are based on a wide variety of open source and proprietary infrastructures including PHP, HTML 5,
Python, Java, Perl, ASP, Ruby on Rails and others. Most CMSs support MySQL and some also support MS SQL Server.
Perforce (free for up to 20 users)
Hosting alternatives include: Cloud services (Microsoft Azure, Amazon Web Services),
Apache Web server (Open source, free), ISP Website Hosting.
A production website would probably be hosted on a corporate site, a cloud provider or an ISP Website Host (which may be based on Apache or
IIS). A developer can also install and use IIS or Apache as a local test server for site development.
Microsoft IIS (free) is included in Windows 8. How to install and configure IIS. (Download link).
You will probably set up your website on your computer in a directory different than the default set up when IIS is installed. To do that, you set up a Virtual Directory under the Default
Web Site in IIS. When doing so, set up Read or Read and Execute permission for users "IIS AppPool\DefaultAppPool", and "IUSR". Here is
PHP (Open source, free)
Python and Django (Open source, free)
- Server-side object-oriented programming language for dynamic websites, form response, and database access. There is a tutorial on the Python.org site.
- Python download link.
If using IIS, Download and install Python using Web Platform Installer within IIS.
- Configuring Python on IIS.
- When specifying directory paths when configuring Python in IIS, make sure to use DOS 8.3 names. Fully spelled-out Windows names will prevent functionality.
- Python Tools for Visual Studio
Django (Open source, free)
MySQL (Open source, free)
- MySQL is basic SQL. Just about every Cloud service and ISP supports MySQL.
- MySQL.org site.
- MySQL download link.
If using IIS, Download and install MySQL using Web Platform Installer within IIS.
Microsoft SQL Server 2014 (SQL Server Express is free)
SQL Server is a full function database supporting Transact-SQL (T-SQL). Available on Microsoft Azure cloud service as Azure SQL Database, available on some other Cloud
and ISP services for an additional fee.
- SQL Server Express is free.
If using IIS, Download and install SQL Server using Web Platform Installer within IIS.