Go Back   Forums @ The Digital Fix > Gadgets and Gizmos > Computing Forum

Reply
 
Thread Tools Display Modes
Old 24-03-2021, 18:16   #1
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
Any Excel users explain Dashboards to me

I have a spreadsheet which I update each month and which records various incidents and the locations that they occur

These locations are spread over 4 separate areas
North, south, east and west

Currently I share this sheet just as the data and people have to pick out what’s relevant to their areas

What I want to do is have a “dashboard” which defaults to all incidents, but allows the user / viewer to toggle to their specific area

What o would them want is for the dashboard to show which locations, within those areas, occurred the most

Can anyone explain this to me simply and how I can also make it look fairly pleasing to look at

Thank you
dunkle is offline   Reply With Quote
Old 24-03-2021, 18:48   #2
Wooglie
Rurouni
 
Wooglie's Avatar
 
Join Date: May 2001
Location: Farnborough
Posts: 6,126
Thanks: 1,684
Thanked 1,131 Times in 481 Posts
I think what you want is a pivot table.
https://support.microsoft.com/en-us/...9-f99134456576
Wooglie is offline   Reply With Quote
Thanked once by:
dunkle (25-03-2021)
Old 25-03-2021, 06:56   #3
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
Cheers
Yeah I use pivot tables currently
So I have my first tab with all data, which includes a column for address and a column for the area (along with dates and telephone numbers and a few other things)

I then have further tabs, one each for all areas as well as north, south, east and west

What I want though is to just have the one tab, and then then be a “dashboard” which my default shows data for all areas but allows the user / viewer to toggle on / off other areas. So can show all, or any combination of areas


Not sure if explaining self very well. Hopefully someone may understand

Cheers
dunkle is offline   Reply With Quote
Old 25-03-2021, 07:09   #4
bumfrog
bum of the highest order
 
Join Date: May 2001
Location: In a Shoebox, In A Hole In the Road
Posts: 13,192
Thanks: 239
Thanked 779 Times in 526 Posts
sounds more like you need to move over to a database. You'll get much more options for displaying a front end to people.
bumfrog is online now   Reply With Quote
Thanked once by:
dunkle (25-03-2021)
Old 25-03-2021, 07:52   #5
shteve
[o]EvilTwinkle
 
shteve's Avatar
 
Join Date: Sep 2002
Location: Cov
Posts: 6,653
Thanks: 623
Thanked 929 Times in 649 Posts
Could you not do it using a filter on the columns?
__________________
I've got a signature and an avatar :p
shteve is offline   Reply With Quote
Thanked once by:
dunkle (25-03-2021)
Old 25-03-2021, 07:52   #6
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
The issue being its work based and I am limited to want software have access to
I believe a basic “dashboard” with interaction is possible but just can’t pull it off
dunkle is offline   Reply With Quote
Old 25-03-2021, 09:07   #7
uk_mez
Groover
 
Join Date: May 2003
Location: UK
Posts: 576
Thanks: 2
Thanked 18 Times in 14 Posts
Could you use Slicers to select the desired data set?

https://www.myexcelonline.com/blog/u...table-slicers/
uk_mez is online now   Reply With Quote
Thanked once by:
dunkle (25-03-2021)
Old 25-03-2021, 11:15   #8
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
Oh that looks like some what what I need. Shall read up. Thanks
dunkle is offline   Reply With Quote
Old 25-03-2021, 12:09   #9
bumfrog
bum of the highest order
 
Join Date: May 2001
Location: In a Shoebox, In A Hole In the Road
Posts: 13,192
Thanks: 239
Thanked 779 Times in 526 Posts
Quote:
Originally Posted by dunkle View Post
The issue being its work based and I am limited to want software have access to
I believe a basic “dashboard” with interaction is possible but just can’t pull it off
sorry I can't more, but good luck
bumfrog is online now   Reply With Quote
Thanked once by:
dunkle (25-03-2021)
Old 25-03-2021, 20:36   #10
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
Quote:
Originally Posted by uk_mez View Post
Could you use Slicers to select the desired data set?

https://www.myexcelonline.com/blog/u...table-slicers/
Worked brilliantly
Thanks

Now just need to try and work out how to make it look “fancy”
dunkle is offline   Reply With Quote
Old 15-04-2021, 06:31   #11
dunkle
Trusted User
 
dunkle's Avatar
 
Join Date: Nov 2002
Posts: 10,670
Thanks: 1,085
Thanked 526 Times in 229 Posts
Few more questions if I may

1) How do I “colour” the background of a workbook so its not all white cells and then have it remained filled with that colour ?
I tried using the fill option and that works fine to start with, but when I then place my Pivot tables over them and they change size when using “slicers” the colour goes and the old grid / cells reappear

2) I have one tab in my workbook for “trends” - these show the previous 3 months figures for a dozen or so areas and then these are sorted by number of incidents in those areas. This is fine for showing who was “top” on those months, but is there anyway of finding an average for those 3 Pivot tables (one each for each month) and having anything above average be highlighted in red ?

3) Graphs and charts. I have a further tab for data year on year comparing figures going back to 2018.
This works fine however as it grows its starting to look a little “busy” - Is it possible to again have the “highest” figure for each month of each year be highlighted in someway ? Even if it is just that figure at the end of the bar ? And also, is it possible to have the chart show an “average” line on it so we can see when over the years we have been above average ?

Hope this makes sense

Thanks
dunkle is offline   Reply With Quote
Reply

Bookmarks

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is On

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I make Excel 2013 show colours like Excel 2003 ? Tempest Computing Forum 6 30-06-2015 01:06
Excel help : simple macro to copy from Excel to Word driver8 Computing Forum 5 10-05-2011 19:12
SQL "dashboards" Roger Belly Computing Forum 6 13-08-2009 07:30
[EXCEL VB] Reading an Outlook msg file from Excel JiBeRjAbEr Computing Forum 3 11-05-2004 11:26

All times are GMT. The time now is 07:55.


Powered by vBulletin® Version 3.8.9
Copyright ©2000 - 2021, vBulletin Solutions, Inc.qq
Copyright ©2000 - 2021 Network N Ltd.