Posted on 13 December 2009
One axis charts combined with the histogram are a great way to summarize unidimensional data. Excel does not provide provide in-built support for one axis frequency distribution charts but they can be created without breaking into a sweat.
The Histogram
The histogram trades off detail for ease of comprehension. Let's look at the chart below.
This one's based on a set of 250 data points with ...
Continue Reading
Posted on 11 December 2009
When raw data (in text *txt or csv format) is imported to excel, the conversion can sometimes have an unintended side-effect - the numbers come out as text after the process. The only choice left is to identify the columns that contain numbers masquerading as text and then to use the
text to column option on each of the columns one-by-one. If the number of columns is large, eyeballing the data can be a bit of a strain.
Here a ...
Continue Reading
Posted on 10 December 2009
Always wanted to make a stacked bar graph but were afraid connoisseurs would smirk. Let me present - the stacked bar graph with helper bars - the latest addition to the charting fraternity. (Drum rolls)
In case you were wondering how this one got made, here are the steps:
Set Up the Data for the Bar Graph
In this example, we pick up ...
Continue Reading
Posted on 09 December 2009
Pareto charts are one of the basic pillars of the six sigma methodology and are typically used to depict the frequency of occurrence of issues that affect 'production'. Although one usually sees them being used as standalones, the humble pareto chart can very well be augmented in order to make it a much more meaningful tool for tracking and resolving pending issues & errors. Let's look at one such approach that utilizes a combination of a horizontal pareto chart ...
Continue Reading
Posted on 03 December 2009
Here are some quick keyboard shortcuts in Excel to help you survive in the
pivot table jungle.
Keyboard shortcut to Sort Fields and Items in a Pivot Table
Thought that you would have to drag the field or the item across the pivot table using the mouse. Hold on - you can use the keyboard too.
Simply type the field or the item ...
Continue Reading
Posted on 01 December 2009
A timeline chart allows the reader to understand the chain of events as they take place over a period of time. The events are typically represented as markers along a horizontal or a vertical line.
In our previous post on adding
interactive labels to chart, we saw how to fit a large number of event descriptions in a single chart. Today we look at a similar approach in order to ...
Continue Reading
Posted on 27 November 2009
The remove background tool is one of the new features introduced in Office 2010. Ever since I downloaded the public beta, I've been itching to take it for a test drive and this weekend, I did just that. I tried my hands at two images - one with high and the other with low contrast. So let's look at the results
Remove Background from a High Contrast Image
We picked up an ...
Continue Reading
Posted on 24 November 2009
Data Validation feature in Excel prevents invalid entries from being entered into a cell in a sheet. Data validation can be set up to work with numbers, text string, date / time or customized formula so that we can restrict the set of values that can be entered in a cell and also prevent wrong entries being made at the time of data entry. Data validation can also be used to create drop down lists so that the user ...
Continue Reading
Posted on 24 November 2009
My first impression of
Excel 2010 was that it 'felt' faster than Excel 2007. So one of the first things that I did was to take it for a test ride. Now, I must admit, testing isn't my forte. A long long time back when I was a developer, I used to write horrible code. I had a fancy for declaring variables like i1, i11, i111 (and so on and so forth) and seldom wrote comments that could explain ...
Continue Reading
Posted on 23 November 2009
Here is some VBA code that can help you automate working with a
slicer in Excel. Slicers, as you would know, are the latest introduction to Excel. As I mentioned in my previous post, they can also be programattically controlled using simple VBA code. Let's see how.
Slicer Creation using VBA - Basic Steps
Let's look at the slicer hierarchy - a workbook has slicer caches (accessed the SlicerCaches object), each slicer cache in turn having multiple slicers (one for each ...
Continue Reading
Posted on 20 November 2009
Slicer
What is a Slicer? One can think of a slicer as an extension of a
pivot table which makes the job of filtering the pivot table data easier. One shortcoming that pivot tables earlier had was that you could not control them remotely. Short of VBA, you could do things with pivot tables only when you had one in front of you. A slicer on the other hand acts as a 'remote control' for the pivot table. You can ...
Continue Reading
Posted on 19 November 2009
Excel 2010 introduces a new feature called sparklines. Sparklines are essentially miniature charts showing only the plot and pretty much nothing else (though you can turn on chart axis if you want). The advantages are obvious. In the same amount of space that it to present a normal excel chart with a handful of data points, you can pack a dozen or more sparkline charts. If used intelligently, the data loss due to absence of reference lines and ...
Continue Reading
Posted on 18 November 2009
Excel 2010 beta is out today. For those who read my review of Excel 2007, I mentioned that the chances of my being able to try out
Excel 2010 anytime soon weren’t very high. Excel 2010, I thought at that time, and considering how Excel 2007 turned out, would simply be too big a resource hog to work with the hardware that my pc runs on. In that case I would have to give my faithful old pc ...
Continue Reading
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
