Excel Topics: Question & Answer

We've added this category to include some of our general questions from viewers.

Hyperlinks:

Extract hyperlink address (web addresses ONLY)

Extract hyperlink address (files and web addresses)

Filter final series of numbers in hyperlink address

Delete all hyperlinks on a sheet

Update cell based on hyperlink selected

View hyperlink result at top of viewing area

Checkboxes:

Update a cell when a checkbox is clicked (Forms toolbar)

Update a cell when a checkbox is clicked (Control Toolbox toolbar)

Radio buttons:

Set up groups of radio buttons on a form to function independently

Combo Boxes:

Creating a combo box in Excel

Link data to a combo box and update underlying data

Conditional Formatting:

Change the font color based on the value in the cell

Automatically alternate row colors (dynamically)

Automatically highlight highest and lowest values in a range of cells

Test for duplicates:

Test for duplicates in a column

Test for duplicates in two columns, combined

Test for duplicates in eight columns, combined (and delete duplicates)

Create a column in Excel that must contain unique values

Validation:

Set up cell to only allow a certain number of characters

Set up a cell to only allow numbers

Filters:

Filter a single column based on 3 or more criteria

Filter 2 columns based on 3 or more criteria

Apply a filter to an existing filter

Copy filtered data to a new worksheet

Sorting:

Sort data in alphabetical order

Freeze/Unfreeze Panes:

Freeze panes in Excel

Freeze panes to see first row and first column

Unfreeze panes in Excel

Integration with Access:

Open an Access Form from Excel

Time Calculations:

Perform time calculations in Excel

Perform negative time calculations in Excel

Array Formulas:

Sum values in Column A when value in Column B and Column C match criteria

Sum values in Column AB when value in Column E and Column AB match criteria

Use an array formula to count ranges of times in Excel

Use an array formula to count the number of children who will attend lunch

Use an array formula to perform a two criteria lookup

Miscellaneous:

Test range of cells for formulas (or missing formulas)

Delimit values with Ascii 29 character

Remove carriage returns (displayed as tiny boxes) and <br> tags from data in Excel

Automatically refresh pivot table when data in a sheet changes

Automatically refresh pivot table when user switches between sheets

Link one cell to another cell (control the recalculation)

Change the background color of a row based on a cell value

Macro to warn when a record will expire within 31 days

Overwrite column B value when column A value is higher

Relative vs Absolute referencing

Function to calculate total cost based on a tiered fee structure

Autonumber a cell each time the spreadsheet is opened

Open a new instance of Excel each time for a file

Share an Excel spreadsheet between multiple users

Sum each month's data applying an exchange rate

Generate a random number in Excel

Get contents of first cell in a range that has data

Get contents of last cell in a range that has data

Counting matched pairs

Convert date/time value in Excel to a date value (remove time portion)

Copy and paste only nonblank cells (condensing paste range)

Excel Topics: Question & Answer

Spreadsheet News

Book Shop

Sponsors Links