Microsoft Office Excel – Tips & Tricks

How to Close all files at a time:

You opened 22 workbooks in excel and work in one by one file. You are called by some one for urgent out door work. At that time you should close all the opened files in excel. If you want to close one by one there should be no time. At that time you can close all 22 files at once. How is it possible? What is the shortcut method? Let us see.

Hold down ‘Shift’ key and click on file menu then go to ‘close all’. By clicking the ‘close all’ button you can close all opened files at a time. 

How to Increase the number of recent work books:

There are only 4 recent workbooks in file menu as default.         

If you want to open minimum 6 workbooks to maintain your office accounts you should increase the number of recent workbooks in the file menu. To activate this job go to Tools>options>general tab. By clicking the ‘General’ tab there should appear a button ‘Recently used file list’ and a number box near it. You can increase the number of files by typing number or by clicking arrow mark.

How to Open all files at a time:

If you want to open 9 files to work you should open one by one. There should be waste of time to open all files. There is a short way to open all files at a time in excel. That is called as ‘workspace’.

First of all open all the files one by one. Now go to ‘file’ menu and select ‘save workspace’. Give any name to identify the workspace. All opened files are saved to this named wok space. If you want to open all these files, you can open this workspace only. But one thing, if you move any file from this set to another folder the file will disappear from this workspace.

To move cursor to desired position after typing number:

By pressing cursor key after type a number in a cell you can move the cursor key in direction shown by arrow key. Although, if you are a ‘numeric key pad’ user, use ‘enter’ key instead of ‘arrow’ key. But when pressing ‘enter’ key, excel will move the cursor to the below cell. Then how to set the cursor direction as we like?  There is a setting in excel program to do this.

Go to Tools > options > edit > Move selection after Enter Direction. Then click the arrow near ‘Down’ button as shown in the above figure. There should appear a scroll bar and you can set a direction to move the cursor in excel work sheet.

How to put page number in each page:

Whenever you type a long report in excel program and want to put page numbers to all pages follow this easy step.

Go to ‘File’ menu and click ‘Page Setup’. There should appear a small window. In that window there are 4 Tabs. There is ‘Header/Footer’ Tab as the third tab. In ‘Footer’ dropdown menu there will be ‘options’ button. In this option select Page 1 of ?. If you select this option, footer will be printed.

In every page the number 1 of 2 will be printed.

There is another way to print the page number. Select ‘Custom Footer’ button from option menu then select the ‘Text Box’ appeared in the right side corner. In the Text Box click the cursor and type ‘Page’ then leave a space, then click the icon ‘X’ and leave a space after that type ‘of’ then leave a space then select ‘++’ (Total Pages) icon. By this Page number will appear on the footer.

To appear Titles on each page:

In excel worksheet the titles are typed on top row of the first page. In other pages the titles should not appear automatically. So, there is some difficult to type proper data below the titles of each page. To solve this problem you can set the titles on each page automatically. When you go to next pages the titles can stay on the top of page.

To set the titles for each page, select the cells you want to stick on the top of page. Then go to menu bar then click window > freeze panes. There you can see a line in your work book. Now you can go to any pages and can see the titles. If you don’t want to see the titles in each page, you can follow the same steps and click the ‘Unfreeze panes’.

To open excel with more worksheets:

 When opening a excel workbook there are three worksheets appear by default. You can increase the number of worksheets in a excel workbook. Usually, to increase the work sheet, we go to insert menu and increase the work sheet. But you can increase the number of worksheets by another simple way. Go to Tools > options > general. In ‘general’ tab there is a small window appears. In that window there is a line says ‘sheets in new workbook’. Near that there is a number ‘3’ in default. You can change the number as you like.