Learn Excel 38

Free Preview – Week #38 Learn Excel from Mr Excel Copyright 2005 Bill Jelen All Rights Reserved Encourage your friends to sign up at http://www.mrexcel.com/learn-excel.html This week - 6 topics -including some of my favorites. Transpose, Fill Handle, Copy Across Sheets & more.

76 of 853

LEARN EXCEL FROM MR EXCEL

QUICKLY COPY A FORMULA TO ALL ROWS OF DATA Problem: As shown in Fig. 138, you have a worksheet with 5,000 rows of data. You enter a formula in a new column and need to copy it down to all of the rows. Is there a faster way to copy the formula down to all rows?

Fig. 138

Strategy: Double-click the Fill handle. This shortcut will copy the formula down until Excel encounters a blank cell in column D or F. The Fill handle is the square dot in the lower right corner of the cell pointer box. When you hover your mouse pointer over the Fill handle, it changes to a cross, as shown in Fig. 139.

Fig. 139

77 of 853

Part 1: The Excel Environment

If there is data in the cell to the left of E2, then Excel will copy the formula based on the first blank cell in column D. If there is no data in D2, but there is data in F2, then Excel will copy down to the first blank cell in column F. Additional Information: The Fill handle can be used to extend a series. Select two cells that Excel can use to extrapolate the series. Doubleclick the Fill handle to extend the series down to all rows. In Fig. 140, the record numbers will continue in a 1, 2, 3, 4 sequence.

Fig. 140

There are other Fill possibilities. One cool technique is to Fill Weekdays. Enter a starting date in one cell. Place the cell pointer in that cell. Right-click and drag the Fill handle down several cells. The tooltip will indicate that you are filling the series with daily dates. When you release the mouse button, you will have several options to choose from, as shown in Fig. 141. Select Fill Weekdays to fill in only Monday through Fridays.

Fig. 141

Part I

78 of 853

LEARN EXCEL FROM MR EXCEL

Additional Information: The Fill handle is a shortcut to default settings in the Edit – Fill – Series command. Enter a cell, select that cell and choose Edit – Fill – Series to display a dialog where you can specify any type of series. As shown in Fig. 142, the dialog will fill the odd numbers from 1 to 99, down a column starting with the active cell.

Fig. 142

Summary: Learn to double-click the Fill handle to quickly copy a formula down to all cells in a range of data. Commands Discussed: Edit – Fill – Series

QUICKLY TURN A RANGE ON ITS SIDE Problem: As shown in Fig. 143, you have a column of 20 department names. You need to build a spreadsheet with those names going across row 1.

Part 1: The Excel Environment

79 of 853

Part I Fig. 143

Strategy: Use the Paste Special – Transpose option to turn the range on its side. 1)

Highlight the department names in column A.

2)

From the menu, select Edit – Copy to copy the cells to the clipboard.

3)

Move the cell pointer to a blank area of your worksheet. In this case, perhaps cell C1.

4)

From the menu, select Edit – Paste Special. In the Paste Special dialog box, choose the Transpose option, as shown in Fig. 144. Choose OK.

Fig. 144

80 of 853

LEARN EXCEL FROM MR EXCEL

Result: As shown in Fig. 145, the department numbers from column A are turned sideways and fill cells C1 to Z1.

Fig. 145

Gotcha: The columns that you paste to will not automatically resize to fit the data. Select C1:Z1 and from the menu, choose Format – Columns – AutoFit Selection. Additional Information: The Paste Special Transpose technique can also be used to convert a horizontal row of numbers into a column. The technique can also be used to turn a rectangular range on its side. In Fig. 146, Range A1:D4 was transposed to Range A6:D9.

Fig. 146

Summary: Using the Paste Special Transpose technique is useful to quickly turn a range of values on its side. Commands Discussed: Edit – Paste Special – Transpose

81 of 853

Part 1: The Excel Environment

STOP EXCEL FROM AUTOCORRECTING CERTAIN WORDS Problem: Every time you type the name of your WYA Division, as shown in Fig. 147, Excel changes “WAY” to “WAY”, as shown in Fig. 148. It is impossible to type WYA without entering it as a formula: =”W”&”Y”&”A. Part I

Fig. 147

Fig. 148

Strategy: There is a large list of words that are automatically replaced as you type. Excel added this feature to correct common mistypings. This is a good feature, unless you routinely have to type one of the words that Excel thinks is wrong. Luckily, you can edit this list without turning it off. From the Tools menu, select AutoCorrect options. On the AutoCorrect dialog, go to the AutoCorrect tab. Look in the Replace Text as You Type section. Scroll down to the list to find where it replaces WYA with WAY. Select that line and click Delete, as shown in Fig. 149. Fig. 149

82 of 853

LEARN EXCEL FROM MR EXCEL

Summary: You can edit how Excel will autocorrect words without turning off the feature by going to Tools – AutoCorrect – Options and removing the selected lines from the list. Commands Discussed: Tools – AutoCorrect – Options

USE AUTOCORRECT TO ENABLE A SHORTCUT Problem: You work for John Jacob Jingleheimer Schmidt. It is frustrating to type this name continuously. Strategy: Set up an AutoCorrect entry to replace JJJS with John Jacob Jingleheimer Schmidt. From the menu, select Tools – AutoCorrect options. In the Replace section, type JJJS. In the With section, type the complete name, click Add, as shown in Fig. 150.

Fig. 150

83 of 853

Part 1: The Excel Environment

Result: Typing “JJJS”, as shown in Fig. 151, will cause the just the replacement word to appear. After typing a Space or hitting Enter to finish the cell, “JJJS” will change to the complete name, as shown in Fig. 152.

Fig. 151

Part I Fig. 152

Summary: Using Tools – AutoCorrect and adding a shortcut for a long or difficult word will cause AutoCorrect to fill it in for you. Commands Discussed: Tools – AutoCorrect Options

WHY WON’T THE TRACK CHANGES FEATURE WORK IN EXCEL? Problem: After using Tools – Track Changes, you cannot insert cells. Strategy: Track Changes is a great feature in Word. However, when you turn on Track Changes in Excel, Microsoft automatically makes your workbook a shared workbook. If you select Tools – Track Changes, Excel will share your workbook, as shown in Fig. 153.

Fig. 153

84 of 853

LEARN EXCEL FROM MR EXCEL

The shared workbook function in Excel has so many limitations that it is nearly impossible to use. Once you share a workbook, you cannot do any of the following tasks: • • • • • • • • • • •

Insert blocks of cells Delete worksheets Merge or unmerge cells Change conditional formatting or data validation Create charts Insert drawing objects, hyperlinks, scenarios Use automatic subtotals Use pivot tables Record or edit macros Enter CSE or array formulas Use data tables

It is possible that a novice Excel user might never use the above features. It is even possible that before you bought this book, you never used those features. However, sharing a workbook makes it virtually unusable for an intermediate Excel user. There is no strategy for this. Unless your changes will only involve radically simple worksheet changes, avoid the Track Changes and Share Workbook options. Summary: Using Track Changes in Excel should be avoided. Commands Discussed: Tools – Track Changes; Tools – Share Workbooks

COPY CELLS FROM ONE WORKSHEET TO MANY WORKSHEETS Problem: You have twelve monthly worksheets in a workbook. You’ve made changes to January and now need to copy the changes to the other eleven worksheets. Strategy: After you’ve successfully made changes to January, follow these steps:

Part 1: The Excel Environment

1)

85 of 853

Select the January worksheet. While holding down the Shift key, select the December worksheet. This will select all twelve worksheets and the January worksheet will be the active sheet.

Note: If your changes are in a middle sheet, such as April, then the process is different. First, click the April worksheet. Next, Shift+Click the December sheet, and then Ctrl+Click the January, February, March sheets.

2)

Select the cells that you want to copy. If the cells are not adjacent, select the first range and then Ctrl+ select the remaining ranges.

3)

From the menu, select Edit – Fill – Fill Across Worksheets, as shown in Fig. 154.

Fig. 154

4)

From the dialog, as shown in Fig. 155, select if you want to copy values, formats, or both.

Fig. 155

Part I

86 of 853

LEARN EXCEL FROM MR EXCEL

Gotcha: After this, right-click any sheet tab and select Ungroup. If you fail to ungroup, any changes you make to the active worksheet will be made to all worksheets. Additional Details: This command is fairly difficult to use. You have to be able to group sheets and then make the sheet with the changes to copy the active (top) sheet. The above steps are designed to help select all sheets. If you need to copy from March to only June, September, December, then you might do this: 1)

Select March to make it the active sheet.

2)

Ctrl+ select June to add to the group.

3)

Ctrl+ select September to add to the group.

4)

Ctrl+ select December to add to the group.

5)

Do the Edit – Fill – Fill Across Worksheets.

Summary: To copy cells from one worksheet to several other worksheets, select the worksheets to copy to, including the worksheet that has the data to copy. With that worksheet as the active sheet, select the data to copy, and then select Edit – Fill – Fill Across Worksheets. Commands Discussed: Edit – Fill – Fill Across Worksheets