Hi
Can you help I am finding it hard to use the data below in a chart, pivot table with sliders with the Legend as the Topic, the bottom X axis as Month-year and left Y axis as the numbers. I want to have flexibility to show the data with sliders for month-year, topics etc.
I want to set up either in a chart or a table which is linked to another source so if updated the chart and table is as well. Then the sliders would let me see the separate Topics, with the months or one of the topics with Jan to Mar 2019 data.
Hope this makes sense and appreciate any help you can give me.
Dave
| Topic | Apr-18 | May-18 | Jun-18 | Jul-18 | Aug-18 | Sep-18 | Oct-18 | Nov-18 | Dec-18 | Jan-19 | Feb-19 | Mar-19 |
| Litigation | 5 | 15 | 20 | 25 | 30 | 32 | 43 | 38 | 50 | 43 | 12 | 20 |
| Cases | 5 | 15 | 20 | 25 | 30 | 32 | 43 | 38 | 50 | 43 | 12 | 20 |
| Family | 5 | 15 | 20 | 25 | 30 | 32 | 43 | 38 | 50 | 43 | 12 | 20 |
| Civil | 5 | 15 | 20 | 25 | 30 | 32 | 43 | 38 | 50 | 43 | 12 | 20 |
Hello David,
I am not sure I understood your question to the fully, but let's do a try.
Please see attached file for an example.
I have put your data in a tabular data format and from that I have then a Pivot Table with a timeline instead of a slicer, the functionality is the same.
I hope this will give you some help.
Hi Anders
Thanks very much for that attachment that is good. But I had problems showing the Months as April to Dec 2018 and Jan to Mar 2019 it kept saying 'Sum of Jan 2019'
I will work with this but I want a financial year so Apr 2018 to Mar 2019 and that to appear not just the Month?
Thanks again
Dave
Hi Dave,
I have ungrouped the date and have instead formatted the view using with ordinary number formatting. Even though you see the dates in the Pivot Table as Apr-18 it is still a date value. If you want this heading to be like text then you need to create an extra column in the Excel Table.
Hi Anders
The table I started with had months in the Column headings and Topics as Row headings and that is how our data is collected.
But are you saying it has to be in a Tabular Data Format, see below, for me to be able to create a chart and add slicers etc?
Thanks
Dave
| Date | Topic | Cases |
| 01/04/2018 | Litigation | 5 |
| 01/04/2018 | Cases | 5 |
| 01/04/2018 | Family | 5 |
| 01/04/2018 | Civil | 5 |
| 01/05/2018 | Litigation | 15 |
| 01/05/2018 | Cases | 15 |
| 01/05/2018 | Family | 15 |
Hello David,
I can at least state for sure that working with data setup in a tabular format will make it a lot easier to work with the data. Here is a good artcle about it. https://www.myonlinetraininghub.com/excel-tabular-data-format
Whenever I get a task to create a report I always start with rearring the data to tabular format, as it saves me time later on.
