How to create a unique target chart in Excel

By

Hello everyone, and welcome back to another tutorial where this time we're going to create a unique target chart using Microsoft Excel. If you've followed the previous posts you will get the idea on what we will try to do and shouldn't have any difficulties whatsoever.


   1. First step, fill in the cells with the information given bellow. Once we're finished with this tutorial you can create the tablebased on your needs and preferences.
   2. Step two, by now you probably noticed the headers consisting the Agents, Units sold, Target and Didn't meet target by. What you will do now is under the Didn't meet target by cell type the formula =MAX(D7-C7,0) in order to avoid the IF function we've added MAX instead, where we set 0 as our number limit so we don't have net results like -12, -25, -36 etc. 
   3. Step three, we've added another header Target overachieved where we will type the
formula =MAX(C7-D7,0). Once we've added the formulas on both headers, make sure you drag and drop so it will automatically calculate the remaining cells.
   4. Step four, select the Target column without the header and alt+F1 to insert the bar chart.







  5. Next, right click on the bar chart and select Change Series Chart Type.
   6. Next, select Line > Line with Markers.



   7. Once you click OK you are going to notice that the bar chart changed to a lined chart. But we don't want the lines. What we want is the dots for our target chart. So, click on the dot and select No Outline. Once you select that, you will get the same result as in the picture.




   8.  Go to Insert > Shapes > Line. Hold Shift and create a straight line. Once you've done that copy the line and paste it on the dots that we've created before. You should get the same result as in the picture.



   9.  Right Click on the number bar just at the bottom of the chart and click on the Select Data.





   10. Instead of the numbers showing from 1 through 10. We want to show the names of our Agents. So after you clicked on the Select Data this window is going to show up. So your next step is to click on the table icon at the right side of the window and select the names of the agents and click OK.





   11. Next, select the Units sold  cells and copy and paste it in the chart. Once you do that, right click on the chart and go to Change Chart Type. Instead of Line with Markers, change it to Stacked Column.




   12.  Repeat the same step for the Didn't meet target by as in the previous step.



    13. You will notice we now have too many bars on the primary axis, therefore we're now going to create a secondary axis. Copy the Target column and paste it again on the chart.


   - Next, right click on the dot > Format Data Series > Secondary Axis.






   14. Select the bar, Right click > No Fill.




   15.  Now copy and paste the Target Overachieved into the chart and repeat the formatting processes as in the previous steps. 



   - Note: After you change the bar into a stacked column, make sure you set it as a secondary axis.



   16.  Remove the gridlines and you'll be all set. Enjoy!
Here's also a video tutorial on how to create unique target chart


Comments

Post a Comment

Popular posts from this blog

How to create a barcode using Excel

By
Image
Hello everyone and welcome to another tutorial where we are using Excel to create or generate a barcode using very simple few steps. Before starting with this tutorial make sure you have downloaded a barcode font where we're going to use it in the next steps. Finding a barcode font is very easy, just google the font and you'll be able to download various barcode fonts.     1. First step, enlarge the A and B column so that the barcode would fit in. By doing so, in the A column select the number format as Text.     2. Next, under the A column, we have the serial number ID that will generate the barcode (you can also use letters instead of texts).    - Use the formula to generate the barcode: ="*"& A2 &"*"     3. After you've entered the barcode formula drag and drop so it can automatically generate the barcode for the rest of your serial number ID.     4. At this stage, select all the generated barcode nu
Read more

How to create an actual cell vs target graph in Excel

By
Image
Hello everyone and welcome to another tutorial where this time we're going to see how you can make a very quick and simple actual cell vs target graph in Microsoft Excel in just a few easy steps.      1.   First step, fill in the cells as seen on the picture bellow and write down the benchmark of units sold on the target or goal cell. Now that we have the benchmark of 100 units sold as our primary goal, head over to the actual cell and write down a random or in your case the actual number of units sold. Once you've done that on the graph cell type =D5  as seen on the picture where we will use the formula to show the graph in the next step.     2. Step two, once you click enter you will notice on the graph bar the progress is 50 units of the 100 target units sold. To add the graph go to Conditional formatting > New rule     3. When the New Formatting Rule window opens head over to Format Style and set it as Data Bar and click on the Show Bar Ico
Read more

How to create a student result sheet in Excel

By
Image
In this tutorial I'm going to show you how to use the IF function (logical test) with the True or False value. Also, how to use symbols in excel.     1. For us to use the IF function and Tick symbols, firstly we have to create a basic student data sheet where you can type the necessary information such as: Student name, subject, score, status etc. The first step for you to do is write down a simple student sheet with the as seen on the picture below.      2. Step two, on the Student column select the cells from A2 to A10 , right click > format cells click on the merge cells box, then set the orientation to 90 degrees and click OK.    3. Once you've merged the cells, write down the name of your student and make sure to align your content and text to center and middle as seen on the picture below. Also, make sure you change your font size to your liking as well.       4. Next step, write down the scores of your student on each of t
Read more