SubscribeAnnual Goals Tracker Sheet [awesome ways to use excel]
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]
Download this tracker Excel File
Click here to download the example file and play with it.
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.
| ||||
|
| ||||
|
Leave a Reply
 |
Sachin Tendulkar ODI Stats – an Excel Info-graphic Poster | Writing “Either Or” formula in Excel [Formula Howtos] |  |
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 450+ articles and tutorials on using excel, making better charts. 
19 Responses to “Annual Goals Tracker Sheet [awesome ways to use excel]”
Very nice project tracker. I’m thinking of a myriad of ways to use this. Thanks.
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
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.
@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/ )
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.
[...] link Leave a Reply [...]
awesome Marko – keep it up and great motivation!
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.
@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/
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.
Even I would second Chandoo
Nice work Marco
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…
@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
I have been looking for a goal spreadsheet made from Excel. This looks pretty interesting and this sort of thing might work for me.
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
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.
AMAZING!!! Simply Rocking. Love it. Brilliant.
Merci Beaucoup
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..