We now have a pivot table with the same results. We'll need product as a row label, and revenue as a value.Īnd then apply a number format to revenue.Īnd, that's it. Since I already named the data, I can just use that name here. Now let's build the same report using a pivot table. If you're not as comfortable with formulas, it's going to be quite a bit harder, with plenty of mistakes and learning as you go.
#HOW TO USE PIVOT TABLES IN EXCEL 2013 HOW TO#
So the manual approach isn't too terrible, but of course I already knew how to do it, and I used a lot of tips and shortcuts to speed things up. Now we have the basic report your manager wants.a one page sales report with a breakdown by product. Now I need to copy the formula and add a total, and I'll format the numbers as currency. I could actually use SUMIF, but because I'll be adding more criteria in just a minute, it'll be easier if we start with SUMIFs.īecause I already named the columns, I can easily type the names as I go, without going back to select the data. And it's time to write out the first SUMIFS formula. So, we have the bones of the report in place now, but no data. Now I'll add in some labels and formatting.
#HOW TO USE PIVOT TABLES IN EXCEL 2013 FULL#
While I'm here, I'm also going to name the full set of data so I can easily refer to it when I create a pivot table later.įirst, I'm going to grab a copy of all the products, and then remove duplicates, and sort. Now we have a name for each column in the data.
SUMIFS is easy to use, but it can create some pretty ugly formulas, so to make things easier to read, and avoid a lot of absolute referencing, I'm going to go ahead and name the columns first.
The easiest way to do this is to use the SUMIFS function. So, assuming that you don't know how to use pivot tables, but you are pretty good with Excel formulas, let's build out a manual report first. Now, imagine that your manager sends you this data and asks for a one-page summary that shows a breakdown of sales by product. City, State, and Region, Product and Product Category, and finally, Quantity, Revenue, Cost of goods sold, and Profit. This data represents chocolate sales to several customers in different locations, at wholesale level. So, here we are in Excel, and you can see we've got a set of raw data. So, I hope by the end of the video you'll agree with me. So I think that if you use Excel with a lot of raw data, and you need to summarize that data in different ways, and if you need to create good-looking reports, then pivot tables are definitely worth your time. I love pivot tables.Įver since I got my first pivot table to refresh correctly back in the 1990s, I realized like wow these are really useful tools. So, I think one of the best ways to understand how pivot tables can help is to look at some examples of building some reports, side by side, where one set of reports we're building manually using normal Excel functions and formulas, and the other set, we're building with pivot tables.Īnd that's exactly what we're going to do in today's video.Īnd, I want to be really upfront with you here. So, today we're going to tackle the question Why Pivot Tables?Īnd this question comes up a lot when people first run into Pivot Tables, because they're wondering.why should they care? And you're trying to explain that pivot tables are really fast, that you can summarize data interactively in different ways.blah, blah blahĪnd you can kind of see their eyes just glaze over because, until you've actually used a pivot table yourself, it's really hard to understand why they would be useful to you.
0 kommentar(er)
