Excel Tutorial

Excel Tutorial Shortcut Keys in Excel Formatting in Excel Notes in Excel Formats in Excel Cells and Ranges in Excel Excel Function and Formulas Conditional Formatting in Excel Data Validation in Excel Charts in Excel Excel Ribbon Toolbar Basics of Excel Spell Check in Excel Data Analysis in Excel AutoFill in Excel Goal Seek in Excel Solver in Excel Pivots Table in Excel Go-To Special function in Excel Blank cells in Excel Count Cells with Text in Excel Date and Time in Excel-VBA Dependent Drop-down List in Excel Operators in Excel Dependent Combo box in Excel VBA Error Bar in Microsoft Excel Excel Axes Excel File using Password Excel Unique Values Frequency Distribution in Excel Gauge Chart in Excel Histogram in Excel Sum Every Nth Row in Microsoft Excel SumIF Formula in Microsoft Excel Multiplication in Excel Unique Values in Excel Trendline in Excel Excel Themes Copying formula in Excel Check Marks in Excel Calculating the Last Day of the Month in Excel Calculating Age in Excel Insert Row in Excel

Functions

Excel MAX() Function Excel INT() Function Excel MOD() Function Excel ROUND() Function Excel ROUNDUP() Function Excel AVERAGE() Function Excel COUNT() Function Excel COUNTA() Function Excel COUNTBLANK() Function Excel MIN() Function Excel EDATE() Function Excel EOMONTH() Function Excel HOUR() Function Excel MINUTE() Function Excel SECOND() Function Excel TIME() Function Excel WORKDAY() Function Excel WORKDAY.INTL() Function Excel DAYS() Function Excel WEEKNUM() Function Excel WEEKDAY() Function Excel SMALL() Function Excel LARGE() Function Excel LEFT() Function Excel RIGHT() Function Excel MID() Function Excel FIND() Function Excel SEARCH() Function Excel EXACT() Function Excel SUBSTITUTE() Function Excel TEXT() Function Excel VALUE() Function Excel AND() Function Excel OR() Function Excel IFERROR() Function Excel IF() Function Excel Nested IF’s Function Excel IFNA() Function Excel COUNTIFS() Function Excel VLOOKUP() Function Excel HLOOKUP() Function Excel INDEX() Function Excel MATCH() Function Excel OFFSET () Function Averageif Function in Excel

How To

How to import Microsoft Access data into the Microsoft Excel How to use TODAY function in Excel How to Alphabetize in Excel How to remove duplicate values from excel How to lock cells in Excel How to create drop down in excel How to Delete Row in Microsoft Excel How to Highlight Duplicates Words in the Microsoft Excel How to print titles in Excel How to make use of the Wildcard in Excel How to Make Use of the F-Test in Excel How to make use of the Excel Autofit in Excel How to generate random numbers in Excel How to apply Advanced Filter in Excel How to use Index and Match in Excel

Misc

Absolute Value in Excel Adding Column in Excel Converting Units in Excel Count Characters in Excel Custom Sort Order in Excel Decimals in Excel Division in Excel Locate Maximum Values in Excel Nearest Multiple in Excel Paste Options in Excel Quarter Dates in Excel Row Difference in Excel Separate Strings in Excel Reverse List in Excel Array Formula in Excel What if Analysis Data Table in Excel Excel Shortcut Keys What is a spreadsheet in Excel?

Pivots Table in Excel

What is a Pivot?

A pivot table is a tool to create summary reports from data-sets irrespective of their sizes but is helpful even if the data is more than just 20 rows!

Apart from creating summary reports (counting, summing observations for a variable like a salary or diesel consumption at a pump, etc..), it can be used to analyze data – by selecting various combinations of different variables, you can see how your data changes thus making strategies accordingly.

For example: In fraud, we may have millions of customers leading to millions of data, but the fraudsters may just be a few hundred. Using Pivot tables, we can identify the common attributes of fraudsters, thus targeting them without impacting the genuine customers.

Pivot Table Structure

The below are lists down all the columns we have in the dataset for which the Pivot table was inserted. Currently, we have 3 columns in our dataset – Name, Age and Salary

Pivots Table in Excel

The four pivot table data fields are given below:

Filters: In Filters, we will drag and drop those variables from the above list on which we want to apply a filter.

Columns: In Columns, we will drag and drop that variable for which we want its different values to appear in different columns

Rows: In Rows, we will drag and drop that variable for which we want its different values to appear in different rows.

Values: In Values, we will drag and drop those variables for which we want to summarize the data.

Pivots Table in Excel

How to create a Pivot table?

There are two ways to create a Pivot Table –

Using Toolbar Method –

  1. Go to Insert Tab and click on ‘PivotTable’.
Pivots Table in Excel
  • Excel will prompt you to give the data range for which you want to insert a pivot.
Pivots Table in Excel
  • Once you have given the data-range, it is then up to you whether you want to create the Pivot on the same worksheet or a new sheet – clicking on ‘New Worksheet’ will create the pivot table on the new worksheet and clicking on ‘Existing Worksheet’ will create the pivot table on the current sheet at the location (cell address) you specify by just clicking on the cell.
Pivots Table in Excel

Not using Toolbar –

  • Select the dataset for which you want to insert the pivot table
    • And using your keyboard press the following keys Alt key, D, P and P again (Alt + D + P + P)
    • A pop-up will appear for Pivot Table in which your dataset range would already be present
    • Now you just need to specify the place where you want to insert the pivot table i.e. a ‘New Worksheet’ or on the ‘Existing Worksheet’.

Once either of the above steps has been completed, and you click on ‘Finish’ (and if you had selected on ‘New Worksheet’ which most find more convenient to use), you will get the following format on a new sheet.

Pivots Table in Excel

Formatting in a Pivot Table

The salary figures in the above pivot table need formatting – we will use the following steps –

  • Go to the values field in the pivot table and click on the black arrow head pointing downwards
  • A pop-up menu as shown below will appear – click on ‘Value Field Settings’
Pivots Table in Excel
  • Once you have clicked on ‘Value Field Settings’, another pop-up menu will appear which will look like below –
Pivots Table in Excel
  • Before discussing ‘Custom Name’ field, let’s look at ‘Summarize Values By’ – since over here we are summing the salary variable (for numerical values, by default excel will sum the numbers and not count them) – ‘Sum’ has been highlighted, if you want to ‘COUNT’ and not ‘SUM’, you can select ‘Count’ and pivot table will then give you the count
  • Now assuming, we just want salary sum here, to change the format, we will click on ‘Number Format’ and a familiar window (shown below) will appear. You can now change the format to ‘Currency’ and add a $ (dollar) sign and click on ‘OK’
Pivots Table in Excel
  • Once you have clicked ‘OK’, if you want you can change the ‘Custom Name’ field to something you like – do note that you cannot use an already existing column name. I have changed the name to ‘Salary Sum’ and then click on ‘OK’
Pivots Table in Excel

The data will appear as  shown in the image below. You will notice that the cell A4 is displayed as ‘Salary Sum’ and not as ‘Sum of Salary’. Also, there is a $ (dollar) symbol for salary figures.

Pivots Table in Excel

Slicer

The slicer feature in Microsoft Excel provides visual filters or interactive buttons that display the items that have been chosen within a Pivot Table. In this section, we will check the way by which you can apply filters in a pivot with the help of Excel’s Slicer feature –

  • Click anywhere on the Pivot.
  • Now go to ‘Analyze’ conceptual bar in the toolbar.
  • Now click on ‘Insert Slicer’.
Pivots Table in Excel
  • A prompt will appear asking for which columns you want to insert the slicer for – select all the column and click on ‘OK’
Pivots Table in Excel
  • Here, we have inserted Slicer for 3 columns, ‘Portfolios worked on’, ‘Gender’ and ‘Region’ – as you keep on selecting items in any of these slicers, your Pivot table will keep on getting updated based on these filter values.
  • To select multiple values together, click on the ‘Multi-Select’ option, and then you can select multiple values together.
Pivots Table in Excel
  • To remove filters, click on the icon on the top right and all the filters will be removed
Pivots Table in Excel

Calculated Fields in the Pivot Table

To add calculated fields to the pivot, you will need to –

  1. Click on the Pivot Table
  2. Click on the ‘Analyze’ bar appearing in the toolbar.
Pivots Table in Excel
  • In the ‘Calculations’ section, click on ‘Fields, Items & Sets’ and then click on ‘Calculated Field’
Pivots Table in Excel
  • On clicking on ‘Calculated Field’, a pop-up will appear as shown below –
Pivots Table in Excel
  • Now let’s suppose I want the average amount of salary being given to an age group based on successful cases completed by the employees in that age group –
  • We will click on ‘Calculated Field’
  • In the ‘Name’ section, we will mention the name as ‘Avg Commission per Case’
  • In the Fields section, because we want to find the average salary per successful case, we will first click on ‘Salary’ field and click on ‘Insert Field’
Pivots Table in Excel
  • Then in the Formula section, we will manually add a slash used for division ‘/’
Pivots Table in Excel
  • And then click on ‘Successful Cases’ and click on ‘Insert Field’ and then click on ‘Add’ and then ‘OK’

A new variable will be created in the Pivot table showing the average commission per successful case – you can then change the format or the name of this field as shown in earlier sections

If you want to add a new calculated field, you can go to the sheet which consists of data and add a new column there using formulas.

If you add a new column after the last column, then to ensure that the new column appears in the pivot table, you will –

  • Click anywhere on the pivot table
  • Go to ‘Analyze’ tab
  • Click on ‘Change Data Source’ – clicking on it, excel will take you to the sheet which consists of the data
  • Update the range and click on OK
  • And then click on ‘Refresh’ next to ‘Change Data Source’ option and the new column will start appearing in the ‘PivotTable Fields’

If you add a new column between existing columns, you will just need to click on ‘Refresh’ and the new column will start appearing in the ‘PivotTable Fields’