Entering percentages

How to enter percentages in Excel?
When I format cells as percentages, the exact percentages don't show up. They get multiplied by 100.

To convert a number into percentage,
RIGHT CLICK->FORMAT CELLS->NUMBER->PERCENTAGE and CLICK OK.
You can also specify the number of decimal places to follow percentage.

Tip:

It is always advised to format cells as percentage before you enter values. This is because if you enter your values and format it to percentage, they get multiplied 100 since cent percentage is equal to 100. For example, if you enter the number 4 and format cells as percentage, you get the value 400%. But if you format the cells as percentage and enter 4, you get the value 4%.You can also enter decimal values if you format the cells prior to entering values.

Display formulas

How to quickly find out the cells that contain formulas?
Shortcut to quickly display formulas.

To quickly display formulas in worksheet, press CTRL + ~
To display values, press CTRL + ~ again.

This would display all cells that contain formulas. If your worksheet is protected then pressing CTRL + ~ would not display the formulas but cells containing formulas can be identified by an increase in column width.

Delete rows or columns with zero values

How to delete rows that contain zero values?

Sometimes you may be having a list of names and corresponding values. Certain names may contain zero values and to delete those names one by one, would rather turn out be a nightmare task. To do this within a few clicks, just follow the steps:

• Goto DATA->FILTER and select AUTOFILTER
• You would see a arrow mark in all the columns. This is the FILTER button
  
• Now go the column that contains zero values and click the FILTER button.
• The FILTER button would display the list of all values in the column in a drop-down box. Now select 0 from the drop-down box.
• The FILTER button would turn into BLUE color and would display all the zero values. Now RIGHT CLICK and select DELETE ROW.
• Click the FILTER button again and select ALL to display the values again.

Convert grams into kilograms

How to convert grams into kilograms?
How to convert kilograms into grams?

Assuming that you have typed your data in grams and your boss wants them in kilograms and that too in short time. To make things worse, your data is quite big to effect changes.
No problem, you can do this at a flash. Just follow the steps below:

1. Type 1000 in an empty cell
2. Right click and select copy (or press CTRL+C)
3. Now select the range of your data (assume that your data in grams consists of the range A1:A120").
4. Right click and select PASTE SPECIAL.
5. Select the DIVIDE option from the OPERATION group box.
6. Click OK and your job is done.

To convert kilograms into grams repeat the above steps and the select the MULTIPLY option instead of the DIVIDE option from the OPERATION group box. You can use the same tip to convert any unit into another. Just use the appropriate option.

Quick Minimum

How to quickly find the minimum value from a set of values in Microsoft Excel?


To quickly find the minimum value from a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select MIN (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to count a range of cells, count the numbers in a range of cells, to find the average for a range of cells, to find the maximum value from a range of cells, to sum a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Quick Maximum

How to quickly find the maximum value from a set of values in Microsoft Excel?


To quickly find the maximum value from a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select MAX (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to count a range of cells, count the numbers in a range of cells, to find the average for a range of cells, to sum a range of cells, to find the minimum value from a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Quick Average

How to quickly find the average from a set of values in Microsoft Excel?


To quickly find the average from a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select AVERAGE (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to count a range of cells, count the numbers in a range of cells, to find the sum for a range of cells, to find the maximum value from a range of cells, to find the minimum value from a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Quick Count

How to quickly count a set of values in Microsoft Excel?


To quickly count a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select COUNT (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to sum a range of cells, count the numbers in a range of cells, to find the average for a range of cells, to find the maximum value from a range of cells, to find the minimum value from a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Quick Count Numbers

How to quickly count the numbers in a set of values in Microsoft Excel?


To quickly count numbers from a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select COUNT NUMS (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to count a range of cells, sum a range of cells, to find the average for a range of cells, to find the maximum value from a range of cells, to find the minimum value from a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Quick Sum

How to quickly sum a set of values in Microsoft Excel?


To quickly sum a set of values in Microsoft Excel,

• Select the cells you want to sum (Hold CTRL key to select non-adjacent cells)
• Right click the status bar and select SUM (This is the default option)
• Now check out the sum of the numbers you selected in the status bar.

You can use this same tip to count a range of cells, count the numbers in a range of cells, to find the average for a range of cells, to find the maximum value from a range of cells, to find the minimum value from a range of cells. Just select the appropriate option from the status bar.

Note:
You can select only one option among Sum,Average,Count,Count Nums,Max,Min. But in Microsoft Excel 2007, you can use all the options by clicking them in the status bar.

Cut and paste formulas

Don't cut and paste formulas

As a general rule, don't cut and paste formulas. This is because if you cut and paste a formula the relative references don't change. Just study the example below for better understanding

For simplicity assume you have the values 4 in cell A1 and 5 in cell B1. You enter the formula =A1+B1 in cell C1. If you normally copy the formula from C1 to C2, then the formula would automatically change to A2+B2 in cell C2. But if you cut the formula from C1 and paste it to C2 then the formula would remain the same. So, the next time you cut and paste a formula be sure of what you are doing.

Entering 00

How to enter the value 00 in Excel?
How to precede a number with zeroes?
How to format numbers as text?


By default, you cannot enter the number 00 in Microsoft Excel. (If you don't believe it, try it). Neither you can precede a number with 0 (You cannot enter 070 in Excel)

To do this,

• Right click and select Format cells (You can press CTRL+1)
• Select the NUMBER TAB (By default, this should show up)
• In the CATEGORY list, Select TEXT and click OK

Now you can enter 00 or 000 or precede any number with zero.

Note:
If you add two cells that contains numbers as text, it would add up. So you can have some cool calculations too.

Hiding zeroes

How to hide zero values in Excel?
Zero values don't show up in Excel.What to do?


To hide zero values in Microsoft Excel,

• GoTo->Tools->Options
• Select the View tab
• Now uncheck the box next to ZERO VALUES at the bottom.
• Zero values wouldn't show up.
• To show zeroes, check the box
  

Note:
By default, zero values are shown in Microsoft Excel.
This applies to all versions of Microsoft Excel.
When you use this option, zero values don't show up in the worksheet as well as in Print.
Though Excel hides zeroes, it is visible in the formula bar

Time and date

How to quickly enter date and time in Microsoft Excel?

To quickly enter date, press CTRL+;
To quickly enter time, press CTRL+SHIFT+;

This works in all Microsoft Excel versions(97,2000,2003,2007)

The format for date and time would depend upon your regional settings.

You can format your date and time in your own language. To know more,click here