Pivot Tables in Excel -

Time 2020-07-19 07:04:42

Web Name: Pivot Tables in Excel -

WebSite: http://pivottablesinexcel.com

ID:28564

Keywords:

Tables,Pivot,Excel,

Description:

Do you want to impress your boss? Do you want to see the bigger picture at work more quickly? If you answered Yes to either or both of these questions, then read on.You may be thinking .. What is a pivot table ? Well, a Pivot Table summarizes a large amount of data into a simple and easy way that allows the user to see what the important numbers are in the data. You may have thousands of rows of data, with some rows that are less significant than others so  Pivot Table will let you choose the important summary metrics and summarize the data according to the metrics you choose.Learn how to use Pivot Tables and become an instantly more valuable employee! This is a step by step guide to creating Pivot Tables in Excel. There are only 7 steps!  It is a tutorial for Pivot Tables in ALL versions of Excel from 2007 onwards Excel 2007, 2010, 2013, 2016, etc.We’ve deliberately used a SIMPLE example, as it easier to learn things with simple examples.To start, open the file for the Pivot Table tutorial by clicking here: link to fileA screenshot of what the file looks like is below:You will see that the Excel table shown in sheet 1 in the file shows the expenses paid by company ABC Ltd over a period of time, starting on the 8th of January 2013. The table has over 81 rows and you can’t see at a glance how much was paid for Pens and Pencils in each month  there are 4 months in it January to April, and given that there are so many rows, this is where a Pivot Table comes in handy! The columns in the spreadsheet are:Column A – Month   the month that goods were purchased inColumn B – Date   the date within that month that the goods were purchasedColumn C – Supplier the company that the goods were purchased fromColumn D onwards – the goods that were actually purchased – pencils in column D and pens in column E. I had added more “Goods” / “Products” but let’s keep it simple.We’ll use a pivot table to summariseThe expenditure by MONTH andThe expenditure by GOODS purchasedThe Pivot table will make it easy to see these figures at a glance – that’s their function summarising large amounts of information in a smaller, digestible format.Step 1Click on sheet 2 in the spreadsheet – we want to have the Pivot Table in a separate tab, so that it it is easier to look at when we’re finished.Step 2You will automatically be in cell A1Click on the Insert tab in the toolbar – it is to the right of the Home tab (see image 1)Image 1Step 2On the left hand side, you will see an icon called “pivot table”  (see image 2 below)– click on it, then click “Pivot Table” (instead of “pivot chart”)Image 2Step 3The “Create Pivot Table” window will have popped up (see image 3 below) and here you will need to specify the range of data that you want to create a Pivot Table for.Image 3Step 4At this point, you need to click on “Sheet 1” in the spreadsheet (because that’s where the data is that we want to create the pivot table for).  The cursor will already be in the right place in the “Create Pivot Table” window, so all you have to do is highlight cells A4 to F86  Excel will automatically put in $ signs for you when you do this, as it is fixing the range of data you are looking at – that’s what dollar signs do! See image 4 below. NB if the numbers and letters in your “Table/Range” field shown in the screenshot below are in any way different, then you’ve made an error and you’’ll need to ensure they match. But if you followed the instructions, there’s no reason why they shouldn’t!Image 4Step 5Click “Ok” – at which point Excel will take you back to “Sheet 2” (see image 5 below). Now go to the right hand side of the screen, where it says “Pivot Table Field list” / “Choose Fields to add to report” and tick MONTH. Your screen should look like IMAGE 6 below – there is a grid of four squares and MONTH should appear in the “Row Labels” square.Image 5 Image 6Step 6This is the tricky part – drag your mouse over to the word “Pencils” in the “Pivot Table Field list” / “Choose Fields to add to report” and drag it down to the “Values” square out of the four squares. Your screen should now look like IMAGE 7 below where the values square has “Count of Pencils”Image 7Step 7The “Count of Pencils” calculation counts the number of times Pencils were purchased each month, but we want to change that to “Sum of Pencils” so we can see how much was spent on Pencils each month. To do this, click on “Count of Pencils” then click “Value Field Settings” (see IMAGE 8 below) then in the “Value Field Settings” window that pops up (see IMAGE 9 below), click on “Sum” then click on “Number Format” in the same window – also shown in the screenshot, and change it from “General” to “Currency”. Click ok – that will return you to the “Value Field Settings” table. Click ok again, then repeat Steps 6 and 7 for the “Pens” in the “Pivot Table Field list” / “Choose Fields to add to report”. When you repeat Steps 6 and 7 for “Pens” you will notice that the sigma sign and the word “Values” will appear in the “Column Labels” square (see image 10 below).But don’t panic, this is ok! It’s simply the titles for your columns, which will now be “Sum of Pencils” and “Sum of Pencils”, respectively. This will now complete our Pivot Table – ie it shows at a glance how much money was spent in each month on “Pens” and “Pencils” as well as the Grand Total for each in row 6. If you want to test this, you can go back to Sheet 1 and check the Totals in row 87.OPTIONAL STEP with reasons why a Pivot Table may disappear and another field to add.You can also manually check how much was spent on “Pens and “Pencils” for each month in Sheet 1, but that beats the point. The Pivot Table’s done that for you! To make the Pivot Table more “flashy”, and gain a better understanding of how they work, go to Sheet 2 and tick the “Supplier” box in the “Pivot Table Field list” / “Choose Fields to add to report” window. If it’s disappeared, don’t worry – it will only have disappeared if you clicked outside of the Pivot Table in Sheet 2 eg if you clicked cell E1, it would disappear! But if you clicked on cell A1, the table on the right would magically re-appear! And voila, you can tick “Supplier” to see how much was spent on each “Supplier” each month for the different products. When you do this, it will look like IMAGE 12 below. That’s it! The comments box is below if you wish to post a comment! Peace out! Image 8 Image 9 Image 10 Image 11 Image 12Excel Tutorials | Learn ExcelPivot Tables Youtube Tutorial FileWe ask that you take a moment to read our Terms and Conditions and Privacy Policies. Thank you for such clear, unambiguous instructions as well as explanations on why we re doing what we re doing! Reply Thank you soooo much!!!! You have taught us very well. I do appreciate the sample file where we can practice and do it ourselves. This is very helpful to a Visionary person like me.

TAGS:Tables Pivot Excel 

<<< Thank you for your visit >>>

We have put together this step by step Pivot Tables in Excel tutorial, and we believe you will find it very handy!

Hot Websites