Several Hours vs One Minute

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

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

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

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

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

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

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

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

1. capture the details of the proucts in and out of the store or warehouse.
2. 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

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

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

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

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

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:

1. Select the list of numbers you want to convert.
2. Activate the Text to Column function.
3. In the first step, select delimited.
4. Click next until you are at the last step.
5. Select the format as Text and click finished.
6. 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:

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

MSQuery

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

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

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

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

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

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

How to use data validation

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.