Ultimate Suite for Excel: Getting Started

Thank you for choosing Ablebits Ultimate Suite for Excel, we are happy to welcome you here. To get started, you can look through this quick product overview or download the cheat sheet:

Download cheat sheet

Note. Before getting started, please make sure that you are not using Office from Microsoft Store: How to install Desktop Office instead of Office from Microsoft Store

How to install & activate

First of all, if you haven't installed or activated Ultimate Suite for Excel, please find the tutorials below. If you are already done, move further to explore the Ablebits tabs and features.

Personal edition installation

Business edition installation

How to activate Ultimate Suite license

Ultimate Suite in Excel ribbon

Once you run Excel, you will see two new tabs in the ribbon – Ablebits Data and Ablebits Tools:
Ablebits tabs in your Excel.

Ablebits Data tab

On the Ablebits Data tab, the options are collected in four groups: Merge, Dedupe, Text, Manage.

Merge group

In the Merge group, you will find six features to merge and consolidate data in worksheets; the Vlookup Wizard that builds INDEX/MATCH and VLOOKUP formulas; and tools that compare two or multiple Excel sheets:

Ablebits tabs in your Excel.

  1. Merge Two Tables is one of our top used features. It matches and merges data from two sheets in five steps. You will select the main and lookup tables, matching columns and columns that you want to update.
    More details: How to use Merge Two Tables for Excel
  2. Combine Sheets lets you join multiple worksheets into a single table according to column headers. Tables may be structured identically or differently, and you can select the columns to merge.
    More details: How to combine Excel tables based on common headers
  3. Merge Duplicates combines data in a worksheet from duplicate rows into one. You will select columns where you want to find duplicate entries and columns with values to merge.
    More details: How to combine duplicate rows in Excel
  4. Consolidate Sheets summarizes data from multiple worksheets. In three quick steps, you will select your sheets, a function to consolidate data, and a place to paste the result.
    More details: How to consolidate numeric data in Excel
  5. Copy Sheets offers four options to bring together records from several sheets located in different Excel files. You can paste data to one list or workbook.
    More details: How to copy data from multiple worksheets into one
  6. Merge Cells combines data from text cells. You can use it to merge several cells, rows, or columns keeping all data.
    More details: How to merge multiple Excel cells into one
  7. Vlookup Wizard matches and pulls values from another worksheet in a single step. The wizard builds INDEX/MATCH or VLOOKUP formulas that you can insert into your table.
    More details: How to use VLOOKUP Formula Wizard for Excel
  8. Compare Two Sheets is designed to detect and color different rows or cells in two worksheets. You can merge them manually cell by cell.
    More details: How to Compare Two Sheets in Excel
  9. Compare Multiple Sheets highlights differences in two or more Excel sheets. You can get the different values, formulas, formatting marked with background color, bottom border, or font color.
    More details: How to Compare Multiple Sheets

Dedupe group

In the Dedupe group, there are three options that search for duplicates in one table or in two sheets, identify duplicate rows and highlight unique entries, move repeated values and copy uniques:

Dedupe group in the ribbon.

  1. Duplicate Remover can find and delete duplicates in your worksheet based on one or several key columns; identify unique values, highlight, copy or move the found entries.
    More details: How to find duplicates and uniques in Excel sheet

    Also, under this button, you can find three more handy features:
    More features under Duplicate Remover.

    Find Duplicate Cells can search for duplicates, duplicates + 1st occurrences, unique cells, or uniques + 1st occurrences in an Excel range. You can identify cells with same values, formulas, background, or font color.
    More details: How to Find Duplicate Cells in Excel

    Remove Duplicate Substrings clears repeated text separated by certain delimiters within Excel cells.
    More details: How to Remove Duplicate Substrings

    Fuzzy Duplicate Finder finds and fixes typos and misprints and recognizes omitted, excess, or mistyped symbols.
    More details: How to Find Fuzzy Duplicates

  2. Quick Dedupe is a one-step way to find, show and remove duplicates in Excel. It can not only eliminate dupes, but also select, highlight, identify in a status column, copy, or move them.
    More details: How to dedupe Excel table
  3. Compare Tables provides a fast way to find duplicates and unique values in any two data sets: tables, columns, or lists. The tool searches for matches and differences by one or several key columns.
    More details: How to find duplicates in two Excel worksheets

Text group

In the Text group, you will find 8 features for working with text cells in Excel:

Text group in the ribbon.

  1. Click Trim Spaces to remove leading and trailing spaces and excess blanks between words.
    More details: How to Trim Spaces

  2. Use Change Case to change the case of text in all the selected cells to upper, lower, proper, or sentence case.
    More details: How to Change Case

  3. The following three features offer even more actions with text cells:

    • Click Add to add characters or text to the beginning, end, or other position of the selected cells.
      More details: How to Add Text
    • The Extract button lets you extract a certain number of characters based on their position. You can get the result inserted as a value or as a formula.
      More details: How to extract text and numbers
    • Remove Text can delete characters, character sets or substrings, or remove a certain number of first or last symbols and chars before or after the indicated text.
      More details: How to remove unwanted characters
  4. And the last three of the Text group deal with converting and splitting:

    • With Convert Text, you can convert numbers stored as text to numbers, replace characters, and convert line breaks to space, comma or semicolon.
      More details: How to Convert Text

    • Click Split Text to split cells by any chosen character, divide cell contents by line breaks, split the contents of one cell into multiple columns, split cells by strings, divide a cell into rows.
      More details: How to split cells

    • Split Names can be in handy if you have a worksheet with differently formatted names in the same column and need to put the first, last, and middle names into individual columns.
      More details: How to separate names in Excel
  5. Substring Tools is a set of options for working with multiple values:

    Substring Tools options.

  6. Use Regex Tools to identify cells that match a certain regular expression or to extract, remove, or replace matching text strings.
    More details: How to use Regex Tools

Manage group

The Manage group is all about managing workbooks and worksheets, adding watermarks and table of contents:

Manage group in the ribbon.

  1. Use Workbook Manager to organize all opened Excel workbooks in a catalog and move worksheets across books, insert, rename, delete, and resort them.
    More details: How to manage Excel files

  2. Start Column Manager to arrange multiple columns in a proper order. The tool displays a list of all columns in your table, so you can navigate between them, add new ones, autofit, etc.
    More details: How to manage columns

  3. These two options can be in handy as well:

Ablebits Tools tab

Another Ablebits tab in the ribbon is Ablebits Tools. The options are collected in five groups: Date & Time, Transform, Search, Calculate, and Utilities.

Date & Time group

This group provides two features for working with dates in Excel:

Date & Time group in the ribbon.

  1. Click Date & Time Wizard to add or subtract years, months, weeks, days or even hours, minutes and seconds. The tool not only calculates differences but also creates formulas.
    More details: How to work with Date & Time Formula Wizard

  2. Date Picker is a drop-down calendar and a date calculator. Pick a date in the calendar or add and subtract years, months, weeks, and days from a date and get the result put into a cell.
    More details: How to work with Date Picker
  3. Use Text to Date to convert text strings that look like dates into true Excel dates.
    More details: How to convert text to date in Excel

Transform group

This group lets you transform your data sets in various ways: unpivot, split, transpose, swap ranges, flip, and more.

Transform group in the ribbon.

  1. Use Unpivot Table to transform your pivot table (crosstab) to a one-dimensional list and save the result to another worksheet or workbook, without corrupting the original data.
    More details: How to transform a crosstab list to a flat table

  2. Run Create Cards to turn your table data into label cards – address or mailing labels, price tags and other kinds of cards.
    More details: How to create labels

  3. Split Table is a quick way to separate your worksheet into different sheets based on values in the selected columns.
    More details: How to use Split Table Wizard

  4. Use Transpose to convert rows to columns and vice versa. You can paste values only, values and formulas, or create links to the source data.
    More details: How to transpose data in Excel

  5. Click Transpose by Key Columns to transpose columns to rows by the chosen key columns and simultaneously combine cells if needed.
    More details: How to transpose by key columns

  6. Select the way of table transformation – swapping or flipping?

    • Pick Swap to quickly swap the contents of columns, rows, ranges, or cells without copy-pasting.
      More details: How to swap ranges

    • Click the Flip button to reverse columns or rows, flip data vertically or horizontally.
      More details: How to flip data

  7. This set of options may be useful in your table transformation finishing touches – removing blanks, splitting names into columns, filling blank cells:

    • Delete Blanks will help you get rid of all empty rows, columns, and sheets in a couple of clicks.
      More details: How to remove blanks

    • Split Names can be in handy if you have a worksheet with differently formatted names in the same column and need to put the first, last, and middle names into individual columns.
      More details: How to separate names in Excel

    • Start Fill Blank Cells to fill all the blank cells in the selected range with values from cells above or below.
      More details: How to Fill Blank Cells

Search group

With the tools in the Search group, you can look for values and replace or select them, find broken links, or synchronize the selection in different sheets:

Search group in the ribbon.

  1. Start Find and Replace to search in values, formulas, hyperlinks, and comments across multiple sheets and books and replace in all or selected workbooks and worksheets.
    More details: How to use Advanced Find & Replace for Excel

  2. Select by Value/Color will find cells based on your criteria and select or color them.
    More details: How to select cells by value or color

  3. Here are some features for filtering, finding broken links, and synchronizing selection:

Calculate group

In the Calculate group, you will find four options for data calculations:

Calculate group in the ribbon.

  1. Sum by Color calculates your data by background or font color; counts the number of such cells, and finds average, minimum and maximum values in a range.
    More details: Sum and count by color

  2. Use Count Characters to count all words, characters, or specific symbols. The result may be pasted as a value or as a formula.
    More details: How to count characters and words in Excel

  3. Try out Cross-Sheet Operations if you need to work with same cell or range in multiple Excel sheets. You can edit, create references, copy or aggregate same cells.
    More details: How to use Cross-Sheet Operations

  4. Click Calculate to apply the same math operation (divide, subtract, add, or multiply) to all numbers in the selected range.
    More details: How to perform math operations on a range of cells

Utilities group

In the Utilities group, some additional useful little things for Excel are collected:

Utilities group in the ribbon.

  1. Under the Randomize button, you will find three options:
    The Randomize button.

    • Use Random Generator to fill the selected range with random integers, real numbers, dates, Booleans, and strings.
      More details: How to use Random Generator

    • Shuffle Cells can randomize cells in each row and column individually, re-sort all cells in the selected range, or shuffle entire rows and columns.
      More details: How to Shuffle Cells

    • Click Select Randomly to get a random selection of cells from a dataset according to your conditions.
      More details: How to Select Randomly in Excel

  2. Start Spell Number to convert numbers to words. You can get currency labels and cent values if needed.
    More details: How to Spell Number in Excel
  3. Use Add/Remove Leading Zeros to keep or remove leading zeros in your cells.
    More details: Add or remove leading zeros in Excel

  4. Insert Pictures lets you insert multiple images into separate cells in a column or row.
    More details: How to insert multiple pictures

  5. If you need to copy cell address, convert formulas, or manage notes and comments, use these features:

    • Start Copy Address to copy cell address and paste it as external or absolute reference.
      More details: How to copy cell address in Excel

    • Click Convert Formulas to change all absolute cell references in the selected range to relative ones and vice versa.
      More details: How to convert formulas

    • Use Notes Manager to quickly add, copy, edit, delete notes, and convert cell contents to notes and vice versa.
      More details: How to manage notes in Excel

Ultimate Suite options

Under the Ablebits Data tab, there is the Ultimate Suite group:
Ultimate Suite group.

If your Ultimate Suite is not activated, you will see the Buy Now button and the number of days left for trial use. After license activation, this button will disappear.

Also, there are two drop-down menus: Help and Options.

  • Clicking the Help button, you can go to the Ultimate Suite documentation, jump to our blog, contact us via e-mail, start the welcome tour, go to the logs folder, and open the About window to see the version of the product and the license information:

    Help menu.

  • You can expand the Options menu to:
    Options menu.

    • Check the Load on demand option if you use the Ablebits tools from time to time and when you do this you see that Excel works slower than usual. If this option is enabled, the add-in will be loaded only when you start any of the Ultimate Suite tools. If it's disabled, the add-in is loaded when you start Excel.
    • The Enable Ablebits functions option lets you use the custom Ablebits functions in some of the tools. The feature is very useful but may influence the speed of response of the add-in. If you are not planning to use any of the functions, keep the option unchecked.
    • Delete all backup sheets in this workbook.
    • Change the license key if you already have one.

Next steps with Ultimate Suite for Excel

  • Need more details? Information about licensing, purchasing, privacy protection & data safety, etc. you can find in Ultimate Suite for Excel documentation.
  • Ready to dig deeper into the capabilities of Microsoft Excel and Ultimate Suite? Visit the Ablebits blog to explore our free tutorials and examples.
  • Got an idea for Ultimate Suite improvement? Send your suggestions directly to the Product Team using the contact form below. Thank you!