Log in ....Tribune

Dot.ComLatest in ITFree DownloadsOn hardware

Monday, January 8, 2001
Article

Tips and tricks for Microsoft Excel

Microsoft Office suite has several software which are self-sufficient and do not require any support from other applications. Microsoft office software like MS Excel is among such software, which is perhaps most widely used these days in almost all commercial or technical applications. It has application in accounting, technical and statistical research or wherever the arithmetical computing of data is required. Today, MS Excel has surpassed the earlier scientific software like Lotus123, primarily because of its advanced and user-friendly features. Even a novice can learn and start working on Excel software right from the first day of his exposure to computers. Excel ’97 is nowadays found loaded in almost all desktop or laptop computers and is extremely fast in computing data. However, there are several features about which most users are generally not aware of. These features, if used, can considerably increase working speed as well as provide ease of operation to the user. We shall discuss here few tips and tricks on Excel, which if applied can make the task much easier.

 


It often happens that while selecting a cell range with the mouse, we either under select or over select the desired range. The entire range has, therefore, to be re-selected. In order to precisely select the cell range, what you have to do is simply use the shift key and the arrow keys. While holding the shift key, move the arrow key in the direction of expansion or contraction, till the place where you intend to keep your range. This will highlight the selected area or cells accurately. Similarly, if you want to tie cell reference and label text together in a formula, use [&] in between the reference and text, say (=A1& fans). If cell contains the number 200, the formula will now display ‘200 fans’

Sometimes it happens that available rows or columns in the worksheet are not able to accommodate the required data. For this, highlight column or row for insertion on left or on top respectively, press Ctrl and + key or click on Insert and then select row/column. You can also perform the same function by right clicking the mouse button that will open insert box. Select row / column and click OK. In the same way, if you want to delete a particular row/column, press Ctrl and (-) key or click Edit in the top bar and select Delete, which will open delete box. Thus, entire selected row or column can be deleted. You can also delete by right clicking the mouse and opening the delete box. Repeat the procedure as mentioned for insert.

In case if a user wants to create a graph, while working on Excel, he can do so by selecting the range and press F11 key. This will place a graph on a separate data sheet. He can now work simultaneously on both spreadsheet and chart sheet without closing or reopening Excel Window.

The Internet surfers can extract data from the Web spreadsheet and then manoeuvre it in their own Excel sheet. This can be done by clicking File open dialog box and then opening Web spreadsheet by typing its URL in the dialog box. Select and copy the cells that are to be copied. In their spreadsheet, select Edit and then Paste Special from the menu bar and click OK on paste link button. One can now directly utilise web spreadsheet data.

If you want to put in data in the slanting format, you can do so by rotating the column headings up to 90 degrees. Select the column or cells, right click the mouse button, choose format cells and then alignment. Set orientation up to +90 or - 90degrees. Click OK. You will now get data in slanting format in the cells that you have selected.

In case you wish to note down the current date and time on the worksheet while working, it can be done by typing Ctrl +; and Ctrl + Shift +; In case you require to have current date and time, whenever a document is opened or updated, then you have to type for date as = today() and for time as = now() .

When a person is working on several spreadsheets simultaneously and needs to close them immediately, he can do so by holding Shift key and then clicking on File. Select ‘Close All’ menu. This will save lot of precious time that is wasted in selecting and closing each spreadsheet.

It happens quite often that we store numbers with several decimal places but formatted to display only one or two decimal places. This sometimes creates difficulty as formula uses only the real values and not the displayed one and therefore calculations based on those values do not appear. By applying simple trick, we can use values in the formula, as they appear. Clicking Tools and Option from the menu bar does this. Now select Calculation Tab, and then click on Precision As Displayed. Click OK. Though the change will be for all the numbers in active work sheet only, it can have implications as we cannot retrieve original values later on. So a caution has to be exercised on this feature.

If you want to add up values in the multiple columns or rows, it can done easily by selecting the range of cells and then clicking AutoSum button on the tool bar. You will get the total sum of all numbers in that particular row, column or range of cells. This single stroke of mouse button will save your unnecessary efforts in writing individual sum formulas or copying them from one cell to another.

In cases where more than single users are working on a same computer or worksheet, there are chances that the formulas inserted may get damaged accidentally. However, by default formula is protected in a particular cell and it can therefore be viewed in the formula bar. A user can hide his formula from appearing on the formula bar, if he wishes to maintain secrecy. For this select the cells, formulas of which are to be hidden, then choose Format / Cells from the menu bar. Click on Protection tab and select Hidden Check box. Press OK. Then open Tools from the menu bar, select Protection and then Protect Sheet. Choose from the options and finally click OK.

One problem that Excel users usually face is that as soon they enter some number which exceeds the column width, the column automatically widens or the column start displaying character # instead of digits. This behaviour can be avoided by using the Text function, in which number is displayed as text so that the column width does not change. For verifying that how it works, first set a width of a particular column say A as 2. Then enter number 2222 in cell A5. Excel sheet now displays ##. Now type formula = TEXT (2222,"##") in cell A5. Worksheet now displays the number by overlapping onto next column B. So by using this simple trick, one can use the numeric value in calculations even though the number spreads over the next column as text. Moreover column width also does not change.

There are some other Tricks and Tips that one can follow for faster and easier working on Excel sheets. If you want to add a Header or a Footer, select File / Page Set-up from the menu bar. Click on the header / footer tab. You can either type on the dialog box or select the pre set header or footers. Finally click OK.

Another simple trick you can use is when moving or copying contents of a particular cell. This can be done just by clicking and dragging mouse button, without using a single keyboard button. Bring your mouse button over the lower right corner of a cell, as soon as the pointer changes shape to white arrow, click the contents of the cell and then drag the data to a new cell, thus moving the contents to new location. When mouse pointer changes to a bold plus (+) sign, drag the contents to a new location and release the mouse button, the data gets copied on to the new cell or range of cells.

One more tool that is of immense utility for Excel user is the Format painter. This brush shaped tool that appears on tool bar is used for quickly applying formatting of a range of cells to another cells of the work sheet. Select the range whose formatting is to be copied, then click format painter icon. Now as mouse button icon changes to a brush icon, select the range in which formatting is to copy and then release the mouse button.

In case you want to apply the same format to other areas, double click the format painter icon; mouse pointer will now continue to act as format painter until you click the brush icon again.

These are some of the tips and tricks, which you can utilise while working on Excel worksheet and make your task extremely efficient and productive.

— Roli
 

Home Top