If you collect email addresses in your spreadsheet. You may want to validate an email address in Excel.
Using Data Validation you can check the syntax of an email address to ensure it is legitimate. It cannot check the existence of an address, or who it belongs to. But it will ensure the existence of the @ symbol, a period (.) and no spaces in the address.
[Read more…] about Validate an Email Address in ExcelWhen you enter a date into Excel, Excel will format it using its default date format. Excel provides plenty of alternative date formats for you to choose such as 14.03.01 and 14 March 2001.
We want to use a format that will tell us the weekday of the date, whether it is a Monday, Tuesday etc.
Type a date into a cell of the spreadsheet. This can be any date you want post 01/01/1900. It can be the date you were born, date you were married, or the date the 2012 Olympics starts. For this example, I will use Christmas day 2001
The weekday is now visible in the cell. Notice how the Formula Bar still displays the date as 25/12/2001. Formatting cells changes the appearance of the value, but the cell still contains the original value.
The AutoFilter tool in Microsoft Excel enables you to filter a large list of data to see only the records you want to see. It is a popular everyday tool for working with large Excel lists and helping you to find records and analyse your data.
This tutorial looks at three relatively unknown uses of the AutoFilter tool.
[Read more…] about Three Unknown Uses of the AutoFilter ToolIf you are storing pictures in a list in Excel, then you need to make sure that you can sort the pictures in the list. Unless a few steps are taken in advance you will not be able to sort pictures in Excel.
Imagine you have a list of products and you have a picture of that product in one of the columns. When you sort the list by product name, price, or number sold you will want the pictures to move with the appropriate product.
There are two things you need to ensure are setup before this can happen;
The 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.
