We help IT Professionals succeed at work.

Understanding Tables and their Impacts on Formulas

-Polak
-Polak asked
on
Relatively easy one here, I've never used tables heavily, because up until Excel 2013 there wasn't much reason (in my humble opinion) now because of Slicers, Data Models, Power Pivot, etc... I can see an advantage. Here's my question....

I've become very used to cell references in formulas, take for example the below array (CTRL+SHIFT+ENTER):
{=AVERAGE(IF((AircraftData!$E$2:$E$301=AnalysisEngine!$B18)*ISNUMBER(MATCH(AircraftData!$D$2:$D$301,ConstrainedReqs!$B:$B,0)),AircraftData!$F$2:$F$301))}

Now "AnalysisEngine!$B18" is in my newly created table. But I notice that it hasn't changed the name of that cell reference to the name of the header Column "[@Asset]". What I'm trying to figure out for my self is if I Love/Hate that it doesn't do that automatically, and if I should change it.

So basically, should I be using them instead of cell refs, and what do I need to be aware of if I do? For example, pressing F4 no longer makes something absolute in position.... what if I wanted that.
Comment
Watch Question

Top Expert 2015
Commented:
I will advise using the table names in the formula rather then ranges because table makes it compartively more friendly then the normal ranges when you add new data to it as table automatically extend where as in the formula you have to go manually and expand them...

Here are 10 benefits of table for your reference which can give you more context around it..

Link to tables

Saurabh...

Author

Commented:
Alrighty, thank you, just cautious because I don't have the greatest amount of time to learn the nuances. For example, I just discovered that I cannot use the table names for a rule that I made in conditional formatting...
Top Expert 2015

Commented:
Polak..

Let me tell you this everything has its pro's and con's... Now for conditional formatting i won't be able to comment about it till the time i see or know what you are talking about...

Saurabh...

Author

Commented:
As long as there are more pros.... haha

It's wasn't really a question, just simply go try and create a rule on a table where you set the range of the rule to apply to [HeaderName@] you'll see that the rule is created. However, it sets [HeaderName@] back to a normal range.
Top Expert 2015

Commented:
Will try that in sometime in a middle of a code right now which need to complete.. and will post here my findings..

Author

Commented:
Sure thing when you do can you also let me know why some structured references in my table appear as [@[XXXXX]] where others just appear as [@XXXXX] when I click the cell to go into a formula....?
Top Expert 2015

Commented:
Okay i did some findings and here are your answers...

1. You are right you need to give range as in range format..Because you can't give that value like [@[xxxx]] outside table as it won't recognize that..and it will give you an error as it recognizes that in the table only..

2. If you give a value which has a space or character in between it will take it as [@[xxxx]], However if doesnt have a space or special char it will take it as [@xxxxx].. to give you an example

If i write Header1 it is shown as-->=[@Header1]
Now if i write header-1..it is shown as -->=[@[Header-1]] or even header_1 shown as-->=[@[Header_1]] and header 1 shown as-->=[@[Header 1]]

Hope this helps..it sort of programmatic language of office that how it reads it if it has spaces...

Author

Commented:
Cool, all good stuff to know, this syntax is confusing wish they would have just kept the $$ for absolutes.