Hidden Treasures – Best of Excel on the Web (Sep 2012)

Last updated on September 21, 2012 By

Do you remember the story of Ali Baba and the 40 thieves? The poor woodcutter Ali Baba is in the forest one day collecting firewood, when he overhears a group of 40 thieves. They have come to hide their treasure in a secret cave. But this is no ordinary cave, as it only opens when the secret magic words are uttered.


Quietly hiding himself high in a tree, Ali Baba watches the thieves, loaded with glittering gold and shiny jewels, as they say the magic words “open sesame”.

The entrance of the cave rolls wide open, the thieves enter the cave and unload all their treasure, then they close the cave again with the magic words “close sesame”. Ali Baba’s mouth hangs open as he imagines all the riches and priceless gems waiting inside that cave. His heartbeat quickens and his palms get sweaty, because now he knows the magic words to open the cave!


Did you know that just like Ali Baba, you have come across a secret cave filled with treasure – not any ordinary treasure but special “Excel” treasure. I’m sure that you will find the articles and links in this secret cave to be very valuable indeed! To enter this secret cave, all you need to do is say the magic words “I love Excel!”

Image Credit: Treasure Chest courtesy of chancey

1. Excel Applications

Here are two specific applications that I’ve come across recently. They work inside Excel and have been created by professional developers. You may find them useful if you are a project manager or product manager. The first is called GanttDiva and will help you to create your own sophisticated Gantt Charts in Excel.

GanttDiva – Create your own Gantt Charts in Excel

Do you manage projects on your computer? Have you been trying to use Excel for project management? If so, you should be very interested in GanttDiva, which is an easy to use VBA program that works in Excel to help you plan and create a project schedule and Gantt Chart. It offers full support for schedule logic, including the display of logical relationships. You can generate Resource Demand Charts based on your schedule. If you have a copy of Excel, then with GanttDiva you can simply create your own project plan.


GanttDiva works with Excel XP, Excel 2003, Excel 2007 and Excel 2010. I plan to write a more thorough review of GanttDiva in the future, but in the meantime you can try a 100% free, full-featured copy of GanttDiva for 30 days. You will find that GanttDiva comes with a thorough built-in help system. To get your free trial copy, visit this page.

Bruce McPherson’s Excel Roadmapper

If you want to create a product or technology roadmap in Excel, you should check out Bruce’s Excel Roadmapper tool. He offers it as a free download from his site and offers an online user guide that you can read here. If you are interested in finding out how he created the Roadmapper, there is a more detailed article here.

2. Charts

Introducing the Power of Year-Over-Year Performance Charts

You can use year-over-year charts to uncover hidden trends in data as varied as sales, costs, web traffic, customer complaints and key operating or financial ratios. To find out more about the power of such charts, check out Charley Kyd’s article here.

How to make Box plots in Excel

When you want to summarise how your data is distributed, you can use a box plot. To create one in Excel can be tricky as there is no currently no built-in chart type for a box plot. If you want to learn more about box plots and how to create them, check out Chandoo’s article here. In addition Keith Hew has written a concise article about “What is a Box Plot” that you can read on his blog here.

How to Create a Panel Chart in Excel

When you want to show similar charts that are neatly aligned, you can consider using a panel chart. For instance you might want to show the total defects across a product range, or product sales across different cities. There are 2 ways to create Panel Charts in Excel (1) make one complex chart with separate internal panels for the individual charts, (2) align separate charts and format them to look like panels. To find out how to do the first, check out Debra Dalgleish’s article here, and to find out how to do the second, read Chandoo’s article here.

Conditional Formatting of Excel Line Chart using VBA

Sometimes you may find the urge to change the color of your charts to match specific criteria. For example you may have a chart showing weekly visits to a website, and you want the line of weekly visit to be blue when the trend is up, black when the trend is level, and orange when the trend is down. To achieve this, you should apply conditional formatting to your chart – but the question is, how do you do that? This is an advanced technique that Jon Peltier neatly explains in his article here.

Great Charts to give your managers, customers and clients today

If you urgently need a set of professional quality Excel charts, look no further than Charley Kyd’s Swipe File. Perhaps you have wanted a special-looking chart that will impress your manager? One that will stand out from every other chart that you see.


But you have a problem, and that is you don’t have the time to create a unique set of charts because you’re so busy doing your day job. If that sounds like you then I recommend you check out the impressive collection of Quality Excel Charts created by Charley.


They offer a quick and inexpensive way to create professionally designed charts and tables for your reports, slides, and other presentations in Excel, on the web, in PowerPoint slides and so on. Available for Excel PC or Mac, from Excel 97 to Excel 2010. Browse through the complete collection here.

3. Dashboards & Visualization

Interactive Pivot Table Calendar & Chart in Excel

Wow – what do you think of the interactive calendar chart demo above? To achieve this impressive effect you must have Excel 2010 because it utilises slicers, which is a feature introduced in Excel 2010. Find out how this treasure was created on Chandoo’s page.

Excel Mouse Rollover Techniques

Recently Jordan Goldmeier at Option Explicit VBA has been playing with a novel technique to get Excel to display information when you roll the mouse over specific parts of the worksheet. Imagine using the mouse to select some data points on a chart by drawing a rectangle, and then having Excel pop up information on just those points. Wouldn’t that be cool? If you want to see this in action, check out Jordan’s blog post here. And Jordan has also put together a FAQ to answer some common questions about the Excel Rollover technique.

Find out whether you make these 4 mistakes with your Excel reports

When you use Excel to create management reports, there are several big mistakes you could be making. If you make these mistakes it’s likely that you are spending far more time on your reports than you need to, or you are creating reports that don’t add value to your organisation.


Does this sound familiar? You spend hours creating a one-off spreadsheet with charts displaying the latest company information, and finally end up with something you’re happy to show your boss. When they take a look they are impressed, and they find the spreadsheet so useful that they ask you to update it every week for the weekly team meeting.

Oh – and now you feel trapped because you are overworked and your new spreadsheet has turned from a one-off analysis into yet another weekly thing for you to do. And that’s just mistake number 1.


Find out what the other 3 mistakes are, and learn how to cure them. Head over and check out Charley Kyd’s Dashboard Reporting with Excel page.

4. Formulas

Everyone uses formulas in Excel, and what better way to learn more about formulas than from experts who share their knowledge online? Here is a collection of useful formula tutorials and tips from around the web.

5. VBA Programming

If you know how to read and write VBA, you can develop some very useful applications inside the Excel environment. Most of these articles are for advanced users of Excel, so I’ve saved this section for last.

Do you need advice or help with Excel?

You can easily spend weeks or months learning about different techniques for creating Excel reports and applications before you actually create your own robust and scalable reporting solutions. If you need a a reporting solution and don’t have the time or bandwidth to create your own, please get in touch with us through our Excel consulting service.


We are experienced in creating reporting solutions and would be happy to discuss your specific requirements. Whether you need a totally new Dashboard for your company metrics, or want us to modify and improve an existing report or application, we are ready to help. You can read more about our spreadsheet services on the Excel Consulting Services page.

Related Posts:


Connect on YouTube, LinkedIn, Twitter.

Hey, I'm Victor Chan

Are you struggling with complex Excel tasks? Feeling overwhelmed by spreadsheets that are hard to use?

Many people believe mastering Excel is about learning shortcuts, functions, and formulas. But this overlooks the importance of building practical, real-world applications. It's not just about knowing the tools. It's about using them effectively.

That's where I come in. You'll get a unique perspective to Excel training from me. I have over 20 years of experience at Deloitte and two global tech companies. And I know what can make a difference in your career.

Let me help you integrate Excel into your professional life. Starting today. Read one of my articles, watch one of my videos. Then apply the new technique to your work. You'll see the difference immediately!


Discover the PROVEN Blueprint for transforming your Excel skills, supercharging your productivity, and standing out in your career! My course helps you to learn Excel VBA and save hours of time even if you have zero prior experience with programming.

Solve tricky Excel problems and take your work to the next level! Get customized solutions for your unique needs. Save time and gain insights with truly expert Excel solutions from only $97 per task.

Get a clear overview of your project progress using the Excel project timeline. Use it to communicate the big picture, track task progress, and stay on top of your project goals. Stay organized with our project timeline!

Our cheat sheets provide quick and easy reference to commonly used Excel VBA concepts and code snippets.

Unlock new levels of productivity and efficiency with our cheat sheets, and write VBA code like a pro in no time.

RECOMMENDED READING

Are you looking to upskill and stay ahead of the curve? Excel is a powerful tool that keeps growing in demand. We round up the best online courses for learning Excel.

Are you looking to up your spreadsheet game? Excel is an invaluable tool that can help you stay organized and save time. From data analysis to budgets, Excel can do it all!

Today, having Excel skills is more critical than ever. Those who know how to use Excel are more likely to find higher-paying jobs. And get promoted faster.

JOIN FREE EMAIL NEWSLETTER

Step up your Excel game! Join our free email newsletter and get updates on how to become more awesome at Excel.