ExcelWorks

Comparing 2 set of data from 2 different dates

2011-09-17T22:44:00.001+08:00

In last Friday training, one of the participants came with an exceptionally interesting problem. On a weekly basis, she needs to donwload a spreadsheet from the HR systems which will list down the employees' profiles, such as employee ID, Name, Department, Reporting Manager, etc. Using the downloaded worksheet, she has to compare the details against the previous week worksheet and identify the changes that have taken place. The first answer i thought of is to use VLOOKUP. But the problem is there are more than 20 columns and any of these columns could be different from the previous week. And there are not just a few rows but a few thousand rows. So how on earth can we identify all the cells from 20 columns x say about 6000 rows, total of 120,000 cells? Impossible if we are to eye ball at every cells.

The solution called for the use of VLOOKUP formula using employee ID as the lookup value plus conditional formatting. Using a formula based conditional formatting option, we can input the VLOOKUP formula (to pick up the data from the previous week) and compare the result with the value in the current cell. (Take note that this is not the normal VLOOKUP formula. You have to setup the VLOOKUP formula so that it will return the value TRUE or FALSE, the basis for conditional formatting to work.) If the values are different between the 2 weeks, the cell will be highlighted with a colour. This will allow us to zoom in into the colored cells and work with them. Of course, this is based on the assumption that most of the cells would remain the same between the 2 weeks. We have to make the formula more dynamic and To make the formula dynamic, we have to add in the COLUMN formula so that it can automatically change it's value as you apply the conditional formatting to the right and down the worksheet).

Create a list with no blank cells

2011-09-17T21:14:00.002+08:00

Somebody sent me an excel file containing a list of hospitals in Column A. In the first row, it listed 40 different types of rebates. For each hospital, only certain rebates were used.

He would like to create a dropdown list for each hospital, showing only the rebates that contain a value. For example, he would like the dropdown list to show only Rebate1 and 3 for hospital 1 because only these 2 rebate columns contains values. He asked if it can be done.

I have tried googling on whether I can create a list with no blank cells before and couldn't find a solution. But I deceided to try again. To my surprise, I managed to find a few websites that demonstrated how it can be done using formulas. One of them that explained how it can done can be found here.

I modified the formula to do it for columns. Then I setup the formula such that he can do a double selection, select an hospital first before showing the list of rebates. This involved the use of the all powerful OFFSET formula to complete the solution. To see how it works, download this excel file.

List of Working Days

2011-09-02T10:51:00.000+08:00

Question: How do I automatically create a list of working days from one date to another? I want to make a range of dates from 8/31/11 thru 3/1/2012, for example. I want to have the dates listed in a column.

Answer: You can use the Workday formula WORKDAY(start_date,days,holidays)

About Edate

2011-09-02T10:45:00.000+08:00

Question: We use edate function for finding the date after certain months .do we have similar functions for years and days?

Answer: As far as we know, edate is the only function in Excel. For years, we always multiple the number of years by 12. EYear is not really necessary because the number of days is consistent for the months in every year except Feb. There is no need for days, just add the number of days to the start date.

Extract data within brackets

2011-08-12T01:26:00.000+08:00

Problem:
I want the word in brackets to appear only (Under the Class Column). Eg:

Degree Class
BACC(MERIT) MERIT

Answer:
You can use the formula

=MID(A2,SEARCH("(",A2)+1,SEARCH(")",A2)-1-SEARCH("(",A2))

Or TEXT-TO-COLUMN with “(“ as the separate. To remove the “)” use find “)” and replace with nothing.

Problem:
What if I want the data from within the 2nd bracket?
Degree Class
B.ENG.(CHEM.& BIOMOL.ENG.)(SECOND LOWER) SECOND LOWER

Answer:

You can use the formula.

=MID(A2,SEARCH("(",A2,SEARCH("(",A2)+2)+1,LEN(A2)-SEARCH("(",A2,SEARCH("(",A2,SEARCH("(",A2)+2))-1)

Work Order Status

2011-05-16T11:30:00.000+08:00

Background

1. I will receive work order from planning section
2. I will key those information into excel "work order" sheet from column C to G
3. My production member will follow the work order to assemble as per required
4. After completed, I request them to key in the instrument serial number + details on "Instrument SN" sheet.
5. I create "pivot table" so to understanding the daily output of requirement
6. I want to link the daily output qty from "Pivot table" sheet to "Work order" sheet so to monitor whether Work order completed.

Problem encountered:

Step 6: I need to match date from "Pivot table" to date to "work order" as well as work order no. from "pivot table" to "work order". I have no idea how to do for 2 dimensional matching. At first I try to use sumif but fail.

Answer

I used GetPivotData, a formula that can be used to extract data from Pivot Table dynamically for this. You can change the parameters to pick up the data basd on 2 conditions, the workorder and the date. The number picked up is already a consolidated number from pivot table. I have combined it with IF and ISERR to make sure that all the cells return zero if no data is found.

You can find the solution in this file http://www.everydayexcel.com/files/work_order_status.xls

Why HR need to use Excel?

2011-05-11T23:18:00.000+08:00

1. To monitor the renewal of passports/work permits
2. Calculate anniversaries of employees
3. Track trainin programs attended by employees
4. Analyse benefits paid out
5. To compute salary details including CPF.

Display Product Image (con't)

2011-04-27T15:47:00.000+08:00

Yesterday, I saw a promotion list and found that there were too much information in the print out. It reminded me of a price list that I had from a distributor. There were thousands of products in the list and navigating through the list was a nightmare. So I decided to improve on it with 2 dropdown lists, one for selecting the product model and the other to select the product code. The second list is dependent on the selection on the first list. It helps to zoom into the particular product group and reduce the no of models for selection from a few hundreds to a few. And combined with the Display Product Image solution, I can now view a particular product model and the given description easily. Download this file for more details.

Display Product Image

2011-04-21T01:19:00.000+08:00

This solution is created with a combination of functions and formula. The frame for the changing image is created using the Camera function in Excel. Adding the Offset and Match formula make the address reference change according to the product code appearing in Cell H2. We use data validation to create a dropdown list for users to select the product code to display.

Autofill Frustration

2011-03-25T00:54:00.000+08:00

Question: when I drag a date to the line below it, the date advances by one day. I don't want this. I would like the same date to drag down each time

Answer: If you turn off autofill, you will also turn off the ability to drag cells down. I don't think this is what you are looking for. An alternative approach is to highlight all the cells you wish to fill, type the value and then press Ctrl + ENTER at the same time. It will help you fill up the highlighted cells with the same date.

Financial Quarters

2011-03-18T14:32:00.000+08:00

If your company has financial year that does not coincide with the calendar year, you will have problem presenting your numbers in Financial Quarters, especially when you have to work with hundreds or thousand of records. But by employing a few formulas, this job can be done in just a few minutes. Take a look at my new write up on Calculating Financial Quarters.

find a particular word from of group of words in a cell

2011-03-07T09:00:00.000+08:00

Question:
can excel find a particular word from of group of words in a cell and in turn should give a particular output for a particular text, the text needs to be searched and the output which needs to be displayed will be given when created so that from the next time if it finds that text it needs to give the output which is mentioned earlier, can you please help me on this?

Answer:
You can use the SEARCH formula to find the word. Combined it with IF and the ISNUMBER formula. Assuming that the list is in column A and we are working on A2. The formula to place in B2 would be

=IF(ISNUMBER(SEARCH("REPLACE THIS WITH YOUR OWN TEXT",A2)),"THE OUTPUT YOU WANT","")

Which view is good for Pivot Table and data management?

2011-03-01T15:44:00.000+08:00

Question: I have a large data of about 700 columns & 4000 rows relating to the sales.Great of columns(fields) are due to no. of sale-able items (About 670)

I have designed the table with each individual sale-able items as separate Fields instead of One field under Item Name as I want to view sales transaction in one record. This is to overcome disadvantage of entering data for party name, bill no, date...etc several times for sales transactions having more than one items.
But when I converted the data into Table & make use of Database functions like dsum, dget to extract the results, it slows down.Calculation is very slow,even if I open or save the excel file it takes a lot of time.

I tried pivot table but it didn't worked As I need to drag and drop almost 650 fields.

Answer:
The correct approach is what you have been avoiding, that is, to put the items names under One column. This arrangement facilitate the use of Pivot Table to generate results. What you are concerned with, to view transactions in one records can still be achieved in the Pivot Table view, instead of the raw data view. Right not, you have no choice but to re-organise everything in the other view. A macro can be very helpful for this situation.

Convert multiple rows record into one single row

2011-02-26T11:09:00.000+08:00

Question:

The worksheet shows some payments under each customer (header). How can I apply the “sum if” formula to copy the customer code and customer name to every “PY” in Column D?

Answer:

You cannot apply SUMIF to copy the customer code and company name to every PY in column D. The formula you should use is IF.

First, you must put the customer code and company in the first row. The best way to do this is to use the formula method. For example, in A1 put the formula =C1 to display the customer code in A1.

Using the IF formula, we can set the condition to pick up the customer code or the company name is column D contains "PY", i.e. in cell A1, enter the formula

=IF(D2="PY",A1,....)

That is the first part.

Now the IF formula has a part to present a value when column D does not contain the text "PY". Specifcally, we are interested in is to return the company name if it ever changes and discards all other values. To do this, we have to find the pattern that we can use to identify that the row contains company name. We can check whether column E is empty. We can use another IF formula to do this. Since we have dealt with "PY" in column D, it will not pick up "PY" again. In this case, it will pick up the customer code when it detect the column E is empty. So we have to add another IF formula into the first IF formula. Now the formula becomes

=IF(D2="PY",A1,IF(E2="",C2,....

If it does not find a blank, return the value that is above the formula, i.e. A1. So the the formula in A2 finally becomes

=IF(D2="PY",A1,IF(E2="",C2,A1))

Cannot format my dates in Excel

2011-02-26T10:27:00.000+08:00

Question:

I have a worksheet containing hundreds of rows of data whereby no matter how I tried formatting the “date” (Column D), it still does not shows in ascending or descending sequence but instead it sort by the 1^st 2-digits of the data.

Answer:

The date column is actually not presenting the dates in the right format. They are text formatted probably because your computer is accepting dates with dd/mm/yy format while the dates in your worksheet is in mm/dd/yy. Or it could be the other way round. To resolve this, the fastest way is to use

Data > Text to Columns > Next > Next > Date Format (Select “Date”)” and choose the right date format (dd/mm/yy) for your dates.

After the conversion, you will be able to format the dates in any format you prefer.

Presenting numbers in thousands

2011-02-22T00:47:00.002+08:00

Watch the video below and find out 4 different methods to display a number in thousands. The 4th method is real cool! I am sure you will like it.

Mesh two tables of data

2011-02-20T18:35:00.000+08:00

Question:
How can i "mesh" two tables of data? Each block of data has a common column with the person's last name. When I sort, however, there are missing names in the smaller block of data. I want excel to link or mesh the two blocks.

Answer:
You can apply the VLOOKUP formula on the bigger table for those fields you want to combine. If the formula find the last name (common field), they would return the values found in the smaller table. If not, you will get a #N/A error which is an indication that the value is not available in the small table. Remember to set the last parameter to FALSE.

Combined First Name with Last Name

2011-02-07T11:09:00.001+08:00

Question:
I have 1 column of 2000 rows. Each row consists of a person's name; street address; city, state, and zip code. Thus far, I've gone to the "Data" tab then "Text To Columns" used the "Delimited" option to delimit by "Space." That option has seperated the text within each row into seperate cells. How do I combine the cells to combine,for example, first name with last name, house number with street name, and so on?

Answer:
You can use the "&" key which is a substitute for concatenate. Assuming that your first name and last name is in A1 and B1 respectively, you can combined them together (with a comma in between them) using the following formula:

=A1&", "&B1

Remove line breaks

2011-02-07T10:39:00.005+08:00

Question : I have a report with more than 60K rows and in one column, the cells contains text in more than one line (within the same cell). Instead of manually removing the line breaks cell by cell, is there a easier way?

Answer : There are 2 ways to solve this problem.
One is to go to format cell, alignment tab and remove the check mark on wrap text.
The second method is to use the CLEAN formula. CLEAN formula removes all non-printable characters from the cell and line break is one of them. Assuming that the text is in A2, enter the following formula without quotes in B2 "=clean(A2)".

New in Excel 2007

2010-11-11T23:49:00.002+08:00

Excel 2007 allow you to filter records based on cell color, font color. If the column contains dates, you can even filter by year and month. That's an improvement from Excel 2003.

It also help you to sort by cell colors. Other criteria available are cell icons and Font color.

How to sum up range that contains error?

2010-06-15T23:41:00.002+08:00

If you use the normal SUM formula, you will get #N/A. To find out the total of the values, you have to use the SUMIF formula =SUMIF(B2:B10,"<>#N/A"), assuming that the range is from B2 to B10.

Calculate working days only

2010-02-12T15:07:00.002+08:00

How do you calculate the number of working days between 2 dates? You can use the NETWORKDAYS formula or the SUMPRODUCT formula. This page on calculating networking days tells you all.

New 2010 Microsoft Excel beta

2009-11-27T00:23:00.001+08:00

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

2009-11-09T10:35:00.003+08:00

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

2009-07-07T11:49:00.008+08:00

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

2009-06-13T00:09:00.003+08:00

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

2009-06-01T23:09:00.005+08:00

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

2009-05-13T09:44:00.002+08:00

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

2009-04-25T23:43:00.002+08:00

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

2009-04-20T23:32:00.002+08:00

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

New Excel Challenge

2009-04-10T00:42:00.003+08:00

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

2009-02-26T09:39:00.001+08:00

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

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

SUMPRODUCT

2009-02-08T23:52:00.001+08:00

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

2009-01-22T17:48:00.003+08:00

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

2009-01-04T23:41:00.002+08:00

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

2009-01-02T14:33:00.002+08:00

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

2008-11-05T16:03:00.000+08:00

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

Code to display header from Database

2008-11-05T16:00:00.000+08:00

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

2009 Calendar

2008-10-22T16:39:00.000+08:00

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

2008-10-15T13:35:00.000+08:00

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

2008-09-21T01:01:00.000+08:00

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

2008-05-20T15:06:00.000+08:00

The 3 important attributes of an Excellent Budgeting Tool are:

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

Find out more from this budgeting tool write-up.

Excel 2007 Conditional formatting bug

2008-05-14T12:02:00.000+08:00

I was developing my Excel 2007 eCourse and trying out the conditional formatting that I stumble upon this bug. Maybe it's not, I don't know where to report it so I decided to publish this in my blog to see if somebody would like to provide an answer.

In the conditional formating bug file, take a look at cell B4. It is supposed to be red based on the condition set but it turns out green. Anybody can help?

Learn more about Excel 2007

2008-05-08T23:17:00.000+08:00

How do you like the last post on Excel 2007? If you like it and would like to have more of it, I have good news for you. I am creating a brand new eCourse for you to familarise yourself with Excel 2007 and its FREE! If you interested, click onto this free Excel 2007 eCourse page.

How to activate Save As in Excel 2007

2008-04-15T14:42:00.001+08:00

Hi,

If you are using Excel 2007 for the very first time, you might be stumped on where the save as function is. Because you will not be able to find the familar File Menu you used to see in Excel 2003 and below. So if you are ready to upgrade 2007, you might want to read this first so that you would not be caught off-guard. In here, we will show you the different ways you could save a file:

If you are using short cut keys, I have good news for you. All the short cut keys you learnt in the previous version applies in version 2007. So you would have no problem at all. For those who want to know the shortcut key to save as, it is function F12 or Ctrl + F2.

If you are used to the file menu, you can activate the save as function by clicking on the icon at the top left hand corner of Excel. See the picture below. In that, you will see the all familar list of functions when you click on the file menu in the previous version of Excel.

That's it for the Save As Function in Excel 2007. Do come back reguarly for new updates.

A new Life with Excel Budgeting

2008-04-13T17:28:00.000+08:00

Are you going to start your annual budgeting exercise soon?

Are you getting ready to

Protect the template to minmise the disruption to your consolidation effort?
Check the templates filled up by the business heads in detail, making sure that the template layout is not changed in any way?
Put the budget number in one single workbook so that you could consolidate the numbers by summing across the worksheets?
Set up links to analyse the key expenses by departments, country, by month?
Create links to another workbook for the executive summay and charts for you reporting?
Create multiple sets of report for different managers, e.g product manager, channel manager, etc?
Work overtime and during the weekends to get the budget out before the deadline?
Run a few revisions of the budget and repeat the process for each revision?

If you are, I have GOOD NEWS for you!........More on Excel Budgeting

Does this describe your excel budgeting situation?

2008-04-08T16:35:00.001+08:00

Mega Retailer Company (“MRC”) is one of the leading global distributors of consumer products. Their products are categorized mainly into 3 product groups, Food, Personal Care and Home Care. Its customer base ranges from Hotels/Restaurants to Supermarkets to your neighbourhood provision shop. MRC has local presence in every country in Asia Pacific. One or more companies are set up in each country to serve the local market.

You are the Budgeting Manager for MRC. It’s budget time again! :(

Am I going to go thru the same old process again? Sending the templates......

If you don't want to, why not take a look at this brand new way of budgeting?

Improved Find Function for use in VBA

2008-03-23T00:43:00.000+08:00

Below is the improved find function which aske users for area to find and also choose whether they would like the function to return the cell address, row or column of the cell returns by the find function


Function Find_Address(What_2_Find, Where_2_Find, _
GetAddress_Row_or_Column As String)

' this is a range.
Value_or_Formula = xlFormulas
    'xlVaues - search the text/value in the cell,
        'for cells with formula, it will look at the result.
    'xlformulas - search the text/value within formula
    'xlformulas works even when cell is hidden.
    'It is able to look for value too.
Exact_Partial = xlPart
    'xlPart - find cells that contains (What_2_Find)
    'xlWhole - find cells which contains exactly the value
        'placed in (What_2_Find)
        
Match_Capital_Letters = False
    'False - when A and a is treated the same
    ' True when A and a means different things.
    
With Where_2_Find
    Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _
        LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters)
    'c is the cell that meet your find criteria
    If Not c Is Nothing Then
        
        Select Case GetAddress_Row_or_Column
            Case Is = "Address"
                Find_Address = c.Address
                    ' Change this to c.column if you want
                        'the column
                    ' where the text is found.
                    'change it to c.address if you want the
                        'address returned.
            Case Is = "Row"
                Find_Address = c.Row
            Case Is = "Column"
                Find_Address = c.Column
        End Select
    Else
        Find_Address = "A1" ' Cannot find, so default
                                'the address to A1
    End If
End With

End Function

Several Hours vs One Minute

2008-03-11T21:36:00.000+08:00

You are given a 10,000 lines product listing containing the product code and product description all combined into one column, and asked to separate the product code from the product description. Normally, with just basic Excel knowledge, you need to spend hours going through the 10,000 rows of records and type out the product code which can be 3 letters or 5 letters. Now take this test, find out how much time you need to spend on a sample of 10 records, then multiply your time by 1,000 times. This would be the time you need to spend to get this job done.

With some knowledge of Excel worksheet functions, you can use a formula to separate the 3-letter product codes from the list. But, you still need to take an hour or two to go through all the records to identify all the 5-letter product codes so that you could adjust your formula for the 5-letter product codes one by one.

Now, in our Excel training course, you can learn to apply one more formula that can help separate the product code from the product description intelligently. And the time you needed? One minute - and this is the time taken to set up the formula.

Do you know the 2 formulas you need to save you several hours on the task?

If you want to know the answer and you are not in Singapore, you can find out the answer and also learn how to work more efficiently with Excel using our online "Unleashing the Potential of Excel" course.

Macro to find cell format

2008-03-05T14:10:00.000+08:00

This macro will search through the range and identify the cell format (in our example, it is the cell color) and copy and paste the formulas from the first row to the last row which is 100

Sub Find_Cell_Format()

Set Which_Worksheet_2_Find = ThisWorkbook.Worksheets("Details")
Set Range_2_Find = Which_Worksheet_2_Find.Range("C28:BZ28")
        ' this is a range to find.
last_row_in_the_template = 100 ' Change the number of the last
     ' accordingly

Paste_Type = xlPasteFormulas
            'xlPasteAll 'default
            'xlPasteAllExceptBorders
            'xlPasteColumnWidths
            'xlPasteComments
            'xlPasteFormats
            'xlPasteFormulas
            'xlPasteFormulasAndNumberFormats
            'xlPasteValidation
            'xlPasteValues
            'xlPasteValuesAndNumberFormats
Plus_Minus_Times_Divide = xlPasteSpecialOperationNone
            'xlPasteSpecialOperationAdd
            'xlPasteSpecialOperationDivide
            'xlPasteSpecialOperationMultiply
            'xlPasteSpecialOperationNone Default
            'xlPasteSpecialOperationSubtract

For Each c In Range_2_Find '[A1:C5]
    If c.Interior.ColorIndex = 37 Then '37 refers to color
    'orange in grid chart
        c_col = c.Column
        Which_Worksheet_2_Find.Cells(29, c_col).Copy 'copy the content in row 29
        Which_Worksheet_2_Find.Range( _
            Which_Worksheet_2_Find.Cells(30, c_col), _
            Which_Worksheet_2_Find.Cells(last_row_in_the_template, c_col)) _
            .PasteSpecial Paste:=Paste_Type, Operation:=Plus_Minus_Times_Divide
        CutCopyMode = False
        Range("A1").Select
    End If

Next

End Sub

Macro for the find function

2008-03-05T11:45:00.001+08:00

This macro will return the location (row, column or address) of the cell containing the search value.

Function Find_Row_Num(What_2_Find)

What_2_Find = "Insert row before this line for PAPDR"
Set Which_Worksheet_2_Find = ThisWorkbook.Worksheets("Details")
Set Range_2_Find = Which_Worksheet_2_Find.Range("C30:C65000") ' this is a range.
Value_or_Formula = xlFormulas 'xlVaues - search the text/value in the cell,
                        'for cells with formula, it will look at the result.
                        'xlformulas - search the text/value within formula
                        'xlformulas works even when cell is hidden.
                        'It is able to look for value too.
Exact_Partial = xlPart 'xlPart - find cells that contains (What_2_Find)
                        'xlWhole - find cells which contains
                        'exactly the value placed in (What_2_Find)
Match_Capital_Letters = False 'False - when A and a is treated the same
                               ' True when A and a means different things.


With Range_2_Find
    Set c = .Find(What:=What_2_Find, LookIn:=Value_or_Formula, _
        LookAt:=Exact_Partial, MatchCase:=Match_Capital_Letters)
    'c is the cell that meet your find criteria
    If Not c Is Nothing Then
        Find_Row_Num = c.Row ' Change this to c.column if you want the column
                             ' where the text is found.
                             'change it to c.address if you want the address returned.
    Else
        Find_Row_Num = 1 ' Cannot find, so default the address to A1
    End If
End With

End Function

Consolidate your data in your workbook with this addin

2008-01-30T10:06:00.000+08:00

In may have sounded absurd 2 years ago. But as I continue to train others in my Advanced Excel Course, the idea has turned into a reality. It started off with using MSQuery to consolidate the data manually from different worksheets which I still offer as a topic for users to perform their consolidation and then to prepare reports using Pivot Table. Now, I have developed an add-in that could consolidate the data in seconds and start off with an empty pivot table so that users could prepare the reports based on their requirements. The Addin is free at this moment as I would like to collect some feedback on how to improve it further. So if you are keen, go to this page which will demo how to consolidate salary information for preparing income tax returns form. It will also tell you where to get the free download.

Remove Duplicates

2008-01-20T01:14:00.000+08:00

Excel has a number of ways to remove duplicates. One of them is to use Advanced Filter and another is using the IF formula. Advanced Filter is a quick way to get it done but it has its constraints. The IF formula is a more practical approach based on the scenarios described by our participants in our Advanced Excel Course. Find out how to remove duplicates using both methods here and also the detailed differences between the two.

A revolutionary solution to Excel Budgeting for corporations

2008-01-02T10:22:00.000+08:00

Greatings to you for the New Year 2008!

I feels good to write this entry. After one month of hard work, I have finally produced the 2 articles that provide you with the details of how our revolutionary solution to Excel budgeting is likely to cause a stir in the industry. With this solution, you can finally put your mind at ease when it comes to budgeting. No more sleepless nights to churn out the final output! Not more external links that will cause you to worry that the numbers might not get updated. And a chance to produce quality analysis and prepare all the materials you need to get your budget approved. You can also put off any plans to invest in a Business Performance Management System and still save on extra manpower you need to manage the budget. The solution uses a combination of formulas and functions such as the pivot table and msquery. To read more, read the following 2 articles:

The benefits of Excel budgeting
Our revolutionary approach to Excel Budgeting for corporations

Cheers!

The benefits of corporate budgeting with Excel

2007-12-15T16:10:00.000+08:00

We have finally completed the article on the benefits of using Excel for budgeting. After wrtiting the article, we finally have a complete picture why people stick to using Excel for budgeting and why, after so many years, it is still irreplaceable by the Business Performance Management (BPM) systems available in the market place.

Read the article at everydayExcel Business Lab or our Advanced Excel Profit Center Website.

Inventory Management System

2007-11-26T11:16:00.000+08:00

I have just created and inventory management system using Excel. In this system, there is no macro. The sytem allows users to

capture the details of the proucts in and out of the store or warehouse.
prepare a report that will show the quantities left over in the store by products at the end of each month.

For more details, please read Inventory Management System.

The pains in budgeting

2007-10-18T14:37:00.000+08:00

One of the pains in budgeting is in the consolidation of data, from departments to company level or from country level to regional level. It is a pain because people usually have to link the numbers to the consolidated worksheet. If there are only 3 to 4 files to link, it may not be so bad. Each cell would have only 4 cell references.

But imagine if you need to link 5 or more files together. You could take a lot of time, not to mentioned that there are a high chance that you might link the wrong cells together. And your user in one department may decide that the expenses should be in another row and amend the file to suit their needs and not yours. If this happens, you would start to do more checks on the file for fear that they have also done that to other rows. Or they decide to change the layout in between versions.

Some companies that sell budgeting solutions would therefore recommend that you use their solutions which will solve your problem but will set you back by hundreds of thousands of dollars. If you are in this situation, I have good news for you. By just using formulas and MSQuery (a tool given in MSOffice), you can easily consolidated the information you have into the main file even when they changes the rows in the template. There is no more fear about users changing the row layout, you need not waste any more time checking the layout and the formulas. With our proposed solution, you will be able to save time consolidating the details. What if I told you that you could even provide analysis that satisfy all your users without you have to spend hours or even days trying to prepare the report for them? If you are interested to find out how, drop us an email to keep you updated when our course is ready.

Recruitment

2007-10-18T13:16:00.000+08:00

I met up with a potential client today and we spoke a little on recruitment. She told me, based on her past experience, it is important to respond to job applicant fast. If not, you will end up having to call identied/potential candidates only to find out that they have already find a job.

I shared with her the need to save cost in recruitment using data that exist from the previous recruitment (for the same position). I also shared with her how Excel could be used to create a form that can be used to captured data filled in by the job applicants. This will help her save the need to sieve thru the resumes and immediately go on to the selection process. Because sieving thru the resume takes time and if there are more than one position to recruit, there will not have enough time to sieve the resume for different position. And doing so means you can get your choice candidate before someone else.

And also save on advertising cost. Because if you advertise for a position and you are not able to fill it with the choice candidate, you will end up wasting advertising cost.

Multiple Sources

2007-06-21T10:33:00.000+08:00

It has recently occured to me that the reason why many people are still using Excel for their work despite the availability of powerful systems in their work place. It is because Excel provide a common platform for the systems to communicate with each other and the linking can be handled by any non-technical users. The interface for these system to export data in the csv and text format means Excel is able to format these files and place in an Excel worksheet. When this is done, they are able to link the files from mulitple sources together to create the report they want.

And these reports cannot be prepared during system planning stage because the need arises as the business environment changes. Or it could be due to budget constrains and/even system constrains.

Therefore, Excel comes in handy as a tool to merge the data from theses systems together.

Enter the currency symbols in a cell

2007-06-18T17:56:00.000+08:00

You can enter the Currency symbols such as the £, ¥ or € by using the numeric keypad and ALT key.

To enter the ¥, press and hold on to the ALT key and press 0165 on the numeric keypad.
To enter the £, press and hold on to the ALT key and press 0163 on the numeric keypad.
To enter the €, press and hold on to the ALT key and press 0128 on the numeric keypad.

Note that the above method will only work with the numeric keypad (the number pad on the right side of the keyboard and the simulated number located near the right of the laptop, usually need to be activated for the laptop). It does not work with the number keys located on the top of the keyboard.

Format Numbers as Text

2007-06-18T11:20:00.000+08:00

I just paste a list from another worksheet and found that I need a text format version of the numbers in the list. I format the cells into Text format and tried replace the numbers. I thought it would work. To my surprise, it didn't. I search and found another solution and it works. Here is how:

Select the list of numbers you want to convert.
Activate the Text to Column function.
In the first step, select delimited.
Click next until you are at the last step.
Select the format as Text and click finished.
The list of numbers has been converted to text format.

To do the reverse, i.e. to convert a list of text formated numbers to number format, here is how:

Go to an empty cell and type in the number 1 (General format).
Copy the cell.
Highlight the list of text format numbers you want to convert to number format.
Using Paste Special, select the option paste values and the mutiplication operations.
Click OK and the list of numbers is now formated as numbers.

MSQuery

2007-05-28T15:14:00.000+08:00

I finally found the solution to make MSQuery worked with the files in the same folder. What do I mean by that?

MSQuery is a tool that retrieve data from database, either to present the data in an Excel worksheet or to use it with Pivot Table. MSQuery is different from import file function in the way data are updated into the worksheet. Once set up, users just have to click the refresh button or even can allow automatic refresh when the fle is opened. Whereas for import file function, users have to manually activate the import function each time updated data is needed.

When using MSQuery with Excel file as a data source, the path or directory is hardcoded or fixed. This means that if the file is moved to another folder, the data source will remain at the old directory. This is no good for my consolidation as I would like to work with different versions of the data with each set stored in different folders. Therefore, I looked for a solution on the web and found that I could run a simple macro to change the data source path such that it will always refers to source files in the same folder as the consol file. No longer is there a need to change the path of the datasource when a new version is created.

Allocation of Cost - TL project

2007-04-26T00:16:00.000+08:00

I used to allocate cost based on revenue and number of units. This time round, I learnt something new. I can also allocate cost based on Cost of Goods Sold, COGS in short. This may be a better measure for allocation as the figures are controlled internally. The company or the department is not penalised just because they are selling the goods at a higher price. Having said that, the yard stick for cost allocation remained unchange. It has to be based on the acitivity the cost has a close relation to.

Financial Modelling Project

2007-04-13T14:15:00.000+08:00

Currently, I just got another project in financial modelling. The company's name is TL in short. The model is pretty complex as they have a number of dimensions, Channels group, Channels, Product Group, products and not forgeting that we will always have to deal with a time period of 5 to 10 years.

In the old model built by somebody in the company, the channel and product are presented as a matrix. This takes up the 2 dimensions we all have to work with. For the years, the old model has to be presented in different tables.

And the revenue and expenses are presented in a sheet of its own. While this seems to be a good way to organise the data, it encounters problem when you have to consolidate the data from the different cuuntries into regions. The linking of the data is super complicated. In fact, after the links are established, you will start to wonder whether they are correctly linked.

In the new model, I recommend putting the channel and the product in one dimension, i.e. on the left hand side. In fact, in the new model, there are additional dimensions such as the revenue, COGS, etc also present at the side. I put everything in one worksheet but have them organised using the group and outline function in Excel. So I can collapse the details to give me a overview and it also allows me to drill into the details by clicking the numbers on the top left hand corner of the worksheet (which is automatically created by Excel when you use the group function).

To consolidate the data, I use another worksheet. I created a complicated formula that allows me to read the data from the input sheet. With this database, I can create a pivot table which can help me to analyze the numbers in 2 perspectives, by channels and by products. This is the wonder of pivot table. With that, I can show the budget numbers (group by products) to the product managers and to the channels managers group by channels. How much time would I need to do that, a few seconds. Why? Because the data has been organised in a worksheet, I just need to drag and drop the dimension accordingly. And the data worksheet can be the interface to consolidate the data into the regions, without links. Without links because i intent to use MSQuery to consol the data. The beauty of it? Excel will do the update for me automatically.

That's all for the time being.

Formating Pivot table items using VBA/Macro

2007-04-03T11:27:00.000+08:00

In one of my projects, I need to format the rows in a pivot table so that I can differentiate the different values (e.g. Actual, Budget or Forecast) in a particular field (e.g Type_of_Sales) . I encountered an error when one of the values (e.g. Budget) is not available for the formatting. To resolve the problem, I have to add in a line of code to loop through the values (e.g. Actual, Budget or Forecast) in the field (e.g. Type_of_Sales). If records are found (i.e. pt_item.RecordCount <> 0), then do the formating. If recordcount returns zero, then skip formatting. The code is shown below:

For Each pt_item In .PivotTables("PivotTable1").PivotFields"Type_of_Sales").PivotItems
If pt_item = "Budget" And pt_item.RecordCount <> 0 Then 'do formatting
.PivotTables("PivotTable1").PivotSelect "Budget", xlDataAndLabel, True
Selection.Interior.ColorIndex = 36
Selection.Interior.Pattern = xlSolid
End If
If pt_item = "Fcst" And pt_item.RecordCount <> 0 Then 'do formatting
.PivotTables("PivotTable1").PivotSelect "Fcst", xlDataAndLabel, True
Selection.Interior.ColorIndex = 35
Selection.Interior.Pattern = xlSolid
End If
Next pt_item

Excel Forms

2007-03-31T13:01:00.000+08:00

Just Thursday, I sent out an email to my list sharing with them how to create an Excel form and save time in consolidating the results. Using the data collected, I analyse and presented how I did the analysis. I am surprised that the responses were exceptionally good with questions and people asking me how much the course course. In fact, I even get enquiries from past participants. Find out what this is all about by clicking here.

BTW, I also have a new blog called everydayExcel which I used to share about experiences in other areas. Do visit me regularly.

Excel Challenge

2006-10-18T14:01:00.000+08:00

Here is an Excel Challenge I have created. Let me know how fast can you complete the task.

How to use data validation

2006-10-12T22:49:00.000+08:00

Data validation in Excel is used to restrict user from entering unwanted value or text into the cell.

It can also be used to create a simple dropdown box so that user can select the desired value from a list.

To know how to set up a cell with data validation, click here.

Excel for teachers

2006-10-06T00:31:00.000+08:00

How you can use Excel is really up to your imagination. In one of my training course, somebody showed me a template which is used by their GM to monitor how well the team know about the product(s) and other key salient points necessary to carry out their responsibility. The template was very useful and I have since modified it so that it can be used to profile the class performance and make comparison between a student against the class (in the case of a school teacher) This will help to identify which topic is the class is in and also the student(s) that requires special attention. Would you like to share how you are using Excel in your profession?

Uses of Excel

2006-10-04T22:14:00.000+08:00

Excel contains a lot of formulas that will help to simplify complicated calculations. By knowing these formulas, you can actually calculate depreciation in less than 5 rows. That's right less than 5 rows. I know some of you might be shock to hear that. I have spoken to many people and also know that they have been using Excel in an inefficient way. Why do I say that? Because they have been using an entire worksheet just to calculate depreciation. Isn't that a waste? Want to learn those formulas? Take a look at my website and see if you can figure out.

Uses of Excel

2006-10-03T23:09:00.000+08:00

What can Excel use for? Is it only capable of storing name list? Or to perform summation or tedious calculations? That is what a lot of people thought. The truth is, Excel can do more powerful things than that. One of the interesting things that it can do very well is forms, e.g. survey forms, feedback forms, etc. We are not talking about forms with boxes but form with dropdown list, option buttons and/or with check boxes, something like the form given below:

It is useful to create forms in Excel because by putting the answers in one row through links, users just need to copy and paste the answers into a consolidated worksheet. Thus saving time on consolidation.

Crystal Report

2006-09-10T00:26:00.000+08:00

The first time I encountered crystal report was in the late 90s. It was very powerful in pulling out data from database servers. The activity on pulling out data from databses used to be the job of the IT department. Now, users can do it themselves. It was great because I can pull out the data as and when I needed it without the need to fill up IT request form. And I can get the data I want instantly. But after I learnt about pivot table and MS Query, I never looked back....more

Internal rate of return

2006-08-30T17:57:00.000+08:00

Internal rate of return is a term used in the finance industry. The rate is derived based on the assumption that the net present value of a project is zero. At this rate (IRR), the decision to go ahead with the project is neutral. This means that the project will not add value to the portfolio, neither will it deduct any value from your existing investment. To calculate the IRR requires a financial calculator as it involves a lot of iterations to arrive at zero NPV. Alternatively, you can also use Excel to perform the calculation as a predefined worksheet formula has been developed for this purpose. Read more about how to calculate IRR using Excel here.

Net Present Value

2006-07-31T22:07:00.000+08:00

One categories of formulas that is available in Excel is for calculating financial information. It includes Present Value, Future Value and most important of all, Net Present Value. Net Present Value is used to evaluate the viability of an investment. If the Net Present Value (NPV) is positive, it means that it is worthwhile to take up that investments. It also takes into account the risk appetite of the investor. This is achieved through the use of the discount rate. An investment can be pretty complicated to evaluate as it is likey to involve cash flows for a number of years. Also, this may include cash out flow during the terms of the investment other than that in the initial stage. To calculate NPV, we usually have to draw a time line to get a clearer picture. And Excel is one good tool that could help you draw this time line. What's more, there are formulas that are provided By Excel that will help you calculate the net present value of this investment. To find out how, click here to find out more.

What is present value and how to calculate PV in Excel

2006-07-17T23:33:00.000+08:00

Present value is a financial term used to define the value of a certain amt of money today. The present value of $1 today is $1. It you put $100 in the bank, that $100 will become $105 in one year time at an interest rate of 5%. $105 is the FV of the $100 in the first year, i.e. Year 1. If you continue to put the money ($105) in the bank, it will earn another 5% interest. Your bank account will have $110.25. That is the future value of your $100 today in year 2. If you notice, the future value is dependent on the interest rate offered by the bank. If the interest rate is 10%, the FV of your $100 in year 2 is higher. The amount is $121($100*1.1*1.1). It is equal to your original sum of $100 plus the interest for 2 years. Don't forget that the interest you earn in the first year will also earn you interest in the second year too.

Assuming that you need to save $121 for some expenses two years from now, and you are interested to find out how much you would need to put into the bank today so that you will have $121 in the bank. As the bank is paying an interest rate of 10%, you know that you need to put in less today to obtain $121 in two years as a result of the interest your bank is paying you. That amount you are going to put in today is known as the present value and Excel is able to help you find out what is that amount with its present value formula.

Find out how with this link.

What is present value and how to calculate PV in Excel

2006-07-17T23:23:00.000+08:00

Excel provides a comprehensive set of formulas to perform financial calculations such as the present value (PV) of an amount obtained in the future...... more

How to join texts together and place them in a cell

2006-06-04T23:16:00.000+08:00

This page will show you 2 methods that can join texts together. The result is the same but the way the formulas are presented are different .... more

Excel course

2006-05-26T00:38:00.000+08:00

It is true that you can solve your Excel problem(s) by searching the internet for the functions or formulas and find out how it works. I do that all the time. There are very credible websites on the net where you can find everything you want to know about Excel. The question is, do you know what to find to get to that solution. There are times when I encounter difficulty in solving my Excel problem because I do not know how to start searching. And the cause of it is that I do not know that such as solution exist or certain issues can be resolved using a particular approach. As such, it is important that you build a strong foundation in Excel. With a strong foundation, you can then make Excel work for you and not the other way round. I have come up with a course containing business case studies and how to solve these business case studies using various combination of tools and techniques. This course was orginally a classroom course. To my surprise, it was very well received and my participants are referring their friends and colleagues to attend my course. As such, I decided to create this online course so that I can share my expereince with you. My Excel test is actually the first case in my course. Take the test and you will know what you are missing.

Here is the link which will share with you my story on using Excel to improve my quality of life. If that is what you are looking for, click on the link http://business-excel-training-course.com/online_course.php now!

How to calculate the number of working days between 2 periods

2006-05-22T09:38:00.000+08:00

Excel has a worksheet formula called Networkdays. It's purpose is to calculate the number of days between 2 periods minus Sat and Sun in that period. What's more, you could even exclude holidays in your computation. Assuming that you have a holiday in that week (between Mon to Fri), the formula will return only 4 working days for that week. To find out more about this function, visit http://www.advanced-excel.com/date_functions.html#networkdays

Pivot Table features

2006-05-05T00:52:00.000+08:00

Pivot tables was one of the great functions ever put into Excel. Its drag and drop function make it so easy to use that minimum or no training is required. A demo is what it takes to use the pivot table. That is for the user. Training is however required to set up the pivot table and to learn about the options to make it easier for the user to manipulate the pivot table.
Another key feature about the pivot table is the ability to filter the records conveniently. For example, users can filter the sales data to look at the records for a particular month at the click of a button.

The info can be presented in multiple levels. For eg., U can present the details in product group, followed by product name, then product dimension.

Like Crystal reports, Pivot table can also retrieve data from desktop databases (e.g. Access, DBase IV) and database servers (sql servers, mysql servers). While pivot tables’ capability is not as powerful as reporting tools such as Crystal reports, impromptu, business objects, it is able to meet the needs of most users at no cost to the user since Excel is a basic office productivity tool ...... more details

Payback Period

2006-04-28T01:20:00.000+08:00

I was conducting Excel training a few weeks ago when one of the participants was asking me how to use Excel to calculate pay back period. He asked specifically for that because Excel has provided a complete set of formulas for financial calculations. This includes formulas to calculate Net Present Value (commonly known as NPV), Internal Rate of Returns (IRR), Present Value (PV), Future Value (FV), etc.

Financial formulas are commonly used to evaluate business investments, as to whether it is viable to invest in a particular project. What is unknown to most people is that these financial formulas could be used by individuals as well. One example is in evaluating insurance policies. I thought of using insurance policy because my participants are mostly working in other functional areas and would be less interested in the solution if I were to use a business scenario to present the solution on the payback period. Using an insurance policy is helpful in arousing the interest of the participants because they are likely to purchase or have purchased some insurance policies.

Click here to see the solution

Remove Duplicates using Excel advanced filter

2006-04-19T00:43:00.000+08:00

Using advanced filter, we can get the list of unique records from the data list ...
click her for more details

Using the Subtotal Function in Excel

2006-04-08T23:57:00.000+08:00

A step by step guide in using the subtotal function in Excel. Explanation are also given in the guide. http://www.advanced-excel.com/subtotal_function.html

What is excel used for in business

2006-04-04T00:42:00.000+08:00

The following link talks about how microsoft excel is used in businesses. http://www.advanced-excel.com/what_is_excel.html

All about Vlookup

2006-03-09T00:44:00.000+08:00

Vlookup function is one of the most useful functions in Ms Excel. Click here to find out why

Separate the text into two lines in a cell in Excel

2006-03-06T00:20:00.000+08:00

Here is the solution to separate the text into two lines in a cell in Excel

Permalink -- click for full blog

Excel Video

2006-02-17T11:18:00.000+08:00

When I write for this blog and other excel webpages, I always wonder if you understand what I wrote. In the first place, you have to understand what I write and then, you would have to translate those words into images so that you could appreciate what I am trying to tell you. It must have been pretty painful for you.

But now, I have the solution. That is to produce video. In this way, I could explain what I am doing and you could watch the effects in the video. I am sure you would apprecate the tips and tricks better than what you are doing now. It is like you are in a training course where the training explains the steps and you watch it come true. This means that you can remember the steps by listening and seeing. Here is one of my recent works http://www.advanced-excel.com/find_blank_cells.html. Enjoy.

About Vlookup

2006-02-13T16:19:00.000+08:00

Assuming that I have 2 tables. One table contains a list of invoice numbers. Another contains another list of invoice numbers with the second column containing the value of the invoice found in the 1st column.

I try to set up a vlookup formula in the cell next to the invoice number in tbale 1, to look up the invoice number in table 2 based on invoice numerb given in table 1. If the invoice number is found, I want the invoice value (residing in column 2 table 2) to be returned in the cell. In some isntances, the invoice values were returned the right results but on other times, it gives me an error syntax "#N/A". When I check table 2, the invoice number exist but the invoice value was not returned. What could have gone wrong?

To use vlookup to lookup for a particular value/text in another table (Table 2 in this case), you must make sure that the format is the same as that given in Table 1. If the invoice number in Table 1 is a number, while the invoice number in table 2 is a text, vlookup will return a "N/A" results indicating that the invoice number cannot be found. As such, before you use vlookup formula between 2 tables, you must make sure that the format for (the invoice numbers) in table 1 and 2 are the same. Only then can Excel find the value and return the results you are look for.

Converting Numbers to Text

2006-02-07T17:22:00.000+08:00

You have an Excel worksheet that contains the phone numbers of suppliers and customers. You would like to convert them to text so that you could export these numbers into another IT system. What should you do?

Most users would simply highlight the range of numbers and then format these cells as text. They thought that by doing so, the numbers are all converted to text. THis is not so. When the cells are formated to text, the numbers in these cells remain as numbers. To convert them to text, you need to use the Excel worksheet function called "Text". Assuming that you have a cell in A1 that contains the number 5. To convert it to text, you need to enter the formula =text(A1,"@") , including the "=" into the cell say B1. The number 5 will become a text and when you try to sum up this value with others, it will not included.

Convert text to number

2006-01-24T16:26:00.000+08:00

There are times when you receive a set of records in a worksheet and they are presented in text format. As such you are unable to use these text formatted numbers to perform calculations. In this case, you would need to convert them into numbers.

How then can we convert the text formatted numbers into real numbers that can be used for calculations?

Here is a function that will achieve what you are looking for. It is called the value function. Assuming that you have a text formatted number in A1. To convert it to a number, you just have to type in the formula say in A2 "=value(A1)". The text formatted number would be converted to value and ready for you to perform calculations.

Pulling out text from a cell

2006-01-16T23:38:00.000+08:00

There are 3 functions that will allow you to pull out text within a cell. These 3 functions will help to pick up certain details that are necessary foranalysis or presentation.

For example, you have a column of text which shows the currency denomination and the amount :

SGD 1000
USD 3203
GBP 342
Yen 543334

In our case, we want to know the denomication of the currency used. To do that, we could use the left function to pull out the first 3 characters in the cell. Assuming that the first cell is in A1, the formula would be written as follows "=left(A1,3)" (exclude the "). By doing so, Excel will pull out the first 3 characters of text from the cell. The results would show SGD, USD, GBP, etc..... RIGHT Function works the same way as LEFT function except that the counting starts from the right.

Mid works in a similar way except that it is capable of pulling out text in middle of a phrase that is in the cell. For example, Cell A1 contains the phrase "Excel contains a lot of advanced function". To pull out the string of text say "contains", you can use the following formula "=mid(A1,7,8)" The formula will pull out the string of text starting from the 7th position and 8 characters from that position.

Have a blessed week ahead.

Making full use of the toolbar

2006-01-06T22:34:00.000+08:00

When you install Excel, 2 toolbars (standard and formating) will be presented in the application. You should see something like this.

Tool_Bar_Original

The problem with this presentation is that the icons in both toolbars are not fully displayed. In this case, it would have been difficult for you to know all the shortcuts you could use (with the icons) since you can't see them. To make full use of the icons given in these 2 toolbars, you need to separate them. Here is how:

1) Move your mouse cursor along the icons in the toolbars from the left to the right. As you move to the right, the cursor will become a shape with 4 arrows pointing in 4 different directions.

Tool_Bar_Move

2) When the shape appears, click and hold onto the left mouse button and move the tool bar slight downward. The 2 toolbars will be presented in 2 rows and you would be able to see the full range of icons for the 2 toolbars.
3) Let go of the mouse button and you are done. You should be able to see your 2 toolbars presented as follows:

Tool_Bar_Final

Have a blessed weekend.

What is the fastest way to do this?

2006-01-05T01:26:00.000+08:00

Happy New Year to you. I wish you and your family a blessed 2006.

My apologies for not updating the blog for such a long time. The last 2 weeks was extremely busy for me as I rush for a number of my projects. I received a query and thought it would be good to share the answer with all of you. The question is as follows:

What is the fastest way to do this: check the cells for zero and delete the row if it is zero.

Solution
1) Highlight the list in the Excel Worksheet.
2) Go to Menu and select Edit, Find or use "Ctrl F" (shortcut key)
3) Type in the number "zero"
4) Click on the "Find ALL" button. Make sure that the option exact match is selected. This is to make sure that MS Excel does not return those cells with zero such as 10, 20, 30 , etc.
5.)Select the list by
5a) Go to the first item in the list.
5b) Hold on the shift key and click on the last item in the list.
5c) You have highlighted the entire list (See diagram below)

6) Close the find and replace dialog box.
7) Delete the rows using the following command. Edit >Delete and select entire row and click Ok.

The rows with zeros are deleted.

That's all for today. Drop me an email if this has been useful to you.

Cheers.

The Top Ten secrets of Excel

2005-12-23T01:45:00.000+08:00

Hi all,

My apologies for not updating the blog for such a long time. I have been overwhelmed with work that I could not find the time to provide new information to you. It is quite coming up with topics out of the blue. As such, I appeal to you to send me some of your Exel-related problems and I will solve it FOC. The condition is that I could share it with others on the blog.

Last week, I have attend a live telecast called the "Learn 10 Secrets About Excel" You can view it online @ https://www118.livemeeting.com/cc/mseventsbmo/viewReg or download the whole e-book at a cheaper.

A quick way to copy formula down hundreds of rows.

2005-12-17T01:59:00.000+08:00

If you have 2 columns with 1000 rows each (say column A and B) and you created a formula in the first row (e.g. in column C). In most cases, you would have copied the formula throughout the 1000 rows by dragging the formula down. And one problem you faced is that you would take quite a while to drag to the 1000th row. In addition, you are likely to drag beyond the 1000 rows and have to back track.

Now, there is a simpler way. It just take 2 seconds to copy the formula down 1000 rows, or even 10000 rows. What you have to do is to move your mouse cursor to the bottom right corner of the formula cell in column C until a cross appears. Then double click on your left mouse button. Excel will make reference to the cells to the left of the formula cell (Column B in this case) and copy the formula all the way down to the last row (reference to column B). And you are done.

Enjoy your weekend.

The power of Pivot Table (Part VIII)

2005-12-16T00:30:00.000+08:00

My apologies for not blogging for the past 2 days. I have to attend a conference on coporate planning and was busy preparing for my presentation for the conference. I was also trying to meet some project schedules.

I guess this should be the last posting on Pivot Table. There are too many things to cover on Pivot Table that I can spend 2 full days sharing with you all that I know. Having said that, I didn't want to bore you with more details until you have played around with those that I have posted in the last 2 weeks.

Do you know that Excel Pivot Table can give you the raw data that make up a particular result in one single step?
For example, if you are to double click on any one of the Sum of Amount cells, Excel will present the string of records that make up that number in another new worksheet. It will give you all the other details including fields that are not even presented in the pivot table. This is a wonderful function because it allows you to drill down to the most basic details. I don't think crystal report or Business Objects provide that. I may be wrong though.

The power of Pivot Table (Part VII)

2005-12-13T02:03:00.000+08:00

Let's have something simple today. Now if you have a pivot table that shows two attributes on the left column of the pivot table, i.e. Customer Name followed by Promotion as per our pivot table we created in PART V. Do you know that you could hide the details such as those under promotion?

What you need to do is to double click on a particular customer name and the promotion details are hidden.
To unhide the details, double click on customer name again and the details under promotion appear.

The power of Pivot Table (Part VI)

2005-12-10T00:07:00.000+08:00

First, let us recap what we have covered so far.

1) We have learnt how to create a pivot table.
2) Format the results
3) Present the company sales results by customers, by promotion and by salesman.

I mentioned in point 3 that the sales results were company's results. What if you want to find out who are the key customers in a particular category or division? Here is how:

a) Move your mouse over the pivot table.
b) Click on the right mouse button and select wizard.
c) When you are brought back to Step 3 of 3 of the wizard, click on the layout button.
d) Notice on the top left hand corner that say page? Drag and drop the field call "division" into the page area.
e) Click Ok to get out of the layout dialog box and click finish to complete the wizard.
f) On the top left hand corner of the pivot table, you will see the field "division". With this, you can select the desired division and the data are filtered to show only the top selling customers by promotion by salesmen for the that division.

I hope I have amazed you with what Excel can do. Please come back if you want to discover more. Do come back if you are still not convinced about the power of Excel.

Have a blessed weekend.

The power of pivot table (Part V)

2005-12-09T00:26:00.000+08:00

If you have followed through my recent blogs on The power of Pivot Table Part I to Part VI, you would have formatted the pivot table in a presentable format with the customers ranked by the total amount they have purchased from the company in descending order. The pivot table would have displayed which salesmen have sold products to the customers.

You may find that the above format is not sufficient for you to make decision on how you should classify the customers. You wonder whether the top customers are purchasing more of promotion items or normal items from you. Is pivot table able to do that? The answer is yes and here is how:

1) Move the mouse pointer over the pivot table.
2) Right click the mouse button.
3) A pop up menu will appear. Look for wizard and select it.
4) You are brought back to the step 3 of the pivot table wizard. Click on the layout button.
5) Now drag the field "promotion" from the list to the column area right below the customer name.
6) Click Ok and finished.
7) You should see the field "promotion" appearing on the left side of the "customer name"
8) Move the mouse button over the the right border of the row label as shown in this diagram.
9) Left click on the mouse button.
10) All the sub-total would be selected.
11) Highlight the sub-total with a color. For me, I would choose yellow.

Now you are able to make more informed business decisions with the additional dimension added.

Note: You can add as many dimensions to the column or row area. But remember that having more may not be better. Try not to micro analyse.

Take care and God Bless.

Back to The power of Pivot Table (Part IV)

2005-12-07T21:49:00.000+08:00

Now that you have formated the results (Sum of Amount) for the pivot table, you might want to know that you could also rearrange the customers within the pivot table by ascending or decending alphabetical orders. That is not all. You can also arrange the customers list based on their purchases (ascending or decending order). What's more, you could even do a filter such that only the top x or bottom x number of customers are displayed in the pivot table.

Interested to find out how? Read on.

1) Double click on the grey box/button on the top left corner of the pivot table with the label "Customer Name"
2) A dialog box called Pivot Table Field wil appear. Click on the Advanced button.
3) Another Dialog box called PivotTable Field Advanced Options will appear.
4) Under the AutoSort Option, you can select Manual (Arranging the customer based on your own preference), ascending or descending order.
5) If you click on ascending or descending order, you would be able to choose the sorting preference, By customer name or by Sum of Amount (Purchases)
6) You can activate the Top 10 Auto Show by selecting the "on" option. Click on the drop down box and choose bottom if you decided to show the bottom x results in the pivot table. X is determined by the box next to the top/bottom selection.
7) If you have more than one result in the pivot table, you could select the field which the top or bottom x results should be based on.
8) Once you have made the relevant selection for the pivot table, click "ok" twice to return to the pivot table.

Task Completed.

God bless you with a wonderful day ahead.

If you want to learn about pivot table hands-on and are in Singapore on 12 and 19 Dec, you may want to consider signing up for my course. It will benefit those who are interested in business intelligence. Click here for more details.