How do Excel tables remember formulas

 

If you used a table before seeing your formulas expand and contract with your data automatically. This is a very handy feature tables . Some say they just copy and paste formulas, but this is not true. (See this blog to see why structured table references should be used , which shows how Excel does not copy / paste formulas table : http://excelandaccess.wordpress.com/2013/01/07/why-you- shoulds – references – to – use structured tables / ) the Microsoft Excel team were much smarter than that . Although it has been available since 2007 when the new file formats are released Office , I found about it a few months ago .

 

The new file formats are much more efficient in the way they store data because they use an open XML format. This is best all around for the user. Files are essentially zip files containing a hierarchy of folders containing data . If you need to get up to speed with the new file formats , see these links:

 

http://www.ecma-international.org/news/TC45_current_work/TC45_available_docs.htm (very technical )

 

http://www.jkp-ads.com/Articles/Excel2007FileFormat00.asp (Microsoft Excel MVP Jan Karel Pieterse )

 

I’ll show you how to display formulas table ( and associated information ) an XLSX file. Please note that it works with a XLSM , but the file structure is different for XLSB files because they are in a binary format and store data in bin files, even if it can be done . To view the XML structure I use Notepad + + , but you can use any XML ( notepad-plus-plus.org/download / ) editor.

 

From the data

 

I got some very generic data into a blank Excel file with a column of data and a computed column.

 

 

 

 

The formula is very simple. I typed in the first cell in which the auto-filled into the bottom of the whole column “b” column. At this point the data has been written in the XML file.

 

 

I also changed the name of the table ‘TestTable “(as seen above). This is not what will be seen when viewing the XML file. All the stories get their own identification number associated with them. You can change this if you want, but it is only accessible / visible in the XML structure and has no impact on the name you give the table in Excel. Because of this, I recommend not to change the ID of a table.

 

Change the file type

 

With closing the file (must be saved), you can change the type extension XLSX file from ZIP.

 

 

 

 

 

 

 

You will be prompted to make sure you really want to do this, click Yes.

 

 

View of the XML structure 

 

Each table has its own structure of XML file /. When you open the zip file (double-click) it is opened in a Windows file explorer (folder) of the window. Navigate to the “XL” and you will find a “tables” subfolder. Open it and you will see a list of XML files, one for each table in the workbook. In this example, I created a single table.

 

 

 

If you open the XML file from here (in the zip file), it will be read-only mode. The simplest method is to drag and drop the file (I use the desktop for ease of use and speed) and open it from there. As it is a zip file, it will create a copy and not move the file. When this is done, if you have made changes, you can drag and drop the file again and choose to overwrite the file with your changes.

 

Once you have finished viewing the documents then it is a zip file, close the zip file and change the extension back to ZIP XLSX. You get the same prompt asked if you are sure you want to do this, click Yes. At this point, you can open the file normally in Excel and amendments show.

 

Table Guts

 

When you open the XML file (with Notepad + +, I also use the XML plugin, available free on the Plugins menu for easier viewing as seen below), you will see the structure of the table. There is much information that you can see, including the name of the table, the display name, track number, etc. If you look in the Columns node table, you will see two columns, it is sufficient to id and a name (my first column with manual data, but the second column has a node Formula column calculation of the child. it is the heart and soul of how an Excel remembers your formulas .

 

You can watch and see the formula is slightly different from what I showed above. This is because written in the XML file is the basic format , which are references structured table from Excel 2007. They were not very friendly and were modified in later versions for easier writing formulas. Use the @ symbol for this reference line is not native to Excel 2007. So to keep backward compatibility , the writing XML , regardless of the version ( after 2007) will be saved in the format of structured table reference 2007. Thus you can write a structured reference table for 2007 and 2013 , and vice versa , without a break even if the formulas are different in the formula bar . It is quite genius.

 

You can delete the entire body of data from a table and when you instantiate an array formulas return, and this is how Excel knows what to put . It’s not magic, it’s Excel, and thanks to the members of the Microsoft Excel team , we have the ability to seamlessly calculated columns from one version to another (2007 and ) .

 

In addition, you can modify the formulas here, but it is much easier from Excel .

 

additional Info

January Karel Pieterse has some articles on his website. One of them is how to get and update an XML portion of an Excel (2007 + file format) file.

 

http://www.jkp-ads.com/Articles/Excel2007FileFormat02.asp

 

If you do not see file extensions, you can change the display type of file in your Windows settings. This link shows how:

 

http://windows.microsoft.com/en-us/windows/show-hide-file-name-extensions#show-hide-file-name-extensions=windows-7

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s