Wednesday, February 15, 2012

Found this really cool Excel tricks you'll surely love!

Found this article about 10 obscure and really cool Excel tricks that can easily speed up your spreadsheet chores. Practice it and it will surely come in handy and this is a good arsenal to have when dealing with repetitive and time consuming spreadsheet work.

Here is an excerpt of the articles. Visit the link below for the complete list of cool Excel tricks.

#1: Select All with one click

The next time you need to select an entire worksheet, click the little gray box in the top-left corner of the sheet. As shown in Figure A, it's the space above the row numbers and to the left of the column letters.
Figure A
Select the entire worksheet by clicking on the gray square above the row numbers (and to the left of the column letters).
Why would you want to select the entire worksheet? Let's count some of the ways:
  • With the entire worksheet selected, you can copy it from one workbook (XLS file) and then paste it into a worksheet in a different workbook. Selecting the whole worksheet ensures you won't accidentally miss something. Note: If you want to make a copy of a worksheet within the same book, just right-click on the worksheet tab, choose Move or Copy, then select the Create A Copy check box.
  • There are, of course, other ways to select all the cells in a worksheet. If you're a keyboard person, press [Ctrl]A. If you're a menu person, go to Edit | Select All. 

#5: Generate a unique list of entries in a column

When you support or teach Excel users, one of the most common questions you'll hear is, "I've got a list with a thousand entries in a column, and many of those are duplicates. How do I generate a list of the unique entries in that column?"
There are at least two good answers to that question. The first answer is to refer back to #3 above: Go to Data | AutoFilter and then click the drop-down list for the column in question. Doing so lets you see the list of unique entries onscreen. If seeing the list satisfies your need, you're finished.
The second answer is the one to use if you want to have a list of the unique entries you can copy and paste elsewhere. To generate such a list, you'll use Data | Filter | Advanced Filter. To demonstrate how it works, we'll use the data in Column B from the sample sheet we introduced in Figure B.
  1. Click on the column letter to select the entire column that contains your data and then copy it by pressing [Ctrl]C, going to Edit | Copy, or clicking the Copy button on the Standard toolbar. (Select the whole column because you'll need the column header.)
  2. Paste that data into a column away from your source data range or in a new sheet. After you paste the data, it will still be selected. However, if you inadvertently deselect it, just make sure the cell pointer is located anywhere in the data you pasted before you proceed.Note: You don't have to select all the data or sort it first for this tip to work.
  3. Go to Data | Filter | Advanced Filter.
  4. By default, Excel will suggest filtering the list "in-place." There's nothing wrong with that, but I recommend copying the unique records to another location, so you can compare the two lists side by side.
  5. As shown in Figure I, select the Copy To Another Location option, select the Unique Records Only check box, and type B1 in the Copy To field.
  6. Click OK, and Excel will copy the unique entries from the source column into the new location. It will even sort those entries in alphabetical order, as shown in Figure J.
Figure I
Use the Advanced Filter options to tell Excel whether to filter in-place or to copy the unique records to another location.
Figure J
The Advanced Filter feature copied a sorted list of the unique entries from the source data in Column A.

You can check out the rest of the Excel tricks here - Excel Tricks.

Till the next Excel coolest tricks!


Anil Kumar Pal said...

Intersting and beautiful blog lovely presentation thanks for sharing your excel support We24support tech team are available 24/7 for repairs on computers, printers, laptops, desktops. Our tech team taken to new heights with our technician’s knowledge and 1-866-978-0799 microsoft excel

Chandoo said...

Hi there,

This is chandoo here. Can you remove all the articles copied from at the earliest. IF you want, you can replace them with short excerpts.

Please take necessary action in next few days. Otherwise, I will have to report to blogspot & Google for copyright violation.