Hi
I have inherited and use a basically static spreadsheet, data is manually input from a snapshot taken close of business last day of each month.
The report is updated monthly adding the new month and dropping off a month from twelve months ago.
Since registering and working through your Excel course I would like to make the report dynamic but don't know where to start.
I also need to keep in mind inexperienced viewers of the report. Normally I would send it as a PDF for them to view.
Any tips would be most appreciated.
I have attached a sample of the report.
Regards
Maureen
Hi Maureen,
Welcome to our forum!
Thanks for sharing your file. It's not clear who is entering the data or where the data is coming from. e.g. is it in a report that you can extract electronically or does it come in from various sources via email etc.
Who do you want to make the report more dynamic for; you, creating it or the user?
Mynda
Hi Mynda
Thank you for your reply.
I manually populate the report from pivot tables in other spreadsheets. The other spreadsheets (attached) are generated as a snapshot of the total membership from our database each month.
I feel it is too many pages that could be consolidated and also too full of figures that would be better as graphs etc.
My main concern is the inexperience with Excel of the users.
Maureen
PS. The main spreadsheet (Active) would not upload it was too large, shortened version attached, it is normally approximately 16000 records in length.
Hi Maureen,
It's great to see that the data is in a tabular format and that you already use PivotTables to summarise it.
I would be aiming to create all of your reports with PivotTables. If the layout of the PivotTable doesn't suit your needs then you can use the GETPIVOTDATA function to automatically reference the PivotTable data, effectively automating the creation of your reports. You definitely should not be typing numbers from one report into another.
I would also be aiming to provide some kind of visual representation of the data, as it's quicker to interpret and more likely to be read by your report audience. Charts, Sparklines, Conditional Formatting are great tools for this. If you wanted to provide some kind of interactivity then you could use Slicers to allow the users to filter the data.
I wouldn't be worried about inexperienced users as they can simply look at the reports and if they wanted to take things a step further they can use the Slicers, which are very intuitive.
I'd start by trying to recreate the reports you have with PivotTables. Be flexible in the layout though as it won't be identical to what you currently have. Play around with charts, Sparklines and conditional formatting. I hope that points you in the right direction.
Mynda
