How to convert table to excel range.

The life of a person from the world of technology is an incredible combination of numbers and indicators that periodically require systematization. To accomplish the latter, one has to use special technical tools. Our article will prove that pivot tables in Excel are accessible even for dummies.

A bit of theory

Excel PivotTables (for the Dummies) are a kind of registry that contains a specific piece of data from a source for analysis and is depicted so that logical relationships can be traced between them. The basis for its design is a specific list of values.

Before starting work, it is worth preparing for it necessary materials, which can be reduced for analysis. When forming a preparatory version, make sure that the data is classified, for example, numbers are not confused with a letter designation, and all columns have headings.

Pivot tables in Excel are indispensable for those who, by occupation, deal with large quantity figures that periodically need to be systematized and reported. Excel program helps to calculate and analyze a large number of values, saving time.

Advantages of using this type of data grouping:

• during work, special knowledge from the field of programming is not needed, the method is also suitable for dummies;
• the ability to combine information from other primary sources;
• you can replenish the base copy new information by tweaking the settings a bit.

Learning how to work with PivotTables in Excel doesn't take long and can be video-based.

An example of creating an Excel pivot table - an algorithm for dummies

Having familiarized ourselves with the basic theoretical nuances about pivot tables in Excel, let's move on to applying them in practice. To start creating a pivot table in Excel 2016, 2010 or 2007, you need to install software. As a rule, if you use the system programs Microsoft Office, then Excel is already on your computer.

By running it, you will see a vast field, divided into a large number of cells. For more details on how to make pivot tables in Excel, the video tutorial above will tell you.

With the help of the following algorithm, we will take a detailed look at an example of how to build a pivot table in Excel.
On the panel at the top of the window, go to the "Insert" tab, where in the left corner select "Pivot Table".


Next, a dialog box opens on the screen, where you need to specify the appropriate parameters. At this point in creating PivotTables in Excel, there are several important points. If, before starting the formation of the registry, you set the cursor icon on the sheet, then the empty lines of the window will be filled automatically. Otherwise, the address of the data range must be designated by yourself.


Let's consider in more detail the independent filling of the items in the dialog box.

We do not leave the first line empty, otherwise the program will give an error. If there is a source from which you plan to transfer data, then select it in the "Use external data source" item. An external source refers to another Excel workbook or a set of data models from a DBMS.

Label each column in advance.

Choose a place where the future frame with cells will be located. It can be a new window or this sheet, we recommend using another sheet.


Having fixed all the settings, we get the finished base. On the left side is the area where the base of the future frame is located. On the right side there is a settings window that helps you manage the registry.


Now you need to figure out how this whole structure is built. In the Free Table Fields settings window, you designate the data that will be present.


The whole structure is built in such a way that text data plays the role of unifying elements, and numeric data shows consolidated values. For example, let's combine all receipts by departments and find out how much each received goods. To do this, check the box next to two headings: the department and the cost of the goods in the lot.


Pay attention to how this data is located in the lower area of ​​​​the settings panel.


The department automatically went to lines, and numeric data to values. If you try to click on any column with numbers, they will appear in this area. And in the table itself will be added new column.


This is where the summation takes place. Now we can find out from our report how many goods arrived in each department and their total cost.

You can freely adjust the width of the columns for optimal data layout. Just expand the column widths or row heights the way you're used to in Excel.


If you don't like this orientation, you can drag the row names to the column area with the mouse - just move the mouse, press the left button and drag.


As for the calculation of results, the sum is far from the only function. To see what else Excel has to offer, click on the name in the Values ​​area and select the last command.


In the value fields options, you will find many options for analysis.


For each value, you can choose your own function. For example, let's add the "Price" field and find the maximum price of a product in each department. In fact, we find out how much the most expensive one costs.


Now we see that the “Accessories” department received goods worth 267,660 rubles, while the most expensive one has a price of 2,700 rubles.
The "Filters" area allows you to set the criteria for selecting records. Let's add the "Date received" field by simply checking the box next to it.


Now the pivot table in Excel looks awkward if we need to analyze by date. Therefore, we will move the date from the lines to the filters - just drag it, as indicated above.


The result of these actions was the appearance of another field on top. To select a date, click on the arrow next to the word "All".


Now we have the choice of a specific day, to open the list, click on the triangle in the right corner.


You can also select values ​​for the department.


Uncheck the ones you don't care about and you'll only get necessary information.

While working, you may encounter a similar message "Invalid Excel PivotTable Name". This means that the first line of the range from which they are trying to extract information is left with blank cells. To solve this problem, you must fill in the gaps in the column.

Refresh data in a pivot table in Excel

An important question is how to make and update a PivotTable in Excel 2010 or other version. This is relevant when you are going to add new data. If the update will take place only for one column, then you need to click anywhere in it right click mice. In the window that appears, click "Update".


If such an action needs to be carried out immediately with several columns and rows, then select any zone and on top panel open the "Analysis" tab and click on the "Update" icon. Then choose the desired action.


If the pivot table in Excel is not needed, then you should figure out how to delete it. It won't be a big deal. Select all components manually, or using the keyboard shortcut "CTRL + A". Next, press the "DELETE" key and the field will be cleared.

How to add a column or table to an Excel PivotTable

To add an additional column, you need to add it to the original data and expand the range for our registry.


Click the Analysis tab and open the data source.


Excel itself will offer everything.


Refresh and you will get a new list of fields in the setup area.

You can add a table only if you “glue” it with the original one. You can replace a range within an existing one, but you cannot add another range on the fly. But you can create a new pivot table based on several original ones, even located on different sheets.

How to make a pivot table in Excel from multiple sheets

To do this, we need a pivot table wizard. Let's add it to the panel quick access(the top left of the window). Click the dropdown arrow and select More Commands.


Select all teams.


And find the pivot master Excel tables, click on it, then on "Add" and OK.


The icon will appear at the top.


You must have two identical tables on different sheets. We have data on receipts in departments for May and June. Click on the PivotTable Wizard icon and choose Range Consolidation.


We need multiple fields, not just one.


In the next step, select the first range and click the "Add" button. Then switch to another sheet (click on its name at the bottom) and "Add" again. You will have two ranges.

You don't need to select the entire table. We need information about admissions to departments, so we have allocated a range, starting with the "Department" column.
Give a name to everyone. Click circle 1, then enter "May" in the field, click circle 2 and enter "June" in field 2. Don't forget to change the ranges in the area. The one we are naming should be selected.

Click "Next" and create on a new sheet.


After clicking on "Finish" we get the result. This is a multi-dimensional table, so it is quite difficult to manage it. Therefore, we chose a smaller range so as not to get confused in the measurements.


Please note that we no longer have clear field names. They can be pulled out by clicking on the items in the upper area.


By unchecking or checking the boxes, you adjust the values ​​you want to see. It is also inconvenient that the calculation is carried out for all values ​​the same.

As you can see, we have one value in the corresponding area.

Changing the structure of a report

We have step by step analyzed an example of how to create an Exce pivot table, and how to get data of a different type we will describe further. To do this, we will change the layout of the report. By placing the cursor on any cell, go to the "Designer" tab, and then "Report Layout".

You will have a choice of three types for structuring information:

• compressed form

This type of program is applied automatically. The data is not stretched, so there is little to no need to scroll through the images. You can save space on signatures and leave it for numbers.

• Structured form

All indicators are presented hierarchically: from small to large.

• tabular form

The information is presented under the guise of a register. This makes it easy to transfer cells to new sheets.

By stopping the selection on a suitable layout, you fix the adjustments made.

So, we have told you how to compile the fields of the MS Excel 2016 pivot table (in 2007, 2010, act by analogy). We hope this information will help you to quickly analyze the consolidated data.

Have a great day!

Pivot tables are necessary for summarizing, analyzing and presenting data that are in "large" source tables, in various cuts. Consider the process of creating simple Pivot Tables.

Pivot tables (Insert/ Tables/ Pivot Table) can be useful if the following conditions are simultaneously met:

• there is an initial table with many rows (records), we are talking about several tens and hundreds of rows;
• it is necessary to carry out data analysis, which requires data sampling (filtering), their grouping (summation, counting) and presentation of data in various sections (preparation of reports);
• this analysis is difficult to carry out on the basis of the original table using other tools: ( CTRL+SHIFT+L), ;
• the source table satisfies certain requirements (see below).

Users often avoid using Pivot tables, because sure they are too complicated. Indeed, learning any new tool or method takes time and effort. But, as a result, the effect of mastering the new should exceed the invested efforts. In this article, we will understand how to create and apply Pivot tables.

Preparing the initial table

Let's start with the requirements for the original table.

• each column must have a heading;
• each column should only contain values ​​in one format (for example, the Delivery Date column should contain all values ​​in the format date; column "Supplier" - company names only in text format or you can enter the Supplier Code in numerical format);
• the table should not contain completely blank rows and columns;
• “atomic” values ​​must be entered into the cells, i.e. only those that cannot be split into different columns. For example, you cannot enter an address in one cell in the format: "City, Street name, building number". You need to create 3 columns of the same name, otherwise pivot table will work inefficiently (if you need information, for example, in the context of the city);
• avoid tables with "wrong" structure (see figure below).

Instead of producing repeating columns ( region 1, region 2, …) that will have blank cells in abundance, rethink the table structure as shown in the figure above (All sales values ​​should be in one column, not spread across multiple columns. In order to implement this, you may need to maintain more detailed records (see figure above), rather than listing total sales for each region).

More detailed tips on building tables are set out in the article of the same name.

Makes the build process a little easier. pivot table, the fact that if the original ( Insert/ Tables/ Table). To do this, first bring the source table in accordance with the above requirements, then select any cell in the table and call the menu window Insert/ Tables/ Table. All fields in the window will be automatically filled in, click OK.

Now check the box next to the Sales field in the Field List.

Because If the cells in the Sales column are in numeric format, they will automatically be placed in the Values ​​Field List section.

With a few mouse clicks (six to be exact) we created a Sales report for each Product. The same result could be achieved using formulas (see article).
If it is required, for example, to determine the sales volumes for each Supplier, then for this we will uncheck the box in the List of fields for the Goods field and check the box for the Supplier field.

Drilling down on PivotTable data

If you have questions about what data from the original table was used to calculate certain values pivot table, then it is enough to double-click on a specific value in pivot table to create a separate sheet with rows selected from the original table. For example, let's see which records were used to summarize the sales of the Product "Oranges". To do this, double-click on the value 646720. A separate sheet will be created with only the rows of the original table related to the “Oranges” Product.

Update a PivotTable

If after creating pivot table new records (rows) were added to the original table, these data will not be automatically taken into account in pivot table. To update pivot table select any of its cells and select the menu item: menu Working with PivotTables/ Options/ Data/ Refresh. The same result can be achieved with context menu: select any cell pivot table Refresh.

Deleting a PivotTable

Delete pivot table possible in several ways. The first is to simply remove the sheet from pivot table(unless it has other useful data, such as the original table). The second way is to delete only itself pivot table: select any cell pivot table, click CTRL+ A(all will be selected) pivot table), press the key Delete.

Changing the Totals Function

While creating pivot table grouped values ​​are summed by default. Indeed, when solving the problem of finding sales volumes for each Product, we did not care about the totals function - all Sales related to one Product were summed up.
If it is required, for example, to count the number of sold batches of each Product, then the totals function must be changed. For this in pivot table Totals per/ Quantity.

Changing the sort order

Now let's modify our Consolidated report. First, let's change the sort order of the names of the Goods: sort them in the reverse order from Z to A. To do this, through the drop-down list at the head of the column containing the names of the Goods, enter the menu and select Sort from Z to A.

Now let's say that the Baranka Item is the most important item, so it should be displayed on the first line. To do this, select the cell with the Baganka value and place the cursor on the border of the cell (the cursor should take the form of a cross with arrows).

Then, pressing the left mouse button, drag the cell to the topmost position in the list, right below the column heading.

After the mouse button is released, the Bagel value will be moved to the topmost position in the list.

Change the format of numeric values

Now let's add a digit group separator to numeric values ​​(the Sales field). To do this, select any value in the Sales field, call the context menu with the right mouse button and select the menu item Numeric format…

In the window that appears, select the number format and check the box Thousand separator.

Adding new fields

Suppose you want to prepare a report on the sales of Goods, but broken down by Regions of sale. To do this, add the Sales region field by checking the appropriate checkbox in the Fields list. The Sales Region field will be added to the Row Names area of ​​the Field List (to the Product field). By changing in the area Row names In the list of fields, the order of the fields Product and Region of sale, we get the following result.

By highlighting any Product name and clicking the menu item PivotTable Tools/ Options/ Active Field/ Collapse All Field, can be rolled pivot table to display only sales by Region.

Adding columns

Adding the Sales region field to the rows area resulted in pivot table expanded to 144 lines. This is not always convenient. Because sales were made only in 6 regions, it makes sense to place the Sales region field in the column area.

pivot table will take the following form.

Swapping columns

To change the order of columns, you need to grab the column heading in pivot table drag it to the right place.

Removing fields

Any field can be removed from the PivotTable. To do this, move the mouse cursor over it in the Field List (in the Report Filter, Report Names, Column Names, Values ​​areas), press the left mouse button and drag the field to be deleted outside the Field List.

Another way is to uncheck the box next to the field to be deleted at the top of the Field List. But, in this case, the field will be deleted from all areas of the Fields List at once (if it was used in several areas).

Adding a filter

Suppose that it is necessary to prepare a report on the sales of Groups of Goods, and it must be done in 2 versions: one for batches of Goods that brought profit, the other for unprofitable ones. For this:

• PivotTable, click menu item ;
• Put a tick in the List of fields near the fields Group, Sales and Profit;
• Move the Profit field from the Row Names area of ​​the Field List to the Report Filter area;

Type of resulting pivot table should be like this:

Now taking advantage Drop-down (drop-down) list in a cell B1 (Profit field) you can, for example, build a report on the sales of Groups of Goods that brought profit.

After pressing the OK button, the Sales values ​​of only profitable Lots will be displayed.

Please note that in the Field List pivot table a filter icon appeared opposite the Profit field. You can remove the filter by unchecking the checkbox in the List of fields.

You can clear the filter through the menu PivotTable Tools/ Options/ Actions/ Clear/ Clear Filters.

Also the data is available through the dropdown list in the headers of the rows and columns Pivot table.

Multiple totals for one field

• Clear the previously created report: select any value pivot table, click the menu item PivotTable Tools/ Options/ Actions/ Clear/ Clear All;
• Check the boxes next to the Product and Sales fields at the top of the Field List. The Sales field will automatically be placed in the Values ​​area;
• Drag and drop another copy of the Sales field into the same Values ​​area. V pivot table there will be 2 columns counting the amount of sales;

• v pivot table select any value in the Sales field, right-click the context menu and select Totals per/ Quantity. Problem solved.

Disable total rows

The totals bar can be disabled via the menu: Working with PivotTables/ Constructor/ Layout/ Grand totals. Don't forget to pre-select any cell pivot table.

Grouping Numbers and Dates

Let's assume that you want to prepare a time-of-sale report. As a result, you need to obtain the following information: how many consignments of the Goods were sold in the period from 1 to 10 days, in the period of 11-20 days, etc. For this:

• Clear the previously created report: select any value pivot table, click the menu item PivotTable Tools/ Options/ Actions/ Clear/ Clear All;
• Check the box next to the Sales field (the date of the actual sale of the Goods) at the top of the Field List. The Sales field will automatically be placed in the Values ​​area;
• select the only value of the Sales field in pivot table, call the context menu with the right mouse button and select the item Totals per/ Quantity.
• Drag another copy of the Sales field with the mouse to the Row Names area;

Now pivot table shows how many consignments of the Goods were sold in 5, 6, 7, ... days. There are 66 lines in total. Let's group the values ​​with a step of 10. To do this:

• Highlight one value pivot table in the Row Names column;
• Select an item from the menu Group by field;
• Fill in the window that appears, as shown in the figure below;

• Click OK.

Now pivot table shows how many consignments of the Goods were sold in the period from 1 to 10 days, in the period of 11-20 days, etc.

To ungroup values, select Ungroup on the menu Working with PivotTables/ Options/ Group.

A similar grouping can be carried out by the Delivery date field. In this case, the window Group by field will look like this:

Now pivot table shows how many consignments of the Goods were delivered each month.

Conditional Formatting PivotTable Cells

To cells pivot table you can apply the rules as you would to cells in a normal range.
Let's single out, for example, the cells with the 10 largest sales volumes. For this:

• Select all cells containing sales values;
• Select a menu item Home/ Styles/ Conditional Formatting/ Rules for selecting the first and last values/ The first 10 elements;
• Click OK.

Today we will talk about TABLES. Not about tables, namely about TABLES. This is exactly what Microsoft suggested to call those wonderful tables, which will be discussed below. They appeared in their infancy in Excel 2003 and were called "lists" there. In Excel 2007, they were brought to mind and renamed TABLES (TABLES), and what all normal people used to call tables is now proposed to be called a RANGE (range). In Russia, this approach did not take root, and why would people change the established terms retroactively, so we will call TABLES " smart tables", and let's leave the tables in their common sense alone.

Smart tables

It must be admitted that the term smart tables captures the essence of the phenomenon quite well. Indeed, working with smart spreadsheets in Excel is much easier and more enjoyable than with regular ones. However, not everyone knows about them. Let's fill this gap, because smart tables will truly change your life and you will not want to go back to regular tables after that.

What are they needed for?

Those people who work a lot in Excel eventually understand that when creating their tables, they must adhere to certain strict rules.

1. Give all columns unique column names.
2. Do not allow empty columns and rows in the table.
3. Do not allow heterogeneous data within the same column. If you have already decided that, for example, the sales volume in pieces should be stored in column E, then you do not need to enter the sales volume there, say, in money, for some of the table rows.
4. Do not merge cells unless absolutely necessary.
5. Format the table so that it looks the same in all its parts. That is, it is elementary to draw a grid, highlight the column headings with color.
6. Freeze areas so that the title is always visible on the screen.
7. Set the default filter.
8. Insert a row of subtotals.
9. Use absolute and relative references in formulas wisely so that they can be dragged without the need to make changes.
10. When working with a table, do not highlight rows/columns outside the table. This craze, by the way, is very common - to select the entire row or entire column with one mouse click and paint over. And it doesn't give a damn that there are 100 rows in the table, and in addition to them, another 1,000,000 rows were painted over. And then innocently ask: "Why do my files weigh so much?"

Compliance with these simple rules will help you, if you do not leave early from work home, so at least work more productively and meaningfully, mastering really interesting and complex things, and not fighting the consequences of your carelessness at every step.

So, by the 13th version (Excel 2007), its developers took a closer look at the typical actions of qualified Excel users and gave us the functionality of smart tables, for which many thanks to them. Because most of what I just listed, smart tables either do it automatically or make it very easy to set it up.

So, let's get acquainted with how smart tables are created and what useful properties they have.

1.Create a smart table

You can create a smart table in just a couple of mouse clicks. Stand on any cell of your table,

1. Method 1 - on the HOME ribbon, select Format as a table, choose the design you like (at the same time, 60 standard way formatting)
2. Method 2 - Press Ctrl-T
3. Method 3 - On the INSERT ribbon, select Table

2.Formatting

If before smartening up, your table had formatting (frames, letter and background colors, etc.), then you should probably reset this formatting so that it does not "conflict" with the smart table formatting. For this:

1. Select the entire table - the easiest way is to press Ctrl-A (Latin "A"!) 2 times!
2. On the HOME ribbon, click Cell Styles , then the Normal style

In this case, all formatting problems are immediately solved. However, you will have to restore the cell column formats: date format, time format, nuances of the number format (such as the number of characters after the dot), but this is not very difficult. In any case, it is up to you to decide whether to reset the formatting in this way, or in some other, less "destructive" way, but you need to know about it.

3. Table style preview

Through the menu Format as a table you can see how your table will look when applying any of the available standard styles. Very convenient and visual!

4.Other goodies and usefulness...

1. Alternating color of rows or columns! But did you know that earlier for this it was necessary to conjure 10 minutes with conditional formatting with a tambourine and rat bones!. "And now? Take a look around - what buildings have been built for you, what lawns have been broken, plumbing, TV, gas kitchen, greenhouses, flower beds ..."
2. Turn on the total row with one click!
3. Default filter
4. The first and last column can be highlighted in bold
5. When scrolling the table, the columns are visible WITHOUT pinning areas! Why are you more?!

5. Simplified selection of tables, columns, rows

6. Smart table has a name and can be changed

Although a smart table does appear in the Name Manager, it is not exactly the same as a named range. For example, you can't directly use a smart table column as the row source for the Data validation drop-down list. You have to create a named range that already refers to a smart table, then it works. And in this case, this named range, in case of adding new rows to the smart table, it expands automatically, which I really like to use and highly recommend to you.

7. Insert slices.

Excel 2010 introduced this useful feature like slices. These are visual filters that can be added to PivotTables as well as Smart Tables too. Let's see how it works:

8. Structured formulas.

Structured formulas do not operate on cell addresses and ranges, but on smart table columns, column ranges, and special areas of tables (such as headers, total rows, the entire data area of ​​the table). We look:

A warning for those who like to climb foreign sites: comrades, take into account the differences in the regional settings of Russia and Western countries (English-speaking, for sure). In regional settings there is a setting like " List separator". So in the West it's a comma, and we have a semicolon. Therefore, when they write formulas in Excel, the parameters are separated by commas, and when we write, then - semicolon. It's the same in translated books, everywhere.

Instruction

open Excel document with the data you want to summarize table. Click with the mouse pointer on one of the table cells, required condition- so that some value is written in this cell. The most convenient thing is to give the column an ​​appropriate name, for example, "name", "quantity", etc.

In the menu bar at the top of the window, find the "Data" button (for Excel 2000, XP, 2003) or the "Insert" tab (for Excel 2007) and click it. A list will open in which activate the "Pivot Table" button. The "Pivot Table Wizard" will open, it will help you configure all the necessary parameters.

Select the data source for the pivot table and the document type. The wizard window is divided into two parts. In the upper half, indicate where the information should be taken from, for example, from Excel file or a separate database. At the bottom, the “Pivot Table” item is checked by default, so you can not change anything. Click the "Next" button and proceed to the second step of the wizard.

Use the mouse to select the range of data to be processed. By default, the entire table is selected from the source Excel sheet, you will see this as a flashing dotted box on the screen. If necessary, you can select only part of the data. Click the "Next" button when you have completed the selection of information.

Click the "Done" button on the last table setup screen. You can choose where your pivot table will be located. The default is "New Sheet" and it's the best option. You can also select the "Existing sheet" item, but in this case there may be difficulties with circular references and display.

Set up a summary table. After you click Finish, you will see the table layout and control fields. Each of them is signed to make it easier to arrange the data and customize the final output of information.

Select the desired data source in the window on the left and drag it to the appropriate location on the layout. The table will immediately be populated with values ​​from the specified columns or rows. It all depends on the purpose of creating a pivot table - this is a very flexible tool. To get a particular report, you need to select the information of interest from the list in the field of the final table, uncheck unnecessary data sources and mark the necessary ones.

Useful advice

If the source tables with data are located on several sheets, it is best to transfer them to one and make a continuous range of values ​​for processing.

Try to work with a small amount of data first, then you can quickly understand the logic of building pivot tables.

Excel is one of the main Windows programs. Many users are aware of its existence, but are unaware of the possibilities that it contains. One of them is a table. Those who first learned about this may ask "How to create a table in Excel?"

Instruction

Open the program and in any cell write the name of the table. Under it, in each individual cell, write the name of the columns. In addition, each name must be under a certain letter, which is located above the working field.

Fill in the information you need in each column. Left-click on the resulting information and right-click on any of the cells. If the table name and column designations are not fully displayed, left-click on the cell border and drag to the right. In the same way, you can shorten the length of the cell, only you need to drag the cursor in the opposite direction.

There are several layouts that provide predefined structure to a PivotTable report, but you can't customize the layouts. If you need more flexibility when creating a PivotTable report layout, you can convert the cells to worksheet formulas, and then re-layout those cells to take advantage of the features available on the worksheet. You can convert cells in formulas that use cube functions or with the get.pivottable.data function. Converting cells to formulas greatly simplifies the process of creating, updating, and maintaining these PivotTable settings.

When you convert cells to formulas, those formulas access the same PivotTable data and can be refreshed to see up-to-date results. However, with the exception of possible report filters, you no longer have access to interactive PivotTable features such as filtering, sorting, or expanding and collapsing levels.

Note: When you convert an Online Analytical Processing (OLAP) PivotTable, you can continue to refresh the data to get up-to-date measure values, but you cannot refresh the actual items that appear in the report.

Learn about common scenarios for converting PivotTables to worksheet formulas

The following are typical examples of what you can do after converting PivotTable cells to worksheet formulas to customize the layout of the converted cells.

Rearranging and deleting cells

Let's say you have a periodic report that you want to generate for staff every month. It only needs a subset of reporting data and a custom layout. Can be simply moved and positioned properly cells in the layout, delete the cells that you don't want to be included in the monthly staff report, and then format the cells and sheet as needed.

Insert rows or columns

Let's say you want to display sales data for the previous two years broken down by region and product group, and you want to insert an extended comment on additional rows. Just paste a line and enter text. In addition, you need to add a column showing sales by region and product group, which is not in the original PivotTable. Just insert a column, add a formula to get the results you want, and click Fill Column Down to get the results for each row.

Using Multiple Data Sources

Let's say you want to compare the results in the real and test database to make sure that the test database produces the expected results. You can easily copy the cell formulas and then change the connection argument to point to a test database to compare the two results.

Using cell references to change user input

Suppose you want to change the entire report based on user input. You can change the arguments in the cube formulas to refer to worksheet cells, and then enter different values ​​into the cells and get the appropriate results.

Creating a non-uniform row or column layout (asymmetric reporting)

Let's say you want to create a report that contains a 2008 column named Actual Sales and a 2009 column named Projected Sales, but you don't want any other columns. You can create a report that contains only those columns, as opposed to a PivotTable, which would require symmetrical reporting.

Create your own cube and MDX formulas

Let's say you want to create a report that displays the sales of a particular product in July by three separate salespeople. If you know how to use MDX and OLAP queries, you can enter cube formulas yourself. Although these formulas can be quite complex, you can make them easier to create and improve their accuracy by using Formula AutoComplete. additional information see Using the autocomplete feature .

Converting a cell to formulas using a cube function

Note: This procedure is the only way to convert an Online Analytical Processing (OLAP) PivotTable.

Converting a cell to formulas using the GET.PIVOTOMATIC.DATA function

You can use the get.pivottable.data function in a formula to convert pivot table cells to worksheet formulas when you need to work with non-OLAP data sources, if you prefer not to immediately update the PivotTable to the new 2007 version format, or if you need to avoid errors cube functions.

Make sure the command is available Create GetPivotData in Group pivot table tab Parameters.

Note: Team Create GetPivotData controls the parameter Use GetPivotData functions for PivotTable links in category Formulas section Working with formulas in the dialog box Excel Options.

In the PivotTable, make sure that the cell you want to use in each formula is displayed.

In a worksheet cell outside of the PivotTable, enter the formula that is required up to the point that you want to include data from the report.

Click a cell in the PivotTable that you want to use in a formula in the PivotTable. The get.pivot.table.data function is added to a formula that retrieves data from the pivot table. This function will still receive the correct data if the report layout changes or the data is updated.

Complete the formula and press Enter.

Note: If any of the cells referenced by the GET.DATA.PIVOTOMATIC.TABLE formula are removed from the report, the formula returns the #REF! error.