Chart Label Trick : Applying Descriptive Labels to Chart Series

Chart labels provide information related to the underlying data. In Excel, by default, chart labels can only consist of the following three - Series Name, Category Name and Chart X & Y Values. So what about situations when you want to convey additional information - for example, say a short description about the chain of events that had a material impact on ...

Continue Reading

Chart Label Trick : Applying Descriptive Labels to Chart Series

Calculated field and Calculated Items in a Pivot Table

Posted on 11 September 2009

Calculated field and Calculated Items in a Pivot Table
Calculated field and Calculated Items of a pivot table get their values from the result of a formula. Calculated Field A calculated field allows the user to insert a new data field into the pivot table – one which does not exist in the base data but gets its value from a formula. The formula in turn can include existing fields, numbers and other arithmetical operators. Let’s understand this a little better. Whenever you create a pivot table, by default, the list ...
Continue Reading

Comments (9)

Custom Format in Excel – How to Format Date and Time

Posted on 07 September 2009

Custom Format in Excel - How to Format Date and Time
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 ...
Continue Reading

Comments (2)

Bar Chart with an Average Line for Each Group in Chart

Posted on 04 September 2009

Bar Chart with an Average Line for Each Group in Chart
In our previous article we covered adding an average line to a bar chart. At times, however, it may be useful to show an average line for each period or group when the chart consists of more than one such period or group. The advantage here is apparent - not only can the reader view the data points individually, he/she is also able to build a map of how various groups are placed with in relation to each other....
Continue Reading

Comments     

Interactive Chart in VBA using Mouse Move Event

Posted on 02 September 2009

Interactive Chart in VBA using Mouse Move Event
VBA can be used to make interactive charts that respond to mouse movements. In this article we first start of by making a simple bar chart embedded in a chart sheet and then use the mouse move event of the chart sheet to make it interactive. Create a simple Bar Chart For the purpose of this exercise, we take the list of top 15 billionaires and sort it based on their networth. We ...
Continue Reading

Comments (21)

VBA Read List and Combine All Excel CSV Text files in a Folder or Path

Posted on 29 August 2009

VBA Read List and Combine All Excel CSV Text files in a Folder or Path
Here's the VBA code to read, list and combine all excel, csv, text any other format file in to a single Excel sheet. By editing the code, you can make the program read and then combine files of only a specified extension say, Excel (.xls) or comma separated (.csv) or text (.txt). If you have a lot of small files getting FTP'd over or getting accumulated in a location, this can save a lot of time. [cc lang="vb"] Sub List_All_The_Files_Within_Path() Dim File_Path As ...
Continue Reading

Comments (7)

Custom Format in Excel – How to Format Numbers and Text

Posted on 27 August 2009

Custom Format in Excel - How to Format Numbers and Text
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 (19)

INDEX Formula Excel – How to use Excel INDEX Function

Posted on 26 August 2009

INDEX Formula Excel - How to use Excel INDEX Function
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 (2)

VBA code for Text to Column

Posted on 26 August 2009

VBA code for Text to Column
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 (9)

Bar Chart with Average Line

Posted on 24 August 2009

Bar Chart with Average Line
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 (7)

Excel Color Palette and Color Index change using VBA

Posted on 20 August 2009

Excel Color Palette and Color Index change using VBA
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 (19)

Excel Dashboard using Pivot Table

Posted on 13 August 2009

Excel Dashboard using Pivot Table
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 (6)

VBA Message Box (msgbox) – The Message Can Do Better

Posted on 12 August 2009

VBA Message Box (msgbox) - The Message Can Do Better
The VBA message box (msgbox) helps convey a pre-defined messages to a user. Developers often use the message box for stuff ranging from debugging vba (msgbox "I am here" types) to prompting users for action (msgbox ("Sorry, what you asked for could not be done", vbAbortRetryIgnore)). While it remains one of the most useful and frequently used commands in vba, it is also common to find developers being stuck with the most primitive form of the message box and losing ...
Continue Reading

Comments (10)

Excel Slow to Respond – Avoiding Mistakes that Make Excel Slow Down to a Crawl

Posted on 06 August 2009

Excel Slow to Respond - Avoiding Mistakes that Make Excel Slow Down to a Crawl
Everyone hates it when excel becomes slow to respond. So here are some tips to avoid doing things that can slow down excel. Excel 2007 and later versions sport a much bigger grid which tends to amplify poor spreadsheet design. An Excel spreadsheet that seemed a bit slow in Excel 2000 would likely get bogged down to a crawl in Excel 2007 if you, like me, are using the same hardware. The key to understanding what makes excel slow lies ...
Continue Reading

Comments (9)

Case Shiller Home Price Index and the Base Effect

Posted on 31 July 2009

Case Shiller Home Price Index and the Base Effect
The Case Shiller Home Price Indices are constant quality house price indices for the United States. A number of Case Shiller home price indices exist: A national home price index, a 20-city composite index, a 10-city composite index, and twenty individual metro area indices. These indices were developed by economists Karl Case and Robert Shiller. (wiki) The Case Shiller Index has been one of the most keenly watched indices over the past few months. It has been argued (and perhaps ...
Continue Reading

Comments     

String Comparison Function in VBA

Posted on 30 July 2009

String Comparison Function in VBA
Here's a look at a few string comparison functions in VBA : Types of string comparisons in VBA Binary String Comparison (Case sensitive) in VBA For any formula If you want to compare two string in such a manner that each individual characters is compared with its counterpart in a case sensitive manner (Ex. “This” is not equal to “this” because ‘T’ is not equal to ‘t’), you can do either of the two things: 1. Declare the statement Option Compare Binary at ...
Continue Reading

Comments (3)

How to write a macro in Excel

Posted on 29 July 2009

How to write a macro in Excel
How to write a macro in Excel? Here are a few quick tips: Excel Macro - How to Record Excel Macro - How to Edit Excel Macro - Improving speed of code Excel Macro - Examples Recording a macro in Excel How to record a macro in Excel 2007 and Excel 2010 In Excel 2007 (and Excel 2010), the "Record Marco" menu option is under the "View" ribbon. Click on the "Macros" button towards ...
Continue Reading

Comments (7)

Excel 2007 – Review Continues

Posted on 28 July 2009

Excel 2007 - Review Continues
Excel 2007 was, for me, the discovery of the month. This article continues the review that I started in the previous post. Though this post should have been written about two years back, inspiration only struck me this weekend. Named Range in Excel 2007 The named range is a definite improvement over 2003. A lot of features have again been bought forth in this version (or added anew) such as scope definition and being able to view the values (a great ...
Continue Reading

Comments (4)

Excel 2007

Posted on 25 July 2009

Excel 2007
Excel 2007 finally happened to me. Its not that I've never though of moving to Excel 2007 from the 2003 avatar of Excel, but you see, yours truly has never been accused of being an early adopter in the past, no sir never, and I've got a duty to protect that reputation. I bought my first phone after it had been on the market for a few years, bought my first car after it had been on road for a ...
Continue Reading

Comments (10)

Subscribe

Keep up with the latest stories - Delivered right to your inbox
feedburner

Translate

English flagItalian flagKorean flagChinese (Simplified) flagPortuguese flagGerman flagFrench flagSpanish flagJapanese flagArabic flagRussian flagDutch flagHindi flagFilipino flagIndonesian flagThai flagTurkish flagPersian flag
treeemap software for excel


ARCHIVES