3 Important Attributes of an Excellent Budgeting Tool

The 3 important attributes of an Excellent Budgeting Tool are:

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

Find out more from this budgeting tool write-up.

Excel 2007 Conditional formatting bug

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

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

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

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?

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

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

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.