New 2010 Microsoft Excel beta

Anybody keen to try out the new 2010 MS Excel? You can download a copy from this link http://us2.office2010beta.microsoft.com/default.aspx?culture=en-US

Select entire Pivot Table using VBA

We can select the entire Pivot Table by using the special command called TableRange2. The following codes is to select the entire Pivot Table and paste it to another location (like a blank worksheet).

Sub copy_Pivot_Table()

'Used range is a command readily avaiable in Excel VBA.
ThisWorkbook.Worksheets("Sheet1").UsedRange.Clear

'The code below is to identify and perform a copy of Pivot Table.
'TableRange2 is a special command that refers to the entire Pivot Table
With Worksheets("Report")
.PivotTables("PivotTable2").TableRange2.Copy
End With

With ThisWorkbook.Worksheets("Sheet1")
.Activate
.Range("A4").PasteSpecial Paste:=xlPasteAll
.Range("A4").PasteSpecial Paste:=xlValues
.UsedRange.Columns.AutoFit
End With

End Sub

How to protect part of an Excel worksheet

If you are using the intranet to view your report and would like to have a copy downloaded neatly into Excel, you can consider using Excel Web query. It is a tool that will help you retrieve data from the web and present it into Excel. With web query, you can even get update at regular interval if the data changes all the time. Watch this video to find out how to set it up.

An alternative to IF formula

If you have tried to use eight nested IF formula in a single cell, you will find that it does not work. Because the limit is 7 if in one cell. But that doesn't mean there is nothing you could do. You can consider using the LOOKUP formula which can take in more than 7 conditions. See this LOOKUP page for more details.

How to Use Excel to Choose the Right Fund for Your Insurance Policies

When was the last time you reviewed your investment-linked policy? For me, it was about two to three years back. I was disappointed at the review because I discovered that the investment tied to my insurance policy was making losses. I took a bet and switched to a fund based on pure gut feel. There was no detailed analysis on all the funds to make sure that the fund I have chosen was the best option.



The market indices (e.g Straits Times Index) tell you whether the funds are performing better or worse than the previous indices and do not offer any insight or analysis to decide whether it is worthwhile to hold on to the unit trust or switch to another.



It was an uphill task to review the funds' performance again this year. Every unit trust has its own set of numbers and flipping through the annual report to make comparison across all the funds was a pain and highly confusing. After making comparison across the unit trusts on the income earned, I moved on to review the funds valuation. In the midst of reviewing, I decided to refer back to my income analysis. Guess what? I have lost track of the analysis done earlier. It was very frustrating and I almost gave up until an Excel-lent idea struck me. Why not make the comparison with Excel?



Putting them on tables was a great way to compare the performance across funds. I could scroll left and right to find out the funds ranking. However, moving from one end of the table to another became a strain after some time. In the end, I decided to develop a one page dashboard ...

Using Excel to access databases

One of my uses of Macro is to retrieve records from databases such as sql, Dbase, Access, etc. While MS Query in Excel could do the job pretty well, the database files cannot be shift to other folders. With Macro, this become a possibility. But the main reason for using Macro is I could retrieve records from more complex databases without using additional software. And since my clients like the records to be presented in Excel, this approach becomes a perfect option for me. Accessing databases using macro is relatively easy with ADODB. All you need is a connection, a sql link and a command to present the records in Excel. Take a look at this ADODB page written specially for this purpose.

Business Report on Customer Satisfaction

I have created a business report on Customer Satisfaction after studying the findings that were published in The Straits Times, a newspaper publication in Singapore. I did it because I could not conclude anything from the results which are in numbers. Take a look and let me know which is better. To request for a copy of the report, go to this business report page.

Calculate Depreciation

I have just written a page on how to calculate depreciation with your worksheet. Hope you will like it.

New Excel Challenge

I have come up with an Excel Challenge for anyone who would like to find out how much they know about Excel. You can sign up for the challenge on this microsoft® excel test page.

DateDif - An Excel's Age Calculator

Learn more about this unknown worksheet function that can help you calculate age accurately.

http://www.advanced-excel.com/age_calculator.html

SUMPRODUCT

When I first learned about the SUMPRODUCT formula in Excel, I almost dismissed it as a useless formula used by only few users. How often would anyone need to multiply 2 or more groups of numbers together and add up the results!?
To get to the story,..... go to our sumproduct page.

Budgeting

I have finally developed a personal budgeting template that will allow users to easily entered their budgeted and actual numbers into the template and have the numbers automatically captured into the pivot table reports. This is possible because I used some complex formulas that I "borrowed" from my corporate budgeting course. The template is to demostrate that budgeting can become very easy if you know how to use the right formula for the job. If you want to see the power of this complex formula at work, request for the budget template now!

If you think that the template is useful for your corporate budgeting exercise, feel free to contact me. I conduct the course in Singapore.

Excel Date

Excel stores dates as numbers with the number 1 referring to 1 Jan 1900. Understanding how excel date works is very important because it will help you present the date in different format and also help you in calculations. You can find out more about this and the date formulas in this page on excel date.

Tracking changes in a worksheet

Tracking changes in a worksheet is very simple. In this tracking updates article, we offer you 2 methods, one using conditional formatting and another through a readily available function in Excel. Go to our tracking changes page to find out more.

VBA Code to clear current region

ThisWorkbook.Worksheets("Sheet2").Range("A6").CurrentRegion.Clear

Code to display header from Database

For i = 0 To RS.Fields.Count - 1
ThisWorkbook.Worksheets("Sheet2").Cells(6, i + 1) = RS.Fields(i).Name
Next i

2009 Calendar

What is the best way to create a calendar? Using a program you purchased from the internet, Get a free template from a website or key the dates in manually into an Excel spreadsheet?

How about having a template that has already been done up and all you have to do is to key in the year and the dates are populated automatically into the worksheet. You can add in the public holidays of your choice, even your own leave calendar and have it highlight auotmatically in the calendar. All this without the use of any programs, macros. Just by using formuals. If you think that this is a calendar that will meet your needs, go to this excel calendar page to download the template.

Free Excel Calendar

I have an Excel Calendar Template that could show the public holdiays of any country simply by adding the dates into the worksheet. A good tool to plan for your activities in year 2009. You can click on the link for more info http://www.everydayexcel.com/excel_calendar.php.

Cheers.
Jason Khoo

Discount Rate

I think in calculating PV or NPV, one of the most confusing part of the calculation is the determination of the discount rate. I used to think that discount rate is the interest rate. Now I have come to realise that it is not the case. Discount rate is the rate you use to determine whether a project is worthwhile taking. Discount rate takes into consideration the risk of the project. Take a look at this Net Present Value example and see if it could help to clarify the difference between discount rate and interest rate.

3 Important Attributes of an Excellent Budgeting Tool

The 3 important attributes of an Excellent Budgeting Tool are:

1. Total Control of Template Layout and Easy Customisation
2. Versatile Analysis of Data in Different Business Perspective
3. Easy Consolidation of Data and Generation of High-Quality Reports

Find out more from this budgeting tool write-up.