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

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 on the Excel ribbon

Once you run Excel, you will see two new tabs on 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 five groups: Merge, Compare, Dedupe, Text, and Manage.

Merge group

In the Merge group, you can see tools to merge and consolidate data in worksheets and also Vlookup Wizard that builds INDEX/MATCH, VLOOKUP, and XLOOKUP formulas:
Merge group

  1. Merge Tables matches and merges data from two sheets in six steps. You 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 from duplicate rows into one. You 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 select your sheets, a function to consolidate data, and a place where 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 worksheets located in different Excel files. You can paste data to one sheet 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

VLookup Wizard

Vlookup Wizard matches and pulls values from another worksheet in a single step. The wizard builds INDEX/MATCH, VLOOKUP, or XLOOKUP formulas that you can insert into your table.
More details: How to use VLOOKUP Formula Wizard for Excel

Compare group

In the Compare Group, there are tools to find unmatched records in two or more worksheets, or in two workbooks.

Compare group

  1. Compare 2 Sheets is designed to detect and color different rows or cells in two worksheets. You can then process them manually cell by cell.
    More details: How to compare two sheets in Excel
  2. 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
  3. Compare 2 Workbooks identifies differences between worksheets with the same name in two workbooks. You can see all unmatched rows and cells marked with the color of your choice.
    More details: How to compare two workbooks

Dedupe group

In the Dedupe group, there are three tools 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

  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 tools 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 the same values, formulas, background, or font color.
    More details: How to find duplicate cells in Excel

    Remove Dupes Within Cell clears repeated text separated by certain delimiters within Excel cells.
    More details: How to remove duplicate substrings

    Find Fuzzy Duplicates 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 2 Tables provides a fast way to find duplicates and unique values in any two datasets. 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, there are tools for working with text cells in Excel:
Text group on 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 more options for processing text cells:

    • Click Add to add characters or substrings to a specified position in the selected cells.
      More details: How to add text
    • The Extract button lets you extract a 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
    • The Remove tool 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. This set of tools will help you convert and split text values:

    • With Convert Text, you can convert numbers stored as text to numbers, replace characters, and convert line breaks to spaces, periods, commas, or semicolons.
      More details: How to convert text

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

    • Split Names can come 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

  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

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

  2. These three options can come in handy as well:

    • Adding Watermarks can come in useful when preparing your tables for printing, can help identify the owner or the status of the document or protect information.
      More details: How to add watermark to worksheets
    • 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
    • TOC creates a table of contents with hyperlinks to all the worksheets in the current workbook.
      More details: How to create table of contents in Excel

Ablebits Tools tab

Another Ablebits tab on the ribbon is Ablebits Tools. The tools are arranged in six groups: Formulas, Date & Time, Transform, Search, Calculate, and Utilities.

Formulas group

In this group, there is a tool to work with formulas in Excel.
Formulas group

  • Formula Editor shows Excel formulas in a tree view thus making them easy to write, change, or copy.
    More details: How to edit formulas in Excel

Date & Time group

This group provides three tools for working with dates in Excel:
Date & Time group

  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

  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 Split Workbook to copy selected worksheets to new workbooks. You can move each worksheet to a new Excel workbook, group them in new workbooks by substrings, or move every specified number of sheets to a separate file.
    More details: How to use Split Workbook Wizard for 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 — transpose, swap, or flip.

    • 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
    • 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 is a handy tool when you want to get rid of all empty rows, columns, and sheets in a couple of clicks.
      More details: How to remove blanks

    • Split Names comes in useful 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

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

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

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

Calculate group

In the Calculate group, you can see options for data calculations:
Calculate group

  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 the same cell or range in multiple Excel sheets. You can edit, create references, copy, or aggregate the 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

  1. Under the Randomize button, there are three more tools:
    Randomize tools

    • 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 rows, columns, or 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 insert or delete 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 tools:

    • Start Copy Address to copy cell address and paste it as a 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 can see the Buy Now button and the number of days left for trial use. After license activation, this button is not shown.

Also, there are two dropdown menus: Help and Options.

  • By 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 dropdown

  • You can expand the Options menu to:
    Options dropdown

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

Next steps with Ultimate Suite for Excel

  • Need more details? Information about licensing, purchasing, privacy policy & data safety, etc. you can find in Ultimate Suite for Excel documentation.
  • Ready to dive deeper and learn about advanced Microsoft Excel features and Ultimate Suite solutions? Visit the Ablebits blog to explore our free tutorials and examples.
  • Got an idea for Ultimate Suite improvement? Send your suggestions directly to the Development Team at support@ablebits.com. Thank you!

Responses

suman shrestha says:
June 18, 2024 at 3:14 pm

Please provide me free

Hello Suman,

If you are interested in our Ultimate Suite for Excel, please note that this add-in is not freeware. You can use it for free only during the trial period. Once the trial ends, you will need to purchase a license for the product if you want to continue using it. Feel free to download the trial version of Ultimate Suite from this webpage.

You may find all user guides for the tools here. What is more, you are welcome to download a cheat sheet for the add-in and use it anytime.

If you have any questions or need further assistance from our side, please email us at support@ablebits.com.

Thanks for your efforts Ablebits Team.

Quick question.
Is there any possibility that I could disable the automatic backup creation option?
When i just for instance, change cell case to UPPER-CASE, Ablebits keep creating backup sheet for it. Which is I don't need it. HELP~!!

Kausar Farooq says:
March 21, 2023 at 6:24 pm

I am using this for business purpose.

Hello Kausar,

Thank you for your comment.

You can use Ultimate Suite for Excel for both private and business purposes. If you haven't obtained a license for the product yet, feel free to make the purchase directly from our website.

If you have any questions or need some assistance with our add-in, please let us know. We'll be happy to help.

dorsa divsalar says:
February 7, 2023 at 9:30 am

it is really good and useful

Thank you for your comment, Dorsa.

We are now updating our website and gathering customers' testimonials to publish on the product page. If it's not too much trouble for you, we would appreciate it if you write a few words about our software that we may publish on our website. Your positive feedback motivates us to provide the best experience for you and helps others understand how our product makes their life easier :)

Thank you in advance and have a nice day!

Azhar Mohammed says:
December 26, 2020 at 1:36 pm

Dear Sir/Madam,

This is Azhar Mohammed am the student and i want to purchase this tool, please could you provide me the best discount for this tool so that i can help me in my workshop.

you quick and positive reply highly apricated.

Post a comment

Seen by everyone, do not publish license keys and sensitive personal info!

If you have any questions or issues with this add-in, please feel free to post your concerns in the comments area. As soon as we answer, a notification message will be sent to your e-mail. If you do not want to share your thoughts in public, please contact us at support@ablebits.com.