Visualise Sales Data of a Non-Alcoholic Beverage Company with basic columnar information such as Date of Sale, Time of Sale, Brand, Stock Keeping Unit (SKU), State, City, Quantity sold, Unit Price and Salesman Code. In this sales dataset, each line item represents one visit for one SKU. If nothing is sold in a certain visit, then the SKU […]
Read More →
Assume a simple Sales dataset from which a Pivot Table has been created. The Pivot Table has been sliced by two columns of the dataset. To represent data graphically, a Stacked Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook). The Stacked […]
Read More →
{ 7 Comments }
Assume a simple Sales dataset from which a Pivot Table has been created. The Pivot Table has been sliced by two columns of the dataset. To represent data pictorially, a Pivot Chart has been created from this Pivot Table and the chart is placed on a separate worksheet (of the same workbook). Now let’s say, a […]
Read More →
{ 12 Comments }
Assume two data lists. List1 has Test1,Test2 and Test3 in A2:A4 and List2 has Test1,Test4,Test2 in B2:B4. I would like to align data from both lists to appear as follows: List1 List2 Test1 Test1 Test2 Test2 Test3 Test4 You may refer to my solution in this workbook. You may watch a short video of my solution here
Read More →
{ 7 Comments }
In range E5:E13, assume the entries are 1 2 #DIV/0! 4 5 6 #N/A 8 9 In cell I9, one may want to sum up the figures from cells E5,E7,E11 and E13. If one supplies the individual cell references to the SUM function, then the result will be a #DIV/0! error because the SUM function […]
Read More →
{ 9 Comments }
Visualise a dataset from where you want to create different columns for entries which are listed down in one single column. In a scenario where the entries which have to be shown under each of these columns so created are numeric, a simple Pivot Table will sufficed. However, if entries to be shown under the […]
Read More →
{ 6 Comments }
Visualise a workbook with two worksheets. The tab name of the first worksheet is “My Table” and of the other worksheet is “My colleagues Table”. Each worksheet has 4 columns – First Name, Last Name, Pin code and Mobile Number. In “My Table”, the first, second and third columns are filled up and in “My […]
Read More →
Assume a simple 5 column database with the following data 1. Circle Name – A text field 2. PO_Number – An is an alphanumeric field 3. Quantity sold – A numeric field 4. Unit Price – A numeric field denominated in US$ 5. Revenue – A numeric field which is computed as Quantity sold * Unit Price To determine the Circle […]
Read More →
{ 0 Comments }
Visualise an MS Excel file with two worksheets: Employee headcount – a multi column dataset with information such as Employee code, Date of Joining, Age, Division, Department and Location. Each row represents data for one employee. The number of rows on this worksheet is approximately 700. Training Data – a multi column dataset with information […]
Read More →
When creating a Pivot Table one can easily apply the Maximum function for figures in the Value area section by right clicking and choosing Maximum from “Summarise Values By” > Maximum. The maximum so computed for figures in the Value area section is a “conditional maximum” because the numbers in the Value area section will […]
Read More →
Sales data modelling and interactive visualisations
{ 30 Comments }