Thursday, June 29, 2006

Download Home Loan Checker

Here comes another FREE spreadsheet to better assist your personal finance. If you have a mortgage, you might already know most of the time bank over accrued your interest payment, it might not be significant, but over a loan term of 20 to 30 years, it could snowball into an amount you wouldn’t want to ignore.

There’s plenty of home loan checker software in the market charge you hundreds of dollars. Undeniably there have a much more professional looking interface, but if you could check your loan with a FREE spreadsheet, who cares about flashy design?

You could download the spreadsheet here. Please read the instructions in there carefully, but if you have any questions please do not hesitate to leave your questions in the comment area! Good luck!

Keywords: Download

Wednesday, June 28, 2006

Database Functions: Using DMAX in Excel

DMAX returns the largest number in a column of a list or database that matches conditions you specify.

DMAX(database,field,criteria)

Database - is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field - indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "USB" or "Speed" or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria - is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Feel free to download here for an example.

Tuesday, June 27, 2006

Using DCOUNT in Excel Database

DCOUNT counts the cells that contain numbers in a column of a list or database that match conditions you specify.

The Field argument is optional. If field is omitted, DCOUNT counts all records in the database that match the criteria.
DCOUNT(database,field,criteria)

Database - is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field - indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "USB" or "Speed" or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria - is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Feel free to download the WORD file for better understanding.

Using DAVERAGE in Excel Database

Let's start talking about Database functions in Excel.

Averages the values in a column of a list or database that match conditions you specify.

DAVERAGE(database,field,criteria)

Database - is the range of cells that makes up the list or database. A database is a list of related data in which rows of related information are records, and columns of data are fields. The first row of the list contains labels for each column.

Field - indicates which column is used in the function. Field can be given as text with the column label enclosed between double quotation marks, such as "USB" or "Speed" or as a number that represents the position of the column within the list: 1 for the first column, 2 for the second column, and so on.

Criteria - is the range of cells that contains the conditions you specify. You can use any range for the criteria argument, as long as it includes at least one column label and at least one cell below the column label for specifying a condition for the column.

Feel free to download the Word file to gain a better understanding of this function.

Monday, June 26, 2006

Why Spreadsheet is Important?

I have been an analyst for three years and I have to admit, without spreadsheet skill it would be impossible to finish most of the analysis tasks assigned to me.

When I was in uni studying supply chain, I wasn't aware the importance of Excel spreadsheet. And I thought I have know enough. All I know is drawing tables, bold and italic characters, some basic calculations and formatting. That's it!

Luckily I manage to struggle through my uni life and land a job in the real world, only to realise that I have learnt enough theory, but the practical tool is a stranger to me. One is hard to imagine how powerful and complicated can an Excel spreadsheet do without knowing the functions and formulas available in this wonderful software. After yearsof practicing, I becomes obssessed with the power of Excel. I started to learn as many functons and formulas as I can, applying them into my daily tasks. i have to say becoming familiar with the software has made me more productive in the office environment.

That's why i started this blog, because I know it could be hard to learn on your own without guidance. Nowadays, I do part-time Excel teaching, most of them believe this will enhance their career progression, and i definitely agree with them.

Excel has too many functions, you wouldn't want to commmit into all at a time. Learn only the most practical and commonly used functions is the best way to be productive and subseqently lead to better career progression.

Most of my subsequent posts come from my own learning notes. It has been proven and revised along the track while I teach Excel. Believe, it won't take you long to become proficient in Excel spreadsheeting. If you like, feel free to visit some links available, they all provide useful information about Excel.

Good luck!

Budgeting Spreadsheet

Anyone thinking to take control of their personal finance?

I have created a spreadsheet available for free download. Please click here to go to the download page.

Basically this budget allows you to put in your expenses and income in daily, weekly or annual amount, it will automatically standardise the timeline and present you with a savings plan, and will subsequently identify your three most significant expenses, thus allowing you to identify the critical area to plan your savings!

Enjoy and please feel free to provide me some feedbacks! I will put up more spreadsheets in the neat future...

Keywords: Download

Conditional Formatting in Excel

Imaging being able to highlight one or more cells with colours or bold automatically when the value of cell exceeds 1000, wouldn't it be wonderful? And it makes your spreadsheet so intelligent!

A formatting that only applies if certain condition(s) are met. The formatting could be shading or cells or changing of font, color and size. Conditional formatting is particularly useful in highlighting one or more situations.

Creating Conditional Formatting:

1. Format>Conditional Formatting…

2. Specify condition(s)

3. Specify formatting (when conditions met)

A user can add one or more conditions into a cell or a range of cells.

Protect Excel Document with a Password

Another technique in formatting worksheet.

You can protect elements (e.g. cells with formulas) on a worksheet from all user access, or you can grant individual users access to the cells you specify. The purpose of this function is to restrict user make changes on your worksheet.

Creating Protection:

1. Tools>Protection>Protect Sheet…

2. Enter password

3. Re-enter password

4. “Allow all users of this worksheet to:”

Example:

When you come to the step 4 mentioned above, unclick “select locked cells” and click “select unlocked cells” allow users to access or modify cells that were unlocked.

A user can lock/unlock a cell by going to:

Format>Cells…>Protection

Freeze panes & Split in Excel

A frequenlty used formatting in spreadsheets.

Freeze Panes is used when you have multiple rows/columns of data that could not be fully viewed in a full size window. It allows you to freeze/fix certain columns and/or rows, so when you scroll down or right the frozen columns and/or rows will still stay in its current position in the windows.

Freezing Panes:

1. Window>Free Panes

Some examples:

1. Action: Click on column B, and freeze panes
Outcome: Column A will keep staying at the left of the window if scrolling right

2. Action: Click on row 2, and freeze panes
Outcome: Row 1 will keep staying at the top of the window if scrolling down

3. Action: Click cell B2, and freeze panes
Outcome: Row 1 will always stay at top and column A will always stay at left
Split is another function in manipulating the window size. Click on a cell and split window will result in a window being split into 4 different windows with identical content. In this case, you will have 4 windows allowing you to scroll up/down and left/right on each of the split window.

Creating Split:

1. Window>Split

Excel Data Validation

Let's talk about Data Validation, a function so user-friendly and powerful yet always ignored! It allows you to specify formatting of cells in spreadsheet.

Data validation can be used to prompt users entering requested information. Excel will validate whether the data entered is valid. Otherwise it will prompt and error message. The purpose of this function is to restrict the type and range of data to be entered into a cell.

Types of Data Validation:

• Whole Number
o E.g. Specify the range of numbers to enter

• Decimal
o E.g. Specify the range of numbers with decimals to enter

• List
o E.g. Place an in-cell drop down menu

• Date
o E.g. Specify a range of date to enter

• Time

• Text Length
o E.g. Restrict the length of text

• Custom

Types of Conditions in Data Validation:

• Between

• Not Between

• Equal to

• Not Equal to

• Greater than

• Less than

• Greater than or equal to

• Less than or equal to

Excel Auto Filter

Let's talk about Autofilter function! This is one of the most commonly used tool in Excel.

This is a relatively easy formatting & data manipulation technique.

Putting an AutoFilter into a column header allows user to specify the type of information to view. A filter acts as a drop down menu in the header column. A filter does not sort your data, it simply hides the information that you choose not to display.

Creating AutoFilter

Data>Filter>AutoFilter

If you like to know more, "Daily Dose of Excel" always have good illustrations. Click here to view.

Insert Subtotal in Excel - Formatting

Before we go into this, please bear in mind subtotal is different from the SUM function in Excel.

Users can calculate subtotal and grand total values of a list. A subtotal allows user to hide and display detailed information. This is particularly useful if user wants to work out the subtotal of each category in a long list of data.

To insert subtotals, you first sort your list so that the rows you want to subtotal are grouped together. You can then calculate subtotals for any column that contains numbers.

Inserting Subtotal:

1. Highlight the page

2. Data>Subtotals…

3. A popup window appears:

4. “At each change in:” – choose the column heading that you wish to categorize

5. “Use function:” – Default to “Count” if your chosen field is text and “Sum” if your chosen field is numeric

6. “Add subtotal to:” – specify at which column you want to put in the subtotal function

7. Click OK

Excel Pivot Table - Formatting

Pivot Table! A very powerful tool in Excel! In job interviews when I first started my job hunting some time ago, I have always been asked whether I know how to create pivot table. That means its one of the most frequently used and practical tool in the business world.

A Pivot Table report is an interactive table allows you to combine and compare large amount of data. It offers the flexibility to modify the rows and columns of a table to come out with relevant information.

For example, if you have multiple columns of data, a pivot table allows you to play around with columns of data by putting one column as row, and compare against the other columns of information.

Creating Pivot Table:

1. Data>PivotTable and PivotChart Report…>

2. Follow the three steps in wizard

3. Step 1 of wizard defaults to analyze data in excel and to create a PivotTable

4. Step 2 prompt user to highlight the source data

5. Step 3 prompt user to choose where will the created PivotTable report to be placed

6. When report is created, users will be prompted a window to drag and drop data field into PivotTable

The above is just a simple guide to pivot table, if you like to know more, you could refer to this link. It comes with some pictures, from "Daily Dose of Excel".

Keywords: Formatting