Mastering Excel: Pivot Tables

Product Information

Follow step 1 and step 2 to make a pivot table on this data. Here we will be choosing the fields as Products (Row) and Total (Value).

Important: For this, we need to use the power query, so make sure you have the power query in your Excel version. For Excel 2016, it’s there on the Data tab, and for other versions (2010 and 2013) you need to install the add-on. If you are a beginner, then go for the Excel 2016/2019 Bible or Excel 2016 for Dummies. User who are not beginners and wants to learn formulas and functions then buy Excel Formulas & Functions for Dummies as well asLearn Excel 2016 Expert Skills. We have technically deleted the year 2012 records, so they should be gone from our Pivot Table, right?

Overview

We don’t require all the columns to complete our analysis so we’ll choose only the necessary fields. On the right side, a toolbar will appear – PivotTable Fields. We will choose the required field, in our case, these are Member and Amount: STEP 4: In the Grouping dialogue box, Excel was able to determine our date range (minimum date and maximum date). Microsoft Excel is a spreadsheet program used to record and analyze numerical and statistical data. Microsoft Excel provides multiple features to perform various operations like calculations, pivot tables, graph tools, macro programming, etc. 📚 Which are the best Excel books? Professional Excel Development: The Definitive Guide to Developing Applications Using Microsoft Excel, VBA, and .NET (2nd Edition) by Rob Bovey, Dennis Wallentin, Stephen Bullen, John Green

VBA for Modelers: Developing Decision Support Systems with Microsoft Office Excel by S. Christian Albright I have personally read each of the books and learned a lot from them. You don’t need to get all these books. Just choose one (or a couple) about the topics you wish to learn about and get these first. Microsoft Excel 365 Bible (1st Edition) Good Charts: The HBR Guide to Making Smarter, More Persuasive Data Visualizations by Scott Berinato

Pivot Table Trick #5 – Pivot Table Slicers

Microsoft Excel 2013 Building Data Models with PowerPivot (Business Skills) by Alberto Ferrari, Marco Russo Drag the Region Field in the Rows area. Now drag the Customer field in the Rows area below the Region field. When you do this, Excel would understand that you want to categorize your data first by region and then by customers within the regions. You’ll have something asshown below: To analyze data using a Pivot Table, you need to decide how you want the data summary to look in the final result. For example, you may want all the regions in the left and the total sales right next to it. Once you have this clarity in mind, you can simply drag and drop the relevant fields in the Pivot Table. Excel 2016 Pivot Table Data Crunching (includes Content Update Program) (MrExcel Library) by Bill Jelen, Michael Alexander This Excel book is not recommended for the beginners, the person must have the basic idea of the pivot table and databases.