Dear Fellow Excel Enthusiasts,
I created a dashboard with some charts and data summary tables based on raw data with thousands of rows. The dashboard has multiple slicers and the users are able to dice and slice the data to see what they want - thank you Mynda for your great course on dashboards!
I am asked now to include a filter/slicer where the user could pick a particular date in the past and see all the numbers after that date, or see data in a particular date interval. Date grouping will not work for this purpose, as there has to be a possibility to chose an exact date. Adding a regular slicer with hundreds of buttons, one for each date is not useful either.
I believe that something like this could be achieved with a Timeline in Excel 2013, but unfortunately we will be sticking to Excel 2010 for a while. Is there a way to achieve this in Excel 2010 without a macro?
And, if macro has to be used, how do I do this? Would you be so very kind to point me in the right direction?
Thank you.
Blanka
Hi Blanka,
I wouldn't use a Slicer for this since there'd be (potentially) too many dates. Instead you could use dynamic named ranges to return a range of cells based on the start and end dates the user selects from data validation lists or combo boxes.
In session 5.09 of the Dashboard course I covered how to do this. You should have the session 5 workbook from the Dashboard course that you can refer to and on the sheet 'Analysis Forumlas' there are examples you can copy.
I hope that points you in the right direction.
Mynda
Hi Mynda,
I was looking at the dynamic named ranges and combo boxes, but my file doesn't contain the raw data, it's just linked to it through Power Query. From the Power Query I built my pivot tables.
I may be overthinking it, but in order to use the combo boxes or lists I would have to have each date in its separate row in the pivot table, correct? Then I would end up with a huge pivot table with thousands of lines. Or am I missing something and just not noticing the answer?
Thank you for your pointers.
Blanka
Hi Blanka,
I just checked which courses you took and see that you also did the Power Pivot course.
Why don't you use Power Pivot and load both of your queries into there. Then you can add a Date table so you can create a relationship between the two tables from Power Query using the Date Table, and create your PivotTables from there. That way the PivotTables share the same Pivot Cache.
Otherwise, you can use formulas as I already mentioned, and yes you'd need every date represented in the PivotTable, or load the query to an Excel Table in the workbook.
Or you can use VBA. If you want VBA then you need to ask for help in the VBA forum.
Cheers,
Mynda
Hi Mynda,
It doesn't look like I can get Power Pivot available to everybody in my office, so I don't think I can go this route.
I'll have to play around with the formulas.
Thank you for your help.
Blanka
