Excel Lessons Online

Excel is a fantastic and powerful tool to analyze data and report findings, trends and different data relationships. However, sometimes you have to analyze large amounts of data and produce reports based on this data and present it to your boss and colleagues. But, thankfully Excel has shortcuts within itself – these shortcuts are the Pivot Tables and Reports. You may be intimated by Pivot tables and reports because perhaps you think they are too hard, or you thought they were great when you learned about them in training class or when someone showed them to you, but you quickly forgot about them because you did not use them on a daily basis. Well, this tutorial will show you how to create a pivot report in no time.

The key to remember is that pivot table can analyze numeric relationships in a snap, and produce reports and charts with just one click without going through all steps to create a chart or report from scratch. It is really a shortcut within Excel.

1. Open the Excel application and go to File>New to open a brand new spreadsheet.
In this tutorial, we’ll use fictitious data from a beverage distribution company in Florida named Breeze Company. The data is from the month of 12/2006, and it shows all the sales personnel for the South Florida area and the number of accounts that they opened in four major cities (Miami, West Miami, Hialeah and Coral Gables). So, please enter all the data into the spreadsheet and format accordingly. This is really the most time consuming step because as you will see the next steps are quite short.

2. Go to Data>Pivot Table and PivotChart Report and select this option.

3. Click on the first option, “Microsoft Office Excel list or database,” below the first question (to select the source of data, in this case, the source is an Excel spreadsheet) and then click the first option “PivotTable,” below the second question and click Next.

4. Now select the data area, hit Enter and click Next.

5. Click on the first option to create the table in a new spreadsheet and click Finish.

6. In this step, you actually build the Pivot Table by dragging items from the Pivot Table Field List pop-up window (or you can select the item and then click “Add To” and you select the area where you want to place the items.) But, the quickest way is to select, drag and drop. You can select and drop any item(s) to the “Drop Row Fields Here,” “Drop Column Fields Here,” and “Drop Data Items Here.” The combinations are endless, and you can see in a snap how the different items would relate to one another. In this tutorial, we will keep it simple and we’ll drop one specific item to all three designated areas, so you can see one specific relationship among this data.

7. Click on City and drag it to the “Drop Column Fields Here” section.

8. Click on Salesperson and drag it to the “Drop Row Fields Here” section.

9. Click on Accounts Opened and drag it to the “Drop Data Items Here” section. Now, you have created a table showing all the Salespersons and the number of accounts each opened within the four major cities. Also, the table shows the total number of accounts opened in each of the four cities. In the next two steps, you’ll create a report and chart by just clicking on the available option.

10. Go to the Pivot Table format menu and select Format Report, and a series of report options will appear.

11. For this example, select the fourth report and click OK.

12. Now a brand new professional looking report has been created with just one click as seen below.

13. Now, if you want to see how this information will look on a chart, all you have to do is click on the PivotTable drop down menu, and select the PivotChart option.

14. After selecting the PivotChart option, a chart will be created instantly like the one below. You will see the four major cities aligned with all the salespersons at the bottom and you’ll see the number of accounts totals in bar chart format.

Well done! You’ve created a pivot table, report and chart with some simple and quick steps. Remember, the more you practice the easier it will become.

*If you found this tutorial to be helpful , please consider donating.

17 Comments

Comments RSS TrackBack Identifier URI

Leave a comment