Conditional Formatting Stop if True

Friday, October 10th, 2014 by admin admin

In conditional formatting, we can set more than one rule to format the cells. If the rules do not conflict with each other, then all is well. But what happens when more than one rule turn TRUE? According to Microsoft website, the earlier rule that turns takes precedence over the latter rules. So if the earlier rule format the cells Red and there comes a second rule that formats the cells Blue, the cell will remain as Red.

Then in this case, what is the purpose of the option Stop IF True?

That option works if you have a different format for the 2 rules. If the earlier rule format the cell Red and the latter rule format the Font colour to Blue, both rules will be applied. This is different from the earlier example when the rules are using the same formatting, change the cells colour. In this example, the 2 rules apply different kind of format (one is cell colour and the other is font colour) and therefore, both are applied. If you do not wish to apply the latter rule, change in font colour when the earlier rule turn TRUE, then check the option STOP IF TRUE on the earlier rule. This will make the conditional formatting stop checking the latter rule and therefore, format font colour will not be applied when the earlier rule (format cell colour) turns TRUE.

For a more detailed explanation, please refer to this write-up on Microsoft website http://office.microsoft.com/en-sg/excel-help/manage-conditional-formatting-rule-precedence-HA010342674.aspx

 

Splitting Fixed Assets into multiple rows

Tuesday, September 23rd, 2014 by admin admin

A past participant was working on a Fixed Asset Register and one of the tasks she is required to do is to split up the Fixed Assets into multiple rows, based on the quantity. Given below is an example of what has to be done.

a) A00016 Well Management Pte Ltd 10297 AM00000000002685 4.00 APOT-0150

19-Dec-01

369.48 369.48 0.00
b) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
c) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
d) A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00
e)A00016 Well Management   Pte Ltd 10297 AM00000000002685 1.00 APOT-0150

19-Dec-01

92.37 92.37 0.00

Meaning I need to split item (a) of qty 4 equally into (b) to (e) of qty 1 and cost p/qty in each line.

This would have been quite straight forward if the quantity for all the Fixed Assets are the same. The problem is, they are not. Therefore, we have to use VLOOKUP with approximate match to determine the number of rows required for each line of Fixed Assets and populate them into the relevant rows. The worksheet function COlUMN is used to help us identify the column index number for the VLOOKUP formula. And in this example, I also did a quick demonstration on filling up a range to a specific number almost instantly. Watch the video below to learn the application of each function.

If you have benefitted from the video, we would love to hear from you. Let us know which part of the solution is useful to you and how it has benefitted you.

 

 

New to Excel

Monday, September 1st, 2014 by admin admin

If you or your employees do not know how to use Excel, the first thing you should learn is to entering data into a cell. It may seem obvious to you but we do find a number of people learning the wrong way when it comes to entering data into a cell. The wrong way is to double click on the cell or clicking on the formula bar before entering the data. The right way is to just select the cell you wish to enter the data in and just type. When completed, confirm the entry by hitting the ENTER key. DO NOT click another cell to confirm the entry. You will find yourself not able to perform special entries when you confirm the entry using the wrong way.

Next you should learn is cell formatting i.e.

1) Number Formatting

2) Font Formatting

3) Border Formatting

4) Fill or Cell Colour Formatting

 

 

Quick Access Toolbar icon and Macros

Tuesday, August 26th, 2014 by admin admin

In today’s course, one of the participants asked if he can attach macros to the ribbon so that he can access and run the macro in one click. The answer is yes but not in the ribbon. It is in the Quick Access toolbar. Microsoft does not allow the ribbon to be changed in Excel 2007. To learn how to add the macros into the Quick Access Toolbar, watch the video below:

 

2 “bad” habits to avoid in Excel

Wednesday, July 23rd, 2014 by admin admin

During our Excel courses, we notice some participants have these 2 habits which cost them dearly (time savings) when they worked with Excel.

One of them is double clicking on the cell before they make entries. Because of this habit, they are not able to make full use of Goto Special, a time saving function.

The other habit is confirming cell entries or changes by clicking another cell instead of using the ENTER key. Because of that, they always find themselves getting an answer that they do not expect.

 

 

Calculate YTD Gain

Tuesday, July 22nd, 2014 by admin admin

Michelle has a template that lists the investments made by her clients. On a monthly basis, she has to check the prices of these investment and calculate the paper gain for up to 3 years. Currently, the gains are calculated manually and she would like to find out if the calculation can be  done automatically by Excel. Watch this video and find out how to arrange the data and apply the appropriate formulas to get the answers Michelle wanted.

excel files now open in Kingsoft

Tuesday, July 8th, 2014 by admin admin

After installing Kingsoft, I found that my Excel files now open in Kingsoft. If that happens to you too, you can follow the instruction on this page to restore back the setting. http://www.ksosoft.com/office/217-how-to-set-kingsoft-office-as-default-office-program.html#.U7tKXGCwrIU

 

Short cut key for paste value

Friday, July 4th, 2014 by admin admin

In today’s excel course Hidden Secrets In Data Analysis with Excel, one of the participants asked if there is a shortcut key for Paste Special specifically Paste Value. Unfortunately, there isn’t. Since she has to use Paste Value frequently and there is no shortcut keys, I recommended adding an icon into the Quick Access Toolbar so that she can do Paste Value with one click without having to go to the HOME tab. Here is the recording I have done when I was sharing with the group how to do.

Checking for the existence of Autofilter using VBA

Tuesday, July 1st, 2014 by admin admin

I was goggling for the code to find Autofilter and many websites (including reputable ones) wrote that I can use AutoFilterMode to check for the existence of AutoFilter in a worksheet. But when I tried to run the code, it fails to detect the Autofilter function. The code is

With ActiveSheet
If .AutoFilterMode = True And .FilterMode = True Then
MsgBox “They are visible and in use”
ElseIf .AutoFilterMode = True Then
MsgBox “They are visible but not in use”
Else
MsgBox “They are not visible or in use”
End If
End With

I was working on Excel 2010. After searching further, I finally found the code in the blog listed below that works, at least for Excel 2010.

http://vbadud.blogspot.sg/2008/07/check-for-existence-of-filter-using.html

The code that he published is

If Not ActiveSheet.AutoFilter Is Nothing Then
‘Autofilter mode is on
End If

 

How to convert a pdf document to Excel

Friday, June 13th, 2014 by admin admin

One of the common challenges faced by Excel users is the receipt of pdf (Protable document format) documents containing tables of data that cannot be transferred to Excel easily. When you try to highlight and copy the data from a pdf document to Excel, the data is re-arranged from multiple columns into a single column, different from what was presented in the document. Purchasing an addin to convert PDF to Excel does not guarantee that the conversion is perfect. I encountered the same problem recently with an order list and made another attempt to convert the order list in pdf format to Excel and to my surprise it works! It has partly got to do with the PDF reader you are using. When I use Adobe PDF reader, it cannot be done. But when I use this free pdf reader called Foxit to save the pdf file as a text file, the layout is retained and allowed me extract the data using Text-To-Column without having to spend a single cent on a pdf converter. If you like to know how it is done, watch the video below.

 

s2Member®