Weekly earnings, best way to set it up

[micko_escalade]

Hi all,

I'm helping friend of mine to start logging all of his daily earnings into a Excel spreadsheet and to try to move him away from writing it on the paper.
My small issue is that there's no set daily earnings, meaning he can have 2,3,4 or 5 clients.
Attached is what I made and was wondering if someone can take a look and maybe make better way of how to log it so its easier to log.

Thanks in advance for your time!

[ELeGault]

Micko,

I recommend pivot tables especially when it comes to passing it off to people that know barely the basics. That way you would not have to constantly be correcting issues

Check out the Attached

Weekly Calculator_Modified.xlsx

You will notice I did away with the breakdown and Totals columns as we can do this in the Pivot table!
You may also notice that the Log sheet is a Table itself. This is to make refreshing the pivot table simple on the end user.
Your user will enter in the data like you have been on the log sheet. Date, Address, Amount Earned, Tips. The leading column A will auto fill as it is a formula to allow you to have grouped "Weeks" on the pivot.
I also setup group fields so that it will show quarters/years should he want to see that level detail.

EDIT: You will notice on the Breakdown tab there is instructions on how to refresh the table, It is simply right clicking on the data displayed and selecting refresh. The end user must do this after entering data in the log in order for it to appear!

For you, I would say study the layout, but to also lookup Tables, Pivot Tables - In pivot tables learn what group fields are, how to apply calculated fields and then just for fun slicers. Which is what you see as button options on the Pivot table tab or "Breakdown" sheet of the workbook. I applied one for Date and one for Week Number Ending Date. This will allow for quick filters... Have fun, play with it and ask questions as needed - I think for an end user it is best to set it up in this fashion!

Cheers,

[micko_escalade]

ELeGault,

Thanks for the reply!

I like how you moved dates addresses and listed them under each day. I did not thought of that
I'm using office 2007 so the slicer are not shown.
How can I change the color and maybe put in red, just to stand out total for each day under "Sum of Total Earned" in breakdown?