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

Advertisements

About blakehandler

BLAKE was a Microsoft MVP and award winning programmer with over 20+ years experience providing complete Windows and networking support for small to medium sized businesses. BLAKE is also Jazz Musician and Instructor for residential clients on the Los Angeles West Side.
This entry was posted in Know Where Know-How. Bookmark the permalink.

2 Responses to 10 Excel “How to” Tips

  1. MIkE says:

    I was wondering if you could make one cell in excell have a pull down menu with things to choose from…like when you choose a selection from a pull down menu on a webpageAny help would be greaty appreciated!Thanks

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s