Pivot Table v Formulas

One of Excel’s major strengths is analysing data, that is why most people use it. They have data which they have been capturing, and now they need Excel to analyse that data and give them relevant information and statistics that they can use. Excel does this and does it brilliantly. Now like most good software, there is more than one way of doing this. There are two camps when it comes to analysing data – pivot tables and formulas. Each have their pros and cons, so let’s take a look at them.

Let’s start with pivot tables. There is one major reason why people like pivot tables. They are easy to create. Yes, they are often included in the ‘advanced’ Excel courses, but really they are easy. Capture all of your data in a table, create a pivot table and select your data. Job done. Then all that there is to do is to select what is data and what is information by which you wish to sort. You can play around with check boxes in order to get the correct layout for your pivot table. You can even create some sliders in order to have clear options to select to show the desired data. These are easy to create and easy to use, the problem is what comes after that. Nothing. That is it really, that is what it does. You can extract data from the pivot table using the GETPIVOTDATA function, but it is a rather complicated process and if you can do this, you could just as easily use formulas to extract the data in the first place. The appearance of pivot tables can be altered, but not to the extent that normal cells can. This means that you can’t necessarily get the exact layout that you wish.

Using formulas to extract data is more complicated. Having said this, if you do wish to use the data extracted in other formulas, you will need to extract that data from the pivot table anyway. If you can do that, chances are that using the IF(S), SUMIF(S), COUNTIF(S), AVERAGEIF(S), INDEX and MATCH functions, and Array formulas won’t be an issue for you. This means that you can get your desired results without using a pivot table. The pros for this method is that you can get it to look like you want, and it also updates automatically when you update your data (pivot tables need to be refreshed). This requires a lot more effort to actually set up, but once it is done, it is easy to use the produced data elsewhere, and its appearance can be adjusted to your requirements.

So where does this leave us? Pivot tables or formulas? There is a reason why Excel has both. I don’t believe that one is better than the other, I believe that they each have their purposes. Yes, you can use formulas to accomplish the same results as a pivot table, but it is a lot of hard work. Excel has created pivot tables so that people can access the data that they require without having to know all of the formulas. I personally don’t use pivot tables that often. I create custom spreadsheets for clients, so they have already told me what data they require. I create the spreadsheets using formulas, to extract the data that they require (even have it subject to their selection, like a pivot table). I believe this to be the better option to supply to my clients, as I believe the finished product is neater and easier to use. If you are doing this yourself, and you don’t know all of the formulas and functions required to do this, pivot tables will help you out. There are plenty of videos and instructions on how to use them, and after 5 minutes you can have your very own.

So there you have it, they both have their place and pros and cons. It is up to you to decide what is more important to you. Which one do you prefer to use? Have your say below.

Happy Excel-ing!

Richard Sumner