• Analytics

Toolbar in TeamMate Analytics

  • Toolbar in TeamMate Analytics



    TeamMate Analytics Toolbar



    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:

    • Number – a custom number format can be set in Options
    • Date – a custom date format can be set in Options
    • Currency – a custom currency format can be set in Options

    Manipulate Text - A variety of tools to manipulate text fields:

    • Convert to Proper Case
    • Covert to UPPER CASE
    • Convert to lower case
    • Remove leading spaces
    • Remove trailing spaces
    • Remove leading and trailing spaces
    • Remove all spaces
    • Remove multiple spaces
    • Convert textnumbers to numbers
    • Convert numbers to text
    • Remove numeric characters
    • Remove alphabetic characters
    • Remove non-numeric characters
    • Remove non-alphabetic characters
    • Remove unprintable characters
    • Remove leading characters
    • Remove trailing characters
    • Remove all after X characters
    • Advanced character removal

    Insert Borders – Formats the selected cells with 3 standard accounting borders

    • Top border
    • Top and bottom border
    • Top and double bottom border

    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.