Difficulty: Medium
Functionality: Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance from a large, detailed data set.
Tip 1: Simple example - Setting up the Pivot
We have been provided the below data set - It includes the details of a budget across several months.
In its current format, this information does not provide any useful or actionable information. The data would be much easier to understand if it was summarised by category and total expenditure by month. A pivot table will allow us to do this by simply by adjusting the parameters.
Select the data table. Go to Insert > Tables > PivotTable on the top ribbon.
Excel will display the Create PivotTable dialog with your range or table name selected. In this case, we're using a table called "tbl_HouseholdExpenses".
In the Choose where you want the PivotTable report to be placed section, select "New Worksheet", or "Existing Worksheet". For "Existing Worksheet", you'll need to select both the worksheet and the cell where you want the PivotTable placed. For "New Worksheet", the pivot table will be placed on a newly created tab.
Click OK, and Excel will create a blank PivotTable (As seen below), and display the PivotTable Fields list.
Tip 2: Simple example - Using the Pivot to manipulate data
In the Field Name area at the top, select the check box for any field you want to add to your PivotTable. By default, non-numeric fields are added to the Row area, date and time fields are added to the Column area, and numeric fields are added to the Values area. You can also manually drag-and-drop any available item into any of the PivotTable fields, or if you no longer want an item in your PivotTable, simply drag it out of the Fields list or uncheck it. Being able to rearrange Field items is one of the PivotTable features that makes it so easy to quickly change its appearance. For this particular example, we want to summarise our data by category (columns) and by month (rows).
Click into the blank pivot table and input the following parameters.
The blank pivot table will autopopulate with the data as seen below.
Excel Masterclass Course
If you want to learn more about Excel, regardless of your skill level, we offer free and premium Excel training courses on our website, www.meritexcel.com. Our Excel masterclass covers:
- all the key formulas you need (sumif, index, match, offset, eomonth, concatenate, lookup, if, or, sumproduct, date and time formulas, trim and much more)
- How navigate and use Excel effectively (viewing in 2 windows, paste special, locking cells, removing duplicates, autofill, freezing rows, fill blanks)
- Best practice techniques in Excel (formatting, checks, grouping, formula auditing, structure)
- How to present and manipulate data (Pivot tables, charts, editing charts, filtering and sorting)
- How to create engaging and dynamic spreadsheets (drop-downs, protect data, goal seek and data tables)
- And much, much more!
We offer a free, limited course which you can view here OR you can purchase the full, Excel masterclass here, which has over 20+ hours of amazing Excel content.
ความคิดเห็น