Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Pivot Tip 13 - Use Text to Columns to add extra fields

by
19th Jul 2006
Save content
Have you found this content useful? Use the button above to save it to your profile.

David Carter's series of Five Minute Tips aims to develop your skill at using Excel pivot tables. To test these formating tips for yourself, try them out on your copy of the pivot_practice.xls database.

Often with pivot tables you will find that your source data doesn't contain all the fields that are needed to produce a particular report.

When essential fields are missing from the source data, there are two ways to add them:

  • The data may be contained in an existing field, but it needs to be split out into a separate field. Use the Text to Columns command to do this.
  • The additional fields have to be pulled in from a lookup table.

    In this session we'll look at the first option - Text to Columns. If you have recently worked through Pivot Tip 12 and used the Concatenate command, you will find that Text to Columns is the exact opposite. Concatenate combines several fields into one, while Text to Columns breaks one field up into several.

    Practice Session ' create a new field for Manufacturer
    In the previous sessions you have been asked to save the practice database pivot_practice.xls on your own machine. If you HAVE done this, open it now and click on to the pivot worksheet. [If you HAVEN'T made your own copy, you should do so now. Follow the instructions at the end of this article.]

    First of all, amend the Layout of the pivot table as follows:
    Row = PRODUCT
    Column = DATE
    Data = Sum of NET
    Page = REP

    The first row should show:
    Compaq 1000mb, with sales of 2300 in May

    How to show product sales by manufacturer
    The pivot table currently shows monthly sales by product. We want to see them summarised by manufacturer, in order to negotiate better discounts.

    Click onto the Master worksheet containing the 27 rows of source data

    There are columns for Product and Product Group, but no column for Manufacturer.

    However, the first part of the Product field does contain the name of the manufacturer. We can create a Manufacturer field by splitting this out.

    1) Copy the PRODUCT field
    Make a copy of the PRODUCT field as follows:

    Click on the top of column E to highlight the PRODUCT column.

    Right click for menu. Select: Copy.

    Click on the top of column F to highlight the PRGRP column.

    Right click for menu. Select: Insert Copied Cells.

    The Product field is copied into column F and the other columns all move to the right.

    [Tip: If you made a mistake or just want to see what you've done, click on the Undo icon at the top (bent arrow pointing left)]

    2) Use Text to Columns to break up PRODUCT
    Now click on the top of column F to highlight the new PRODUCT column.

    From main menu, select: Data'Text to Columns.

    The Text to Columns wizard appears. It defaults to Delimited rather than Fixed Width.

    (A 'delimiter' is what separates the fields.) Delimited is correct, so click on Next.

    Step Two appears. Excel has defaulted to 'Tab' as the delimiter.

    This is wrong. Untick Tab. Tick Space below it.

    Vertical lines appear between each word where the spaces were. Next.

    Step Three appears. We only want the first column which contains the Manufacturer code, so we want to omit the second and third columns. Do this as follows:

    Click on the middle General to highlight the second column.

    At top right, click on Do Not Import Column (skip).

    Click on the right hand General, and make that column Skip column too.

    Select Finish. You get the message: Do You Want to Replace the Contents of the Destination Cells?

    Select: Cancel.
    [The message means that Excel is about to overwrite data in columns G and H. You don't want this.]

    3) Text to Columns ' second attempt
    What went wrong? Click on column F again, and select Data'Text to Columns.

    In Step One, select Delimited'Next. In step Two the three vertical lines are still there.

    To see the problem, scroll down the data until you see Sony 24' flat screen.

    This breaks up into four pieces. But Excel only created three columns. So 'screen' is going to get copied over column G.

    This is one of the faults of Text to Columns ' when Excel guesses how many columns are required, it only looks at the top few records.

    Select Cancel to get out of Text to Columns.

    Fortunately, it's not difficult to get around this. We'll insert a couple of blank columns between G and H.

    Click on the top of column G Prgrp to highlight. From main menu: Insert'Columns.

    One blank column is inserted. Now hit the F4 key to repeat. Another column is inserted.

    Now go through the Text to Columns procedure once again (don't forget to change General to Skip column on columns 2 and 3).

    When you press Finish, there's no message about overwriting destination cells. But 'screen' appears a few times in column G.

    In column F the manufacturer name has split out nicely. In F1, change PRODUCT to MANUFR.

    Remove columns G and H (highlight column, then from main menu Edit'Delete).

    4) Create the Sales by Manufacturer report
    We now have our Manufacturer field. Click back onto the Pivot worksheet.

    Click on the pivot. Right click for menu. Select: Refresh Data.

    Right click again for menu. Select: Pivot Table Wizard'Layout.

    MANUFR is now in the list of field names.

    Insert MANUFR in the ROW area above PRODUCT. OK. Finish.

    The pivot table now shows sales by product, grouped by manufacturer.

    To tidy it up a bit, right click on the grey field button MANUFR in A7.

    From the menu, select Group and Outline'Hide Detail.

    The report now shows total sales by manufacturer.

    End of session. Do NOT Save.

    Appendix: How to create the pivot worksheet
    The first articles in the series Tip 1 and Tip 2 and Tip 3 explained how to create the pivot worksheet. If you are new to pivot tables, it would be best for you to go through these first articles in full. If you are already familiar with pivot tables, create the pivot worksheet now as follows:

    Download the Pivot_Practice.xls database.

    Create the following pivot table:
    PRODUCT in the ROW area
    CUSTNAME in the PAGE area
    NET in the DATA area
    DATE in the COLUMN area

    OK. Finish.

    Now Group the dates by month as follows: Right click on the DATE field button in C3. Select: Group and Show Details'Group.

    Months is highlighted as the default. Scroll down to the bottom of the list under Quarters and highlight Years as well. OK.

    The pivot table now displays the transactions grouped by month ' Apr, May, Jun.

    The first line of the pivot table should read:
    Compaq 1000mb 2300 2300

    Change the name of the worksheet from sheet1 to pivot (double left click on the worksheet tab at the bottom).

    There should now be two worksheets ' pivot and master.

    Now save the Pivot Practice workbook on your own machine. Future articles in this series will use the pivot worksheet as their starting point.
    Related material in ExcelZone
    For more than four years, David Carter and AccountingWEB members have built up a massive stockpile of material on Excel and pivot tables. To delve more deeply into the subject, see:

  • David Carter's Five Minute Pivot Table Tips - index
  • Want to learn about Excel pivot tables? Start here
  • The Excel Compendium - Pivot Tables
  • 100 Best Time-Saving Ways to Use Microsoft Office by Simon Hurst

    Subscribe to the ExcelZone newswire
    To keep up with David Carter's Five Minute tutorials, click the button below to subscribe to the free fortnightly ExcelZone newswire. The subscribe function will take you back to the AccountingWEB home page after it adds your name to the subscription list.

  • Tags:

    Replies (0)

    Please login or register to join the discussion.

    There are currently no replies, be the first to post a reply.