I was recently working with a woman in a large organization around tracking training funding applications and she needed to be able to do the following:
- Identify how much was spent by Course Name and this to be analyzed by Gender
- Identify how much was spent by Department and this to be analyzed by Gender
- Identifying how many unique applicants she had e.g. the same person could apply for multiple courses but how to identify how many actual people were involved.
- She also wanted to get a breakdown of the expenditure by amount bands e.g. how much was spent in different bands e.g. 0 to 5000 etc.
So let’s see how I approached this. I began by using Mockaroo to generate a dataset of 1000 fake records.
You can download the starting file here.
You can download the completed file here
Part One – Analysis by Course name and Gender
In this video I show you how to use a pivot table to summarize the data and then add a slicer to analyze by gender
Part Two – Analysis by Department and Gender
This follows a similar format to Part One in that we use a pivot table to summarize our data
Part Three – identifying how many unique applications she had
This uses one of the new dynamic array functions which generates a list of unique names and then combined with the CountA() function to tell you how many there are.
Part Four – Analyzing total expenditure by different spending bands.
This uses the Sumifs() function to show you how to summarize the spending by different expenditure levels.