Author: John

Right Click for a Drop Down List

Did you know you can make a drop down list for selecting an item with just a right click? Yep! I never really noticed it, but there’s an option in the right click menu to Pick From Drop-down List. This can be handy if you’re doing a one off data entry and will allow you to enter some data exactly as it appears in cells above to avoid any typos. To use this, select an empty cell directly underneath your data and right click. From the resulting menu select the Pick From Drop-down List option. Now you have a...

Read More

How to Create a Normally Distributed Set of Random Numbers in Excel

What is a Normal Distribution? From a purely mathematical point of view, a Normal distribution (also known as a Gaussian distribution) is any distribution with the following probability density function. Download Example File Where μ (mu) is the mean and σ (sigma) is the standard deviation. Normal Distribution Probability Density Function in Excel It’s also referred to as a bell curve because this probability distribution function looks like a bell if we graph it. It’s a well known property of the normal distribution that 99.7% of the area under the normal probability density curve falls within 3 standard deviations...

Read More

How To Select A Random Item With A Given Distribution

Not all data is numeric, so when creating random sets of sample data it can be very useful to know how to randomly select some text data from a list. In that post, I showed you a way to randomly select from a list using the RANDBETWEEN function. This method produced an equal chance of selecting each item in the list. But what if we want to weight those probabilities of selection so that some items are more frequently selected than others? Download Example File With this method we can create the probability distribution of an item being selected....

Read More

How To Unpivot Data With Power Query

This situation happens a lot. Someone creates a pivot table based on some source data. They copy and paste it as values and that becomes your new data source. But really you would have preferred the unpivoted source data. Download Example File Can you easily unpivot the data? Yes! I have created a pivot table based on some source sales data. Rows area – I’ve added Customer Name, Customer Country and Sales Channel. Column area – I’ve added the Product sold. Values area – I’ve added the Total. We can see this creates a new set of data where...

Read More

Follow Us

Advertisement

Free Book

Microsoft MVP

Categories

Advertisement

Pin It on Pinterest