Excel Tips &

Pivot Table

Excel Tips

  1. Select And Replace Blank Cells

  2. Unstack Data Using the Delete Blank Rows

  3. Remove Duplicate

  4. Parse Data In Excel

  5. Extract Number From Text

Select And Replace Blank Cells

Unstack Data Using the Delete Blank Rows

Remove Duplicate

Parse Data In Excel

Extract Number From Text

Pivot Table

  1. PivotTables can easily transform your dreaded summary report creating session from hours  of work to just a few clicks!

  2. Provide quick insights by get answer from large data without write complex formula.

  3. Easy and fast to create and use, just drag and drop and no VBA macros

Using Formula

Convert to Excel Table

  1. Select the cell in the table, go to Insert > Tables > Table.

Create Pivot Table

  1. To create a PivotTable, select a cell in the worksheet > Insert > Tables > PivotTable.
  2. Then, select the range of the table and where to locate the PivotTable.

Drag and Drop

Pivot Table Diagram

Group Text Fields

  1. To group text fields, right-click mouse (select column) > Group > named accordingly  

Group Numeric Fields

  1. To group the numeric data, go to Analyze > Group > Group Field 

Group Date Fields

  1. Drag the Date into the Rows field

  2. Ungroup default grouping, go to Analyze > Group > Ungroup

  3. To group the data, go to Analyze > Group > Group Field

Pivot Chart

  1. Click the cell in the PivotTable > Analyze > Tools > PivotChart

Dashboard

  1. Copy all the charts to the Dashboard worksheet

  2. Select the chart, go to Analyze > Insert Slicer

Dashboard

Excel Tips &

By shirlin1028

Excel Tips &

  • 118