dbnet.home Bookmark and Share

The Zen of Excel Pivot Tables

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.

Using Excel with a Database

ExcelDatabase.gif

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.

    • Power Pivot 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.

    • Power View 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 to Excel.

Integrating Microsoft Project with Excel and Visio

ProjectLinks.gif

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 and SUMIF with Excel Structured Tables

Excel Structured Tables allow Excel to provide revolutionary functionality like a database with PowerBI, or with a SQL database as described in Excel-Database, or to some degree among multiple tables within Excel.
Syntax for Structured References.
Structured Reference examples.
Absolute structured references.

Structured Reference addresses columns by their column name. Used with INDEX and MATCH functions or with the SUMIF function, data in tables can be addressed by content, essentially allowing Excel to find its own way around a worksheet.

Structured Reference can address multiple tables in a workbook by name, so data can be addressed by content throughout a workbook. The combination of functions INDEX and MATCH allows for lookup, and SUMIF provides aggregation of values among multiple records.

The INDEX function can be used as:

=INDEX( area, rowNum, columnNum )

specifying a cell by row and column within the table.

MATCH syntax is:

=MATCH( soughtValue, withinRange, Exact )

and returns the item number of a soughtValue within a range. Used with a Structured Table, the number returned could be the number of a row or column containing a soughtValue.


Putting INDEX and MATCH together:

=INDEX( tableName,
MATCH( rowItemSought, tableName[inColumnName], 0 ),
MATCH( columnNameSought, tableName[#header], 0 )
)

Within a table specified by the tableName, a row is sought having the value of the variable "rowItemSought" in the column named the value of variable "inColumnName", and the value in that row is returned from the column whose name is the value of the variable "columnNameSought" in the "#header" row of the table.

The 0 "Exact" value specifies "exact" match; choices include ≤, =, and ≥, specified by -1, 0, and +1. This combination of functions behaves much like VLOOKUP or HLOOKUP, except that this lookup can be up, down, left and right, and column and row order don't matter.

INDEX/MATCH can return a single value, like the serial number of "R730 Server" for system "HW-Qual". To return the total number of Ethernet cables associated with all RNICs distributed among several servers requires a function that can find all records for "RNIC"s and add up the number of "Ports" in those records. (Start thinking or table rows as Database Records). That function is SUMIF.

SUMIF is used as:

=SUMIF ( sourceTable[keyColumn],
keyValue,
sourceTable[valueColumn]
)

The sum of the values in column "valueColumn" for all records in table "sourceTable" containing the value of variable "keyValue" in column "keyColumn" is returned.

Data Visualization

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.

PieChart

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.


Connected Chart Labels

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.

State Machines

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.

    • Stock portfolio transactions.

    • Inventory and sales transactions.

  • References


Microsoft Excel

Excel 2007 VBAExcel 2007 VBA
Programmer's Reference

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 DevelopmentProfessional Excel Development Second Edition
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.

PowerPivot Building Data Models With Power Pivot for Excel 2013
and 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 CrunchingPivot Table Data Crunching

Bill Jelen and Michael Alexander
Copyright © 2016 Pearson Education, Inc.
Published by Que Publishing

Pivot tables and VB are the most powerful features of Excel... Dives into PowerPivot and BI as well as classic Pivot Tables.

Mr. Excel Mr. Excel.

LinkedIn Microsoft Excel Users LinkedIn Microsoft Excel Users .

LinkedIn Excel Developers LinkedIn Excel Developers .

XML

XML Application Development with MSXML 4.0XML 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 Project

Microsoft Office Project 2007 Inside OutMicrosoft Office Project 2007 Inside Out
Teresa S. Stover
Copyright © 2004 Teresa Stover
Published by Microsoft Press



Excel-based Gantt by Craig Hatmaker.

Data Visualization

Visual Display of Quantitative DataThe 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 BallOracle Crystal Ball for Excel

Crystal Ball was originally developed by Decisioneering.

Palisade @RISK Palisade @RISK for Excel and Project

Palisade provides several more analysis tools for Excel including StatTools, Evolver, and PrecisionTree.

More Links

Microsoft Access

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 OutAccess 2007 Inside Out
John L. Viescas, Jeff Conrad
Copyright © 2007 Microsoft Press





Access 2003 Inside TrackAccess 2003 Inside Track
John Pierce, Paul Pardi
Copyright © 2004 Microsoft Press


This one is a non-intimidating introduction.

Excel and Access IntegrationMicrosoft 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-SQLBeginning 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 ProgrammingMicrosoft SQL Server 2008 Programming
Robert Vieira
Copyright © 2009 Wiley Publishing, Inc.
Published by Wrox an imprint of Wiley Publishing, Inc.



ADO.NET

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.5Murach's ADO.NET 3.5, LINQ and the Entity Framework with VB 2008
Anne Boehm
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.

Johnsonbaugh and Kalin Applications Programming in ANSI CApplications Programming in ANSI C
Richard Johnsonbaugh and Martin Kalin
Copyright © 1996 Prentice Hall





Object-Oriented Programming in C++Object-Oriented Programming in C++
Richard Johnsonbaugh and Martin Kalin
Copyright © 2007 Pearson Custom Publishing; Custom Editions for Northeastern University




Design Patterns

Standing on the shoulders of those who have been there before you:

Erich Gamma - Design Patterns: Elements of Reusable Object-Oriented SoftwareDesign 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
    • W3 Schools: HTML, CSS, Javascript, SQL, PHP and more; references and tutorials.
    • Codecademy HTML, CSS, Javascript, PHP, Python, Ruby and more; 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#; Javascript, JSON, XML/XAML etc. VS Web Tools
    • 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
    • Adobe Dreamweaver is a leading website designer, focused toward creative design of client-side.
  • Microsoft Expression Web 4 (free)
    • Microsoft Expression Web 4 (download) is a nice standalone HTML-CSS-PHP editor. Microsoft have replaced it now with function in Visual Studio, but a free community version of Expression Web is available that is quite functional as an editor focused on client-side design.
    • Supports up to HTML 5, and CSS 3 "draft".
    • Using Microsoft Expression Web 2Using Microsoft Expression Web 2
      Jim Cheshire
      Copyright © 2008 Que Publishing





  • Wordpress (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.
  • Javascript
  • jQuery
  • Graphics Tools
  • Perforce (free for up to 20 users)


  • Server-Side Environments

  • Hosting Alternatives
    • 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 a reference.
  • PHP (Open source, free)
    • Server-side programming for dynamic websites, form response, and database access. Straightforward, fully functional, easy to learn and use.
    • PHP.org site: documentation and download.
    • If using IIS, Download and install PHP using Web Platform Installer within IIS.
    • PHP and MySQL for Dynamic Web SitesPHP and MySQL for Dynamic Web Sites, Fourth Edition
      Larry Ullman
      Copyright © 2012 Larry Ullman
      Published by Peachpit Press




    • XDebug PHP debugger.
    • PHP Tools for MS Visual Studio: syntax coloring, Intellisense, PHP edit in HTML, supports XDebug, et al. (VS licensing for Express/Community edition does not support this plug-in.)
    • VS.php PHP development environment for MS Visual Studio.
  • 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)

  • Database

  • MySQL (Open source, free)
  • 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.