Data Forms in Excel

Although not used frequently, excel data forms can be a useful tool to work your way through a medium size data set. Imagine your boss just handed over a set of invoices and asked you to type them onto a worksheet. Now life would have been a lot simpler had he given you say a dozen invoices but as Murphy said, anything that can go ...

Continue Reading

Data Forms in Excel

Excel DATE Function – How to use Excel DATE Function

Posted on 06 April 2009

Excel DATE Function - How to use Excel DATE Function
Syntax of Excel DATE Function Example of Excel DATE Function Possible Errors returned by the DATE Function The Excel Date function returns the number representing the date for a given day, month and year. In Excel DATE function is typically used to construct a date by providing the constituting parts such as day, month and year. The minimum and maximum values that a date can take in Office 2003 is January 1, 1900 and December 31, 9999. Excel DATE ...
Continue Reading

Comments (2)

Top 10 Tips to Increase Blog Views and Traffic

Posted on 04 April 2009

Top 10 Tips to Increase Blog Views and Traffic
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

Comments (1)

Data Validation in Excel

Posted on 03 April 2009

Data Validation in Excel
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

Comments (9)

Add Custom Menu to Excel Toolbar

Posted on 02 April 2009

Add Custom Menu to Excel Toolbar
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

Comments     

Export Excel as Fixed Width Text File Format using VBA

Posted on 01 April 2009

Export Excel as Fixed Width Text File Format using VBA
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

Comments (52)

Excel Offset Function

Posted on 01 April 2009

Excel Offset Function
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

Comments (3)

Create Chart using Named Range in Excel

Posted on 31 March 2009

Create Chart using Named Range in Excel
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

Comments (8)

Mortgage Calculator in Excel

Posted on 30 March 2009

Mortgage Calculator in Excel
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

Comments (2)

Delete Old Calendar Entries and Meeting Requests from Outlook

Posted on 28 March 2009

Delete Old Calendar Entries and Meeting Requests from Outlook
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

Comments     

EMI Chart Calculator Template in Excel

Posted on 28 March 2009

EMI Chart Calculator Template in Excel
Considering how simple and useful an EMI chart calculator can be, I developed this EMI Chart calculator in Excel. EMI chart is now available in two different formats - EMI Chart in PDF format and EMI Chart in Excel You can also download the EMI chart calculator template in excel here For the uninitiated, EMI (also known as Equated Monthly Installment) commonly refers to a fixed monthly installment that ...
Continue Reading

Comments     

How to make your comments in Excel more useful

Posted on 24 March 2009

How to make your comments in Excel more useful
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

Comments     

Conditional Formatting In Excel

Posted on 22 March 2009

Conditional Formatting In Excel
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

Comments     

Refresh All Pivots in a Workbook or Worksheet

Posted on 08 March 2009

Refresh All Pivots in a Workbook or Worksheet
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

Comments     

Cricketing Heat Map

Posted on 22 February 2009

Cricketing Heat Map
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

Comments (5)



Translate

Chinese (Simplified)DutchEnglishFilipinoFrenchGermanHebrewHindiItalianJapanesePortugueseRussianSpanish

treeemap software for excel