Tutorials and How-Tos

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 (https://windsongtraining.ca/an-example-of-using-the-subtotal-command-in-excel/). We used this command to analyse the data set for the top 25 car sales by country in 2020 (https://www.factorywarrantylist.com/car-sales-by-country.html) (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

An Example of Using the Subtotal Function in Excel

The SUBTOTAL function is incredibly versatile, and the name is misleading since it can perform any of 11 different calculations not just finding totals.  The particular calculation is controlled by using a specific value for one of the arguments, which we will describe below. There are two main reasons why the SUBTOTAL function is so [...]

By |2021-12-13T13:38:39-07:00December 13th, 2021|Data Analysis, Excel, MS Office 2016|0 Comments
Go to Top