Best excel tricks

#1: Select All with one click

The next time you need to select an entire worksheet, clickthe little gray box in the top-left corner of the sheet. As shown in FigureA, it's the space above the row numbers and to the left of the columnletters.

Figure A

Select the entire worksheet by clicking on the gray square above the rownumbers (and to the left of the column letters).

Why would you want to select the entire worksheet? Let'scount some of the ways:

• With the entire worksheet selected, you can copyit from one workbook (XLS file) and then paste it into a worksheet in adifferent workbook. Selecting the whole worksheet ensures you won'taccidentally miss something. Note: If you want to make a copy of aworksheet within the same book, just right-click on the worksheet tab, chooseMove or Copy, then select the Create A Copy check box.
• With the entire worksheet selected, you canquickly and easily change the font in all cells or apply formatting to allcells.
• With the entire worksheet selected, you candouble-click on any line separating two column letters or the line separatingany two rows. Doing so tells Excel to adjust the widtth of the columns or theheight of the rows to accommodate the data in the cells, which is very helpfulif you've just shrunk (or enlarged) the font size of the text in your cells.

There are, of course, other ways to select all the cells ina worksheet. If you're a keyboard person, press [Ctrl]A. If you're a menuperson, go to Edit | Select All.

#2: Copy the formatting (attributes) of one or more cells and apply them toanother cell or range

Once you learn to use the Format Painter tool (which lookslike a little yellow paintbrush on the Standard toolbar), you'll wonder how youever got by without it. To format a cell (or cells), select a cell (or cells)that are formatted the way you like and click Format Painter. Then, click anddrag to apply that formatting to another cell (or range of cells).

Here's an example to illustrate how Format Painterworks. Start by manually formatting cell A1 as Times New Roman 9, bold, andunderlined and then use the Fill Color tool to make the background of the cellyellow. With cell A1 selected, click Format Painter. You'll notice that Exceldisplays a paintbrush next to the cursor, as shown in Figure B. Whilethat paintbrush is visible, all you have to do is click (and/or drag) to applyall of the attributes from cell A1 to any other cells (Figure C). This shortcut saves time because you don't have tomanually reapply the font and other attributes to other cells.

Note: By default, Format Painter is a "once andout" function. That is, if you click once on Format Painter and then painta cell or range of cells, Excel will stop painting as soon as you release themouse. If you want to paint a number of different cells or range of cells,double-click on Format Painter. The paint brush function will then continuepainting cells as long as you keep clicking. To turn off Format Painter afteryou've double-clicked it, press [Esc].

Bonus tip

The cool thing about Format Painter is that it isn't limitedto picking up the attributes of one cell at a time. For example, suppose youhave applied different formatting attributes to two or more cells in the samerow or column, as we did in Row 2 of FigureD. Using Format Painter, you can transfer all those formats to the rowsbelow, as shown in Figure E.

#3: Perform one-click data mining with AutoFilter

If you support Excel users, AutoFilter will be a surefirehit, both for beginners and for experienced users who haven't seen this featurein action before. Go to Data | AutoFilter, and Excel will add drop-downarrows to the first cell in each column of data in your sheet. When you clickon any of those drop-down arrows, Excel will display a list of the uniqueentries in that column, as shown in FigureF. Just select the desired entry to limit the display of records, as shownin Figure G.

AutoFilter notes

If you look closely, you'll notice that the color of thedrop-down arrow changes from black to blue whenever you make a selection. Thatchange in color is a visual cue to remind you that your list has been filteredby a selection from that column.

As you probably guessed, you aren't limited to filtering thelist on just one column. You can click on the drop-down for two or morecolumns, and Excel will display only those records that match your selectionsin each column.

There are two ways to turn off AutoFilter. One is to clickon each of the columns where you made an AutoFilter selection and choose the(All) option. The other way is to go to Data | Filter and select Show All.

#4: Press [Ctrl]~ to display formulas so you can troubleshoot or debug them

If you wanted to troubleshoot a formula in early versions ofExcel, you had to click on the cell containing the formula and look at theformula itself in the Formula Bar. In more recent versions of Excel, you candisplay your formulas by pressing [Ctrl]~. If you want to view the dependentcells for a particular cell, select it before you press [Ctrl]~.

To demonstrate this feature, open any worksheet thatcontains at least one formula and press [Ctrl]~. Our sample sheet contains twoSUM functions. Figure H shows oursheet after we selected cell B2 and then pressed [Ctrl]~. Notice that when youpress this key combination, Excel 2003 also displays the Formula Auditingtoolbar.

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

When you support or teach Excel users, one of the mostcommon questions you'll hear is, "I've got a list with a thousand entries in acolumn, 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. Thefirst answer is to refer back to #3 above: Go to Data | AutoFilter and thenclick the drop-down list for the column in question. Doing so lets you see thelist of unique entries onscreen. If seeing the list satisfies your need, you'refinished.

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. Togenerate such a list, you'll use Data | Filter | Advanced Filter. Todemonstrate how it works, we'll use the data in Column B from the sample sheet weintroduced in Figure B.

1. Clickon the column letter to select the entire column that contains your data andthen copy it by pressing [Ctrl]C, going to Edit | Copy, or clicking the Copy buttonon the Standard toolbar. (Select the whole column because you'll need thecolumn header.)
2. Pastethat data into a column away from your source data range or in a new sheet. Afteryou paste the data, it will still be selected. However, if you inadvertently deselectit, just make sure the cell pointer is located anywhere in the data you pastedbefore you proceed. Note: You don'thave to select all the data or sort it first for this tip to work.
3. Goto Data | Filter | Advanced Filter.
4. Bydefault, Excel will suggest filtering the list "in-place." There's nothingwrong with that, but I recommend copying the unique records to anotherlocation, so you can compare the two lists side by side.
5. Asshown in Figure I, select the Copy ToAnother Location option, select the Unique Records Only check box, and type B1 in the Copy To field.
6. ClickOK, and Excel will copy the unique entries from the source column into the newlocation. It will even sort those entries in alphabetical order, as shownin Figure J.

#6: Let Excel calculate your subtotals for you

The Subtotals feature is yet another life-changing tool forthose who haven't seen it before. In the old days, before the Subtotals featurewas introduced, here's how you generated subtotals: You'd sort your data,manually insert blank rows between the groups of data you wanted to subtotal,and manually insert the appropriate Sum functions. Many Excel users still takethat approach when they want to generate subtotals, which is regrettable, sinceit provides many opportunities for errors.

Here's a quick walk-through of how to use Excel's Subtotalsfeature:

1. Saveyour worksheet under a work name. To do so, go to File | Save As and add"_work" to the original filename. Trust me, you don't want to practice usingthe Subtotals function using the only "good" copy of your worksheet. As you'llfind when you sally forth into experimenting with this feature, a misstep canmake a mess of your data that's hard to clean up.
2. Sortyour data on the column by which you want to subtotal. This is an importantstep, because the Subtotals feature doesn't care if your data is sorted; itwill simply subtotal records in the order they appear.
3. Clickanywhere in your source data and go to Data | Subtotals. When you do, theSubtotal dialog box will appear and Excel will take its best guess as to thecolumn on which you want to subtotal and the function you want to use (Sum) forthose subtotals. (You can also generate subtotals using a host of otherfunctions, such as Average, Min, and Max.)
4. ClickOK to generate the subtotals.

Figure K showswhat our sample data looked like before we generated the subtotals. Figure L shows the results.

Note

The Subtotals function doesn't automatically expand thewidtth of the columns to accommodate the subtotaled amounts or the labels. Wehad to widen our columns before we captured the screen shot of our worksheet todisplay the contents of columns A and B.

At this point, direct your attention to the top-left cornerof the worksheet. You'll see, in an extremely tiny font, the numerals 1, 2, and3. If you want to see only the grand total line, click 1. Click the number 2,and Excel will "roll up" the detail lines and display only the subtotal andgrand total rows, as shown in Figure M.Click 3 to display the default view, which is all of the details rows, thesubtotals, and the grand total.

Subtotals notes

If you want to view the detail rows for just one of theentries in your list, click the plus sign (+) beside that row. To remove the subtotalsand restore your worksheet to its original state, go to Data | Subtotals andclick the Subtotal dialog box's Remove All button.

#7: Analyze selections with the AutoCalculate menu

This is a handy tip for anyone who teaches spreadsheetskills classes to adult learners. I use this tip to open my Excel classes, andI have been surprised at how many experienced Excel users have never heard ofit.

Here's how it rolls. Create a new workbook and enter somerandom text and numbers. Press the NumLock key and confirm that you see NUMdisplayed in Excel's Status bar at the bottom of the Excel window.

Right-click in the vicinity of Ready in the bottom-leftcorner of Excel's Status Bar to display the incalculably valuable AutoCalculatemenu. Gone are the days when you manually key a few numbers off of a sheet toget a quick total. Now you can get it off the screen with a few clicks.

As Figure N shows, the AutoCalculate menu calculatesresults on the fly and saves you the trouble of setting up formulas tocalculate things like the sum, minimum, maximum, or average values in the cellsyou've selected.

If you want to have fun, activate any of the AutoCalculatemenu options. Then, click and roll through a range of cells that containvarious entries and watch as Excel displays the changes in the currentcalculation on the Status Bar.

It's a nice little feature whose only drawback is that youcan't copy and paste the results of the calculations. It's dynamic andview-only. Still, it's great for sanity checks. For instance, you might belooking to the Count for what should be the number of expected data rows plusthe row for the column head. Just click on a column head, and AutoCalculatewill tell you how many cells in that column contain values.

#8: Love your [Ctrl] key--for three reasons

This three-fer will demonstrate why you should love and use your[Ctrl] key:

Reason 1--Fastnavigation. When you press [Ctrl] and any arrow key (north, east, south, orwest), you jump to the last populated cell in that direction. Think of usingthe [Ctrl]-arrow key shortcut as an alternative to pressing [Page Down] to findthe bottom row of a data set or pressing [Tab] to find the last column. Bonustip: Hold down the [Shift] key while you press any [Ctrl]-arrow key shortcut toselect all the cells between where you are and where you jump with the [Ctrl]key.

Reason 2--You canmake noncontiguous selections. That's a fancy way of saying you can selectany cells you want, regardless of whether they're contiguous--next to eachother in a row or column. Hold down the [Ctrl] key while you click on a cell orclick and drag through a range of cells. As long as you hold down the [Ctrl] key,you can click and select to your heart's content.

Combine this tip with tip #7, and you can use the AutoCalculatetool to analyze any combination of individual cells or blocks of cells. FigureO shows our screen when we used AutoCalculate to sum the cells we selectedwhile holding down the [Ctrl] key.

Reason 3--Fast dataentry. Suppose you want to put the same string, number, or formula into twoor more cells. Using the old-fashioned approach, you'd type the string, number,or formula into the first cell and then copy and paste that entry into thedestination cells. But there's a little-known time-saving tip that makes shortwork of placing the same entry in multiple cells. First, select all the cellsyou want to populate. Type the entry, but don't press [Enter]. Instead, press[Ctrl][Enter]. When you do, Excel will copy what you typed into all of theselected cells.

#9: Transpose data from a row to a column, or vice-versa

Here's a tip that eliminates the need to rekey data. Supposeyou've entered your data with three column headings running across Row 1 andthree row headings running down Column A, like the ones shown in Figure P.

After working with the data for a while, you decide you'drather have the current set of row labels (months) running across the columns. Whateveryou do, don't even think about rekeying the data.

You'll find the best solution under the Paste Special menu. Startby selecting and copying your entire data range. Click on a new location inyour sheet, then go to Edit | Paste Special and select the Transpose check box,as shown in Figure Q. Click OK, andExcel will transpose the column and row labels and data, as shown in Figure R.

Note

You aren't limited to using the Paste Special | Transposeoption to rearrange multiple rows and columns of data. It works just as wellwhen you need to turn a single row of labels into a column, or vice-versa.

#10: Convert calculations to literal values

This jewel, like #9, is also found under Edit | PasteSpecial. Experienced users may squawk that this tip doesn't qualify as obscure.However, I decided to include it because, in teaching both beginning andadvanced Excel classes to thousands of adult learners over the years, I've mettoo many people who never even heard of it--which is a crying shame.

Here's how it works. Suppose you have a worksheet withcolumns and rows chock full of calculations, running the gamut from Sumfunctions to If tests to vertical and horizontal lookups. The calculations arecorrect and your data is pristine. You save the worksheet.

Now you need to use a subset of that worksheet in anotherworksheet. If all you're going to do is print the subset of columns or rows,you can simply hide those rows and columns, print what you need, and unhide thecolumns and rows later to restore the sheet to its normal state.

But if you're going to e-mail a copy of the spreadsheet to acoworker or a third party, you may not feel comfortable simply hiding certainrows and columns. You may want to delete them instead. The problem is, ofcourse, if you simply start deleting rows and columns, you're going to geterror messages in the cells that depend on the cells you deleted.

The solution? First and foremost, save a copy of your pristinespreadsheet under a new name. Just go to File | Save As and add "_work" to theend of the "real" name. Use the Select All tip (#1) to select the entire sheetand then copy it. Without moving the cursor, go to Edit | Paste Special. Now,select the Values option, as shown in
Figure S,and click OK. When you do, Excel will replace all the formulaswith the values they're currently calculating and displaying. At that point,you can delete columns or rows and move cells around without generating asingle error message.

Note

If you use the Paste Special | Values option and the datayou're pasting contains calculated dates or numbers formatted as currency, thedate calculations will be pasted as the Julian date value, and the currencywill lose its dollar signs and commas. To preserve that kind of formatting whenyou convert calculations to literals, simply choose the Values And NumberFormats option (instead of Values).

Jeff Davis is an IT project manager and consultant basedin Louisville, KY, and is a frequent contributor to TechRepublic

 Source:-https://www.techrepublic.com