Excel Spreadsheet Lessons - PDF by ojb58518

VIEWS: 23 PAGES: 7

More Info
									                                                 Lesson #3:

 How to Prepare Excel Reports with Changing Numbers of Rows and Columns
                            from a Pivot Table

Problem:
You need to regularly make an Excel spreadsheet report that is based on data from a pivot table.
However, your readers want an ordinary Excel spreadsheet, not one with the pivot table embedded.

 In the earlier lessons, we saw how Excel pivot tables and charts make it easy to “slice-and-dice” data in
a way that helps you see relationships and trends you otherwise would miss. But how do you share this
information with your managers and associates?

Most of your audience may not want to analyze the data because you already did the analysis. This is
especially true if you will be doing the analysis regularly (like weekly or monthly). They want a polished
report they only have to read. You also want to send a presentable report, because it provides you with
recognition for your analytic skills.




In addition, pivot tables (like the one shown above) are dynamic. It is possible for each user to be looking at
a different view of the data, making it difficult for them to discuss the report.


In other words, we want to get from a pivot table that looks like the one above, to a regular Excel worksheet
with a layout we can completely control, so it looks more like this:
Challenges:
Pivot tables and pivot charts are great for analysis. But it is hard to move their results into regular Excel
worksheets, because the pivot table has interactive controls that should not be transferred to the
report. This forces you to copy only the values of the pivot table data to the report worksheet. But that,
in turn, destroys the formatting of the data cells in the report worksheet.

Even worse, the number of rows and columns in the pivot table can change with every “run” or use of
the pivot table. This affects at least the following (and perhaps others):

    •    If the target worksheet contains background colors, special text formats, or other design
         elements, changes in the number of rows and columns can cause those design elements to be in
         the wrong cells of the worksheet.

    •    If the target worksheet contains formulas that refer to the data, the formulas may end up
         referencing the wrong range of cells.

    •    If the target worksheet has charts, the number of series in the chart will not adjust to the
         amount of data.

Solution:

The manual method to copy the data from a pivot table to an Excel worksheet, while preserving the design
elements, formulas, and charts of the target worksheet, involves the following general steps:


    1.   Open the worksheet with the pivot table in Excel.
    2.   Count the number of rows and columns of data that you will be copying to the report. You may need
         to note this down, so you can refer to it, later.
    3.   Open the report worksheet and note how many rows and columns have been set aside for the data
         coming from the pivot table.
    4.   If the number of rows set aside is less than the number of rows you will be copying over, insert
         additional rows, preferably into the middle of the data area.
    5.   If the number of columns set aside is less than the number of columns you will be copying over,
         insert additional columns, preferably into the middle of the data area.
    6.   Copy the data from the pivot table.
    7.   Go to the report worksheet, right-click, and select "Paste Special". From the menu that appears,
         select "values". This ensures that Excel will not copy the pivot table's field controls and other
         elements that only work within the pivot table.
    8.   Re-format the cells of the report that were affected by the copy. This is necessary, because pasting
         only the values from the pivot table destroyed any formatting in the report worksheet. You need to
         manually undo the damage.
    9.   Review and possibly fix any formulas in the report worksheet that were affected by the copy. For
         example, it may be necessary to copy formulas into the rows and columns that you inserted in steps
         4 and 5.
    10. If there are any charts, delete or create new series to match the data you copied into the report.
          You may also have to adjust the ranges of the data and the category, if the report does not use
          named ranges for those.
    11. Double-check everything. It is very easy to overlook an error in a formula, or a range, caused by
          the change in the number of rows and/or columns.


Alternatively, you can use Xcelential Pro together with Excel. Xcelential Pro automates the process of
getting pivot-table results into a regular Excel worksheet, while preserving/updating design elements,
formulas, and charts.

You can start with a report template, so that even the initial report is generated by the program based
on the pivot table’s contents. You may then customize the report worksheet’s look, so that future runs
will produce even better-looking reports.

Lesson:
This lesson will focus on getting the data from a pivot table into a regular Excel worksheet. To minimize
the time required to go through the lesson, we assume that you completed the previous lesson. If you
have not yet done so, please click here to download and follow it.

To follow this lesson you will need to have installed Xcelential Pro. If you have not yet downloaded the
program, go to: http://www.xcelential.com/get-XcelentialPro.html. If you have downloaded it, but have not yet
installed it, please install it now.

Let’s begin from the end of Lesson 2. These are the steps you need to perform, in order to transfer the
changing contents of a pivot table into an Excel report.

    1. Start Xcelential. If you went through Lessons 1 and 2, you will see a dashboard that looks like
       this:




    2. Right-click on the panel with the Campaigns data (the lower panel in the illustration, above
       example), and select the “Versions” option. This screen will appear:
3. Select several instances of the spreadsheet file. Check the box labeled “Include hidden data”,
   to instruct Xcelential to grab all the detailed data in the spreadsheet. Also check the box
   labeled “use new template when merging” (ensure that both check boxes are checked). Click
   “Merge”.

4. When the form with the list of template files appears, select “GenPivotColumn.xls”. Then click
   ok.

5. Xcelential will bring up a form that allows you to select from several options. The default
   options are already good. Just click ok.




6. Xcelential will generate a report based on the template you selected. It will then open the
   worksheet for you, so you can see the resulting report, which will look like this:
That report probably could use better formatting. It is based on a very generic template, with not
even a title that is specific to the data. Let’s fix it up.

All of this work will be in Excel, so we won’t go into it in much detail. Feel free to do any of the
following without having to concern yourself with Xcelential:

    1. Resize or move the chart anywhere within the sheet. However, do NOT move the chart to
       another sheet, as Xcelential cannot keep track of a chart on another sheet.
    2. Change the chart type, axis, legend, title, gridlines, or other chart features. Xcelential
       does not get affected by those settings.
    3. Change fonts, colors, column widths, row heights, or other properties of the data table.
    4. Change the report title, the run-date, and the other text on the first row.
    5. Add or insert rows and columns before or after the data table, as long as they will not be
       affected if the data table is resized. Remember that the pivot table is very volatile, so the
       number of rows and columns of data can change with every run. We will take up in later
       sections how to add formulas to cells outside of the pivot-table data that was copied into
       the report, but for this first exercise, let’s keep it simple.

  After making whatever changes you want, save the Excel file. In our practice, we made a
  number of those changes, to format a report that looks like this:
      Now, let’s re-generate, but this time w/ new data.

          1. Switch back to Xcelential.
          2. Right-click on the dashboard panel we were working with, and select “Versions”.
          3. Select a different set of dates from the Versions form, and check the “include hidden
             data” checkbox. Then press “merge”.
          4. In the next form, click “OK”.

Note that those are the same steps you would perform to consolidate data and generate a pivot table
with Xcelential. Generating a presentation-quality report from spreadsheets requires no additional
steps at all!

In net, it takes just 3 steps to produce a new presentation-quality report from Xcelential.

Result/Conclusion:
Xcelential Pro automates the process of generating custom-formatted reports from data whose rows
and columns may change each time the report runs. This would very often be the case if the data comes
from a pivot table.

Note that Xcelential Pro does all this, on top of its ability to consolidate data from multiple versions of a
spreadsheet. We can sum up what Xcelential Pro does, based on the first 3 lessons:

          1.   It creates an analysis spreadsheet by copying the template you selected.
          2.   It opens all the source spreadsheet files you selected from a list it presents to you.
          3.   It locates the data you want consolidated in each file.
          4.   It copies the collected data to the file it created.
          5.   It creates the pivot table with a pivot chart, and starts an initial analysis.
          6.   It uses an Excel worksheet you designate as a template for the desired output.
          7. It moves the data from the pivot table to the templated Excel worksheet, while preserving
             the integrity of the design elements, formulas, and charts of the target worksheet, even if
             the number of rows and columns has changed.

On a final note, you can also use the following outputs of Xcelential, in addition to the report worksheet:

          1. Use the worksheet with the consolidated data (for example, to upload the data to a
             database).
          2. Use the pivot table in the analysis workbook it created.

Was this lesson helpful? Feel free to enter comments and suggestions in our forum
(http://www.excelential.com/bb/viewtopic.php?f=7&t=374)

								
To top