Posted on 04 April 2009
If you have been wondering how to increase blog views for your blog, the following tips can come in handy. Mind you, the two most important factors to increase your blog views are - great content and large number of good quality links from other blogs to yours. If you can achieve both of these, rest should be easy to manage.
Increase blog views by writing good content on your blog
As ...
Continue Reading
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 22 February 2009
Heat maps are pretty versatile. They have been used for everything ranging from charting home prices to changes in the stock market.
(Average Listing Price for US states, week ending Feb 17, 2010. Source : www.trulia.com)
(SNP performance, Feb 19, 2010. Source : www.finviz.com/map.ashx)
Inspired by the last ball win over the South Africans yesterday night, I wondered if some of that action could be captured in a graph. ...
Continue Reading