Amazing Power Query Tips and Tricks

Number 1 - Forecast Sheet. You want a quick peek of the future? So you've got a set of numbers here that look like they're in a pattern. All you have to do is highlight that, go to Data, and choose Forecast Sheet. This gives you a trend line and the forecast, and you can click the up-arrow here to extend it out for a longer period of time if you want to look farther in the future. That's just a quick look at the Forecast option for something really simple.

Number 2 - SUMIFS Function. The SUMIFS function allows you to do conditional summing of data. So in this example I have a table with month, item, and amount. Over here I'm going to add the sum of the amounts based on this criteria. Entering the SUMIFS function we just do SUMIFS and the range is the range of values that you're summing. The criteria in this first example is the month so we're going to select the month range and the value we're looking for is right there and you can see the total is 68 which is the sum of these first three. For this one we're going to do the same thing. The range is still the values the, criteria that we're selecting for first is the month, with this being the month. We can continue on with another set of criteria. Have it be the item, and this is the specific item we're selecting and you can see 78 is the total of just May Item2's. And that's the SUMIFS function.

Number 3 - IFERROR function. The IFERROR function can be used to clean up some bad data. For example, I have a formula here that calculates the per item amount for the quantity and the total. If I copy this down I get a divided by zero error because the quantity on this line is zero. Well, the IFERROR function can be used to clean this up. All you have to do is come in here, add IFERROR in front of your formula, and if that value is an error then what do you want to do? Let's just put in zero. Now when I copy that down it puts a zero in there but still calculates the other locations correctly. And that's a quick fix with the IFERROR function.

Number 4 - Filled Maps. Filled Maps are just like any other chart but way cooler. Take a set of data, highlight it, make sure you have location specific information in here, which can be a state, a city, a zip code, GPS coordinates, or any other location related data elements . Go to insert and choose Maps. Select this Filled Map and your data elements will be placed on a map. In this case the United States. You can also add different chart elements like Data Labels and you can change the chart to different types of styles. It's a very cool 2016 feature. And that conclude this video of the top 25 Excel 2016 tips and tricks. Don't forget to check out the other tips and tricks videos for Outlook, Skype for Business, Word, PowerPoint, and more coming in the future.

You don't have to attend formal classes at a university to get a grip on ... who have literally never opened an Excel spreadsheet in their life.

Kissmetrics

Search This Blog

Amazing Power Query Tips and Tricks

Comments