An improved feature in Excel 2007 are Excel Tables (previously known as Lists). The purpose of an Excel Table is to aid with managing and analysing data.
Excel Tables allow for easier sorting, filtering and formatting and interpretation of your data.
They have some features that I think are MAGICAL, so keep reading to see why you should be using them to save time and make your workbooks easier to use days, weeks and months after you created them.
NOTE: Excel Tables are not to be confused with Data Tables that can be used for what-if analysis.
Enter your email address below to download the sample workbook.
How to insert Excel Tables
They typically only have column headings, with no blank columns within the table.
To insert an Excel Table select a cell anywhere in the data range you want converted to a table and press CTRL+T.
 |
In the example below the Table begins with the headers on row 3. Rows 1 and 2 are not part of the Table.
 |
Magical Feature 1
Once your table is inserted you will see that formatting is applied automatically and a new tab will appear on the ribbon called ‘Design’.
Here you can choose from 61 different built in styles to change the format as I have done above, or create your own in line with your branding.
 |
If you don’t want to work with your data in an Excel Table but you like the instant formatting it provides, you can convert the table to a regular range while keeping the style formatting you applied.
You’ll find this option on the Design tab of the Ribbon, or simply right click anywhere in the table and select Table/Convert to Range.
Magical Feature 2
Insert a Total Row. Whoop-dee-doo I hear you say. Ah, but this isn’t any ordinary Total Row.
 |
OK, I agree it looks pretty ordinary so far, but take a look at the picture below. Simply select a different function from the drop down list, or select More Functions to enter something else.
 |
- The Total formulas don’t have to reference the Excel Table; however it’s unlikely you’ll need to do this.
- You can toggle the Total Row on and off and Excel will remember the functions that exist.
- As you add rows/columns your Total Row will automatically update to include the new data.
- The Total formulas use the SUBTOTAL function so that if you Filter your table the Totals will only SUM the visible cells.
Magical Feature 3
Insert a formula in a new column.
1) Type in your new heading immediately after the last column and the Table will automatically incorporate the new column in the Table range and apply the formatting.
2) Tip: Format the cell before you enter your formula.
3) Enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.
4) Add rows and your formula will AutoComplete to fill the new rows.
5) Any changes you make to the formula will automatically propagate to all rows.
6) Notice the formula in the formula bar below does not refer to cell references. Instead it uses what Excel calls ‘Structured Referencing’ which is very similar to Named Ranges. Read Magical Feature 4 below for more on this.
Excel Tables 2007 Syntax:
Excel Tables 2010+ Syntax:
Magical Feature 4
Working with Formulas in Tables
When you insert a Table Excel automatically gives the Table and each column a Structured Reference, similar to a Named Range.
This Structured Reference automatically updates as you add or remove data.
The name for the table is usually something like ‘Table1’ and the columns will have names based on the column headings in the table. If you don’t have any column headings Excel will automatically insert headings like Column1, Column2...and so on.
As you add or remove columns the Structured References automatically update.
Renaming Structured References
You can rename the default ‘Table1’ name on the Design tab in the Properties section.
 |
To rename the columns simply type a new name in the headers.
Table Formula Shortcuts
To reference:
- The whole table =Table1[#All]
- The headers =Table1[#Headers]
- The entire column = Table1[[#All],[Column1]]
- All data (i.e. not including the headers) in the table =Table1[#Data]
- The header value of one column =Table1[[#Headers],[Column1]]
- The current row in Excel 2007 it's: =Table1[[#This Row],[Column1]] or in Excel 2010 onwards it's simply =@Column1
These Structured References (Table1, #Headers, #Column1 etc) can also be combined e.g.
=Table1[[#Headers],[#Data],[Column1]]
......and so on.
Note: If you renamed your Table you will replace ‘Table1’ in the formula with your Table name. Likewise you will use your column names if they aren’t the default Column1 and so on.
Using Structured References in Formulas:
- Sum the Salary column =SUM(Table1[Salary])
- Average the Salary column =AVERAGE(Table1[Salary])
- Sum the whole Table =SUM(Table1)
Note: the above formulas exclude the header cells.
- Multiply Salary by 0.1%
- Excel 2007 formula: =Table1[[#This Row],[Salary]]*0.1%
- Excel 2010+ formula: =[@Salary]*0.1%
Note: [#This Row] or [@row] for Excel 2010 onwards is indicating that the formula is applied to each row individually as in the example below.
Excel 2007 Syntax:
Excel 2010+ Syntax:
Getting to grips with using Structured References in formulas takes a little practice, but just imagine how much time you will save once you can write a formula from anywhere in your workbook without to-ing and fro-ing back and forth between sheets.
Important things to know about Structured References:
- Table names are unique to the workbook. If you have 5 Tables in a workbook on many sheets, each table will have its own name which you can reference from any sheet.
- You can’t have a Named Range with the same name as a Table or Column.
- You will find your Table names in the Name Manager alongside your Named Ranges.
- If the table name or columns names are changed the formulas will automatically update with the new names.
- Structured References are handy when working with data validation drop down lists, PivotTables, and anything else dependent on the Table range like formulas etc.
Magical Feature 5
They automatically insert Filters.
I’m not going to go into how to use Filters here, but for those of you who know how to use them, you'll be pleased to know that all formulas used in the total row automatically exclude any rows hidden by the filter.
And any new rows added are automatically included in the total.
Absolute and Relative References
Unfortunately you can't use the F4 key to apply absolute references to Structured References in formulas. They have some quirky rules depending on whether you're copying and pasting a formula or left-clicking and dragging it. More on working with absolute references for Table Structured References.
What do you like about Excel Tables? Are there any features that you find annoying?
Did you like this tutorial or could it have been better? Let me know your thoughts in the comments below.
| Please share this with your friends and colleagues on Twitter, LinkedIn, Google+ and Facebook etc. |
Paul Laba says
I’m a big fan of Excel tables and their structured referencing. Much more intuitive than R1C1 style addressing when referencing table rows and columns.
But one thing I *dislike* about structured referencing is that, when I copy a formula from one table column to another, and the source column includes a structured reference to another column, that reference doesn’t change in the target column as it would with relative references.
For example, suppose I have a table containing month columns (Jan, Feb, … Dec), and the Jun column contains the formula =1.1*[@May]. If I copy that formula to the Jul column, I end up with the same formula (=1.1*[@May]), not =1.1*[@Jun] as I might expect.
That’s because structured references are, by definition, absolute. If relative references are desired in a table, they must be the standard R1C1 style addresses.
I wish Excel provided a way to tag a structured address as relative, similar to how I can use $ within an R1C1 style address to tag a row or column as absolute. I could then copy formulas from one table column to another with the expected (relative reference) result.
Catalin Bombea says
Hi Paul,
Strange, by default, table structured references are relative, not absolute, the column name should change when you copy the cell to the right.
You can make structured references absolute, here is a tutorial: excel-table-absolute-structured-references
If you want to prevent the formula to change the column name when the formula is copied across, the absolute reference should look like this:
=1.1*Table1[@[May]:[May]]
Catalin
Paul Laba says
Sure doesn’t work that way for me. When I *copy* a formula from a table column containing a structured reference to another table column, the column name reference *does not change*. That means, at least in the case of copying/pasting, structured references are absolute, not relative.
Apparently, as I just discovered, if you use a cell’s handle to drag it’s formula to another cell, any structured references in the formula *is* treated as relative, and changes accordingly in the target cell.
That’s wildly inconsistent. R1C1 type references certainly don’t behave that way: if a cell’s formula includes a relative R1C1 address (no $ prefixing the row and/or column), copying or dragging that cell to another cell produces the exact same result. Why shouldn’t structured references behave the same way?
In my opinion MS made a mistake here. Structured references *should* be relative by default, just as R1C1 style addresses are relative by default, and should produce the same result whether the cell is copied or dragged.
Catalin Bombea says
Hi Paul,
There is indeed a difference between copying and dragging a cell with a formula in a table, but it’s not the only difference between normal excel cell references and structured references. For example, there is no switch to make the reference absolute in a table, like the $ sign in a normal cell reference.
Can’t tell if this is a mistake or not, a table is a complex object, it’s hard to transfer all normal operations to a different type of object.
Just knowing these differences, should be enough for you to decide what operation you should do, right? 🙂
Catalin
Burdette says
Love the column chart… Like to know how you combined the 3 columns (Data, Max, Min) into one for the chart. Also how did you get the column color and only the correct data label to follow – did you use Conditional formatting?
FYI – I did add different numbers to the data and the column (line chart did also) came out correctly.
If you do not have time to reply, I understand but these are something that would be very good in the classroom and in business.
Mynda Treacy says
Hi Burdette,
For the benefit of others reading this, your question is referring to the charts on this post: https://www.myonlinetraininghub.com/label-excel-chart-min-and-max
The Data, Min and Max columns are set to overlap as per step 3: Overlap columns; right-click any column > format data series > Series overlap 100%, Gap width 60% (or whatever you prefer).
The colors are set at the series level as per step 4: Colour code min/max; left click the max column > format the fill colour. Repeat for the min column.
Kind regards,
Mynda
Arif says
Excellent explanation
Mynda Treacy says
Thanks, Arif 🙂
Akhil says
Hi Mynda,
I am trying to create a structured reference using this formula – “=LEFT(C2, LEN(C2) – 3)”. Please could you help me with this?
Table name = P01table
Column specifier = name
Item specifier = #Data
Thanks a ton!
Catalin Bombea says
Hi Akhil,
If you type this formula in the same table in another column, excel intellisense should automatically enter the table reference if you select a cell from the table
=LEFT([@name], LEN([@name]) -3) should be the formula.
If the autocomplete feature does not work, check your settings from File-Options-Formulas-make sure the option: Use table name in formulas is checked.
Akhil says
Hi Catalin,
Thanks so much for your help – that worked absolutely perfectly!
Catalin Bombea says
You’re welcome 🙂
Bill Palmer says
Hi Mynda,
This seems like a simple problem, but I’m struggling. Maybe you can help.
I’m using a userform for data entry, with a listbox tied to a named range (MainDB). When I add a new record, I can see the database update, but the list does not update properly until I exit the form and return. Is there a way to refresh the listbox data without exiting? Should I convert the named range to a table?
Thanks for any help you can provide.
Bill
Catalin Bombea says
Hi Bill,
Usually, that listbox is populated when the form is activated. If you add new values to the source of the list, you should write code to update that listbox (use a new procedure to update list: clear existing entries and use the same code you have for populating the listbox again)
To clear the listbox: listBox1.Items.Clear()
Catalin
Peter Buyze says
Excel Tables are 1 of those fabulous features, I use them almost invariably now.
Nevertheless, there is 1 thing that one should bear in mind. Before setting up an ET or converting an existing data set to an ET, one should carefully consider the following.
When a new row of data is added to the ET, the AutoComplete is done based on what is in the 1st row of data. If, after setting up an ET one wants to add a new row to act as row 1, hoping to have that new row update existing rows & fill in new rows at the bottom, it won’t work, i.e. it does not update.
One could convert the ET back to a normal table, then reconvert into an ET, but that only sometimes do the trick. I have not come across a satisfactory solution for this.
In my opinion the best way is to carefully plan beforehand what data should be in row 1, then stick with that.
Mynda Treacy says
Indeed, Peter. Tables are designed with consistency in mind. Therefore the formula in row 1 should be the same for the entire column.
Mynda
Paul Richardson says
checking it out now
Stephen says
I’ve recently found your site and guidance; they’re excellent and very informative. Congratulations.
I have a question about remote access to Excel Tables. I’m using MS Office Professional Plus 2013.
In one workbook I have a number of data tables (the Source). Some of these have been formatted as Excel Tables, some as named ranges.
A separate workbook has been configured as an Excel Template from which I generate a number of different workbooks that look up data from the Source dependent on a drop-down constrained list. The setup works really well and the newly generated workbooks populate properly and update when the Source is changed and when they are opened or forced to refresh data.
EXCEPT I’ve just discovered that the data is not looked up if the Source is not open. Look ups to the named ranges work whether or not the Source is open. Look ups to the Excel Tables do not. If I have a generated workbook open when the Source is closed the looked-up cells are blank (due to IFERROR). As soon as I open the Source, the blank cells populate properly and immediately.
Is this something that is known or designed, or do I need to start error tracking? I’m using Excel Tables because the data in the Source is dynamic. If I can’t use Tables I suppose I’ll have to go back to OFFSET.
Thanks for your help.
Stephen
Catalin Bombea says
Hi Stephen,
Welcome to MyOnlineTrainingHub, glad to hear you like our materials.
What “Lookups” are you using? VLOOKUP or INDEX-MATCH?
I would extract data from the Template with Power Query, this way you will no longer have external links, and you will still be able to refresh data from Template. It’s a much stronger setup, in my opinion.
Cheers,
Catalin
Cavs says
Hi Catalin,
Thanks for your reply. I’ve done some more work on this since I posted and I’ve established that the remote lookups (using INDEX in an Array formula to return multiple matches) do not work when the source is an Excel Table, unless the source file and the destination file are both open on the same computer. The same formulae work when the source is a Named Range, even if the source is not open, and even though the cell references for the Range are identical with the Table. I find that very odd.
I should mention that this is on a corporate infrastructure so clients using the documents might be in different parts of the UK, and all the files are on a central SharePoint server. I don’t think the Excel has Power Query installed and I can’t add it to the corporate installation.
I’m managing the central source index and I can do most of the reporting on my local machine, my issue was just making sure that any time anyone opened one of the destination documents it always contained the current data, looked up as it opened. I’ve achieved that by eliminating Tables, I just have to manage the changes in range myself. When I get time I’ll automate that as well!
Thanks for your help.
Stephen
Catalin Bombea says
Then you can try to bring source data from Data tab, Get External Data section, From Other Sources – From Microsoft Query , from excel files. You will have to browse to your excel file, then select the tables/sheets you want to bring into your file. This data connection is refreshable also.
Cheers,
Catalin
Carter Hoag says
This would be great as a video!
Gord Miller says
So well presented–concise and so easy to read…passing on to my staff. Thanks.
Mynda Treacy says
Thanks, Gord! Glad you found it helpful.
Kamran says
Hi Mynda,
Is there option to sub total in Excel table
Mynda Treacy says
Hi Kamran,
No, you can’t use the Subtotal tool in an Excel Table, but you can use a PivotTable to summarise and subtotal the data.
Mynda
Peter Buyze says
One other nice feature to mention is that when you scroll down a table to the point where the column headers would normally disappear, those headers get “absorbed” in the column in place of the column’s letter designation. That way you don’t actually have to freeze your headers in a long table.
Carl Thørner says
I am not able to download: Excel_Blog_Workbooks.xlsx (https://www.myonlinetraininghub.com/wp-content/moth-practice-files/Excel_Blog_Workbooks.xlsx).
Catalin Bombea says
Hi Carl, try to right click the link and choose: Save link as or Save target as, depending on your browser. Make sure it is saved as xlsx, your browser might change the extension.
Cheers,
Catalin
Dave Bonallack says
Hi Mynda,
I have a Table (Table1) in columns B thru H
I have this formula in K5 =J5+Table1[@Days]
I want to fill across from K5 to BD5
I want J5 to increase as I go (J6, J7 etc) but I want the table reference to stay absolute.
I can do this one cell at a time by selecting K5, then hitting Ctrl+R, L5, then hit Ctrl+R etc
But I can’t seem to do all the cells with a single swish
Can we make table references absolute?
Regards – Dave
Catalin Bombea says
Hi Dave,
You have to duplicate the column name, to make it absolute:
=Table1[@[Days]:[Days]]
You will find more info here: excel-table-absolute-structured-references
Catalin
Dave Bonallack says
Ah, OK, thanks. To create the original formula, I typed ‘J5+’ then clicked on a cell in the table. Is there some way the absolute table reference can be entered by clicking, or do I have to type it in manually?
Dave
Catalin Bombea says
Unfortunately, yes, you have to type it manually, there is no shortcut for this.
Catalin
Dave Bonallack says
Ok Catalin, thanks for your help.
Rob says
Thanks for the information. Well done.
However it’s a pity that you don’t describe how to turn off Microsoft’s ridiculous idea of automatically naming columns column1, column2 etc.
It would also help if you explained how to use Table Formula Shortcuts, such as Table1[#All] or one column =Table1[[#Headers],[Column1]]
Catalin Bombea says
Hi Rob,
The automatic naming is not optional, it cannot be turned off. Power Query and Power Pivot will also give automatic names to new columns if you don’t provide one, excel cannot work with columns with no names…
The Table Formula Shortcuts should be used like any other range reference in a formula, they are structured references to excel ranges.
For example, if the range A1:C10 contains an excel table, where row 1 is the Headers row (with these column Names: Agent Name, Month, Sales), and you want to sum the data from column C, a formula will look like this: =SUM(C2:C10)
With structured references, the formula will look like this: =SUM(Table1[Sales]). Structured referfences will make formulas more readable, because you can actually understand what the formula will sum, without the need to go to the range of cells C2:C10 to see what is in those cells.
Note that you refered to a table column like “one column =Table1[[#Headers],[Column1]]”. The expression is refering to a single cell, in headers row, column 1, not to the entire column 1. A reference to a column will look like Table1[Column1], or Table1[[#All],[Column1] (this last expression includes the column name, not only the data range)
Cheers,
Catalin
Le Hung says
This is not new nowadays however like it as easy to share and coach my colleagues. Thanks
Mynda Treacy says
Cheers, Le.
You’d be surprised how many people haven’t heard of Excel Tables. Microsoft esimate only 1% of Excel users know of and use Excel Tables!
Spread the word 🙂
Mynda
justine dougherty says
Hey Mynda,
I put the formula in as above BUT this is what it looks like on my sheet
=[@Salary]/Table9[[#Totals],[Salary]]
WHY would that be?
Thanks
Mynda Treacy says
Hi Justine,
In Excel 2010 the way you reference a row changed to =[@row], whereas in Excel 2007 it is =Table1[[#This Row],[Column1]].
I’ve updated the post above to include this update.
Kind regards,
Mynda
Hemant says
Download the workbook and practice what you learn. (https://www.myonlinetraininghub.com/wp-content/moth-practice-files/Excel_Blog_Workbooks.xlsx) is not working.
Unable to download
Mynda Treacy says
Hemant,
I tested it and it works for me. It may be your browswer. Please rignt-click the link > Save As > choose your folder you want to save it in. Make sure the file extension is .xlsx
Let me know if you’re still having problems.
Kind regards,
Mynda
Faridz Ridzuan says
Thank you very much , now everything starts to make sense in my brain…;)
Mynda Treacy says
Glad we could help, Faridz 🙂
Lindsey Delaney says
thank you! I have read up on tables since I watched you Dashboard training a few weeks ago, but this was a great summary!
Mynda Treacy says
Thanks, Lindsey. Glad you’re making use of Tables 🙂
Martin Stephenson says
I am working with the instructions above however i cannot understand the comment “Tip: Format the cell before you enter your formula. in Excel Table Magic feature #3. can you help please?
Mynda Treacy says
Hi Martin,
Point #3 explains that when you enter the formula and you will see that the table automatically copies the new formula and formatting down the whole column to the bottom of the table. This is known as AutoComplete.
I hope that clarifies things. It doesn’t really matter if you format before or after entering your formula but I find it’s quicker to select one cell to format than a range of cells that might expand past the visible page. I prefer not to format entire columns as it can bloat your file size.
Kind regards,
Mynda
Miffy says
Seriously. Before finding your site, I had considered myself a bit of an Excel guru. Now, I feel like I don’t even know a fraction of what there is to know about Excel. Fortunately, because of my background, your webinars & tutorials are very easy to follow and apply immediately. Working in Process Improvements and Change Management, I can use the new features I am learning about on a daily basis to inform leadership and monitor our progress.
Thank you!!!!
Mynda Treacy says
Thanks, Miffy. I’m glad you’re enjoying our site and finding loads of useful tips.
Mynda
Tayyab Bhutta says
Hi Mynda,
This tutorial is excellent and fulfills all the required information that is necessary for the creation of tables and insertion of Data along with Formulas. I really admire your endeavoring that are helpful people like me and I am really thankful and grateful for your kindness with respect to spreading knowledge.
Always stay blessed.
Mynda Treacy says
Thanks, Tayyab. I’m glad you found this tutorial helpful 🙂
suzanne eikenberry says
Hi Mynda,
Magic Feature #3 is not working for me when I download your practice sheet. Is it possible it is because I am using Excel 2013? I had to manually add the new column to my table. When I added the formula, I had to right click on the cell to get the option to copy down.
Second question on the extra column, I created a % of total by writing the formula: =[@Salary]/table1[[#Totals],[Salary]]. However, if I want to change the total line to maximum then the formula changes to % of maximum. Is there an easy way to keep it constant even if I change the way I look at the total?
Mynda Treacy says
Hi Suzanne,
Magic Feature #3 should work in Excel 2013. The trick is to put the formula in the very next available column. If you skip a column Excel will not include it in the table.
To fix your % of total change your formula to this:
=[@Salary]/SUM([Salary])
Kind regards,
Mynda
Moshe Slonim says
Hi Mynda,
This is just a thank you note.
I have been exposed to your emails since I took Dashboard webinar.
The webinar as well as the emails are very clear and helpful.
All the best
Moshe
Mynda Treacy says
Thank you, Moshe 🙂
I’m glad you’re finding our training helpful.
Brian Crawford says
I do Excel development using a lot of VBA and while I only use tabular data (thought you’d appreciate that) I’ve not formally used Tables before. I have an upcoming project where they might be appropriate but it was not clear from the article if or how to reference the Table data using VBA.
Could you comment on that or point me in an appropriate direction to get more information
Some great material on your web site, clear and easy to understand.
Thanks
Brain
Catalin Bombea says
Hi Brian,
The syntax to refer to table objects is quite simple:
-To refer to an entire table:
Range(“TableName”)
-To refer to a column:
Range(“TableName[ColumnName]”)
-To refer to Headers:
Range(“TableName[#Headers]”)
To easier manipulate the table, you can refer to table components , using ListObjects method:
Sheets(“Sheet1”).ListObjects(“TableName”).HeaderRowRange is identical to Range(“TableName[#Headers]”)
More info you can find at:
the-vba-guide-to-listobject-excel-tables
Cheers,
Catalin
Ozen Kandirali says
Hello Mynda,
I take your dashboard online course. I do follow the links you put to understand better the subject. Here it says download the data but it doesn’t work. I wonder I do some mistakes? I have mac 2011 excel.
Thank you for respond in advance
Ozen
Mynda Treacy says
Hi Ozen,
I suspect your PC/Browser is changing the file extension when you download it. The file is a .xlsx file so please ensure when you right-click then File Save As that the file extension is .xlsx and if not please change it. You should then be able to open the file like any other Excel file.
Please let me know if you still have problems.
Mynda
Ozen Kandirali says
Hi Mynda,
I really really appreciate your messages and my words are insufficient to explain my thanks. I thank you many many times.
I will tell you how I solved the issue. My problem was with the link (Download the workbook and practice what you learn) on above the page. When I tried to download the link I was getting a page with written an ascii code. It was not an excel file. So I copied the link in an excel worksheet and then I tried to open the link. Yay! Here the excel file is. It works well.
My macbook might cause the problem, I don’t know what the reason was. Anyway it works well and I am on my way to learn from your excel pages.
Thanks again for this great job you do.
Mynda Treacy says
Hi Ozen,
It sounds like you were originally left-clicking (or the equivalent on the Mac) the link and your browser was trying to open the Excel file. Whereas you should be right-clicking the link and saving the file as an Excel file and then browsing to the file location to open it.
Not to worry. The main thing is you have managed to open it.
Kind regards,
Mynda
Mynda
Wendy Farkas says
This was great! I had Excel for Mac 2008 at home for several years while I was working with Excel 2007 in Windows at work. Have since upgraded to Excel 2011 at home. Am slowed down a bit by having to “translate” concepts between the platforms, but it was very helpful to learn about Excel tables. In particular, I want to incorporate Structured References into my current project.
And those Magical Features – they ARE just that! It might be nice to see these illustrated in a future video.
What’s been most helpful is to be able to listen to your Dashboard tutorial, stopping every few slides to read your related tutorials (like this one) and work through the downloadable workbook. Your Raw Data sheet was already formatted when I opened that file, so I brushed up on removing the table formatting and filters, finding the formula for selecting the data range with a keyboard shortcut, and then creating the table (which is invoked with CTL+L on the Mac). I do believe I’ll catch up soon.
My evening’s entertainment, but very gratifying. Thanks!
Mynda Treacy says
Hi Wendy,
I’m so pleased you’ve found this tutorial helpful and are excited about using Tables. The practice you’re doing will be well worthwhile.
Kind regards,
Mynda
Des Lavender says
So, if you start your fomula with a – it will automatically return the result as negative number. Not what people might intend!!
Mynda Treacy says
Correct, Des. Likewise if you begin the formula with a +. In Excel the – is a minus sign in every numeric context unless text.
Kind regards,
Mynda
Des Lavender says
Hi there, this is brilliant!! Just a couple of things:
1) Because the NHS is poor, very poor, I’m still running Windows XP and the (I’m guessing) screen shots are miniscule and can’t be seen.
2) The link to the /moth-practice-files/Excel_Blog_Workbooks.xlsx just enabled me to download a .zip file
Mynda Treacy says
Hi Des,
Are you referring to the screenshots on the blog post above? If so, they aren’t miniscule when I look at them so there must be something on your PC causing the problem.
The download file is a .xlsx but your browser is changing the extension on download. Download the file again and at the File Save As screen type over the .zip extension with .xlsx and you will be able to open the file as a regular Excel file.
Kind regards,
Mynda
Susan Slinkman says
One snag I just ran into with tables is that if you protect the sheet, the table won’t add new rows for the user. One way to get around that was to use data validation (instead of protecting the sheet) that would keep users from overwriting important formulas but then you get those ugly triangles all over your sheet. I ultimately had to take away the table for that project – it was a shame because it worked so well as a table. Susan
Mynda Treacy says
Hi Susan,
Yes, unfortunately that is a limitation with Tables. We’ve got a blog post on a VBA solution to that problem coming soon.
Mynda
Lisa Pederson says
Hi Mynda,
I have an Excel table set up in one workbook and am trying to do a SUMIFS function in a different workbook using some of the structured ranges in the table. I can’t seem to be able to make it work, so am wondering if the table functionality and structured ranges are available only within the same workbook?
If I set up the SUMIFS function within the same workbook I can type “Table1[” and once I type the [ I get a list of the available structured ranges. This doesn’t happen when I begin the function in a different workbook and then click over to the workbook with the table and begin typing “Table1[“.
I’m trying to practice using tables so that I become more familiar with them. Finding out about these and how they work has been a real eye-opener. 🙂
Thanks,
Lisa
Mynda Treacy says
Hi Lisa,
The easiest way to reference cells in another workbook is to start your formula in workbook 1, that is type in =SUMIF( then with your mouse navigate to the other workbook and select the range of cells in the table.
Excel will fill in the necessary file name, sheet name and table references. It should look something like this:
=SUMIF(‘[Workbook 2.xlsx]Sheet1’!Table1[column_reference]
Kind regards,
Mynda.
Allen Reidhead says
The information is excellent, well directed, concise and orderly! Thank you.
Mynda Treacy says
Thanks, Allen. Glad you found it helpful 🙂
Ru says
Thank you for sharing! It has always been helpful.
In colume 1, why is there a “+” sign right after “=”? The results seen to be the same with or with the “+”.
Mynda Treacy says
Hi Ru,
When you enter a formula in Excel you can start it with + or – and when you press ENTER Excel will automatically add the = sign for you. I sometimes write my formulas starting with a + since it’s on the numeric keypad and easy to press…. saves me reaching all the way over to the equals sign 🙂
Kind regards,
Mynda.
Henk Stander says
Hi Mynda, I am unable to download the practise file. It downloads gibberish.
Thanks again.
Mynda Treacy says
Hi Henk,
The download file is a .xlsx file. It sounds like your browser is changing the file extension on download. You need to download it again and make sure that the file extension in the ‘Save as’ (or equivalent for your browser) is .xlsx before saving it.
So, right click the link > choose ‘Save file as’ or ‘Save as’ or similar > choose a file location and change the file extension to .xlsx
Kind regards,
Mynda.
Gary E Travnicek says
Excellent tutorial.
Please correct the inconsistency of 10% versus .1% in the formula and/or the table:
Multiply Salary by 10% =Table1[[#This Row],[Salary]]*0.1%
Mynda Treacy says
Good spot, Gary. Thanks. I’ve fixed it now.
Cheers,
Mynda.
RAMNEEK SHARMA says
its great,thanks for helping me
Mynda Treacy says
You’re welcome, Ramneek 🙂
Doug says
Thanks, your articles are so helpful. Understanding tables is going to make everything so much easier.
Mynda Treacy says
Cheers, Doug 🙂 You’re welcome.
Lyn Stevens says
Great, at last someone has given me a simple explanation of what an Excel Table is and how to use it. Thankyou
Mynda Treacy says
🙂 You’re welcome, Lyn.
Oyller says
Magnificent! I’ve been using excel 2003 till very recently. It is a real magic. Thank you so much for sharing this great discovery to the world 🙂
Mynda Treacy says
Cheers, Oyller 🙂 In Excel 2007 Tables got a make over and are a lot more user friendly. Enjoy.
Sajid Mahmood says
is there any option or formula to convert digit in words,
for example i wirte a amoutn 47,587/- in a cell, i want to see this amount in words in a seperate cell,
is it possible???
Mynda Treacy says
Hi Sajid,
Every now and again this question comes up. Yes, it is possible, but it’s so complex it’s not really worth using. Daniel Ferry, MVP and Excel Hero blog owner came up with this solution:
=REPT(INDEX(nwords1,MID(TEXT(A1,n0),1,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),1,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),1,3)>99)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),2,2)>19)*MID(TEXT(A1,n0),2,1)+1)&REPT("-",(--MID(TEXT(A1,n0),2,2)>20)*(--MID(TEXT(A1,n0),3,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),2,2)-8)*(--MID(TEXT(A1,n0),2,2)>9)*(--MID(TEXT(A1,n0),2,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),3,1)+1)*((--MID(TEXT(A1,n0),2,2)<10)+(--MID(TEXT(A1,n0),2,2)>19)))&" million ",(LEN(A1)>6)) & REPT(INDEX(nwords1,MID(TEXT(A1,n0),4,1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),4,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),4,3)>99)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords3,(--MID(TEXT(A1,n0),5,2)>19)*MID(TEXT(A1,n0),5,1)+1)&REPT("-",(--MID(TEXT(A1,n0),5,2)>20)*(--MID(TEXT(A1,n0),6,1)>0))&INDEX(nwords2,((MID(TEXT(A1,n0),5,2)-8)*(--MID(TEXT(A1,n0),5,2)>9)*(--MID(TEXT(A1,n0),5,2)<20)))&INDEX(nwords1,(MID(TEXT(A1,n0),6,1)+1)*((--MID(TEXT(A1,n0),5,2)<10)+(--MID(TEXT(A1,n0),5,2)>19)))&REPT(" thousand ",--MID(TEXT(A1,n0),4,3)>0),(LEN(A1)>3)) & INDEX(nwords1,(LEN(A1)>2)*LEFT(RIGHT(A1,3),1)+1)&REPT(" hundred",--MID(TEXT(A1,n0),7,3)>99)&REPT(" ",(--MID(TEXT(A1,n0),7,3)>99))&INDEX(nwords3,(--RIGHT(A1,2)>19)*LEFT(RIGHT(A1,2),1)+1)&REPT("-",(--RIGHT(A1,2)>20)*(--RIGHT(A1,1)>0))&INDEX(nwords2,((RIGHT(A1,2)-8)*(--RIGHT(A1,2)>9)*(--RIGHT(A1,2)<20)))&INDEX(nwords1,(RIGHT(A1,1)+1)*((--RIGHT(A1,2)<10)+(--RIGHT(A1,2)>19)))Kind regards,
Mynda.
Sonia says
I am creating a database if you will that shows ALL temps on one sheet.
then on the 2nd and 3rd sheet I would like to have terms on one and active on the other. I dont know what would work best to have the terms automatically extracted from sheet one into sheet 3 once term date has been entered on sheet one. Can you help please
Mynda Treacy says
Hi Sonia,
It sounds like you could use a VLOOKUP formula with multiple criteria.
Kind regards,
Mynda.
NaeemShahzad says
please solve my problem
i am working in library my problems is a man take a book for rent.
book issu time 8:00 am and book return time 3:00 pm so reader book reading in 2days. what i can use excel formula this condition. and tell me time in 24 hours. tell me how much time he was reading book total time in24 hours.
i shall be thank full for solve my problems.
Mynda Treacy says
Hi Naeem,
In one cell enter the borrow date and time like this:
1/10/2012 8:00:00 AM
and in another cell enter the return date and time like this:
3/10/2012 3:00:00 PM
In a third cell enter a formula that takes the return date & time less the start date and time and format the cell to a custom number format [h]:mm
Answer is 55:00
Kind regards,
Mynda.
NaeemShahzad says
very nice i like your post.
Vaibhav says
Thanks
All your tutorials are really helpful, once again , thanks a ton !
Mynda Treacy says
Cheers, Vaibhav!
Hamdullah says
nice
Mynda Treacy says
Cheers, Hamdulla 🙂
oliver says
Excellent job highlighting the benefits of Excel tables in 2007. I also learned a few things about strucured references too! I’ll be certain to include this topic in my next Excel training class.
Mynda says
@Oliver. Thanks.
Those familiar with earlier versions of Excel are likely to never discover them…and that would be a shame. Spread the word!
aromatherapy says
Gosh, I’ve been looking about this specific topic for about an hour, glad i found it in your website!
Peter says
I’ll have to try using these in my financial analysis spreadsheets.
Peter