Creating charts with multiple criteria without summary sheets and thousands of distinct formulas

RobN

I have a grid that contains an employee's level of expertise is a specific skill. There are 90 skills listed (columns) and around 50 employees. Each cell contains None, Beginner, Intermediate, or Expert. There are additional columns for each employee's name, department, and whether they are 'Active'.

In order to create a stacked column chart that shows the number of employees with each level of expertise, I had to create a summary grid with None, Beginner, Intermediate, and Expert as columns, and each skill as a row. I then added a formula to each cell to calculate the number of employees with that level of expertise for each skill. That required that I add 360 unique formulas, but allowed me to create the right visualization.

I'm now being asked to create additional graphs based on filtered criteria (dept and whether an employee is 'Active). If I took the same approach, this would require seven new summary sheets with over 2500 distinct formulas.

Is there a better way to do this?

Julie Fortney

Have you tried using reports with grouping and summaries to create charts? Here's an example:

RobN

I've tried this, but can't seem to figure out how to get the summary to include the expertise levels. In your example, it appears that the graph is only showing the number of people with each skill, not their level of expertise. Is that right?

Julie Fortney

That is true. Do you have the Pivot premium app? I've seen some other Community suggestions where users have used the Pivot app to help them with use cases like this.