User Tools

Site Tools


excel:start

CM Tools for Microsoft Excel

Best Practices for Excel

  • Use tables rather than filters (select data area then select format as a table, this gives you access to refer to items by column and table name
  • Name your tables! Don’t refer to everything as Table1 and Table51, name them sensibly such as Table_Servers, Table_Sites, etc… You can do this by clicking the table, then from the ribbon click Table Tools > Design and editing Table Name.

List of Tools

Amalgamate Data – Excel includes functionality to remove duplicates, but not to do anything with them. This tool allows you to amalgamate all duplicates by treating the first column as a key value. It then collects all entries that have the same ID (first) column and detects each unique value in the second column. If there are multiple entries in this list it will collate them all and separate the values with a comma.

Table Lookup Wizard – This tool simplifies and expands the capabilities of VLOOKUP’s by combining them with the power of Excel tables. This allows you to lookup data from other tables easily without having to use any code or formulae. It also includes basic error checking, as well as the capability to make changes later.

Error Checker – Includes functionality to scan through table columns and find instances where you predominantly use a formula but some cells do not use that formula (e.g. has been removed, different formula, or manually typed answer). Gives you the option to correct the entire column to match the primary formula

Text Tools – A variety of quick tools which allow you to reformat text (e.g. to capitalise or lowercase). Also more specialist tools for example to reformat cells which contain IP Address (IPv4) to remove common errors (like 0.0.0.0, APIPA addresses, text, IPv6 Addresses)

Change Log

excel/start.txt · Last modified: 2016/02/03 16:39 by chris