Here we have a brief description of each of the TeamMate Analytics tools – just click on the tool name for a description.
You can jump to the relevant section by clicking below:
The TeamMate Analytics toolbar includes various tools that help with day to day Excel productivity, and specifically help with cleansing and manipulating source data into the format you need.
Format as – Reformats a number into one of 3 types:
Manipulate Text - A variety of tools to manipulate text fields:
Insert Borders – Formats the selected cells with 3 standard accounting borders
Tick/Cross – Inserts a “tick” / “check” or “cross” symbol into a cell using the included TeamMate Analytics ticks font.
Wrap Text - Toggles wrap text on/off for the current selection.
Merge, Wrap and Autofit – Merges the selected cells, wraps the text and autofits the cells to the appropriate height. When the built in excel function “Merge and Centre” is used, it is unable to autofit the row height to ensure that all the data is visible, TeamMate Analytics solves this.
Debit/Credit Columns - Splits an amount column into two separate debit/credit columns, or combines separate debit and credit columns into a single column
Fill Cells – Fills selected cells with fixed values, incremental values or random values. You can either prefix, append or replace existing values. You can also “fill from above”, allowing you to repeat a value (e.g. supplier number or date) where it only appears once.
Fix Dates – Have you ever had a system report where Excel doesn’t recognise the dates? This tool will automatically work out what format the source data is in, and covert it to dates that Excel (and TeamMate Analytics) can recognise and work with.
Global Find and Replace – Searches for a value or text across the current workbook, all open workbooks or all workbooks in a folder. You can also choose to replace the found term if desired.
Add Date Column – Creates a formatted date column, for example to identify the “week number” and “financial year”, which can then be used for summarisation.
Header and Footer – Inserts a standard header and footer into the worksheet, using information stored in Engagement Details.
Comment to Header – Many of the TeamMate Analytics tools store details about the options that were selected, source data, etc. as a comment in cell A1 as an audit trail. This button converts this comment into a document header, also incorporating stored information from Engagement Details.
Reverse Polarity – Converts positive numbers to negative numbers and vice versa.
Swap Delimiters – Converts numbers in the format “12.345,67” to “12,345.67” (a common problem with European data reports) so Excel can recognize them as numbers.
Move Minus Sign to Front – Moves the minus sign in numbers that are displayed as “9,652.23-” from the back to the front, so Excel can recognise them as numbers (e.g. “-9,652.23”).
Convert to Values – Converts any formulas in the current selection to their values.
Extract Visible Cells – Extracts all the visible cells (i.e. not cells that are hidden or filtered out) on the sheet to a new sheet.
Sheet Index – Creates a click-able index sheet, either ordered alphabetically or in the original sheet order.
Sheet Checker – Checks your source data for potential issues like merged cells, duplicate headers, total rows, blank rows/columns, etc. As well as identifying issues, Sheet Checker can automatically fix most of them at the click of a button.
Monetary Unit Sampling – Estimates, extracts and evaluates a statistically selected sample from a population.
Stratification – Splits data into bands containing ranges of values. You can also define your own stratification bands and also select a random sample from each band.
Random Sampling – Extracts a number of randomly sampled items from your population.
Systematic Sampling – Extracts every ‘nth’ item from the population.
Join Sheets – Joins two sheets together, based on a common matching column – this may simple, but it literally has hundreds of uses and one of the most regularly used tools in TeamMate Analytics!
Append Sheets – Joins multiple sheets that all have the same column structure together. For example, where you have separate reports for each sales person, each warehouse, etc. that you wish to combine into one report.
Aging – Re-ages a listing based on a variety of aging options, including custom aging bands that you can define.
Quick summary – Quickly and simply summarises your data, based on the fields and criteria you specify.
Advanced summary – Summarises your data in a variety of ways and performs statistical analysis on the results.
Ledger split – Splits a report into several worksheets based on the values in a selected column. For example, it can split an inventory listing into a separate tab for each warehouse.
Column statistics – Produces a detailed report on the actual data in each of your columns, such as maximum, minimum, mean, standard deviation, skewness, positive values, zero values, etc.
Highlight Changes – Highlights any differences between 2 versions of a worksheet or workbook and can add a comment to the changed cell to show the previous value.
Benford’s Testing – Performs Benford’s Digit Analysis testing to identify unusual patterns in data – includes 1st, 2nd, 3rd and first 2 digit tests.
Sequential Invoices – Identifies any instances where a supplier has issued multiple invoices with sequential references.
Exception Report – Extracts any records that meet the criteria you specify (up to 3 criteria).
Specific Comments – Searches a worksheet or range for specific comments, such as “suspense”, “rainy day”, “unknown”, etc. It can use values from a stored list in options.
Top and Bottom – Extracts the top and bottom X records from a list.
Gap detection – Identifies for gaps in a sequence of numbers or partly numeric references, such as missing invoices or journals.
Duplicate extraction – Extracts duplicate records, based on up to 3 fields you specify.
Same, Same, Different – Extracts records where 2 fields are the same and a 3rd is different for example, same amount, same date, different vendor.
Outliers – Extracts records where the amount significantly deviates from the mean.
Out of Mask – Extracts records where a field does not match a pre-defined mask, such as bank account numbers/sort codes, National Insurance/social security numbers, etc.
Number Analysis - Extracts any records which have a rounded amount, to 10, 100, 1,000, 10,000, 100,000 or 1,000,000, any records with values ending in ’999′ or zero values.
Analytical Review – Compares two columns of numbers and calculates the absolute and percentage differences (e.g. current period and prior period).
Number Checker – Compares two columns of numbers and looks for any possible missing digits or transposition errors between them.
Options – Allows you to specify global options for TeamMate Analytics
Multi Drill Down – Allows you to perform a “Show Detail” on multiple cells in a pivot table at the same time, either exporting the results to separate sheets, or to a single sheet.
Rename Show Detail Reports – When you “Show Detail” in a Pivot Table, Excel renames the sheet numerically, e.g. ‘Sheet4′. TeamMate Analytics can automatically rename the new sheet with information about the cell you drilled down on.
Double Click to Sort – Allows you to sort your data by double clicking in row 1 of the column you wish to sort by – this can be turned off in the options
Quick Stats – Provides statistics about the selected cells on the right click menu. Selecting a statistic stores the value to the clipboard, so you can easily paste it to another cell or even another document/program. For example you can copy the total of the selected cells and paste that into Word.
Quick Filter and Quick Extract – Allows you to quickly filter out or extract items matching selected cells.
Tick Font – A special font consisting of a wide variety of ticks that can be used to mark up your data. This font is available in any Windows program, so you can use it in Word or PowerPoint too.
TeamMate Analytics includes 7 “Section Modules” each specifically targeted to an area of the accounts.
Prepare Sheets – This prepares a worksheet, ready for use with the Section Modules.
Non Current Asset Section Module
Inventory Listing Section Module
Trade Receivables Ledger Section Module
Trade Receivables Listing Section Module
Trade Payables Ledger Section Module
Trade Payables Listing Section Module
General Ledger/Journal Listing Section Module
Expenses / PCard Transaction Listing Section Module