Learn to master pivot tables, one of the most powerful analysis tools of Excel. The analysis of the data in a scene from “The Lion King”, a hyena is so intimidated by the imposing of the king to hear a thrill just to hear pronounce the name. Here, some features such as the “pivot tables” at times, they do just that effect for those who work with Excel. Believe us, it is not worth it to get caught by those awes. Pivot tables (and PivotCharts, which we discuss in the next issue) are powerful tools, but they are not at all difficult to use. It is the means that allow to analyze, and in a second time to present, also huge amounts of information. All in the space of a few minutes at most, using the analytical skills of Excel. The first (and most difficult) step to make good use of pivot tables is to understand what they serve.
The term ” data analysis ” (used for example in driving Microsoft Excel ), in fact , it means everything and nothing , it does not explain in what way you analyze information. A PivotTable is primarily used to collect data , to calculate totals and subtotals and highlight , for each grouping , the only information that you wish to highlight . All this in an automatic way and according to the criteria that can come in handy. To tell the truth, even this definition makes it 100 % with the idea of what are the pivot tables, but it is a first step. To begin to analyze the data, you must have the data to analyze : a truism . For example , as in our case , of the simple sales reports . Note how the data are reported in columns and each column has as its first header line , ie where there is no data , but there is a brief description of its content . The table shows a series of purchases , and are kept for the name of each customer , the product purchased, the quarter of the year in which you made the purchase and the amount of the invoice.
Creare tabelle pivot in Excel
Create a Pivot Table, or at least its basic structure, is a fast intervention : just click on a cell within the table that contains the data and press the ” pivot table ” in the Insert ribbon . No need to select the entire table : Excel thinks to identify and select all data. Excel presents a dialog box that indicates the group of cells which has been selected as the source for the pivot table. Here you can choose whether to create the table in a new worksheet or in a specific location on a worksheet already exists. In our case we do not change the default values. Here is the screen ( a bit ‘ cryptic ) that Excel welcomes you to the world of pivot tables. The main area is initially empty . On the right sidebar are shown in a box the names of the columns (called fields that Excel) with subsequent check boxes .
To give shape to the pivot table , you simply select one or two fields in the right pane . Just check the box before the customer , then ” Product” , and see what happens automatically in the work zone creates a table that shows which products were purchased by each cliente.Se uncheck the two boxes and re- select in reverse order ( before “Product” , then ” Customer”) , the table is created in a different way , and shows that customers bought each product. Customer and product fields are entered automatically by Excel as ” Row Labels ” (the box on the right to check) . This means that customers and the products are ordered each on a different line . If you now try to add the field to the sales table , Excel puts on a separate column : why is a numeric value , Excel will automatically create totals and subtotals at each row.
What does this mean ? Simple: in the figure, next to the name of each customer , it shows the total of his bills , and then, just below , next to the name of each product, as it is written to the same customer has spent on each type of product. If you now try and you also add the column district , Excel goes a bit ‘ of confusion in the field as well as district contains the numbers , Excel sums up exactly as it did for the Sales field .
But these are not values to be added (indicating , they said, the quarter of the year when every purchase is made ) , and logically should not be placed in a separate column but where there are also customer names and the names of the products . To fix this error is not difficult: just click on the “Sum of the Quarter “, which appears in the Values pane (which Excel automatically positions all numeric fields ) and select “Move to row labels. ” Take a look at what has happened . The table is modified, “stretched” . Now we are in fact not only the total and partial split by customer and by product, every product purchased from various customers , it became an additional level of detail , which indicates for each customer / product sales of each trimestre.Per hide a few lines, in the case where the table would become hard to read, just click the boxes with the “-” sign next to each name. Let’s take a quick look at the functions of filtering data .
The table built so far contain the data for the entire table. But it can happen , for example, you want to print the situation for a single customer. In this case , simply click on the tab of the customer ” Row Labels ” and select “Move to filter the report. ” In this way, in the first row of the spreadsheet , you will see a drop down menu where you can select a single customer (in the picture you can see all the data relating only to Mickey Mouse , which is logical, not bought mousetraps . .. ) .
Want to change the look of the table ? The design of the tape, which only appears when you are positioned on the pivot table , lets you act on both the structure of the table is on its appearance . You can change the layout of the table (there are four dedicated buttons , each of which leads to various options) , you can decide to use or not use the headers of rows and columns and highlight the entire table with alternating colors. Finally, there are a number of color themes , and with a click of the mouse will allow you to make your table more readable ( and , why not, give it a more attractive ) .
Now that you have started to move inside the pivot tables, and you probably have begun to realize its full potential, you should know that … this is not all! There are details of this powerful tool that goes beyond our step-by-step, and you can experiment without fear, now that you have the basics you need to understand a little ‘how to move better. Most of these “advanced controls” is located in the Ribbon Options, which is available only while you are positioned on the pivot table. validation criteria Excel spreadsheet