convert numbers in Pivot table to Symbols

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 !!!

How about Conditional Formatting?

--
Bernard Liengme
www.stfx.ca/people/bliengme
remove CAPS in email address


"maxlog" <[email protected]> wrote in
message news:[email protected]...
>
> 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 !!!
>
>
> --
> maxlog
> ------------------------------------------------------------------------
> maxlog's Profile:
> http://www.excelforum.com/member.php...o&userid=30605
> View this thread: http://www.excelforum.com/showthread...hreadid=502574
>

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.

maxlog wrote:
>
> 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 !!!
>
> --
> maxlog
> ------------------------------------------------------------------------
> maxlog's Profile: http://www.excelforum.com/member.php...o&userid=30605
> View this thread: http://www.excelforum.com/showthread...hreadid=502574

--

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.

Dave Peterson wrote:
>
> 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.
>
> maxlog wrote:
> >
> > 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 !!!
> >
> > --
> > maxlog
> > ------------------------------------------------------------------------
> > maxlog's Profile: http://www.excelforum.com/member.php...o&userid=30605
> > View this thread: http://www.excelforum.com/showthread...hreadid=502574
>
> --
>
> Dave Peterson

--

Dave Peterson

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!

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!

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.

maxlog wrote:
> 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
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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 wrote:
>
> 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
> ------------------------------------------------------------------------
> maxlog's Profile: http://www.excelforum.com/member.php...o&userid=30605
> View this thread: http://www.excelforum.com/showthread...hreadid=502574

--

Dave Peterson

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

Thank you all for your great help!!!