Financial Quarters

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

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?

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

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

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

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

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

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

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

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?

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

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

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.