Facilitating Technology Literacy || Call Us Today! 780-240-6806|contact@WindSongTraining.ca

# Posts

Home>Posts>

## 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 [...]

By |2022-03-01T17:41:31-07:00March 1st, 2022|Data Analysis, Excel, Excel Functions, Tutorials and How-Tos|0 Comments

## 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 [...]

By |2022-07-29T17:55:10-06:00February 25th, 2022|Data Analysis, Data Model, Excel, Power Query|0 Comments

## 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 [...]

By |2022-01-31T17:38:03-07:00January 31st, 2022|Data Analysis, Excel, Linear Algebra, Matrix Multiply, Tutorials and How-Tos|0 Comments

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

## 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, [...]

By |2022-01-11T17:14:51-07:00January 11th, 2022|Excel, Tutorials and How-Tos|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