Named Range - A Few Quickies

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 ... Continue Reading

Comments (8)

So How Many Of These Excel Shortcuts Do You Know, Punk ?

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 ... ... OR ... 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 ... Continue Reading

Comments (7)

An Excel Chart’s Journey Across Versions

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. ... Continue Reading

Comments (2)

Data Validation List in Excel - Create Drop Down List Using Data Validation in Excel

Posted on 25 December 2009

Data Validation 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 ... Continue Reading

Comments (3)

Data Validation in Excel - How to Use Data Validation Feature in Excel

Posted on 24 December 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

Comments (1)

Custom Format to Catch Numbers Appearing as Text

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

Comments (3)