Excel

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...

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...

Importing Large Files into the Excel Data Model

In a previous article we demonstrated how Excel responds when we try to load too many rows of data into an Excel worksheet. The problem is that a worksheet has a maximum of 1,048,576 rows and any additional data is truncated. Large datasets must then be loaded into multiple worksheets for analysis which can become...

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...

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. In this post we would like to use a Pivot Table for...

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. The Scenario manager, like Goal Seek and Data Table, allows us to see what would happen if...

Excel Basics: How to Name a Cell or a Range of Cells

In this article we are going to look at one of the basic elements of Excel, specifically how to name a cell, and alternately how to name a range of cells. This is useful for general readability and efficiency when it comes to using functions and formulas. Naming cells is also very useful, with readability...

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...

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...