User Interface

You are currently browsing the archive for the User Interface category.

Monday, September 29, 2008 was an ugly day.  How ugly do you ask?  The Dow Jones Industrial Average (DJIA) plunged nearly 800 points with violent moves all day as the House rejected the bailout plan.  Ouch babe.  This one will unfortunately be one that will be remembered.

Yahoo Finance provides historical data for Equities and Indices such as the DJIA.  What do we do with a bunch of DJIA closing and their associated dates?  Hrmm… graph anyone?

Download the Sample File

This graph is fairly easy to create but it does not tell us much.  After all, I think it is pretty obvious that the US economy has grown since 1928…  I am thinking that it would be a really nice & spiffy if we were able to create an interactive version of this graph.

Download the Sample File

Remember the scrollbars from when I purchased a vehicle?  Yeah, let’s use them.  Let’s also use another cool feature, “Name Manager“.  You can find the Name Manager within the Formula Tab within Excel 2007- it also exists in Excel 2003.

Quick explanation for Excel Names.  For many financial related spreadsheets, the concept of interest rate is used extensively.  Lets say we designate cell B1 as our interest rate. To make our spreadsheet formulas easier to read, let’s Name cell B1, iR.  To do this we have two main options, either go into the Name Manager and create the variable iR (=B1).  Or simply, edit the cell name next to the formula bar.

Now referring to interest rate is easy, instead of =B1, we can utilize =iR.  The main advantage of this approach is that it is cleaner and easier to understandNames can also be dynamic.  As you can see, I have defined three complex names with complex formulas.

Names can also be referenced as series within Graphs.  Once we Select Data > Edit Series, we are able to refer to the Names previously defined.

So here is ultimately how the Excel spreadsheet works.

  1. Scrollbars are created which drive our starting date and zoom features.
  2. Names are created which create arrays based on the scrollbar values.
  3. Graph Series refer to Names.
  4. Graphs will update based on real time changes in the scrollbar!

So with that, let’s analyze a one year return.  I see a sea of red.  Ouch.

Download the Sample File

Please, let’s hope for an Economic recovery soon!

Hours after Ron bought his new favorite car, he applied at the dealership to become a salesperson.  “I built a great car selection tool, now I can use it to make good business.”

But he knows something was not quite enough – a good interactive user interface should further remove the user from the raw data.  And he is right.  As his experienced dealer superior recalls, if you give too many detailed choices to the average customers, they will get too stressed out and freak out.

So Ron decides to build a new UI – one that allows the user to select among the car options and displays the specs (and price) for the car of interest, one at a time.

Download the Sample File

First, he makes a list of cars currently available at the dealership.  With ActiveX Control’s Option Button (Alt L > I > O), the basic selector is created as follows:

In this case, like before, he links each of the Option Buttons to a cell to the right of the car name.  Note that this could be a tedious process once the list gets long.  Consider using VBA to automate the task.

To develop a good UI, it’s important to have an interactive way to distinguish the selection.  Note how your browser changes the color of a link that you click on?  Ron decides to first format this entire table to the de-selected look.  The column of TRUE/FALSE values might confuse the customer, so he hides it by changing the font color to white.  Note that the graying out of fonts could add contrast to enhance your selection, but it’s a matter of personal style.

Now, he adds conditional formatting to the table.  In this case, one set of rules is applied to each of the three columns.  Excel 2007 gives you this really neat Rules Manager for conditional formats, but the same effect can be done in earlier versions of Excel.

This is what the result looks like.  When the customer clicks on a car of desire, the entire selection lights up!

Lastly, he uses a combination of lookup functions to create a detailed table of specs to display on the right:

Download the Sample File

With such an interactive UI, obviously, Ron’s car dealer business is taking off!


Not too long ago, I decided it was time to evolve my mode of transportation.  Sure, I had the tools of the trade- shoes, bicycle, subway pass.  But I desired something more, something greater than myself which would weigh over 2 tons.  Yes, my friends- I was looking for a new car.

Being a car buyer is quite stressful especially if you have limited experience.  Although some of you may believe the process of purchasing a vehicle is an artistic notion, I decided I wanted to consider just a few factors: price, engine, miles per gallon, passengers, automatic/manual transmission.

Research is very important for consumers.  The first item that I wanted to determine in my new quest for a new mode of transportation is, what are the current hot cars?  Thanks to my friends at Yahoo Auto, I was able to determine 8 cars that I wanted to analyze.  A few tweaks here and there and I was able to determine basic information which I would need for my analysis.

Great.  Since I want to use Excel for the basis of this analysis, I have decided to build a nifty graphical user interface (GUI).  OK- great.  So what does this mean?  For the model intensive folks, this means no more lame-O blue cells (or whatever color you firm uses) to represent inputs or random highlighted cells where you “input”.  No, sir.  We want to build something that looks prettier and functions more intuitive.  That’s why I have decided to add some fancier items such as scrollbar, checkbox, drop down and radio buttons.  Something like this…

Before I begin, a few little notes:

  • This model contains basic calculations which will not be discussed in this post.
  • Conditional formatting strikes again!  (Remember, use this technique to quickly troubleshoot + dynamically highlight cells instantly)
  • I have officially began my migration to Office 2007!  (the file is saved as 2003 XLS format).

As many of you know, Excel 2007 has a pretty fancy item called the Ribbon.  We are looking for the Developer tab within the ribbon.  Don’t see it there?  By default, many of you may not have this option available.

  • Office Button > Excel Options > Popular > check “Show Developer tab in the Ribbon”.  (Alt F > I > Alt D)

Now that we have our Developer tab sitting nicely on our Ribbon.  Let’s discuss why we added this tab.

  • Now let’s look for the ActiveX controls.  Developer > Insert (Alt L > I).  Here are some pretty nifty inputs we can utilize to make the GUI that we so desired.
  • Each of these ActiveX controls has their own individual properties including Appearance + Characteristics.  There are tons of options, so please take the time to explore each individual option.
    • Before you can modify the properites of each ActiveX control, you must first enter “Design Mode”.  Developer > Design Mode (Alt I > DM).  To view each ActiveX control’s property simply choose an existing item and choose Properties within the Developer tab.
    • Of all the options, there is one which is VITAL.  It is known as LinkedCell.  This is the link between the ActiveX control and Excel.  Make sure you set this property to refer to the Excel Spreadsheet. 
    • There are other useful properties as well, but I will let all of you dig into the spreadsheet to figure them out! I’ll update the post if anything is confusing.

At the very end, we are able to use the GUI + the settings.  Voila.  Here are our filtered results!

In case you are wondering- the blue cells mean that there is a match in criteria.  The number of smileys designate the number of total matches.  So while you are at it- download the Excel spreadsheet and like a fancy car… take it out for a spin.  Vroom Vrrrrroom indeed.
~ FIN~
PS.  You might be wondering what car I purchased.  Yes, indeed …1998 Red Toyota Camry.