Loading...

Follow Excel Solutions - Basic and Advanced on Feedspot

Continue with Google
Continue with Facebook
Or

Valid


Hi Friends,

In this article I'll explain how to use FIND function in excel, although we do not use this function in basic excel function but it is very useful in Excel VBA or Excel Macro.

So let us learn it first. As the name of this function suggest it is used to find some text or string within a cell. If FIND function is used nested with IF function then it can help you a lot.

String means a  text, in programming language we use string instead of text, those who are from computer programming background will understand the logic, for now please note String means Text.

Please note that this function is case sensitive. You must type the proper case while searching a text using FIND function.

Description

The Microsoft Excel FIND function returns the location of a substring in a string. The search is case-sensitive.

The FIND function is a built-in function in Excel that is categorized as a String/Text Function The FIND function can be entered as part of a formula in a cell of a worksheet and we will use this property with IF function nested.

Syntax

The syntax for the FIND function in Microsoft Excel is very simple.

=FIND( substring, string, start_position)

Parameters or Arguments

Substring (part of text)

The substring or part of text that you want to find.

String

The string or text to search within.

start_position

Optional. It is the position in string where the search will start. The first position is 1.

Returns

The FIND function returns a numeric value, the first position in the string is 1.

If the FIND function does not find a match, it will return a #VALUE! error.

Examples :

Let's look at some Excel FIND function examples and explore how to use the FIND function as a worksheet function in Microsoft Excel:

FIND Function in Microsoft Excel

Based on the Excel spreadsheet above, the following FIND examples would return:

Case 1 : Finding a particular text/string
            =FIND("Basic",B2,1) in 2nd row or =FIND("blog",B3,1) in 3rd row


FIND Function in Microsoft Excel


Case 2 : Case sensitivity
            I've got an error #VALUE! -  because I am trying to find 'Fox' in the given text where it is typed as 'fox' and therefore I got an error though the formula is perfect.
            =FIND("Fox",B4,1)


FIND Function in Microsoft Excel

Case 3 : This example is little bit confusing because you can notice that I am finding for 'w' and the result is 5 i.e., 5th position is the first occurrence of 'w', apparently it is wrong. The output is correct.

Here what I am trying to find is the position of 'w' after the first three 'w's. The formula in the cell I typed is =FIND("w",B5,4) meaning that find me the position of 'w' after the fourth character of the provided string. The below image will clarify it.

FIND Function in Microsoft Excel

           
Hope you have enjoyed this article.

Thank you for reading...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Hi Friends,

Today I will discuss about AUTOFILL options in Microsoft Excel. Everybody want a shortcuts in Excel and autofill is such a tool in Excel which surprisingly reduces the manual work that has to be done.
What is AUTOFILL ?
AutoFill is a very useful Excel feature. It allows you to create entire columns or rows of data which are based on the values from other cells. In other words, Excel compares the selected data and tries to guess the next values that will be inserted.

Excel is becoming very intelligent day by day and it will be proved in my next articles.

Actually Excel tries to guess about the work we are doing and tries to help us for example, if you need ten months in words in consecutive ten cells let suppose you type 'January' in first cell and then if you drag the fill handler upto 9 cells the next months like February, 'March' etc will automatically be displayed. Same for numbers also. But there are difficulties. Let us understand it by few examples with images.

 To use the Simple Excel Autofill 

          1.    Excel Cell Fill Handle and Autofill Options Box
          
          2.    Enter a value into the start cell;
          
          3.    Use the mouse to drag the 'fill handle' (the small black square at the bottom right of the start cell) across the range of cells to be filled;
    
         4.    When you drag the 'fill handle' across the range of cells to be filled, Excel will fill the selected cells, by either repeating the value in the first cell or by inserting a sequence from the first cell value (e.g. 1, 2, 3, ...);

        5.    Click on the 'Auto Fill Options' box, which will appear at the end of your selected range of cells. This will give you the following different options:

Simple Excel Autofill


Copy Cells - copy the initial cell across the selected range;

Fill Series - fill the selected range with a series of values (typically increment by 1), starting with the initial cell value;

Fill Formatting Only - fill the selected range with the formatting, but not the values of the initial cell;

Fill Without Formatting- fill the selected range with values, but do not copy the formatting from the initial cell.
Select the option that you required for the filled cells.

Autofill Using More Than One Starting Cell Value

Excel Auto Fill Sequence

If you want Excel to recognize a series that is not a simple increment by 1, this can be done by typing the first two values of your series into the first and second cells of a range. Select both of these cells and again, drag the fill handle across the range to be filled. Excel will automatically recognize the pattern from the two initial cells and continue this across the selected range. Using this method, you can get Excel to fill cells by increments or decrements of any number (e.g. 2, 4, 6, 8, ...).

Autofill Using More Than One Starting Cell Value


Alternatively, if you want Excel to fill cells with repeated alternating values (e.g. 1, 2, 1, 2, 1, 2, ...) you can start off the pattern in the first two (or more) cells, then, with the initial cells highlighted, drag the fill handle and then click on the 'Auto Fill Options' box. Within this box, select the option 'Copy Cells' to repeat the initial cell values across the selected range.

Autofill Dates & Times

Excel Autofill Dates

As dates and times are stored in Excel as numbers, these can also be used with the Excel Autofill.

By default, if you just type in a single date or time and drag the fill handle, dates and times will complete in a series, by adding one day (for dates), or one hour (for times). However, as with simple numbers, you have the option of clicking on the 'Auto Fill Options' box, to select a different type of Auto fill.

Times have the same four Auto fill options as are shown above, for simple numbers (i.e. Copy Cells, Fill Series,Fill Formatting Only, Fill Without Formatting). However, for dates, there are additional Auto Fill options. As well as the four options for simple numbers and times, there are also the following:

Fill Days - Look for a pattern in the day when filling the selected cells;
Fill Weekdays - Look for a pattern in the day when filling the selected cells, but do not include Saturdays or Sundays in the series;
Fill Months - Look for a pattern in the month when filling the selected cells;
Fill Years - Look for a pattern in the year when filling the selected cells.

Autofill Dates & Times


Autofill Functions and Formulas 
The Excel Autofill feature also works with functions and formulas in Excel which is very useful. However, with this type of Autofill, there is no 'series fill' option. Instead, Excel observes the rules of Absolute and Relative Cell References (i.e. if a row or column reference is preceded by a $ sign, excel will keep the reference constant as the formula is copied to other cells; Otherwise, the row or column reference will be adjusted as the formula is copied to other cells.

Horizontal and Vertical Autofill

Excel Autofill Across a Row
As well as working down a column, the Autofill feature also works horizontally, across rows.

Simply drag the fill handle across the cells that you want to populate.

Autofill Multiple Rows or Columns Simultaneously

The Excel Autofill can also handle data in more than one row or column. This is shown in the example below, in which cells A1 and A2 have numeric values 1 and 2, and cells B1 and B2 both have the numeric value 3.

Highlighting cells A1 to B2, and then dragging the fill handle down columns A and B causes the Autofill to complete both columns with their own series (i.e. column A completes with 1, 2, 3, 4, ..., and column B completes with 3, 3, 3, 3, ...)

Autofill Multiple Rows or Columns Simultaneously


Double Click on the Fill Handle

For speed, you can Autofill a column by double-clicking on the fill handle of a highlighted cell (or range of cells). If the cells below or adjacent to the highlighted cell (or range) contain values, double clicking the fill handle causes the Autofill to fill down the current column until it reaches the end of the current data range.

In my next article I will show you how to enter customize list to get these in autofill options.

Thank you for reading ...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

In my last article, I discussed about the function COLUMN & COLUMNS and the use of these functions with VLOOKUP, nested.

In this article, I will show you another function ROW which is also is a useful function in Microsoft Excel if used wisely.

ROW() works same as the COLUMN function, the only difference is it gives you the number of the row where this function is written. 

Let us first describe the function and then we will see how can we use this function in Excel.

Description

The Microsoft Excel ROW function returns the row number of a cell reference.

The ROW function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax
The syntax for the ROW function in Microsoft Excel is as follow.

=ROW( [reference] )

Parameters or Arguments

Reference
Optional. It is a reference to a cell or range of cells.
Returns

The ROW function returns a numeric value.

ROW Function without Reference


It’s a simple function to remember and to use. The very basic question which may arise in your mind is where to use it. I use ROW function to make HLOOKUP dynamic. Why HLOOKUP ?

Because HLOOKUP is such a function which looks up for data row wise, and like VLOOKUP where it asks for COLUMN number, HLOOKUP asks for ROW number. We must enter the ROW number to get the desired data using HLOOKUP.

The trick is to use ROW function in HLOOKUP’s row_index_number argument or parameter.

Lets take one example of it.

Suppose we have a table arranged in the following way. We should get the data row wise using HLOOKUP. The syntax of the HLOOKUP function is as follows.
=HLOOKUP(lookup_value, table-array, row_index_number, [range_lookup])

Row function in Microsoft Excel


Now instead of writing the row number in row_index_number, if we write ROW() and if we show this function the reference, it will not be necessary to add the row number manually. Because the row number now will be supplied by the ROW function itself.

Please note that while entering the function ROW within HLOOKUP function you have to give the cell's address in the ROW function.

=HLOOKUP(E15,E2:H5,ROW(E2),0)

Please refer to the below image. The working of ROW function is same as the working of COLUMN function.

ROW function in HLOOKUP function



Hope you have enjoyed this article.
Thanks for reading... 
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Friends,

In my last article I discussed about COLUMN function and I've shown you how to use it with VLOOKUPfunction to make VLOOKUP function more dynamic.

But COLUMN function is good for single column matching, what we will do when there are multiple columns to be matched easily ?

In the function COLUMN we give the reference cell's address and we get the number from the first column (i.e., column 'A') and we use this property of COLUMN function in the VLOOKUP function in column index number to get a dynamic VLOOKUP formula.

But sometimes we face situations where we have to put the column index number in VLOOKUP function not from the first column in the Excel sheet but from column other than 'A' column or first column (e.g., from column 'C4' to 'F4'). In this situation we can use COLUMN function but a little tweak is require (which I'll show in other article). Here in this article we will use another function which will serve the same purpose.

Microsoft Excel provides a function, almost same as COLUMN function that can resolve this problem, the function is COLUMNS. Note, it is COLUMNS, an extra 's' is added at the end which suggest that it can be used to count the number of columns more than one from any starting column.

We need to pass an ARRAY into COLUMNS function which will return the number of columns that is included in the array. Please don't be anxious about ARRAY, it is very easy besides if you don't know about ARRAY you can still use this function.

It is also one of the easiest functions in Microsoft Excel. Lets' first understand this function then we will use it in VLOOKUP function to make VLOOKUP function more dynamic.

Description

The Microsoft Excel COLUMNS function returns the number of columns in a cell reference.

The COLUMNS function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax

The syntax for the COLUMNS function in Microsoft Excel is:

=COLUMNS(array)

Returns

The COLUMNS function returns a numeric value.

Example of COLUMNS function

Write "=COLUMNS(D2:F2)" in G2 cell and it will return the number of columns from 'D' to 'F' i.e., 3 in this case.

How to use COLUMNS function in Excel


Now we'll use this numeric value in column index number portion of VLOOKUP function.

How use COLUMNS function in VLOOKUP function

Normally VLOOKUP function looks like =VLOOKUP(J2,B1:F10,5,0). Here 5 is the column index number, we'll replace this by COLUMNS(B2:F2). Hence the formula now looks =VLOOKUP(J2,B1:F10,COLUMNS(B2:F2),0)

COLUMNS function in VLOOKUP function


To make VLOOKUP more dynamic we have used COLUMNS function, but what about next cells, if we assume that there are more than one columns to be mapped using VLOOKUP.

Its' very simple, we'll use $ sign to restrict the incremental property of Excel formula and the we'll just drag it in a row.

The formula now becomes  : =VLOOKUP($J2,$B1:$H10,COLUMNS($B2:F2),0)

Here we have restrict the first cell so that it does not get incremented but we have left the second potion to get incremented while dragged.
= COLUMNS($B2:F2)

Please have a careful look at the below image and you will be able to understand it and this will make VLOOKUP more dynamic.

COLUMNS function in VLOOKUP function


Hope you have enjoyed this article.
Thank you for reading...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Friends,


today I am going to discuss about  a new function in Microsoft Excel, if used with another function like VLOOKUP will prove to be a very useful function and it will make your formula very dynamic.
The function is COLUMN, as the name of this function suggest it gives us the information about column. 

In this article I'll show you how to use this function with VLOOKUP and will make our basic VLOOKUP function very dynamic and will shorten the time to write multiple VLOOKUPs.

Let us first know about this function. Its' very easy and basic function to remember and it comes under LOOKUP functions in Excel.

Description
The Microsoft Excel COLUMN function returns the column number of a cell reference.

The COLUMN function is a built-in function in Excel that is categorized as a Lookup/Reference Function.

Syntax

The syntax for the COLUMN function in Microsoft Excel is:
=COLUMN( [reference] )

Parameters or Arguments

reference
It is optional. It is a reference to a cell or range of cells. If the reference parameter is omitted, it assumes that the reference is the cell address in which the COLUMN function has been entered in.

Output or Returns

The COLUMN function returns a numeric value like 2,3,4 etc.


Example of Column Function

Write "=COLUMN()" in any cell and it will return the column number of that particular column where the function is written if there is a blank within the bracket.


Column Function without Reference


Its' very easy... Now let me write another example ...

Write "=COLUMN()" in any cell and select or type any cell reference within the bracket e.g., "=COLUMN(D3)".
It will also return the column number of that particular column. The benefit is you can get the column number of the referred cell.


Column Function with Reference


Now we will use this property of COLUMN function in VLOOKUP. You remember in VLOOKUP functions I wrote the syntax as VLOOKUP( value, table array, col_index, [range_lookup] ).

col_indexis the column number, so if instead of writing column number I write column and then the put the reference of that column within this function then the result will be same. For eample the formula =VLOOKUP($U2,$B:$C,COLUMN(B2),0)
(Here $ sign is to fridge the cell, as you know).


VLOOKUP function in one Cell using COLUMN function



What is the benefit we get using this function ?or
How to use VLOOKUP function with COLUMN function ?

To understand it lets' take an example, suppose if we have four consecutive columns that we have to fill with data collected from VLOOKUP then we have to write the column numbers four times even if we have dragged the VLOOKUP formula to the consecutive cells. But we can reduce our time by dragging one VLOOKUP formula instead of writing column numbers in each and every VLOOKUP formula if we use COLUMN function within VLOOKUP function with reference cell within the column function, an example of Nested function. But do not forget to increase the range of table array otherwise it will display #REF! error, e.g.,

 from    =VLOOKUP($U2,$B:$C,COLUMN(B2),0)
to       =VLOOKUP($U2,$B:$G,COLUMN(B2),0)

The below Image will make it more clear.

VLOOKUP function in multiple Cells using COLUMN function


Hope you like this article, thank you for reading...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Friends,


Now I hope you have noticed that there is another option in Formula tab under Option or Analyze menu as Calculated Item. Today I am going to discuss with you about Calculated Item and the difference between Calculated Field and Calculated Item.

Let take an example, in the below Pivot Table I need to add Saturday and Sunday as one day and then this value should come combined with the existing data, i.e., data related to Saturday and Sunday will come as it was, additionally a new item will appear as Sat+Sun with their total value. Here we can use Calculated Item option.


Its' very simple to use, you need to know where & how to use it otherwise you will find Calculated Item as Grayed colored inactive option.

Where to Use Calculated Item ?

Calculated Item is used in situations where we need another Item in the Row Label item field, i.e., as another item in the same column. The below picture will clear it.

How to Use Calculated Item ?

Place your cursor in any cell within Pivot Table but remember the column should be in Row Labels box, i.e., select any cell in the column where you want to add the new Item.

Calculated Item


Now go to Options click Formulas, from the drop down menu select or click Calculated Item.


A new window will appear as Insert Calculated Item in "***". Give a name to the new Item (in my case it is Sat+Sun). From the Fields section click on the Column Name and you will notice the Items are listed in the right side under Items head. In the formula box enter your formula (in my example it is =Sunday+Saturday). Click OK and you are done, you can now see a new Item in the Pivot Table.

Insert Calculated Item Input Box


That's it, the main difference between Calculated Field and Calculated Item is the first option will create an entire Field and the Calculated Item will create a Item in the Pivot Table.

Pivot Table Before entering Calculated Item


Pivot Table After entering Calculated Item


Hope you have understand the difference between the Calculated Field and Calculated Item and now you can easily use Calculated Field and Calculated Item.


Thanks for reading...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Friends,

In the last two articles we have learned about how to create or insert a PivotTable and few Customization in Microsoft Excel. In this article we will discuss about Calculated Fields in Pivot Table.

But before that let us first know ...
Why should we use Calculated Fields and in which circumstances we should use this technique.

Let me ask you a question ?

Have you ever try to sum two columns in a Pivot Table ?

Let me clear it, if you want to sum first two columns using SUM function or using plus sign like sales value of product 1 & product 2 in the last blank column what will happen ?

You will find a long formula starting GETPIVOTDATA and six parameters (!!!), total value will be calculated for one cell. Okay, now if you drag it to sum the entire data range (that we normally do), you will get zeros all the cells except the first cell. This is because when you try to sum two cells from Pivot Table by default Excel uses the GETPIVOTDATA function with static cell ranges. This is the reason not to get the sum in all cells.

Now in this situation we have one option left i.e., sum by reference (i.e., mentioning that 'sum C2 cell with D2 cell' for example), but here is also we might have problems because in future if you want to change the Pivot Table structure there will be either a blank column between the Pivot Table and the newly created column outside the Pivot Table or Pivot Table will ask you to overlap the column which you just created.
But think if we have some technique to sum these two columns within the Pivot Table as individual column then these problems will vanish and additionally you will get an opportunity to add the new column whenever you need in the Pivot Table.

Yes... there is a process to do this in Microsoft Excel PivotTable, which is known as Calculated Field.

Calculatedbecause you put your own formula to calculate from the Pivot Table column(s).

Fieldbecause your newly created column will be listed in Pivot Table as a new Column or Field.

How to Create Calculated Field in Pivot Table

       1.   Select any cell within Pivot Table, you will see two additional Tabs Options and Design

       2.   Click on Option Tab

       3.   Under Tools submenu, click Formulas beside PivotChart

       4.   Click Calculated Field

Calculated Field Options Tab Then Formulas

       5.   A new small window will appear, "Insert Calculated Field"

a)  In the first input box labeled as Name type a name for your Field, for example, 'Sum Of Three Products'

b)  Go to next input box either by clicking in the box or you can hit TAB button

c)   Here you will put formula to be calculated, e.g., I need sum of three columns so I typed SUM after equal sign and selected the fields separated by commas, you can use plus sign without Typing SUM.

d)  To select the fields to be added you need to select it, and either you can double click or can click on the Insert Field Button, notice a single opening and closing quote is automatically printed in the formula box for each item.

e)   Click OK and you are done, you can now see a new field in the Pivot Table and you can also find it within the PivotTable Field List Box.

Insert Calculated Field Window

Create Calculated Field Window - Using Formula

New Field Added as a Column within Pivot Table

New Field Added as a Column within Pivot Table


This is how to create Calculated Field in Pivot Table. Now I will show you...

How to Use Logical Function IF in Calculated Field in Pivot Table

To use logical IF function within Pivot Table as Calculated Field the steps are same as mentioned earlier, remember if you use IF function in Calculated Field it only displays the numbers after calculation, no Texts are allowed, if you try to evaluate Text based upon the result of IF it will give you #VALUE error.

Please watch carefully the following picture you will understand how to use IF in Calculated Fields.

Using IF function in Pivot Table Calculated Field

Using IF function in Pivot Table Calculated Field
Remove or Delete a Calculated Field from PivotTable

It is very simple.
From Options tab click Formulas to open the Insert Calculated Field window.
From Name drop down list select the Field you want to Delete, click Delete button below Modify button, click OK and done.

Remove or Delete a Calculated Field from PivotTable

Modify a Calculated Field from PivotTable

If you need to rectify formula or the field name, its' better to do it through Modify button.

From Options tab click Formulas to open the Insert Calculated Field window.

From Name drop down list select the Field you want to Modify. Make necessary changes in the Name or Formula and then click Modify button and click OK and you are done.

Modify a Calculated Field from PivotTable

Calculated Field in Pivot Table

Few Things About Calculated Fields in PivotTable

Limitations

       1.   Calculated field formulas cannot refer to the pivot table totals or subtotals

       2.   Calculated field formulas cannot refer to worksheet cells by address or by name.

       3.   Sum is the only function available for a calculated field.

       4.   Calculated fields are not available in an OLAP-based pivot table. (OLAP - Online Analytical Processing, we'll discuss in another article).

Features

       1.   For calculated fields, the individual amounts in the other fields are summed, and then the calculation is performed on the total amount.



       2.   Calculated fields are automatically available in all pivot tables that are based on the same pivot cache

That's all friends for today. Hope the topic discussed today is easily understandable. Keep reading & keep asking & commenting...

Thank you ...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Friends,

In the last article we have learned about how to create or insert a Pivot Table in Microsoft Excel but that was the basics of Pivot Table, now in this article we will discuss more about the Pivot Table customization.

Pivot Table summarize large data, but for analytical purpose it needs few modifications to make pivot table report more attractive and beautiful. One thing I would like to share with you which I have experienced in my carrier that any report you prepare (summary is also a report) in Microsoft Excel or other Spreadsheet application or any other application, one of the important things are the beautification of the report and the presentation of the report, should be easily understandable and the data provided in the report should be accurate in an organized way.

The tool, Pivot Table is equipped with all these properties and more. You will find this tool as very easy and useful tool and you will be surprised how important this tool is !!!

So, lets' move to the discussion.

Basic Excel Pivot Table


The Pivot Table we have created in the last article (above image), having 

     1. 'Total' column wise and row wise but row wise total is at the top of the Table which we are not accustomed. Moreover, 

     2. Two column headers in the original data are coming in one column in Pivot Table

     3. The Column Header is replaced by Row Labels

These three things needs to be customized, lets' Customize Excel Pivot Table step by step.

          a) Select in cell in Pivot Table, after clicking two more Tabs' will appear, Options Tab & Design Tab

          b) In Design Tab under Layout menu Click Report  Layout button

          c) Click 'Show in Outline Form', you will notice Row Labels column header has split into two column header which was present in the original back-end data, Zone & Day for our example.
The Pivot Table created by default takes the report layout in Compact form, by clicking 'Show in Outline Form' you just changed the layout to Outline form.


Customize Basic Excel Pivot Table Outline Form

          d) Now we need the Total Column in our Pivot Table from Top position to Bottom Position, to do this select any cell in the Pivot Table, 
                      i. Click in Options Tab
                      ii. In the PivotTable category click on Options button.
                      iii. A new window will appear PivotTable Options as shown in the below image. 

Customize - PivotTable Options Window - Classic PivotTable Layout

                      iv. Click on Display Tab
                      v. Click to give a Tick mark in the Classic PivotTable Layout Checkbox.
                      vi. Click Ok and done.
                      vii. Alternatively, you can right click in the PivotTable and select Pivot Table Options..., Pivot Table Options window will appear then follow the above steps and Done. 

Now you can see Row Totals are showing one after another block and it is below the data.

PivotTable Options On Right Click of Mouse


Formatting Pivot Table
Now we will again customize PivotTable to make it more beautiful by applying color and border step by step i.e., formatting Pivot Table. 

If you try to format Pivot Table Like any other table formatting by color and border, it will not be permanent, whenever your data changes these formatting will be erased, therefore, you need some technique that will make PivotTable formatting dynamic which will not change if the data changes. 

There are various methods to format Pivot Table so that it looks beautiful by implementing Dynamic methods to format Pivot Table.

Method 1

             1. Select any cell in Pivot Table
             2. Click Design Tab
             3. In the Pivot Table Styles Tab you will find various styles, select any one from these styles. If you hover over these styles you can see the preview in the Pivot Table.

Pivot Table Styles to Make Pivot Table Beautiful

Pivot Table Styles to Make Pivot Table Beautiful

Method 2

             1. Move the cursor to any row total, it can be easily identified because this cell contains the word Total. A small black arrow will point to the right of this row, click here, you can notice the whole Row Total is selected with the other row Totals.

             2. Now in the Home Tab in Font group select Font Color and Fill color, these rows reflect the color you choose.

             3. We need border in the Pivot Table, therefore, click the drop down arrow in Border Icon and

             4. Select any Borders as per your choice. The selected Rows will be bordered.

             5. Now hover over the Data part, in my example it is the date. A small black down arrow will appear, click here to select the whole column, if you want to select multiple column you can do it by dragging it to the last column.
             
             6. Select border as per your choice. And you are done.

Pivot Table Font & Fore Color - Your Own Customized Color
Fill Blank cells With Zero in Pivot Table

How to remove blank cells in Pivot Table ? 

Often I get this question from my colleagues and friends and students. This appears to be a difficult task to everybody - to fill the blank cells with Zero in Excel Pivot Table. Difficult, because you will not be able to edit Pivot Table cells. To do this you need to follow the following steps.

             1. Open Pivot Table Options window either by right clicking in the Pivot Table or from Options Tab to Options button.

             2. In the Layout & Format Tab you will find For Empty Cells Show option in checkbox in the Format section. 

             3. Type '0' in the text box provided as shown in the below image. 

             4. Click OK and you are done, all blank cells will now show zero.

Fill Blank Cells With Zero in PivotTable

Dynamically Colored Customized PivotTable

That's all friends for today. In my next article I will show you some advanced setting in Pivot Table including how to use Excel formula and functions within the Pivot Table. Enjoy reading...
Thank you ...

Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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...
Read Full Article
Visit website
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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 then2.   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...
Read Full Article
Visit website

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free year
Free Preview