# Annual Goals Tracker Sheet [awesome ways to use excel]

Posted on March 1st, 2010 in Charts and Graphs , excel apps - 29 comments

Marko, who is a long time PHD reader and an excel ninja sent this via email,

I work at an insurance company in Slovenia. At the beginning of each year we have a conversation with our superiors to review our work in the past year and to set new goals (main activities) that we’re gonna work on throughout the year. To keep track of these activities I took the liberty of modifying Jennie’s A New Year Resolutions Template that Kicks Ass and created a more “dynamic” template.

He sent me this:

I immediately liked this implementation, for various reasons. This example shows how we can combine various powerful features in MS Excel to create something truly unique and outstanding. It uses,

• Conditional formatting to highlight rows when the activity is 100% complete. [learn how to]
• Formulas to show check boxes when the activity is done. [learn how to]
• A thermometer chart to show the progress against the target. [learn how to]
• An elegant date formula to show how many days are remaining to finish the goals. [learn how to]
• And scrollbar form controls to adjust the % completion values. [learn how to]

### Thank you Marko

Very elegant, very slick. Good work Marko. Thanks for sharing this with us.

### Share your Excel Success with PHD

I like to learn how you use excel creatively to solve problems at work. Send me examples, workbooks or ideas. I am always looking for new ways to use excel and your contributions will help us all. Email your stuff to me at chandoo.d @ gmail.com.

 Sachin Tendulkar ODI Stats – an Excel Info-graphic Poster Writing “Either Or” formula in Excel [Formula Howtos]

### 29 Responses to “Annual Goals Tracker Sheet [awesome ways to use excel]”

1. Paul says:

Very nice project tracker. I'm thinking of a myriad of ways to use this. Thanks.

2. Elias says:

This is really cool. I think you can avoid formulas in range M8:M13 and substitute formula in M6 for this. =SUMPRODUCT(K8:K12,N8:N12/100)

Regards

3. Joe says:

Is there a way to create a log of each time the slider is adjusted? (like on a mouse up , or an on change action)

Would be interesting to have a log to perform an analysis on.

Maybe every time a value is changed, a row is added to another sheet with the specifics, and then a chart on the dynamic range to visualize the progress over time. With the option to aggregate the data to the day/week/month level.

4. Chandoo says:

@Elias: good tip 🙂

@Joe: Very good idea on logging part. You can do that very easily with Active-x scrollbar control instead of form control. You can assign a small macro to change event of the scrollbar and log the changes in a separate sheet.

Even with form control you can monitor the linked cell using worksheet_change event thru macros.

A simpler, but painful way could be to write some circular formulas that would put timestamp in a cell whenever linked cell content changes. (refer to timestamping in excel article for more on this approach: http://chandoo.org/wp/2009/01/08/timestamps-excel-formula-help/ )

5. david says:

the worksheet reminded me of a "feature".

how do show a specific portion of cells, but HIDE the rest?

in this worksheet, only range A1:L15 is visible. All other cells are GREYED out/hidden.

7. Ubique72 says:

awesome Marko - keep it up and great motivation!

8. Yoav says:

Very impressive work Marko.

If you could add a bionic leg that will kick me in the butt and get me to do stuff, it'll be perfect. 🙂

9. Chandoo says:

@David.. you can hide un-necessary columns and rows by selecting all of them and clicking "hide" option. More on this here: http://chandoo.org/wp/2009/11/03/make-better-excel-sheets/

10. david says:

i see! thx!

btw, previously i encountered an invoice template done in excel.

it was done similar to this worksheet, but i cant seem to unhide those hidden columns/rows (cuz i wnated to see the formula which are hidden from view).

no hidden formula/calculation sheet. just a single sheet, with selective range of viewable range.

11. Nimesh says:

Even I would second Chandoo

Nice work Marco

12. Hank says:

Question which is driving me crazy...How is the data in columns N and O being hidden? It's not text color, formatting or being hidden...

13. Nimesh says:

@Hank: It's because of the custom formatting of ;;;
It's also used in Jennie’s "A New Year Resolutions Template that Kicks Ass"

Found that after you pointed out 😀

Thanks Hank

14. Bob B says:

I have been looking for a goal spreadsheet made from Excel. This looks pretty interesting and this sort of thing might work for me.

15. Ankur Jain says:

Hi Chandoo,

What - in your opinion - will be the easiest way to add a 6th row (activity) in this case?

Thanks and Regards
Ankur Jain

16. Nico Kleynhans says:

I have successfully downloaded this wonderful spreadsheet. It works like a dream. However, I need to monitor TWO sets of data so I made a COPY and PASTED to the next page.

However, the data from the first page causes the slider on the second page to change. Obviously it carried the links across. How do I copy so that the second page will not look for data on the first page? I am fairly ignorant about software.

17. Wings_of_Angle says:

AMAZING!!! Simply Rocking. Love it. Brilliant.

18. Raaj says:

Merci Beaucoup

19. Kris says:

Hi,

I like the template (great work) I have always wanted to do new things in excel. unfortunately I'm working on excel (2003) version :(, this tracking is very useful in my work. I tried some portion of template but did not succeed in doing scroll bar and therometer. is there alternate way to me, so that i can try to creae one for my work. for this I spend lot of time on this.

Thank you!
kris..

20. Colin says:

1) The custom cell format for cells K8:K12 is 0%;-0%;"" can you please what this is trying to achieve?

2) For the same range, I dont understand the reason behind the Weight range? I assume that in this example it is just a guestimate / ranking on what the user determines for which activity is more important over other activities? Surely it would be better if the percentages actually change based on how much each of the actual activities change, i.e. as one activity is 100% the weight is changed and distributed between the remaining weights? If this is not done, I dont see the point of even having K8:K13? Or am I missing something?

Regards
Colin

21. Thameur says:

Hi,
How could i add more than 5 activities.

Thks.

• Gökay says:

Simply copy a line and past below it. Later edit M6 cell like this :
(....+K12*M12*N12/100+K13*M13*N13.....K14*M14*N14/100;0)

22. […] I stumbled around, trying to find a way to make my chart match my imagination, I came across this chart from Chandoo.org. I don’t know how to manipulate it very well, but I came up with five writing […]

23. Subodh Zare says:

Really impressive & easy to put in use !

24. Norma says:

I am looking for something like this however, more for a daily/weekly tracker. So If I have 125 QA to do in a week, how can I automatically keep track of what percentage im at. Is there a formula I can enter that will automatically calculate my percentage?

25. Harsh says:

Hi Chandoo,

I found this article very interesting and i think this might solve my problem if you help me with below information:

I need this goal tracker for multiple (let say 5 people) with their individual progress but in one chart.
so, lets understand this - if i give a dropdown with displaying 5 people name and based on the name one can change his /her progress for 5 activity. and i can check that there only.

is this possible and how can i do that??

26. Ram says:

Thanks for sharing the template, It's very useful.

27. ZOE says:

I tried adding a new activity so I copied a line and pasted but now when I moved the cursor I'm getting the number of the cell I copied. help pls

28. Ilkin says:

How we can inrease the quantity of the activities? How we can add the new activity.