Posted on 11 September 2009
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
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 ...
Continue Reading
Posted on 04 September 2009
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
Posted on 02 September 2009
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
Posted on 29 August 2009
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
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
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
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
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
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
Posted on 12 August 2009
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
Posted on 06 August 2009
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
Posted on 31 July 2009
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
Posted on 30 July 2009
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
Posted on 28 July 2009
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
Posted on 25 July 2009
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
