COURSE DESCRIPTION: Pivot Tables are an absolutely essential tool for anyone working with data in Excel. Pivots allow you to quickly explore and analyze raw data, revealing powerful insights and trends otherwise buried in the noise.
In other words, they give you answers. Whether you're exploring product sales, analyzing which marketing tactics drove the strongest conversion rates, or wondering how Boston condo prices have trended over the past 15 years, Excel Pivot Tables provide fast, accurate and intuitive solutions to even the most complicated questions. This course gives you a deep, 100% comprehensive understanding of Excel Pivot Tables and Pivot Charts.
In Excel 2013, you can apply a wide variety of pivot table styles, and one is automatically used on new pivot tables. To get close to the Classic look, follow these steps: Select a cell in the Pivot Table. Pivot Table Tools - Design Tab. Subtotals - Drop-Down. Lets you show subtotals at the top or bottom of groups. Compact is useful to minimise scrolling and to stop data moving horizontally. Tabular is useful to show the data in a conventional table format.
I'll show you when, why, and how to use Pivot Tables, introduce advanced sorting, filtering, and calculation tools, and guide you through interactive, hands-on demos and exercises every step of the way. Chris Dutton is a certified Microsoft Excel Expert, analytics consultant, and best-selling instructor with more than a decade of experience specializing in business intelligence, marketing analytics and data visualization. He founded Excel Maven in 2014 to provide high-quality, applied analytics training and consulting to clients around the world, and now mentors 35,000+ students in more than 170 countries. He has developed award-winning data analytics and visualization tools, featured by Microsoft, the New York Times, and the Society of American Baseball Research.
What is a pivot table in Excel? A pivot table allows you to organize, sort, manage, and analyze large data sets in a dynamic way.
Pivot tables are one of Excel’s most powerful data analysis tools, used extensively by financial analysts around the world. In a pivot table Excel essentially runs a database behind the scenes, allowing you to easily manipulate large amounts of information. How to use a pivot table in Excel Below is a step by step guide of how to insert a pivot table in Excel: #1 Organize the data The first step is to ensure you have well-organized data that can easily be turned into a dynamic table. This means ensuring that all data is in the proper rows and columns. If data is not properly organized the table will not work properly. Ensure that the categories (category names) are located in the top row of the dataset, as shown in the screen shot below. #2 Insert the pivot table In step two you select the data you want to include in the table and then on the Insert Tab on the Excel ribbon locate the tables Group and select Pivot Table, as shown in the screenshot below.
When the dialog box comes up, ensure the right data are selected and the decide if you want the table to be inserted as a new worksheet, or located somewhere on the current worksheet. This is entirely up to you and your personal preference. #3 Setup the pivot table fields Once you’ve completed step two, the “PivotTable Fields” box will appear. This is where you set the fields by dragging and dropping the options that are listed as available fields. You can also use the tick boxes next to the fields to select the items you want to see in the table. #4 Sort the table Now that the basic pivot table is in place, you can sort the information by multiple criteria, such as name, value, count or others. To sort the date, click on the autosort button (highlighted in the image below) and then click “more sort options” to pick the various criteria you can sort.
Another option is to right click anywhere in the table and then select Sort, and then “more sort options”. #5 Filter the data Adding a filter is a great way of sorting the data very easily. In the above example we how to sort, but now with the filter function, we can see the data for specific sub-sections with the click of a button. In the image below you can see how, by dragging the “channel” category from the list of options down to the Filters section, all of a sudden an extra box appears at the top of the pivot table that says “channel” indicating the filter has been added. Next, we can click on the filter button and select the filters we want to apply (as shown below).
After this step is completed, we can see the revenue, shipping, and marketing spend for all products that were sold via the Instagram channel, for example. More filters can be added to the pivot table as required. #6 Edit the data values (calculations) The default in Excel pivot tables is that all data is shown as the sum of whatever is being displayed in the table. For example, in this table we see the sum of all revenue by category, the sum of all shipping expenses by category, and the sum of all marketing expenses by category. To change from showing the sum or all revenue to the “count” of all revenue we can determine how many items were sold. This may be useful for reporting purposes. To do this, right click on the data you wish the change the value of and select “Value field settings” which will open the box you see in the screenshot below.
In accounting and financial analysis, this is a very important feature, as it’s often necessary to move back and forth between units/volume (the count function) and total cost ore revenue (the sum function). #7 Adding an extra dimension to the pivot table At this point we only have on category in the rows, and one in the columns (the values). It may be necessary, however to add an extra dimension. A brief warning, however, that this could significantly increase the size of your table. In order to do this, click on the table so the “fields” box pops up and drag an extra category, such as “dates” into the columns box.
This will sub divided each column heading into additional columns for each date contained in the data set. In the example below you can see how the extra dates dimension has been added to the columns to provide much more data in the PivotTable. Additional features and examples To learn more about PivotTables in Excel, check out our Advanced Excel Course which covers all the main functions a financial analyst needs to perform an analysis.
In addition to that course, we also highly recommend these free resources:. Excel Shortcuts PC Mac Excel Shortcuts - List of the most important & common MS Excel shortcuts for PC & Mac users, finance, accounting professions. Keyboard shortcuts speed up your modeling skills and save time. Learn editing, formatting, navigation, ribbon, paste special, data manipulation, formula and cell editing, and other shortucts. Excel Formulas Cheat Sheet CFI's Excel formulas cheat sheet will give you all the most important formulas to perform financial analysis and modeling in Excel spreadsheets. Advanced Excel Formulas Must Know These advanced Excel formulas are critical to know and will take your financial analysis skills to the next level.
Advanced Excel functions you must know. Learn the top 10 Excel formulas every world-class financial analyst uses on a regular basis. These skills will improve your spreadsheet work in any career. Var searchInputs = document.getElementsByName('s'); for(var i = 0; i.