Excel Pivot Tables

Before we get into creating Pivot Reports, we are going to take a quick look at Excel’s Pivot Table functionality. I want us to do this because much of what Pivot Reports does, is very similar to, and based on, Pivot Tables. So we’re going to start here, in NetSuite, at this “Demo Pivot Report SO Search”, results page. This search provides a list of sales order line items, and some data about each of those items. We’ll actually create this search a few videos from now, but right now we just need to work with its results. Also, if you watch the rest of this video, and find it confusing because you are not familiar with the data. Go ahead and watch the video where we create the search, and come back and watch this a second time. This should help clear up any confusion about the data here. To get this into Excel, we have two options. We can export this as an Excel file, or we can export it as a CSV. I’m going to choose CSV, because it’s a little cleaner and will work better for our needs. This downloads and we can open it. We need to resave this as an Excel file, so the Pivot Table will work. I’ll save this under the desktop, and choose Excel Workbook up here. I’ll start by making these columns a little wider, so we can see the data. And actually, Columns D and F can be made a little narrower. For the pivot table to work, we need to make sure our data is contiguous. That is, there are no empty lines, and Excel recognizes this as one set of data. If I click anywhere here, I can press Ctrl + A, and the range is selected. Let’s scroll to the bottom to make sure that we have everything, and in this case we do. Now we can create our pivot table. We can also click anywhere else in the data. We don’t have to have the range selected. Excel will figure it out. We’ll click Insert, then click Pivot Table. I’m going to expand this to make sure that our full data range is selected, and it is. We can click OK and the new pivot table is created in a new sheet. There are a lot of different options for analysis. Based on the source data, we could answer many different questions with this data set. But maybe we want to figure out what sales reps have sold the highest number of specific products. Let’s drag our Sales Rep field down to Rows. Now our rows show up with names in them. We’ll put the Quantity field under Values, since that’s what we want to analyze. We immediately see that some of our reps have sold a lot of products and some have sold almost none. Whether this view is valuable or not, is going to depend on your business, and what you sell. If you only have one product, or all your products are in one price or product range, this might be very helpful. But if you sell a range of goods, this might not be as useful. Of course, what we are doing here is not much different than what we could get from basic summary operations. Let’s add items here under Columns. Now we have the number of items broken out by rep. This doesn’t really look that good. We have a lot more products than we have sales reps, so this view is very wide. We can scroll all the way to the right and see just how wide it is. Let’s make a quick change that will make this easier to digest. We’ll move Item down to Rows. We have an interim view that might be useful. We’ll also move Sales Reps to Columns. Now we have basically switched the Rows and Columns, or Pivoted the table. All of these steps are probably familiar to any analysts out there. The data in this view is the same as it was in the wider view, but it is probably a little easier to analyze now. If we scroll to the right, we can see this is much more narrow. I’ll close the Pivot Table Fields window, so we can see more of this. Also, let me resize this column and shrink this a little. Now the width of this fits all on one page, and we can scroll down to analyze the results. This is going to be easier since we usually read from top to bottom. With this set of data we can answer questions such as who sold the most of any product we choose. We could add more information from our original data set, and we could use this to answer many other questions we might have, with relative ease and speed. We have looked a bit at how to use pivot tables in Excel, but in truth we have only scratched the surface. This is true both of what can be done in Excel, and what can be done in NetSuite. The point of this video wasn’t to teach you to use pivot tables. It was more, so you have an idea of what they are, since people that don’t work in Excel often may not be familiar with them. One of the things you should realize is that we have looked at several different analytical views, in a very short period of time. More importantly we have been able to change the views quickly and easily. We did this in Excel, but wouldn’t it be great if we could do this all in NetSuite? Well it turns out we can. And no surprise, it’s with Pivot Reports. That’s what we’ll take a look at in the next few videos.