This is the second version for PK’s Utility Tool. This is absolute free to cost. In this Utility Tool, we have carried forward all the features form PK’ Utility Tool V1.0
Click here to know more about features of PK’ Utility Tool V1.0-
PK’s Utility Tool 3.0 is available now
Click here to download PK’s Utility Tool V1.0
Click here to download PK’s Utility Tool V2.0
Click here to learn how to install an add-in in Excel. If you are installing PK’s Utility V2.0 then you should remove PK’s Utility Tool 1.0 because version 2.0 has all the feature and functions.
Features are in red rectangles are new in this version, rest we have carried forward all the features form PK’ Utility Tool V1.0 –
Worksheet Manger Group:
In the worksheet Manager group, we have added 3 new features:
- Unhide All worksheets: Using this feature you can hide all the worksheets from active workbook on one click.
- Show/Hide Sheet Tab: Using this feature you can show or hide sheet tabs in active workbook. On first click it will hide sheet tabs. Click again to show the sheet tabs.
Click on Show/Hide Sheet Tab to hide the sheet tabs
- Table of Contents: To create a new worksheet in your active workbook with a table of worksheets, you can click on this button. It will keep the hyperlink for the respective worksheets.
File Manger Group:
In this Group, we have 2 new features-
File Converter:
To convert your files in another format you can use this feature:
Using the file converter tool, you can convert the files as given below-
- Excel to Word: Office 2007 or above version is required.
- Excel to PDF: Office 2010 or above version is required.
- Word to Excel: Office 2007 or above version is required.
- Word to PDF: Office 2010 or above version is required.
- PDF to Excel: Office 2013 or above version is required. Tool will open the PDF file with MS word first then copy the data from word and will paste in to Excel. While opening the PDF with Word, it may ask below given confirmation. Please check the check box and click on OK.
- PDF to Word: Office 2013 or above version is required. During this process, it may ask below given confirmation. Please check the check box and click on OK.
Data Segregation Tool:
Data Segregation Tool is useful to segregate the data into multiple excel files or multiple worksheets. It can save the data into different Excel files or you can keep them opened also.
Learn how to create Data Segregation Tool step by step-
Random Generator Group:
This is the new group in this version. In this group we have 2 features-
Random Generator:
Random Generator is useful to randomly generate numbers or text. You can generate below given random types in selected range-
Below is the snapshot of Random Generator. You can change the minimum and maximum range –
Fill Random Values:
This is feature has been carried forward form PK’s Utility Tool V1.0. Earlier it was in Selection group. Using this feature, you can fill the random values from Excel sheet, or you can used comma separated list. Given list will be distributed randomly in the selected range.
Date Entry Group:
This is also a new group. It was not available in PK’s Utility Tool V1.0. In this group we have given 4 different features-
Searchable Drop-down List:
If you can a data validation list in excel cells and in there is a long list in drop-down of data validation list, then it is very useful.
Learn how to make searchable drop-down list in Excel using VBA
Date Entry Form:
It will not the data entry form. You can use it for data entry in the table available on Excel sheet.
Learn how to use Data Entry form in Excel without VBA-
Enter Date:
You can enter the date in selected range using Calendar.
Learn how to create the calendar control in Excel VBA-
Enter Time:
You can enter the time in selected range using Time Picker.
Selection Group:
In the selection group, we have added 4 new features-
Trim and Clean:
It will remove the leading and trailing zeros form selected range.
Number to Text:
You can convert selected numbers in text format.
Text to Number:
If your numbers are in text format, then you can convert them into number format.
Add Leading Zero:
You can add leading zero in the selected range. You the below given form you can choose number of leading zeros or you can choose what should be the length of your number after adding leading zero. It will add the leading zeros accordingly.
Tutorials and Downloads Group:
In this group, we have the hyperlink of our website from where you can learn and downloads –
Functions:
We have added 4 new functions. Earlier it has 17 function, which was taken from PK’s Function Kit.
Watch this video to learn about more function in PK’s Function Kit.
In excel sheet, you can type “=PK” then you will the get the list of functions-
In the below given table first 4 functions are added in this version, rest 17 function have been carried forward from PK’s Utility Tool V1.0
| S.No | Function Name | Syntax | Result |
|---|---|---|---|
| 1 | PK_Get_Comments | Input_Cell | This formula will return the comments text form the input cell. |
| 2 | PK_Get_Emails | Input_text | This formula will fetch the email ids from the long text. If there are multiple email ids are available in the long text then it will return semi colon separated email ids. |
| 3 | PK_Get_Distance | Start_Latitude,Start_Longitude,End_Latitude,End_Longitude | This formula will return the distance between two places in Kilometers (Km) on the basis of latitude and longitude of Start Point and End Point. |
| 4 | PK_Specific_Char_Count | Complete_Text,Search_Text,Match_Case | This formula will return the count of specific character count. Third argument is options. If you want the case sensitive search then put third argument as True |
| 5 | PK_Unique_Count | criteria_Range | This formula will return the unique values count from given criteria range. Criteria Range should not have more than 100000 rows. |
| 6 | PK_Duplicate_Count | criteria_Range | This formula will return the duplicate values count from given criteria range. Criteria Range should not have more than 100000 rows. |
| 7 | PK_Vlookup_By_Cell_background_Color | Lookup_Cell As Range, Table_Array As Range, Col_Num As Integer, Get_last_Value As Boolean | This formula will return the value from Table array range on the base of lookup cell background color. Col_index_number to be given as number of the column in table array from which it will return the value. Get_last_Value is Boolean, if there are duplicate colors in table array then for 0 it will return the first value from table array and for 1 it will return the last value from table array. Note: This formula will not refreshed automatically when color is changed in table array, need to press F9. This will not work with conditional formatting colors. Table Array range should not have more than 100000 rows. |
| 8 | PK_Vlookup_By_Partial_Lookup_Value | lookup_value As Variant, Table_Array As Range, col_index_num As Integer, range_lookup As Boolean | This formula will work just like VLOOKUP. First it will check the complete lookup value and will return the result. If complete lookup value is not available in table array then it will check if lookup value is available partially then it will return the value. |
| 9 | PK_Count_By_Cell_background_Color | criteria_Range As Range, criteria_cell As Range | This formula will return the count on the base of criteria cells background color. Note: This formula will not refreshed automatically when cell background color is changed, need to press F9. This will not work with conditional formatting colors.criteria_Range should not have more than 100000 rows. |
| 10 | PK_Count_By_Case_Sensitive_Criteria | criteria_Range As Range, criteria As String | This formula will return the count on the base of criteria value. It will count only when case of criteria is matched in criteria range. For Example: if criteria is "ABC" then it will not count "abc" from criteria range. Note: criteria_Range should not have more than 100000 rows. |
| 11 | PK_Count_Font_Bold_Cells | criteria_Range | This formula will return the count on the base of criteria cells's font is bold. Note: This formula will not refreshed automatically when font is made bold in criteria range, need to press F9. This will not work with conditional formatting font bold .criteria_Range should not have more than 100000 rows. |
| 12 | PK_Count_By_Partial_Value | criteria_Range As Range, criteria As String | This formula will return the count on the base of criteria value. It will also count when criteria is partially available in criteria range. For Example: if criteria is "ABC" then it will not count "abc" from criteria range. Note: criteria Range should not have more than 100000 rows. |
| 13 | PK_Text_Join | delimiter As String, ignore_empty As Boolean, text_range As Range | This formula will join the multiple strings given in text range separated by the given delimiter. Ignore_empty needs to be taken as true or false. Note: Text Range should not have more than 100000 rows. |
| 14 | PK_Extract_Numbers | Alphanumeric_Text | This formula will extract the numbers from Alphanumeric Text. It will return a string not a number |
| 15 | PK_Extract_Text | Alphanumeric_Text | This formula will extract the non numeric characters from Alphanumeric Text. It will return a string. |
| 16 | PK_Weighted_Average | Weight_Range,Average_range | This formula will return the weighted average for given Average range. Weight range and Average range should have the same number of rows. |
| 17 | PK_Sum_Of_Digits | Whole_Number | This formula will return the sum of digits of given number. |
| 18 | PK_First_Name | Complete_Name | This formula will return the first name from the given complete name. |
| 19 | PK_Middle_Name | Complete_Name | This formula will return the middle name from the given complete name. If middle name is not available then it will return blank. |
| 20 | PK_Last_Name | Complete_Name | This formula will return the last name from the given complete name. If last name is not available then it will return blank. |
| 21 | PK_Ordinal_Date_Format | Input_Date | This formula will return the ordinal date for given date like for 1/1/2018 it will change as 1st Jan, 2018. Note: It will return a string not a date. |