Use the LET Function in Excel

If you familiar with programming or VBA you'll love the LET function. It basically lets you define a name with a cell, assign it a value and perform calculations with that name. It might be overkill with basic functions, but when you've got more complicated formula that call on the same range or set data ranges, the LET functions makes it more efficient to execute and easier to read and troubleshoot. It's only available on Microsoft 365 version, so if you got that version check out this video to get an introduction to the LET function.

Create Interactive Power Query Report Start & End Date

Power Query can make interactive reports with a start date and end date with a simple function query. This is not as hard as it sounds even though it involves getting into the M code a bit. The nice thing about this method is that you can then hide the data source from a user and give some basic instruction on how to use this to generate a basic report. Though it covers date selection, this method can be applied to other type of user input like text selections.

Sankey Diagram in Power BI

Sankey Diagrams show how data flows from one entity to another. You'd think that it would be difficult to create but with Power BI and the Visuals marketplace from Microsoft, you can create a simple Sankey chart to have provide some interactivity or just copy/paste to a presentation or infographic.

Google Sheets - Lookup the Last Matching Value

If you've worked with most lookup functions (like VLOOKUP, LOOKUP, etc.) it will bring back the first matching value in the range you selected. However there may be times when you want to see the last matching value from your lookups. In this video it will go through two examples of how to get the last matching value from a lookup with Google Sheets. One will be using the LOOKUP function (along with other functions) and the other will be a crazy combination of INDEX, IF, ARRAYFORMULA, ROW AND LARGE.

Unpivot Multiple Columns | Added Columns Changes

To unpivot a report or table used to be a difficult process, but it's become a much easier process with Power Query feature in Excel. Still there are things to consider when using the unpivot feature because when column changes with adds it DOES depend on how the unpivot is done and where the column is added.

Google Sheets - Perform a Lookup [3 Examples]

In most spreadsheet applications there are multiple ways to do the same thing. Google sheets is not exception as it provides you many ways to accomplish similar tasks. A common task in most spreadsheets is looking up values or records based on set criteria. There are a few functions we can use in Google Sheets to do a lookup and this video will cover three functions that can accomplish lookups: VLOOKUP, the INDEX / MATCH combination and FILTER (though the FILTER function is not technically a lookup function it can still be used to lookup records).

Perform a Lookup with the FILTER Function

One of the new Dynamic Array functions in Microsoft or Office 365 is the FILTER function and this can let you perform a lookup on a table of data. Generally when we think of filtering data it's already in a table and we're using the drop downs to filter based on some criteria. With the newer FILTER function, we can separate the source table and the output table in different parts of the worksheet tab or in separate worksheet tabs. This can even be used in scenarios where we'd want to make a dashboard to separate this. This video will cover some different examples of how FILTER can be used. Common usage (0:56) Multiple criteria (5:20) Find duplicate data (9:55) Output few adjacent columns (11:47) Output few non-adjacent columns (12:35)