JET – Useful Excel Tools

x5

  My Folders – Save your favorite or most used Excel folders to this handy tool to allow quick access to your most used Excel files.

• Open Windows Explorer to selected folder or file.
• Organize favorite folders into categories.
• Filter files in the current folder only, the current folder and all sub-folders or in all saved folders.
• Batch rename your computer files using an Excel file as a renaming template.

x8

  Navigate – Quickly jump to any Excel sheet in any open workbook.

• Open Windows Explorer to the selected workbook path.
• Hide/Unhide all selected sheets.
• Protect/Unprotect all selected sheets.
• Change the tab color of all selected sheets.
• Reorder (sort) sheets.
• Copy print settings (user selected) from selected sheet.
• Paste copied print settings to all selected selected sheets.
• Bulk rename sheets using an Excel template.

x1

  Go Back – Quickly toggle between the last used Excel sheet and the current sheet.

• Useful for comparing two sheets.
• Press Ctrl+Shift+P (keyboard shortcut can be changed in settings) to activate.

x3

  Quick Calculator – Opens a calculator that can interact directly with Excel ranges.

• Option to pick the function that will be applied to the current saved value.
• Result is saved across all workbooks and is retained until Excel is closed.
• Ability to set the number of decimal places shown.
• Can input the result to the active cell.
• A running history of all inputs and results can be viewed from the View menu.
• A My List feature to save important values or text that can be quickly inputted to Excel on double-click.
• The My List feature can also be used to save range selections that can be re-selected later even on different sheets or workbooks.

x2

  Goto Next Unique Cell – Quickly jump to the next cell that is different from the cell currently selected.

• Enables traversing long sorted columns very quickly without having to scroll or use the Page Up or Page Down keys.
• Hold the Shift key while clicking one of the arrows to select all exact matching cells instead of going to the next different cell.
• Hold the Ctrl key while clicking one of the arrows to NOT skip over hidden cells.

x3

  Jump – Quickly move the current selection to another column or row.

• Allows moving a complex multi-area selection one or more columns to the left or right or one or more rows up or down.
• Use the Multi-Select (M-Sel) button to toggle multi-cell selection on or off.  When multi-cell selection is on you can select additional cells without holding the Ctrl key or remove existing cells that are currently selected without losing the selected cells you want to keep selected.
• The Resize button allows expanding the current selection by the specified number of rows or columns or to resize the current selection to the entire row or column.
• Hold the Shift key to adjust the selection by the previously used values without the form opening and hold the Ctrl key to adjust the selection in the opposite direction of the previously used values.

x5

  Select Cells – Select cells based on type, format or contents.

• Each Select Cells search is done on the existing selection when the Select Cells form is originally opened to use the current selection click the ‘Add Sel’ button.
• The cell contents option can be based either on the cell value, the cell’s formatted text as displayed to the user or the cell formula.
• The cell contents comparative can be =,<>,<,<=,>,>=,between, not between, like, not like, unique, not unique, not unique incl original or alternating.
• Multiple values can be entered for the cell contents option by pressing Ctrl+Enter to enter additional matching criteria.

x2

  Cell / Cell Font Color – Apply Color to Cells Interior or a Cells Font.

• Choose between the original Excel 56 color palette, the Excel 2007-2010 color palette, the current Excel 2013-2021 color palette or your own custom palette.
• Color can be applied to cells, drawing shapes or chart objects.

x8

  Convert – Quickly convert the selected range of cells.

• Convert options:
  • Values to text
  • Text to numbers
  • Formulas to values
  • Change case to UPPER, lower or Proper case
  • Clean text by removing the non-printable characters
  • Trim text (left, middle, right or any combination)
  • Transpose values from rows to columns or columns to rows
  • Normalize  values – great for turning multiple columns into more rows to make pivot table analysis easier.

x1

  Cell Viewer – View a cell’s value, text and formula character by character.

• This tool is great for testing if a cell contains any special characters like Line Feed Char(10), Carriage Return Char(13) or non-breaking space Char(160) that causes vlookup formulas not to work as expected.
• The Formula dropdown can be changed to FormlaR1C1, FormulaLocal or Formula2.

x1

  Cell Styles – Apply a user created style to the selected cells.

• Easily create new cells styles by copying the active cells formatting and choosing all appropriate formatting properties.
• All user created styles are available across all workbooks unlike Excel’s style manager which saves custom styles only to the workbook it’s created in.
• Easily create table styles by specifying one set of formats for the first row and a different set of formats for all additional rows.
• 26 built-in custom styles are already included but each one can easily be removed if not needed.

x5

  Defined Name Manager – View, add, edit, delete or import defined names.

• Powerful filter and sort tools to customize the defined names that are shown.
• Unhide, hide or delete defined names in bulk.
• Double-click any defined name that is based on an Excel range to go directly to the cells specified.
• Bulk import new defined names or changes to existing defined names.

x2

  Formula Viewer – Parse Excel formulas into their component parts and display them in a tree-like structure.

• Allows seeing both the Excel range addresses and range values that make up the current formula.
• Evaluate sub-functions nested within the larger formula.
• Double-click any formula component that is based on an Excel range to go directly to the cells specified.
• Quickly view all precedent cells along with their location, value and formula.

x1

  Multi-Cell Copy & Paste – Copy multi-area non-continuous Excel ranges.

• Paste either the values for formulas.
• Copied values or formulas can be pasted as follows:
  • Down – as a single column of values or formulas.
  • Across  – as a single row of values or formulas.
  • Maintain Relative Location – select only a single paste cell and each copied value or formula will be placed in the same relative location as it was copied.

x2

  Value Formulas – Quickly value all formulas if they contain any of the function keywords.

• Value the current selection, worksheet or workbook.
• Value all functions (blank form), a single function or a list of functions.
• Optionally, view the list of cells that would be valued including their location, formula and value.

x5

  Get External Data – Import external data to Excel using a simple point and click query builder.

• Current supported external data sources include any Microsoft SQL server, SQLite database, Microsoft Access database, CSV file or another Excel file.
• All connections and queries can be save for future exports.
• Data can be exported to a new worksheet, a new formatted table, a linked table or linked pivot table that can then be quickly and easily refreshed by right-clicking the exported table and selecting refresh.
• Data can also be imported from Excel to the external data source.
• The included query builder greatly simplifies creating the SQL query syntax allowing any user, even with no understanding of SQL, to create their own queries using this step-by-step query builder wizard.
• The included query builder contains the following features:
  • Filter by list – this shows all the unique values in the field in the left box and the values to filter on in the right box.
  • Filter by value – this allows filtering by using operands like =,<>,<,<=,>,>=,between, not between, etc.
  • All filter options can have the “Prompt”checkbox checked which will then prompt the user at run-time for the values to filter on.
  • Each field in the source table or query can be include or excluded from the returned data set.
  • Calculated fields can be added with an included function builder to help with syntax.
  • Group By can be set for all fields to summarize the returned data set.
  • The final SQL statement can be customized manually for more advanced users.

x3

  Find Links – Find and or break all external reference links.

• View all external reference links in either the current sheet or workbook.
• Before breaking links view their parent sheet, parent sheet’s visible status, parent object name or range address, parent object type, parent object visible status, the property the link is found in, the fully qualified link path, how the link will be broken, the break result (success or failure) and any error messages associated with breaking the link.
• Double-click any link to go directly to the parent object.
• Choose which links are broken if you want to allow some links to remain.

x1

  Print with Options – Apply custom print rules before print previewing the active sheet.

• Create new print templates that can be saved for repeated future use.
• Print rule options include:
  • Remove cell color – remove specified cell colors before printing.
  • Remove font color – change specified font colors to black before printing.
  • Hide Rows – that meet a specified criteria before printing.
  • Hide Columns – that meet a specified criteria before printing.
  • Change/Execute drawing object – examples including clicking a button or changing a combo box before printing.
• Once the print preview window is closed after printing is completed or cancelled all actions done by the print rule will be undone automatically.

x1

  Save as CSV File – Save the current Excel sheet as a CSV file.

• Quickly save the active sheet as a CSV file without having to open Excel’s Save As dialog or being pestered by Excel’s warning messages.

x1

  Show Access Database User List – View the current computer names that are logged into a user selected Microsoft Access database.

• Allows you to select a Microsoft Access database to see all the users that currently have the database open.

x1

  Workbook Splitter – Copy sheets from a large workbook into smaller workbooks based on a user-defined Excel template.

• The Excel template contains the following columns used to determine how to create the smaller workbooks:
  • Workbook Name: this is the name of the new smaller workbook to be created and is repeated for each copied sheet the new workbook will contain.
  • Sheet Names to Include: this is a list of the sheet names that will be copied from the workbook to split into the new workbooks.
  • Open Password: optionally, you can include a workbook open password for the newly created workbook.
  • Write Password: optionally, you can include a workbook write password for the newly created workbook.
  • Status Message: this column should be left blank as the Workbook Splitter program will post success or failure messages for each sheet copied into the new workbooks.
• Once the template has been created it can be saved for later use.

   Also included in the JET part of the JetCube Excel Add-In is the following user-defined functions that can be used in any of your Excel formulas.

1. JCTIF – a replacement IF function that allows creating formulas that have long running sub-functions to only have to be calculated once.
2. JCTLike – a function to compare two values using wildcards.
3. JCTInStrRev – a function to get the position of a string from the end of another string (like a SEARCH in reverse).
4. JCTExtract – a function to quickly extract either letters, numbers or special characters or any combination thereof from another value.
5. JCTGetCellColor – a function to get the Excel color # from a cell.
6. JCTSumByColor – a function to sum cells by their color.
7. JCTXLColorToRGB – a function to convert an Excel color # to it’s RGB value.
8. JCTRGBToXLColor – a function to convert and RGB value to an Excel color #.