Tip 1 : Can you find duplicates using a combination of Conditional Formatting and the COUNTIF function?

Answer: Here is how you can do it:

=COUNTIF($A:$A,$A3)>1
=COUNTIF(column with duplicates, first cell in column)>1

Tip 2 :

When making cells fixed in a formula, if you press F4 twice, it will make the row fixed. If you press F4 three times, it will just make the column fixed.

If you have any suggestions for an Excel Tips & Tricks topic, or you would like help with a particular function, please email enablement@alchemex.com and yours could be the next Tip Of The Week.

Click here to download this latest tip in pdf format

Alchemex provides examples of MS Excel procedures for illustration only, without warranty expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The MS Excel procedures on this web site are provided "as is" cannot be guaranteed that they can be used in all situations

• How to protect a worksheet but unprotect selected cells so they can be edited.
• The way to add a chart to some data without going through the Chart Wizard.
• How to check if a given date is within 90 days of today’s date.
• How to change a year value represented as a decimal (e.g. 3.667 years) into years and months.
• The way to average a series of non-contiguous cells, excluding any cells that contain zero values
• How to add a second list to a validation list, where the contents change based on the value chosen in the first list.
• Formula that can help work out various dates in Excel 1
• How to prevent the formula error (#VALUE! or #DIV/0!) from ocurring
• How to use the Tab key to jump to the next cell in an Excel spreadsheet
• Formulae to help work out various dates you might need in Excel
• A quick way to determine if the range contains duplicate names
• How to hide the formulae in a spreadsheet
• Quick and easy way to clear specific cells but keep the formulas
• Looking for a way to simulate that nerdy green-banded computer paper on screen? Look no further!
• Get familiar with Excel’s Paste Function dialog box and learn how to perform a variety of basic maths operations without a single formula
• How to adjust a range of numbers without using a formula
• The easiest way to keep the current date or time permanently set in a cell
• The way to hide the tabs for all worksheets
• How to avoid duplicate entries in a range of cells
• Excel keeps a different value in the cell for dates, this is how you can accurately compare it to text of a date
• How to return the lowest value of a group of numbers, but ignore any zeroes there may be
• The way to count the number of cells based on whether cells are upper or lower case
• How to close all open workbooks without closing Excel
• The quick way to check whether the contents of one column matches the contents of another column
• The way to find the cell address, rather than the value, of the lowest number in a range of numbers
• How to send information from some cells in an Excel spreadsheet directly to an email
• How to work out the day of the week given the full day/month/year date
• Quick way to correct all errors on a sheet with one value, regardless of type of error
• An easy way to add a double, rather than single, underline to text
• A quick way to use AutoFill to increment numbers but leave a cell blank between them
• A quick way to select all cells on a sheet with a formula
• How to add notes to a formula using the N function
• Convert a value into words
• Sort worksheets in alphabetical order
• Fill a range with a series of dates
• Quick way to create a Chart
• Customise a sort option in a PivotTable
• Update your formulas manually
• Using your Formula Auditing toolbar to show cells
• Group your dates into Weeks in PivotTables
• Using Data Validation when entering in dates
• Using the SUMIF function
• Quick way to clear a range of selected cells
• Hide the formulae when projecting worksheets
• Split data between columns
• Add 00 in front of values
• Filter Values in a PivotTable e.g Not to show 0 values
• How to use Grouping on a protected worksheet
• Display values that are in millions as thousands
• Using an alternative to a VLOOKUP
• Join several strings of data into one cell
• Format your dates to show the Period Numbers
• Link an Excel worksheet to a PowerPoint presentation
• What to do if your dates in Excel are seen as text and not dates
• The way to filter date transactions to only show dates that fall into a range from 30 to 60 days from today’s date
• Delete all non-formula data on a worksheet
• Remove all hyperlinks from the worksheet
• Formula that can round whole numbers e.g. up to the nearest hundredth
• Doing a Sum function, to define criteria to limit the cells the function considers when it generates the result
• Create a Macro that will copy the current worksheet and save the workbook using a cell (A1) as the file name.
• When using the SUMIF Function between workbooks, you may get a VALUE error if the source workbook is not open
• Create a list of Symbols that can be inserted into Excel e.g. ½
• Convert negative numbers to a positive value
• How to open a workbook always on a specific worksheet
• The quick way to name a range of cells
• The quick way to copy formulas across columns, also ensuring the formatting is retained
• The quick way to wrap text in a cell.
• Is there a way when using the IF function, to get Excel to recognize the case of the text.
• Is there a quick way access a list of Named Ranges?
• Is there a way to paste paragraphs of text into a single cell in Excel?
• Is there a way to work out the difference between two dates, but exclude weekends?
• Is there a way to password protect a workbook but still allow people to open it as read only?
• How to copy data from Microsoft Excel and paste link it into Microsoft Word
• The quick way to enter text or a formula once into a cell, then fill it into a specific range
• The way to find the cell address of the highest value in a range
• It is possible in Microsoft Excel, to track the last cell that has been changed by the user
• The way to work out the difference between two dates, but exclude weekends
• The Quick way to navigate between worksheets
• The quick way to select a range of cells
• It is possible to copy several individual, distinct areas onto one worksheet and have them linked to a source.
• Function Keys
• How to speed up data entry if you have a lot of duplicates to enter
• Microsoft Office Excel 2007 top 10 benefits
• The quick way to add extra lines to a pivot table
• To use the keyboard and not the mouse when using Autofill
• Quick way to convert data from Caps to proper style
• How to exclude hidden cells when copying and pasting data in Excel
• How to use the Find And Replace function to also check the header and footer areas of the workbook
• Cells can change colour based on how close the date in the cell is to today’s date
• Using Excel to graphically show data without using charts
• The way to calculate the last date of a month for a range of months automatically without using a function
• How to make the ‘Merge and Centre’ formatting option available in Excel during normal use
• You can use the same Named Range on different sheets of the same workbook
• How to shade cells based on odd or even values
• How to use Excel Help without online resources
• How to count the number of blank cells in a range