Here is a sample dataset of learners who have cleared different stages of multiple courses on offer within an Organisation:

Learner

Stage completed

Course

Bill

Stage 1

Public Speaking

Bill

Stage 2

Public Speaking

Bill

Stage 3

Public Speaking

Susan

Stage 1

Effective Communication

Bob

Stage 1

Public Speaking

Bob

Stage 2

Public Speaking

Sheila

Stage 1

Effective Communication

Sheila

Stage 2

Effective Communication

Sheila

Stage 3

Effective Communication

Frank

Stage 1

Effective Communication

Frank

Stage 2

Effective Communication

Henry

Stage 1

Public Speaking

Henry

Stage 2

Public Speaking

Bill

Stage 1

Effective Communication

Bill

Stage 2

Effective Communication

From this sample dataset, one may want to know how many participants have completed each stage of these multiple courses. The expected result is shown below:

Row Labels

Stage 1

Stage 2

Stage 3

Effective Communication

1

2

1

Public Speaking

2

1

Grand Total

1

3

2

In this workbook, I have shared 2 solutions - one using formulas and the other using the Power Query & PowerPivot.

Assume a dataset with two columns which lists down the student names in column A and courses opted for in column B. Since one student can opt for multiple courses and the same course can be taken up by multiple students, there can be repetitions in both columns. The objective is to create a matrix like data structure (with courses appearing in both row and column labels) with numbers inside the matrix quantifying the "Number of students who opted for course A and C". So, for all possible course combinations, one may want to know the number of students who opted for those combinations.

The description above can be extended to cases where buying behavior has to be analysed. A sore manager may want to know "How many people who buy Brand A also buy Brand B."

Here's a snapshot of the source data and expected result

The number 1 in cell H4 (and cell F6) means that there is only one student who opted for courses B and D. Likewise, 3 in cell H5 (and cell G6) means that 3 students opted for courses C and D.

You may refer to my Power Query and PowerPivot solution in this workbook. Power Query has been used for generating a dynamic list of Courses and Power Pivot has been used for writing the DAX formula for quantifying within the matrix.

Assume a five column dataset - ID, Age, Gender, Time and Class. For chosen ID's, the objective is to:

1. Assign a Rank (in ascending order of time i.e. lowest time will be rank 1 and so on) to each ID
2. Determine the overall place of each ID - Count of unique time entries lesser than equal to the chosen ID' time entry

These can be computed with the VLOOKUP(), RANK(), FREQUENCY(), INDIRECT() functions and array formulas. You may refer to range H3:K8 of the Sample worksheet. So far so good.

What adds to the problem is to meet the objectives outlined above after satisfying additional conditions. For e.g., one may want to give conditions such as Age between 20 and 35 and colours as Orange and Yellow. Carrying out computations for ranking and Overall place after satisfying these conditions will make the formulas fairly complex.

I have been able to solve this problem with the help of the PowerPivot. You may download my solution workbook from this link.

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 column displays No Sale. So effectively there is a line item for each visit whether or not something is sold in that visit.

From this simple Sales dataset, here are a few questions which one may need to find answers to:

1. How did the Company perform (in both years 2013 and 2014) on two of the most critical Key Performance Indicators (KPI's) - Quantity sold and Number of Visits. Also, what is the month wise break up of these two KPI's.

2. Study and slice the two KPI's from various perspectives such as "Type of Outlet visited", "Type of Visit" - Scheduled or Unscheduled, "Day of week", "Brand", "Sub brand".

3. Over a period of time, how did various SKU's fair on the twin planks of "Effort" i.e. Number of visits YTD and "Business Generated" i.e. Quantity sold YTD.

4. Analyse the performance of the Company on both KPI's:
a. During Festive season/Promotional periods/Events; and
b. During different months of the same year; and
c. During same month of different years; and
d. Quarter to Date

5. "Complimentary Product sold Analysis" - Analysis displayed on online retailers such as Amazon.com - "Customers who bought this also bought this". So in the Sales dataset referred to above, one may want to know "In this month, outlets which bought this SKU, also bought this much quantity of these other SKU's."

6. "Outlet Rank slippage" - Which are the Top 10 Outlets in 2013 and what rank did they maintain in 2014. What is the proportion of quantity sold by each of the Top 10 outlets of 2013 to:
a. Total quantity sold by all Top 10 outlets in 2013; and
b. Total quantity sold by all outlets in 2013

7. In any selected month, which new outlets did the Company forge partnerships with

8. Which employees visited their assigned outlets once in two or three weeks instead of visiting them once every week (as required by Management).

9. Which outlets were not visited at all in a particular month

10. Business generated from loyal Customers - Loyal Customers are those who transacted with the Company in a chosen month and in the previous 2 months.

These are only a few of my favourite questions which I needed answers to when I first reviewed this Sales Data. Using Microsoft Excel's Business Intelligence Tools (Power Query, PowerPivot and Power View), I could answer all questions stated above and a lot more.

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 such as Employee code, Training Date from, Training Date to, Training Program Name, Training Program Category (Internal and External), Training Location and Training Service Provider. Each row represents one training attended by one employee. The number of rows on this worksheet is approximately 2,600.

Let’s suppose that the training calendar of this company runs from July to June. Some questions (only few mentioned for illustration purposes) which a Training Manager may need answers to are:

1) How may unique employees were trained each year; and
a) Of the unique employees trained, how many were first time trainees and how many were repeat trainees
i) Of the first time trainees:
(1) How many joined this year
(2) How many joined in past years
ii) Of the first time trainees:
(1) How many were trained within the first year of joining
(2) How many were trained in the second year of joining
(3) How many were trained in the third year of joining
(4) How many were trained after three years of joining
iii) Of the repeat trainees:
(1) What is the average gap (in days) between trainings
(2) What is the minimum gap (in days) between trainings
(3) What is the maximum gap (in days) between trainings

Getting answers to the questions mentioned above would entail writing a lot of lookup related formulas, applying filters, copying and pasting and then creating Pivot Tables. While the example taken above is that of a training database, you may envision “drilling down to and slicing” any dataset – Marketing, Sales, Purchase etc.

You may watch a short video of my solution here

In these two workbooks, you will be able to see the level to which one can drill down and analyse data using the Power Pivot add-in. When you open this workbook, please go the first worksheet and make the relevant choice of MS Excel version first so that you start looking at the Analysis from the correct worksheet.

You will be able to see the analysis in these workbooks only if you are using one of the following versions of MS Office:

1. Excel 2013 Professional Plus; or
2. Excel 2010 with the Power Pivot add-in installed. Power Pivot is a free add-in from Microsoft which can be downloaded from here.

Lastly, if you are using the Power Pivot add-in in Excel 2010, you will not be able to see the underlying Data Model or the calculated Field formulas because this workbook has been created in Excel 2013 Professional Plus and unfortunately the Power Pivot model is not backward compatible. However, all the analysis performed in this workbook can be performed in Excel 2010 as well (with the Power Pivot add-in installed).

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, you will need to install Microsoft Office Professional Plus 2013 (this version will already have two of the four components of Power BI - PowerPivot and Power View). Additionally, you will have to install the following add-ins from Microsoft (the other two components of Power BI)

1. Power Query; and
2. Power Map

I have tried to showcase the prowess of Power BI tools of Excel 2013 in these two workbooks:

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:

1. Employees only (those who attended without spouse and children); and
2. Employees, spouse and children (Family); and
3. Employees and spouse (no children); and
4. Employees and children (no spouse)

You may refer to my solution in this workbook. I have solved this problem using:

Given a database of 50,000 rows, counting unique values with conditions using formulas would either adversely effect workbook performance or would not work in the first place at all.

In this workbook, I have shown the technique to count unique values with conditions on a large database

1. Using PowerPivot - Will only work in Excel 2010 and higher versions

2. Using a normal Pivot Table and SUMPRODUCT() function - Will work for all versions but is not as efficient as the PowerPivot solution.

To count unique values with conditions on small databases, you may refer to the following link

Assume a two column database of patient ID's and service availed. One patient may avail the same service multiple times in a year due to which that record may appear as many times as the service is availed. For e.g., if patient A001 avails the Radiology service twice, then A001 and Radiology will appear in two rows.

Once may want to create the following two reports from this database:

1. A list containing all those records where the patient availed just one service; and
2. A list containing all those records where the patient availed more than one service

Depending upon the version of MS Excel which you are using, there could be two ways to solve this problem

Solution for MS Excel 2010 and higher versions

If you are using the PowerPivot add-in, then a calculated column formula can resolve this problem.

Solution for all versions of MS Excel

I have shared two solutions here:

1. Array formula and advanced filters; and
2. Only array formulas

For better understanding of the question and to view the final solution, please refer to this workbook.