Sorting Pivot Table by Last Column in Row Field (Row Label)

In a Pivot Table, if there are 5 or more than 5 columns as Row fields (Row label) and if we want to sort entire pivot table by values available in column 5 then it is not possible to sort Column 5 values only using Sort options (Except manually dragging rows in a pivot table) as data is grouped based on previous columns and it will be sorted within groups of previous columns. Sorting in Pivot is based on primary(First) column and then subsequent columns.

For more Info - http://answers.microsoft.com/en-us/office/forum/office_2007-excel/sorting-in-a-pivot-table/b83ef899-6c49-4a98-afb2-65ac2fc43f97

there is a workaround as below; 

In your source data, Add a new column with same values with column that you want to sort at position 5; refresh current pivot or create a new pivot; create pivot layout as per your requirement; Place newly created column as first column ; do sorting on first column and hide first column of pivot table ; if you have "Report Filters" then report filter headings will also become invisible due to hiding column hence write "report filter " headings in the cells next to the "Report filter" selection drop downs" or Else instead of hiding column just format the first row field in  such a way that one can not see any values there (like applying sheet colour to fonts)

Unhide Sheets using Single Click

In MS Excel, hide multiple sheets using a single click is available i.e. Select multiple sheets by pressing control button, and then right click and select Hide Sheets. But this is not available for unhidden sheets.

Below is VBA add-in to unhide sheets using single click

1. Open a New Excel File

2. Make Sure Macros are enabled (Click Here to Know How to Enable Macros)

3. Open Visual Basic Editor (VBE) using Alt + F11

4. Insert Module

5. Add below code and Close VBE

 ===============================================================

 ' Add-In Unhide Sheets using a Single Click

Option Explicit

Sub UnHideSheets()

Dim i, cnt as integer

    cnt = ActiveWorkbook.Worksheets.Count
    i = 1
  Do
     Sheets(i).Visible = True
     i = i + 1
  Loop Until i > cnt

End Sub

===============================================================

 6. Click on "Save As" file

 7. Select "Excel Add-In" in the "Save-As Type" Dropdown and Save file as "Showsheets"

7. Save File as "Add-In" File

8. Close Excel File

Now, It should be available for display sheets using single click

Manage Add-In

1. Open MS Excel

2. Click on File -> Options  For Excel 2010  (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")

3. Select Add-Ins and then from "Manage" Drop Down (At Bottom) - Select "Excel Add-In" And then Click on "Go"

4. Select "ShowSheets" and Click on OK

Map Add-In to Button

1. Click on File -> Options (For Excel 2007 - Click On "Office" Button and then click on "Excel Options")

2. Select "Quick Access Toolbar"  For Excel 2010 (For Excel 2007 - Click On "Customize")

3. Select “Macros” from “Choose Commands from”

4. Select "UnHideSheets" and Add

5. Then Under “Customize Quick Access Toolbar”

     a. Select "For all Documents (Default)"

     b. From Below List Select "UnHideSheets" and Click on Modify

6. Select any sign & give any display name that you like to view then Click Ok

7.  Click “Ok” , Save & Close file

8. New icon will be created at top

9. When you want to unhide sheets of any workbook click on the newly created button; it will show all hidden sheets from that workbook.

Scheduling To run Macro using VBScript

To Execute Excel Macros through VBScript
Here is the link

COUNTIF & SUMIF

Normally we use these functions often while analyzing the data. Let us see the practical use of these functions.

COUNTIF - COUNT function counts numbers in a range, while COUNTIF function counts values that is provided in the function as a condition, e.g. If you want to view count of available numbers in a range, then use COUNT function but sometimes we would like to know how many times a value is appeared in a column. Let us say in column A, Salesmen's names are appearing, we want to see how many times single salesman name is appeared in that column. Then we will use countif function. i.e. =COUNTIF(A:A,A1) , It works as follows, in A1 salesman name is "BOB" and we are looking for how many times his name is appearing in column A (or in any other column). COUNTIF function returns number which shows how many times BOB is appearing in column A. Apply filter if you would like to see salesmen appearing more than once in a list. The same thing can be done by using pivot table.

SUMIF - SUMIF is function used for doing sum based on condition. This can be used when you would like to sum based on a condition usually for a summary report. Let us see with an example.
Suppose you would like to see total of sales of a product in a summary report. Data includes sales figure by diffrent cities, e.g.













Now to create summary report, to see the sum of total sales by city. List the city names of which summary report you would like to create. e.g.







Now insert following formula in sales column and drag it till Row 4.
=SUMIF(B:C,G2,C:C) . It will first ask range range to be used for doing sum. Here I have selected range B:C i.e. City & Sales column from first table, then condition is supplied i.e. city name in G2, and finally range is given on which sum needs to be done. so formula will look city name from G2 in range B:C and sums the numbers from Sales column from first table which matches the city name and returns the total sum viz.







Again this can be also done by using pivot table.

Splitting Name/Data in a cell

Sometimes we have to split data in a different cell. e.g. We have Names in column A. And we have to split names as First Name, Middle Name &  Surname. There are two ways to do this activity.

1.  Select the column in which Names are available,  Click on “Data” menu, then click on “Text to column” . Select “File Type” as Delimited. Click “Next” , Select delimiter as “Other” and In a text box Next to other , enter space.  Click Next and then Finish. Data will be distributed in 3 columns.

2. Using Formula -

If Name is in A1 then formula to get

First Name

If Name in column A is “Ajay Kumar Pai” Then Above formula will show first Name only i.e. “Ajay”.  LEFT function returns leftmost characters of a cell, and FIND function return number where space is given in Name. so in above case. FIND function finds the space starting from first character. As first space is after Ajay then it returns number as 5, Then LEFT function returns first 5 leftmost characters i.e. “Ajay”

Last Name 

Above formula first substitutes space after first name as # then finds location of space after middle name as first space is replaced by #. After that it counts the lenght of characters first name and middle name using function LEN and finally it counts total characters and deducts it from length of characters till first space i.e. after middle name. It gives the number of characters in last name and Right function splits rightmost characters upto number of characters of last name and gives last name.

if Middle Name is not in the name i.e.  only first and last name is there like “Ajay Pai”. then another formula is used. i.e.

Middle Name -

It works same as above explanations.

~ Sangram