Convert Multiple Line Records into Different Rows

Sometimes you get a list that's in a two column name value or attribute value format and need to split these multiline records into separate rows. If this were a small table you could do a copy/paste or create some formulas to pull out the name value pairs. If this were a recurring process you might even write some VBA. Well I'm no VBA guru but this can be done with Power Query and then be used again. If you get these type of tables often and have to perform these steps on a regular basis just overwrite or append the new data and refresh the query like it's magic! 🧙‍♂️

Combine Tables with Header Values in Different Rows

Excel can be used to to combine or append tables in multiple different worksheet tabs in the same workbook file. But there may also be a value that you'd want to preserve in those worksheets to use for a new column value in the combined output. The tables are all structured the same and have the same column headings but the worksheet tabs have the generic name (i.e, Sheet1 or Sheet2). The common field in each worksheet can be used as a value that categorizes the tables. One worksheet will be California data and another worksheet would be New York data. However there's a catch...the header for those state are in different rows for each sheet. If these were small data sets, then copy/paste and some column movements would suffice. However if these are large tables, you do this on a recurring basis or you had 10 or 20 worksheets in the file, it would be a chore. This is a great opportunity to use Power Query and this video will show how. This is a tip I learned from Gil Raviv's book. Get the book at https://amzn.to/2Zj9ADY

Use PowerPoint Zoom Feature to Make Landing Page Summary Slide

The Zoom feature can help you make a summary slide that acts like a landing page for your presentation. It's available only on Microsoft 365 subscription or PowerPoint 2019. It's helpful if you have a large presentation and wanted to have your audience come back to a summary or landing page slide the center on the overall topics, section or categories of the presentation. It's a neat feature that keeps an audience engages in the main messages of the presentations.

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.