On september 25th 2009, the island of Aruba held their parliamentary elections. Local television station, TeleAruba, contracted me to create a system to predict and track voting results as they are announced. I started my research about one month prior to the election date since that was when the station contacted me for the first time. I wasn’t sure which way I was going to take this. One thing I knew is that whatever direction I would take, this would be something I have never done before. In the past I have worked with Excel to do the same predictions and graphs etc. Deep down I wanted to do something new and not with Excel. First I thought to go web based with flash graphs. I tested a couple of tools, but simple tasks like changing bar colors on a bar graphs would take scripting knowledge and skills. I did not have the time to develop those skills. Then I found a book on the shelves of a local bookstore: “Excel 2007 Dashboards & Reports for DUMMIES.” I never thought that I would buy one of these books because I do not consider myself a dummy. To my surprise, this book ended up being the best investment I have made in a while. I read the 298 page book in one night. It was clear to me that this would be the direction I was going to take since I already was familiar with Excel 2003. Here are the techniques I have used to create an elections dashboard in Excel 2007.
- One of the first things I did was to separate the data from the presentation. The Excel 2007 dashboard would connect via ODBC to a Oracle Express database. The data went straight into a pivot table report, then I would refer to the cells in the pivot table to extract the data and do further calculations.
- To add functionality I turned to macros. Macros are very easy to create in Excel. If you could press record button on your cassette recorder from the 80’s you can record macros in Excel 2007. Via a macro on the presentation sheet, I would change the pivot table filter to bring in new data or change the views.
- Graphs were made from the referenced cells (mentioned in point #1) instead of the pivot table directly. If I had to change from data source, I would delete the pivot table and create another one on the same sheet. This would give me flexibility. Combined with the macros mentioned above, the graphs become dynamic and change in front of the audience’s eyes as new data is presented.
- Another technique that I liked very much is the dynamic labeling. This has helped me very much in presenting key information like total votes counted up to a particular moment, the total voting population, names of the different districts, and names of the schools where the polling booths were located.
- Probably the most impressive feature Excel has (I understood that it was already in the 2003 version) is the dynamic picture. You select a range of cells, copy them and paste them as a dynamic picture link on another sheet. The pasted cells appear like a picture on the sheet, but it changes dynamically as the values from the original cells change. I was able to incorporate this into the elections dashboard highlighting comparisons with results from previous elections with the current one and displaying % difference. To save space, I even layered the dynamic pictures one on top of each other and with a macro I would bring the desired information in picture on command. This happened in a particular corner on the dashboard making it easy for the user to get used to (sort of a dynamic spot on the dashboard where the same space is re-used to present different information on command or as desired) This last trick is not in the book. I came up with this because I ran out of space. Sometimes certain situations force us to think out of the box and come up with creative solutions using standard features in applications in a way they were not intended to be used.
- XY Scatter plot was used to display pictures of selected records. This also is not in the book, but I found an example online. It is pretty impressive and gave a functionality to my Excel dashboard that many may not think was possible. Using this technique I created an illusion that the background of the graphs mentioned in point #3 change dynamically. For example one of the graphs had a dynamic label for a school name then the background of the graph would have the corresponding picture.
A month ago, I could only make Excel graphs and pivot tables. Today, thanks to an amazing book and many hours of practice, additional research, trial and error, sweat, and sleepless nights I can say that I have successfully build my first Excel 2007 Dashboard and so can you!
About the Author
Name: Remigio J. Rasmijn
Education: Bachelor’s Degree in Management Information Systems from Florida International University in the fall of 1998.
Contracted since 2001 by local television station to track and predict election outcomes. This is his 3rd election working for the television company. Next assignment will be a Quality Control Dashboard for the local university.
He developed an Excel training course: Using Excel as a Survey Analysis Tool.
Since 2006 he has been working as an ICT Instructor at a vocational school, Colegio EPI (Economics Unit). He teaches Excel, FrontPage and Access. He also does consultancy on survey design and analysis using Excel as the analysis tool.
Another passion of the author is Information Security. He focuses on information security awareness.
The author is also a percussionist and plays congas, bongos, timbales, djembe (African drum) and many Brazilian percussion instruments.
For more information please visit Colegio EPI Economics Unit.
No comments have been posted yet.