Sort Column by Cell Color

Sorting columns are a common occurrence and usually done via ascending or descending based on A to Z or Z to A with text or number values. But did you know you can sort by color? The use case maybe that you've done some conditional formatting for the cell color and you want to sort on blue, yellow and green in that order. It can be done with the custom sort feature.

Calculate Cumulative Principal & Interest

When you've got a loan or mortgage and you're the analytical type, maybe you'd want to know how much you're paying in principal and interest for a giving time period. For example for the first year, how much is that payment for the principal (hint: it's not much) and how much of the payment is interest going to the bank (it might surprise you). You can figure this out with Excel.

Find Value NOT Between a Range

Excel makes it easy to find values that are between a range; say numbers between 20 to 40. If you wanted to find value outside the range (i.e., 1-19 or 40 and above), Excel makes it equally as easy. I'll show two ways you can do this; one with filtering and the other with conditional formatting.

Pivot Table Calculated Item Solve Order Issue

If you've created multiple calculated items in a pivot table you might get into a situation where they are not showing up correctly. Somehow the calculations are wrong or don't look right for what you're trying to communicate. Often times it will be the way that the calculations are ordered that is creating the confusion. Re-arranging the sort order of the calculated items is then the solution. This video will cover what dialogue windows can be used to resolve the issue and also go through the steps of creating the pivot table with the "wrong" series of steps that lead up to the problem.

Excel MIRR vs IRR

When looking to compare similar projects tools such as IRR and MIRR are used.  But what's the difference between these two when used for capital budgeting ranking? A major difference is that for IRR the cash inflows are reinvested at the project's IRR, while the MIRR is using an alternative re-investment rate for the cash inflows.  The video covers the basics of how to use the Excel IRR and MIRR functions.

Quickly Change Multiple Queries, Same Source in Power Query

If you've created multiple queries from the same source and then the location of that source changes, you'd think that you would need to go into each query and one by one change the source location. If there's a few queries, it not so bad. If it's a LOT of queries, that will be painful. You can actually use a parameter and then tweak some source settings to let you change the source location all in the same time. Plus at the end of the video I'll show a simple design choice that could be used to mitigate all this without using a parameter.

Extract Text in Power Query

When you have some values to pull out of a text or numerical string in a cell, you can use the extract commands in Power Query to make it easy. This is an great option to use instead of using the RIGHT, MID or LEFT functions in Excel or trying to split the value by some delimiter. It's so much more easier to pull the data into the Power Query editor and using Transform and Extract to pull your desired text string out.