Custom Format in Excel - How to Format Numbers and Text

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

Comments (10)

INDEX Formula Excel - How to use Excel INDEX Function

Posted on 26 August 2009

Syntax of INDEX Formula Example of INDEX Formula Possible Errors returned by the INDEX Formula INDEX formula in Excel returns the cell at the intersection of a particular row and column within a range. The INDEX formula is typically used to locate a value in a given range by specifying its X-Y coordinates (i.e. the row and the column) within the range. INDEX Formula Syntax INDEX Formula has four parts: INDEX (range_to_search_in, row_position, column_position, range_number) ... Continue Reading

Comments     

VBA code for Text to Column

Posted on 26 August 2009

Here is a basic VBA code for converting Text to Column that works across multiple columns in an Excel workbook. You can edit this code to turn on (or off) various options such as DataType (xlFixedWidth or xlDelimited), TextQualifier and which delimiters to use while converting from text to column. [cc lang="vb"] Sub text_to_column() Application.ScreenUpdating = False On Error Resume Next For Each wksht In ActiveWorkbook.Worksheets For Each col In wksht.Columns Columns(col.Column).TextToColumns _ ... Continue Reading

Comments (2)

Bar Chart with Average Line

Posted on 24 August 2009

A Bar Chart is used to represent data using horizontal bars. One way in which you can augment a bar chart is to add an average line. Create a bar chart Create a bar chart in Excel using any particular data set at your disposal. In our case, we took the Forbes list of the richest people on earth (2009 figures). Here's how the data looks like. To create a bar chart, select ... Continue Reading

Comments (2)

Excel Color Palette and Color Index change using VBA

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

Comments (5)

Excel Dashboard using Pivot Table

Posted on 13 August 2009

Excel Dashboards created using a pivot table have the advantage of being low maintenance. The excel dashboard created using this method will typically have three layers - the raw data, the pivot table and the final dashboard presentation. This loosely follows the Model–View–Controller pattern found in software programming with the pivot table acting as the controller. Creating an Excel Dashboard using pivot tables As we mentioned earlier, to create a complete dashboard, the following three pieces need to be put together. ... Continue Reading

Comments (3)