Overview
If you’ve used Excel for more than a couple minutes, you’ve probably used formula references and know a couple of the symbols for them. Knowing all of them though is crucial for building quality and optimized formulas and workbooks.
In this article, we’ll walk through the nine of the symbols you can use with formula cell references. This will help you understand formulas and write them the correct way. Make sure to grab a copy of the example workbook that accompanies this article using the box below! And if you want to learn the top 52 Excel functions worth using, check out this comprehensive template.
Why use formulas with cell references
Cell references are the backbone of Excel. Without them, Excel isn’t much better than a big calculator. But with them, you can build completely dynamic files that can go well beyond financial reporting. It’s borderline programming!
I make it a goal to minimize hard-coded values as much as possible and maximize formulas. And not just for the cell values, also in conditional formatting. I also use a small arsenal of macros to reuse formulas and formats and check for cells that are hidden or don’t contain formulas.
And now that we have dynamic array functions and dynamic references (see the last two symbols below), they’re even more dynamic! These are relatively new concepts, so if you aren’t familiar with them, give them a try today!
The formula symbols
1. Dollar sign ($)
2. Colon (:)
3. Comma (,)
4. Exclamation point (!)
5. Single quotes ('')
6. Brackets ([])
7. Curly brackets ({})
8. Hashtag (#)
9. Period (.)
Working with named ranges
So far, we’ve covered all the symbols you need to know when working with cell references – within the same sheet, another sheet, or even another file. Another incredibly useful cell reference concept is named ranges. If you’re going to use Excel like a pro, named ranges are a must-know Excel feature. To learn about them in-depth, check out this dedicated article on named ranges.
Key reasons why you should consider using named ranges include:
- Simplifying formulas/references and making them easier to read/understand
- Giving you a central reference – like a tax rate input – that you can update in one place and reflect throughout
- Giving you another way to make dynamic references that can expand or contract automatically as the data changes
- They work well with other valuable Excel features, such as data validation and conditional formatting
- They are crucial for working with macros as traditional cell references don’t shift when you insert/delete rows or columns, but named ranges will reflect those changes
Video
Summary
Knowing how formula references work is a crucial and fundamental skill for every Excel user to master. And that doesn’t just mean the basic stuff like colons and commas, it also means referencing other sheets and files, and the newest symbols (hashtags and periods) that make your files even more dynamic. There have been a lot of great updates to Excel in the last few years, and these are two of them. Don’t get left behind!
Have you done some cool stuff with the newer formula reference symbols yet? Let us know in the comments below!
Recent Comments