Posted on 16 November 2009
So the poor little manager looks at his computer and says, "how to heck am I supposed to separate this data down to individual sheets for each department." The manager in question had just been handed over a large file containing financial information for all the departments in his organization and was asked to break it down so that each department's data should end up in a separate sheet.
The manager ...
Continue Reading
Posted on 12 November 2009
When you create a
pivot table in Excel 2007, the pivot table field headers get inserted automatically. The headers make the job of slicing the data easier but they also have an unintended side effect - they make the first column to become much wider than the rest.
One way out is to adjust the width of the first column to make it match the other columns and then disable ...
Continue Reading
Posted on 11 November 2009
Pivot Tables underwent a bit of a change in Excel 2007. Our reader Miron commented on the Pivot Table section of my review of Excel 2007 saying, "Excel 2007 ruined a really good tool." Excel 2007 did shake up a lot of things that we were comfortable with - including charts and pivot tables which were probably some of the most frequently used features. But not all have been misses and I think changes have made the pivot tables ...
Continue Reading
Posted on 10 November 2009
I saw this memory game a few days back. The website promised an instant boost in the players memory if they played this game for a 4 week period. So if you've been wondering how to get that 'excel'-ent memory, here's the prefect game for you.
The Memory Game
The game is pretty straightforward. You are shown a grid of cells, some of which are colored and some are left blank. Once you've memorized the pattern of the colored cells, the ...
Continue Reading
Posted on 09 November 2009
Of all the mysteries in the world, one of the most profound is - why do women shop. A man can probably go through most of his adult career with a pair of conservatively stitched suits and matching ties. My old man still takes great pride in a dark, striped one that he got stitched when he had just been commissioned into the forces. I remember the time when he even had a slightly worn one handed over to me ...
Continue Reading
Posted on 05 November 2009
OLAP Cubes provide a convenient way to crunch data in Excel. Imagine a Pivot Table pulling information from thousands or millions of data points with hundreds of columns and you being able to share this humongous data set with colleagues using an Excel spreadsheet that weights nothing more than a few Kilobytes. Add to this the ability to update the source data while the users get to look at that information real time. That’s what a Pivot Table based on ...
Continue Reading
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
