I have a database that is filled in by multiple people at different times – I would like to create a graph that shows a time series analysis of only the last quarter’s data. I know how to set up a table such that as the table is updated or added to my plot also updates. My question though, is there a way to tell excel to only graph say, data from 3 particular months, in the order they occur, without going in and sorting my data each time?
So for example, my list is say 100 rows long – each row has a date and a data figure. The rows are constantly being added to, and not in time sequence. So right now I may have row 100 dated 6/1/14, and then row 101 is added by someone and its 1/1/14, and then row 102 i added and its 5/1/14, and so on. Is there a way to tell Excel that I want it to look at my table and graph sequencially only the last 3 months of data. So in the above example, row 102 would appear on my graph automatically, but row 101 would not since its more than 3 months ago. Is there a way to do this, or do I need to just sort my data by date each time and just create my graph from that?
Thanks for any help/
Use a pivot table: Guide to pivot tables
- Make sure your data is in a table – If not select the data and on
the home tab press Format as Table
- While your table is active go to the table’s TABLE TOOLS that pops up
- Hit Summarize as Pivot table
- Choose where to place it
- Build your pivot table as needed – Dates in the series, count in the values
In the pivot table’s PIVOTTABLE TOOLS tab go to the ANALYZE tab
Build your chart as needed.
Now you have two options:
- Use the dropdown arrow above the dates in the pivot table to select the date range you want (you’ll need to tick Select Multiple Values)
- In the pivot table’s PIVOTTABLE TOOLS tab go to the ANALYZE tab and select Insert Slicer, choose the date field and drop that wherever you want. This will create a presentable tool next to the graph that can be used to change the date range – hold Ctrl and click to select multiple dates.