Posted on 02 October 2009
IMF's world economic outlook report bought forth some interesting charts. The charts in question depicted the probability associated with future data points and highlighting the zones of uncertainty associated with each estimate. You can access the entire report
here.
Creating a Chart with Probability Bands around the chart data
Often times there is a bit of uncertainty associated with data points. "Confidence level" is a term often used by statisticians to ...
Continue Reading
Posted on 01 October 2009
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 ...
Continue Reading
Posted on 30 September 2009
Custom Format can be used to format numbers, text,
dates and time. The technique can be extended to charts as well where the custom format feature can be used to color the chart axis and chart labels.
In this example, let's say we wanted to show the sales numbers for a particular duration as a chart. We would, in addition, like to have some kind of separation between what is considered below, average and above average performance. Custom format ...
Continue Reading
Posted on 24 September 2009
In
Chart Label Trick - Descriptive Labels we talked about adding custom labels to chart. In this article we discuss offsetting chart labels - which means positioning the labels above or below the actual data points at a predetermined distance.
Getting the chart data in place
Like always, we first get the chart data in place. For the purpose of this chart, we will use two data series - the first ...
Continue Reading
Posted on 23 September 2009
This two part article consists of:
Part A -
Converting HTML to Excel using Javascript
Part B -
Exporting Excel to HTML using VBA
HTML to Excel using javascript
Let’s first take a look at tables in html. Data tables are typically stored in HTML using the <table> tag. Such a table will typically consist of one or more rows (<tr> tag) with each row having one or more cells (<td> tag). To read a table from an HTML document ...
Continue Reading
Posted on 21 September 2009
Here's a bit of VBA code to convert
Text to Column which will work across multiple columns selected together at one go.
...
Continue Reading
Posted on 18 September 2009
The labels used with the chart category axis are probably the most ignored part of the entire chart making process. You see, the labels on the value axis can be modified, the bars, the lines and the plot area - all of them can be embellished - colors added, widths adjusted,
descriptions applied - the works you know ! Even the poor little gridlines can afford a makeover. But what about the chart's category axis label - what do ...
Continue Reading
Posted on 16 September 2009
Conditional formatting (in pre Excel 2007 versions) had a limitation of only allowing for upto three conditions for formatting cells. A quick way to include more than three conditions when using conditional formatting is to use the
custom format feature in tandem with it. Conditional formatting can be used to specify the first three conditions and then upto three additional conditions can be specified using custom format - giving us 6 different colors to work with.
...
Continue Reading
Posted on 15 September 2009
Chart table - as the name suggests - is a beautiful fusion of a chart and a table giving rise to a visually appealing yet information rich presentation of data. Not that the concept itself is anything new - experts have openly and vociferously championed the cause of the chart table, its various facets have been discussed at length in numerous forums and the latest versions of excel come equipped with tools that further aid their creation (namely sparklines). However ...
Continue Reading
Posted on 13 September 2009
In
Custom Format Numbers and Text we discussed using the custom format feature on numbers and text. Here is a neat little trick using which we can format a cell to make the text symbol align left and the numbers present in the cell right.
Hold your breath..here comes...
"Custom Text Symbol"* General
where:
Custom Text Symbol is the customized symbol that you would like to appear against each number. So if ...
Continue Reading
Posted on 12 September 2009
Chart labels provide information related to the underlying data. In Excel, by default, chart labels can only consist of the following three - Series Name, Category Name and Chart X & Y Values.
So what about situations when you want to convey additional information - for example, say a short description about the chain of events that had a material impact on sales (or say when you ...
Continue Reading
Posted on 11 September 2009
Calculated field and Calculated Items of a pivot table get their values from the result of a formula.
Calculated Field
A calculated field allows the user to insert a new data field into the pivot table – one which does not exist in the base data but gets its value from a formula. The formula in turn can include existing fields, numbers and other arithmetical operators. Let’s understand this a little better. Whenever you create a pivot table, by default, the list ...
Continue Reading
Posted on 07 September 2009
In
custom format in Excel we discussed using the custom format feature to format text and numbers. In this article, we format date and time values and see how we can make them appear more meaningful. If you haven't had a chance to read through the first part where we talked about basics of the custom format feature, it is highly recommended that you do so before coming back and finishing this one off.
Before we begin, again please bear ...
Continue Reading
