Applying the BYCOL and BYROW Functions in Excel

Introduction

Of all of the latest functions that have been released by Excel over the last two years or so, BYCOL and ByROW are turning out to be among the easiest and best to apply. In this video, I show you how to use the BYCOL function to create a table of Descriptive Statistics that is significantly better than the equivalent table that is produced by the Data Analysis ToolPak:

• it is not messy
• it is dynamic and updates if you change the input data
• it is really easy to amend the table if you change your input data completely

Here are some images from the video that will convince you to take a look at the functions for yourselves:

Using the Data Analysis ToolPak gives you a ghastly output table like the following. Yes, we can spend some time making it pretty but why should we?

Here are the data I have used in my example in the video: we can derive descriptive statistics both for columns and for rows, in this case:

Contrast my descriptive statistics table with the Data Analysis ToolPak version and note, this is the finished article, straight from the formulas and I had to do nothing else to make it look so good:

There you are, I am sure you will find it worth your while to spend about 10 minutes of your life watching the video and seeing what is possible.

I have highlighted just the BYCOL function in this blog entry but the video includes the BYROW version and its output as well.

Duncan Williamson

21st December 2023

My video is here

You can download my working Excel file from here:
https://docs.google.com/spreadsheets/...