Formatting

You are currently browsing the archive for the Formatting category.

Another thing that happened to me since 2009 was home ownership: bought a place and put my name on a serious mortgage contract.  As I go about doing my daily business, I often wonder: how much of this home do we actually own, and how much do we still owe??

What came immediately to mind is the fundraiser tracker.  You’ve probably seen something like this in conjunction with your alma mater, local animal shelter, or kid’s day care asking you for money:

0823bars2_0

 

I wanted to create a visualization like that, in Excel, next to my family’s finances.  Like a true engineer, I absolutely hate manually updating anything, so this had to be automatable.  How do I do it?

 

 

Let me spare you of the gruesome details of my experimental process, and just say that you can draw pictures with bar charts.  Wanna draw a circle with an absolutely rectangular bar chart?  Let’s do it visually:

Step 1 – Create a stacked bar chart.  The number of stacks that you need depends… and trust me for now that you need 4 for a circle:

0823bars2_1

Step 2 – Make sure you have enough bars.  100 is always a good starting point in my opinion.

0823bars2_2

Step 3 – Set the gap between bars to 0%.  Now this chart looks like some European country’s flag.  Still quite rectangular:

0823bars2_3

Step 4 – Adjust the height of these 100 bars so they look like this.  Note how some stacks are much taller than others:

0823bars2_4

Step 5 – Resize your chart into a square.  Clean up the colors and update the axes ranges, and…

0823bars2_5

Ta-da!  Here’s the circle!  Don’t like how pixelated it is?  Add more bars!

0823bars2_6

 

The key here is to think of a picture as a rectangle with w-by-h pixels.  Then you can replicate it with w stacked bars each having a maximum height of h.  How many stacks you need depends on how complicated your graph is.  Is it possible in theory to draw a Mickey Mouse?  Sure, but I intend to stick with simpler images that can be abstracted into a few layers of colors.  You’d need to either set up formulas or a lookup table for the height of these stacks on each bar.  For a circle centered on (a,b), remember the formula is (x-a)^2 + (y-b)^2 = 0.

 

Back to my original goal of drawing a target tracking graphic.  I decided to go with a house complete with a chimney, even though our home looks nothing like it.  It involves a handful of vertical, horizontal, and sloped lines:

0823bars2_7

 

The shape of my house, including where the chimney is, is entirely parameter-ized.  So by updating a few cells I can get to alternate versions like these:

0823bars2_8

 

Lastly, I needed to fill it in with the progress tracker, as follows.  Note that my blue bars and white bars within the house are two separate data series, with heights adjusted dynamically to the input value:

0823bars2_9

0823bars2_10

 

Just to illustrate how flexible this setup is, if you don’t like my vertical split, the complete/remaining portions can be divided horizontally as well:

0823bars2_11

 

This way, I’ve got a bar-based graphics that updates automatically.  The only thing left to do is actually paying off that mortgage!

Only because my soon-to-be-former coworker Monica asked about this topic, I decided to dust off this blog (finding that login was no easy task!).  It’s hard to believe but the date stamp on the most recent post was… six years ago!  It’s not that I’ve forgotten about this blog, but I’m borderline at a point in life where you can’t teach an old dog new tricks.  And when I don’t learn new tricks, it’s hard to blog about them.

Well, I did learn one thing more recently than 2009: bar charts.  Remember when we all first learned about these fundamental expressions of statistics, like, in fourth grade?  Drawing the axes with a pencil and measuring out the height of each bar with a ruler was among the most painful part of any math class that I can remember… and yet, this two-post series is about those.  A steroids-injected version of those boring bar charts.

In its first part, we will examine how to create waterfall charts with Excel’s native capabilities.  A waterfall chart is one where floating bars are used to illustrate the progression of a number.  For example, I used one in my recent guest blog entry on Hungry for Points, to outline how my points & miles balance changed between two points in time:

0823bars1_4
Added some, subtracted some, and a new balance!

 

On the surface, it seems impossible to draw floating bars with Excel’s charting tool.  That may be true, but the trick is to craft an optical illusion with white bars.  The same idea can be applied to both overlapping clustered bars and stacked bars.  Let’s see how the former works:

Step 1 – Determine the step-wise cumulative value, and the increment/decrement amount:

0823bars1_0

Step 2 – Create a clustered bar chart where each step has two bars: for increments, bar 1 should be the previous cumulative amount and bar 2 should be the new cumulative amount; for decrements, it’s the opposite.  For the first and last steps representing the balances, bar 1 should be 0:

0823bars1_1
“Bar 1” is orange here.

Step 3 – Make sure bar 1 is in front and set the overlap to 100%:

0823bars1_2

Step 4 – Fill bar 1 white, or whatever your background color is:

0823bars1_5

Step 5 – Color the individual bars:

0823bars1_3

Step 6 – Add data labels to explain your chart.  These can easily be formulaic and linked to your numbers:

0823bars1_4

 

Voila! This may look exotic, but it really isn’t!

Stay tuned for the next article in this series where we take this technique to the next level!

 

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!


 

I’m dedicating this post to the first person to compliment on the Excel Workbook blog…

Some time has passed since Ron‘s Beijing Olympics post.  It’s now 2020 and Laser Tag has become an official Olympics sport.  The globally famed Laser Tag champion Wang Y. blew her way through the games with amazingly acrobatic shooting, docking, and sniping skills.  After the thrilling final round where all athletes gave their 101%, you as the game admin recorded the scores as follows:

Download the Sample File

Having studied Ron‘s medal comparison model for the past three Olympics, you now have full appreciation for the neat user interfaces that Excel allows you to create.  Now you want to use that knowledge to create an entirely new and personalized score reporting system.  Here’s what you have in mind:

  • Scores will be presented in a bar graph
  • Each participating athlete will receive a personalized score card
  • Each athlete will be listed first on her score card with name spelled out on the graph, in honor of her excellent sportsmanship
  • All the other athletes will be listed in alphabetic order by country code; their names will not be shown

For example, the score card for China’s Wang Y. should look like this – her name, country, and score are listed on top, followed by the other countries in order: Australia, Brazil, (skipping China), Germany, and so on.

While this task could be done fairly quickly by hand, it could get very tedious.  You are a busy game admin after all, and don’t have the time to personalize 10 score cards manually.  You already know how to locate the chosen athlete, but how do you re-order the remaining list?

The INDEX() function comes in handy.  It returns the value of an element in a table or an array, selected by the row and column number indexes.  It often complements the MATCH() function, and can be considered as an inverse function to MATCH().  Note how it’s accomplishing the same task as the OFFSET() function used for Beijing Olympics.  Now you write:

=INDEX(
          [score array],
          CurrentItemPosition – (CurrentItemPosition<=ChosenItemPosition)*1
          )

The second argument determines whether to shift the reference by one row.  When China is selected (position 3), the 1st and 2nd countries need to be shfited into positions 2 and 3, but all other countries in the list will stay put for positions > 3.

Now you have a neat user interface that generates a unique score card as easily as a drop-down menu selection.  Many people resort to writing macros for this type of tasks, but they can more often than not be done with a relatively simple set of formulas.

The following is the score card for the USA athlete, who won silver.

Congratulations to all!

Download the Sample File

Let’s recap.

  1. You want to learn about Excel.
  2. You want to buy computer parts to build a PC.
  3. You research the prices at Best Buy and Circuit City.
  4. You research the prices at more stores.

You have optimized the computer part prices and determined that the lowest total cost is $640.63. Before you pat yourself on the back for your hard work, you must realize that although cost is important, it means little unless you determine where to shop. The following Exhibit cleanly indicates the best price option and its respective store.

Download the Sample File

One option to create the exhibit is to use a manual “select cell, highlight, change font” (rinse, repeat, recycle many many times). This manual process may be fine for small projects, but the task can quickly become tedious if information changes or expands quickly.  Let’s create the exhibit using Excel’s highly powerful and usable Conditional Formatting (Excel 2003: Format > Conditional Formatting).

  • If an additional “cheaper” column is available, this task is easy. Choose the appropriate cells and set the “Cell Value Is” to refer to the Cheaper column.  (In the example below, column I refers to the before mentioned “cheaper” column.)  I have chosen the “equal to” option, but there are many other options that are easy to understand.

Download the Sample File

  • Now, lets make the assumption that you have no interest in creating an additional “cheaper” column.  No problem.  Instead of the default “Cell Value Is“, change the condition type to “Formula Is“.  This allows you to write an Excel formula which must solve to True or False (or equivalent).  In the example below, columns C through G refer to prices specified in Best Buy through Walmart.

Download the Sample File

Conditional Formatting is a very useful tool. When working for a financial firm, I used Conditional Formatting to error check, show real time changes in Equity Prices (Bberg BDP function), and highlight cells for concern.  What is really nice about Conditional Formatting is that you can utilize all the popular Excel formulas.  In many ways, you can create instant feedback for your spreadsheets because you can set up cells to format automatically based on your input/formulas.  Unfortunately, there are restrictions.   Excel 2003 only allows 3 conditions, the size of the file will increase, and large spreadsheets can become noticeably slower.  Microsoft expanded this tool in Excel 2007 and offers >3 Conditional Formatting formulas and more intuitive default conditions.  I have yet to explore all the Excel 2007 additions, but perhaps this could serve as a post in the future!?  Thanks to Peter for his original post.

Note*  If you need to build a computer, do your own research.  These prices most likely do not reflect actual store prices!

Download the Sample File

My intern came to me today and asked about displaying multiple lines within a cell.  I’ll write the first real post in his honor.

Most people know that when entering text in Excel, pressing ALT+ENTER makes a new line.  However, he needed to generate this multi-line string dynamically (via a formula).  The new line characters, carriage return (CR) and line feed (LF), are not as obvious in Excel as most programming languages make them.

Note: This refers to the ‘\r\n’ in C-styled languages and vbCrLf in Visual Basic. In many environments, however, only the latter (‘\n’ and vbLf) is necessary to create a new line.


Download the Sample File

One way to do this, which may be more intuitive to non-programmers, is to use the same ALT+ENTER key stroke by entering this formula:

= [first line] & “[press ALT+ENTER here]” & [second line]…

Another way to do this, which is longer but probably preferred by programmers, is to employ the new line characters (CR + LF):

= [first line] & CHAR(13) & CHAR(10) & [second line]…

Similar to aforementioned note, only CHAR(10) is necessary for the desired results.  Add CHAR(13) only if you have the pet peeve from certain programming styles.

This entry may sound obvious, but here’s the catch: by using the formula above, you probably will still see all your text concatenated on one line.  You still need to wrap text (Format -> Cells -> Alignment tab -> Wrap text) in order to see multiple lines!  Note: Excel automatically sets the wrap text property when you enter plain text (instead of a formula) with a line break in a cell.

Download the Sample File

Tags: , , , ,