To add scroll bar in the graph to scroll through the available values. If you fit too many data points in any trend or bar graph then it looks very cluttered and shabby. Look at the sales trend graph below...
In the above charts, it's very difficult to make out anything as it contains lot of information with too many data points and hence they are overlapping and very difficult understand.
Question is then what should we do to represent such large data in limited available space?
How about adding scroll bar for to the graph and display only few number of data points so that the chart is understandable and more readable? One can scroll through to see more data points. See the example below...
How to insert scroll bar in the graph:
- Make your data ready for all the values of the X axis. For our example, we need to make data for all the days starting from 1-Jan to 31-Mar.
- Copy few data points to a new location from the base data and make the
chart only with few data points.(click here to see steps for making graph)
- Insert scroll bar object from developer ribbon. (if developer ribbon is not available refer enable developer ribbon in excel 2010 or enable developer ribbon in excel 2007)
- Set the below properties of the scroll bar (enable Designed Mode to select the Scroll bar)
- LinkedCell: The cell in which Excel stores the value of the scroll bar. In our example we will give M1 for this property.
- Min - This the value of the scroll bar when it's at lowest point i.e. at the left most point. For our example we will give this value as 0.
- Max - This is the value of the scroll bar when it's at highest point. i.e. at the right most point. In our example we will give this value as 77. (the difference between the highest and lowest point of X axis of the all data minus number of values visible on the graph i.e. difference between 31-Mar and 1-Jan minus 12)
- Large Change & Small change: It is the increment value for every point change in the scroll bar. In our example, we will increment value by one since on each left or right arrow click of scroll bar, we want the period of the graph to be shifted by one day.
- Based on the
scroll bar value, insert formula to get X axis values of the graph. In the
given example I have entered the starting date in one cell and added the scroll
bar value to that to get the starting point of the X axis. Remaining values of
the X axis are derived by adding 1 to the previous value.
- Use lookup
formula to get the data for selected period from the entire data. Exit the Design Mode and try changing scroll bar.
I have explained simple method of preparing dynamic chart using scroll bar in this article. In addition, you can further enhance and add more functionality like providing drop down list also. Refer to how to create dynamic chart for adding this feature.
Note: for providing scroll bar as well as drop down list in the same graph, you will have to modify your base data and the lookup formula to derive value for the graph.
If you want an out-of-the-box solution and you have Excel 2013, you can now benefit from the various apps that are published in the MS Office Store. For example, the yellohcharts app has build in zooming function and timeline navigation. If you go to www.yellohcharts.com, you can see the demo video.
ReplyDeleteIf you go to https://store.office.com/appshome.aspx and search for the Yellohcharts app, you can try it for free...hope this helps.