Google Sheets - Create a Dynamic Dropdown List

Want to learn how to create a dynamic drop down list in Google Sheets? It happens when you click a small drop down arrow in the cell and it shows you a list of choices. Then after the first selection from that drop down list will help filter another drop down list for another set of options to choose from. This is pretty easy to do by involving the named ranges, the data validation feature and the INDIRECT function.

Perform a Two Sample Test in Excel (paired t-test)

If you're testing a scenario that looks at a before and after situation AND wanted to know if there the result is based on random chance or NOT, you probably want to use a paired t test. Excel has some neat functions that give you the ability to do a paired or dependent t test easily. This video will show a quick way using the Data Analysis feature and then the various functions and formula to do it in a more manual (but challenging) way.

Google Sheets - Covert Columns or Rows into Comma Separated Values

Been in a situation where you had to convert values in columns or rows into comma separated values (csv)? Maybe you got a column of value and you needed to query it in another system, BUT that system needed the input values separated by commas (or semicolons). Using Google sheets, there are actually a couple of ways you can do this and I'll show three examples with three different functions to make it happen.

Animate a PPT with the Zoom Feature

Been in a situation where you had to convert values in columns or rows into comma separated values (csv)? Maybe you got a column of value and you needed to query it in another system, BUT that system needed the input values separated by commas (or semicolons). Using Google sheets, there are actually a couple of ways you can do this and I'll show three examples with three different functions to make it happen.

Perform an Approximate Match in Power Query

If you're familiar with doing an approximate match with VLOOKUP or INDEX / MATCH, you know that it returns the next largest match that is less than the specific looked up value. Some called this a fuzzy match, but it's basically the closest match you can find. You might be wondering why you'd want to do this in Power Query when the lookup function mentioned above can do the job. Power Query may work better when you find yourself doing these kind of fuzzy lookups on a recurring basis or this step is part of a larger series of steps when you have a large dataset (over 500K records maybe?). This video will show how to replicate a VLOOKUP or INDEX/MATCH approximate match.

Calculate a Two Way ANOVA with Replication in Excel

A two-way ANOVA test analyzes the effect of independent variables on the expected outcome along with their relationship to the outcome itself. Basically you'd use a two-way ANOVA when you want to know how two independent variables, in combination, affect a dependent variable. Excel make ANOVA calculation fairly easy. This video covers the basics of how to perform an ANOVA with replication test. The key concept to remember here is to see if there is a strong relationship of the interaction between the factors and if there is, it determines that the individual analysis of the factors are not as significant. So you'll need to do a deeper dive on which interactions are affecting your data.

Join Cell Values with CONCATENATE, CONCAT or TEXTJOIN Functions

Would you like to merge text or values in different cells? Depending on the version of Excel, you have several functions that can combine the cells contents together. When combining the values or text string of multiple cells you can put them all together or you can separate them with a delimiter like a comma or semicolon. Whether you want to put data into one column as a single entry or have a separator like a comma, this video will cover the different functions (CONCATENATE, CONCAT & TEXTJOIN) that you can use to do this.