Hidden Treasures – Best of Excel on the Web (Sep 2012)
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.
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.
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.
- How to use the Offset formula [Chandoo]
- How to create initials from a list of first, middle and last names [Get Digital help]
- How to use dynamic ranges in an Excel data validation drop down [Contextures]
- How to highlight duplicate entries in a list [Chandoo]
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.
- How to use VBA for web scraping [Spreadsheet1]
- How to create links to all sheets in a workbook [Get Digital Help]
- How to sort values in one cell using a custom delimiter [Get Digital Help]
- Use a mouse hovering technique to create an interactive chart
- How to clear an Excel table using VBA [RAD Excel]
- How to create an Excel calendar with VBA [Get Digital Help]
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.