- #Understanding pivot tables in excel 2013 how to#
- #Understanding pivot tables in excel 2013 update#
- #Understanding pivot tables in excel 2013 manual#
- #Understanding pivot tables in excel 2013 full#
But can she also see a breakdown by year instead of region? So you hand that back to your manager and an hour later she tells you it's great.
#Understanding pivot tables in excel 2013 manual#
I'm also going to change the pivot table layout to "outline", and line it up again with the manual report.
#Understanding pivot tables in excel 2013 update#
All I need to do is add region as a column label and we're done.Īgain, there's no need to update any formulas. Now I can copy the formulas across the table and add totals.Īgain, it's not too terrible, but lots of clicks and plenty of formulas.Ĭhanging the pivot table report is a bit easier. I'll need to lock the column reference for product.because I'll be copying that in just a moment.then add a new range and criteria for region with the row locked. Next, I need to extend the SUMIFS function to handle both product and region.
This time, I need to transpose the regions across the top. She wants region across the top.įor the manual report, I'll start by grabbing a copy of all the regions and I'll follow the same process as we did for products before. So, let's look at how both of these reports handle a few change requests.įirst, let's say your manager comes back and asks for a breakdown by product and region.
Of course, no manager will ever be satisfied with their original instructions. And notice that I didn't use any formulas at all. 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.
#Understanding 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.
#Understanding 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)
