Posted on 21 January 2010
Named Ranges are probably one of the most useful features in Excel. Named ranges can add interactivity, make long formulas shorter and and if used properly, generally provide a clean mechanism to share information across the workbook. I remember being mighty impressed with Peter Rakos
3D rotation model last year and spent a good amount of time trying to understand it. The VBA code is only a few lines with the major work being taken up by the ...
Posted on 19 January 2010
So you thought you knew every goddam' Excel shortcut out there? Thought that you were the quickest draw around the office block. Not so fast sunny boy ... not so fast ! You've just ventured into the mean and vicious badlands of the Bison.
You have two choices - stop reading this here ...
... stay back and take up the challenge ....
So before I have your ego blown off, I will give you 7 chances to salvage it.
Excel Shortcut ...
Posted on 10 January 2010
Sometime back I wrote a post on making control charts using Excel
. The chart was initially created in Excel 2003. The chart consisted of the plot values, the average line and upper & lower control limits. While the main data series was a line chart, the average, upper and lower control limits were plotted as dots of an X-Y and then were extended into lines using the horizontal error bars. Shown below is a sample.
Posted on 25 December 2009
feature in Excel allows the user to create a drop down list in Excel. The drop down list created using data validation allows the user to pick and choose a single value from the entire list and thus prevents entry of invalid values. The drop down list can be created by typing in a set of values, using a range of cells or by writing a formula in the data validation option box.
Create a Drop Down List ...
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 ...
Posted on 03 December 2009
Here are some quick keyboard shortcuts in Excel to help you survive in the pivot table
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 ...
Posted on 24 November 2009
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 ...
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 ...
Posted on 20 November 2009
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 ...
Posted on 19 November 2009
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 ...
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 ...
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 ...
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.
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 ...
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 ...
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.
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
Custom Text Symbol is the customized symbol that you would like to appear against each number. So if ...
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 ...
Posted on 27 August 2009
The custom format feature in Excel allows the user to “paint” or mask a number, text, date or time value in a user defined custom format. Before we get into the details, it is important to remember that when you format a cell using this feature, it does not change the value present in the cell. The only change is of how the value appears to the user on the screen.
A custom format can be applied to a particular cell ...
Posted on 20 August 2009
Excel Color Palette has an index of 56 colors which can be modified using VBA. Each color in the palette is associated with a unique value in the index that can be changed programatically. At times it is useful to know the relative positioning of the various colors within this index as well as how various versions of Excel treat colors. This is the first in the series of articles that will expore this further.
How to generate Excel Color Index ...