Tips and Tutorials for Excel - Range and Cells

Using these add-ins can really save your time:

Kutools for Excel: It includes 300+ handy features and functions to free you from time-comsuming operations in Excel. Free Trial

Office Tab: Bringing a handy tabbed interface in your Microsoft Office. Free Trial

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

Modify

Convert to date

• Convert various non-standard date formats to standard date
• Convert yyyymmdd to normal date format
• Convert date to weekday, month, quarter or year in Excel
• Convert hours,minutes and seconds to decimal hours
• Convert military time to standard time in Excel
• Convert between unix timestamp and date
• Convert between Julian date and calendar date
• Convert date to fiscal year/quarter/month in Excel
• Convert date to text or number in Excel
• Convert time string to time or datetime in Excel
• Convert time to decimal hours/minutes/seconds

Round without formula

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

Randomize Utilities

• Randomly fill values from a list of data
• Randomly add background/fill color for cells
• Generate random strings
• Insert random numbers in a range
• Generate random date quickly in Excel
• Randomly sort cells in a column or range
• Select cells randomly
• Quickly generate random time in Excel
• Generate random even or odd number in Excel
• Generate random decimal or integer number in Excel
• Generate random number without duplicate in Excel
• Generate random positive or negative number in Excel
• Generate random weekday or weekend date in Excel
• Generate random number based on given mean and standard deviation in Excel
• Generate random Yes or No in Excel
• Keep/stop random number from changing in Excel
• Select random name from a list in Excel

Insert Sequence Numbers

• Create and insert unique sequence numbers
• Auto number merged cells in Excel
• Auto number or renumber after filter in Excel
• Enter the same data/formula in multiple cells at once in Excel
• Generate lottery numbers

Separate Text

• Separate area code from phone number in Excel
• Separate positive and negative numbers in Excel
• Separate whole number (integer) from decimal in Excel
• Split cell by first space delimiter in Excel
• Separate text and numbers from one cell into two columns

Extract Text

• Extract number only in Excel
• Extract text before/after space/comma in Excel
• Extract text between two words in Excel
• Extract text only from alphanumeric in Excel
• Extract time only from date time in Excel
• Extract unique values in Excel
• Extract all but first or last character in Excel
• Extract bold text only in Excel
• Extract decimal values in Excel
• Extract firs/last n character in string in Excel
• Extract initials from names in Excel
• Extract postcode from addresses in Excel
• Extract middle name in Excel
• Extract text between commas, parentheses and brackets in Excel
• Extract text based on text color
• Extract duplicates from one column to another in Excel
• Extract string from IP address in Excel
• Extract state, zip code or city from address in Excel
• Extract street number from address in Excel
• Extract or get the year, month and day from date list
• Extract text before or after dash from cells
• Split date and time into two separated cells in Excel

Resize cell

• Resize chart area/plot area/title in Excel
• Resize row height and column width in Excel
• Resize sheet tabs in Excel
• Shrink text to fit cell in Excel

Convert Ranges

• Convert column list to a cell in Excel
• Convert decimal degree to degrees,minutes and sceonds
• Convert month name to number in Excel
• Convert one cell to multiple rows/cells in Excel
• Convert cardinal to ordinal number
• Convert hex to decimal number
• Convert hex to binary number
• Convert vertical list to horizontal or vice versa in Excel

Merge Cells and Ranges

• Merge dates and time into a cell in Excel
• Merge lists without duplicate in Excel

Prevent Duplicate entries

• Prevent duplicate entries in a column
• Prevent entering values with spaces in Excel
• Prevent special characters entering in Excel

Spell out numbers

• Spell out or convert numbers to English words

Paste data to visible cells

• Paste data to visible or filtered cells only
• Paste skipping hidden/filtered cells and rows

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
• Display negative numbers in brackets

Force Value in Cells

• Change numbers in cells to text
• Change text to number
• Convert scientific notation to text or 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 / convert columns and rows into single row
• Transpose / convert columns and rows into single column
• Transpose duplicate rows to columns in Excel
• Convert matrix to vector or single column

Transpose Table Dimensions

• Covert cross table (2-dimensional table) to list
• Convert flat list to 2-dimentional cross table
• Convert matrix style table to three columns

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
• Convert text strings to formulas
• Enter/display text or message if cells are blank in Excel

Count Cells

• Count duplicate cells in a column in Excel
• Count letters in a cell/range/worksheet in Excel
• Count merge cells only in Excel
• Count/sum cells greater but less than a number
• Count characters, letters or numbers in Excel
• Count cells above a certain value in Excel
• Count blank cells or nonblank cells in Excel
• Count data by group in Excel
• Count half hour intervals
• Count days until next birthday
• Count cells between two values in Excel
• Count cells with specific text in Excel
• Count cells with text number in Excel
• Count yes no answer in Excel
• Count hours worked in Excel
• Count values exclude duplicates in Excel
• Count if cell does not contain text in Excel
• Count if cells contain any date/data in Excel
• Count cells if contain X or Y in Excel
• Countif by date/month/year and date range in Excel
• Countif filtered data/list in Excel
• Use countif to calculate the percentage in Excel
• Countif with multiple criteria in Excel
• Countif a specific value across multiple worksheets
• Count the number of spaces of text string in Excel
• Count the number of times a character / word appears in a cell
• Count/sum cell values by color with conditional formatting
• Count number of cells with nonzero values in Excel

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
• Fill blank cells with dash in Excel

Insert Blank Rows/Columns

• Quickly insert blank /empty rows
• Quickly insert blank column every other column
• Insert multiple blank rows or columns at once
• Insert blank rows when value changes in Excel

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 uppercase
• Change uppercase to lowercase
• Change uppercase to proper or title case
• Change lowercase to proper or sentence case
• Capitalize first letter only or first letter of each word

• Add text to the beginning or end of all cells
• Add text in middle of selected cells in Excel
• 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
• Add unit to each cell

• Remove certain characters from Text String
• Remove first / last word from text string in cell

• Reverse text string or words order in Excel

• Remove trailing spaces from cells
• Remove extra spaces between words 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
• Quickly remove dashes from cells in Excel

• Add spaces after commas in Excel
• Insert spaces before capital letters in excel

• Hide zero currency in Excel
• Hide zero percent in Excel
• Prevent text spilling over next cell in Excel
• Insert same text in every other row in Excel

Advanced Merge

• Merge cells (combine cells contents) without losing data
• Merge cells with space, commas and semicolon
• Merge rows without losing data
• Merge columns without losing data
• Combine the first and last names in one cell
• Quickly combine text and date into same cell in Excel
• Split full name to first and last name
• Combine multiple rows into one and sum duplicates
• Easily concatenate text based on criteria in Excel
• Change – (dashes) to 0 (zero) in Excel
• Combine cells and keep the cell formatting in Excel

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
• Convert angle between degrees and radians
• Convert temperature units
• Unit conversions
• Convert feet to inches, miles, and meters
• Convert time measurement between hour, minutes, seconds, or day
• Convert birth date to age

Copy cells

• Copy cells data with row height and column width in Excel
• Copy cell as text value not formula in Excel
• Copy formulas only without formatting in Excel
• Copy fill color only to a range in Excel

Round Data

• Round date to nearest month in Excel
• Round date to previous or next Sunday in Excel
• Round data to nearest 5/10/50 in Excel
• Round and sum data with one formula in Excel
• Round time to nearest second/minute/hour in Excel
• Round number to nearest odd or even number in Excel
• Round data up or down in Excel

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