Sales Dashboard Tutorial

[wsnyder]

Hi all,


Using Excel 2007

I've been using Excel for awhile

I didn't realize how poorly I was using it until I came across Chandoo.org


So in an effort to correct the error of my ways, I am working through some of the tutorials on the website.


For the Sales Dashboard Tutorial, I got the formula working except for the special characters. I tried inserting the correct characters from Insert..Symbol..Wingdings3, no luck. The Insert..symbol is ghosted out.


I also tried inserting the symbols in helper cells, copy the symbols, so I could paste inside the formulas. No luck, when I paste, the charcter I used for a placeholder while typing the formula simply disappears but the new symbol is not in its place.


Any thoughts as to what I am doing wrong?


Many thanks,

-winston

 

[Hui]

I Can't reproduce this

Can you simply select a cell with some text and change the font to Wingding 3

Are the Wingding 3 characters showing up in the Preview box of the Cell properties, Fonts Dialogue ?

Can you use Wingding 3 in Word ?

Otherwise you could try deleting it and then reinstall the font

a free copy of Wingding 3 is available at

http://www.webpagepublicity.com/free-fonts-w2.html

 

[wsnyder]

Thanks Hui


I typed some text into a random cell pre-formatted Calibri 11pt

I selected the cell and changed the font to Wingding 3 - all appeared correct

Here is what I typed "azsxdcfv".

Of course it appears as symbols in the cell formatted with Wingding 3, but in the formula bar the textt appears as "azsxdcfv"


I entered the 4 symbols needed in the given equaion in random cells preformatted with Wingding 3. All appear correct in the cells. When I click on each cell, nothing appears in the formula bar


I F2'd the cell with first symbol needed, highlighted the symbol and [Ctrl] + [C] to copy to the clipboard


I then opened the formula in the formula bar found my first charcater holder (^). I backspaced the char holder out and [Ctrl] + [V} to paste the first symbol. Nothing happens. Not even a space between the quote marks.


Many thanks,

Winston

 

[Hui]

Winston

If you want to put an upward pointing filled Triangle Wingding3 "p"

I would type the formula as normal in your standard font

=+IF(C6=1,"p","q")

And then change the font of the cell to Wingding 3

This gives me an upward filled triangle if C6=1 or a down pointing triangle if not equal 1


If you push F2 the cell text shows

=+IF(C6=1,"p","q") in wingding 3 characters, but the furmula bar shows as normal ascii characters


Hope that helps

 

[wsnyder]

Thanks HUI,


That works.

First thing I notice, you are using char numbers (112, 113, 116, 117), I was attempting to use (129, 130, 131, 132) - slightly smaller, but same appearance.


Secondly, your example uses a single font in a cell, the example dashboard is using a mix of arial for the number and pcnt symbol, and Winding3 for the description character


I typed the letter "p" into the formula but all character formatting options of the Ribbon are ghosted out - so I get p back.


I also tried typing p in a cell and formatted for Wingding3. I received an upper triangle. I copied this character from the cell and pasted it into the formula. I still received a "p" instead of ofan upper triangle.


Many thanks,

Winston

 

[TessaES]

I think Chandoo used the triangle symbols from the Arial font. You can find then using Insert/Symbol. They are in the collection 'Geometric shapes' which you will find if you scroll down in the symbol dialog.

I don't think you can use two different fonts in the formula.

If you want to use the shapes from Wingdings3, you should put them in a separate column.

 

[keymaster]

Yes, I have used the arrow symbols in the Arial font. They are the easiest way to combine symbols with data. And as TessaES pointed out, you cannot combine fonts in formula results. You have to use 2 cells if you want 2 different fonts.

 

[wsnyder]

Thanks all,


That respolved my issue.

I did modify the formula slightly to remove the pcnt symbol from the concatenation as well as display only 1 decimal place instead of 2. IMO I think both of these factors give us a cleaner display to understand. I also changed the font to Calibri 10 pt - looks a little better than Arial.


Here's my current formula:

=IF(E5>0, CONCATENATE(FIXED(E5*100,1,TRUE),REPT(" ",10-LEN(FIXED(E5*100,1,TRUE))),"▲"),IF(E5<0,CONCATENATE(FIXED(E5*100,1,TRUE),REPT(" ",10-LEN(FIXED(E5*100,1,TRUE))),"▼"),CONCATENATE(FIXED(E5*100,1,TRUE),REPT(" ",9-LEN(FIXED(E5*100,1,TRUE))),"◄►")))


One last thought, is there a way align all of the decimal points while retaining thy symbols, or would I need to put the symbols in an adjacent cell?


Many thanks,

-winston