This blog was authored by Y. Karp. Visit www.ykarp.com for some light-hearted blogs from the same author.
Most people, even true beginners, know the very basics of how to type in data into an Excel spreadsheet. After all, an Excel spreadsheet is really just an electronic version of the old-fashioned bookkeeping grid.
While the horizontal and vertical lines may appear familiar, Excel’s automated features give that grid a whole new meaning – a power beyond simply adding, subtracting and multiplying numbers. The Pivot Table is one such example and is today’s topic of discussion.
The Pivot Table is probably one of the easiest ways to automate the presentation of ranges of numbers in an Excel spreadsheet. Put simply, the Pivot Table is an automated summary of whatever information appears elsewhere in the Excel Workbook. For example, let’s say that you have used Excel* to track sales figures for any number of products over the last financial year. The massive amount of data might be impressive, but it is probably very difficult to get to the bottom-line figures of how much was sold per product per month. A Pivot Table would be a perfect way to automatically boil it all down to a single bottom-line figure.
(*Just so you know, there are also ways to extract data from your existing MS Access databases into Excel or other database platforms like SQL, so that you can use Excel’s number-crunching expertise (such as Pivot Tables) to figure out the statistics you need to know).
By default, Pivot Tables are presented in a grid, with row and/or column headings around the edges and the data in the middle. The default setting is also to display row and column totals, although this, like most things, can be changed. Most of the way the Pivot Table looks is customizable, although there are certain limitations to what you can actually do.
The type of data that the Pivot Table presents all depends on what information you need to squeeze out of your spreadsheet. For example, you could get the Pivot Table to show you a count of items (e.g.: how many of a product was sold); you could get the Pivot Table to show you a sum of items (e.g.: the total value of the products sold in a particular month); or, say, the average sales per month. Of course, Excel gives you many more options of what type of data you can view and many ways in which to view it.
As with most Excel features, the good-old Pivot Table has its plusses and minuses (pun intended). Pivot Tables give you an easy way to view the data, but because it is an automated feature, there are limitations on what you can do with it. For example, a Pivot Table likes its space. It won’t shift over if the Pivot Table next to it wants to expand, resulting in an error message. Also, when the raw data changes, the Pivot Table doesn’t update itself. You have to refresh it manually – unless, of course, you are running a macro or a script that will update it for you on the fly. On the whole, though, I think that the Pivot Table is a great way to really see what your business is up to – drill down to the information you want to find the answers you need to keep your business on track.
If you would like to know what the real status of your business is and what your data is telling you, contact Easy Data Solutions for customized Excel spreadsheets and business information models. Or, if you would like to learn more about using Excel and its powerful features, sign up for a tailor-made coaching course for value added learning.
Website: www.easydatasolutions.co.il Email in**@******************co.il
After looking at a handful of the blog posts on your
blog, I honestly like your way of blogging. I book marked it to my bookmark webpage list
and will be checking back in the near future.
Please visit my website too and tell me your opinion.