Making Excel Charts Look Cool

Ray of Light, originally uploaded by xrrr (?).

After being annoyed with Excel XP making rubbish charts for a report I did a bit of Googling for a better option. Juice Analytics has an excellent site that gives you the low down on making those default charts look better. I tried it out and it gets great results. However...

As well as a PC I also own a Macbook. On the Macbook I tried the free trial of Apple's iWork08 software, a cheaper version of Microsoft Office that is good enough for 90% of your Word, Excel and Powerpoint needs. Amongst other things iWork08 has great looking charts. Though I could have imported my Excel workbook into iWork08 I decided it would be much better to get Excel to replicate the Apple generated graphs. Based on the info from Juice Analytics and a bit of RTFM it turned out to be quite easy. These tips apply to Office XP version of Excel but I am sure the Vista charts will need some help too.

This how to will show you how to turn the default Excel charts:



Into something a whole lot more Apple Cool:



Step 1: Change the Colours
The most basic change is to use some more modern colours than the default Excel ones. I chose to use the default colours that Apple uses as they are bright and have good contrast. I used to think that Excel had a limited colour palette and those were the only colours you can use. Turns out I was wrong.

Go to "Tools", "Options" on the menu and chose the "Color" tab. This opens the colour palette for the worksheet. Charts are drawn using the colours in the "Chart Fills" and "Chart Lines" starting from the left. You can modify these colours to a new set by clicking the "Modify..." button and then the "Custom" tab. I used the Apple colours that have the following RGB values:

1: 59,107,157
2: 110,163,90
3: 237,175,76
4: 202,65,61
5: 131,81,139
6: 142, 145, 144

If you need more than 6 colours then repeat from the top but lighten each up a bit by moving the slider on the colour chooser a little bit upwards. I also set one of the colours in the "Standard Colors" block to RGB 184,184,184 to use for grid lines.

Step 2: Remove the Junk
For some bizarre reason, the default chart settings include a whole bunch of stuff that most of us don't want. So lets turn it all off.

Chart Area
Right click in any white space within the chart but outside of where the chart is drawn. Choose "Format Chart Area".

Patterns Tab
Set "Border" to "None"
If you are pasting into Powerpoint and want a transparent background set "Area" to "None"

Plot Area
This step does not apply to all chart types (e.g. Pie). Right click in the grey area behind the chart. Choose "Format Plot Area".

Patterns Tab
Set "Border" to "None"
Set "Area" to "None"

Grid Lines
This step does not apply to all chart types (e.g. Pie). Right click on the grid lines. Choose "Format Gridlines".

Patterns Tab
Set "Color" to the grey you set above to 184,184,184
Set "Weight" to the third weight down

Axis
This step does not apply to all chart types (e.g. Pie). Right click on one of the axes. Choose "Format Axis".

Patterns Tab
Set "Lines" to "None" - This should only be done on one of the 2 axes. Leave the line alone for the axis that bars are columns originate from.
Set "Major tick mark type" to "None"

Data Series
For each series in the chart (bar, column, pie segment, etc) right click. Choose "Format Data Series".

Patterns Tab
Set "Border" to "None"

Options Tab
For bar and column charts change the "Gap Width" to "50" to make the bars thicker.

Legend
Right click on the legend. Choose "Format Legend".

Patterns Tab
Set "Border" to "None"

Placement Tab
Set "Placement" to "Top"


That's it! Sit back and admire your much cooler, easier to read, more professional looking chart!

Step 3: Save it
Now you have gone to all that effort you can save your new format to apply to future graphs.

Right click on your new chart and select "Chart Type".
Select the "Custom Types" tab.
Near the bottom click "User-Defined".
Click the "Add" button that has just appeared.
Give it a name and description.

The next time you create a chart click to "Custom Types" and "User-defined" and you will see your cool format chart in the list. Unfortunately it does not store the "Chart Area" settings but those are easy to fix.

Colours are stored with the worksheet so creating a new Excel file will reset the colours. You can import colours from other open Excel files by selecting the file with your new colour theme in the "Copy colors from" drop down.

Have fun!

Out with the old...

2001 Ford Ka 1.3, originally uploaded by xrrr(?).

After owning it for almost five years, today we traded in Alison's Ford Ka for a VW Polo. The Ka was already 2 years old when we bought it in March 2003 and it is almost exactly 7 years old this week.

The Ka was totally past it. It had been used as a skip work horse during the renovation period on our house and bore the brunt of the heavy stuff. My excuse for not using my car is that the boot aperture is too small as it is not a hatchback :-)

Day 50 - Will it all go in?, originally uploaded by xrrr(?).

Day 353: ...Going..., originally uploaded by xrrr(?).

The last MOT cost £400 which was 10% of the value of the car when we bought it and more than half the trade-in price! So with the following list of worries and an impending MOT due in two weeks we decided it was time to trade it in:

• Squeaky clutch - used to be just on hill starts but recently been on most gear changes
• Shagged suspension - a million and one clanks and knocks as the car drives around
• Odd engine noise when turning left - no idea what this is
• Bobbling of paint and rust around the base of the B-pillar
  
• 66,000 miles on the clock
• Lost Ford badge on the back - not a worry but it is surprising how much it changes the look of the car

I had always found the Ka uncomfortable as a passenger as the window winder always bashed into my knee and the cabin was too small. To its credit though it never broke down. It has needed a new window mechanism (due to the drivers window getting stuck open), some suspension work and both headlight bulbs replacing (both went within a month of each other) but it has never broken down.

When we decided to buy a new car Alison wanted another Ford Ka. It took me a while to persuade her to look at other cars within the guidelines:

• Nothing Japanese (Alison doesn't like the look)
  
• Nothing French (I didn't trust the reliability)
  
• Must have air con
• Must look nice
• Must have at least a 1.3 engine
• Nice to have: iPod connectivity
  

Well the Polo ticked all the boxes even though it was a little more expensive than I had planned. The new shape Polo actually looks really nice too.

2008 Volkswagen Polo 1.4 S, originally uploaded by xrrr(?).

We eventually opted for brand new a 3 door 1.4 Polo S which, including the options (iPod adapter, metallic paint, seat upgrade [the car was ordered from stock and had that already on]) and the finance and servicing deals, was only a few pounds more a month (literally) than buying a decent second hand Polo.

2008 Volkswagen Polo 1.4 S, originally uploaded by xrrr(?).

Buying the car was 'fun' to say the least. The sales guy very quickly passed over negotiations to the dealership manager after I took with me a Glass Guide evaluation (£3.50 from the website) and a print out of the new car price offer from Broadspeed.com. This allowed me to enter negotiations with the lowest price offer in the UK for the new car (or as near as damn it) and for an idea of the best price I could get for the trade in. Therefore with a 'cost to change' already in my mind before we went in I was ready for battle :-)

The conversation went something like this:

Me: I want to pay £8800 cost to change
Dealer: The best I can offer for the trade-in is £700 and matching the Broadspeed price that makes the monthly payment a mere £[I forget the amount]
Me (grabbing the calculator): That means a cost to change of £9050. That is more than I had in mind.
Dealer: That is the lowest we can go. It is a good offer.
Me: Your valuation of the Ka is £350 less than the "average condition" trade on Glass.
Dealer: Yes, that is because we think that is a fair offer.
Me: Ok, £9050 is more than I wanted to spend.
Dealer: We can't go any lower
Me: Offer me £250 more for the trade-in and we have a deal
Dealer: No deal
(lots more benefit statements from the dealer)
Me: Ok. Lets cut this short. Lets go all Dragon's Den and split the difference. I will go up £125 if you meet me in the middle.
Dealer: No deal
Me: Well I am not paying more that £8925
Dealer: Perhaps we can talk more if you look at these extras: Autoglym Life Shine (£350), Gap Insurance (350) and a service and maintenance plan (£17 pcm for 3 years).
Me: Lets not get distracted by those, agree to the car and we can talk about the extras.
Dealer: Gap insurance.....
Me: Agree to £8925 and I will shake your hand and pay the deposit now.
Dealer: That is too low
(Lots of pushing of the "extras")
Me: Look we are arguing over £125 here!
Dealer (thinks about it): Ok, you have a deal. Now about the Gap Insurance.
Me: Ok good, you have a deal. Now lets look at these extras.
(Dealer draws a graph on paper of insurance costs verses car costs)
Dealer: If your car is an insurance write off in the first 3 years this insurance will cover the difference to allow you get a new car again. £350 is a good deal.
Me: No thanks.
Dealer: If your car is an insurance write off...
Me: Look I spent nearly four times as much on a car for me last year [huge exaggeration] and I did not take the gap insurance for that so I ain't going to take it now.
Dealer: Ok, Autoglym Life Shine. Modern cars have a water based paint that needs protection. I am sure when you bought your last sofa you had it Scotch guarded, this is the same thing for the interior and exterior of your car.
Me: No thanks. [not quite believing that car manufacturers would actually put lower quality paint on new cars than they used to - even if they do I am sure they would not let the dealers tell anyone!]
Dealer: Are you telling me that this is not a good thing?
Me: No, I agree it is a good thing but it is not worth £350. Again, I was offered something similar when I bought my car and I looked it up on the internet and it got very mixed reviews on whether it is worth it.
Dealer: I can do it for £300
Me: To be honest I would still be sceptical if it was £50 so no thanks.
Dealer: Ok, so the gap insurance I can do that for you at cost price, £200.
Me: Look. I don't want all this. I agree the Autoglym looks good but no way am I paying more than £50 for it. The gap insurance is a risk mitigation. I am a gambling man [I am not but I was getting carried away] and I am happy to take the risk. So thanks but no thanks.
Dealer: Ok fine.

Phew, that all took about 2 hours. After all that we got a 9% discount on the list price of the car which ain't bad. I probably could have spent another 2 hours and got a bit more off but I really could not be arsed!

Anyway, they texted me a few times during the week to make sure I did not want the extras but I did not crumble! I did accept the VW offer of 3 services for £200 upfront as three years of servicing the Polo is about £400 according to the dealer's service price chart. Looks like quite a sound deal :-)

So we picked the car up today with no problems other than me being held hostage for 30 minutes...

It turned out that there was a slight misunderstanding in our household regarding a car's "Log book". Before we left to go and swap the cars over I asked if Alison had the "service book, log book and driving licence". We got to the dealer and he asked for the V5 Log Book for the Ka. Turned out the V5 Log Book had been left at home!

I guess it is an easy mistake to make. The service book is a book and it has the words "Service Log Book" on the front. Whereas the V5 Log Book is a piece of paper from the DVLA and looks nothing like a book.

They asked me to stay behind whilst Alison popped home to get the V5 otherwise I would have had to pay a £500 retainer (a.k.a ransom!) until we produced the V5 for the Ka.

So, all sorted eventually. Probably about an entire day of my life taken up with the various processes but Alison is happy and that is the important thing :-)

2008 Volkswagen Polo 1.4 S, originally uploaded by xrrr(?).

Merry Christmas!

Let it snow!, originally uploaded by xrrr.

Merry Christmas everybody. I hope Father Christmas brings you everything on your list :-)

I am sure a few households will be waking to the following scene with a Nintendo Wii replacing the N64!

Flickr Stats

Flickr Stats, originally uploaded by xrrr.

At last! Flickr has finally enabled photo stats!

A long awaited feature that provides statistics on what photos are popular and where they were referred from. It just stops short of the much requested 'WHO has been looking at my photos' but is still very cool.

Looking at my stats I can see that on 15 December 2007 I had 332 views and 2 comments. my top viewed picture was 'Johnny Depp as Captain Jack Sparrow' at Madame Tussauds with a grand total 51 views yesterday / 232 views this week / 1,484 views all time.

Johnny Depp as Cap'n Jack Sparrow, originally uploaded by xrrr.

Another interesting feature is the ability to see the details of the referrer or in other words the place that linked to my Flickr page. The vast majority of views came from Flickr itself either from postings in discussion groups, photos in pools or searches. The second highest referrer is Yahoo Image Search. Therefore I can see what people were searching for when they ended up looking at my photo. The current all time top 5 views from searches is:

1. Jack Sparrow
2. Noob
3. Mercedes McLaren
4. 2008 BMW M3
5. Mercedes Benz

Bizarrely 50% of the Jack Sparrow searches have appeared in the last week. Not sure if my picture managed to get to the front page or whether people have just been searching for Johnny Depp more?!

When I get time, I am sure to be doing a lot more "stat mining" as it is fascinating to see how people are using my photos :-)

Canon Speedlite 430EX Comparison Test

Something new to play with, originally uploaded by xrrr.

Canon Speedlite 430EX Comparison Test

First of all let me mention that I am no test or photography expert and I wish I had the knowledge, background and TIME to write a review similar to a dpreview.com level of detail. But I don't!

I bought the flash unit and was curious what the benefits were. So to test the settings I took some sample pictures and have included the pictures and my notes here for the benefit of all.

If you do not agree with my amateur observations then that is fine, you can make your own mind up as all of the full size pictures are on Flickr. Just click on the photos and click the "All sizes" button to get to the large versions. If you an amateur like me and are thinking of buying an external flash unit then hopefully this will help.

Also, before you do anything with your Canon flash you absolutely need to read NK Guy's Flash Photography with Canon EOS Cameras first!

In this post I have two tests:

1. Comparison of the camera's built in flash with the Speedlite
2. Comparison of a few basic flash options to improve a scene

Both tests use the following equipment:

• Canon 400D/Digital Rebel XTi
  
• Canon EF35mm f/2
  
• Canon Speedlite 430EX
• Adobe Photoshop Lightroom
• Douglas Photographic Photographer's Grey Card
  

Test 1 - Comparison of the camera's built in flash with the Speedlite
This test was performed indoors at night under a standard 100w light bulb. I chose the items in the scene to give reflections, darks, lights and a bit of colour. There is also a grey card used to help with the white balance corrections.

Shot #1.1

Flash Test 1.1 - No flash, originally uploaded by xrrr.

This was my control shot with no flash. True to the various reviews of the 400D the auto white balance selection is way off. Manual correction is required but the end result is a fairly well lit scene. Perhaps a bit dark and the shadows are a bit harsh but I would be reasonably happy with that shot. We are here to test the flash though, so lets press on.

Shot #1.2

Flash Test 1.2 - Stock 400D Flash, originally uploaded by xrrr.

This is the built in pop up flash on the 400D. As with most flash photography at short range, it is very harsh. The shadows from the overhead light are softened out but the glare and reflection from the shiny objects is too much. The white of the pen and paper also loses a bit of detail. Interestingly the only blown highlights are in the reflections of the flash (which you would expect). The flash has been reflected off the shiny container onto the Mont Blanc pen case leaving a nasty light mark. At least the white balance was more accurate. Note that the camera automatically adjusted the shutter speed to adapt for the flash being switched on.

Shot #1.3

Flash Test 1.3 - Speedlite 430EX Direct Flash, originally uploaded by xrrr.

This was taken with the Speedlite 430EX pointing directly forward with all of the default settings. I was initially quite surprised by this shot as it is virtually no different to the built in flash. The shadows are slightly differently placed due to the height of the Speedlite but the glare is still there. So much for the "perfect exposure every time" claim from Canon!

To be honest I should not have been surprised as a flash is a flash and directly head on is the worst kind of flash. I think I had assumed that the 'evaluative through the lens' (E-TTL) light metering would recognise not to blast out such a strong light pulse as it is not required.

I note that my EF 35mm f/2 lens does not support E-TTL distance measurement which means the flash will use a default setting. When I get a chance I will run a test with my EF-S 17-85mm f/4-5.6 IS USM that *does* support distance measurement. If you have no idea what I am on about then you have not read NK Guy's flash guide yet!

However, the point of an external flash is that you gain control over what it does...

Shot #1.4

Flash Test 1.4 - Speedlite 430EX Bounced Flash, originally uploaded by xrrr.

This shot used the flash as it is supposed (?) to be used as I directed the flash at my white ceiling and let the light bounce onto the scene. This has created a much more even light. The hard shadows of shot #1 have been softened slightly and there is no harsh glare from from the reflective objects. The flash is a lot less noticeable, in fact the glare from the light bulb on the neck of the bottle is what catches my eye.

Saying all that, for this scene I still prefer the side light from shot #1.1 as it provides more depth than shot #1.4. More practice required I think :-)

Test 2 - Comparison of a few basic flash options to improve a scene
This test was run in the same location as Test 1 but it was an overcast daylight coming through the window on the right of the scene casting long, dark shadows. There was also a window on the far side of the room on the left of the scene. The sun was coming from the right but you can still pick up the left hand window reflections in the Seven Seas container.

Shot #2.1

Flash Test 2.1 - No flash, originally uploaded by xrrr.

As with test 1 the camera's auto white balance is terrible, but shooting in RAW allows me to fix that in the Lightroom software. The light cast on the scene makes it look a bit dull. Handy I have a flash then!

Shot #2.2

Flash Test 2.2 - Black Focus, originally uploaded by xrrr.

I have used the Speedlight with the flash pointing directly at the scene. In this test I wanted to test the difference between what focus point the camera registers because the Speedlite will use the active focus point at the time the photo is taken. This means if you use the camera like I do and keep the centre point as the main focus point, focus and then recompose before taking the shot then the flash will get confused.

In this shot, the focus point is central and focussed on the black Mont Blanc pen case. The result is that the background objects are better lit at the expense of the white paper in the foreground losing some definition. The shiny object has picked up the flash glare but because of the changed angle (looking down more) it is not as noticeable.

I am not sure why, but the camera did not change the shutter speed to adjust for the flash. I think the evaluative metering must have got hung up on the black. Lets see what happens if we change the focus point...

Shot #2.3

Flash Test 2.3 - White Focus, originally uploaded by xrrr.

This shot used the lowest focus point that was covering the white paper in the foreground. Notice how the contrast of the paper is far better and the light looks more natural. A word of caution before you think the flash made a big difference: The camera was in aperture priority mode so me changing the focus point also changes the shutter speed as I was in evaluative metering mode. So a fairly inconclusive test that I need to redo when I get chance.

There is still a lot of glare so lets try and improve it a bit more...

Shot #2.4

Flash Test 2.4 - White Focus (Bounced), originally uploaded by xrrr.

In this shot I aimed the flash away from the scene and bounced it off the back of my grey card (which is white). This has removed almost all of the glare from the scene and kept the lighting reasonably even. The shadows are softer and everything looks more evenly lit. Also in this final picture I only had to tweak the white balance the tiniest amount. I am not sure what happened to make the auto white balance work better but it is all good.

Conclusions
The external flash was not the immediate 'fix it all' solution I was hoping for but my first 50 shots on my 400D (my first SLR) were absolute rubbish as I had not learned how to use it. All of these quick tests were run with the Speedlite on the default "auto" settings so I was not making full use of the flash.

The flash unit gives a massive amount of tweaking potential that, once mastered, should prove to be a useful addition to my (ever increasing) camera kit. I never use the pop up flash just as I never use the camera's "auto" mode.

I am now considering a cheap softbox attachment for the Speedlite to see if I can get that utopian soft light for indoor portraits without having to invest in a remote flash or studio lights!

Am I glad I invested? Absolutely! I like the being in control of the flash in order to obtain better photographs. I now just need to learn how to use the thing!

I hope this was useful :-)