Problem: You hate using cell references such as B2 in formulas.
Strategy: Use natural language formulas. These formulas are fairly amazing. Excel has offered support for natural language formulas for many versions. With these formulas, you can use the headings in a worksheet to describe which cells you want to reference. By default, natural language formulas are turned off in later versions of Excel. To enable them, go to Tools – Options – Calculation and select Accept Labels in Formulas, as shown in Fig. 214.
As shown in Fig. 215, you can now enter formulas such as these: =SUM(East) and =SUM(ABC)
It is important to note that there are no named ranges in this worksheet! Excel is simply looking at the labels in the fi rst column and row of the spreadsheet.
To refer to sales of ABC in the West, you would enter =West ABC, or =ABC West, as shown in Fig. 216.
If you wanted to fi nd the ratio of East ABC to West GHI, you would use this formula =ABC East/GHI West, as shown in Fig. 217.
Additional Information: Amazingly, these formulas can even handle labels with space in them. As shown in Fig. 218, the formula of =ABC Gross Profit will work
Also, if you’ve ignored all of my pleas to have headings take up only a single row of cells, and have a spreadsheet with “Gross” in E1 and “Profit” in E2, you can still use the formula =Gross Profi t ABC, as shown in Fig. 219.
Important: You must refer to the headings in the order that they appear from top to bottom. You can even figure out the Gross Profit on a Gross quantity, as shown in Fig. 220.
Gotcha: If your data set is missing labels, Excel may have trouble calculating formulas that are in cells that are not adjacent to your dataset.
Summary: Natural Language Formulas present an alternative to typical formulas in Excel. They might be great for your boss’ boss who can’t quite get the hang of using cell references in Excel.
[...] you some tips and tricks to get your Excel features optimized. You will get post about how to use a formula to solve your problem, it will guide you through with a simple step by step guidance. With this [...]
[...] by Excel.But you should know better first about excel and its function, there are huge list of Excel formulas you could use, but you should pick the most appropriate for you. So you will have a solution, not [...]
[...] don’t need your old calculator to get how much you did over the month on sales.And with some Excel tips and tricks you could use your Excel to next level of usage. Here you can find what you can do with Excel [...]