Setting up Microsoft SQL Server 2019 with a Microsoft Sample Database for BI

When learning how to use business intelligence (BI) software, we can find many examples using text or Excel files. Microsoft's Excel and Power BI also have the capability to connect to databases and extract data directly from them for analysis. Microsoft provides sample databases for download, and we can restore these databases to an SQL [...]

Dimensions and Measures in Excel Pivot Tables

Excel's pivot table is a tool that allows us to summarize and explore data held in one worksheet or Excel table, and display the results of the summarization in a second table. In this post we consider the basic workflow of creating pivot table reports by dragging fields to the appropriate sections of the PivotTable [...]

By |2022-08-09T18:01:16-06:00April 26th, 2022|Data Analysis, Excel, PivotTables|1 Comment

Introduction to Excel’s SUMPRODUCT Function

In this article we will focus on introducing Excel's SUMPRODUCT function. It is also, in part, a continuation of a previous article Excel and Some linear Algebra using Matrix Multiply to Solve Problems. We will use the same data file as the previous article (after going through a simple example first) and illustrate how to [...]

Excel Basics: Absolute Referencing with Formulas

In this article we are going to look at a basic foundational concept of Excel, namely: using absolute referencing with formulas. To illustrate this, we’ll use an example, see figure 1. (Note that the example is a pared down version of the one used in the article on the scenario manager Excels Scenario Manager Part [...]

By |2022-02-11T17:58:56-07:00February 11th, 2022|Data Analysis, Excel, Excel Basics, Tutorials and How-Tos|0 Comments

Excel and Some Linear Algebra – Using Matrix Multiply to Solve Problems

In this article we will look at using Excel and the matrix multiply function MMULT, along with the TRANSPOSE function. We will go through an example using a formula and then go through the same example with a linear algebra perspective.  This article assumes the reader has a basic knowledge of linear algebra, specifically dot [...]

An Example of Using a PivotTable Instead of the SUBTOTAL Command

Recently we posted an article in which we looked at the example of the SUBTOTAL command ( We used this command to analyse the data set for the top 25 car sales by country in 2020 ( (Figure 1). Figure 1 In this post we would like to use a Pivot Table for [...]

By |2022-01-26T17:43:37-07:00January 26th, 2022|Data Analysis, Excel, PivotTables|0 Comments

Excel’s Scenario Manager – Part of “What-if Analysis”

In this article we are going to look at Excel’s Scenario Manager. It is one of the three sub-commands in the What-if-Analysis group on the Data Tab: Fig. 1: The What-If-Analysis command and its three sub-commands The Scenario manager, like Goal Seek and Data Table, allows us to see what would happen if [...]

By |2022-01-11T17:53:49-07:00January 11th, 2022|Data Analysis, Excel, Tutorials and How-Tos, What-if-Analysis|0 Comments

Importing Too Many Rows Into An Excel Worksheet

For many of us, Microsoft Excel might be either the only tool, or a favourite tool, that we have available for data analysis. Excel can work with large amounts of data, but there are limitations on how much data we can load into a worksheet. In particular, the maximum number of rows in a single [...]

By |2022-07-29T17:56:56-06:00January 6th, 2022|Data Analysis, Excel, Power Query|0 Comments
Go to Top