40 |
POWER EXCEL WITH MR EXCEL |
|
|
2.Type ### in the Find What dialog.
3.If the dialog is not showing the options, click the Options button.
4.Ensure that Look In is set to Values and that Match Entire Cell Contents is not checked.
5.Instead of clicking Find, click Find All. Excel adds a new section to the dialog, with a list of all the cells that contain ###.
6.While the focus is still on the dialog, click Ctrl+A. This will select all the cells in the bottom of the Find All dialog.
You can now format just the selected cells. For example, you could choose fewer decimals or a smaller font size, or you could choose to display the numbers in thousands.
Gotcha: In Step 6, you are supposed to press Ctrl+A to select all of the found cells. Be careful that the focus is on the dialog box before pressing Ctrl+A. For example, if you change the font size, the focus would switch to the worksheet, even though the dialog is still displayed. Pressing Ctrl+A at this point would select all cells in the worksheet instead of just the matching cells. To reestablish focus on the dialog box, you need to click the title bar of the Find and Replace dialog.
MIX FORMATTING IN A SINGLE CELL
Problem: I’d like to use strikethrough on the text in part of a cell. Is this possible?
Strategy: You can apply different formatting to certain characters in a cell.
You select the cell and then press F2 or double-click the cell. Select characters with the mouse or by using the arrow keys in combination with the Shift key. You can then apply formatting. Many icons on the Home tab of the ribbon are enabled. Any formatting shortcut keys, such as Ctrl+5 for strikethrough, will work. If you need to apply superscript or subscript, you use the Format Cells dialog by pressing Ctrl+1 or click the dialog launcher in the bottom-right corner of the Font group.
Figure 85 Format a subset of characters in a cell.
Gotcha: In addition to the character formatting, you can apply other formatting to the entire cell. For example, in C5, you can safely apply italic or underline to the cell without removing the bold from the first word. However, if you apply bold to the entire cell, Excel will not remember that you started with just the first word bold. You can not use the Bold icon on the entire cell to toggle back to the formatting shown in the figure.
Gotcha: If you later use the Justify command, the internal formatting will be lost.
ENTER A SERIES OF MONTHS, DAYS, OR MORE BY USING THE FILL HANDLE
Problem: I need to create a new worksheet. My first task is to enter the 12 month names across row 1. Is there a faster way than typing them all?
Strategy: You type the first value and drag that cell’s fill handle to the right or down. Follow these steps:
1. Type January in cell B1. If you now press the Enter key, Excel will normally move the cell pointer to B2. You can press Enter and then press the Up Arrow key to move back to B1, or you can simply press Ctrl+Enter to accept the cell value and stay in the current cell.
2. The square dot in the lower right corner of the cell is the fill handle. Click it and drag right or down.
As you drag, a ToolTip will show you the value that will be entered in each cell.
PART 1: THE EXCEL ENVIRONMENT |
41 |
|
|
|
Figure 86 As you drag, a ToolTip shows values to be filled. |
|
3. When you release the mouse button, Excel will fill the series with month names. |
|
|
●● For quarters and years, use 1Q 2018 or 1Q-18 or 1Q.18. |
|
|
Gotcha: Excel can extend many built-in series, but can it count 1, 2, 3, and so on? If you enter 1 in cell B1 |
1 |
|
and drag the fill handle down, what do you think you will get? 1, 2, 3. What will you actually get? 1, 1, 1. |
|
|
Additional Details: Excel can extend many other built-in series in addition to month names: |
|
|
●● |
Jan will extend to Feb, Mar, and so on. |
|
●● |
MON will extend to TUE, WED, and so on. |
|
●● Q1 will extend to Q2, Q3, Q4, Q1. (Also Qtr 1 or Quarter 1) |
|
|
●● |
Room 10 will extend to Room 11, Room 12, and so on. |
|
●● |
1st period will extend to 2nd period, 3rd period, and so on. |
|
●● |
Today’s date (press Ctrl+;) will extend to tomorrow’s date. |
|
Many people tell me to enter 1 in B1, 2 in B2, select B1:B2 and drag the fill handle. While this works, there is a faster way: You can enter 1 in B1 and then hold down the Ctrl key while you drag the fill handle. Excel will fill with 1, 2, 3. Alternatively, select the 1 and the blank cell next to the 1. Drag down. Excel will fill
1, 2, 3.
The Ctrl key can be used to copy instead of fill. Select a date or text. To copy without incrementing, drag the fill handle while holding down Ctrl.
Additional Details: If you forget to hold down Ctrl, you can open the Auto Fill Options dropdown that ap- pears at the end of the range. You can select Fill Series to change the 1, 1, 1, 1 to 1, 2, 3, 4. You can toggle
Ctrl while dragging - keep your eye on the + symbol next to the mouse pointer.
Gotcha: The Fill Options icon can be difficult to dismiss. This is particularly annoying if it is covering up data. The Esc key will not make it go away. One fast way to dismiss the icon is to resize a column on the worksheet.
If you need to fill odd numbers, you can enter 1 in B1 and 3 in B2. Select B1:B2 and drag the fill handle.
There are other fill possibilities as well. One cool option is Fill Weekdays. You enter a starting date in a cell, place the cell pointer in that cell, right-click, and drag the fill handle down several cells. A ToolTip will indicate that you are filling the series with daily dates. When you release the mouse button, you will have several options. Choose Fill Weekdays to fill in only Monday through Friday dates.
To fill the 15th and last of each month, select both dates, right-click the fill handle and drag. When you release the mouse, choose Fill Months.
Figure 88 Select both dates.
Figure 89 Right-drag, fill months. |
Figure 87 Right-click and drag the fill handle to |
access these options.. |
42 |
POWER EXCEL WITH MR EXCEL |
|
|
Additional Details: The fill handle is a shortcut to default settings you can also get by selecting Home, Fill, Series. You can enter a value in a cell, select that cell, and choose Home, Fill, Series to display a dialog where you can specify any type of series.
Say that you want to fill the numbers from 1 to 1,000,000. Try this:
1. Enter the number 1 in a cell and select that cell.
2. Right-click the fill handle. Drag down one cell. Drag back up. Release the mouse button.
3. Choose Series… from the bottom of the flyout menu. (Be careful, you want “Series…” from the bot- tom, not “Fill Series” from the top.
4. In the Fill Series dialog, choose Columns. Enter a Stop Value of 1,000,000. Click OK.
Figure 90 Fill 1 million cells easily.
HAVE THE FILL HANDLE FILL YOUR LIST OF PART NUMBERS
Problem: Sure, the fill handle is good for filling months, days, and sequential numbers. But what about other lists I have to type lists of product lines, company regions, sales rep names, and so on.
Strategy: No matter what job you do, you probably have some annoying list of items that you have to type over and over. If your list contains from two to 96 items, you can add your list of items to the Custom Lists dialog. You can then fill items from the defined custom lists by using the fill handle.
Say that you work at the Bigger Burrito Co., and you constantly |
|
|
need to type the flavors of burrito filling. Here’s how you can sim- |
|
|
plify this task. |
|
|
1. |
Type the list in a column. (Or, find an existing range with the |
|
2. |
list.) Either way, select the list before going to step 2. |
|
In Excel, choose File, Options, Advanced. Scroll down to |
|
|
3. |
General and choose Edit Custom Lists. |
; |
Click the Import button in the Custom Lists dialog in order |
||
|
to import your custom list. |
Figure 91 Type this list for the last |
|
|
time. |
Figure 92 Import the list from a range.
PART 1: THE EXCEL ENVIRONMENT |
43 |
|
|
Note that if you later change the flavors in this list, you can edit the list in this dialog. Make sure to click the Add button to commit the changes..
After you add the custom list, you can type any item from the list in a cell and then drag the fill handle. Excel will fill in the remaining items from the list. If you go too far, the list will repeat.
Additional Details: Say that you want to store a list of names, and the first name in the list is a really long name, such as John Jacob Jingleheimer Schmidt. Rather than having to type this name to start the list, you could make the first item in the list the heading. So, perhaps you could type Class1 or MktgDept and drag the fill handle to get the correct list.
Additional Details: The custom list is flexible with regards to case. If you type the first item in all caps, the list will fill as all caps. If you use lower case, the list will fill as lower case.
Additional Details: Custom lists are stored in your computer’s registry. It is therefore very difficult to transfer a list from one computer to another. One method is as follows:
1. Set up a custom sort using your custom list. (See How to Sort a Report into a Custom Sequence)
2. Move the workbook to the new computer. 1 3. Do a sort on the new computer. In the Order column, select Custom Lists. Click Add.
Since the above process is fairy convoluted, it might be easier to copy the lists into a blank workbook on the old computer, and then import the lists on the new computer.
TEACH EXCEL TO FILL A, B, C
Problem: The fill handle can fill weekdays, months, quarters, and now numbers. Why can’t it fill A, B, C?
Strategy: Create a list of the alphabet. Save that list as a custom list as described above. Here is a fast way to create the alphabet.
1. Select a range that is one column wide and 26 rows tall. 2. Type =CHAR(ROW(A65)). Press Ctrl+Enter.
3. Ctrl+C to copy. Paste, Values using your favorite method.
Character number 65 is a capital letter A. Using ROW(A65) will return the number 65 in the first cell, 66 in the next cell, and so on. This handy formula will create the letters from A to Z. Import the range as a custom list as described in the previous topic.
ADD TOTAL TO THE END OF JAN, FEB, ... DEC
Problem: Every time I drag the fill handle to put month headings in my worksheet, I have to type the word total in the 13th column. The four custom lists for months and weekdays can not be edited in the Custom List dialog box.
Strategy: Memorize Jan, Feb, Mar, ..., Dec, Total as a custom list. Even though there will be two custom lists that start with Jan, the one at the bottom of the list “wins” and will be used.
Figure 93 When two lists contain “Jan”, the one nearer the bottom wins.