Group Dates in a Pivot Table

Grouping dates in a pivot table is one of those nice slice and dice features. If you get dates in a MM/DD/YYYY or DD/MM/YYYY format and want to see your data aggregated or rolled up into months, quarters, years or any other interval you need to group the dates. What is nice is you can expand and collapse the months (or whatever grouping or ungrouping you chose) to simplify your views. The other nice thing is that you can put the pivot table data into a chart and use the expand and collapse capabilities from the table to affect the chart or use the expand/collapse buttons on the chart itself. This chart can also be put on a separate sheet so you don't have to see the table. 

A note that is discussed in the video is what happens when you can't group and it may have something to do with the date format (Hint: It's not working because of text). There's another note on how this date grouping can be automatically done or undone.

Perform a Two Sample Proportion Test in Excel

When we want to compared the proportion of two groups (or samples), a suitable way to do this is with a two sample proportion hypothesis test. The question we're trying to answer is if there is a significant difference between the the proportion of the two groups/samples. This could be something like do the proportions of a drug effect females the same way they affect males or if the proportions of one group voting over a topic the same as another group. Too bad there is no tool to select a few cell ranges and gave an output (like t-test in the Excel Data Analysis Tookpak), but it's not too bad to manually lay out some cells and go through the formulas/functions to perform the hypothesis test.

Create a Calendar Table based on Start and End Date Inputs

If you're into building data models and your tables include dates, a good thing to have is a separate calendar table. You may want to create a calendar table that has the date, month name, year day name, etc. This gives you ways to slice and dice the data for further analysis and also a way to uniformly reference your other tables to one calendar table. It's fairly easy to create a list of dates in Excel by having a start date and incrementing by one day, but that is static. You'll always need to update it when new data comes in. The preferable way to do this is to automate it and that's when Power Query comes into play.

Animate a Social Media Phone Feed in PowerPoint

PowerPoint is great for animations and if you wanted to simulate a mobile phone screen as if someone was to browse through their social media feeds, this can be done in most versions of PowerPoint quite easily. In the latest versions of PPT (Office or Microsoft 365 subscription or 2019), you can use the Morph feature to do this. If you don't have those PPT versions, this can be done via the animation command. This video will show both examples of how to animate scrolling through a feed on a mobile phone image.

Turn a Picture into Puzzle Pieces in PowerPoint

If you want to create a puzzle piece image out of an existing image, you'd think it was something that could only be done in Photoshop or pay someone to do. With most versions of PowerPoint, this can be done with just a few steps. You can event animate the pieces with the morph command (but this would take Microsoft 365 subscription or PPT 2019). Later on in this video I'll show the animation effect using that morph feature. If you don't have the current versions of PPT, you can do this with the animations command, but it'll just take more work (that won't be covered in this video, but subscribe and check out my other animations vid on https://www.youtube.com/doughexcel/?s...). As a bonus near the end of the vid, making an image out of other shapes will be covered so check it out.

Use Power Query to Create a Dynamic Calendar Table

When you're creating relationships between tables that have dates, it often useful to have a separate calendar table that references all the dates for the year or years that you need. It'll also be nice if it has extra fields or columns that give just the year, month or day name. You can often do this manually in Excel with a few functions, but if you wanted to make it auto-magically update based on your given start and end date, Power Query makes it much easier to do once you set it up.

Perform Quick Pareto Analysis in Excel

A Pareto analysis tries to find out what variable are responsible for the bulk of the outcomes. It's referred to as a Pareto Principle or the 80/20 rule. The concept can be expressed with the saying that 80 percent of the wealth is owned or created by 20 percent of the population. Though in real life data may not fit tightly in the 20 percent, the idea of Pareto is that a small number of variables have a disproportionate ownership or cause of an outcome. This video show how to quickly find out from your data what variables are responsible for the "80%" in your data using Excel.