Monday, November 22, 2021

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.

Sunday, November 14, 2021

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 and pick up the tips there and made a video. Here it goes...

Sunday, November 7, 2021

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.

Sunday, October 31, 2021

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.

Sunday, October 24, 2021

Estimate with a Linear or Power Demand Curve

In Excel you can chart out an estimation of demand with a demand curve. A demand curve gives us an estimation for the relationship between price and the number of units that a customer would demand or the units of what we’d expect to sell. There are two common types of demand curves: a linear demand curve and a power demand curve. With a linear demand curve, the number of units demanded increases in a linear way as price decreases. Supposedly there are very few examples where a linear demand curve exist but it’s useful because it’s simple to grasp. The second common demand curve is the power demand curve. It’s probably something more identifiable for real world examples in the cases where the effect of price changes may have a bigger impact on demand. So you might have a question on which one to use and the best way to do that is to get some data to chart it out. See the video to learn how.

Sunday, October 17, 2021

Embed and Caption Name an Excel File in PowerPoint

When you shared PowerPoint and Excel files, sometime you just want to share one item instead of two. You can embed your Excel file into PowerPoint and have it clicked to open up. It works well when you don't expect the contents of the Excel file to change though it could increase the PPT file size depending on the size of the Excel data. You can even give the name to the caption of the embedded file or just give it a click to open text so users know what to do.

Sunday, October 10, 2021

Lookup the Last Value in Excel [XLOOKUP & INDEX / MATCH]

Looking up the last value is usually not the default when you perform lookups with various lookup functions. However there are times when you want to find the last item in a list or array that matches a criteria. With the newer XLOOKUP function, it makes this job very easy. The only catch is that XLOOKUP is on newer version of Excel. If you didn't have the latest Excel version, you can still perform a lookup for the last item and that can be done with a combination of the INDEX and MATCH functions.