Splitting Lists - Excel 2003 and 2007

Splitting Apart Lists (Excel 2003 & 2007)

For additional information, check out the Excel Functions and Formulas class.

This is one of my favorite Excel tips for 2 reasons:

1. It is incredibly simple and easy, once you get the hang of it, 
2. Most people do not know about it.

I am surprised to find, even when teaching intermediate to advanced-level students, how many are not aware of this tip.

Here it is.

Sometimes we receive information that needs to be parsed. For example, a list of names that needs to split into first and last names. Excel makes it easy with the little used Text to Columns feature in the Data menu.



Before you begin:


Make sure you have enough columns to the right of your list. If, for example, your list contains, first, middle & last names, you should have at least 3 blank columns to the right of your list.


To Split Text (Excel 2003 Instructions).

1. Click the Data menu, then Text to Columns.
   
2. If characters, spaces, commas or tabs separate your list, select Delimited.
   
3. If columns or spaces separate fields, select Fixed width.
   
4. The next screen will show you what your list will look like parsed.
   
5. If it looks okay, click the Finish button.
   
   
   
   

To Split Text (Excel 2007 Instructions).

1. Select the Data tab.
   
2. Click the Text to Columns button on the Data Tools option.
   
3. Follow steps 4-6 shown above.
   

Excel 2003 & 2007 - Quickest Ways to Copy Worksheets.

Copying worksheets is one of my favorite Excel time savers. If you have an Excel file (workbook)  that has similar worksheets, such as a budget workbook with a sheet for every month, copying worksheets will save you time. 

This tip works with Excel 2003 and 2007.

Some of you may know the traditional way to copy a worksheet (Excel 2003 instructions):

• Click the Edit menu.


• Choose Move or Copy Sheet.


• Make certain the Create a Copy box is checked.


• Select where you want to place the sheet (in a new workbook file, or in the current workbook).


• Click OK.

 

Did you know that there's an even quicker way to do this? This works in Excel 2003 and 2007.

 

To copy a worksheet in the same workbook.

• Hold down the Ctrl key.


• Left-click and drag the worksheet.

 

To copy a worksheet to another workbook. Excel 2003 and 2007.

• Make sure both workbooks are open.


• Click the Window menu, then Arrange. In Excel 2007, Click the View Tab, and then Arrange All.


• Choose Vertical, and then OK.


• At this point, both workbooks should be open side-by-side.


• Left-click and drag the worksheet from one workbook to another.

For additional information:

Intro to Excel 2007 - Atlanta Training class.

Excel 2003 Basics - Atlanta Training Class.


Excel 2003 Intermediate - Atlanta Training Class.

Excel 2003 - Control Enter Key Action

Question:
When I hit enter after typing an item, Excel automatically takes me to the cell below the current cell. Is there anyway to change this so that it
moves to the cell on the right?


Answer:
Changing this feature can be valuable, particularly when you have to do a lot of data entry.
 

Here's how you make the change.


Click on the Tools menu, and select Options.


Click the Edit tab.


Make sure Move selection after Enter is
checked.

Next to Direction,
choose Right.


Click OK.


This works with Excel 2003 only. For the Excel 2007 instructions, click here.

MS Office Tip - Save Time by Adding Buttons to your Toolbar

This tip shows you how to add buttons to any toolbar in the Microsoft Office 2003 suite (Access, Excel, Outlook, PowerPoint, Publisher, etc.)

Why would you use this, may ask? I have found that adding a couple of buttons to the toolbar for frequently used commands speeds up my productivity. A perfect example, from Microsoft Excel, is the button to delete a row from a spreadsheet. Clicking on a button is a lot faster than having to go through the Edit menu each time I need to remove a row.

Here are the instructions for adding the Insert Rows button to your toolbar. Although this is an Excel command, similar steps will work for all MS Office programs.

• Right click in the blue area to the right of any of your toolbars.
  
  
• Click on Customize.
  
  
• When the dialog box pops up, click on the Commands tab.
  
  
• Under the Categories list box, click on Edit.
  
  
• Scroll down the Commands list box until you come to Delete Rows
  
  
• Click on Delete Rows and drag it to any toolbar.

Voila! You're done. The next time you need to delete a row, simply click on the button you've added to your toolbar.

Now, go and experiment to see how many buttons you can add to your existing Access, Excel, Outlook, PowerPoint or Publisher toolbars to save you tons of time.

Excel Tip: Quickly Find Named Ranges

The ability to name ranges is a valuable Excel feature, particularly when you are working with complex workbooks.

Naming ranges allows you to assign an easy to remember name to a:

• cell,
  
• range of cells, or
  
• formula.

You can use a named range by referring to its name (ex., Tax Table), rather than having to remember its address (ex., Tables!$A$11:$AF$42) in the workbook.

To quickly find a range, Type F5. The list of all named ranges shows up in the dialog box. When you double click on the name, Excel will automatically take you to the range.

To list all of the ranges in the workbook, as well as their locations,

• Select the Insert Menu,
  
• Select Name,
  
• then Paste,
  
• and Paste List.

For additional information, see Learn Excel Formulas from A to Z - Atlanta Excel Training Class.

Excel Tip - Conditional Formatting.

Excel Tip - Conditional Formatting

This tip comes in handy when you are using Excel for a report. You can set it up so that when values are above or beneath a particular threshold, the data will be formatted in a particular manner.

Here is an example of a way you can use conditional formatting:
You have a spreadsheet you use to track deadlines. You can set the format for the "due date" column so that if the due date is less than 2 weeks away, the cell will be shaded yellow. Once the due date has passed, the date will be underlined and in red. This way, you can quickly see which due dates are coming up, and which deadlines have slipped.

To use conditional formatting:

1. Select the cells you want to format.
2. Click on the Format menu, and select Conditional formatting.
3. Set the format appropriately.

For additional information, see Learn Excel Formulas from A to Z - Atlanta Excel Training Class.

Excel - Change the Default File Location

When you try to open an Excel workbook, Microsoft's default is to look in your c:\documents subfolder.

Here's a quick trick I use to change the default file location. The seconds you save will add up to hours over time.

• Click on the Tools menu.
• Click on Options.
• Select the General Tab.
• Under Default File Location, type in your new location (ex., c:\excel). By the way, you can also change the number of sheets in a new workbook (Excel's default is 3).

For additional information, see Learn Excel Formulas from A to Z - Atlanta Excel Training Class.

How yours truly used technology to save $5,000 a year

One of the guiding principles in my business is I show business professionals ways they can use technology to save time, lower costs or generate revenue.

Here's a real life example, based on an Excel project I was working on this morning.

Project overview: This project involves updating a 290 row, 10 column spreadsheet. Just for grins and giggles, I decided to try updating the sheet manually, and then using Excel formulas to get the job done - just to see how much time and money I would save.

The old (expensive) way: It took 1 hour and 57 minutes to update 63 rows, at a typing speed of 80+ words per minute. At this rate, it would have taken another 7.02 hours to complete this project. (I started at 8:36 am and finished 63 rows at 10:33 am).

The smarter way: Through modifying and combining a couple of Excel formulas, I was able to finish the remaining 157 rows in 13 minutes!

This included the time spent:

• modifying the formula,
  
• copying and pasting it throughout the spreadsheet,
  
• creating a formula to test my results, and
  
• correcting 4 errors.
  Note: I made the errors while updating the report the slow and expensive way. Without formulas, I wouldn't have been able to test my results and catch errors.

The bottom line: Let's assume this report is generated once a month. By properly using Excel, I will save $4,908 of my time each year. If this is a weekly report, the annual savings are a whopping $21,268.

My point: Properly using technology not only saves time and money, it may also save your reputation (by enabling you to check results).

For additional information, see Learn Excel Formulas from A to Z - Atlanta Excel Training Class.

Excel Tip: Making things Bigger

If you're looking at a spreadsheet, and the text is just too small, here's a quick way to magnify your spreadsheet.

Go to the File menu, and click Page Setup.

Make sure the Page tab is highlighted.

Under Scaling, increase the percentage amount in adjust to.

In my case, I changed the adjust to amount to 150%.

Click the Print Preview button to make sure all looks well, and print.

For additional information, see Learn Excel Formulas from A to Z - Atlanta Excel Training Class.

Excel Tip - Quick Copy and Paste

Here's a quick way to copy and paste from one row to another.

The cell you are copying to must be directly below the one you are copying from.

Position your cursor where you want to paste the information.

Type Ctrl + D.

This copies and pastes information in one step.

For additional information, Learn Excel Formulas from A to Z - Atlanta Excel Training Class.