background preloader

Jon's Excel Charts and Tutorials - Index

Jon's Excel Charts and Tutorials - Index
Related:  HOW TO?

Butterfly Chart – Excel Chart with Dual Converging Scales A Butterfly chart is a chart where two entities are compared side by side using scales meeting at the center. Due to its shape, the chart resembles a butterfly and hence the name. These charts are sometimes also known as Funnel or Tornado Charts though I find “butterfly” to be a better description as it allows for a greater variation in shape than a funnel or a tornado does ! So let’s jump straight into creating a beautiful looking butterfly chart. Getting the Data for the chart Although a simple looking butterfly chart is as easy to create as a bar chart, there is some value in adding labels, converging scales and the other embellishments. The first three columns essentially contain all the data related to the business. Making the basic Chart Let’s create a basic chart with five series. Adding the XY series for the dummy scales Once we’ve inserted the XY-Series the chart looks like this: Aligning the XY points to the X axis If you noticed, the points are not aligned to the X-Axis.

The Daily Graph "The Daily Graph" re-creates charts from The Economist's Graphic Detail blog using standard run-of-the-mill Excel techniques without macros. We do try to milk Excel for all it's worth and apply techniques that may not have been intended in the way we use them. In the end, it's the result that counts. "The Daily Graph" is published whenever we spot an interesting chart on The Economists's blog that looks like it cannot be done in Excel. Typically once or twice a week if we can find the time.The Daily Graph blog comes with a dowloadable version of an Excel workbook so that you can follow what we did... or you can "borrow" our work and use it for something entirely different. "The Economist" is a trademark of The Economist Newspaper Limited.

How to Create a Panel Chart in Excel To show a concise, clear summary of data for several departments or cities, you can create a panel chart in Excel. It shows all the data in a single chart, with vertical lines separating the groups. My chart shows sales for bars and cookies, in four cities, over the first 7 months of the current year. I learned this technique from Jon Peltier's website, where he also sells a Panel Chart Utility, that creates dot plot and bar panel charts. Panel Chart Steps The instructions for making a panel chart look long and complicated, and I've avoided learning this technique, because it was a daunting process. Last week, I finally took the plunge, and it's not so bad, once you get the big picture in your head. Add a separator field to the source data Summarize the data in a pivot table Copy the pivot table data as values Create a line chart from the copied data Add another series to create vertical dividing lines Add final formatting to clean up the chart Add a separator field Summarize the data

How to Find the Right Chart Type to Represent your Numeric Data 22 Feb 2016 Charts help you visualize numeric data in a graphical format but the problem is there are just too many types of charts to choose from. This diagram will help you pick the right chart for your data type. couch mode print story Charts help you visualize numeric data in a graphical format but the problem is there are just too many types of charts to choose from. If you are finding it hard to pick the right chart type for your type of data, refer to chart chooser diagram. The poster, designed by Andrew Abela, is also available as a PDF. Related: Create Graphs Online with Google Charts You may also want to check out Chart Chooser – an online tools that lets you shortlist charts visually. Comments are closed but if you want to respond, please send me an email or tweet.

Cosmograph in Excel - World migration with bilateral flow chart - E90E50fx Igor: What is this?Freddy: Schwarzwalder KirschtorteMonster (OS): MmmFreddy: Oh, do you like it? I'm not partial to desserts myself, but this is excellentIgor: Who are you talking to?Freddy: To you. You just made a yummy sound, so I thought you liked the dessertIgor: I didn't make a yummy sound. Monster (OS): Mmm. by The FrankensTeam Initially I was inspired byPeoplemovinCarlo Zapponi's chart. It is a very nice job that I wanted to reproduce in Excel ...and I created a file but did not get a satisfactory result (if you want I can send the file) ... sometimes we have to know how to lose ... or maybe we just need to change strategy. I had to fix the formulas in values (where it was possible) to lighten the file ... and probably could do much lighter and without all of that data ... but it came out so and maybe will improve it later :-) Click here to download the Excel file. See also here:Life Expectancy by Nathan Yau's chart with ExcelEdward Tufte's Slopegraphs in Excel

Build a Dynamic Chart There are many instances when one wants to create a chart that reflects a growing data set or a chart that shows only part of a data set. These are easily accomplished in Excel by creating named formulas and using these named formulas in charts. Download the zipped workbook containing the examples below Once the basic concepts illustrated below are well understood, Advanced Examples contains solutions to more complex requirements. An extension to the above ideas so that the user can specify the data to be shown using business terminology. Keywords: Named formula, dynamic formula, dynamic chart, name in chart, grow, self adjusting, auto adjust, graph partial range, select column or row, name, form control drop-down dropdown list box combo How to do a doughnut chart ? (Chandoo) Posted on February 20th, 2014 in Charts and Graphs - 23 comments Today lets take a stroll outside what Excel can do and make something fancy, fun and may be useful. Nowadays, many newspapers, websites and magazines are featuring info-graphics. An info-graphic is a collection of shiny, colorful & data-full charts (or often pieces of text.) What I am talking about is this: How to create this chart? A word of caution Before you smack your lips at the thought of stringy donuts, let me warn you. Go easy with these charts. Now that we have learned the ill-effects of donuts, let’s make & eat one, cause we are bad-ass like that. Set up data cells This is easy. In C5, write =1-C4 This will give us the balance portion of donut where thread should appear. Create a donut chart Select both cells and insert a donut chart. This is what we get. Make 2nd slice of donut transparent This is simple. Adding thread to donut – 2 ways to do it There are many ways to cook a donut. This is my favorite. What do you think?

Stacked column flow chart - Franken's Team Freddy: This is the moment. Well, dear, are you ready?Inga: Yes, Doctor.Freddy: Elevate me.Inga: Now, right here?Freddy: Yes, yes, raise the platform.Inga: Oh, the platform, oh that, yeah, yes. by The FrankensTeam I had seen a very interesting chart in an Italian daily paper. I immediately wanted to create the Excel version of it! You can see in 3 year the "platform is raised", so the stacked columns start from different point. We downloaded the usage of the top 7 mobile operating systems from 2010 to 2014 to create a chart with more data series. Here is the "platform raise" version: ...and the 0-baseline version: In our file the data table could be found on Data sheet, below the table you can set the "platform raise" numbers so, the chart will be updated accordingly: Source of the data: How does it work? The chart is built up to be able to work dynamically with a 8x8 data table, so if you add new years, those will immediately appear on the chart.

How to Create Funnel Charts in Excel Reporting Strategies (Download the workbook.) Although “funnels” are a common term in selling, the concept also can apply to other business activities. The general idea is that an organization begins with a large number of something and then goes through a series of stages to winnow that number down to a smaller number. Usually, the goal is to make the small number as large a percentage of the large number as possible. In sales for example, the large number could be all possible prospects and the small number could be those who buy. Funnels also can apply to challenges or problems. The chart above illustrates the general idea. If the chart represents a sales funnel, we would want Stage 4 to be a large percentage. But if the chart represents an email funnel, we would want Stage 4 to be a small percentage. This figure combines several charting techniques that are easy to set up but are seldom used in Excel. Set Up the Funnel Data This figure shows the data for the chart. Select the range B2:C2.