Tips and Tutorials for Excel - Editing and Formula
Author: Sale Support Last Modified: 2015-05-18
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
Select
- Select max data with max function
- Select the highest value and lowest value
- Select minimum data with min function
- Select all formula cells
- Select last cell with data in a column or row
- Find earliest and latest dates in a range
- Find first blank cell
- Find position of first/last number in a text string in Excel
- Get end of month date in Excel
- Get month from week number and year
- Find the maximum / minimum absolute values
- Find Max/min value in group
- Select cells based on another cell
- Select cells based on certain criteria
- Select cells with specific text
- Select all negative numbers in Excel
- Convert zeroes to dashes in Excel
- Remove rows based on cell value
- Select cells with error values
- Change background color based on cell value
- Highlight row if cell contains text/value/blank in Excel
- Find and replace exact match in Excel
- Check or find if cell contains specific string/text/word in Excel
- Copy rows if column contains specific text/value in Excel
- Find and replace fill color in Excel
- Find and replace values greater than / less than a specific value
- Select non-adjacent cells or ranges
- Deselect cells from selected ranges
- Reverse selections of selected ranges
- Select all cells with data in selections
- Select every nth column in Excel
- Select every other or nth row in Excel
- Move every other row to colunm
- Select every even row in Excel
- Copy every other row
- Delete every other row
- Hide every other row
- Quickly sum every other or nth row/column
- Merge every other row/cell in Excel
- Change the row height / column width alternated
- Find unique values between two columns
- Find duplicate values in two columns
- Find duplicate values or same values in two sheets
- Remove duplicates and replace with blank cells
- Filter unique records from selected column
- Find duplicate rows in a range
- Remove everything but duplicate rows in Excel
- Find the closest date to today on a list in Excel
- Identify and select all bold cells or text
- Identify row and column number of cell
- Find address of cell with max or min value
- Get the address of active cell in Excel
- Identify and select all merged cells
- Identify and select all locked cells in Excel
- Select entire column except header in Excel
- Sum entire column except header in Excel
Delete/Remove
- Remove blank rows
- Delete multiple empty columns
- Remove duplicate rows
- Delete visible rows only in Excel
- Delete all comments in Excel
- Delete all but formula in Excel
- Delete rows if contains zero
- Delete rows if not containing certain text in Excel
- Delete rows note matching criteria on another sheet
- Delete pivot tables in Excel
- Delete unique values in a column
- Keep or delete leading zeros in Excel
- Remove middle initial from name in Excel
- Remove text before or after a specific character in Excel
- Remove formulas from worksheet but keep the values/results in Excel
Sum values
- Sum values based on criteria in another column in Excel
- Sum values between two dates in Excel
- Multiply two columns and then sum in Excel
- Sum cells with text and numbers in Excel
- Sum / count checked checkboxes in Excel
- Sum every n rows down in Excel
- Sum values based on text criteria in Excel
- Sum multiple columns based on single criteria in Excel
- Sum only whole numbers in Excel
- Sumif with multiple criteria in one column
- Sumif with one or more criteria in Excel
- AutoSum multiple rows/columns/worksheets in Excel
- Sum values without or exclude subtotals in Excel
- Sum values without or with n/a in Excel
- Sum values in each colummn in Excel
Average values
- Average based on day of week in Excel
- Average cells based on multiple criteria in Excel
- Average every 5 rows or columns in Excel
- Average last 5 values of a column as new numbers entering
- Average top or bottom 3 values in Excel
- Calculate average between two dates in Excel
- Average a range of data ignoring zero
- Calculate average without max and min values
- Average absolute values in Excel
- Average cells ignoring error values
- Average only positive or negative numbers
- Calculate average/compound annual growth rate in Excel
- Average range with rounding in Excel
- Average by year/month/date in Excel
- Average rate of change in Excel
- Weighted average in Excel
- Average per day/month/quarter/hour with pivot table in Excel
- Average timestamps of day in Excel
- Average cells from different sheets in Excel
- Average numbers greater than 0 in Excel
- Average of dynamic range in Excel
- Moving/rolling average in Excel
- Running total /average in Excel
- Weekly average in Excel
- Average functions in Excel
Links & hyperlinks
- Remove all hyperlinks
- Remove hyperlinks without removing formatting
- Create hyperlink in a cell to another sheet
- Link textbox to a specific cell
- Prevent / disable automatic hyperlinks in Excel
- Prevent hyperlinks from changing color in Excel
- Hide hyperlink addresses in Excel
- Open multiple hyperlinks at once in Excel
- Convert url text to clickable hyperlink
- Extract actual addresses from hyperlinks
- Change multiple hyperlink paths at once
- Convert file path to hyperlink in Excel
- Break all external links in Excel
- Find and list all links (external references)
- Disable update links message when open a workbook
- Auto email with cc or bcc field by mailto function in Excel
- Create a hyperlink to a specific folder in Excel
- Send email from Excel with mailto hyperlink function
- Delete all Pictures easily
- Delete all charts Workbooks
- Delete all Auto Shapes quickly
- Delete all Text Boxes quickly
Stop/Turn off
- Stop auto formatting number to date in Excel
- Stop autocorrect (c) to © in Excel
- Stop rounding big numbers in Excel
- Turn off autocorrect in Excel
- Turn on/off scroll lock in Excel
- Stop/turn off auto fill color
Other
- Format text as subscript
- Format text as superscripts
- Format single character in selected cell in Excel
- Shade alternate rows
- Shade every other column
- Quickly shade alternating filtered rows
- Alternate row color based on group
- Copy specified cell formatting from one cell to other cells
- Quickly clear or remove all formatting of cells
Date & time
- Change American date format
- Change multiple dates to day of week
- Convert date to weekday name or month name
- Quickly insert current date with specific date format
- Insert current day, month or year
- Remove time from date
- Remove year from date
- Remove date from date time in Excel
- Determine if a date falls between two dates or weekend
- Add / subtract days / months / years to date
- Count the number of specific weekdays between two dates
- Count / calculate the number of days between two dates
- Convert week number to date or vice versa
- Count / calculate weeks / months / years between two dates
- Find first / last day or working day of a month
- Calculate day of the year in Excel
- Calculate days left in month or year in Excel
- Find the date of next Monday in Excel
- Find the date of previous Sunday in Excel
- Convert dates to text strings
Formula
- Format ranges same as another cell
- Copy formula without changing its cell references
- Copy numbers without formulas in cells
- Only copy comments from cells to another
- Only copy formatting from cells to another
- Copy values and formattings in cells
- Apply same formula to multiple cells
- List all formulas in Excel
- Keep formula and formatting when copying and pasting
- Change relative reference to absolute reference
- Change absolute reference to relative reference
- Keep formula cell reference constant in Excel
- Replace range names with cell references
- Change cell reference in formulas to range names in Excel
- Change #DIV/0! error to the readable message
- Search and replace # formula errors with 0 or blank cells
- Reference same cell from multiple worksheets
- Show cell reference (as text) instead of value and vice versa
Lookup & Vlookup
- Lookup latest or earliest date in Excel
- Lookup the last matching value in Excel
- Lookup value to match case sensitive in Excel
- Lookup value with multiple criteria in Excel
- Use vlookup exact and approximate match in Excel
- Vlookup to get the row number in Excel
- Vlookup to return blank instead of 0 or N/A in Excel
- Vlookup values across multiple worksheets
- Vlookup values from right to left in Excel
- Ignore errors with Vlookup function in Excel
- Vlookup value and return true or false / yes or no in Excel
- Vlookup return value in adjacent or next cell in Excel
- Vlookup between two dates and return corresponding value
- Vlookup in two dimensional table in Excel
- Vlookup next largest value in Excel
- Vlookup to another workbook
- Vlookup to return multiple values in one cell
- Find highest value in a row and return column header in Excel
- Find the highest value and return adjacent cell value in Excel
- Find the first / last value greater than X in Excel
- Find the first / last positive / negative number in Excel
- find the max or min value based on criteria in Excel
- Find or check if a specific workbook is opened or not in Excel
- Find out if a cell is referenced in other cell in Excel
Drop Down List
- Create a dynamic drop down list with alphabetical order in Excel
- Create a drop down list remove blank in Excel
View|Range & Cells | Editing & Formula|Workbook & Worksheet|Others