Here is tricky scenario, faced by Basil, our forum member,
I want to have Excel display a wing ding check mark when a user types “y” in a cell. I have been trying to do a substitute formula but putting the symbol in an unused portion of the spreadsheet and calling it to the selected cell but I can’t get it to work. Any thoughts? [more]
There are 2 simple solutions I can think of (other than the solution proposed by Axim5)
1. Using custom cell formatting
This approach is more robust, but a compromise. Instead of “y” and “n”, user should type “1” and “0”. Then we can use custom number formatting to conditionally display the tick mark symbols.
PS: you need to change the font to “wingdings”. 🙂
See this:
2. Using conditional formatting
[This method works only in Excel 2007 and above]
Starting with excel 2007, you can use conditional formatting to set cell format codes as well. This means, when the cell value is Y, we can conditional format the cell to show tick mark symbol. All you have to do is define a new rule, and then go to “number” tab and set the format code you want.
For eg. a code like this will give an output shown to the right.
There you go Basil. Go check all you want.
More resources on cell formatting and conditional formatting:
- Excel Conditional Formatting – 5 tips and tutorials
- Number Formatting in Excel – Tips
- Hiding a cell’s contents using conditional cell formatting
- Number format codes + Chart Labels = Pure fun
What is your favorite number formatting trick?
Share with us using comments.
7 Responses to “CP014: How to create awesome dashboards – 10 step process for you”
Very interested in topic unfortunately I get no sound when I play it.
Where can I get add ins for excel 2007
#Budala
Try using Google to search for Excel Addin
I would like to join your email newsletter.
@Andy
Goto: http://chandoo.org/wp/
The newletter registration is top Right corner
Hello Chandoo,
i am one of the great fan of your. i am one MIS executive in realstate company.And i try make dash board for CRM(Client Relation Management) Dept formance and anlysis process.Its get created not so effective according to can u suggest me .what i hve to give or take for makeing it for effective and attractive?.
regards,
Shashak verma
Hi,
I want to create an interactive dashboard in excel (Google Drive) which should have multiple data along with beautiful charts..
I handle two teams in IT Tech support so were preparing 2 different dashboards.. however need to club and want to create single report, can anyone help something like