Welcome to the Advanced Track!
Watch all tutorials seamlessly on our YouTube playlist by clicking the left button.
Or, scroll down and watch the videos individually.
1. Pivot Table: Spreadsheet here!
As its name aptly states, a pivot table allows data to be pivoted or turned in different angles so as to see the data differently. An immensely powerful tool, it is also often one of the least understood. Discover how to create one.
2. Slope & Intercept
The SLOPE and INTERCEPT functions will calculate elements of a line! Why are they useful? As you’ll discover in the next video, these functions will allow you to create a best fit line and improve your prediction skills!
3. Scatter Plot & Best Fit Line: Spreadsheet here!
When you have two data-sets and are curious about their relationship, a good first step is to plot their points along a scatter plot. Here we’ll discover how to create a scatter plot. Here we’ll also understand what a best fit line and learn how to create one as well!
4. Predictor Application: Spreadsheet here!
Using the skills you learned above, you will also master the LINEST function and see how we can use the best-fit line to predict what score a student can expect given the number of study hours he or she put in.
5. Vlookup
The VLOOKUP or Vertical Lookup is an extremely practical function. I’d wager that this function is one of the most popular ones within the Lookup function. Discover it here
6. Hlookup
A sibling of the VLOOKUP function, the HLOOKUP function stands for Horizontal Lookup. The concept here is parallel to the VLOOKUP function, the difference being that this function looks horizontally across rows before returning an output located x number of rows below the search entity row.
7. Column & Row
These are two basic functions. You may be wondering why this tutorial is located in the Advanced section rather than the Beginner section. Well, while these functions on their own are straightforward, they are often used in conjunction with more advanced lookup functions, including the VLOOKUP and the HLOOKUP.
8. Random Prize Drawing: Spreadsheet here!
Finally! We’ve got an application that will tie together some of the concepts we’ve learned earlier. Here, discover how we can incorporate the RAND function (introduced in the Intermediate II Track) along with the VLOOKUP function to create an automatic random prize drawing tool! You’ll also discover how the mechanics of VLOOKUP can be replaced with the Index function, which we’ll explore in the latter tutorials here.
9. Array
An array is simply a collection of items. Here you’ll discover that there are two types of array functions: a single and multi-cell. In this video, we’ll focus on a single cell array formula, showing you a classic example of calculating a compounded rate of return!
10. Transpose
The TRANSPOSE function is a key example of a multi-cell array function. Discover how this function can easily swap data from a row to a column and vice versa!
11. Offset: Spreadsheet here!
Let’s discover the OFFSET function, which also belongs in the LOOKUP function family. We’ll explain what the OFFSET function does, how it is set up, and take a look at an example!
12. Intro to INDEX
The INDEX function is powerful and can even get around limitations of a VLOOKUP function. It is critical to understand this concept before moving on, because the next video builds upon this knowledge.
13. INDEX & MATCH: Spreadsheet here!
Let’s combine the INDEX and the MATCH function. First, we’ll review the INDEX function, then we’ll delve into what the MATCH function is, and finally, we’ll discover how to apply both in an example.
14. Round Robin Tournament: Spreadsheet here!
A round robin tournament means that every participant plays against every other participant! Here you’ll be able to create a schedule and score card for such a tournament. Developing this tool will incorporate the following functions: IF, SUM, TRANSPOSE, and INDEX & MATCH.
15. Design an Experiment: Spreadsheet here!
Discover how to design and test your own experiment. Learn about sampling design, data collection and quality control, and descriptive statistical analysis.