What You Can Do With Excel?

With the numerous features available in Excel, you can use it widely. Some of them are:

• Creating invoice, purchase order, DO, etc
• Presenting data in different types of chart formats!
• Get the latest prices for stocks, percentage change in stock value, etc.
• Calculate a loan due date or how many days you have been alive?
• Calculate future value of your saving or amount to keep in order to get targeted amount by end of saving... through excel functions.
• And many more....
  

Those tasks can have a 'build-in calculator' that will automatically calculate the figure based on the pre-set formula with 100% accuracy. This is the biggest advantage of computer spreadsheet compare with manual when dealing with figures.

Thanks to the powerful Excel feature – Formulas and Functions! …and you can learn it in the online Excel tutorials help below.

If you have used the correct functions and formulas within the spreadsheet, the computer automatically re-calculate the end values.

Adding Visual Control To Your Report through "traffic light"

I used to deal with incoming shipment of imported parts. With current economic slow down, our warehouse storage was full, such lead to stranded container at Port. I have to monitor number of day balance before detention charges imposed, in our case we have 30 days free period.

Normally we used to just minus current date to arrival date, most of the time we only see numbers.
Recently I came across excel function i.e "Conditional formatting", from that day, it looks more colorful , more colors to show level of critical. Here how the function work.

Conditional formatting enables you to quickly and easily color coded your information based on parameter that you set. It saves time by emphasizing exactly what you want to see.

Select the cells to which you want to add conditional formatting. In our example, we selected cells F6 through F9.

Add conditional formatting

1. On the Format menu, click Conditional Formatting.

2. In the Condition area, do one of the following:

   • To change the formatting based on a value, select Cell Value Is, select a condition from the drop-down menu, and then enter a number or a formula. If you enter a formula, start it with an equal sign (=).
   • To change the formatting based on a formula that evaluates to TRUE or FALSE, select Formula Is, and then enter a formula.

   In our example, we chose Cell Value Is, selected the condition less than, and entered 5.

3. Click Format.

4. Select the formatting you want to apply when the cell value meets the condition that you specified, or when the formula value returns TRUE.

   In our example, we changed the Cell color to red & the text to bold.

5. Click OK.

6. To add another condition, click Add, and repeat steps 3 through 6.

7. In our example, we added a second condition stating that if the Cell Value Isless than 10, the cell will be colored to yellow.
   

This is the result.

We can have up to three condition.

So, have a try and give your report some value.

Remove Blank Rows

This will show you how to remove blank row, this normally happen when you downloaded data (such as from SAP system) to Ms Excel
First
Highlight your range and go to Edit>Go to>Special and select "Blanks"
Then
"Ok" now go to Edit>Delete or Ctrl+Shift+= (equal)
and then select "Entire row" from the "Delete" dialog
and click "Ok".

Adding Hidden Text to Formulas

Imagine you have a formula like: =$2018+$1056-4*$120.

When you initially wrote it you knew what each number represented, but you come back later and can't remember.

Add a hidden note to your formula by using the N() formula

i.e. =$2018+$1056-4*$120+N("My Wage+Bonus-4 weekly loan repayments").

The N() function will convert text to zero.

But be aware not to put * instead of + infront of N i.e *N(" ") since this will be return the amount as zero (similar like numbers multiple with zero value).