Generating Random time values in Excel

Hello all. Let’s look at how to generate random time values in excel. We shall be using the floor.math function and the rand function to generate time values. rand() function will generate random value between 0 and 1. Syntax is =rand() floor.math function rounds off the number to the nearest integer or the provided significanceContinue reading “Generating Random time values in Excel”

Generating Random Dates in Excel

Hello all. In this short section, let’s look at how to generate random dates. We shall we be using the “randbetween” and “date” function. Randbetween function will generate random number between the min and max range specified. Syntax is =randbetween(bottom,top) Date function will return number that represents the date based on the specified year, monthContinue reading “Generating Random Dates in Excel”

Power Pivot 101

Hello all. In this section let’s look at the basics of power pivot. Power pivot is one of the powerful tools which can help in getting multiple data source and create a pivot table out of it. The data source can be data thru ODBC, from database (like SQL, Access), Azure, Teradata, other excel sheetsContinue reading “Power Pivot 101”

Select dataset based on drop down value

Hello all. In this tutorial, let’s look at returning a complete data set based on selected string as drop down value. This tutorial can home handy when you want to pull standard set of metrics for different department in an org or if you want to pull meta-data about certain product’s sales numbers. Let’s workContinue reading “Select dataset based on drop down value”

Pivot Table – Advanced Part 02

Hello all. This is the 2nd part of advanced pivot table section. If you missed reading the introduction and 1st part of advanced, check here and here. Let’s use the same data set, as in the previous sections. Let’s look at how sales has happened for the company month on month in terms of %Continue reading “Pivot Table – Advanced Part 02”

Pivot Table – Advanced part 01

Hello all. Let’s look into some advanced features / functions of Pivot table. As Pivot table can do multiple things, I have broken the advanced section in to two parts. If you haven’t gone through the Pivot table 101 (introduction) section, please check out here. We will be using the below dataset. Revenue is nothingContinue reading “Pivot Table – Advanced part 01”

Pivot Table 101

Hello all. Let’s look at Excel’s built-in Data interpretation tool – Pivot Table. Pivot table is easy to use and can interactively explore the given data. Pivot table is one of the commonly used tools and being a pro in Pivot table is key to be pro in excel. Pivot is powerful and a easyContinue reading “Pivot Table 101”

Named Ranges in Excel

Hello all. Let’s look at one of the cool / handy features in excel – Named Ranges. Named ranges makes the formula more easy to create, read and re-use. In simple terms, any cell data can be substituted with a easy name of our choice. Let’s look at a simple example. In the below dataContinue reading “Named Ranges in Excel”

How to create dynamic graphs – Change graph based on values in drop down

Hello all. Fancy dynamic graphs that changes based on selected inputs part of drop down list? Like the below: Let’s look at the steps in creating this dynamic graphs. We would be performing the following steps to achieve this: Convert the data set to a table Create a simple graph based on the table CreateContinue reading “How to create dynamic graphs – Change graph based on values in drop down”

How to calculate commission % on total sales

Hello All. In this tutorial let’s look at how to calculate commission % on total sales based on the set threshold limits or set targets. Let’s see the below data set: In the data set, we have columns A & B as the target / threshold values (example, if the sale is between >100 butContinue reading “How to calculate commission % on total sales”