I have been provided with an excel table which is quite cumbersome. In aged care the information management have to access and provide analysis on is quite extensive but this file is not set up well.
In the organisation who provided the chart, there are 171 residents and there are 32 high risk aspects of care which need to be analysed. Not all residents will have experienced the high risk aspect of care. I'm not really sure how to set up the database and then how to create a dashboard to obtain information about who has what risk.
I have attached a copy of the file so it is clear what I have been given to start with.
I'd be grateful for any assistance. My excel skills are not quite up to this as yet 🙂
I had a think about this a little more and thought that breaking the information up into different groups and putting onto separate tabs may assist with getting this to be able to analyse it would be helpful. I have attached an updated file.
After thinking about this for sometime, I realised I had to sort the data more and then add the pivot tables and charts to create the Dashboard.
For some reason I can't get the "month" button on the charts to show the month - e.g. "July". It just shows as "Month" even when filtered to a specific month.
Also, I had to put the heading in as a text box separately, I couldn't get this to be there automatically
This still needs some formatting and finalising - but if there are any thoughts on what I have done so far would be greatly appreciated.
Hi Sue,
Great to see you're making progress. The 'Month' button isn't designed to show the selected month. It's simply a drop down button that allows the user to choose the month. If you prefer to show the chosen month then a Slicer is better.
You can add a chart title by clicking the + symbol that pops out on the right of the chart when you select the outer edge of the chart. You'll see a list of elements you can add, including a chart title.
You need to reformat your data so that it is in a tabular format. e.g. taking the 'Falls PT' sheet you should have the following columns:
- Month
- Fall Type (this column will contain the labels in columns B:AF)
- Count/Value (whatever you want to call the column that contains the numbers)
When you format your data in a tabular format you can use Slicers to allow the user to filter the data based on the fall type(s) they want to see in the chart.
See example file attached, sheet Falls - Tabular Data for an example of how your data should be structured and how you can use Slicers and bar charts to display your data.
I hope that points you in the right direction.
Mynda
Hi Mynda
Thanks for the example file. I'll have to research more to get an understanding of it. I've quite a bit more learning to do to get to this point I think.
I'll do that over the next month and submit the updated version at the September meeting.
I'll give you an update so you can comment on the progress and any further improvements I can make.
Regards
Sue
