10 Excel “How to” Tips

I’ve noticed users repeatedly asking these “Excel: How to” questions on newsgroups and blogs. So I’ve gathered the “quicker” solutions here: 
 

Insert a “Return” (blank line) in your text 

1. Enter your text in the formula bar
2. But instead of using <Enter>, use <Alt> + <Enter> 
   

   ---

Press <F11> to Create a Chart 

1. Select your data
2. Press <F11> (nuff said?)
   

   ---

Do NOT use a floppy disk to work on your spreadsheet. 

Excel creates many temporary files when you’re working on a spreadsheet. Since floppy disks are too slow and unreliable, copy the file to your hard disk. Then after making & saving changes, copy it back to the floppy disk.

---

Join text from multiple columns by using the “&” operator or the “concatenate” function.  

Example: Having people’s first names in one column, middle names in another column, and last names in a third column.

 

	A	B	C	D
1	William	Henry	Gates	William Henry Gates
2	Steven	Anthony	Ballmer	Steven Anthony Ballmer

 

Use either of these formulas in cell D1

=concatenate(A1," ",B1," ",C1)

=A1&" "&B1&" "&C1

 Note: The " " in formulas are needed to insert a blank space between the text.

---

Set the Print Area 

Normally, setting the print area requires: selecting the print area ,then go to the File pull-down menu, select Print Area followed by Set Print Area.

 

But this is easier if you place a Set Print Area button on your toolbar. Clicking the Set Print Area toolbar button sets a print area to the currently selected range.

 

Adding the Set Print Area button to your toolbar:

1. From the View pull-down menu, select Toolbars, followed by Customize.
2. Click the Commands tab.
3. Under Categories, select File, scroll down the list of commands and click Set Print Area
4. Drag the Set Print Area icon to your Excel toolbar.

---

Changing Text to Numbers when Importing 

Sometimes when importing files, numeric values may (incorrectly) import as text. One method to convert text into numbers, is to multiply these text values by 1.

 

To convert the text values, follow these steps:

1. Click on a blank cell, and type 1
2. Select the “1” Cell you just created, right-mouse click and click Copy
3. Select the range of values that you want to convert from text to numbers.
4. Right-mouse click on the range, and click Paste Special
5. Under Operation, click Multiply, and then click OK 

 

---

Enter the current date or time in a cell 

TIME: Press <Ctrl> + ;  followed by <Enter>  (that’s a semi-colon)

 

DATE: Press <Ctrl> + :  followed by <Enter> (that’s a colon)

 

---

Entering the same value (text, number or formula) into a range of cells

 

KEYBOARD METHOD: 

1. Select the range of cells that you want to fill with the same text, number or formula
2. Type the text, number, or formula, but do not press <Enter>. Instead, press <Ctrl> + <Enter>

  

EVEN BETTER: Use the Fill Handle 

1. After you enter text, number or formula into a cell, there’s a small black dot in the bottom right corner called a Fill Handle

 

2a. If you enter text, number, or formula into ONE cell and drag the Fill Handle in the same row or column, it will fill the other cells with that same value!

 

2b. If you enter numbers into TWO cells and drag the Fill Handle in the same row or column, it will fill the other cells with the continued pattern. 

Counting: Type “1” in one cell, and “2” in the next, use the Fill Handle on the TWO cells to quickly fill the other cells with the numbers counting by 1

 

Year Counting: Type “2005” in one cell, and “2006” in the next, use the Fill Handle on the TWO cells to quickly fill the other cells with the numbers counting by 1

 

Pattern: Type “2” in one cell, and “4” in the next, use the Fill Handle on the TWO cells to quickly fill the other cells with the numbers counting by 2

 

Text with Numbers: Type “Text 1” in one cell, and “Text 2” in the next, use the Fill Handle on the TWO cells to quickly fill the other cells with the text and number pattern! [Note: Any text and pattern will work!] 

2c. If you enter a special value into ONE cell and drag the Fill Handle in the same row or column, it will fill the other cells in a very special way! 

Days of Week: Type “Monday” (or any day) then use the Fill Handle to quickly fill out the days of the week!

 

Months: Type “January” or “Jan” (or any month) then use the Fill Handle to quickly fill out the months of the year!

 

Months & Year: Type “January 2005”  or “Jan 2005” (or any month & year) then use the Fill Handle to quickly fill out the months with year(s)!

 

Time: Type “12:00 AM” (or any time) then use the Fill Handle to quickly fill out the time by hour!

 

Time Pattern: Type “12:00 AM” in one cell, and “12:15 AM” in the next, use the Fill Handle on the TWO cells to quickly fill the other cells with the hours counting by :15 minutes.

 

---

Automatic Formula Fill (Handle)

 

1. Enter your data into your desired rows/columns
2. Enter your formula you wish to replicate, then Double-Click on the Fill Handle!
   

   ---

Troubleshooting a long formula 

1. Select the section of the formula you wish to troubleshoot in the formula bar
2. Press <F9> to evaluate only the selected part of the formula
3. Press <Esc> to exit 

   Note: If you press <Enter> to exit, you’ll lose part of your formula (press <Ctrl> + Z to undo).

  

Of course, there’s no substitue for complete Excel training
More Tips: 20 Outlook "How to" Tips
20 "Best-Of" Word Tips
Windows Tips by the Baker’s Dozen

Advertisement