Dec 13, 2013

Imagine you’re in a situation where you want to send a list of the employees’ birthdays so that everyone in the company will know when to send their regards to others in the organisation. You have a list of all of the birthdays including the birth year in an Excel spreadsheet. How would you convey this information in a way that wouldn't embarrass anyone or betray any confidences by revealing the year people are born? You could edit each birthday and remove the year from the field, but what if you need the information later? Instead, what you can do is create a custom number format, or a mask, in Excel 2010 that will only display the information that you want when you print it out or convert to PDF.

1. In your Excel spreadsheet, select the column of dates.
2. In the ‘Home’ tab, open the dialogue box of the ‘Numbers’ panel. You’ll see many different styles of dates under the ‘Category’ area. Select the ‘Custom’ option.
3. In the right box, a number of different formats will appear. Choose ‘d-mmm’ from the list to display only the day and the Alpha month and click OK.

With these changes, you’ll see that the new format will appear, but the original data will still display in the formula bar. Be sure to print or convert the file to PDF. If you send the Excel file, it will defeat the purpose of hiding the data! Another option might be to display the day of the week that a particular date in time occurred. For example, if you wanted to display Wednesday or Friday, you would need to do the following;

1. Repeat steps 1 and 2 above.
2. In the ‘Type’ text box, type in the format ‘dddd.’ This will display only the day that the date occurred.

A third option that you may consider is adding a word to the front or back of a number field. An example would be if adding the words ‘Employee ID-’ in front of each employee number. To create this type of custom format, follow the steps below.

1. Repeats steps 1 and 2 above.
2. In the ‘Type’ text box, type in ‘“Employee ID-” ####.

One hash symbol represents one digit in the field you want to format. The hash marks are place holders representing the numbers that will be substituted in their place in the actual content. You can see from these examples that creating custom number formats in Excel is easy and allows you to manipulate data and information to be presented in a certain way. If you’re interested in finding out what else Excel can do, take a look at New Horizons’ Excel training programs.