Ultimate Suite for Excel: Getting Started

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:

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 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.

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:

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:
   

   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:

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:
   
   • Remove substrings deletes all the indicated character sets from the selected column.
     More details: How to remove multiple values

   • Remove Rows by Substrings erases all the rows with certain text values at one go.
     More details: How to remove rows by substrings in Excel

   • Extract Substrings retrieves the indicated values out of a column and puts them into another one.
     More details: How to extract multiple substrings
   • Replace Substrings searches for certain values and replaces them with the indicated characters.
     More details: How to find and replace multiple values
   • Add Substrings inserts one or several text strings to the needed place of the selected cells.
     More details: How to add substrings in Excel
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:

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

Formulas group

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

• 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:

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.

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:

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:
   • Use Filter to filter a list by value in a selected cell or by multiple values in one column.
     More details: How to filter by cell value

   • Find Broken Links detects invalid external references. All broken links are displayed on the add-in pane, so you can review, open, or edit them.
     More details: How to find broken links in Excel

   • Sync Selection may help if you have multiple sheets and want a certain range to be selected in all these sheets.
     More details: How to keep the same selection in Excel sheets

Calculate group

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

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:

1. Under the Randomize button, there are three more 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