convert numbers in Pivot table to Symbols

[maxlog]

I am working on a compliance scorecard using Excel to measure if student
homework are submitted on time. I get results in pivot table from a SQL
db, the basic results in excel are:

for On-time, I get number '1'
for Missing, I get number '0'
for Late, I get number '3'

How can I convert these numbers to a reader friendly symbol, say
convert 1 to a green 'check mark', 0 to a red 'X' and convert 3 to a
'black flag' ?

Thanks !!!

 

[Bernard Liengme]

How about Conditional Formatting?

 

[Dave Peterson]

How about something more pedestrian?

Select your range to fix
edit|replace
what: 1
with: On-Time
replace all

and again for the others.

Then you could even do stuff with that data--other formulas, filtering...

That would be much more difficult with shapes and colors.

 

[Dave Peterson]

And I'd do this in the raw data. Then use that field as a column/row area with
"count of" in the data area.

 

[maxlog]

Since the data set in the are refreshed almost everyday with new
assignments added into the workbook, hardcoding type of manually
convert will not work due to the frequency of the updates.

I like the conditional formatting suggested by Bernard, but it only
changes the color of cells. Any idea how I can convert to symbols
instead of numbers that are meaningless to readers?

Thanks!

 

[maxlog]

Since the data set in the are refreshed almost everyday with new
assignments added into the workbook, hardcoding type of manually
convert will not work due to the frequency of the updates.

I like the conditional formatting suggested by Bernard, but it only
changes the color of cells. Any idea how I can convert to symbols
instead of numbers that are meaningless to readers?

Thanks!

 

[Debra Dalgleish]

You could use a custom number format:

In the pivot table, right-click the data field heading
Choose Field Settings
Click the Number button
Select the Custom category
In the text box, type:

[Red][=0]"û";[Green][=1]"ü";"n"

NOTE: To create the û symbol, hold the Alt key, and on
the number keypad, type 0251
For the ü symbol, use 0252

Click OK, click OK
Select the cells with the symbols, and format them with the Wingding font.

 

[Dave Peterson]

Conditional formatting can't change the values.

If the changes worked, then I think I'd just record a macro that did those
changes. Then rerun the macro when I need it again.

 

[maxlog]

I like Debra's suggestion, it works fine for my project.

Thank you all for your great help!!!