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

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 (1)

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 (70)

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 (4)

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 (14)

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)

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 (1)

How to speed up calculation and Improve performance of Excel and VBA

Posted on 23 November 2008

How to speed up calculation and Improve performance of Excel and VBA
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

Comments (18)

Alternative to Pivot Table in Excel

Posted on 22 November 2008

Alternative to Pivot Table in Excel
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

Comments (5)

Adding Forms Scrollbars to your Excel Charts and Spreadsheets

Posted on 11 November 2008

Adding Forms Scrollbars to your Excel Charts and Spreadsheets
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

Comments (1)

Simulating Multithreading in VBA using Excel

Posted on 08 November 2008

Simulating Multithreading in VBA using Excel
This article describes 3 ways of simulating multithreading in VBA using Excel. One feature that I sorely miss in the VBA's integrated development environment (IDE) is the ability to do multithreading. I think part of that comes from having worked in my previous avatar with Java and C++. In the past few days I’ve been thinking about ways in which one can ‘simulate’ multithreading in excel. (If you noticed, I said ‘simulate’ – you can’t actually multithread with vba – not atleast ...
Continue Reading

Comments (7)

How to Read Data from Pivot Table Using VBA in Excel

Posted on 07 November 2008

How to Read Data from Pivot Table Using VBA in Excel
Reading pivot tables using vba can be quite easy. Here are a few ways you can do it. You may also want to read my previous article on formatting pivots. (Before you dive further, you may want to download this example which will guide you through some of the steps in reading a pivot table data using vba) The Pivot Table Hierarchy Lets look at this peice of code: Sub ListAllItemObjects()...
Continue Reading

Comments (20)

VLOOKUP Function Error

Posted on 18 October 2008

VLOOKUP Function Error
You think you've entered the right formula and yet you get the dreaded #N/A error on the sheet.....well....here are some useful tips to get you through the vlookup blues! =vlookup(value_to_match, range_to_find_this_value, which_column_to_return , try_to_approximate_if_no_match) (Delimited) text being compared to numbers If you have imported data from a plain text file (a .txt file), your data may most likely have numbers which have been converted to string. Excel will still display the values ...
Continue Reading

Comments (2)

Get each of your spreadsheets a customized menu

Posted on 09 October 2008

Get each of your spreadsheets a customized menu
Those who’ve worked with elaborate multi-worksheet dashboards would realize the usefulness of adding a customized menu option. Apart from other things, a customized menu option can be used to guide the user to specific portions of an excel spreadsheet. For example, if you have a long report consisting of multiple charts, pivots, images and data fields, you may want to spare the reader the effort of locating the most useful (or important) portions by creating a new menu option and ...
Continue Reading

Comments (1)

How much is $700 billion?

Posted on 03 October 2008

How much is $700 billion?
USA Today came out with this graphic recently which compares the size of the recent bailout package with the other historical packages in the past. I tried my own version of this and came pretty close to achieving the same results in excel. "Close" because there would be errors since the size of the boxes may not accurately represent the number corresponding to it. As Paulson said, "If you have ...
Continue Reading

Comments     

Making pivot tables move in tandem

Posted on 23 September 2008

Making pivot tables move in tandem
While working with multiple pivot tables, one often encounters a situation where multiple pivot tables are created with the same underlying set of data. In such cases, you may want to have the ability to simply select a value from a single drop-down and have all the pivot tables reflect that change. A small piece of code can be used to achieve this. It uses the PivotTable.PivotFields().PivotItems attribute to check if the value selected ...
Continue Reading

Comments (2)

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