iiQ Resources - Excel Tips for Importing Asset Data

Tool

What It Does

Key Formula / Use Case

Remove Extra Spaces (TRIM)

Eliminates unnecessary spaces, retaining a single space between words.

Formula: =TRIM(A1)

Use Case: Cleans up titles in CSV exports (e.g., for VLOOKUP/XLOOKUP consistency).

Scientific Notation

Converts values written in scientific notation into a numerical or text format.

Use Case: Fixes instances where data like ISBNs are incorrectly converted to scientific notation.

Keep Leading Zeros

Preserves leading zeros by adding an apostrophe (‘) or changing the cell format to Text.

Use Case: Necessary for inventory tags, ISBNs, zip codes, or other identifiers that start with zeros.

Remove Duplicates

Eliminates repeated values or entire rows.

Use Case: Essential before reporting, pivoting, or building clean, canonical lookup lists.

Highlight Duplicate Values

Identifies duplicate cell values within a column or multiple columns.

Use Case: Helps identify assets without unique identifiers or variations of the same model/category before an import.

Tool

What It Does

Key Formula / Use Case

VLOOKUP

Searches the first column of a table for a key and returns a value from a specified column in the same row (cannot look left; fixed column index).

Formula (Exact-match): =VLOOKUP($A2,$D$2:$G$100, 3,FALSE)

Use Case: Simple, stable tables where the lookup key is in the leftmost column.

XLOOKUP

Modern lookup that searches one range for a key and returns the aligned value from another range (left or right, vertical or horizontal; defaults to exact match; allows custom "not found" result).

Formula: =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Use Case: Most modern lookups; ideal when columns may move, or when needing leftward lookups or built-in error handling.

CONCATENATE (TEXTJOIN/CONCAT)

Combines text from multiple cells into a single string. (joins A2, B2, C2 with spaces and ignores empty cells)

Formula (TEXTJOIN): =TEXTJOIN(" ",TRUE,A2,B2, C2)

Use Case: Build readable labels, full names, IDs, or URLs by merging fields with consistent separators.

Tool

What It Does

Key Use Case

Autofill Cells

Double-click the cell’s bottom-right fill handle to copy its value down the column automatically.

To quickly fill blank cells with the same value (e.g., a missing Asset Type).

Copy + Paste Values

Replaces formulas with their calculated results, pasting only the values without underlying formulas or formatting.

Avoids reference errors when using formulas and then adjusting columns or copying data across sheets.

Pivot Tables

A dynamic summary table that aggregates large datasets (grouping, counting, summing, averaging).

Quickly and flexibly summarize transactional data (e.g., number of assets) for spotting trends and answering ad-hoc questions.