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.

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.

Rename Multiple Columns with Power Query

Sometimes you get data from others that have columns that you'd need to change the name. If it's something you do once, using the Excel Find and Replace function is fine. But if it's something you're going to do multiple times or a recurring basis, you'd think that a VBA macro is something that will need to be written. And if you don't know VBA, this bulk automation process may seem out of reach. However if you have Power Query in your versions of Excel, it's actually quite easy to rename multiple columns at once with some nested M code functions.

Removing Multiple Columns in Excel | Power Query

To remove or delete blank columns in an Excel table is fairly straight forward and easy. Just select the column or press Ctrl to select multiple columns to delete. However if there might be a LOT of columns and they might be non consecutive (i.e., not next to each other). You may have received this table from a system, person or some other source that has just not thought about you as a end user (the table has twenty or more empty columns EVERYWHERE) and it's going to be a lot of clicking. There are a couple a ways this could be solved and it doesn't involved writing VBA. There is a little bit of M code to write in one of the examples, but it is not that much considered its usefulness. See the video now to see how it's done.

Import SharePoint File into Power Query

Importing a Excel file that is hosted on SharePoint or OneDrive should be as easy as copying the URL from the browser or when you use the share feature in SharePoint / OneDrive. Unfortunately, it's not so straightforward. First, the URL link from the share feature isn't event the right one to use and then you have to be on the online version and request to open it with the desktop version to get the correct URL. After that it's just a copy and paste of an edited version of the URL to put into the Power Query field. It's sounds like a lot but once you see how it's done, it's not so bad. Hopefully Microsoft will make it easier in future Power Query updates. But in the meantime, check out the video to learn how to import a file hosted on SharePoint into Power Query.