Posted on 02 November 2009
It's monday morning and you've got a pivot table on your hands - its source data consisting of three columns – product,
date and sales volume over a preiod of time.
A pivot table created using this as underlying data may typically have the date running across rows, the product category as the column or page field and finally the sum of sales appearing in the data field in ...
Continue Reading
Posted on 30 October 2009
I reckon that today would be the last working day before Halloween and it might be your only chance to get even. So here are a few Excel tricks especially for you this Halloween - each one getting progressively meaner.
Send somebody a Workbook that just doesn't Scroll the Right Way
This one is for your super boss (I mean boss's boss). Now wish as you may, you really can't do too many bad things to him, right - so this one's ...
Continue Reading
Posted on 28 October 2009
A scatter chart helps to plot data with two or more variables. One great advantage that a scatter chart has is the ability to show a relatively large number of data points in a small area. However, as the number of points plotted within the same space increases, it sometimes becomes difficult to distinguish individual data sub-sets.
One quick solution to group all the points within the desired data sub-set is ...
Continue Reading
Posted on 25 October 2009
In
Applying Offset Labels to Chart we looked at how a chart's labels can be offset to avoid cluttering. But what if you wanted to use the chart to narrate a story - and a pretty long one at that? How would you do it? If you turn on the chart axis labels, the entire thing gets messed up. Roots growing out of the chart axis wasn't exactly the point that we would like to highlight to someone who's ...
Continue Reading
Posted on 22 October 2009
Nearly everybody I know uses Excel for tracking the returns in the stock market. Not many however know that Excel can be used for online stock tracking and getting live price information from stock exchanges like NYSE and Nasdaq as well. Here's how.
Establishing a connection to the MSN server for Stock Quotes
Click on the data tab in the Excel ribbon (in Excel 2007 and later). In Excel 2003 and earlier, ...
Continue Reading
Posted on 16 October 2009
As an analyst, I often need to keep track of the change in position of various players in the industry. More often than not, the position change needs to be tracked between two specific points in time - say quarter 1 vs. quarter 2 or perhaps year 1 vs. year 2. In this article we take a look at a few alternative approaches to plotting position changes and find out which one serves us best. For this example let's take ...
Continue Reading
Posted on 09 October 2009
Yesterday's Google doodle was a barcode and that got me curious. So here are some free barcode fonts in Excel and a few interesting tidbits about them.
Barcode History
Barcodes were developed in 1948 by Bernard Silver and were patented in 1952. While the linear pattern of the barcode is prevalent today, during its inception, bulls-eye patterns were also experimented with. However due to the problems encountered while printing of the later, the liner version caught on. The first ever purchase using ...
Continue Reading
Posted on 08 October 2009
A drop down list in Excel allows users to pick a value from a restricted set. By providing user a choice and yet preventing impermissible values, drop down lists help make your spreadsheets interactive and foolproof. While there are more than 1 way to make them, the most commonly used method is using data validation. You can create both static and dynamic drop down lists in Excel - the later re-sizing themselves as newer values are added or existing ones ...
Continue Reading
Posted on 07 October 2009
Kaiser (
Junkcharts) posted this line chart sometime back. A recreated chart is shown below where labels have been added to last point of each series in the chart. If you noticed, the labels have also been enhanced to show both the series name and the value of the data point. Lastly, the Y Axis of the chart has been offsetted to crossover at the last category.
The benefits are obvious - the ...
Continue Reading
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
