Of total sales. We can get this done faster than it takes to make a cup of coffee. Let's get started. (upbeat music).
In this example, we have sales and quantity data by product, customer and company. Now, the first thing you need to do before you insert a Pivot Table.
Is to make sure that your data is organized in a proper format. This means it should be in a tabular data format like you see here. Each column has a header..
You shouldn't have empty columns in the middle and you also shouldn't have empty rows in the middle of your data set. And I do think you shouldn't have all total values.
But check this out for the Range is hard coding it to SAS3 SKS108. This is until where I have data. If I add more lines to this,.
They will not be considered in my end report, I will have to update my range and I'd rather avoid this step. So what I need to do before I insert a Pivot Table.
Is to turn my data set into an official Excel table. This has the added advantage that whenever I get new data added, my Pivot Tables will automatically reflect that the moment I press Refresh,..
And all I have to do is click on this button here that's called Table or use the shortcut key Ctrl+T. Excel figures out that this table has headers,.
You can go with OK. And if there's automatic table layer that Excel gives you bothers you like it bothers me, you can go and remove it, just select None..
Posts Related:
- Infographic PowerPoint Slide Design Tutorial
- How to Clean Messy Data using Power BI Query Editor Example: Clean Names with Comma, Dash, Space
- Camtasia 2022 NEW Features Reveal
But what I also prefer to do
Is to give my table a name so that I can recognize it later on. You can do it from this Table Name box here. Now that we have a table,.We can automatically summarize with a Pivot Table, or go back to Insert and click on PivotTable. Check what happens now, we get the name of our table. As our table expands with new data.
They will automatically be included in our Pivot Table. But before I do this, I want to show you even an easier way of inserting a Pivot Table, and that's to click on Recommended PivotTables..
These are actually quite smart, they might just give you exactly what you're looking for, or they might help you get a faster start. So for example, here I have the sum of sales USD by customer name,.
One of the reports I want to create has sales USD in it. So I'm going to go with that, just select it and click on OK, you get your Pivot Table inserted on a new sheet. This is the result of my table,.
It does all the aggregations for me, I didn't have to write a single formula to do this. Now on the side here we get the Pivot Table fields. But you can see I have them organized where the fields are on one side,.
And the boxes that define the layout of my Pivot Table on the other side. That's an option you can select here. The default is the first one, Fields Section that Stacked..
Now, if you want to have more space for your fields here, you can go and select Fields Section and Area Section, side by side. Now, what you see here are all the headers of your dataset. That's why each column needs to have a header..
What Excel went ahead and did, when you inserted your Pivot Table, is that it put the sales USD in the value section, and customer name in the row section. Now, if for some reason,.
You don't want to see the sum of sales USD,
But instead you'd rather see something else, for example, the average, you can click on this drop down, go to Value Field Settings.And change your selection from here. So I go with Average and OK. Now, you can also change that from here, right mouse click is your best friend in a Pivot Table. If I right mouse click,.
I can also select Value Field Settings from here, I'm going to put this to Sum and go with OK. Now, another thing you can get to with a right mouse click is the Number Format..
So let's say I want to add 1000 separator to my numbers here, I can select Number Format. So don't select Format Cells because that just formats the underlying cell,.
Number Formats stays with your Pivot Table as it expands. So I'm going to go with Number, use 1000 Separator and zero decimal places. Now, you can adjust and update this Pivot Table as you see fit..
So for example, if I wanted to look at the different regions, I could decide to add the region in the filter. And then when I click on this drop down, I can select a specific region..
Or I can activate multi select, which in this case doesn't really make sense, because I just have two items, and you can also leave it on All. Now, if you decide that you don't want to have Region.
In the filter, you can bring it to the column section. And now we have customer name by region, here. We also automatically get the grand totals on this side. Now, if you have another level,.
So let's say we're going to bring product description to the rows here, we also get subtotals. Now, here's where you might want to update the design of your Pivot Table..
So for example, under Grand Totals, you might decide to deactivate it for either to rows or the columns or for everything if you don't want to see it at all. So if I go with Off,.
It completely removes my grand totals. If I just activate it for Columns Only, I get the grand total on the bottom here. You might also want to do the same thing for subtotals. If I select Do Not Show Subtotals,.
It's going to remove them from here. Now, as for the layout of your report, the layout that I personally prefer is to show in a tabular form. This gives me column headers for each of the fields here..
Now, another thing you might want to do is to fill in the gaps here. Under report layout, you could select Repeat All Item Labels, and that repeats everything and fills in the gaps..
Now I'm just going to remove that. And now let's take a look at the button here. So you have the ability to collapse fields or expand fields. If you want to take away this ability,.
You can go to PivotTable Analyze, and under Show click on this toggle here, and it takes away the Buttons, if you click again, it brings them back..
Now, the field list, this one here, if for some reason you close that, and you want to get it back, you can do that by going back to Show and selecting the fields list from here..
Now, I'm just going to put everything back to the way I had it. So I don't want region. Whenever you don't want something, you can just kick it out, just drag and drop it here somewhere in the fields list..
I also don't want the product description. So I just want customer name by sales USD. One other thing you might want to do is to update the header here. So I don't want anyone to see sum of sales USD,.
Let's say I just want them to see sales USD. When I press Enter Excel doesn't like it if a header here is identical to a field name here. So if you want it to be identical,.
What you need to do is either add a space after the name or before the name, or you can completely change the name. So we could just call this Sales instead of Sales USD..
Also for Grand Total, I can just call this Total. Okay, so let's also update the design of this Pivot Table by selecting a different style. So you have a lot of options here.
And you even have the ability to create your own Pivot Table style, if you'd like. Now, one thing I'd like to do here is to sort this,.
So just right mouse click, Sort, Largest to Smallest. Now, notice that the Pivot Table columns collapse, there is an option for this, you can actually remove Autofit if you don't want this to happen..
So right mouse click, go to Pivot Table options, on the bottom here you have Autofit column with an update, let's take away that checkmark and go with OK..
Now, one thing I originally wanted was to get the sales percentage by customer as compared with the total. And let's say I also want to see the complete sales values here..
Well, what I can do is to bring in the Sales USD a second time into my Values field. But now check this out, I'm going to right mouse click,.
Show Values As and select % of Grand Total. But check out all the other options that you have. If you get a chance, go ahead and try these.
Because they can come in handy when you're creating reports. Now, I'm going to call this Sales % Okay, so our first report is done. Before we test whether this updates properly if we add new data to our table,.
Let's go ahead and create our second Pivot Table. The way I personally prefer to do that is to copy an existing Pivot Table, go to the side and paste it in and then adjust what I need..
