Introduction
Have you ever imaged how you could add a combination of text and numbers in a cell within Excel and reference that cell in a formula without getting an error? You are probably thinking... hmm how is that possible! Let me show you how!
When setting up your assumptions tab for Financial Models, it is very nice for values to have a label at the end of numbers so that they are clear. For example you may want to include < 2.5 x or 15 years as an input in your calculation as they read better when in one cell instead of including 15 in one cell and years in another. Don't worry if you don't quite get what I mean yet, the link to the Excel workbook at the bottom of this page shows how we have demonstrated this and allows you have a practice run.
Single Character Example
Here, we show you how to include number(s) with a single character in a cell and use reference this cell in your calculation. Let's work with < 2.5 x and feel free to follow along.
- Enter 2.5 into a cell and press CTRL + 1 to open the format cell dialogue box shown, under category scroll down and select Custom.
- By default, the Type section will show General, clear this out and type in 0.0 (this is a placeholder for a digit with one decimal place), if you do not want a decimal place you type in 0.
- Then type in < 0.0 x as shown in image below, it’s up to you to include spacing or not (whatever your preference is). The sample box section shows what your results would be in the cell. Now try using the cell in a calculation. Magical hey! We thought so too!!
- Hopefully, you got the same result as below. You can now attempt to reference this cell in any calculations and the days of error messages are over, phew! Please don't forget that you can only use the above format when you have a single character before or after a zero. It wouldn't work for multiple characters after a number(s) such as 15 Years, so come with me on the next flight to learn more.
Multiple Character Example
Oh, there you are, it's good to see you again! We already know we need to type CTRL + 1 to show our dialogue box and clear out whatever you have under Type and replace with 0 bla bla! So let's dive straight in.
- Now , type 15 in a cell and notice that if you type in 0 years in the Type section, nothing happens. Yes, this is because years has multiple characters. What next?
- Try putting the years in quotation mark like this 0 "years" and click OK! This cell containing a combination of numbers and letters can now be referenced in any calculations.
Remember to click on the link to our workbook below to see our calculations and demonstrations within Excel.
Conclusion
Given you are now a wiz at custom formatting, why don't you download our workbook to practice today so that you can keep impressing your bosses and colleagues. If you have found this post useful, please leave us a comment below and let us know what else you would like to see on our page. Thanks.
GOOD LUCK!
Click hyperlink below to download Excel file.