“Susan” works for the Learning and Development Department at her Company as a Learning Database Administrator. As part of her daily duties she is required to not only track daily progress of learning interventions, but also supply various Senior Managers with a range of different reports.
“Susan’s” predicament is that she uses only a single set of data but she wants to generate several different graphs based on the same table (without using Pivot Tables, Pivot Charts or Slicers).
Well “Susan”, you are in luck..because Excel is absolutely awesome!! Let’s have a look at how we can help “Susan” solve this conundrum.
Let’s assume the table below represents the various Key Performance Indicators that “Susan” needs to report on. (Link to this Excel Sheet at the bottom of this article).
Each Senior Manager is looking for a different graph that represents a different set of Key Performance Indicators.
“John” wants to see the Training Budget vs Actual Training Spent (/Spend). “Steven” wants to see the number of Attendees and Non-Attendees and “Frank” wants to see the number of New and Existing training programs presented.
So, lets get started.
(If you’re not a 100% sure on how to generate Graphs in Excel, please feel free to have a look at my earlier article on how to quickly generate graphs).
Our first step would be to select the data within the table and insert a Graph: (Don’t select the heading – “Training Program”).
Once we’ve selected our data, it is time to insert our Graph. Navigate to the Ribbon at the top of your screen, click on “Insert”, select the graph button and then the relevant graph that you would like to insert. I like the 3D graphs 🙂
Once you’ve followed these steps you should see a horrendous graph similar to the one below. It contains waaaaaay too much information and isn’t particularly useful. So let’s change a few things.
Remember “John” wanted to see the Training Budget vs Actual Training Spent (or Spend). So let’s provide John with only the information that he requires.
Firstly, click/select the Graph that you just created. Once you’ve clicked/selected the graph you will see that Excel highlights the data used to create the graph (in the screen below you will note that the data has been highlighted in Pink and Light-blue).
To provide “John” with his desired information simply drag the pink squares (as indicated below) to only include the information that “John” wants. As you do that, the rest of the relevant information will be selected automatically.
Now you will see, the Graph adjusted itself automatically to only represent the Training Budget and Actual Training Spent (/Spend).
If you’d like to practice doing the same for “Steven” and “Frank”, feel free to download the template from this link and have some fun.
Until next time.