Right-click the Revenue field, and set “Show Values As” to “% of Grand Total” You can do so by performing this one simple step (assuming you have created the pivot table):ġ. But, rather than show the total revenue for each product, you want to show revenue as a percentage of the total revenue. For example, perhaps you want to show a breakdown of revenue by product. In some of the cases, you’ll want to show a percentage rather than a count in your pivot table. The pivot table above shows total revenue by product, but you can easily rearrange fields to show total revenue by customer, by product line, by month, and so on by dragging different fields to the Rows or Values area. Voila ~ in just a minute, you got your first pivot table in excel. Drag a numeric field into the Values area (e.g. Drag a “label” field into the Row Labels area (e.g. In the Create PivotTable dialog box, select the data range and click OKģ. On the Insert tab of the ribbon, click the PivotTable buttonĢ. Start by selecting any cell in the source data.ġ. If you have well-structured source data, you can create a pivot table in less than a minute. Compared to the time it would take you to build an equivalent report manually, pivot tables are incredibly fast. Many people think building a pivot table in excel is complicated and time-consuming, but it’s simply not true. For you who have good working knowledge on Pivot Table and would like to advance further, you may consider our Advanced Pivot Table for Microsoft Excel 2016 & 365 Users course.īelow are 3 simple yet useful features to start your pivot table journey Building a Pivot Table in Excel in Just One Minute If you want to advance your current level of Excel skills and be more qualified in working with pivot tables in excel, then don’t miss out on our 1-Day Pivot Table for Microsoft Excel 2016 & 365 Users: Basic to Intermediate Workshop. Therefore, you will need to remove it for better analysis with pivot table in excel. If you have an existing data report that has already been summarised (with total, subtotals, or average value), it will hinder the ability of pivot table in excel to produce different slices of the data. In summary, it is best to use your raw data report (as shown in the image above) to create your pivot tables for data analysis. Image: Sample of Datasets for Pivot Table in Excel There should be no merge cells in the datasets.All empty cells should contain zero or text (e.g.There should be NO blank rows and blank columns.Datasets must have a unique column header that identifies the column field.Datasets must be organized in rows with each row represent a record.
How to create a pivot table in excel how to#
5 Ground Rules before Creating Pivot Table in Excelīefore we move on to learn how to create a Pivot Table in excel, it is important to understand the 5 ground rules when using the pivot table feature in excel: Therefore, it helps to make data analysis easier and more effective. The beauty of pivot table in excel is they allow you to interactively explore your data in different ways. You can group data into categories, break down data into years and months, filter data to include or exclude categories, and even build charts. With very little effort (and no formulas) you can look at the same data from many different perspectives. However, unlike a static report, a pivot table in excel provides an interactive view of your data. You can think of a pivot table as a report. With very little effort, you can use a pivot table to build good-looking reports for large data sets. Pivot tables are one of the most powerful and useful features in Excel. Data stored in tables have multiple benefits over data in cell ranges of a worksheet, especially when using PivotTables ( read more about the benefits of using tables).What is Pivot Table in Excel and Why is it Useful? If your data range is not already formatted as a table, we’d encourage you to do so. To do all this, we’re first going to format our range of values as a table in Excel and we’re then going to create a Pivot Table to make and display our percentage change calculations. It’s a pretty typical example of a sales sheet that shows the order date, customer name, sales rep, total sales value, and a few other things. In this article, we’re going to use the straightforward example of calculating and displaying the percent by which the total sales value changes month by month. You could use this technique to do all kinds of things-pretty much anywhere you’d like to see how one value compares to another. While typically used to summarize data with totals, you can also use them to calculate the percentage of change between values. Even better: It is simple to do. Pivot Tables are an amazing built-in reporting tool in Excel.