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.

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.

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.