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.
70 Responses to “10 Tips to Make Better and Boss-proof Excel Spreadsheets”
Proper print settings on each sheet helps your boss to print the reports quickly without hastling you after printing irrelevant stuff.
It is highly relevant that you print your reports once before circulating it to your boss or other people.
Knowing that what your boss actully look at in the entire report can be very usefull. You can build a good summary of what your boss wants and put that as separate tab in the form of dashbord report, so that your boss does not peep into rest of your work and start pocking you with irrelevant stuff.
You can also put that Dashboard into the email summary and not trouble your boss to open your workbook. This is ultimate boss proof tip and I have been using this for long time now.
Thank you Chandoo. Great checklist to follow before delivering an excel spreadsheet to someone else. Some points you mention are seemingly so simple that we might overlook them - like selecting cell#A1, but they make a difference to the impression the spreadsheet creates at the recipient's end.
Dear Chandoo,
Great tricks.
One trick I use (more and more) is to hide the sheet tabs and to hide the formulabar via the 'tools' 'options' and the 'view'-tab.
Another trick is to limiting the scrolling area to hide all columms (or rows) until the end of the sheet. Select the column, press CTRL+SHIFT+RIGHT, right-click on the column and hide (also possible via VBA).
I was wondering though if 'boss-proof' is related to 'excel-stupid-proof'?
Cheerio
Tom
Just wondering if the hiding formula bar really works when a recipient opens it whose "view-show-Formula Bar" is still checked...
It's saved to the sheet I believe.
Absolutely agree with this post !!!
on the past months, after reading this blog, PTS's and Debra's Contextures, one of the things I've beggining to do as a best practice is to create all my spreadsheets with 3 tabs: data, summary and control, and this last one generally xlveryhidden, and sometimes the data one hidden as well.
And this restrictions are also being applied as best practice, and with a lot of benefits as you well mentioned. Furthermore, if combined with dynamic named ranges, formulae is more readable to users, and the WOW effect is often achieved when the question "How did you do that?" arises.....
Keep on the good posts !!!
Rgds,
Martin
Would you mind sharing an example of this technique?
Is there a way to keep the data in a seperate file rather than the same excel. This way you could keep presentation and data separate. But not sure how you would link up the two excel files
Yes, there is a way but it is not prefered.
I used this a coulple of times, (You need to code).
mail me if you need assistance with some sort
It entirely is possible. The problem comes though, when you share the spreadsheet.
If the recipient doesn't have both files, or access to both, things break when the values try to refresh.
ey, why is the boss a she??
haha - welcome to the future. About time.
Chandoo, one more trick that we could use with the help of VBA, RT click on the View code of the particular sheet, in the properties table set the Visible status to 2-xlveryhidden, this ensures the sheet name does not show up even when the BOSS tries to unhide the sheet from the sheet >> unhide option. Dont forget to password protect the VBA (available under tools >> VBAProject properties.
Very good tips, although I have to say Chandoo, that your cats probably need to be spayed or neutered if they behave like that. =)
Good to see all these tips on a single "sheet", and giving the name *boss proof*, and Dilbert was a great welcome 😀
The best way to "Boss Proof" (and "Self Proof"!!) a spreadsheet is to keep back ups. I use a macro that saves the last 3 significant versions of the spreadsheet all with a date stamp included in the file name.
To quickly select cell A1 on all sheet, use CTRL-Page UP or CTRL-Page down to navigate between sheets and CTRL-Home to select cell A1 (if you have frozen pane, it will select the top left cell of the section below).
Great list. And I follow every single item... I also use a consistent background color for input cells in every report/dashboard. And I use a little VBA to identify the user and change the report accordingly (selecting the right market, for example).
Chandoo, Nice post. I like to use the hidden Paste Picture Link option. Keep the original report you want displayed on a hidden sheet and only show the boss the report picture. Also great to watch the confusion when boss trying to select cells is worth the effort!
I usually save as PDF if there's no interactivity in the report. That way nothing can go wrong 🙂
PDFs work a dream for me too and saves the boss's EA from telling me all the time that she can't print my work!!
@All.. thanks a ton for sharing your ideas. I am thinking of writing a part 2 of this post explaining some of your ideas in detail.
@Bazlina ... I will make sure the boss is a HE in the next post 🙂
"10 Tips to Make Better and Boss-proof Excel Spreadsheets"...
Unless of course your Boss reads PHD !
Great article with one glaring error.
If (like me) the majority of your spreadsheet errors are *caused* by cats, adding more cats is just going to increase the problem.
@Hui you always have a boss, even if you are boss. If you dont have a boss, then may be a cat or even a dog.
@Debra: hmm... Are you sure the cats are not after the mouse? Go learn some keyboard shortcuts.. now 😛
Great Web Site. I've done almost all the above in trying to build my application and it's taken me hours and hours reading my "dummies " book. Thank you for all this information.
Is there a formula I can use that will automatically return to "A1" cell should an associate use the 10 page spreadsheet I have?
Is there a way to set an expiration date on my workbook so that beynd that date no one will get beyond the cover page?
Paul, in all my "user facing" workbooks (those that I distribute) I create a named range called "Home" on the worksheet(s) that are most likely to be used. Then I write a little VBA that selects the Home range whenever that worksheet is activated or on other triggers depending on the context of the sheet. This is more appropriate for the dashboard tabs or summary tabs my job requires.
But I usually set this functionality up early on in the design process so I can take advantage of it as well. I will sometimes assign a keystroke to the GoHome macro.
I'm in the marketing department (aka the picture department) and have to say that the macros/Excel sheets from our controlling department are the worst! They come to me to sort out the mess!!
@Peter: You can try creating a table of contents and then place it on each and every sheet so that user can jump to anywhere from anywhere. Here is a tutorial to help you get started.
Also, You can prevent users from accessing the workbook after a certain date using macros. But users can certainly by pass it by disallowing macros on that workbook.
@Jimmy: Wow... (just kidding) Welcome 🙂
I was recently given a spreadsheet to improve upon.
One of the "boss-proof" actions that the previous author had used was to use data validation instead of protecting the sheet to ward off people changing formulas.
After entering a formula or value into a cell, use data validation to only allow, in this spreadsheet, whole numbers between 9999999 to 99999999.
It's a bit of a pain to actually correct stuff instead of just unprotecting a sheet, but for those that know how to unprotect a sheet, it's a definite way to keep them from fooling with formulas.
Puchu,
We would love to see "Print" in your links section.
It helps us taking prints as neat as your posts 🙂
Chandoo,
I've emailed you a couple of times looking for avenues I need to try to put my workbook on the Internet.
I notice you use PremiumThemes for your Web Site...You must feel good about their service. Do you think PremiumThemes might be an option for me?
Paul
Instead of :
Now Right click and select “Hide” option.
Shortcut can be used : Ctrl+0 (to hide)..
sir i wanted to know,how to hide cells or tab without hiding rows and columns? PLZ TELL ME
Hi Chandoo!
Great tips! Im researching on an excel project now that you can create to "lighten" the size without sacrificing the data inside..
We usually encounter problems with the data, excel file is shared, in a network folder.. and there are 11 people that enters their own productivity in each tab.. however, there comes a time (uncertain) where some of the data they enter either gets deleted or changes value.. could this be a file size problem? are there other ways to create this file that will decrease data inconsistencies?
thanks!
[...] Hide un-necessary rows to create clean looking workbooks (and 9 more tips) [...]
[...] Presentation format: all spreadsheets, should be designed so that it is easy to follow the process flow and result. Almost every spreadsheet should be presentable and understandable to senior management without additional formatting or explanation. (tips: how to design boss-proof excel sheets) [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] on Excel formatting here: How to make better excel sheets, Formatting [...]
[...] 10 Tips to make better & boss-proof spreadsheets in Excel [...]
You will find another quick and easy technique here:
http://www.onsitetrainingcourses.com.au/main/page_blog_hiding_most_excel_rows_and_columns.html
[...] tips: Learn how to make better Excel sheets Spread some love,It makes you awesome! [...]
Save what you want the boss to see as a PDF. Absolutely foolproof and no cats hurt in the process.
I really enjoyed allot of the tips on here, especially the one on comments on cells. That will come in handy on allot of our projects. I would also like to share on on my little tricks. I am constantly working on several different reports with several different systems and in doing so I am constantly running in problems and my way out of them is simply calling <a href"http://www.reportingguru.com/"> Reporting Guru </a> and telling exactly what I'm going through and they can tell me exactly how to get out.
One of the things I've found to boss proof my worksheets are a few simple VBA scripts to automatically protect the workbook/worksheets, and direct them to the "Quick Look" dashboard page, I hide all of the raw data sheets before saving. The script looks like this:
Private Sub Workbook_Open()
Sheets("Summary").Protect Password:="password"
Sheets("Labor Cost by Site").Protect Password:="password", AllowUsingPivotTables: =true
Sheets("Labor Cost by month").Protect Password:="password"
Sheets("Quick Look").Protect Password:="password"
Sheets("Quick look").Activate
ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=False
End Sub
I also have a pivot that contains labor cost data which cannot be refreshed while the worksheet is locked.
Private Sub Worksheet_Activate()
Sheets("labor cost by site").Unprotect Password = "password"
Set pvttable = Worksheets("labor cost by site").Range("a1").PivotTable
pvttable.RefreshTable
Sheets("labor cost by site").Protect Password = "password", AllowUsingPivotTables:=True
End Sub
OPPAN GANGAM STYLE!
good
Your post are always with something creative , thanks for sharing this information , your post are worth reading and implementing 🙂 great job
Hi,
I will try to learn every point slowly !
Shokran Chandoo.
Best boss Proofing of sheets is useing indirect(address 😛 this prevents most smartass bossess from doing any actual changes cus the formula will be long and hard to understand for any bystanders..
Also putting the actual calculations on a different sheet can make a sheet bulletproof from bosses.. especialy if you put them in the Very hidden so when the boss learns how to unhide sheets he wont simply find them.
One thing iv also learned is that most bosses is scared of macros that gives "virus" warnings before beeing run 😛 That include the default warning from Excel...
Long formulas or work arounds is best way to go.
What's the best way to amalgamate two existing excel spreadsheets into one?
Two teams use the same format spreadsheets with individual data split into calendar months and I want to make them one without manually entering the data.
Alt + D + D + N
Write a query and viola, Two sheets into one.
Changing the properties of the file to read-only . (While the file is closed, right click on the file and check the read-only box.)
This allows my boss(es) to access the file -- even change it -- without being able to save their changes. If a boss likes his 'new' version, he can save it with a different file name.
But now -- how to prevent the boss from deleting the file altogether? Or deleting the whole network?
Hey man.
Think you can go as easy as to make a shortcut that links to your read only document. Then the boss wont know of the root document. He can figure it out but lets face it. He is a boss and 70% if them wont know squat
Instead of "Hiding" rows & columns, I find "Grouping" works best as its very easy to quickly see if a worksheet has hidden rows/columns. Sometimes hiding a random row/column is not easily noticed and can create issues.
I have one xl sheet with different dates in many columns and one raw's. I want to send this data to another xl sheets for each date. if somebody can help me will be great.
Dear Samantha,
Check out the website of Ron de Bruin. He has a great set of macro's and free add-in that can help you with this issue.
http://rondebruin.nl/win/s3/win006.htm
Tom
Hello, I have just found out that I made a mistake in my spreadsheet: I had a column of negative numbers, but one of them was positive (while it should have been negative). Is there a formula/system to avoid this?
Thanks.
Mariateresa
Yes, data validation. Values you denote would be between -1 and -999,999,999.
Hi,
Hiding any worksheet can be unhidden and messed around easily. I change the visibility in visual basic from -xlSheetVisible to -xlSheetVeryHidden. By this, even if you right click on sheets, you will be unable to find the hidden sheets.
Cool? I think so...
Very informative, Thanks
Is there a way to lock cells in an already protected worksheet.
(Thus the entire worksheet is protected, then the entire office can open it as read only but only a few users have the password to edit the file)
I would like an additional password or prompt box so these few users don't accidentally change formulas.
Itss such as you learn my thoughts! You appear too understand
a lot abnout this, like you wrote thee e-book in it
or something. I fel that you just could do with some percent to presseure the message house a little bit,
but insatead off that, this iis wondeerful blog.
An excellent read. I'll definitely be back.
It is in reality a nice and helpful piece of info.
I am happy that you just shared this useful info with
us. Please keep us up to date like this. Thank
you for sharing.
I laughed out loud reading the 2nd solution about moving to marketing department and making ppts.
I've been using "technical" sheets for a long time already and depending on the audience it is hidden or not. I'm currently in my NO VBA mindset, so the very hidden option is no longer. Using sheets names like: TechnicalCodes; ExplicitVariables;SetUp; HeavyCalc seem to work to my experience as they send along a message "Don' t you mess-up here, you fool!". A "Read This" section or sheet however does not work!
Reading stuff on this site has helped me develop a good habit of using colors and themes to assist the end user in being well-behaved. In my book the best advise here, because it is about the user experience and not only about protection your own work.
For dashboards I get rid of tabs and scroll bars. Besides 2 exceptions, I need to come across a manager who can turn them on again without my help.
Seems that I forgot about protecting cells, sheets and workbooks altogether. Damn!
Thanks for the informative article Chandoo, I've been struggling with Excel lately. It's a powerful tool, but hard to learn for me.
Thanks Chandoo for sharing these excel sheet tips it helps me a lot to understand excel more.
Nice roundup, Chandoo! Here's one more I thought would be relevant:
For Excel 2013+, you can hide the ribbon, as shown in this animated gif: https://gridmaster.io/tips/hide-ribbon-excel-space
This will simplify the interface, making it less likely for people to accidentally make changes. 🙂
THANK YOU SIR
I'm better at Power BI thanks to you!