Pivot Table in Microsoft Excel - Pivot Table Field List Report Functions of Filter Column Labels Row Labels Values

Friends,

So far we have discussed about various Excel functions for example VLOOKUP, HLOOKUP, IF, SUMIF, COUNTIF etc, we discussed about conditional formatting, how to find duplicate or unique values, we used tools like filter & sort etc., and  now in this article we will discuss about PIVOT TABLE - which is a very easy and excellent tool provided by Microsoft Excel to summarize a very large data. 

There is lot of things in Pivot Table which can help you in many ways to prepare your data for analysis. This tool in Excel has many customization options to facilitate the data analysis. Proper use of Pivot Table in Excel can reduce the time taken to prepare a report to view or to analyze.

Pivot table is a tool which can summarize large data. We use Pivot Table in Excel to get customized summary from a large or bulky data. Pivot table summaries data by combining the duplicate figures into one unique figure or data. Pivot Table appears to be a very complicated tool in Excel for some people but it is not that complicated if you understand the functions of its various parts. Creating a Pivot Table is very simple, understand your requirement and plan accordingly before inserting Pivot Table. There are few rules which you have to follow to insert a pivot table, these are listed below.

    1.   You need an organized data, here 'organized' means, there should not be any blank column or rows in a Table of data

    2.   Each Column must have a column header or column name.

    3.   No merged column header should present in the column header or within the data

How to Create or Insert a Pivot Table

1.   Select a cell within your data.

2.   Go to Insert Tab, in Tables sub-menu click PivotTable

Create PivotTable Window

Insert PivotTable Table Option

3.   A small window will appear "Create PivotTable" as shown above, you can notice that there is four radio buttons, of which the first one is 'Select a Table or Range' and this option is automatically detects the data range. Check this data range, if it is not OK select it.

4.   In the next block you can notice there is another two radio buttons below the first one, New Worksheet and Existing Worksheet. Click OK if you want to insert PivotTable in new Excel Sheet in the same Workbook or you may select the next radio button and provide the destination cell in the same Excel Sheet. Click OK, I always use New Worksheet.

5.   Now you will  see a Blank format in the Excel sheet and at the extreme right one small tool window named PivotTable Field List - this is the column selection window and your PivotTable design will depend upon this arrangement and therefore, it is very important. Now please watch carefully the below snapshot.

PivotTable Field List Window

6.   This PivotTable filed chooser window has few parts (bordered by red), four small boxes, you need to understand the function of these boxes. We will start from the data part for the ease of understanding.

            a) Values : In the extreme right and in the bottom of PivotTable Field List window you will find a little box named as Values, here you need to put the data part i.e., data column e.g., Sales Target, or Sales value etc., you can put one or more than one fields or columns by either ticking column headers displayed in 'Choose fields to add to report' or you can Drag & Drop the columns in this box.

            b) Row Labels : In left of the Values box there is another small box named as Row Labels, you need to put that column for which you need the data, e.g., Zone, Employee Name, Employee code etc., remember this is not your data part. You will get summarized data respective to this field, for example, North Zone -> 50 (Target), as shown in the bellow picture.

Basic PivotTable

We have just created a basic Pivot Table in Excel. Now let us understand the functions of another two boxes namely Report Filter & Column Labels

           

            c) Report Filter : Now let suppose in my data I need to derive employee wise performance or Target but one by one Employee Code. Here we can use Report Filter, the box located above the Row Labels box. Just Drag & Drop Employee Code in this box and you are done. You will now see a new entry in the top of the Pivot Table as Employee Code (All), with a drop down list. You can select any item in this drop down list and the related data will be displayed in the Pivot Table hiding all other data.

Please refer to the below image.

Use of Report Filter in Pivot Table

            d) Column Labels : Now let suppose I need the data date wise, date will be column header and the Target value will be date wise & day wise. Here we need to put the date column in Column Labels, as shown in the below picture.

Use of Column Labels in Pivot Table

This is the basic methods to use PivotTable in Microsoft Excel. Hope you have fully understand the functions of four magical boxes in Pivot Table. If you have any problem in this part please post in comment box and in my next article I will show you some amazing techniques in Pivot Table in advanced level, till then keep reading...

Thank you...

How to Find Trace Copy or Count Duplicates in Microsoft Excel Using Formula

Hi Friends,

The beauty of Microsoft Excel is that if you think logically then it is possible in Excel. Today we will discuss about such a topic which will make you realize the beauty of Excel.

In my last article I have discussed about how to identify Duplicate or Unique values in Microsoft Excel using Excel's inbuilt tools like conditional formatting. Hope you have got enough knowledge about Conditional Formatting and about Duplicates in Excel.

Today we will continue discussing about Duplicates in Excel but using Formula. We will discuss about

1.   How to Count Duplicates or Unique values Using Formula and then

2.   We will copy or pick or collect the Duplicate or Unique value using Formula

So lets' start. For easy understanding we'll list it step by step.

Find Duplicate or Unique Using Excel Formula

Please have a look at the above picture where light yellow highlighted colored cells contains Duplicate values which I highlighted using Conditional Formatting. Now we need to fill the next four columns using formulas.

The beauty of Excel is that you can do any logical things using proper functions and nesting them with each other.

How to Count Duplicates or Unique values Using Formula

1.   We know COUNTIF function counts specific value or character within a specified range. So we can use this function to count how many times one specific number is appearing in a given range.

2.   The syntax of COUNTIF function is COUNTIF(range, criteria), in our example this formula becomes =COUNTIF($C$3:$C$16,C3) as shown in the below image.

3.   The trick is we provide a range (here it is C3:C16) and put dollar sign to fridge it by pressing F4 button and then we provide the number we are looking for Duplicates (here it is "C3").

4.   If the output of COUNTIF function is more than one then this number has duplicates in the given range else no duplicate value is present.

Find & Count Duplicate or Unique Using Excel Formula

5.   Now if you want to write the word 'Duplicate' against duplicate and 'Unique' against unique value in a data range, we just need to update the formula using IF function as shown in the below picture.

the formula is : =IF(COUNTIF($C$3:$C$16,C3)>1,"Duplicate","Unique")

Identify Duplicate or Unique Using Excel Formula

So this is the trick to find the duplicate values using formula, nesting of IF and COUNTIF function now we will move to the second part of this article.

How to Copy or Pick or Collect the Duplicate or Unique values using Formula

This part is also same as the above method or trick, what we need to change is that we need to make a nested formula using IF & COUNTIF function as above example.

Now the tweak is put a conditional checking part to judge whether the count of number is more than one or not. If the count of the provided number is more than one, then we need to print this number if we need duplicate value by referring the cell (C3 for example in the formula =IF(COUNTIF($C$3:$C$16,C3)>1,C3,"") in the value if true part of IF function and one pair of double quotes to print nothing if this number is unique. Please refer to the below image for further clarification.

Identify and Copy Duplicate or Unique Values Using Excel Formula

The reverse is the case if you want to get the data for Unique value.

I hope you have enjoyed this article and realize the beauty of  Nesting in excel.

Identify and Copy Duplicate or Unique Values Using Excel Formula

Keep reading for new ideas in Excel...

Thank you for reading...

How To Remove Duplicate Values In Excel - Identify and Highlight Duplicate Values

Hi Friends,

In this article  we'll discuss about how to find Duplicates or Unique values in Microsoft Excel. In Microsoft Excel duplicate values may come anywhere in a column or in a row and sometimes it is the situation's demand to highlight or find out these duplicate values or to remove or count these duplicate values.

A duplicate value is one where all values in the row are an exact match of all the values in another row. Duplicate values are determined by the value displayed in the cell and not necessarily the value stored in the cell.


So, our requirements can be listed as follows

1. Filter or Identify or Highlight Duplicate or Unique values

2. Remove or Delete Duplicate values

3. Remove or Delete Duplicate rows or columns

4. Count Duplicates or Unique values

The easiest way to identify and highlight the duplicate values is to click on Conditional Formatting button in the Styles group in Home tab. Then point to the first item in the drop down list named as Highlight Cells Rules and this will expand and at the bottom of this expanded list you will find Duplicate Values option Click on it as shown in the below images. 

Highlight Duplicate Values

Highlight Duplicate Values

A new window will appear as Duplicate Values as shown in the above image, select duplicate or unique as per your requirement from the drop down list. You can also select the cell color or the font color from the drop down list or you can customize the highlight color of this list. 

Now your data will highlight the duplicate or unique values as shown in the below image.

Highlight Duplicate Values

We have successfully identified and highlighted the duplicate values, you also can identify or highlight the unique values by the same techniques, only you need to change the duplicate option to unique in the drop down list.

Remove Duplicate Values or Filter Unique Values

In Excel, you have several ways to filter for unique values or remove duplicate values:

• To filter for unique values, use the Advanced command in the Sort & Filter group on the Data tab.

• To remove duplicate values, use the Remove Duplicates command in the Data Tools group on the Data tab.

• To highlight unique or duplicate values, use the Conditional 
Formatting command in the Style group on the Home tab as stated above.

Filtering Unique Values or Removing Duplicate Values

Filtering unique values and removing duplicate values are two closely related tasks because the displayed results are the same, a list of unique values. The difference, however, is when you filter for unique values, you temporarily hide duplicate values, but when you remove duplicate values, you permanently delete duplicate values.

Therefore, it's a good idea to filter for or conditionally format unique values first to confirm that the results are what you want before removing duplicate values.

Filter for Unique Values

1. Select the range of cells, or make sure the active cell is in a table.

2. On the Data tab, in the Sort & Filter group, click Advanced.

Advanced Option to find Duplicate Values

3. In the Advanced Filter dialog box, do one of the following:

    a. To filter the range of cells or table in place, click Filter the list, in-place.

    b. To copy the results of the filter to another location, do the following:

          i. Click Copy to another location.

          ii. In the Copy to box, enter a cell reference.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select a cell on the worksheet, and then press Expand Dialog.

4. Select the Unique records only check box, and click OK.

The unique values from the selected range are copied to the new location.

Remove duplicate values

When you remove duplicate values, only the values in the range of cells or table are affected. Any other values outside the range of cells or table are not altered or moved.

Because you are permanently deleting data, please make a copy the original range of cells or table to another worksheet or workbook before removing duplicate values so that if you find something wring, you can rollback the data.

1. Select the range of cells, or make sure that the active cell is in a table.

2. On the Data tab, in the Data Tools group, click Remove Duplicates.

Remove Duplicates

3. Follow the below procedures:

• Under Columns, select one or more columns.

• To quickly select all columns, click Select All.

• To quickly clear all columns, click Unselect All.

• If the range of cells or table contains many columns and you want to only select a few columns, you may find it easier to click Unselect All, and then under Columns, select those columns.

4. Click OK.

A message is displayed indicating how many duplicate values were removed and how many unique values remain, or if no duplicate values were removed as shown in the below picture.

After Removal of Duplicate Values

5. Click OK and you are done.

Please Note: You cannot conditionally format fields in the Values area of a Pivot Table report by unique or duplicate values.

Thank you for reading this article...

Conditional Formatting in Excel Formula, Icon Sets, Find Duplicate or Unique value and Edit or Modify or Remove Conditional Formatting

Hi friends,

After discussing about basic Conditional formatting I think we should learn few more Conditional formatting features. Formula driven Conditional formatting is one of the awesome features in Microsoft Excel.

In today's article we will learn about the advanced uses of Conditional formatting with few functions and will use the Icon Set like, Arrows, Flags, Street Light symbols, colored circles etc.

Conditional Formatting Icon Sets

Using Formula in Microsoft Excel Conditional formatting

Using Formula in Microsoft Excel Conditional formatting is very simple click Format only cells that contain' in New formatting rule window and type your formula in the Edit the Rule Description box as shown in the below picture. You can use any formula in this box as per your requirement.

Conditional Formatting Using Formula To Decide Cell Fore Color and Font Color

How to apply Conditional formatting in all cells

To apply Conditional formatting in all the cells in Excel you need to select the cell where you have applied the Conditional formatting and then click on the Format Painter button in the Home Tab as shown in the below picture and apply it to the require cells.

Format Painter

In Microsoft Excel you will find few Icon Sets under Conditional Formatting > Icon Sets as shown below. If you select a cell containing data and hover over these icons you will find these icons are displaying in the selected cell. Let us understand the implementation of Icon Sets.

Conditional Formatting Icon Sets

Use of colored Arrows from Icon Sets in Conditional formatting

To use the Icon Sets select any one cell in your data table where you need these icons. Now there are two ways to use Icon Sets in Excel.

    1.  Go to Conditional formatting > New Rule, New Formatting Rule Window will be displayed or

    2.  Click Conditional formatting > Icon Sets, a icon will be displayed in the selected cell, now go to Conditional formatting > Manage Rules (At the bottom of the drop down list, I prefer to use this method)

Now, if you use the First method then Steps are,

    1.  Select 'Format all cells based on their values'

    2.  You will find one drop down list Format Style, as shown in the below image. Here you will find few options namely

a.  2-color scale

b.  3-color scale

c.  Data bar

d.  Icon Sets

    
       3.  Select Icon Sets and at the bottom of the window you will find another Drop down list Icon Style, select your icon as per your requirement.

Now, if you use the Second method then Steps are,

1)  go to Manage Rules from Conditional formatting button in the Home tab

2) Conditional formatting rules manager will appear, click on Edit Rules button

3) Now in the Edit the Rule Description head put your logic as per your requirement and Click Ok, done. Please follow the below images.

Conditional Manager Rules Manager

Conditional Formatting Edit Formatting Rules

If you practice it few times you will be able to master it very easily, if you need video tutorial, please click here to view this in YouTube.

How to delete Conditional formatting

Follow the steps :

   a)  Go to Conditional Formatting > Clear Rules

a) Click on Clear Rules from the selected cells if you want to delete conditional formatting from specific cells or

b) Click on Clear Rules from Entire Sheets if you want to clear it from the entire sheet.

Conditional Formatting New Formatting Rules

How to Find Unique or Duplicate in Excel

Conditional formatting is a very good tool to find out the unique or duplicate values in Excel Columns.

As show in the below picture, go to Conditional formatting > New Rule, click 'Format only unique or duplicate values'.

In the Edit the Rule Description section select unique or duplicate as per your requirement, format the cell color or font by clicking Format button. Click OK and you are done.

Thank you for reading... There are more advanced methods in Conditional formatting which I'll discuss latter in another article... Keep reading and asking questions...