Sometime in November, I got an interesting E-mail from a gentleman named Guru. The title said “Excel Workshop in Maldives”. In the email, Guru introduced himself and asked me if I can come to Maldives to conduct few Excel workshops for companies and individuals.
I usually neglect such mails as many times the actual training (or big consulting assignment etc.) will not happen. So I replied to him giving my number and asked him to call me. I was surprised to receive his call. After talking awhile, it was clear that Guru is tenacious and would not take No for an answer.
So we set things in motion and thanks to Guru’s perseverance, I ended up boarding a flight to Male on 22nd of January. This is a story of what happened next…,
(note: I traveled with my son & Jo. Business trip for me, beach holiday for them.)
The journey (onward):
Almost all the flights to Male from India leave from either Trivandrum, Chennai or Bangalore. We choose the Trivandrum option because it is a direct flight (other 2 flights have a pit-stop at Colombo, Sri-Lanka). The flight took 75 minutes.
Soon after take-off, all we could see was big-blue Indian Ocean beneath us. About 60 minutes in to the journey and small islands and resorts started appearing. They looked nothing like I have ever imagined. The seas were clear blue or green or a mix. The beaches were pure white. The islands looked lush with greenery. The water villas (houses constructed on water) looked calm and elegant.
The same pattern repeated for next 5-10 minutes in various islands before our pilot announced that we would be touching down at Male international airport.
Male Airport
When I saw Male’s map on Google, I thought the airport could not fit anything bigger than an ATR. But I was surprised when we boarded an Airbus 320 at Trivandrum. But I was in for even bigger surprise when we landed at Male. The airport is as big as many other airports I have seen. In fact, they have an entire island for the airport.
So after getting down and finishing immigration formalities we came out.
A note about visas for Maldives:
Maldives has no prior visa requirements for a majority of countries. Almost anyone can go and get a tourist visa for 30 days. Visit Maldives Visa site for more info.
Guru was waiting for us outside airport. We took a ferry to Male, the capital of Maldives. The airport and capital city are well connected by frequent ferries (one every 10 mins). It takes about 15 mins to reach the city.
Male City – Initial Impressions
I have been to some of the most crowded cities in the world – New York, Mumbai, Hong Kong. But I never saw narrower roads than I did in Male. This is probably the first impression you get too. A majority of Maldivians live in Male. Since the city is a small island, they had to get creative to contain so many people and shops and everything. Some of the impressive ways they manage this:
- Almost all roads are one-ways
- Many buildings are multistory and quite narrow.
- Many elevators are small and can carry 6 people at a time.
- People use bikes or cycles (although you will find a lot of cars, including a BMW that was parked near Sultan Park for the entire duration I was there)
Since I was traveling with family, Guru has arranged accommodation for us in his boss’ house. This was much better than staying in a hotel as my son got more space to play and run.
How my Workshops went?
I had a busy schedule from the moment I landed. On the first day, we conducted a Free Excel Workshop in Aminiya School. This was to get participants to sign-up for our paid workshop.
I choose the topic of Conditional Formatting as it is very close to my heart and the session went very well. We ended up adding few more people to our evening batch.
Later Guru briefed me that I need to conduct 18 hours of Excel training at STELCO (State Electricity Company) and 9 hours at HDC (Hulhumale Development Corp)
We started the training at STELCO next day. We spent the first day discussing Excel overview and writing formulas. The participants were quite friendly and by second day we were cracking jokes and having fun while learning lots of stuff.
Later in the night I conducted a session for individual participants (about 9 of them) again on same topics.
This went for 3 days before we added one more client – FSM (Fuel Supplies Maldives).
What I learned from my workshops?
- Start with Overview: I always assumed that people would know how to use Excel. So my learning plan started with Formulas (that is how it is for Excel School too). But I was surprised to realize that people want to have a good overview of Excel before jumping in to specifics. So after frist day morning, I changed my plan. My first class became “overview of Excel”. In fact, I even added a lesson Zero to Excel School after coming back.
- No plan: Before leaving for Maldives, I made elaborate learning plans for both intermediate and advanced Excel sessions. But after landing there, I realized that it is better to have a loosely structured plan and modify it as per participant’s needs.
- Metaphors are powerful: Often while explaining concepts like namebox, relative vs. absolute references, countif, pivot tables, conditional formatting it was difficult for some participants to understand how they would be relevant. But thankfully, using metaphors I could get my point across
- Talking for 8 hours a day is a lot of work: After talking for more than 8 hours a day for a week, suddenly I respect all my teachers even more.
- Pivot tables excite people: In all my classes, when I demoed pivot tables, I could hear “wow!!! that is so much better” from many participants. They raise the overall curiosity of the class and suddenly everyone is paying attention to know more. (hint: expect more pivot table stuff on chandoo.org too)
Participants’ Response:
We had about 50 people attending the workshops. And a majority of them gave a very high rating (4 or 5 out of 5) for it. Many actually wrote testimonials and praised us for doing it. All 3 companies are hopeful to do a follow-up workshop in a few months.
I also learned a lot of things about Excel while explaining or answering students’ questions.
I had a self-doubt whether I would be able to pull off an in-person training program. Now, I am more confident. I can handle future workshops more easily.
So it was a win-win for all of us.
What we did when I was not teaching Excel?
Despite being a small city, Male has lots of surprises. So we were busy for the first 4 days exploring the city and discovering our way back to home. The best things I liked about Male are,
Walks: You can walk from one end of Male to other end in about 30 mins. So you would start from one ocean front and end up another. Although the streets are narrow, they all have foot-paths. So it is easy to walk, leisurely explore the shops and other attractions, watch other tourists and locals.
Ocean Front near Jumhoore Maidhan: is a very lively place to sit and watch tourists, enjoy the sun, ocean breeze, play in the park (or watch your kids play).
(Nishanth and Jo in the sun – Near Jumhoore Maidhan, Male)
Food: Lots of restaurants serving authentic Asian, continental and Italian varieties. So many varieties of fish and other sea-food at really affordable prices. We especially liked Thai and sea-food at Lemongrass restaurant near Farhadee Magu (close to Sultan Park).
People: Although we did not interact with many people outside my training hours, what I found is that people are very friendly, helpful and cheerful. Participants of my training program are even more awesome as they showed immense curiosity and sense of humor.
About the beaches:
But many people do not go to Maldives to visit Male. They go because of the spectacular beaches in Maldives.
Unfortunately, due to my busy schedule, we could not get much time to explore various beautiful islands in the archipelago. But we did go to two different islands and they both were mind-blowing.
Hulhumale:
(view from Hulhumale jetty)
This is an island close to Male. Government of Maldives is developing this island as the mainland Male is very congested. This is where all the new projects are coming up. (and HDC, one of the companies I did training for, is developing the island)
We went to Hulhumale by a ferry on Wednesday (26th of Jan). Hulhumale has lots of beaches (Male has only ocean fronts and one artificial beach). The beaches are very clean, sand is clear white and you can walk almost 200-300 meters in to the water without getting drowned (in some places). We spent the whole evening there.
Six Sense Resort – Lankanfushi Island
(view from a water villa – Six Sense Resort – Lankanfushi Island)
A couple of the evening batch students worked at Six Sense Soneva Gili Resort in Lankanfushi Island (one was a training manager and another is a F&B manager). Initially, the training manager tried to arrange a similar workshop at the resort. But they could not make a decision immediately. So we agreed that next time I visit Maldives, I will conduct a workshop at the resort.
But they invited us to spend a day at the resort. Since Maldives is an Islamic country and Friday (and for some companies Saturday) are holidays. So we decided visit the island on Friday (28th). Initially I wanted to say no to the proposal as I was too tired with all the classes. But my wife was keen to enjoy the beaches. So we did go.
Going there proved to be the best part of the trip. The island and the beaches there are nothing like I have ever seen. The waters, sun, sky and calm resort instantly rejuvenated me. We spent the whole afternoon at the beach. I even swam for a while.
We had some coffee and snacks the restaurant. My son started crying loudly when the restaurant supervisor, a Japanese lady, said hello to him.
(3 of us at the staff canteen – Six Sense resort)
We left the place barely in time to catch the ferry back to Male.
Are you planning to Visit Maldives? A short tourist guide:
When to go?
November to March is a really good time to visit Maldives. It is very sunny and hot through out the year here. So you may want to avoid the summer months (April-June) or Monsoons (July-October).
What to take?
Beach-wear of course. They have showers in Airport too!!! Carry sun-glasses, hats, slippers, cotton clothes.
About Visas:
For a majority of countries, You do not require visa to enter Maldives. You can get a tourist visa for 30 days upon arrival. Visit Maldives Visa information site.
How much it costs to visit Maldives?
Maldivian currency is Rufiya (MVR). You can get 12.75 MVR for each US $.
Almost all the items are imported to Maldives from near-by countries. For this reason, many food items etc are expensive. That said, compared to costs in many developed countries, Maldives is cheap. You can have a really good meal (with sea-food etc.) for about $10.
Some hacks for budget travelers:
- For breakfast, go to Seahouse at the Hulhumale Ferry Terminal. They have breakfast buffet for 65 MVR on all days. You can find all varieties (English, Continental, US, Asian) of breakfast items, juices etc. The best part is, you can watch the ocean, speedboats, soak in sun while enjoying the food for a couple of hours.
- Do not buy milk: It is very expensive here. Instead, you can buy Milk powder and use it for coffee / tea. You can also get yogurt.
- Take a cab: Taxis are un-metered in Male. You can go from anywhere to anywhere by paying just 20MVR. So if you are tired, hail a cab.
- Eat out: There are tons of places through of Male that are cheap and delicious. You can walk in to almost any restaurant and eat food for less than $20.
- No shopping: Since almost everything are imported, you will find the prices to be on higher side for usual shopping items like consumer electronics, clothes, shoes or cosmetics. I was told TVs are cheaper, but carrying one to back home would be a pain.
Closing Thoughts:
We really enjoyed our brief stay at Maldives. I am thankful to Guru and IIPD (the organization Guru works for) for everything they have done to make the training workshops a great success.
Special thanks to STELCO, HDC and FSM for trusting me and giving their time & attention.
I was left with a few hundred Rufiyah by the time we returned to Airport. But I did not give them back to Guru as I know that I would be visiting Male once again. But next time, I hope I could spend a few more hours by the beach too.
Bonus Excel Tip for those of you making this far:
I know you read the travelogue because you want to know more about me. I find it very humbling. So here is a small Excel tip 🙂
Use NETWORKDAYS.INTL() to calculate working days between 2 days with custom weekends:
Often, you may want to find out number of working days between 2 dates. We can use NETWORKDAYS() formula to do this. For eg. NETWORKDAYS(“1-JAN-2011″,”31-JAN-2011”) would tell you the number of working days in Jan (assuming Saturday and Sunday are weekend holidays).
But what if you live in countries like Maldives, where Friday is the weekend. Well, thankfully, you can use the NETWORKDAYS.INTL() formula. This is a new formula introduced in Excel 2010.
So =NETWORKDAYS.INTL(“1-JAN-2011″,”31-JAN-2011”,16) will give you the number of working days in Jan 2011 assuming Friday is a weekend holiday.
But what if you don’t have Excel 2010?
Well, you can use networkingdays() custom UDF instead.
56 Responses to “Creating in-cell bar charts / histograms in excel”
Ay jhakkas!!!
Man, you're on a roll. A true-blue Excel innovator. What you're writing makes me think - why didn't anyone else think of this before?
Now that I've showered all the praises on you, it won't hurt to have a few comments on my blaag 😉
PS. I meant the innovator part.
@Amit ... thanks, I was also curious why this one was not explored, but again, I havent really searched a lot to ensure that I am posting the same ideas again. My intent is to make few people to benefit from this, if that happens I would be happy...
btw, posted a comment on your blaag... hope you are happy now 😀
Don't worry about repeating the ideas in the online world. As long as you are not copying it off anyone else and it is helpful for the readers, it's fine.
PS. the comment does not count.
The idea actually is not a new one :).
Check out MicroCharts
http://www.bonavistasystems.com/
to see how far you can get with font based in-cell charting
[...] can never get tired of in-cell charts, whenever I get sometime, I try to experiment something on them. Here is an idea to design true [...]
[...] Since we can insert any character in to a cell using formula, by installing a custom bar chart / pie font in our computer we can create incell graphs in excel with ease. Click here to see example pie chart, line chart. [...]
Where is the file? I can't seem to locate it. I want to donwload it. Thanks Chandoo!
Found it.
Great job, Chandoo. Love the site - and the fact that you provide downloads to help us (me) learn your secrets faster. I downloaded the font but can't figure out how to add it to my font library... Any hints? Thanks! Keep up the fantastic work.
@Mahqooi: Thank you and welcome to PHD 🙂
This is how you can install a font in a windows machine:
unzip the font files (if needed)
select and copy the font file to clip board by pressing ctrl+c
go to control panel > fonts
paste the file by pressing ctrl +v
repeat this procedure for other font files if any
if you are using mac, just right click on the font file and select install option.
let me know if you have some issues with this.
Hi Chandoo,
is there any mirrors for the bargraph font?
it seems that fontstruct.com is down for maintenance.
thanks!
@Cybsych: I am not sure if they have any mirrors. I will look in to my backup to see if a copy of the font can be located and ping you back. Thanks.
hi Chandoo, fontstruct is back online 😉
BTW, I am wondering about this in-cell chart.
How do I apply an automated conditional formatting to only a bar/point?
For example, the first image in this post, whereby RED = highest, BLUE=lowest.
Chandoo,
I guess this bars only work with positive numbers? so if you a list of costs per month, but one month you have negative cost meaning income due to let's say vendor credits. This incell bar could despict the month with a negative digit. or could it?
hi Chandoo, guess that you missed out my query 😀
is there a way to highlight the MAX and MIN bar based on the actual data (not the normalized)?
@Pedro, for that you need to have another set of characters (may be A-J for 0-9 and K-S for -1 to -9 and then use them to show the bars. It is a bit tricky, but achievable.
@Cybpsych: The highlighting was done manually (As you can see, there is probably no easy way to highlight / change colors of a portion of cell using Conditional formatting etc.). I am sorry, but you need to use someother sparkline technique to achieve this (or, write your own macro)
http://chandoo.org/wp/2008/09/05/microcharting-excel-howto/
thanks chandoo!
I love this simple and quick way of visualization results. I would like to learn more about normalizing values (i.e. the use of linear normalization). Can someone kindly point me in a good direction for this beginner? Much thanks to everyone (especially Chandoo) for the wealth of information provided. Long live the internet age!
@Jason: you can use simple excel formulas to normalize a set of values. If the list of values is in say a1: a10 and you want them to be normalized from 1 to 100, you can do that with a formula like: =A1/max($A$1:$A$10) * 100. Also, you can use the RANK formula to calculate the percentile of any value in the list.
[...] Bar | Sparklines | Pie charts | Bullet Graphs | w/ Conditional [...]
Nifty way to normalize the data....I'll have to take that into account when working with my charts.
One thing I'd like to add, you can eliminate the need for custom fonts with the bar charts by using a REPT function and using a small "g" set to the Webdings font. It's more likely anybody opening the file will have access to that font than the custom one you've provided. (More portability is a good thing 🙂 )
Portability is great.
I don't quite see how the REPT formula and the webding fonts can combine to solve the portability issue.
Mind you, i see that +REPT("g",1) will give you a bar, but we would need several bars of unequal lenght.
Can you elaborate?
Thank you
@Matt: I almost forgot about this comment. Thanks to Pedro for the bump.
As he points, portability is a good idea, but we will not be able to get bars of variable height using webdings font.
We can ofcourse use that along with text rotation and char(10) to create a pseudo incell bars. Here is a tutorial: http://chandoo.org/wp/2008/07/15/incell-bar-charts-revisited/
@Chandoo: Yep, that's exactly what I meant, use your text rotation and char(10) trick with REPT("G",) (then set the font to Webdings) to get your string of bars with variable height.
@Pedro: REPT("g",1) will give you one "g" (or in Webdings a bar of 1 height).
REPT("g",B2) will repeat for the value in B2... 🙂 Use that with Chandoo's take on linear normalizing, and yer all set.
Wingdings with an "n" character would be even more portable, but just doesn't look quite as cool...but pretty much everybody has that font, so it'd be portable.
You may have to adjust the font size in order to get all the bars to show correctly, perhaps some sizing of the row heights as well...
You can fake an incell line chart by using:
REPT(" ",B2-1)&REPT("n",B2)
where B2 is the value in the cell you want as a data point.
Wow, the formatting was horrid, let's elaborate a bit more...
REPT("",-1)&REPT("n",) - would give you a line graph, where could be a reference to each cell you'd like as a data point.
REPT just repeats a text string a number of times, it can be either a hard number (like Pedro's example), or a reference to a value in another cell (more handy). I believe Webdings is a common font in the MS Office suites I'm familiar with (2000 thru 2003), but I'm not sure of 2007's suite.
@Matt A: I am sorry for the formatting mishap. I am afraid of using too many plug ins, but I guess a simple HTML based comment box seems like a good idea now that lot more commenters are typing formulas and vba code in the comment box.
Coming to the formula.. thanks for sharing it. And yes, you are right, webdings is common to Office 2007 too. But even better solution would be to use good old pipe | symbol. When the font is Arial, the pipe character spacing looks optimum and subtle enough to look like an incell histogram / column chart.
After some searching through the character maps in Arial I noticed that there's a box symbol --> ? (created by holding ALT then typing 5595 on the numpad) that would work perfectly as another character to use for column charts. It looks just like the Webdings "g" character.
Is there a way to change the colour of the bars based upon the data. eg. 1-5 = red, 6-7 = amber, 8-10 = Green
@Ben... you can change the color of all bars in a cell using conditional formatting. But selectively changing color of bars inside cell is not possible unless you do it manually or through VBA.
[...] Creating in-cell bar charts / histograms in excel @ Pointy Haired Dilbert Filed under: Stuff [...]
Is this work only for the numbers or will it work for % data also. I tried to do the same for % data, but i didnt get. Pls let me know the formula for % data.
[...] trick is to use Incell Charts. [...]
Hello Chandoo,
I really like this, but I have Office for Mac 2011 and for the life of me I cannot figure out how to see the bargraph as an available font.
I have followed all the instructions for adding a font, but it does not appear. Do you have any suggestions?
Thanks
prb
Thanks. This one was cool and helpful. Can we experiment the same with "in cell" line graph as well? 🙂
Chandoo,
How do you "manually" change the color of the last bar in the series?
Lawrence
@Lawrence
Select the chart
Select the series
Select the last point/column of the series
Ctrl 1 or right click Format Point
Select a color
Hui,
Thanks!
I should have been more descriptive. What I meant to ask was about the in-cell bar graph created with the REPT function described above. How do I get the last REPT (the last bar) to be a different color than the rest?
Lawrence
@Lawrence
You cannot change colors in a cell using formula
You can use either VBA code or do it manually
Select the cell
Copy and paste it as values
Edit the cell F2
using the arrows move to the character you want to color
Shift and select the cell by arrow keying over it
with the characyer selected
Ctrl 1 (Format Cells)
Change the Font Color to suit
It won't be a color change per se...but you can set an IF statement in your REPT formulas for different characters to show as the bars. The characters "c" and "g" in Webdings are both boxes, one is a solid block, the other an outline.
For example, say I wanted to highlight the highest bar in my REPT formulas...my formula to translate the numeric cells A2:A15 to characters would be:
IF(A7=MAX($A$2:$A15),REPT("c",B7),REPT("g",B7))
so if the cell I'm checking (here it happened to be A7), is the highest number...its bar would display differently further along down in the concatenations...
@Hui...THANKS!
@ Matt A... Very cool idea. What formatting do you recommend for the cell? The Webdings "c" hollow box is very faded and hard to read even if bolded and bigger font size is used. If I could just punch it up a bit it would be perfect with 5 "c" columns followed by a single solid "g" column...as in showing the trend in the trailing 6 months of data.
Lawrence
@ Lawrence
Good question...lately I've been using ? (which you get from holding ALT then typing 5595 on the numeric keypad) for most of my bars. Unfortunately the character map doesn't lead me to a differently "shaded" box of the same size. Reason I use this nowadays...it's part of arial font...just a special char map character I can rapidly input w/o any formatting nonsense.
I'll check to see if I can replicate another box of same size that may have different shading using the same method...no luck as of yet.
I've just built the in cell bargraph and was trying to create a pop up window which would display the Monthly Sales for Last 12 months when they click on any of the bargraph cells
[...] Reference:Â http://chandoo.org/wp/2008/05/13/creating-in-cell-bar-charts-histograms-in-excel/ Like this:LikeBe the first to like this. [...]
[...] To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip [...]
Hi, there is a problem with the Bargraph font. On my win7 machine it works perfectly but when I try to install it on my boss's mac it returns an error called " 'Name' Table Structure"
I tried to install on two different macs and the same error resulted. As a result the font does not show up as an option in any program.
Â
Just an FYI. I don't use macs but I know some people do.
Whats up! I just wish to give a huge thumbs up for the good info you might have right here on this post. I can be coming back to your weblog for extra soon.
[...] like .docx, .htaccess etc.) 43. To quickly insert an in cell micro-chart, use REPT() function… Get Full Tip 44. COUNT() only counts number of cells with numbers in them, if you want to count number of cells [...]
Thanks Chandoo for the font!! It works great once installed on my machine, but is there any way (besides printing and scanning the doc) that I can get the graphs to show up on other peoples' machines without going through the font install process? My file has to be sent out to clients that don't have that font installed.
Sarah, Excel doesn't allow embedding of fonts (aside from a workaround using a macro). The font will need to be sent to all who want to view the file. I went through the same question with my boss. I ultimately just installed the font on her computer.
If the data is only to be viewed, and not modified, moved, etc. you can save the file as a pdf. The font can be viewed that way.
Hello every one there is a problem I need auto update summary formula from other sheets data pick please give me sample file and also auto up grate summary sheet format.................
@Joesali
I'd suggest asking this type of question at the Chandoo.org Forums
I'd suggest uploading a sample file also
Hi chandu,
Apart from excel, i need the formula to find bar graph height dynamically when using with log scale, for example for linear graph i would take the maximum value to height of the panel as
(value divided by maxvalue) * height.
Now , i am using a logarithimic graph can you tell me the right formula which fits perfectly.
Thanks in advance
Nice info... Thanks... very hepfull... 🙂
The font does not seem to be available at fontshop. Is there somewhere else to download the bargraph font?
@Amber
Try doing a Google search for Bargraph Font
it returns several possibilities
Is there a way to do this without using bar graph font? We have a financial report to be published to stakeholders and they will not have this font installed, so probably will not be able to view the bar chart as well.