Tips and Tutorials for Excel - Range and Cells

Using these add-ins can really save your time:

Kutools for Excel: includes more than 120 handy functions and tools to free you from time-comsuming operations in Excel 2007/2010. Free Trial

Office Tab: brings you a handy tabbed interface in your Microsoft Office applications as follows. Free Trial

View|Range & Cells | Editing & Formula|Workbook  & Worksheet|Others

Modify

Convert to date

• Convert various non-standard date formats to standard date

Round without formula

• Round / round up / round down a range of cell numbers
• Round range of numbers to even in Excel

Randomize Utilities

• Generate random strings
• Insert random numbers in a range
• Randomly sort cells in a column or range
• Select cells randomly

Insert Sequence Numbers

• Create and insert unique sequence numbers

Prevent Duplicate entries

• Prevent duplicate entries in a column

Spell out numbers

• Spell out or convert numbers to English words

Change Sign of Value

• Change negative numbers to positive
• Change positive numbers to negative
• Reverse signs of values in cells
• Fix trailing negative signs in cells
• Change negative number to zero

Force Value in Cells

• Change numbers in cells to text
• Change text to number

Swap Ranges

• Swap rows or columns
• Quickly swap contents of two cells

Transform Vertical Range

• Change row to column
• Change columns to rows

Transpose Table Dimensions

• Covert cross table (2-dimensional table) to list
• Convert flat list to 2-dimentional cross table

Replace Accented Characters

• Replace accented characters with regular characters

Flip Selected Cells

• Flip/reverse a column
• Flip/reverse a row
• Flip the first and last name in cells

Fill Cells with Displayed Values

• Replace formulas with results or value
• Convert formula to text string

Fill Blank Cells

• Fill blank cells with linear values
• Fill blank cells with value above in Excel
• Fill blank cells with 0 or other specific value
• Apply formula to an entire column or row

Insert Blank Rows/Columns

• Quickly insert blank /empty rows
• Quickly insert blank column every other column
• Insert multiple blank rows or columns at once

Merge/Unmerge cells

• Quickly merge adjacent rows with same data
• Unmerge cells and fill with duplicate values

Text Tools

• Change case of text 2007 and 2010
• Change text strings to sentence case
• Change text strings to uppercases
• Change uppercase to lowercase
• Change uppercase to proper or title case

• Add text to the beginning or end of all cells
• Add strings of text to multiple cells
• Add/ insert leading zeros to numbers or text
• Add/ insert prefix or suffix to selected cells, rows, and columns
• Add percentage symbol for multiple numbers in cells

• Remove certain characters from Text String

• Reverse text string or words order in Excel

• Remove trailing spaces from cells
• Remove leading spaces in cells
• Remove spaces between character and numbers within cells
• Remove leading and trailing spaces
• Remove indents within cells

• Remove alpha characters from cells
• Remove non numeric characters from cells
• Remove numeric characters from cells
• Remove non-printable characters from cells
• Remove non-alpha characters from cells
• Quickly remove line breaks
• Remove non-alphanumeric characters

Advanced Merge

• Merge cells (combine cells contents) without losing data
• Merge rows without losing data
• Merge columns without losing data
• Combine the first and last names in one cell
• Split full name to first and last name

Operation

Operation Tools

• Change values in a cell range
• Add values to multiple cells
• Divide a range of cells by a number
• Do exponential calculation to a range of cells
• Multiply a range cells by a number
• Round a range of cells
• Subtract a number from a range of cells in excel

Currency Conversion

• Convert currencies
• Convert between dollars, pounds, euros
• Unit conversions
• Convert feet to inches, miles, and meters
• Convert time measurement between hour, minutes, seconds, or day
• Convert birthdate to age

View|Range & Cells | Editing & Formula|Workbook  & Worksheet|Others