Home > Microsoft Excel > How to Create a Pivot Table in Excel? — The Easiest guide Show
If you ask anyone with decent experience in using Excel, about the most useful Excel feature, they will vouch for the Excel Pivot Table. It is one of the most searched Excel features on the internet, and for good reason. Related: Creating A Dynamic Pivot Chart Title Using Slicers Using Getpivotdata In Excel Dashboards In Excel Using Pivot Tables, Pivot Charts And Slicers In this guide, let’s see what makes the Pivot table one of the most popular and powerful Excel features. This guide covers What is a Pivot Table?Microsoft describes a Pivot Table in Excel (or PivotTable if you’re using the trademarked function name!) as “an interactive way to quickly summarize large amounts of data”. It’s a pretty good description. What is the use of a Pivot Table in Excel?The Excel Pivot Table function is an essential part of data analysis in Excel. Before the Pivot Table came along you’d need multiple functions tied together in a complicated and convoluted way to perform the same action that just takes a few clicks in a Pivot Table. To say they revolutionised the way the average Excel user performs data analysis is an understatement. They are such a big deal that they have their own Wikipedia page. For the end-user, Pivot Tables are remarkably simple to use and easy to learn. There are hundreds of brilliant articles on how to create your first Pivot Table as well as some excellent lessons on YouTube. We’re going to contribute by showing you some of our highest rated videos that teach you how to create a Pivot Table in Excel. If for any reason you don’t get it the first time, don’t worry, we’ve included multiple Pivot Table tutorials to help you master this essential skill. If you have an hour to dedicate to a Pivot Table tutorial, then start with the video below. This is a recording of a live class we held in 2019 that takes you through everything you need to know to start analysing your data using Pivot Tables. These live classes are all free as part of a Simon Sez IT membership. Learn Pivot Tables in 1 Hour – Video Tutorial If that’s too much, scroll down and we have some other, shorter videos taken straight from our Excel courses. How does an Excel Pivot Table work?All Pivot Tables start life as a boring old range of data. But once you create a Pivot Table, Excel takes a quick look at the data and stores it in its cache. This is called the Pivot cache and it is responsible for the super fast calculation of summaries that Pivot Tables are known for. Each time you add or remove data from the Excel Pivot Table, Excel does not deal with the source data, rather it uses this Pivot Cache as a quick shortcut. Also Read: Introduction To Power Pivot and Power Query In Excel Getting Started With Power Pivot: Advanced Excel Excel Crash Course – Learn Pivot Tables In 1 Hour How to Create a Pivot Table in Excel?Step 1: Turn the Data Range into a TableYou can create a Pivot Table in Excel from a range but we strongly recommend that you turn your range into a table as this makes it a lot simpler to add or remove data later on. For example: Turn your Range into a Table to add or remove data later onA few golden rules about your data range or table before you create an Excel Pivot Table with it:
Step 2: Open the Create Pivot Table WizardOnce you’ve turned your range into a table (use Ctrl-T to do this quickly!) you then need to select a cell in that table, go to Insert on the ribbon and select Pivot Table on the far left. This brings up the Create Pivot Table Wizard where you can start selecting your Pivot Table options. Step 3: Select the Source Table or Range for the Pivot TableThe first option you’ll notice is that Excel is asking you to select the table or range. Because we have already created a table and we were clicked into that table when we chose to insert the Pivot Table, Excel has done the hard work for us and has selected that table as our range of data. Select the Table you have created or the Range you need for the Pivot TableStep 4: Set the Location of the Pivot TableSelect whether you create your Pivot Table in a new or an existing worksheet. Once you hit OK, you’ve created your first Pivot Table. Hurray! What you’ll see next is a blank table to the left with a set of options on your right. These options are the Pivot Table fields and this is where the magic starts to happen. I’ll show you how to do this in the next section. Pivot Table Fields on the right is the place to add or remove data into your Pivot tableHow to Add Data to an Excel Pivot Table?Using the Pivot Table Fields panel you can now start to manipulate your data. Four QuadrantsA pivot table is based on these four quadrants:
We’ll see what each of these quadrants mean in a minute. These four quadrants are the key to manipulating the data in your Pivot Table. You can now start to drag the values at the top of the Excel Pivot Table Fields section into the quadrants below. Add or remove Pivot table Fields into any of these Four QuadrantsValues:The values quadrant is what decides the type and value of calculations that the Pivot Table should display. It is the meat of a Pivot Table so to speak. In the following image, the area bordered in red is the Values area in a Pivot Table. The Values section displays the value of PivotTable fieldsRows:The rows quadrant is what decides the rows that the Pivot Table should display. The Rows are used to slice the data in a suitable way that we are looking for. For example, you want to look at the total sales that occurred in different months. For this, you need to drag Months in the Rows quadrant. In the following image, the area bordered in green is the Rows area of a Pivot Table. Rows Area displays the Rows of the Pivot TableColumns:The columns quadrant is what decides the columns that the Pivot Table should display. That is columns are used to further dice the data into a suitable format. For example, you want to look at the total sales that occurred in different months across different departments. For this, you need to drag Months in the Rows quadrant and further drag Departments into the Columns quadrant. In the following image, the area bordered in blue is the Columns area of a Pivot Table. Columns Area displays the Columns of the Pivot TableFilters:The filters quadrant is optional and is used to further drill down your Pivot Table. For example, you may want to look only at the sales value of the Detroit Branch. This can be done by dragging the Branch field into the filter quadrant. Now, you can select the branch you are looking for from the drop-down list and view only its data. Value Field SettingsHow do you change what’s happening in your value field away from displaying the sum? Simple, you need Value Field Settings. To access these select any value in your Pivot Table, go to analyze on the ribbon and select “Field Settings”. Alternatively, click the little down arrow in the value quadrant and select “Value Field Settings”. This brings up the options you have in relation to your values. You can average instead of sum, you can count or use Min or Max. You can then select how you show the values including adding some calculations and changing the number format. You can change the type of value to be displayed in the Value Field SettingsAnalyse data using Pivot TableDepending on which quadrant you pick, the table will format differently so there are a few rules to stick to:
Sales Values across MonthsSay you have dates in your rows quadrant and a set of corresponding values in your values quadrant. Excel will automatically do a couple of things.
This is the Pivot Table starting to work. From your dataset, it’s now summarising that data by month for you. All within a few clicks. Of course, you may not want that exact data and you may not want to add it together. The amazing thing about the Excel Pivot Table function is just how flexible it is. You can drag and drop, remove and change the data within those quadrants as much as you want and you’ll start to see just how powerful Pivot Tables are. Sales Values across months in Each branch.The data in our example is sales data. Above we’re seeing sales by month which is useful. But what if we wanted to see sales by branch and by date? Easy, we just drop the branch data into the rows column, under the date and we get a breakdown of that as well: Add Branch in the Rows Quadrant below the Months field, to display sales values for each branch for each monthSales Values across months in Each branch for each department.If we wanted to get even more detail we could then add the departments to the column data and we’d see a summary of sales by branch, by the department and by date! You can quickly see that with very little effort on our part we can now draw really meaningful insight from our dataset: Add The Department Field to the Columns Quadrant to display the Sales values in Each month for each State across different departmentsWhat are the Benefits of Pivot Tables?All that an Excel Pivot table does is help you effortlessly slice and dice your data. A normal Excel sheet or table might not suffice for your data needs. Suppose you need to quickly find out the Bread sales in January that occurred in the Detroit region, manually doing it each time using filters or formulas can be painstaking. Or if you need to quickly look at the top 5 performing regions in the coffee department, using an Excel function is akin to taking the roundabout way to reach your destination. An Excel Pivot Table achieves all this and more in just a few clicks. Slicers and Filters If you’ve grasped the basics and you’re ready to conquer some more advanced pivot table tutorials, then you need to know all about Slicers and Filters. By filtering and slicing your data in a Pivot Table you can start to get analyse specific areas of your dataset and pull out interesting patterns. Plus, if you want to create an interactive dashboard that others can use, you’ll want to master these functions. All this and so much more is explained in this video:
In this video our Excel expert Toby shows you everything you need to know about Slicers & Filters :
Suggested Reads: How To Use Excel Countifs: The Best Guide Excel Sumifs & Sumif Functions – The No.1 Complete Guide How To Protect Cells In Excel Workbooks-the Easiest Way FAQsWhat is the use of a Pivot Table in Excel?An Excel Pivot Table is used to summarise data in a reorganised format. While doing this, you can sort, filter, sum, count or even average your values across different fields. What is a Pivot Table formula?In a Pivot Table, under the value field settings, you will find summary functions to find SUM, AVERAGE, and COUNTS of values for the fields. If they are not enough you can create your own formula to find the required value. These are called Pivot Table formulas Let’s wrap upThat’s enough Pivot Tables for today, isn’t it? In this guide, we looked at the basics of creating and using pivot tables. The key takeaway from this tutorial is that an Excel Pivot Table is a very versatile tool to drill down and look at your data. There are more interesting things to do with them and we’ll deal with them in later advanced guides. If you find this guide useful, check out our Excel courses for more high-quality comprehensive guides on advanced Excel topics. If all the above isn’t enough Pivot Table for you, then we’ve got an extended Pivot Table video here for you. It’s 40 minutes long, so get comfy.
Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 100+ IT training courses. Other Excel classes you might like:
Deborah AshbyDeborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom. She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP. She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute. How do you organize a PivotTable?In a PivotTable, click the small arrow next to Row Labels and Column Labels cells. Click a field in the row or column you want to sort. on Row Labels or Column Labels, and then click the sort option you want. To sort data in ascending or descending order, click Sort A to Z or Sort Z to A.
What is the field list in a PivotTable?Then, show the PivotTable Tools on the ribbon and click Analyze> Field List. The Field List has a field section in which you pick the fields you want to show in your PivotTable, and the Areas section (at the bottom) in which you can arrange those fields the way you want. and then pick the layout you want.
What displays the total values for each particular row or column in a PivotTable?On the PivotTable Analyze tab, in the PivotTable group, click Options. In the PivotTable Options dialog box, on the Totals & Filters tab, do one of the following: To display grand totals, select either Show grand totals for columns or Show grand totals for rows, or both.
Which are types of fields used by pivot tables quizlet?PivotTable fields can be one of three types: data, summary, and category.
|
The four areas for organizing fields in a pivottable are rows, columns, ________ and values.
zusammenhängende Posts
Urheberrechte © © 2024 paraquee Inc.
