Use Power Query to Unpivot Report with Multiple Column Row Header Fields

If you've ever gotten a report, it's already been summarized with a specific format with a set of column or row headers (or maybe multiple layers of column or row headers). You'd think that it would take some time with multiple copy and paste steps to get the report view back into a table. With Power Query, you can revert this report back into a proper table so you can do some further analysis in just a few steps.

Cut Out Image from a Background in PowerPoint

To remove an image from a background or the cut the background away on a picture you want to re-use somewhere else used to be a tasks that involved Photoshop skills, a subscription to some online editing software or a small consulting fee to a graphic artist. Now with the latest version of PowerPoint (Office or Microsoft 365) you can perform this image editing task with just a couple of clicks (depending on the complexity of the image). This video will show 3 examples of how to cut a picture away from it's background from the simple to a bit more complex. See the timestamps to start from the simple example to the more complex. 1 - Gift Box (1:06) 2 - Pumpkin (9:40) 3 - Athlete (14:58)

Create Static Timestamp of Start / End Dates in Excel

If you're doing some project management and don't have a large budget, you'll probably end up using MS Excel as a poor man's project management tool. One concept in most project management timelines is the concept of estimated dates and actual dates. Usually you'd input the dates manually for these dates, but it would be nice to have the actual date entered "auto-magically" based on the value changes of another cell (i.e., when a percent complete is entered). But you don't want the actual dates to change after the initial value is triggered in that cell. This video covers a workaround using iterative calculations and sort of breaks a rule in spreadsheet design where you don't want circular references. In this case, we'll have to change the spreadsheet setting to allow for it. See the video to learn how to do this.

Add Percentile in a Pivot Table

Pivot Tables are a fantastic analytic tool in Excel to display data in a easily consumable format. They can quickly aggregate data into sums, averages and even standard deviations in the values fields of the pivot table. What if you wanted to rank data? Not by sorting, but in some "statistical" way? What if you wanted to get percentiles (like the 90th or 95th percentile)? You could create your own table with the PERCENTILE.INC or PERCENTILE.EXC functions to calculate it based on your specific criteria but then you'd need to maintain or update the range where those formula reside when there is new data. You can actually put percentile (or even quartile) data into a Pivot Table with the use of the data model and some DAX. Though data model and DAX sounds complicated, it's actually not to hard to use to get percentiles. See the video to learn how.

Generate Random Numbers that Adds Up to a Certain Amount

I saw this question in one of my other videos about random number generation videos and I was curious to know how could a set of random numbers be generated to add up to some determined amount? Well I found a thread on stackoverflow.com and pick up the tips there and made a video. Here it goes...

Create Horizontal Box Plot with Mean and Median

Box and whisker charts are something that is easy to create in Excel if you’ve got version 2016 or higher. There’s the option to create a vertical box plot but unfortunately there’s no option to create a horizontal box and whiskers chart. It’s actually fairly easy to create a sideways box plot by modifying a stacked column chart and adding a scatter chart for the mean (average) value.

Presenting Different Scenarios with OFFSET Function

The OFFSET function lets you refer to a cell(s) that's a specific distance from another cell. Though not a lookup function, it can almost work like VLOOKUP. I'll cover an example to show how the OFFSET function works with a simple use case of picking different scenarios of price and quantity inputs to determine revenue outputs.