Revert a Report with Multiple Header and Total Fields to Proper Table

When you've received a report from someone it would be nice to do your own analysis of the data. However if it's already in some report table format with multiple header or row fields and multiple total or subtotal fields, you'd think that you'd have to do a lot of copy and paste to turn it back into a table so your could do some different pivot or crosstab analysis. With Power Query, you don't need to because the transformation can be done it quite easily and quickly.

Add a 3D Image or Animated 3D Image in PowerPoint

The latest versions of PowerPoint (PowerPoint 2019 or the Office / Microsoft 365) give some neat 3D model images for you to use. They're visually appealing and if used in the right way will give your presentation some added effect. The neat things is that the image can be manually moved around in it's three dimensional space so you can give the audience a specific focal point to the image. The other cool thing is having the 3D image move with the inclusion of a library of animated 3D models.

Use Wildcards to Lookup and Sum Up Values

Have you ever received a list and wanted to do a lookup to add some values up, but the looked up value was combined with other text? Sure you could try to pull out that looked up value into another column and then use it to help with the lookup process to perform the calculation, but that would be more steps. You could actually use a wildcard to help out in this situation to match the looked up values and then perform your sums. Click on the video to see what I'm talking about.

Calculate a Chi Square Test with Excel

A Chi (rhymes with the word "my" but starting out with a "k" like kite) squared test is used to determine the relationship between observed and expected values that are categorical variables. This means items that are counted, so this type of test would be used for continuous variables like weight or height. Excel has some useful functions that will perform Chi squared calculations BUT you'll still need to calculate out the Chi squared test statistic based on your data. Check out the video for two examples of Chi squared - goodness of fit and test for independence.

Perform a One Sample Proportion Test in Excel

This video covers how to perform a one sample test for proportion. This type of test used to estimate the proportion of a population. It compares the proportion to a target or reference value . Unfortunately Excel doesn't have a function in the Data Analysis Tool-pack to do this in a few steps, but it's not that hard if you lay out the calculations in a column and then re-use if needed.

Perform Year over Year Analysis with a Pivot Table

If you're familiar with Pivot Table, it's a great analysis tool. One common type of analysis is to compare the year over year (or month over month) change or growth of items. Maybe it the comparison of the actual number value or you could want the percentage change. One option is to have your data in the pivot table ready and make the calculations outside of the table (e.g., in the next column). But if you do it that way and make changes in the Pivot Table, the outside cell calculation could not work because they might be reference different cells now. This video will look at a simple data set of car sales and see how to compare the difference and percent differences over two years and make comparisons by date (months) and even by the car make to emphasis the flexibility of Pivot Table when you make changes. You don't need to calculate the change outside of the Pivot; instead let Excel do it for you. Then all you need to to it to plug your final table into a report or presentation.

Quick Charts, Totals or Conditional Formats with Quick Analysis Tool

If you're having trouble thinking about what kind of chart, formatting or calculation to help analyze your data, you can get some idea or insight from Excel by using the Quick Analysis Tool. It can give you a sample of the kind of visuals or formatting that can be applied to your dat