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 →
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 →
Visualise a Pivot Table with a few Fields dragged in the Report filter, Row labels and Value Area section. In the Column labels are two fields, Month and then Year – so in the column labels, for every month, there is data for three years 2005, 2006 and 2007. For some months, there is data […]
Read More →
{ 2 Comments }
Assume a dataset with a Key Performance Indicator (KPI) [appearing in one column] data for years ranging from 1985 to 2010 for 114 countries. This dataset has 170,000 rows of data and one row below the last row for every country, there is a total of the KPI column. So, if there are 25 rows […]
Read More →
{ 10 Comments }
Assume quantity sold date by date and City in a three column database. The objective is to determine year wise, month wise and City wise running total of quantity sold in a Pivot Table. The issue which will arise with generating this result in a Pivot Table will be that the Show Values As > Running […]
Read More →
{ 6 Comments }
With Power Business Intelligence (BI) tools of Excel 2013, one can metamorphose raw data and/or results of complex calculations into stunning and interactive visualizations. Power View (one of the four components of Power BI) allows one to create a PPT like flow in Excel thus allowing one to weave a story. To be able to interact with/create visualizations, […]
Read More →
Assume a table which lists attendees for a Company’s Annual day function. In this Table, data for every attendee is shown on a separate row so if an employee attends the function with his/her spouse and three children, then there will be 5 rows for that employee. The question is to determine the count of the following family configuration: […]
Read More →
Let’s say the data sheet has Sales and Profit data by Product, Region, Salesman and Date. One may want to analyse the following via a Pivot Table “The Sales Delta by year, month, salesman and product i.e. for May 2012, compute the [Sales in May 2012] less [Sales in May 2011] for every salesman and product. Do […]
Read More →
Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart
{ 31 Comments }