The more I get into Excel Dashboards the more excited I get. This is a great video that I hope will give you some ideas and techniques to present lots of data on a number of Excel Charts. This video is a little longer than the ones I usually post but it is well worth 15 – 20 minutes of your time.
The video looks at a large table of sales data from a number of US states. If you put all the states on a graph at the same time then there is no way you can see any trends or information for 50 lines at the same time.
The solution that this video shows is actually very simple but is has a number of steps. First of all we make a smaller grid to show only 5 states. Next we then fill this grid with a selection of the data in the main grid using VLOOKUP command. Finally we make a number of charts and link them to each row on our smaller grid. Then finally you can make a vertical scroll bar to change the data in the small grid – and is by magic the data in the smaller grid updates and so do the charts. Neat.