Search This Blog

Search Engine

Custom Search

20140529

How to Set Print Area in excel?

Excel Method - Set Print Area in excel
Level  - V.Imp
Useful for - All
Type - Method















How to SET PRINT AREA in excel?
Steps are given as below : -
Open excel file
Select the area which you want to print
Go to "Page Lay Out" near to "Home" & "Insert" tabs
Click on "Print Area"
Click on "Set Print Area"
Now type CTRL+P ie type CTRL and P Button at same time
Click on Preview
Only the selected area will appear in Print Preview
Get the print by clicking on the PRINT Button

Border adjustment in print preview & fit to 1 page & 2 page in excel

Excel Method - Border adjustment in print preview & fit to 1 page & 2 page
Level  - V.Imp
Useful for - All
Type - Method



How to fit the print to 1 page & 2 page in excel

Steps are given as below : -
Type CTRL+P ie type CTRL and P Button at same time
Click on Preview
Go to Page Set Up
Go to Page Layout bar
In the Page dialog box put - Scaling section - put a dot on "Fit to __ page wide by __ page tall
If you write 1 for page wide & 1 for page tall then the print gets fitted in 1 page
If you write 1 for page wide & 2 or 3 for page tall then the print gets fitted in 2 & 3 pages accordingly. However width gets fitted in one page only

For border adjustment
Steps are given as below : -
Type CTRL+P ie type CTRL and P Button at same time
Click on Preview
Click on Show Margins
Adjust Margins as per your convenience

How to repeat rows while printing in excel?

Excel Method - How to repeat rows while printing?
Level  - V.Imp
Useful for - All
Type - Method




Row to repeat while printing
Steps are given as below : -
Go to Page Layout bar
Go to Print Titles
Go to Sheet-Rows to repeat at top
Select row - whichever you want to repeat
If you select 4 rows then 4 rows will get repeated at every print of page
If you select only 1 row then only 1 row will get repeated at every print of page

20140528

How to print page nos in Header & Footer in excel?

How to print page nos in Header & Footer in excel?

In advanced versions of excel we directly have option
Go to Excel file which you want to print
Type CTRL+P
Click on Preview
Click on Page Set Up
Click on Header Footer
Click on either in Header or in Footer wherever you want page no. option as "Page 1 of ..."
In older versions this facility of the wordings "Page 1 of..." is not there.
In such cases Go to Footer or Header (as per above mentioned path) &
Wherever you want page nos., type the below wordings
in left, right or middle tab
of Footer or Header
- Page &[Page] of &[Pages]

That way if you want only page no. 1,2,3 etc appearing then you can type wordings - &[Tab]

20140527

Interesting fact supporting BhagvadGita

Interesting fact of the Day!!:-)

The Bhagavad-Gita

1. ARJUN'S DILEMMA



King Dhritaraashtr said: O Sanjay, assembled in the holy field of Kurukshetr and eager to fight, what did my people and the Paandavs do? (1.01) Sanjay said: Seeing the battle formation of the Paandav’s army, King Duryodhan approached his guru and spoke these words: (1.02) O master, behold this mighty army of the sons of Paandu, arranged in battle formation by your other talented disciple. There are many great warriors, valiant men, heroes, and mighty archers. I shall name a few of them for you. (1.03-06) Also know, O best among the men, the distinguished ones on our side.

EXPLANATION

There are many people having less brains who doubt that how can a person like Sanjay sitting in a room with King Dhritrashtra see the events of the battle field. And who will like to trust him. Sage Ved Vyas had given him or more meaningfully activated remote vision of Sanjay by his penance power . As per spiritual science his desire force dominated nature & he could do things he wanted eg. Activating remote vision or giving eye sight to someone. He wanted to give this ability to Dhritrashtra but Dhritrashtra denied any type of normal or paranormal vision. On the contrary he said to give it to Sanjay. This was bcos eye-sight was something he could never get in full life time & now getting it in this old age was something unacceptable for him. Sage Vyas thought that if Dhritrashtra sees the blood shed & fury of war, may be, he might give orders to stop the war. Hence with a good-will he offered Dhritrashtra the eye-sight to which he denied

FACTS SUPPORTING TRUTHFULNESS OF SANJAY'S REMOTE VISION

1)        ttThere was/is one intellectual in modern age - Russell Targ (born April 11, 1934) who is an American physicist, parapsychologist and author who is best known for his work on remote viewing.[1]
2)      Where countries like India are having lack of interest in acquiring supernatural powers or believing them, countries like US were far ahead investing heavy amounts on such Projects – right from 2nd world war upto 1995

Have an enlightened day ahead!! :-)



How to make fonts or text slanting or vertical in a cell in excel file?

How to make fonts or text slanting or vertical in a cell in excel file?



  1. Right click cell
  2. Go to Format cells
  3. Go to Alignment
  4. Go to Orientation - this is located on right corner
  5. Move the arrow
  6. If you move the arrow to 90 degree, all fonts or text will become vertical
  7. If you move the arrow to 45 degree, all fonts or text will appear in a slanting fashion


20140526

Interesting Facts supporting BhagvadGita

Good Morning!! :-)


When the jivātmā (soul or ātman) migrates from one body to another, it draws from the previous body the five senses and the mind and takes them along with.”  - Bhagvad Gita

Explanation

 When we die, we take away our senses with us, we take our mind with us & we also take our intellect with us. Those who do not believe in a soul are “FOOLS” as per Lord Krishna. That means sense organs are not senses. Senses are made up of mind material (Manomay). Intelligence is not cerebrum. It is something abstract just like mind & senses. We are not humans with spirits, but we all are spirits presently in a human form. One who knows this is wise. Rest are either fools or ignorant ones.


Interesting facts supporting this Gita Quote

1)     Psychiatrist Ian Stevenson, from the University of Virginia, investigated more than 2,500 case studies & found that in reincarnation cases, memories are still stored. Means if a person dies & takes new birth then from the age of 5 to 15, he remembers in almost all cases, his previous life memories. That means intelligence is not cerebrum or a biological part. It is a part of soul and as per Gita this gets carried with us.
2)    Whichever person claiming they have seen ghosts say that ghost could see, speak, follow, remember the pains etc. This also proves that if biological body is destroyed, astral body, astral senses & intelligence survives & they are something different from physical body.

3)    When our eyes are closed in dream, then also we can see. We can hear in a dream, we can taste etc. This proves that senses are something different then biological sense organs.


Have an enlightened day ahead!! :-)


20140525

Format borders and apply cut or streak line on the contents in cell

EXCEL LESSON -  Format borders-Using various types of borders & fonts with streak line or cuts upon it
LEVEL - Important
TYPE - Method
MIGHT BE VERY USEFUL FOR - All



HOW TO FORMAT BORDERS

Select the table you want to format the borders for.
After selecting, right click
Go to "Format cells"
Go to "Border" - the 4th tab after Number, Alignment & Font
Based on selected style, that type of border can be applied
Based on the color, the desired color can be applied to those borders
Give OK & then the selected border style & color gets applied on the selected table's border.




HOW TO PUT A CUT OR STREAK ON A GIVEN TEXT IN EXCEL FILE

Select those excel cells where you want that the text in those cells should have a cut/streak upon them
Choose the shortcut key "CTRL + 5". i.e type ctrl button & 5 button at same time.
The text will develop a streak or cut
If u type CTRL+5 again then the streak goes away
Here only that 5 should be taken which is below F4 & F5 button.
The "5" of the Number pad of the PC or laptop does not work for this shortcut key. Please note.

Example is given below : -
Shortcut key - CTRL+5 to apply cut on a given text

20140524

Large Formula, Minimum & Maximum formula in Excel

EXCEL LESSON -  Using Large formula, Maximum & Minimum formula in excel
LEVEL - Average but very useful
TYPE - Formula
MIGHT BE VERY USEFUL FOR - in excel to pick first highest, second highest etc. values without sorting table


If there is a table with single column, it is possible to sort the table & get the highest, second highest, third highest etc. values one after another

But if sorting is not allowed, then Large Formula helps to get the desired 1st,2nd,3rd etc. highest values

If in excel file the cells,
C1=600
C2=400
C3=200
C4=1000
C5=300

Then without sorting, we can get the first highest, second highest, third highest etc. values by below LARGE Formula

 =LARGE(C1:C5,1)
 =LARGE(C1:C5,2)
 =LARGE(C1:C5,3)
 =LARGE(C1:C5,4)
 =LARGE(C1:C5,5)

These formulas will give value as below : - 

 =LARGE(C1:C5,1) = 1000
 =LARGE(C1:C5,2) = 600
 =LARGE(C1:C5,3) = 400
 =LARGE(C1:C5,4) = 300
 =LARGE(C1:C5,5) = 200

This Large function picks the value from the given  selected range of the column and puts it at a user specified location in the list

This function or formula is most useful when the values are contained in multiple columns & it is not possible to sort all columns, still we want to get the first highest, second highest, third highest etc. values

Eg.
Sales          Jan          Feb          Mar
North $5,000 $6,000        $4,500 
South $5,800 $7,000         $3,000 
East        $3,500 $2,000        $10,000 
West     $12,000 $4,000 $6,000 

Then using LARGE Formula gives us values as below : - 

1st Highest Value        $12,000 =LARGE(D24:F27,1)
2nd Highest Value       $10,000 =LARGE(D24:F27,2)
3rd Highest Value         $7,000 =LARGE(D24:F27,3)

Another way to find highest and lowest value is using Maximum & Minimum formula

Highest $12,000 =MAX(D24:F27)
Lowest   $2,000 =MIN(D24:F27)




SORTING DATA FOR A BIG TABLE IN EXCEL

EXCEL LESSON -  Sorting Data for a big table
LEVEL - Average
TYPE - Method
MIGHT BE VERY USEFUL FOR - All



SORTING DATA FOR A BIG TABLE

Sorting can be done each columnwise & the connectivity does not get spoiled by this, if all columns are selected.

However if all columns are not selected & you try to sort then in some older versions of excel, the table relativity might get spoiled


20140523

51 official words of praise!

List of the official words of praise
  1. Excellent
  2. Wow!!
  3. Tremendous
  4. Great!
  5. Neat!
  6. Awesome!
  7. Amazing
  8. Brilliant
  9. Dynamite
  10. Fantastic
  11. Impressive
  12. Incredible
  13. Outstanding
  14. Spectacular
  15. Super Job
  16. Terrific
  17. Wonderful
  18. Precious
  19. Fabulous
  20. Unique
  21. Marvellous
  22. Radical
  23. Bravo
  24. Winner
  25. Hooray
  26. Sensational
  27. Darling
  28. Bingo
  29. Stupendous
  30. Groovy
  31. Great Job
  32. Perfect
  33. Special
  34. Keep it Up
  35. Nice effort
  36. Good work
  37. Well done
  38. You are star
  39. Thank you
  40. Magnificient
  41. Super
  42. You are great
  43. Honesty
  44. Integrity and trustworthiness
  45. Pride and attention to detail
  46. Dedication and goal-orientation
  47. Analytical and strategic thinking skills
  48. Efficiency, organization, and time-management skills
  49. Economic and budgeting skills
  50. Being a team player
  51. proactive & tireless contributor


How to change column and row size in excel & how to do SORTING

EXCEL LESSON - Changing cell size, row & column height for multiple columns
LEVEL - Average
TYPE - Method
MIGHT BE VERY USEFUL FOR - All

HOW TO CHANGE COLUMN AND ROW SIZE IN EXCEL?

Select all contents in excel by using formula CTRL+A

When all columns & rows are selected, take the mouse cursor to above column headers where A,B,C,D....etc is written above white excel cells i.e. (Grey border of excel where A,B,C etc. is written on column side & 1,2,3,4...etc. is written on row side)

The mouse cursor pointer will convert into a double arrow = one vertical arrow & one horizontal bar

So when, cursor with double arrow comes, double click, to auto fit column or row

On single clicking  & adjusting width of one row or column, the same gets applied to all rows & column

When no selection is done, (i.e. CTRL+A is not used) the above rules are applied only for single row or single column



5S

What is 5S?

5S methodology

Photo Source - By Musinik (Own work) [CC BY-SA 3.0], via Wikimedia Commons

1) Sort
2) Straighten / Stabilise / Set in order
3) Shine
4) Standardize
5) Sustain


Japanese words for 5S

1) SEIRI = SORT
2) SEISO = SHINE
3) SEITON = STRAIGHTEN
4) SEIKETSU = STANDARDISE
5) SHITSUKE = SUSTAIN

Broader meanings

1) Sort = Differentiate between necessary & unnecessary things. And Throw away unnecessary things.

2) Straighten / Stabilise / Set in order = Label the necessary ones if possible in ascending order or sequence so that it is easy to find. Maximum labelling = Set in order. A place for each & every thing = Straighten

3) Shine = Cleanliness

4) Standardise = Maintain cleanliness. Or make standard reporting formats for cleanliness = Utilise checklists for above 3s.

5) Sustain = Keep habit of maintaining checklist or filling checklist on regular basis; where checklists = standardised process documented for following above 3s (Sort, Straighten, Shine)

20140522

How to get all locations in single cell in excel using access cross tab query

How to bring Multiple locations in one row in excel.

How to collate all splitted locations.


EXCEL WORKING  - PART 1 

1)      Open excel file
2)      Paste all items in first row
3)      Paste all locations in second row
4)      Use “1 & 0” formula as given in 3rd row of excel sheet  (Use this formula everytime for collating locations) {=IF(A2<>A1,1,0)}
5)      Use “Repeat sequence” formula in 4th row of excel sheet {=IF(C2=1,1,C2+1)}
6)      DO NOT CHANGE HEADINGS OF THIS EXCEL FILE. Only paste data below this headings


ACCESS WORKING – PART 2 

1)      Open Access file
2)      Open excel file as given above in that access file (give a fixed location to that excel & access-both files & don’t change file names after that. This is bcos access will define excel file name & if you change excel file name, the query won’t work)
3)      Use the cross tab query. Go to create-Query Wizard-Cross tab query-Use first column(locations) as Row headings-Use "Repeat sequence" as Column headings-Take Locations & "Last" Function -& Give NEXT. The query will be made.
      Design query will show - First column = Group by ROW HEADING, Second Column = Group by COLUMN HEADING, Third Column = Expresson - Value & Fourth Column = Expression Row Heading
4)      When you will run the cross tab query , you will get locations one after another in access file.
5)      Export this again in excel file

EXCEL WORKING – PART 3 

1)      In excel use the Concatenate formula to join all columns
2)      Use formula of concatenate to join all locations
{=C2&";"&D2&";"&E2&";"&F2&";"&G2&";"&H2&";"&I2&";"&J2&";"&K2&"-"&L2&";"&M2&";"&N2&";"&O2&";"&Q2&";"&R2&";"&S2&";"&T2&";"&U2&";"&V2&";"&W2&";"&X2&";
"&Y2&";"&Z2&";"&AA2&";"&AB2&";"&AC2&";"&AD2&";"&AE2&";"&AF2&";"&AG2&";"&AH2&";"&AI2&";
"&AJ2&";"&AK2&";"&AL2&";"&AM2&";"&AN2&";"&AO2&";"&AP2&";"&AQ2&";"&AR2&";"&AS2&";"&AT2}


This will give you all locations in one single row.