Making an Icon Set show only two conditions

I really like the new icon sets that are in Excel 2007. They’re kind of a neat way to format a cell to show interpretive information at a glance.

One practical place to use these is as an alternative to the using custom number formats that I blogged about last year. I decided to use Excel’s icon sets to show a green check when something was positive and a red x when negative, as shown below:

One thing that can be frustrating when trying to set this up is that Excel forces you to use the three part icon set. By default, the image above would have a yellow exclamation beside the zero. Personally, I don’t need it. But with Excel forcing it on you, how do you avoid it? Here’s how I did it:

  • Select the cells:

  • From the Home tab, choose Conditional FormattingàIcon Setsà More Rules

  • Using the default of “Format All Cells Based On Their Values”, set up the rule as follows:

  • You’ll need to change:
    • The “Type” fields to “Number”
    • The Icon Style
  • Apply the rule

Now you have the cells looking as shown below, but how do you hide the yellow exclamation on the zero?

The answer is to set up a conditional format using another of Excel 2007’s conditional format tools; Stop if True

  • Select the cells again
  • This time choose “New Rule” from the Conditional Formatting menu (not “More Rules” from the icon set area)
  • Choose to “Format only cells that contain” and set up the rule as shown below:

Applying the rule now won’t have any effect, as it is essentially a blank rule. (See the message about “No Format Set”?)

  • Click Format
  • On the “Fill” tab, click “No Color” for the background and click OK twice

We now have our second rule set up, but nothing appears different. Let’s fix that:

  • With the cells still selected, go back and choose “Manage Rules” from the Conditional Formatting menu
  • Check the “Stop if True” box beside your latest rule as shown:

Click Apply and it should the yellow exclamation disappears from the scenario.

The “Stop if True” feature is a great addition to Excel’s conditional formatting as it allows us to decide just how many of our formats we wish to overlay in 2007. Depending on how many you apply to one cell though, you may have issues with trying to figure out inheritance issues, so use them carefully.

Random thoughts on phones

Some things kind of make me shake my head. When we were at the MVP summit, we think we left Dee’s cell phone charger at the hotel. So we headed down to our local dealer to see how much it was going to cost to replace it. $45. Ouch. Or… we could get a new phone for $40, which came with a charger. That seemed like a no-brainer, as Dee didn’t like sending text messages on her old phone anyway since it didn’t have a full keyboard. So now she’s sporting a nice new Samsung cell phone.

As nice as it is to have a shiny new phone, it does kind of make you shake your head at how disposable technology is though. And why does every different phone, even within the same supplier’s lines, have a different charger? Why can’t they all standardize on USB or something? That’s pretty irritating, IMO, and leads to a ton of unnecessary e-waste.

On a note that doesn’t seem related, but will be by the end of the post… (Relatively) recently, Canada put in place a “Do Not Call” list. We signed up right away, and it worked for a bit. Then slowly we started getting calls again. As it turns out, the CRTC doesn’t do any vetting of who buys the lists as every agency has an obligation to know who NOT to call. Sadly though, this means that bad companies can buy lists that only contain valid phone numbers. Arrgh! So go figure that my cell phone, whose number is only 11 months old, is now getting 2-3 “FO” calls per week.

What really gets my goat though, is what my phone company (Telus) did when I tried to deal with one. The calls I get on my cell are from an “agency” who asks me to dial 9 to speak to a representative. This is a well-known scam that allows them to make long distance calls on your number. The best part, though, is that the number shows up on call display on my phone. So I phoned Telus to let them know that I had the number for a scam artist. After all, they only control the entire phone system in BC and Alberta. They didn’t want to hear about it. I basically got the impression that they don’t give a crap what scam their customers may fall into, so long as they get their long distance funds. That is pretty piss poor in my opinion, as they should be able to turn them over to the RCMP or something, and make the phone lines safer for everyone. It’s a typical Telus attitude though, and the reason that the only Telus service in my house is the cell the company pays for. (I switched to Shaw’s home phone service the day it was finally available in my area.)

Now, back to Dee’s phone for a second… One really cool thing that I stumbled on is that she can actually put in a list of blocked numbers. How cool is that? We haven’t explored it in detail, but that got me thinking…

I’d LOVE to see a feature in my Windows Mobile device that would allow me to blacklist a number. And I’d like this blacklist to be serious enough not to just ignore a call, but rather a list of calls to discard completely. As it is, not only do I get the irritating ring, but the jerks leave me a message. In order to clear the voicemail icon, so that I know that I actually don’t have any unheard messages, I have to burn up cell time… and that costs me money. It would also have huge benefits to those who end up with social issues in their lives like harassment. Just blacklist the number and it’s done. Granted, you can phone the cell carrier and block a number for a fee per month, but let’s make this self serve and easy to implement. I’d rather burn my cell time making calls, rather than dealing with scam artists.

Granted, I’m not sure how practical it is for Microsoft to deal with, or if they’d have to reinvent the phone company. They are the largest software company in the world, though, so you’d hope that they could influence the future tech direction of a phone carrier.

Actual Charts vs Images

In a blog post last week, I was looking at the image formats to use when I snapped a picture of a chart. I’m kind of glad that Jon agreed with my assessment on EMF being the best image format for this, as it at least means I might be on the right track.

Jon also asked a question though… “Why not just use charts”.

Good question, and sorry I didn’t respond earlier, Jon. I got wrapped up in the course I was teaching. ;)

Main Purpose:

Just to get it out there, the main purpose of me building these charts was to show them as micro charts on a dashboard. Some inspirational dashboards I was modeling on can be found at Charely Kyd’s site. Here’s just one of them.

Now, it’s ENTIRELY possible that I’m going about this the wrong way, and am about to get schooled. I’m totally okay with that if it’s the case. I haven’t done any dash-boarding in Excel before, so this is a big experimentation for me. With that in mind, please feel free to offer suggestions or critique. I’m really trying to find the best mix of results and maintainability here.

I think the best approach to explain why not just use charts is to look at what I did along the way… So here’s the history of why I went where I did.

Original Chart

I created my original chart and got it looking exactly as I wanted. I got the colours the way I wanted, set up my scales to work for me, and I basically got it to where I like the overall display. What I ended up with is the chart below (shown at actual size):

Scaling Attempt 1:

From there, I tried to scale the original chart down to micro chart size, and ended up with this nasty looking thing:

I had a quick look through all the options on the chart, but couldn’t find anything that dealt with the scale preservation when shrinking. At least, I did find the “Size” on the Chart ToolsàFormat contextual tab on the ribbon, but it didn’t seem to help. I could have tried to build it smaller, I guess, but then it would be hard to read on screen to see if it looked okay. In addition, I don’t know if I could get everything to the right size. (Font sizes of 1.5, 1.2 or lower maybe?)

Basically, since the scale went all nasty, I gave up and reached toward the camera tool.

Scaling Attempt 2:

I reached towards the camera tool to create snapshots of the charts. Actually, this had another bonus in that I need to create different dashboards from the same data, so I will end up using the same chart more than once. I figured that the camera tool would be a great solution for that, since I would only have to maintain the source chart once.

But the camera tool is flaky. About 6 different charges into the project, I ended up with all the charts suddenly turning grey, and I couldn’t get them to come back. Naturally, if you can’t rely on it, you really can’t use it, so I ditched it and moved on (after wasting a bunch of time trying to make it work.)

Scaling Attempt 3:

This is where I opted to go with copy the chart and doing a PasteSpecialàPicture. Using the EMF version I got a nicely scaled micro chart:

This added another benefit as well, actually, in that the dashboard page could be built on a separate sheet from the original data. Maybe it was the way that I set it up, but this seemed difficult to do with the camera tool. (In all fairness, I did NOT try to create a chart with source data on another sheet, but I can’t see that being an issue.)

Potential/Known Issues:

I’m not going to say that this is all roses, either though. There are issues here, obviously, maintenance probably being the biggest of them. The image is static, so I had to knock up some VBA code to remove all the existing pictures on the sheet and rebuild them. That actually wasn’t a big deal in the grand scheme of things. And at least the charts look consistent with the originals and print well.

In the process, I discovered that you can name charts in Excel 2007 through the UI. (Whooppee, you’d expect that…) But I also discovered that you can name multiple charts the exact same thing. That sucks. Once I’d set up one series of charts, I copied them all to use with my next set of stuff. (You see Food above, but I also have about 9 other product lines to deal with.) I figured I’d just change the source data, but I also had to be very careful to make sure I got the chart names, or I’d have some weird things happening when I referred to those names in code. (I’m floored that we’d be able to name multiple charts on the same sheet with the same name, but there you go.)

I’m also not happy with the difficultly this presents in creating the dashboard for the first time. How do you get the code to recognize the insertion point of the picture on the sheet? The dashboard has numeric data as well, with some (basic) conditional formatting, and the column widths are not consistent. So how do you line up the images with the grid? The short answer is that you can’t. So then we get into issues of how to get the image to the correct place.

Thoughts that are currently still cooking…

I’m still trying to work my way through this, but I’m starting to wonder if I won’t have to force consistent column widths. If I did, I can see a couple of potential solutions to my issues:

  • I could then set up a table of chart names and cell references. A little code could then retrieve the chart and place it at the coordinate of the cell specified.
  • Rob van Gelder made spark lines by creating a shape via a UDF. This might work if I could feed in the chart name as a parameter… but I think it’s a non-starter. While you seem to be able to create simple line shapes, I’m running into issues trying to create get the full chart into a (new) shape.

I think I’d actually prefer the latter, but this still kind of sucks as it would probably need consistent column widths to avoid overlapping charts. While it would work well there, I’m not sure I want the rest of my report bound to having extra whitespace.

At any rate, that’s what led me into pictures from real charts.

Suggestions, thoughts and comments all welcome.

Best picture format for scaled charts?

*Note:* The charts in this post are based on sample data and do not reflect any real financial performance.

In setting up my first dashboard, I took a page out of Charley Kyd’s book and placed a bunch of little graphs on my report. I tried to use the Camera tool first, but after I had about 7 on the page, the graphs all went to grey backgrounds, and you couldn’t see anything anymore. I could not, for the life of me, get them to revert to live images. Very irritating. I suspect that it has something to do with Excel 2007, but I’ve never used the camera tool in earnest before, so can’t be sure.

Just to get this out of the way first, I set up a bunch of charts on a worksheet. I ultimately wanted to show a smaller version of these charts in my dashboard. Unfortunately charts don’t scale well when you try to shrink them down, or at least, mine are poorly set up to do so. This is why I wanted to use the camera tool, as it deals with this well.

Since the camera tool was giving me issues, I elected to go with copying my charts and pasting them as images. Adding a little VBA to it, I can wipe all the existing charts on the page, and re-create current chart images. This gives me semi-live chart updates vs the live updates that would have been available if I could have made the camera tool to work for me.

During the process, I found that some of the Paste As Image choices scaled better than others. Here’s what I started and ended with:

Height Width
Original 181.9357 252.5019
Resized 80 100

The formats that you can paste into are Png, Jpeg, Gif, Enhanced Metafile, and Bitmap. I tried all of them.

The ironic thing is that print preview makes the Enhanced Metafile look absolutely awful, as you can see here:

In reality though, once printed out I found that the Enhanced Metafile gave the truest representation to the original of all of the formats. That surprised me a little, as I was kind of expecting png to be the best. My thoughts on this, for reference:

Png: Looked like certain lines were “clipped”. The bottom of the number 1 characters, for example, seemed to miss part of the bottom line.

Jpeg: Everything looked like it’s been bolded, and there seems to be a smattering of extra pixels on the graph. It looks just… “dirty” I guess.

Gif: Numbers are blurred and clipped. Far from professional looking

Bitmap: The second best of the lot, but still looks dirty

The Enhanced Metafile, on the other hand, was crisp, clean and sharp. Despite being scaled out of proportion, it still looks very good.

Just to prove that they do look better, here’s a PDF of the printout: imgsample.pdf

I’m curious to other thoughts on this, as this is the first major effort I’ve concentrated in this area. If you’re using pictures, does what I’m looking at hold true for you as well?

Weird chart sizing observation

I’m not sure if the observation is weird, or the actual chart sizing is. I think this is a bug, but I’m curious if others see similar behavior. This is an Office 2007 thing (at least, I haven’t tested it in other versions…) Here’s the repro steps:

  • Make a chart and select it
  • In the VBE, find out the width. I just did the following in the immediate window:

    ? selection.width

  • It came back with 400. All good
  • Set the width to something (again in the immediate window):

    Selection.width = 245

  • Now, check the width again:

    ? selection.width

  • It comes back with 250

This strikes me as a little strange. I would have thought that the width would be the same as what I set it to. No matter the number given, it always seems to come back with a width that is 5 pixels larger than what I set it to.

The height and left properties do not seem to suffer this issue, only the width that I’ve encountered so far…

Anyone else get similar behavior?

New Computer Game

I’ve got to admit that I’m proud of my little brother here. He recently graduated with a Computer Game Programming & Design diploma, and got a job working on a game with a startup company. It looks like his first game just went live today.

Personally, I haven’t played it as I’m waiting for Bri to send me an iPhone, but I figured I could give it a plug anyway. Here’s the official write-up:

Gravity Well is an innovative, addictive mix of pinball and mini golf. The unique use of gravity pulls your orb around 36 deadly environments. Survive the perils of lasers, zappers, crushers and spikes! See if you can beat them all!

GROUNDBREAKING CONTROLS: Crafted for the Apple iPhone and iPod Touch, you manipulate gravity to move the ball through challenging, interactive environments.

AMAZING SOUNDTRACK: 17 tracks from the award winning Humble Brothers created exclusively for Gravity Well. The soundtrack is available for purchase on iTunes.

“TRUE BUBBLE WRAP” GAMEPLAY: Takes only a few moments to learn, yet offers hours of gameplay. Addictive & satisfying!

A portion of proceeds go directly to support these worthy organizations: BCSPCA, Heart & Stroke Foundation of BC & Yukon, and the Canadian Cancer Society.

It’s all of $3.99, so if you want to give it a go and help support a startup game company (or see screen shots), click here.

Resize Images in Word

I’m working on writing up a course, and it always drives me nuts when the pictures I put in the document are different shapes. Height is fine, but I always want the width to be the same as the document looks weird with margins that change. Resizing manually is not practical as it takes time and eyeballing is not really reliable.

So I knocked up a quick piece of code to resize all the images in my word document. I don’t claim to be a Word VBA expert, so this can probably be improved, but here it is:

Sub ResizeImages()

‘Date Created : 4/1/2009 21:36

‘Author : Ken Puls (www.excelguru.ca)

‘Macro Purpose: Resize an image in Word

 

Dim shp As InlineShape

For Each shp In ActiveDocument.InlineShapes

shp.Width = 300

Next shp

 

End Sub

 

The size is measured in points, and 300 equates to about 4.75″ on the page (at least here, anyway.)

Very tired of snow…

It’s snowing here… again. We’ve had more snow this year than I can ever remember in Nanaimo, and it’s getting really old. I don’t anticipate that this snow will stick, but it’s still irritating. So far it has snowed at the beginning of Jan, Feb, Mar and now April, with our first big snowfall of the winter being in mid December. Honestly, there is still a little bit of snow by the stop sign on my street from the FIRST snowfall. Ridiculous.

The bigger part of irritation for me is that it is coming during every month end reporting period, causing us grief on getting to work. Yesterday, as we’re busy preparing for our quarter end (which has an EXTREMELY tight deadline), high winds knocked the power out… and now it’s snowing.

Grr….

Oh well, we’ll get through it. We always do, but I think it’s time for spring to come along now!