MS Excel: Question and Answer
We've added this category to include some of our general Excel questions from viewers.
Freeze/Unfreeze Panes
- Freeze top row in Excel 2013 | 2011 | 2010 | 2007 | 2003
- Freeze first column in Excel 2013 | 2011 | 2010 | 2007 | 2003
- Freeze first row and first column in Excel 2013 | 2011 | 2010 | 2007 | 2003
- Unfreeze panes in Excel 2011 | 2010 | 2007 | 2003
Layout
Hyperlinks
- Extract hyperlink address (web addresses ONLY) in Excel 2003
- Extract hyperlink address (files and web addresses) in Excel 2003
- Filter final series of numbers in hyperlink address in Excel 2003
- Delete a hyperlink from a cell in Excel 2013 | 2011 | 2010 | 2007 | 2003
- Delete all hyperlinks on a sheet in Excel 2003
- Update cell based on hyperlink selected in Excel 2013 | 2011 | 2010 | 2007 | 2003
- View hyperlink result at top of viewing area in Excel 2013 | 2010 | 2007 | 2003
- Create hyperlinks to link cells in column E to their matching value in column C in Excel 2007
Buttons
- Creating a button in Excel 2007
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
- Open the Visual Basic Editor in Excel 2011 | 2010 | 2007
- Display the Developer tab in the toolbar in Excel 2016 | 2013 | 2011 | 2010 | 2007
Mouse Behavior
- Enable right click on Mac in Excel 2011 for Mac
Integration with E-mail
- Email the active worksheet in Excel 2007
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
- Set up a cell to only allow up to a certain number of characters in Excel 2013 | 2011 | 2010 | 2007 | 2003
- Set up a cell to only allow (EXACTLY) 10 characters in Excel 2013 | 2011 | 2010
- Set up a cell to only allow positive numbers in Excel 2013 | 2011 | 2010 | 2007 | 2003
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
- Sort data in alphabetical order in Excel 2003
Integration with Access
- Open an Access Form from Excel 2003
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
- Turn off AutoCorrect in Excel 2010 | 2007 | 2003
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
Advertisements