Profile Facebook Twitter My Space Friendster Friendfeed You Tube
Kompas Tempo Detiknews
Google Yahoo MSN
Blue Sky Simple News Simple News R.1 Simple News R.2 Simple News R.3 Simple News R.4

Thursday, February 24, 2011

Excel In A Crunch

tips and trick excell
You appreciate the fact that Excel is a power-packed program, but you are also a professional on the go, and, frankly, you don’t have a lot of time to learn the finer points of the software. No sweat. Read on and we will give you some great ideas of how to kick up your Excel skills a notch without spending a lot of time doing so.

Explore Various Scenarios

One of the most powerful things you can do with Excel is play around with worksheet values to answer “what-if” questions. What if sales decrease by 6% next year? What if the cost of goods sold increases by 3%?

To calculate multiple outcomes such as these, you can use Excel’s Scenario Manager. This tool enables you to specify which data cells you want to change and then quickly view the result in the worksheet.

To use the Scenario Manager, select the cells you want to include in the scenario and then choose Tools and Scenarios. In the Scenario Manager dialog box, click Add to display the Add Scenario dialog box. Type a name (such as Status Quo, Worst Case, or Best Case) in the Scenario Name text box and then specify which cells you want to change. Click OK and then enter a new value for each changing (variable) cell in the Scenario Values dialog box. Click OK, and the Scenario Manager dialog box will redisplay with the new scenario shown on the list. Complete the same steps for each scenario you want to create. Finally, to switch between the scenarios, click each one’s name in the Scenario Manager dialog box and view the change directly on your worksheet.

Select Cells By Data Type
You can quickly locate all the cells in a worksheet that include a particular type of data, such as those that include comments or formulas. For example, if you want to identify cells in a selected range that contain formulas, press CTRL-[ (opening bracket); to find and select all cells with comments, press CTRL-SHIFT-O.

You can also highlight all the cells in a range that include objects, such as clip art, charts, or pictures. This helps you quickly identify all the items so that you can then apply actions to them, such as resizing, moving, formatting, or grouping them. To select all of a worksheet’s objects, click one of them and then press CTRL-SHIFT-Spacebar.

Find Any Type Of Data

You’ve probably noticed that it doesn’t take long for a business worksheet to quickly mushroom in size, making it almost impossible to find data by simply scrolling. This is especially true when you’re working on a laptop with a relatively small screen. It’s also an inefficient way to find, let’s say, one customer’s name among several hundred. But help is at hand in the form of Excel’s Find command.

To use this command, first select the range in which you want to look for the data (or select a single
cell if you want Excel to search through the entire worksheet). Choose Edit and then Find or simply
press CTRL-F to open the Find And Replace dialog box. Enter the text that you want to locate, and then click Find Next to highlight the next cell with your data; click Find All to display a list of cell references that contain the search text.

By default, Find searches through cells with values, such as text or numbers. But you can also locate information buried in worksheet comments, even if it is not displayed. To do this, click the Look In drop-down arrow in the Find And Replace dialog box and then choose Comments from the list.

Add Comments

You can easily add comments to an Excel worksheet, either for your own future reference or to share your ideas with others. To add these notations, rightclick in the cell where you want to place the comment and then choose Insert Comment. Type your notes in the comment box and then click outside of it.

If necessary, you can edit the contents of a Comment by right-clicking the cell, selecting Edit Comment, and then modifying the text as you would in a word processor. You can also hide, show, or delete a comment by right-clicking the cell that contains the Comment and then choosing the appropriate command on the displayed list.

Replace Data & Formulas

In addition to helping you locate data, Excel can help you rapidly replace formulas, numbers, or text with other data. For example, you can update a name that appears multiple times in a worksheet. To do this, choose Edit and then Replace or press CTRL-H. In the Find And Replace dialog box, enter data in the Find What and Replace With fields. Click Find Next, decide if you want to replace the selected occurrence, and then choose Replace. (If you’re feeling especially brave, you can instead choose Replace All without looking at each occurrence.)

Calculate Values On The Fly

You can quickly calculate and display information about a selected group of values on Excel’s status bar, which is located at the bottom of each worksheet. To do this, select the cells you want to add and then view the total on the status bar. But don’t stop there. You can also display a wealth of other information about a selection by right-clicking the status bar and then choosing functions such as AVERAGE, MIN (minimum), or MAX (maximum) on the pop-up menu.

Take A Shortcut

You’re probably already familiar with many basic Excel keyboard shortcuts, especially when you take your laptop on the road and don’t want to use the mouse. However, there are some little-known shortcuts that can help you work even more efficiently in selecting ranges and moving around a worksheet.

For example, you can quickly select an entire data region (a range of data cells bordered by empty cells) by holding down CTRL-SHIFT-* (asterisk). Another way to select a data range is to place your cell pointer in the range and then press CTRL-A; press CTRL-A a second time to select the entire worksheet. You can also press CTRL-Spacebar to select the column where your cell pointer is located.

Another helpful keyboard shortcut is to press END followed by an Up, Down, Left, or Right arrow key to efficiently move the cell pointer to the outermost edge of a data range. ▲

Related Post:

Widget by [ Iptek-4u ]


Post a Comment

Copyright © 2010 - All right reserved