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.

* * *