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.
Trackbacks & Pingbacks
- Pingback by MogBlog » Blog Archive » Annual goals spreadsheet – Neat usage of Excel on March 2, 2010 @ 12:20 am
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
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.
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