Excel Dashboard Tutorial

[@rebeccawood5512]

By the way, thank you SO MUCH. I appreciate all the time and effort you spent putting this all together. It's people like you that make people like me enjoy investing in ourselves!!

[@thesmallman]

Thanks for reaching out by email Rebecca - it was people like you that inspired me to make this public on my channel. Thank you!!!!

[@SUBLIMEnovel]

Excellent tutorial. Perfect pace for me. Thank You!

[@thesmallman]

You're welcome!

[@zee.things]

Loved your work, helped a lot for Dashboard skills. Appreciate Hardwork on the World Map :)

[@thesmallman]

Glad it helped!

[@SuperDavidFz]

Fantastic, amazing . Good job 👏 Marcus

[@thesmallman]

Thanks David - you must have been the first person to sit right through it. It is great that you invest the time in yourself.

[@SuperDavidFz]

@@thesmallman I'm investing a lot, and these video lessons of yours as well as the example of the website dashboards are helping me a lot

[@thesmallman]

@@SuperDavidFz is there anything I could do more of? I have a rather narrow field. My background is visual display and programming. It is not for everyone.

[@rogeriooliveira4934]

A help, database with filters and I need to copy to another folder, so there are no problems, how to select the only filtered lines? is there a shortcut to select only the filtered lines? thanks

[@thesmallman]

Yes - just use VBA to copy all of the data and the lines that are filtered out will not be copied. It is pretty simple and you can find code on my website to copy filtered data.

[@bellanaumov]

Good Job, thank you!

[@bellanaumov]

You are funny too! :) haha! Never!

[@StepUp-One]

Incredible Effort, had real fun making it work. Many thanks. Just one issue when i tried to update the data by pressing the update button. First there was an error about table "URL" was not found. Then i did put names for every pivot table we created. now i have an error that says " The Column "Reports" of the table wasn't found". Will be great if you can help solve this.

[@thesmallman]

Mohamed - Excel is extremely consistent. You will notice when I UPDATE DASHBOARD at 1.34.00 in the video that the model refreshes beautifully. I want to point out that you and I are using exactly the same Excel start model. So the button works on my model - this means you have done something along the way that is different to what I did. If you sent me your file I would spot where you mis stepped but you would learn more if you find this yourself. If you still have problems in a few days email me and I will help you through.

[@spydymn5114]

I just watched your video and loved it, i will be going through in more detail again to see how you are handling the data in detail. I started doing this myself a few weeks ago just for my county and city but have been adding data manually. I am wondering if this can be adapted to county and city level only? that way i could learn how to do the dash board for that level. thanks again for this video it is very well done and easy to follow

[@thesmallman]

Hi spydymn - thanks for your kind words. I just wish more ppl would watch the video. LOL. The model can be altered to add more detailed data if different reports are uploaded. John Hopkins has the links on their site and once you have the URL it is a matter of going through the same process of uploading and manipulating the raw data. I am glad you liked it. All the best. Marcus

[@spydymn5114]

@@thesmallman I couldn't find links for county/city level so far so will probably still continue and complete as is just to learn as i like what you have done. Did come across a few issues at beginning as my version of excel/power query is older said could be incompatible, also cant just go to ribbon to import data from url have to go through and add new query to get url window to appear. other issue was kept getting privacy issue when trying to save and load had to redo a few times before realized i could change main query settings to allow to use same privacy setting. also noticed when editing files my excel didnt use proper editor with code/function popups just like normal text box for me, but got through all that not really issue just learning curve i should say with different excel versions will continue to complete project and keep looking for local data to try also thx again

[@thesmallman]

Thanks for the comment. I am using Office 365 so I guess there will be version control issues for say earlier versions of Excel. I hope you can find your way around it though. As for the region Covid data, I have seen it. There is drill down for the USA and China on the John Hopkins website. I got this from the Hopkins site just now. docs.google.com/spreadsheets/u/2/d/e/2PACX-1vRwAqp96T9sYYq2-i7Tj0pvTf6XVHjDSMIKBdZHXiCGGdNC0ypEU9NbngS8mxea55JuCFuua1MUeOj5/pubhtml# It outlines data by US State - confirmed, hospitalised, recovered, deaths and much much more. Hope it helps.

[@spydymn5114]

sorry my bad i was looking for github links for county/city levels- i did notice on there site it did drill down to county level but not city, no matter i am still going to complete project and continue on my own version for city level for my county and add data manually just now. thx again for everything

[@thesmallman]

Yup just at State level. That is a serious amount of data if it went down to State. Actually I have started playing with the model again. As the data has grown I have to change the file type to an ASCII file and import all data. This solves the glitch that happens after 200 days. I did not see that when I made this video. I might have to do a follow up video to show the change. All the very best man. I appreciate you taking an interest. I find this stuff fascinating. :)

[@MNToor]

Good stuff.

[@thesmallman]

Thanks! Appreciate the feedback.

[@seanmealiffe19]

Hi Marcus i have a question about dashboards, can you display your data from a pivot table via a picture imported from your file, while also keeping it interactive? Thanks in advance!

[@thesmallman]

Hi Sean - Yes you can! Have a look at the camera tool. It is simple with that tool. I have also written articles about how to have a picture display a chart. www.thesmallman.com/toggle-excel-chart This article shows you how to display a chart on a picture but the same is true for any Excel range including Pivot tables. However, look at the camera tool first as it is simple. All the best - Marcus

[@rebeccawood5512]

I'm loving it!! Great job! I'm about half way through. I like to take it slow and easy so I can REALLY ABSORB everything you have been so kind to share!! Question - shouldn't the formula in column L on the Rank tab =@INDEX(Piv!L5:L$200,List!$H$2) refer Piv!O5 instead of Piv!L5? I changed it to =@INDEX(Piv!O5:O$200,List!$H$2) so it would add up correctly on the dashboard. I think you actually recognized and fixed it on the video but the workbook still has the old formula.

[@thesmallman]

I saw that in the video and fixed it on the fly. I forgot to update the source file. I WILL - LOVE that you picked it up. Thanks so much.

[@thesmallman]

I will NOT post the completed model. You will have to create the lot to steal the booty. It is an investment in yourself :)

[@plumsolutions]

Well said!

[@thesmallman]

Thanks Danielle, I am interested in adding to people's learning, not their library.

[@curtisbrown2216]

Hello Marcus. I loved your Video, Trying to load the data and in the advanced editor I typed {URL} as Table=> (noticed the table was named URL instead of github. I am getting error message "the type identifier is invalid" What am I doing wrong. Thank you in advance.

[@thesmallman]

Hi Curtis - many thanks for pointing this out. I have updated the raw file. You almost got it. If you used (URL) as table=> you would have claimed the win. You will never have that problem again. I appreciate your comment very much as you helped improve this tutorial for the people coming after you. All the best.

[@curtisbrown2216]

@@thesmallman Thank you very much. Got me over the hump.

[@thaynankhanal5856]

Thank you very much for such a great video!!! I was able to complete the dashboard with a different Github data source. However, when I update the dashboard, it will add new raw data but it won't add an updated date on the timing tab. Hence not updating the data validation active date list. Am I missing something here? Everything else seems working.

[@thesmallman]

Appreciate your kind words and question. The model was not built for all time. A certain amount of maintenance is required with any model. If you make the dates on the Timing tab longer (better still create a table of the dates), then go to cell W2 on the Dash tab and extend the data validation list or point it to your new table, then your model should work as expected. Hope this helps on your journey.

[@sxnagg]

Hi Marcos... this is a great video, incredibly didactical for a big effort. Congrats on that. However, I have an issue, the query, since I've created it, doesn't update, do you know what it's possibly wrong? I would appreciate your kindness answer. Best regards.

[@thesmallman]

Hi Gonzalo - thanks for the kind words. Without seeing the file I could not diagnose a solution. If you want to send me the file at the email on my website I will be happy to have a look at it for you and get it working. All the very best.