Category: PIVOT TABLES

Show months with no data which fall within a certain date range of a Pivot Table

{ 4 Comments }

Here’s a simple 4 column dataset Bacterin Donor# Recovery Agency Date Donor Received DONOR STATUS B050001 1 09-06-2005 00:00 ACCEPT B050002 3 09-06-2005 00:00 ACCEPT B050003 1 09-06-2005 00:00 ACCEPT B050004 1 09-06-2005 00:00 ACCEPT B050005 1 09-06-2005 00:00 ACCEPT B050006 1 09-06-2005 00:00 ACCEPT B050007 1 09-06-2005 00:00 ACCEPT B050008 4 09-06-2005 00:00 ACCEPT […]

Read More →

Filter the Rank Field in a Pivot Table

{ 4 Comments }

Imagine a two column dataset – Customer Code and Material Number (with alphanumeric data).  The objective is to determine the second highest quantity sold per Customer code. Since we will first have to determine the Customer wise and Material Number wise quantity sold, a good way to get started is to use a Pivot Table.  […]

Read More →

Compute month wise pending audits

{ 0 Comments }

Assume a three column dataset showing Audit ID, Date of receipt of audit mandate and Date of audit completion.  There are other columns as well but they are not important for our Analysis.  One may want to compute the following month wise: 1. Which (Audit ID) are the audits pending at the end of every […]

Read More →

Consider a Pivot Table Value field column as a criteria for computing another Value Field column

{ 8 Comments }

Assume a simple three column dataset showing hours worked by different machine on different dates.  So column A is Date, column B is Machine Name and column C is hours worked.  There are duplicates appearing in column A and B .  Blanks in column C depict machine idle time. The task is to create a simple […]

Read More →

Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart

{ 31 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 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 →

Show Slicer selection on a Graph

{ 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 →

Recompute figures in the Value area section of a Pivot Table after receiving a user input

{ 0 Comments }

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 →

Data slicing and analysis with the Power Pivot

{ 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 →

Drop additional fields in the Row area of a Pivot Table without affecting the already computed “conditional maximum” in the Value Area section

{ 2 Comments }

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 →

Perform different calculations in the Subtotal/Grand Total column of a Pivot Table

{ 33 Comments }

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 →