How to group Excel columns or rows

Grouping Excel columns/rows enable to hide/collapse, or unhide/expand selected columns or rows by one click. This is useful in worksheet where hide/unhide actions is done frequently on the same columns or rows.

The following instructions will show columns grouping in Excel 2010 however the same method is also true for rows grouping, just in step 1 begin with selecting rows instead of columns.

1. Select the columns to be grouped

2. Click Data TAB then click Group

Click the picture to enlarge

The collapse/expand button will be created

Click the picture to enlarge

*** Done ***

Clicking now on the new created '-' button will collapse columns C,D and E, the '-' sign will change to '+'.

Clicking on the '+' button will expand columns C,D and E.

* * *

Calculating Payment Due Date After EOM

Here are two options to calculate payments due date after EOM (End Of Month).

Click the picture to enlarge

Reference Date - Date from which days count starts
X Days - Number of days to count after EOM

First Option (as in Excel row 5 in the above picture)
E5 = Due Date =DATE(YEAR(A5),(MONTH(A5)+QUOTIENT(C5,30)+1),DAY(IF(MOD(C5,30)=0,1,MOD(C5,30))))
A5 = Reference Date
C5 = EOM + X Days

All calendar months are counted as having 30 days.

The X Days is counted from the 1st day of the month follows the EOM of Reference Date.

If X Days is a round number of months to count, such as X=30 or 60 or 90 or 120 etc. then the Due Date is set to the 1st day of the month coming next after the last counted month.

For Example:
The input:
Reference Date is 15/02/2011 (15th/Feb/2011)
EOM + X Days is 30
  
The result will be: 1st/Apr/2011
EOM is end of February so month counting will start from the month of March
X Days = 30 means to count round number of 1 calendar month.
March will be counted as 1 calendar month although in March there are 31 days then the due date is set to the 1st of the next month which is 01/04/2011 (1st/Apr/2011).

If X Days is not a round number of months such as X=35 or 63 or 97 or 122 etc. then the Due Date is set to the day which is the number of days left after counting whole calendar months. In this example the days left are 5 or 3 or 7 or 2 though the Due Date in the month coming right after the last counted month, will be on the 5th or 3rd or 7th or 2nd day of that month accordingly.

For Example:
The input:
Reference Date is 15/02/2011 (15th/Feb/2011)
EOM + X Days is 35
  
The result will be: 5th/Apr/2011
EOM is end of February so the count will start from the month of March
X Days = 35 means to count 1 whole calendar month and after that additional 5 days.
March will be counted as 1 calendar month and after that the 5 days will be counted in April so the due date is set to 05/04/2011 (5th/Apr/2011).

Second Option (as in Excel row 7 in the above picture)
E7 = Due Date =DATE(YEAR(A7),(MONTH(A7)+QUOTIENT(C7,30)),DAY(1))+C7-1
A7 = Reference Date
C7 = EOM + X Days

The X Days is counted from the 1st day of the month coming after EOM (End of Month) of Reference Date.

In this option the days count doesn't have any interpretation of counting whole months but just counting days. 30 days wouldn't mean 1 whole month, it would mean 30 calendar days. So when counting 30 days from February 1st the due date will be March 2nd since February has 28 days only.

* * *

Try to print a file but 'Save as ...' window pops up

When you click the Print button in attempt to print a file but instead of printing the file Windows display the Save as... pop up window, check that:

(a) The printer is turned ON

(b) Default printer is set. To check this,

     Click Start, Devices and Printers and see if the printer is marked with green check sign.

* * *

How to change Word 2010 Ruler display Inches to Centimeters

Click the File TAB, Options, Advance, scroll down to the Display section, choose the measurement unit in the field ‘Show measurements in units of:’ and click the OK button at the bottom of the window.

* * *

How to display Word 2010 ruler

To turn Word’s ruler ON/OFF click the ruler icon located at the upper right side of the screen below the Ribbon.

Each click on the ruler icon will toggle the ruler ON/OFF.

How to turn on Windows Games

Windows 7 comes with built in set of games placed by Windows in the games folder.

When all games are enabled, the games folder should look like this:

To open the games folder: 

• Click Start 
• In the search field type games. Search result list will pop up.
• Click Games Explorer, usually at the top of the list
  and the games folder will open.

If the folder doesn’t look like the above screenshot of the games folder, probably the gameson the computer were turned off.

To turn on the games do the following:

1. Click Start
2. Control Panel
3. Programs and Features
4. Click the link,  Turn Windows features on or off, at upper left side
You may be asked to enter administrator password.A window with list of programs will open.

5. Click the checkbox near the Games to select it
6. Click OK.

* * *

Save Excel 2010 file in Excel 2003 format

When the default preference is to save all Excel 2010 workbook in file format of Excel 2003, change the ‘Save files in this format:’ on the Save sheet options, to Excel 97-2003.
File extension of Excel 2003 is *.xls
File extension of Excel 2010 is *.xslx

Click File TAB, Options, Save, Select the preferred format in the ‘Save files in this format:’ field.