Excel PivotTables


Accumulate the information is not enough: it is equally important to be able to use in the best way . Excel is a program quite complicated, with many features to manage large amounts of data . In this Step by step we will learn to use a tool poorly understood , but the big potential: the pivot tables.

A personal computer can be used for work , for fun , to keep order in the family budget and for many other purposes. In almost all cases, however, sooner or later will warn the need to create some kind of archive to manage our data , in other words a database. A database is used to collect ( and return at a later time ) any type of information . Excel , as well as do complex calculations and create compelling graphic designs , it can handle a small database . Using PivotTables can reorganize their information in ways that from time to time we consider most useful , hiding information that does not concern us at the moment . With a little ‘ practice all this happens with a few simple mouse clicks . In this example we will use the database of a ‘ business selling computer products, but as usual we will describe the operations that can very well be used for other purposes , such as a business plan , or a shopping list . You will be surprised how easy it is to truly become masters of their own data , grouping them to your liking.

Technological products are always in fashion : Updates and new models never set . Let’s pretend to be the secretary of a small company responsible for the ‘ sales office . To PivotTablekeep track of sales of our items we use a simple database built with Excel, as shown in the picture . A PivotTable in this case , it is useful to know which item is immediately available , in what quantity , if you are selling more printers or scanners or even quarter in which you made the biggest sale . To make things easier , we have created this example with only six columns of a spreadsheet. Once you have taken a little ‘confidence , of course, you can add more columns with other information , such as net income , number of orders per customer and so on.
Click anywhere on your list . From the Data menu, choose ” PivotTable Report .” It will start the PivotTable Wizard , which will have already selected the ” Microsoft Excel list or database ” . This is exactly what you want , so go ahead click Next.

The PivotTable Wizard wants to know where you will find the data to be used. If you do not have nothing against

Excel PivotTables

it , will automatically select all rows and all columns of your list , drawing a dotted line around the data . Again, the wizard guessed our intentions , so we still click Next. Our Month – Vendor , Category , Unit , Turnover and Paid – appear as buttons on the right side of the dialog box . Let us click on it and – while holding down the left mouse button – drag the diagram to the left of the buttons. In particular , drag the rectangle Seller in the ROW , COLUMN category in the rectangle and Unity in DATA area , as you will see latter will be changed automatically.
Click Next and you will be asked if you want to create the PivotTable in a new worksheet or existing worksheet. Accept what is proposed ( create a new worksheet ) , and click Finish. Do not panic , your original data

have not disappeared , they are always there for you : just click on Sheet 1 in the bottom of the window .
For now , however, we will work with the new sheet that is created .

Let us examine our new PivotTable.

What happened? As you can see , we have summarized some information. We can immediately see various types of totals : we (column D ) , but also the sub total of the individual article

. It is not all : we can still gather new information from our database

If by chance the PivotTable toolbar is not already open by itself , go to the View menu, drop down to Toolbars and choose PivotTable from the list . In this way you have at hand the various commands of this tool. Click the ” PivotTable Wizard ” , can be found at the top of the toolbar on the right of the ” Pivot Table ” .

Read MoreCreate an Excel macro to calculate the Salary

Now all you have to do is simply drag the Date of the button above the rectangle RIGA, just below the Vendors button . Leave everything else as is. Click Finish . Now is listed for each sale and the grand total .

And now you want to see in detail the behavior of each sale for each day ?

This is also very simple , thanks to the pivot tables. First of all, back again all’autocomposizione pivot table and make a small change. Move the button type bike in the ROW and COLUMN button Date of the area . Click Finish . In the picture you will find the result of this operation: all the days of training are listed in row 2 , column B is indicated whether it is racing bike or MTB .

Come back again all’autocomposizione PivotTable. The more curious among you may have noticed that there is an area that we have not yet used : this is the PAGE option . Move over that button type category. Let all the other buttons where they are now . Click Finish . The cell A1 of your spreadsheet will now label as ” Category Type ” and in cell B1 there will be a drop-down list . Click on the small arrow pointing down , in cell B1 to display the list . From here you can choose whether to display separately , only those for certain item .

There are still more possibilities. Click on any of the cells with the unit in row 4 . On the PivotTable toolbar, click the Group button (the button with the arrow pointing to the right ) . This will open the Group By dialog box . The field ” From ” and ” To: ” contain the dates of the first and last day recorded in the database. This is just what we want , then we leave everything as it is . In the ” Group By ” choose Quarters , and then click OK . Now you can check for each quarter of the year.

 

We saw the possibilities offered by the most important pivot tables. It remains to do a little work of aesthetic improvement taking care of the formatting. However, it is best not to overdo it with the formatting because Excel has many difficulties in preserving the settings when you change the styles of the pivot tables. On the toolbar , click on the ” Pivot Table ” (the first button on the top left). Go to Select. From here you can choose ” Entire Table ” or ” Enable Selection ” . You will use the first option for things like the background color , while the second will use for particular areas, such as formatting of miles driven . To change the background color , select ” Entire Table .” From the Format menu, choose Cells. Click the Patterns tab and choose a color from among those submitted . Click OK .

Return to the PivotTable button and select Options. The dialog box that appears has a lot of possibilities to set the formatting. You can accept the choices already preset directly from Excel , and then click OK. Or, if you’re curious , you can try playing with the different settings and see for yourself what happens. You can decide whether to display the totals for rows or columns, how to format the table , whether and how to show empty cells , or error values ​​and many other things .

If you click with the left mouse button on the question mark in the upper right corner of the dialog box, and then click an option, a small window will appear to help with the explanations for that option Read More: Data Analysis with Excel