Creating nice-looking advanced charts, dashboards

[Work4]

Hello, Excel world!

I've managed to go through basic books about creating charts in Excel. I can say, I even learned some tricks to make reports/dashboards nicer. But browsing though web, I found some really amazing reports done in Excel.
Reality-check: need to learn A LOT more to get to that point.

So now I'm here, asking Excel community to help me find the right books/videos/instructions on how to create reports like those in the images bellow.

Image #1: How to create reports with arrows showing you positive/negative growth?

Image #2: (I think I can manage the bars for "abs" change; but how do I insert those balls/circles, so that their size and color will reflect +/- change?

Image #3: I don't even know where to start with this one! Do you do 1 by 1? But how do you than manage sizes of pies, circles? They are proportional to the market size.
And coloring, how does that work? Getting those small green/red pieces in to reflect the change...

Image #4: Straightforward report; you have your monthly goals, plans and then actual realization. Line itself is already good, green & red colors make it stand out even more; a nice fancy thing, specially for making good first impression.
But still, how it's done?


That's just a few things, I'm sure there are plenty more which I have yet to discover. So where can I learn more about this? Maybe a quick question: is this still "pure Excel", or does further advanced charting require some coding?


One more thing: creating nice dashboards in Excel is easier when you have just Excel data. It get's a bit tricky when you start connecting to PivotTables. And if those are connected to OLAP cubes (Analysis Services), Excel can be a real hell (e.g., Excel chart will get all data from a table or no data at all; it doesn't allow you to show just a few rows from a PivotTable). Any advice on how to make life easier, tips & tricks? My current solution is to just create a lot of simple pivot tables and create one chart after another. It's also possible to convert PT to formulas and work with cube functions.

Thanks in advance for all your inputs!

---

---

---

 

[Michael M]

There are some great sites around.
Have a look at these for starters

http://www.mrexcel.com/Excel-dashboards-Xcelsius.html
http://www.contextures.com/excel-dashboards.html
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Excel Dashboards - Templates, Tutorials, Downloads and Examples | Chandoo.org - Learn Microsoft Excel Online
Free Microsoft Excel Dashboard Widgets to Download
AJP Excel Information - Gauge


You could also look on Jon Peltiers site and also John Walkenbachs site for more information on charting ( John also has a book called "Pivot Tables and Charts", that might be worth a read)

 

[thisoldman]

If you enjoy charting, Excel 13 is worthwhile. The charting tools are much easier to use, once learned, than those found in earlier versions of Excel. If you want to see some over the top Excel charts, you might start with some optical illusions at Excel Hero:
http://www.excelhero.com/blog/2010/02/excel-optical-illusions.html

You may differ in opinion from their criticism of charts and graphics, but articles and books by and about Edward Tufte and Stephen Few will make you think about your choices in graphing data.

Here's a list of some of Mr. Few's articles:
http://www.perceptualedge.com/library.php#Articles

Mr. Tufte's books:
http://www.edwardtufte.com/tufte/books_vdqi

Criticism and opinion first:
Red and green might be hard for the colorblind to see. The combination of red and blue is usually safer.

I find all these charts to be too busy. They don't tell their stories cleanly. Some of the data might be more clearly presented in a table without graphics.

Image 1: Variation on a bullet chart
Numerous tutorials for bullet charts are available. Here's one:
http://peltiertech.com/WordPress/how-to-make-horizontal-bullet-graphs-in-excel/

I think these are overlaid series in a combination chart. The arrows are custom markers. Using Excel 13, the arrows are found under the menu Insert > Shape > [Isosceles Triangle]. Created on the worksheet, then stretched, rotated and colored. Finally copied into the chart using, with Excel 13, "Series Fill" >> "Picture or texture fill".

Image 2: Circles of different sizes and colors
I think the circles are not strictly an Excel chart, more likely these are typographic effects. This might be done by setting the cell font to Wingdings 2, the cell contents being a conditional formula (IF, CHOOSE?) to return the glyphs from CHAR(149) to CHAR(152). Conditional formatting would then set the font color to red or green.

Image 3:
I don't use pie charts, I find comparing values by area to be too difficult. Same thing for bubbles. I have to hunt for the text to see what the actual data might be.

A minor criticism: the different size pies in each cell might be too subtle, I didn't pick up on that feature until the third look.

Image 4:
Stacked Area charts? You might also want to look at
http://peltiertech.com/WordPress/fill-under-between-series-in-excel-chart/.

 

[thisoldman]

Reviewing the graphs and my first reply, I noted the shapes used in Image 1 are not triangles but modified arrow shapes. The same technique would still apply. Replace the series markers with an edited arrow shape.