totn Excel

MS Excel: Question and Answer

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

Freeze/Unfreeze Panes

Layout

Hyperlinks

Buttons

Checkboxes

  • Update a cell when a checkbox is clicked (Forms toolbar) in Excel 2003
  • Update a cell when a checkbox is clicked (Control Toolbox toolbar) in Excel 2003

Radio buttons

  • Set up groups of radio buttons on a form to function independently in Excel 2003

Combo Boxes

  • Creating a combo box in Excel 2003
  • Link data to a combo box and update underlying data in Excel 2003
  • Use two combo boxes to retrieve values from a table in Excel 2003

Conditional Formatting

  • Change the font color based on the value in the cell in Excel 2011 | 2010 | 2007 | 2003
  • Automatically alternate row colors (one shaded, one white) in Excel 2010 | 2007 | 2003
  • Automatically alternate row colors (two shaded, two white) in Excel 2010 | 2007 | 2003
  • Automatically alternate row colors (three shaded, three white) in Excel 2010 | 2007 | 2003
  • Automatically highlight highest and lowest values in a range of cells in Excel 2011 | 2010 | 2007 | 2003
  • Automatically highlight expired dates and dates that are 30 days from expiration in Excel 2010 | 2007 | 2003
  • Highlight 1st, 2nd, 3rd highest scores in Excel 2003
  • Change the font color in one cell based on the value in another cell in Excel 2003
  • Change the fill color of a cell based on the value of an adjacent cell in Excel 2010

Visual Basic Editor

Mouse Behavior

Integration with E-mail

Test for duplicates

  • Test for duplicates in a column in Excel 2003
  • Test for duplicates on partial cell contents in a column in Excel 2003
  • Test for duplicates in two columns, combined in Excel 2003
  • Test for duplicates in eight columns, combined (and delete duplicates) in Excel 2003
  • Test for duplicates in eight columns, combined (and delete duplicates and originals that were duplicated) in Excel 2003
  • Test for duplicates in eight columns, combined (and clear the values in the 8 columns when a duplicate is found) in Excel 2003
  • Create a column in Excel that must contain unique values in Excel 2003

Validation

Filters

  • Filter a single column based on 3 or more criteria in Excel 2003
  • Filter 2 columns based on 3 or more criteria in Excel 2003
  • Apply a filter to an existing filter in Excel 2003
  • Copy filtered data to a new worksheet in Excel 2003

Sorting

Integration with Access

Time Calculations

  • Perform time calculations (Example #1) in Excel 2003
  • Perform time calculations (Example #2) in Excel 2003
  • Perform negative time calculations in Excel 2003

Array Formulas

  • Use an array formula to sum values in Column A when value in Column B and Column C match criteria in Excel 2003
  • Use an array formula to sum values in Column AB when value in Column E and Column AB match criteria in Excel 2003
  • Use an array formula to sum all of the order values for a given client in Excel 2007
  • Use an array formula to count ranges of times in Excel in Excel 2003
  • Use an array formula to count the number of children who will attend lunch in Excel 2003
  • Use an array formula to perform a two criteria lookup in Excel 2003
  • Use an array formula to count the number of rows that match 2 criteria in Excel 2003
  • Use an array formula to count the number of occurrences when the value in column A is greater than or equal to the value in column D in Excel 2003
  • Use an array formula to average values when 2 criteria are met in Excel 2003
  • Use an array formula to calculate monthly attendance in Excel 2003
  • Use an array formula to count the number of dates exceeding 12 months in Excel 2007
  • Use an array formula to count matches on multiple values in one column in Excel 2007
  • Use an array formula to count the number of rows when column F and column L match criteria in Excel 2003

Parsing

  • Create a formula to parse out BAG information and BOX information in Excel 2007

AutoCorrect

Other Examples

  • Test range of cells for formulas (or missing formulas) in Excel 2003
  • Delimit values with ASCII 29 character in Excel 2003
  • Remove carriage returns (displayed as tiny boxes) and <br> tags from data in Excel 2003
  • Automatically refresh pivot table when data in a sheet changes in Excel 2003
  • Automatically refresh pivot table when user switches between sheets in Excel 2003
  • Link one cell to another cell (control the recalculation) in Excel 2003
  • Change the background color of a row based on a cell value in Excel 2003
  • Macro to warn when a record will expire within 31 days in Excel 2003
  • Overwrite column B value when column A value is higher in Excel 2003
  • Relative vs Absolute referencing in Excel 2003
  • Function to calculate total cost based on a tiered fee structure in Excel 2003
  • Autonumber a cell each time the spreadsheet is opened in Excel 2003
  • Open a new instance of Excel whenever you launch the application in Excel 2003
  • Share a spreadsheet between multiple users in Excel 2003
  • Sum each month's data applying an exchange rate in Excel 2003
  • Generate a random number in Excel 2003
  • Get contents of first cell in a range that has data in Excel 2010 | 2007 | 2003
  • Get contents of last cell in a range that has data in Excel 2010 | 2007 | 2003
  • Counting matched pairs in Excel 2003
  • Convert date/time value to a date value (remove time portion) in Excel 2003
  • Copy and paste only nonblank cells (condensing paste range) in Excel 2013 | 2011 | 2010 | 2007 | 2003
  • Display a large number without scientific format in Excel 2003
  • Formula based on whether a cell has a value in Excel 2003
  • Format the display of a date value in concatenated text in Excel 2003
  • Set the print area in Excel 2003
  • Calculate the incremental difference in a running total column even when blank values are found in the series in Excel 2003
  • Link to a cell in a Lotus file in Excel 2003
  • Count the number of cells with a particular font color in Excel 2003
  • Calculate a weighted value based on number of months in Excel 2007