Let’s say we need to put a prefix in front of a number to identify the period being used. Whether that be year, month or week.

You can achieve this with a custom number format combined with conditional formatting. The image below has the structure that we will use. You can download the example file at the button on bottom of this post.

Cell B3 has a drop-down to select between years, months and weeks.

The cell B2 is an input number that drives the formula in cell D2. The formula in cell D2 is

=SEQUENCE(B2)

This function is part of the new dynamic array set of formulas and you need the subscription version of Excel to use it. It creates a sequential list of numbers, driven by cell B2.

We can select the whole of column D and apply three separate conditional formats to the column.

1. First select column D.
2. Then click the Conditional Formatting icon drop-down on the Home ribbon and select New Rule in the bottom section.
3. Click on the last option in the top section which starts Use a formula …. Enter the following formula in the formula box.

   =$B$3="Years"

4. Click the Format button and click the Number tab and select the Custom option on the left-hand side, at the bottom of the list.
5. In the Type box enter the following entry and click OK.

   "Yr" 0

   

6. Click OK.
7. Repeat steps 2 to 6 for the other two types of periods.
   You can see all the conditional formats if you click the Conditional Formatting icon drop-down on the Home ribbon and select Manage Rules – see below.
8. Test the format by changing cell B3.

The beauty with this technique is that even though the numbers look like text – see images below – they are still in fact numbers, and can be used in calculations.

Download example file