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).

Shortcut with CTRL & SHIFT Key

Let share about shortcuts

Practicing the CTRL SHIFT shortcuts will save tons of time:


CTRL SHIFT : Enters the time
CTRL SHIFT & Creates an outline box around whatever you select
CTRL SHIFT _ Removes the outline box around whatever has an outline
CTRL SHIFT * Highlights all the text surrounding the cell filled with data or numbers
CTRL SHIFT ! Applies the number format to selected cells
CTRL SHIFT % Applies the percentage format to the selected cells
CTRL SHIFT # Applies the date format to the selected cells
CTRL SHIFT @ Applies the time format to the selected cells
CTRL SHIFT ( Unhide columns that are selected
CTRL SHIFT ) Unhide rows that are selected


Here are a few other CTRL shortcuts that can save time:

CTRL ; Enters the date
CTRL 9 Hide Rows selected
CTRL 0 Hide Columns selected
CTRL D Fills down current formula into all adjacent cells; this avoids copying first
CTRL K Insert a hyperlink

Continue_Create List of Day

Here come the picture of previous tips for batter understanding.

How to build List of Day (Sun, Mon, Tue) continuously

Happy New Year 2009... there will be lots more post the year.

Recently I am doing my macro project on Yearly Attendance Leave Chart (in Ms Excel). One of the logic is to assign day for each date and to identify Saturday and Sunday, so that it will be highlighted as different color in the chart. The issue now is how to identify Sunday & Saturday the fastest way instead of assign it manually. Here is the way;
1. Build list of date
i. Key in 1st date ex 01/01/09 (DD/MM/YR) - cell(A2)
ii. Key in 2nd date ex 02/01/09 (DD/MM/YR) - cell(A3)
iii. Select both cells, place the cursor at the right corner of the selection till the cursor mark as
"+" and drag cell downwards.
2. Build list of day
i. Key in 1st day ex Thursday - cell(B2)
ii. Key in 2nd day ex Friday - cell(B3)
iii. Select both cells, place the cursor at the right corner of the selection till the cursor mark as
"+" and drag cell downwards.
tips: Fastest way, once (+) appear, double click the (+) sign, the list will be copied to the
following cell following the trend of selected cells.

Next posting : Video how to perform this post.