A common problem when using Excel is handling duplicate entries. I am often asked about removing or highlighting duplicates and common entries.
But prevention is better than the cure, so let’s look at how to prevent duplicates in Excel.
We can use the Data Validation tool with a formula to stop duplicates from being entered by the user. In this example we want to prevent duplicates from being entered into column A.
[Read more…] about Prevent Duplicates in ExcelNamed ranges provide many benefits when using spreadsheets. They allow easy access to data on other sheets, make references absolute and provide meaningful names to data.
However, if the list is updated often with new records or columns of data, then the named range needs to frequently be updated also. This is an irritating and time consuming role, so this post aims to solve that problem by making the named range grow automatically when new rows and columns are added.
I was reminded of the brilliance of this feature just yesterday when I was asked by a company how they could get their formulas to include any new rows of data automatically. They had various formulas and PivotTables running on a large list of data to perform sales analysis. This data is frequently updated as more sales are made.
[Read more…] about Make a Named Range Grow AutomaticallyThe Data Validation tool in Excel can be used to validate uppercase entries. This post demonstrates two examples of validation to ensure text is entered in uppercase.
The first ensures that the whole entry has been written in uppercase, and the second will only validate the first letter of an entry.
This example will ensure that the whole entry is in uppercase.
[Read more…] about Validate Uppercase Entries in ExcelThe Conditional Formatting tool can be used for some very cool tricks in Excel. Today, I needed it to format all the Saturdays and Sundays in a list.
A user had a list of dates and she wanted to highlight each Saturday and Sunday in the list so that they would be instantly recognisable. It also creates and nice effect by splitting each week into blocks.
This tutorial demonstrates how to create a simulation of a dice throw in Excel.
The throw of a dice is a popular away of determining an outcome in board games. This random chance of a number between 1 and 6 provides that element of luck to games.
Download the completed dice throw simulation spreadsheet
The first step is to format the cells so that they look like the side of a die.
[Read more…] about Simulate a Dice Throw in Excel
