For many years I worked in accounting without knowing the existence of Microsoft Excel Pivot Tables and how they can create huge efficiencies when working with large amounts of data. The best part about Excel Pivot Tables is that you don’t have to be working with just financial numbers, but it works with any form of data including numbers, text, dates, etc. The possibilities are endless!
Pivot tables extend the capabilities of our individual database functions. It allows the user to take any information from a database and puts it in an interactive worksheet table used to quickly summarize large amounts of data into a form that the user can control the format of the final product. You can also take the Pivot Tables you have created and create a Pivot Chart for a presentation or report. All this can be accomplished in minutes to help summarize your data into a managable form.
Here are five quick easy steps to getting you started:
1) Download your data to an excel worksheet (make sure to include column headings in row “1” that describes each columns data).
2) Select all the data of the worksheet that you want included in the Pivot Table including the column headings in row “1”.
3) Once you have all the data selected, go to “Insert” and select “Pivot Table”.
4) A new tab will open with your Pivot Table. Now all you have to do is drag your fields (column headings you identified in step #1) from the “Pivot Table Field List” task pane that you want into your pivot table in the order and format you want. Once you get started you will see how easy it is! There will be four drop zones for your data: Report Filter, Column Labels, Row Labels and Values.
5) Manipulate the Pivot Table until you have the desired format needed.
Pivot Tables are a necessity in creating and finding efficiencies when dealing with large amounts of data. These tables can be used in all areas of your business, from being able to organize employee lists by location, position, pay rates, to being able to take large amounts of data from your manufacturing or distribution facilities and finding trends.
I have even used Pivot Tables to show 3rd party vendors how they can provide more accurate and timely information to our business and it has greatly reduced the time it takes to receive the necessary information from these providers which in turn creates efficiencies for us because we have the information earlier and more accurately than in the past. Pivot tables are really a great tool to use and the number of possibilities is only limited by your creativity!
If you have any questions, comments, or suggestions of topics that you would like me to discuss in the future, please use the comment section below! 🙂