Category Archives for Advanced Excel
Rollovers for Gantt Charts
Before we start, you might have noticed some renovations on this blog. Yeah, it was time for a change. I’m working this template out, so sorry if some of the formats seem off. Also, I have started a new facebook page. I’ll be keeping it updated with stories from my favorite blogs. Also, you can […]
Continue readingSpreadsheet Guidelines and Best Practices
Today, I found this journal article, The role of OR [Operations Research] specialists in ‘do it yourself’ spreadsheet development, from the European Journal of Operational Research, by John S Edwards, Paul N Finlay, John M Wilson. The article outlines 20 Guidelines for Spreadsheet Development and 21 Best Practices Points, which I excerpt below. Guidelines for Spreadsheet […]
Continue readingEasy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 2)
Wow, it’s been a while since my last article. I’ve actually been pretty busy the last few weeks putting the finishing touches on an Excel deliverable for one of my company’s clients. We delivered the product about a week ago and things have just now started to slow. So now it’s back to blogging. If […]
Continue readingEasy One-Way Sensitivity Analysis on Weighted Sum Models in Excel (Part 1)
This article describes what I like to call “Easy One Way Sensitivity Analysis” in Excel. I’ve used it before in several dashboards. If you want to try it out for yourself before reading ahead, both Healthcare dashboards on the Fun Downloads page implement my Easy One Way Sensitivity Analysis method. Here’s a pic of one of […]
Continue readingTurn an Excel Spreadsheet into a Bitmap Image
So I’ve created a spreadsheet program that will import a selected bitmap and use Excel as a canvas to display it. Each cell is a pixel. Here’s a picture of president Obama in 8-bit glory: And here’s my current March Madness bracket in stunning 24-bit: You can download the file below, and instructions are included […]
Continue readingEdit button, please
This is a long overdue article, my apologies. The basis of the article surrounds a question in the comments section of an article on Chandoo.org (comment #75) written about the hyperlink() method I discovered and wrote about here. In Chandoo’s example, when a user rolls over different “hotspots” representing different datasets, the corresponding data comes into […]
Continue readingHow to Create a Rollover Effect in Excel: Execute a Macro When Your Mouse is over a Cell
“Rollover B8 OV1” (like Rollover Beethoven…? get it?)Update 17 July 2011: This blog entry was featured in Chandoo’s latest post. So if you’re coming here from his site — welcome! original post: This post was inspired by Chandoo’s post on hyperlinks in Excel. If you haven’t checked his website out yet – you should. It’s […]
Continue readingGetting a Handle on hWnd
Sometimes, you’ll want to programmatically take advantage of Windows outside the capabilities readily available through Visual Basic for Applications (VBA). For example, both eliminating the “Close”-button or creating a more opaque background on a window require the use of the Windows API. Doing this in Visual Basic isn’t too hard, but there are some distinct […]
Continue reading