Posted on 03 April 2009
Data Validation in Excel is a feature that allows you to control or restrict the type of data that you can enter in to a cell in a workbook. When used properly, data validation can help you prevent users from entering invalid values in an excel workbook. This reduces potential errors and can save you a lot of time.
You can download an
excel workbook with various examples of data validation in excel using simple, named lists and dynamic named ...
Continue Reading
Posted on 02 April 2009
You can add a custom menu to the excel toolbar using a simple VBA code. All you need to achieve this is to get a handle on one of the already existing menu items in the excel menubar. Once you have the handle, you can write a simple instruction for excel to add a custom menu item right before or after that specific item in the menu bar. Once that is done, it is just a simple step from ...
Continue Reading
Posted on 01 April 2009
Although the reverse is easy, export of an excel worksheet as a fixed width text file (.txt) file is sometimes is not. Now, Excel does provide you with the option to save a worksheet as a text (.txt) or a Comma Separated (.csv) file, but if you had to ensure that each of the fields have their own specified lengths, this job is far from easy.
I recently came across a ...
Continue Reading
Posted on 01 April 2009
The OFFSET function in Excel returns a reference to a range that is offset by a specific number of rows and columns from another range or cell.
Before we begin, you can download the workbook containing a few examples of the
offset function in excel here.
The syntax for the Offset function in excel is: Offset( range, rows, columns, height, width )
range is the starting range from which the offset will be applied. Often it is a single cell but can ...
Continue Reading
Posted on 31 March 2009
Creating a chart that uses a named range is excel can be accomplished by following these basic steps. Before we begin, let us first look at the building blocks.
What is a named range?
A named range is a group of cells that have explicitly been given a name. Every time you refer to that name, the workbook automatically knows that the user is referring a collection of cells that name represents. You can assign ...
Continue Reading
Posted on 30 March 2009
A mortgage calculator made using excel can come in handy when you need to do quick analysis of how a mortgage fits in with your overall finances. Over the weekend, I designed a
mortgage calculator in excel that calculates how much you need to pay for your mortgage.
The nice thing about this spreadsheet based calculator is that all you have to do is to plug in the loan amount, the ...
Continue Reading
Posted on 28 March 2009
All too often we let old calender entries and meeting requests accumulate in outlook resulting in the calender folder gradually bloating out of shape (and receiving the dreaded "your mailbox is over its size limit" message from the server)
How do you solve the problem of deleting older calendar entries and meeting requests in outlook - well here's how:
Go to your outlook ...
Continue Reading
Posted on 24 March 2009
Did you know that there are many other ways in which you can add comments to your excel spreadsheet. Here are a couple of ways:-
Fill Effects
You can insert a comment by simply using the Right Click -> ‘Insert Comment’ options. You can then double click on the comment’s border and click on ‘Color and Lines’ tab in the pop-up. In the drop-down for the ‘Colors’ section, ‘choose Fill Effects’. Now select ...
Continue Reading
Posted on 22 March 2009
Conditional Formatting is one of the most useful features of Microsoft Excel. And while most people are happy to use it for changing the color of the text in a particular cell, there are endless ways in which it can be extended. Here are a few:
Hide errors
Remember when you had a copy a complicated formula onto 40,000 cells and some of them gave you the dreaded #DIV! error. That did really ...
Continue Reading
Posted on 08 March 2009
The following code will refresh all pivot tables in an excel workbook or a worksheet. There are two ways to achieve this:
Refresh all pivots in Workbook - Method 1
Sub try1()
For Each pt In ActiveWorkbook.PivotCaches
pt.Refresh
Next pt
End Sub
Refresh all pivots in Workbook - Method 1
Sub try2()...
Continue Reading
Posted on 03 January 2009
muyinteresante published this delightful map of progress of various branches of science and the personalities that helped them evolve. It took me a while to study the entire thing but I did end up with a pleasant sense of completeness even though the various 'scientists' (I use that term loosely to include philosophers, astronomy, biology and others) may have lived in periods separated by many years or even centuries.
My guess ...
Continue Reading
Posted on 01 January 2009
NY Times captured these stunning images of the ongoing conflict in Gaza.
Israeli soldiers clean the barrel of a tank near Israel's border with the Gaza Strip.
Cars buried under rubble at the site of the ministry buildings compound. Israeli aircraft bombed a government compound, buildings linked to the Islamic University and the home of a top Hamas commander
Continue Reading
Posted on 19 December 2008
The year 2008 was an extraordinary year by any standard. From the stock markets hitting record highsand then melting down in what was described as a once in a century event, from China showing its prowess during Bejing Olymipics, from war in Iraq and Afganistan to democracy being restored in other parts of the world, from the gyrations in oil prices to focus on global warming, the world we it all ...
Continue Reading
Posted on 29 November 2008
"Do not come up, I will handle them", these were probably the last words which Major Sandeep Unnikrishnan told his men as he was hit by bullets while engaging terrorists inside the Taj. He and fellow NSG commando Havildar Gajender Singh lost their lives as did Hemant Karkare, the celebrated head of the Maharashtra Anti-Terrorism Squad, while fighting the terrrorists. I feel so sad for their families and may God give them the strength to bear the loss.
India has ...
Continue Reading
Posted on 23 November 2008
The key to increasing speed of calculation and improving performance of your excel worksheet and VBA code lies in understanding how Excel calculates (and re-calculates) each cell and resolves dependencies. Whenever an event occurs that causes Excel to recalculate (for example a cell is changed, a VBA code calls for re calculation or anything else), the entire calculation process is carried out roughly as follows:
1. Load workbook in memory when the workbook is first opened.
2. Track all dependencies and build ...
Continue Reading
Posted on 22 November 2008
In one of my previous posts (
How to read data from Pivot Tables using VBA), I talked about using the getpivotdata() function in VBA to read values from a Pivot Table. In this article, we are going to look at building an alternative to pivot tables. Let's begin.
Pivot tables are an immense help while working with data sets – they sum up the data into a small, neatly organized space. You can pick and choose the various values from ...
Continue Reading
Posted on 11 November 2008
One exercise that is done with almost religious fervor in all companies at the beginning of each financial year is the preparation of the budgets and targets for the next year. Every year a fresh faced newly recruited manager is identified, handled this dreaded responsibility and asked to present the first draft to the management team. And so the rookie spends a couple of nights pouring over multiple spreadsheets and a plethora of for complicated formulae and comes out with, ...
Continue Reading
Posted on 09 November 2008
Getting the Flex command line compilers can be a bit of a trick. I recently started working with Flex and it took me a little time to get the configuration right.
The 5 step process to get the Flex compiler to work in Windows:
1. Download Flex Builder from
Adobe
2. Download Java SDK from
Sun
3. Install both on your computer
4. Go to the Flex Builder's SDK bin folder and ...
Continue Reading
