How to Calculate Margin of Error in Excel

Ever see those polling results where it says "the average number of people think this with a margin of error of..." or " the percentage of people that think the president is doing a good job is this with a margin of error...."? This margin of error calculation is based on samples and some common statistical formulas....which you can do in Excel. Learn how...

Power Query - Importing Table in PDF to Excel

Importing a table from a PDF document into Excel can be a difficult process. You might have been doing a copy and paste and editing or deleting our strange characters, spaces or tabs. Whether it was a small table or large one, it's a time intensive process. If you've got the Microsoft 365 subscription (formerly Office 365 or O365), you might have the latest features from Power Query to help make this an easier process. If you don't have Power Query, it still can be done but with another Microsoft application - Word (see that video here at https://youtu.be/COUOvhbQ4CQ)

Create an Updating Dropdown List with Power Query

This video will show you how to create a dynamic drop down list from a table that gets transformed in Power Query. The output table become a source for a drop down list using the Data Validation feature in Excel. You can then hide the output table because maybe you don't want users to mess with it. This is a simple example, but it can be applied to more complex situations.

Passing Input Parameters into Power Query

In Power Query, you can set up a worksheet to take user input, perform calculations or other forms of data manipulation (or transformation) and output a specific table based on the user input. This would be an example of passing an input parameter to Power Query. Even though you could perform this with some combination of functions, pivot tables or slicers, the nice thing about Power Query is that the underlying steps can all be "hidden" from your user and you can just enable a one click does all for your user. This simple example in the video will show you how to pass a user parameter from Excel into Power Query.

Group and Merge Data for Calculations in Power Query

This video show how to use the group by and merge capabilities in Power Query with in an example use case. This was an actual use case where management was trying to track the completion rate for workers for courses they needed to take over multiple quarters as part of an employee development initiative. This involved figuring out what courses employees took, the completion counts per manager and the completion rate of the managers respective of their total employee population. This could usually be done with copying and pasting data table, performing lookups, calculation totals and finally formatting it in one table for upper management consumption. Doing all those task takes time so if this could be done in ONE click, it would save time. So Power Query can do this and very effectively. See the video to learn how.

Use the FREQUENCY Function

A common activity in analyzing a bunch of data is to categorize it. One way to do that is to put it into bins or buckets of ranges. For example if you have a list of ages you may want to put the count of people is the age range of decades like 0 to 10 and then 11 to 20 and etcetera. You can use the FREQUENCY function to do this simple categorization. See the video to learn how.

Chord Diagram in Power BI

A Chord Diagram or Network Radial Diagrams as a type of chart that is popular in data viz programs. You can't find it in Excel and not natively in Power BI. However it is a data visual that you can import from the Power BI marketplace (and it's from Microsoft). It's a visually appealing chart and quite popular to data viz practitioners. You might think that making this is hard, but with the chord data visualization import from the marketplace, it's really easy. And you don't need fancy programs like R or Python.