This post is part of Excel Dashboard Week
Early in Jan, I got this mail from Mara, a student in Excel School first batch.
Hi Chandoo,
I took your first Excel batch class and loved it. I created a dynamic and interactive dashboard for my work. My boss thinks it’s an excellent tool and I have you to thank for and also Francis Chin who shared his travel dynamic dashboard. I integrated things you taught so thanks so much!
I felt very proud reading her email, so I asked her if she can share the dashboard with some dummy data so that we all can learn from her example.
Being a lovely person Mara is, she gladly emailed me the workbook and I am thrilled to include it in Dashboard Week.
Customer Service Dashboard Snapshot:
Here is the dashboard that Mara prepared.
[View this dashboard image in full size | Demo of this dashboard]
Techniques used by Mara to Create this Dashboard:
Mara used several techniques to create this dashboard. But I specifically liked 5 things about this dashboard. They are,
- Tweetboard kind of area at the top where she showed summary of status. [Related tip]
- Dynamic dashboard which can be filtered based on a month.
- Interactive chart with check boxes to show / hide information. [Related tip]
- Interactive comparison chart to compare target with actual performances (of valet wait times). [Related tip]
- Scrollable list of various gift shop items. [Related tip]
Download Customer Service Dashboard Excel Workbook
Click here to download the workbook prepared by Mara.
I encourage you to examine the file and see how you can implement similar dashboard in your area of work.
Thank you Mara
Thank you so much for your generosity and enthusiasm to educate us. I have enjoyed examining your dashboard. You have shown creativity and skill in putting this together.
If you like this file, say thanks to Mara.
Contribute to Excel Dashboard Week by sharing your tips / files:
You too can share your tips, excel workbooks, snapshots to make this Excel Dashboard Week truly awesome. Just fill this simple online form to send your contributions.
15 Responses to “Christmas Gift List – Set your budget and track gifts using Excel”
[...] Christmas Gift List – Set your budget and track gifts using Excel … [...]
I'm confused: if you spend $10, and your budget is $40, shouldn't the amount in the "Within Budget?" column stay black, since you didn't go over budget?
In other words, since we overspent on the electronic photo frame, shouldn't the $8 cell turn red?
@JP.. maybe Steven is encouraging consumerism... ?
I havent realized it earlier, but now I see it. If you unprotect the sheet, you can change the formula in Column I to =IF(G13=0;" ";F13-G13) from =IF(G13=0;" ";G13-F13), that should correct the behavior.
Thanks Chandoo. I thought of making a shopping list spreadsheet for Christmas, but this is neat so I think I'll use this instead.
Chandoo & Steven thanks for this spreadsheet. But for the sake of a person who has been staring at this megaformula in vain for the last 40 mins and not afraid to ask, would it be possible for you to walk us through the logic used here?
=SUM(SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="-"))+SUMPRODUCT(SUBTOTAL(3,OFFSET($K$13:$K$62,ROW($K$13:$K$62)-MIN(ROW($K$13:$K$62)),0,1)),--($K$13:$K$62="0")))&" / "&SUBTOTAL(2,$G$13:$G$62)
Thanks Chandoo.. This is one of the best budget spreadsheets I've ever seen.. The Arrays are out of this world!! And it's FREE!!
Chandoo, can you tell us more about Steven? Does he have his own site?
JP, I think Chandoo changed it when he changed the currency formatting from £ to $, a negative figure is a good thing in this case. But don't change the formulas, the overbudget and under budget won't work properly if you do. Also Chandoo I think you've accidentally broke the conditional formatting for the alternating row colouring the formula is different to the version I sent you. As for the megaformula chrisham, it gave me a headache trying to get it all working, so I will let Chandoo talk you through it.
Hi,
In cells I6 and I7, I understand that subtotal together with offset function returns an array of ones after which, the sumproduct function gives the desired result.
But I’m not able to figure out the reason for using an array in I8 to return the most expensive gift.
Can’t the formula be just
“=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)”
Savithri, Cell I8 needs the array, if the formula was “=VLOOKUP(SUBTOTAL(4,$G$13:$G$62),$G$13:$J$62,4,0)” it would find the highest price from the filtered range (i.e. highest actual in filtered range is $50) BUT then return the first person with that actual, not looking in just the filtered range (so first person on the list with a $50 actual.)
To see what I mean, change the formula, then change all the actuals to $50 then filter for baby, it lists the first name on the list.
But a good question 🙂
Thank you. I now realise that the array is used to get the ‘filtered range’ instead of the entire range, as table array for look up value.
[...] Download This Template [...]
this looks like an awesome excel sheet!! is there anyway i can get it emailed to me unprotected? for some reason, i am unable to download it 🙁 help!!
Hi I also can not download to a mac as the sheet is protected any help would be great
[...] to send her a pricey present. Rather, send a card with a picture of your child. Here’s a cool Excel sheet that will help you estimate your budget per person and let you track [...]
[...] husband and I pour/poor over the Christmas spreadsheet (yes, I do know how dorky that sounds, but we’re not the only ones!), figuring out who should give what to whom. We live at a distance from most of our family, so it [...]