Archive | Microsoft Excel RSS feed for this section

Converting Text to Columns in Excel

Today, I taught myself how to separate text in an Excel column into two columns with the Text To Columns function. This can be a huge time-saver when you have a lot of data to convert.

I have a spreadsheet with student info…name, email address, etc. The first and last names were in one column separated with a comma. Well, I needed the names in separate columns, and discovered the Text to Columns function. Now, I would like to share it with you.

It’s a good idea to create empty columns for your converted data to the right of the column holding the text you’d like to convert or split (first and last names for me). Then select the column you’d like to split and click Text to Columns on the Data tab on the Ribbon.

Text to Columns command on the Data tab in Excel

Text to Columns command on the Data tab in Excel

When you click the Text to Columns command, the Convert Text to Column Wizard dialog box appears with a two choices…Delimited or Fixed width. Use Delimited if the text in the column is separated with a comma, tab, semicolon, or a space; and choose Fixed if the text is aligned in columns with spaces between each field.

In the next step of the Wizard, you’ll choose the column format, like Text or General; and the destination column for your output. If you don’t choose a destination, the converted text will replace the original text in the column you selected earlier and flow into the column or columns to the right when you click Finish. It’s so easy and it works with Microsoft Excel 2007 or 2010 (Windows) and Excel 2011 (Mac). So cool!

I need to use this function two times a year; so if I forget it, I’ll refer to this blog post to refresh my memory. And,you can do the same.

I hope you found this Excel tip helpful. Post a comment if you have an Excel question for me!

Kathy

Microsoft Excel Online Classes Start Monthly

 

Share
Comments { 2 }

5 Essential Microsoft Excel 2010 Tips for Advanced Users

 

Fine tune your Microsoft Office 2010 Excel skills with a few masterful tricks.

By Edward Mendelson, Jill Duffy

June 16, 2011

 

 100 Essential Tips for Microsoft Office 2010

Like learning a foreign language, trying to become a Microsoft Excel master comes with a “use or it lose it” quandary. You can learn all the tips and tricks in Excel you like, but if you don’t practice them often enough, they slip from your mind, fleeing your memory without a trace—not even the name of the darn thing you’re trying to remember. Pick up a new idea for Excel on Monday, and if you don’t use it Tuesday or Wednesday, it’ll be gone by Thursday.

The five tips in this article are things we think all advanced users of Microsoft Excel will not only learn quickly, but also use often enough to remember them tomorrow. They cover some of the most important features that spreadsheet makers should know, as well as some of the new features in the latest version, Microsoft Office 2010 Excel. For example, we take a walk-through creating the new Sparkline Microcharts, or charts that fit inside a single cell. We also cover in detail working with transposed tables, including how to make them “live.” We even go over how to add content or formatting to more than one sheet at once, and explain when and why you should ungroup sheets.

For more Microsoft Office 2010 tips, including beginner tips for Outlook, Excel, and Word, see the complete list of links at the end of this article. Also see our complete reviews of Microsoft Office 2010 ($389 street, 4 stars) and Microsoft Office 2010 for Mac ($99 street, 4.5 stars). You can either read our tips in the slideshow below or page through them in the Table of Content.—Next: Insert a Transposed Copy of a Table with Two Mouse Clicks >

View Slideshow See all (5) slides

Insert a Transposed Copy of a Table with Two Mouse Clicks

Transposition Even Better: Create a

Create Sparkline Microcharts In Excel

Add content or formatting to multiple sheets at once

More

 

• 8 Essential Microsoft Outlook 2010 Tips for Intermediate and Advanced Users

• 9 Essential Microsoft PowerPoint 2010 Tips

• 10 Essential Microsoft Access 2010 Tips for Beginners

via 5 Essential Microsoft Excel 2010 Tips for Advanced Users | PCMag.com.

Share
Comments { 2 }

Formatting Numbers with the General Format in Excel

 

Have you ever typed a number in a cell, and the number appeared as a date? To Excel, dates are numbers. Luckily, it’s easy to fix by selecting the desired cells and choosing the correct format from the Home tab on the Ribbon or from the Format Cells dialog box.

Notice in the example below that cells cells B2 through F2 are formatted as dates. You can verify the formatting of a cell by clicking the cell and looking at the Number Format box in the Number group on the Home tab in Excel. In the example, find the word Date in the Number Format box.

Cells B2 through F2 formatted with the Date format in Excel

To change the format of the numbers (yes, dates are numbers to Excel), select cells B2 through F2; click the down arrow in the Number Format box and choose the desired format. To choose a number without any special formatting (no dollar sign or decimal places, for example), choose General as shown in the example below.

Choosing General Number Format in Excel

After formatting the numbers with the General format, your numbers will look like this:

Numbers formatted with the General format in Excel

Notice that the newly formatted numbers in cells B2 through F2 are right-aligned in each cell. Right alignment is the default format for numbers. Numbers formatted with the General format have no decimal places, commas, or dollar sign.

Have an MS Office question? Post it at my 2educateyou website or Facebook page. And I have a PowerPoint Facebook page devoted just to PowerPoint!

From Formatting Numbers with the General Format in Excel page.

Applies to Excel/PowerPoint 2007 and 2010

Kathy

 

Share
Comments { 0 }

Remove files from the Recent List in Office 2010 Applications

 

One enhancement Microsoft made in Office 2010 is the ability to remove individual files from the Recent Presentations list on the File menu. To remove a file from the list:

  1. Click the File tab then click Recent from the menu.
  2. Right-click the file you’d like to remove from the list and choose Remove from List as shown in the example below:
Remove File from Recent List - PowerPoint 2010

Remove File from Recent List - PowerPoint 2010

It’s as easy as that! Removing a file from the list deletes the pointer to the file, not the actual file.

Unfortunately, this option is not available in Office 2007. You can, however, remove all the files from the list in Office 2007 (and 2010) applications. Here’s how to do it in PowerPoint 2007/2010:

  1. Click the PowerPoint 2007 Office button or PowerPoint 2010 File tab.
  2. Click the PowerPoint Options button (PP 2007) or the Option button (PP 2010)
  3. Click the Advanced link and scroll to the Displaysection.

    Changing the number of Recent Documents - PowerPoint 2010

  4. Use the Show this number of Recent Documents spin arrows (up and down arrows) to change the value. The default value for PP 2007 is 17 documents, and 25 documents for PP 2010.
  5. Click OK when you’re done.

If you have questions, please leave me a comment. I’d love to hear from you!

Kathy
Computer Applications Instructor
PowerPoint 2010 online class
PowerPoint 2007 online class
PowerPoint 2010 one-day workshop
Office 2011 for the Mac

 

Share
Comments { 1 }

Create a SmartArt Graphic in PowerPoint 2010 or 2007

SmartArt Graphics

This feature allows you to use shape, color, pictures, and text to make a point visually in Word, Excel, and PowerPoint 2010 (and 2007).

One way to work with this feature is to use the SmartArt button on the Insert tab. Another way is to use the Insert SmartArt Graphics button on a Title and Content slide layout in PowerPoint.

If you have existing text that you’d like to convert to SmartArt, select the text, and then click the Convert to SmartArt button in the Paragraph group on the Home tab.

There are over 80 SmartArt graphics to choose from in the SmartArt Gallery! The categories include List, Process, Cycle, Hierarchy, Relationship, Matrix, Pyramid, Picture, and Office.com.

Here’s a short video I created showing you two ways to create a SmartArt graphic in PowerPoint 2010:

Please post your questions or comments below.

Thanks!

Kathy
PowerPoint 2010
icon

PowerPoint 2007
icon

Share
Comments { 2 }