\n<\/span><\/li>\n<\/ul>\n
Preparing Your Data<\/b><\/h2>\n
Before creating a PivotTable, ensure your data is clean and organized.<\/span><\/p>\nGuidelines:<\/b><\/h3>\n\n- Your data should be in a tabular format (no blank rows\/columns).\n
<\/span><\/li>\n
- Each <\/span>column must have a unique header<\/b>.<\/span>\n
<\/span><\/li>\n
- Avoid merged cells.<\/span>\n
<\/span><\/li>\n
- Make sure data types are consistent within columns (e.g., all dates, all numbers).<\/span>\n
<\/span><\/li>\n<\/ul>\n
Example:<\/span><\/p>\n\n\n\n| Region<\/b><\/td>\n | Salesperson<\/b><\/td>\n | Product<\/b><\/td>\n | Sales<\/b><\/td>\n | Date<\/b><\/td>\n<\/tr>\n\n| East<\/span><\/td>\n | Alice<\/span><\/td>\n | Printer<\/span><\/td>\n | 500<\/span><\/td>\n | 01\/01\/2024<\/span><\/td>\n<\/tr>\n\n| West<\/span><\/td>\n | Bob<\/span><\/td>\n | Laptop<\/span><\/td>\n | 1200<\/span><\/td>\n | 01\/02\/2024<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\nHow to Create a PivotTable in Excel 2016 Pro<\/b><\/h2>\nStep 1: Select Your Data<\/b><\/h3>\n\n- Click anywhere inside your data range.<\/span>\n
<\/span><\/li>\n - Or manually select the entire table.<\/span>\n
<\/span><\/li>\n<\/ul>\n Step 2: Insert a PivotTable<\/b><\/h3>\n\n- Go to the <\/span>Insert<\/b> tab on the Ribbon.<\/span>\n
<\/span><\/li>\n - Click on <\/span>PivotTable<\/b>.<\/span>\n
<\/span><\/li>\n - In the dialog box:<\/span>\n
<\/span><\/p>\n \n- Choose your data range (selected automatically if you're clicked in a table).<\/span>\n
<\/span><\/li>\n - Decide whether to place the PivotTable in a new worksheet or the current one.<\/span>\n
<\/span><\/li>\n<\/ul>\n<\/li>\n - Click <\/span>OK<\/b>.<\/span>\n
<\/span><\/li>\n<\/ol>\n Step 3: Building Your PivotTable<\/b><\/h3>\nThe <\/span>PivotTable Field List<\/b> appears on the right.<\/span><\/p>\nThere are four main areas:<\/span><\/p>\n\n- Filters<\/b>For filtering the whole PivotTable.<\/span>\n
<\/span><\/li>\n - Columns<\/b>Fields to display across the top.<\/span>\n
<\/span><\/li>\n - Rows<\/b>: Fields to display down the left.<\/span>\n
<\/span><\/li>\n - Values<\/b>Fields to summarize (e.g., totals, averages).<\/span>\n
<\/span><\/li>\n<\/ul>\n Example:<\/b><\/h4>\nTo analyze total sales per region:<\/span><\/p>\n\n- Drag \"Region\" to <\/span>Rows<\/b>.<\/span>\n
<\/span><\/li>\n - Drag \"Sales\" to <\/span>Values<\/b>.<\/span>\n
<\/span><\/li>\n<\/ul>\n Customizing Your PivotTable<\/b><\/h2>\n1. change the Summary Calculation<\/b><\/h3>\nBy default, numerical fields are summed.<\/span><\/p>\nTo change:<\/span><\/p>\n\n- Click the dropdown on the value field in the PivotTable.<\/span>\n
<\/span><\/li>\n - Choose <\/span>Value Field Settings<\/b>.<\/span>\n
<\/span><\/li>\n - Select other options like <\/span>Count<\/b>, <\/span>Average<\/b>, <\/span>Max<\/b>, <\/span>Min<\/b>etc.<\/span>\n
<\/span><\/li>\n<\/ul>\n 2. format numbers<\/b><\/h3>\n\n- Right-click a value in the PivotTable.<\/span>\n
<\/span><\/li>\n - Choose <\/span>Number Format<\/b> to apply currency, percentage, or custom formatting.<\/span>\n
<\/span><\/li>\n<\/ul>\n 3. rename fields<\/b><\/h3>\n\n- Click on a field name in the PivotTable and type a new, more descriptive name.<\/span>\n
<\/span><\/li>\n<\/ul>\n Using PivotTable Tools<\/b><\/h2>\nExcel 2016 introduces the <\/span>PivotTable Tools<\/b> tab when the PivotTable is selected. It consists of two sub-tabs:<\/span><\/p>\nAnalyze Tab:<\/b><\/h3>\n\n- Insert Slicer<\/b>Adds filter buttons for quick filtering.<\/span>\n
<\/span><\/li>\n - Field List<\/b>Show\/hide the field list.<\/span>\n
<\/span><\/li>\n - Refresh<\/b>Updates data if the source table has changed.<\/span>\n
<\/span><\/li>\n<\/ul>\n Design Tab:<\/b><\/h3>\n\n- Report layout<\/b>Show in Tabular, Compact, or Outline form.<\/span>\n
<\/span><\/li>\n - Subtotals\/Grand Totals<\/b>Turn them on\/off or adjust their positions.<\/span>\n
<\/span><\/li>\n - Styles<\/b>Apply visual formatting for better presentation.<\/span>\n
<\/span><\/li>\n<\/ul>\n Using Slicers and Timelines<\/b><\/h2>\nSlicers make it easy to filter data visually.<\/span><\/p>\nTo Add a Slicer:<\/b><\/h3>\n\n- Select your PivotTable.<\/span>\n
<\/span><\/li>\n - Go to <\/span>Analyze > Insert Slicer<\/b>.<\/span>\n
<\/span><\/li>\n - Choose the fields you want slicers for (e.g., Region, Product).<\/span>\n
<\/span><\/li>\n - Click OK.<\/span>\n
<\/span><\/li>\n<\/ul>\n You can now filter the PivotTable by clicking the buttons in the slicer.<\/span><\/p>\nTo Add a Timeline (for date fields):<\/b><\/h3>\n\n- Go to <\/span>Analyze > Insert Timeline<\/b>.<\/span>\n
<\/span><\/li>\n - Select a date field.<\/span>\n
<\/span><\/li>\n - Click OK and use the slider to filter data by time.<\/span>\n
<\/span><\/li>\n<\/ul>\n Grouping Data in PivotTables<\/b><\/h2>\nYou can group:<\/span><\/p>\n\n- Numbers (e.g., group sales in ranges)<\/span>\n
<\/span><\/li>\n - Dates (e.g., by month, quarter, year)<\/span>\n
<\/span><\/li>\n - Text (manual grouping)<\/span>\n
<\/span><\/li>\n<\/ul>\n To Group:<\/b><\/h3>\n | | |