Microsoft Office Tips, Tricks, and How-to's to help you use MS Office like a pro!

Carol's Corner Office

Category Archives: Excel 2007

Free Microsoft Excel 2007 articles

Separate Text into Multiple Columns in Excel 2007

Posted on by Posted in Excel 2007

A subscriber wrote to me recently with a problem. It seems he had copied information from the Internet and pasted into Excel. The problem was that all of the information was now contained in one column. He wanted to know how he could prevent that from happening in the future. I have received inquires in the past regarding this same area of interest. Sometimes the data in question had been copied from a database or an Word document.

Obviously, the information you have copied is not very useful to you if it is all contained in one column. In order to efficiently use your data you need to have the sorting, filtering and calculation options available to you and for that you need to have your data moved to separate columns.

Luckily, Excel provides you with a very efficient method of accomplishing this regardless of how many rows of data are involved, the Text to Columns function. Having said that, Text to Columns works best when using data from a plain text file. There must be some elements of consistency to your file; each row must be delimited for example, by a line break or a comma, etc. or contain a fixed number of characters for each column.

Follow the steps below to learn how:

  1. Examine your data to determine how it can best be delimited. Is each field separated by a comma or space?
  2. Select the cells you would like separated into columns. Bear in mind that this must be a single range of data and must be one column wide only.
  3. Make certain there are ample empty columns to the right of the selected data to accommodate the separation.
  4. Now you will open the Text to Columns dialog box:
  5. Click on the Data tab and in the Data Tools group, click on Text to Columns.
  6. The Wizard will now open to guide you through the rest of the process.
  7. Select the type of data you are working with (i.e., delimited or Fixed Width)
  8. Click Next.
  9. You will now tell Excel which delimiter is used in your data (if you are using delimited data) and you will see a preview of how your data will be separated. Assuming everything is in order, click Next.
  10. In the next step in the Wizard, the default format for each column is General and General will convert numeric values to numbers, date values to dates and everything else to text. Should you want, you can set a different format for any column. As you click each option, an explanation of that option will be displayed to the right.
  11. If a column contains numeric data with leading zeros that you want to keep, you will need to set the format to Text.
  12. If there is data that you do not want or need, you can select Do not import column (skip).
  13. Click Finish.

Your data will now have been separated into columns.

Transfer an Older Personal.xls to Excel 2007

Posted on by Posted in Excel 2007

If you are getting a new PC and your old PC was running an older version of Excel but your new system will have Excel 2007 what do you do?

Well you need to move your old Personal.xls to your new computer if you want to have the use of all your macros.

You might think you could just copy and paste the old Personal.xls from your old computer to your new one but that is not the answer. MS Excel uses a new file format based on XML and so it is best to have your Personal.xls in the new format. That does not mean that you cannot get the old information to the new system though.

Follow the steps below to learn how to accomplish this:

  1. Copy the old Personal.xls file to the new system.
  2. Rename it something else, such as OldPersonal.xls.
  3. On the new system, start MS Excel and open the Excel 2007 Personal.xls file.
  4. Open the OldPersonal.xls file.
  5. Copy the macros from the old file to the new file.
  6. Save the new Personal.xls file.

You will now have the use of all your old comfortable Excel macros!

Give your Right-Aligned Text Some Space in Excel 2007

Posted on by Posted in Excel 2007

I find that it is more visually pleasing to create my column labels in Excel and have them right-aligned. The only problem with this arrangement is that they are then pressed right up against the borders, which is not very visually appealing.

The good news is that you can create some space between your text and the border and still have it right aligned.

Follow the steps below to learn how:

Select your text cells.

  1. In 2007, on the Home tab, click the small arrow on the bottom-right corner of the Number group.
  2. On the Number tab, click Custom in the Category list.
  3. In the Type field, enter the @ symbol followed by a blank space.

The @ symbol represents your text. The @ sign followed by a blank space informs MS Excel that if there is text in this cell, and to display a blank space after the text.

  1. Click OK.

Now, your text labels will be set off from the border by the width of one blank space. If you prefer, you can use more spaces until you are pleased with the appareance.

Right Aligned Spaces

How to Change the Default Chart Type in Excel 2007

Posted on by Posted in Excel 2007

If you find that you are usually changing the default chart in Excel to something more to your liking, doesn’t it make sense to just change the default to the type of chart that you prefer to use when you need one? Again, it is all about saving time and keystrokes.

Follow the Steps below to learn how:

  1. Click on the Insert tab and click the Charts dialog launcher in the bottom-right corner of the Charts group.
  2. Select the type of chart you would like to use as a default, including subtype.
  3. Click the Set as Default Chart icon at the bottom.

It will be much easier to now create charts that you prefer immediately, rather than redoing it!

How to Exceed the Limits with Excel 2007 Footers

Posted on by Posted in Excel 2007

The default Excel footer field is limited to 255 characters and sometimes that limit poses a problems for companies. Why a problem? Well some companies like to include a logo in their footer, as well as confidentiality disclaimers, etc.

You will be happy to know that there is a way around the 255 character footer limitation. Follow the steps below to learn how:

  1. Open an existing workbook and tab to the worksheet in which you would like to place your footer, or create a new one.
  2. Insert a text box across the bottom of your worksheet, taking care to make sure that it stays within the page parameters. Note: This may be easier for you if you use Page Break View.
  3. In MS Excel 2007, you will find the Text Box on the Insert Tab of the Ribbon.
  4. Right-click on your text box and select Add Text.
  5. Enter the desired text.
  6. Format the text to your liking, including the font, text alignment, background color, text color, etc.
  7. Now right-click and copy your text box.
  8. Next, paste it in MS Paint.
  9. You can access MS Paint by clicking on Start | Programs | Accessories | Paint.
  10. Resize the Paint field by dragging the corner or edges so that it is the size of the text box.
  11. Save the Paint object as a .png file and do not forget to name it something intuitive so that it is easy to recall the next time you would like to use it.
  12. Return to your Excel worksheet.
  13. Open the footer – in version 2007 click Header & Footer on the Insert tab.
  14. Select Custom Footer.
  15. Click inside the left section of the footer.
  16. Click the Insert Picture icon.
  17. Navigate to your saved .png file and click on Insert.

Your worksheet now has a custom footer and there were no text limits when creating it!

Using the Convert Function in Excel 2007

Posted on by Posted in Excel 2007

There have been times in my career when I have received Excel Workbooks from someone and when I printed them out I got a huge surprise as the degrees of heat were not in Fahrenheit or the distance was not measured in miles. Instead, they were listed in Celsius and meters! Wouldn’t it be great if you could just turn those meters into miles, etc?

Well, if you have the Analysis ToolPak loaded in MS Excel, you can use the Convert function to perform these and lots of other calculations such as hours to minutes or gallons to teaspoons!

If you are not sure if you have the Analysis ToolPak, it is very easy to add. Follow the directions below to find out how:

  1. In Excel 2007, click the Office button and Excel Options.
  2. Click Add-ins.
  3. Select Excel Add-ins in the Manage box at the bottom.
  4. Click Go.

Once you have installed the ToolPak, you can now use the Convert function. Just click in the cell where the measurement resides and use the following syntax: =CONVERT(A2, “C”,”F”)

MS Excel will now convert Celsius to Fahrenheit.

You will be able to convert a huge amount of measurements using this great Add-In. To see the full list click here.

Recalculation in Excel 2007

Posted on by Posted in Excel 2007

Here is an alternative method that will show recalculations that are visible in Excel.

It turns out, you can take a picture link of a cell and place that picture link any place you like in your Workbook and then when the cell changes, it will show the changes in the picture link. I thought that was pretty cool and so I naturally had to share it with you!

You can use either of the two methods below:

  • You can use the Paste Picture Link command after copying the cell of interest; or
  • You can scroll through the Commands Not on the Ribbon option in MS Excel Options, locate the Camera tool and add it to the Quick Access Toolbar (QAT), so it will be readily available to take a snapshot of any cell you would like to keep a keener eye on.

I think you will have fun experimenting with this!

Making Use of Paste Options in Excel 2007

Posted on by Posted in Excel 2007

If you have only used Paste in MS Excel to paste content that you have cut or copied from another location, you are not making use of a very powerful tool in this latest version of MS Excel. For a long time now in past versions, Paste Special has given you the ability to paste only individual components of your selection, such a values or formatting or even some combinations of components such as pasting values and numbers formats but no other formatting.

Follow the steps below to access Paste Special:

  • Click on CTRL + ALT + V (my favorite), which also works in MS Word and PowerPoint.

Or

  • On the Home tab, click to expand Paste Options and then click Paste Special.

You can avoid lots of formatting snafus by availing yourself of these options. The Paste Special shortcuts that were available in previous versions of Excel from a pop-up menu on the Standard toolbar are now right in front of you when you click Paste on the Home tab. These options include some of my favorite time savers, such as Paste Values and No Borders. No Borders will paste everything except any borders on the source content which will leave the destination borders intact.

The Paste option and the Paste Special dialog box also offer the Transpose option, which will paste copied columns of data as rows and vice versa.

It also offers Operations. With Operations, you copy a value, select the range to which you would like to apply the value and then select the option to Add, Subtract, Multiply, or Divide by the copied value for each cell in the destination page.

Makes you want to run out and get your license for MS Office 2007 doesn’t it?

Excel 2007 Feature Finds Unique Values In Your Data

Posted on by Posted in Excel 2007

The end of the month has rolled around and your superior wants you to submit a memo to report on the sales for the entire month. Your spreadsheet is comprised of 100 invoices, with Invoice Numbers in Column A, Client Name in Column B and Product Title in Column C.

Many of your clients place multiple orders during the month and therefore you will need a quick way to obtain a listing of each client that placed an order last month.

In Excel versions prior to 2007, you would have had to generate this data using either Excel’s Advanced Filter or Pivot Table feature, but luckily, MS  Excel 2007 has a new feature called Remove Duplicates, that supplies you with an additional, speedier method of producing this list.

Follow the steps below to learn how:

  1. Launch MS Excel if it is not already open.
  2. Open your Monthly Sales spreadsheet
  3. Select the Client Name column.
  4. Copy the data to a blank section of your worksheet – You should have at least one blank column between the original data and the copied data.
  5. Click inside any cell within the copied data.
  6. Click the Data tab.
  7. In the Data Tools group, choose Remove Duplicates.
  8. Click OK.

MS Excel will remove all duplicate customer names from the copied list.
You can also use Remove Duplicates to produce a list of unique values for more than one field.

To generate a list of clients and the products they ordered, follow these steps:

  • Select the Client Name and Product Name columns.
  • Copy the selected data to a blank area of the worksheet.
  • Click inside any cell within the copied data.
  • Click the Data tab.
  • In the Data Tools group, choose Remove Duplicates.
  • Click OK.

MS Excel will generate a list of each client and the products they ordered, excluding duplicate orders for the same product from the same client.

You can then use the Sort function to make the list a bit easier to read by following the steps below:

  • Click any one of the cells in the Remove Duplicates results list.
  • Click the Data tab.
  • Click the Sort button.
  • Click the My Data Has Headers check box.
  • Click the Sort By drop-down arrow and select Client Name.
  • Click OK.

Limit What is Shown in the Formula Bar in Excel 2007

Posted on by Posted in Excel 2007

In Excel 2007 you now have the ability to prevent the Formula bar from displaying more than one line of text. In this way, the worksheet is not obscured by the Formula bar when a cell with a large amount of content is selected.

One of my subscribers wrote to me inquiring if this was an option in Excel 2003 or earlier version. I hated to tell him, but the answer is no. This feature is new in Excel 2007 and there is no equivalent of it in earlier versions.

  • What folks did in the past, in earlier versions, was turn off the display of the Formula bar completely by clicking View | Formula Bar.
  • Alternatively, some folks inserted a blank “dummy row” at the top of their worksheet that they didn’t mind being overwritten with the expanding Formula bar.

I am sorry that I cannot give you a better way to accomplish this, but thank goodness we now have version 2007!