Most Valuable Professional


View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.

Speed up your file

FastExcel
The best tool to optimise your Excel model!
Home > English site > Articles > Excel Tables
Deze pagina in het Nederlands

Working with Tables in Excel 2013, 2010 and 2007

Introduction

This article has been published on the Microsoft site (in Dutch)

With the release of Excel 2007, Microsoft has introduced a new concept of working with tables of data. This new functionality is (not surprisingly) called "Tables". In fact, Tables in Excel 2007 are the successor of Excel 2003's "List" feature, with added functionality.

This article introduces you into the concepts of working with Tables in Excel 2013, 2010 and 2007 and shows you how they may help you in your everyday Excel use.

Creating Your Table

Creating a table in Excel is easy. Of course you already have some data available somewhere on your sheet. Select the cells that contain the data:


Figure 1: Select the table area

Next, on the Home tab of the ribbon, find the group called "Styles". Click on the button that says "Format as Table" (see figure 2):


Figure 2: "Format as Table" button on the Styles group of the Home tab.

After clicking this button, Excel shows a new user interface element called a gallery, with a number of formatting choices for your table, see figure 3:


Figure 3: Table format gallery.

Select one of the predetermined formats. After clicking one of the formats, Excel will ask you what range of cells you want to convert to a table (see figure 4). If your table contains a heading row, make sure the checkbox is checked. Click OK to convert the range to a table.


Figure 4: Dialog asking what range of cells has to be converted to a table.

After you’ve finished these steps, your table will look like figure 5.


Figure 5: Range of cells, after converting to table

Special functionality of a Table

After defining a table, the area gains special functionalities:

1. Integrated autofilter and sort functionality

If your Table has a header row, it will always have filter and sorting dropdowns in place on the header row. See figure 6:


Figure 6: sorting and filtering dropdowns

2. Easy selecting

Selecting an entire column or row is simple: move your mouse to the top of the table until the pointer changes to a down pointing arrow (figure 7) and click. The data area of that column is selected. Click again to include the header and total rows in the selection.


Figure 7: selecting an entire column of data within your table

You can also select the entire data area or the entire table by clicking near the table’s top-left corner (the mousepointer changes to a south-east pointing arrow, see figure 8).


Figure 8: selecting all data within your table or the whole table is just one or two clicks away.

3. Header row remains visible whilst scrolling

If your table is larger than fits on a screen and you scroll down, Excel 2007 has a nice new feature: the column letters are temporarily replaced with the table’s column names (but only whilst you’re inside the table!). See figure 9.


Figure 9: Table header names on Excel’s column header when scrolling

4. Automatic expansion of table

If you type anything next to a table, Excel assumes you want to expand the table and automatically increases the table size to include your new entry. Of course you can undo this expansion too, or switch off this behavior entirely.

5. Automatic reformatting

When you insert or remove a row (or column) in your table, Excel will automatically adjust the formatting: alternate shading is kept nicely in place.

6. Automatic adjustment of charts and other objects source range

If you add rows to your table, any object that uses your table’s data will automatically include the new data.

Table Options on the Ribbon

Once you have selected any of the cells within the table, you will see a new tab appear on the ribbon, called Table Tools, Design. Figure 10 shows you what the ribbon will look like after you click this tab.


Figure 10: Ribbon after clicking the Table Tools tab.

Each group on this tab is discussed in the following paragraphs.

Properties group

The properties group (see figure 11 below) enables you to do two things:


Figure 11: properties group on Table Tools tab

1. change the Name of the table

The name of a table is used when you refer to cells within the table in a formula.

2. Change the size of the table

Click this control to change the size of your table.

Tools group

This group (see figure 12) has three controls:


Figure 12: Tools group on Table Tools tab

1. Summarize with PivotTable

It is obvious what this control does. After you have created the pivot table, you don’t need to worry about updating the sourcerange of the pivot table anymore. If you add data to your table, Excel automatically expands the source range of the Pivot table to reflect your changes. Of course you still have to refresh the Pivot table to see the results.

2. Remove Duplicates

Another new feature which has been added to Excel 2007. After clicking this control, you are presented with a dialog with which you can select the columns that you want to use to determine whether a row in the table is unique. (see figure 13)


Figure 13: Remove Duplicates dialog

3. Convert to Range

By pressing this button you demote the table back to a normal range. Beware if you do this when you’ve based e.g. a pivot table on the range, the Pivot table’s source range will not be updated and the pivot table cannot be refreshed anymore.

The External Table Data Group

This group (shown in figure 14) is all about the source data of a table and only applies if the data in the table has been imported into Excel using a database- or webquery or a sharepoint list.


Figure 14: External Table Data group on the Table Tools tab of the ribbon

This group has 5 buttons:

1. Export Data

This is in fact a combobutton. If you press it you’re offered two options,

"Export Table to SharePoint List" and "Export Table to Visio PivotDiagram". What these are exactly is beyond the scope of this article.

2. Refresh

Use this combobutton to refresh the external data in your table. If you click the arrow beneath the button, you’re offered a menu which amongst others also includes "Refresh All", with which you can refresh all external data ranges in your file.

3. Data Range Properties

This button can be used to change the properties of the external data you have based your table on.

4. Open in Browser

If your table is a sharepoint list, this button enables you to open a browser window with that list.

5. Unlink

If your table is a sharepoint list, this button disconnects the table from the list.

Table Style Options Group

This group houses the controls which determine how table styles are applied to your table (see figure 15).


Figure 15: Table Style Options group on the Table Tools tab of the ribbon

1. Header Row

When this box is unchecked, Excel removes the header row from your table. The cells of the header row are cleared, but Excel does remember the header. If you type anything into any cell in that now empty row, Excel will not overwrite that information when you check the box again. Instead, Excel will insert a new row to show the header. Cells below the table are then moved down.

2. Total Row

Check this box if you want a total row below your table. Excel will automatically add a sum function below the last column in your table.

3. Banded Rows

Check this box to get alternating shading for the rows in your table.

4. First Column

If you check this box, the first column of your table will be formatted differently from the other columns.

5. Last Column

Formats the last column of your table differently from the other columns.

6. Banded Columns

Check this box to get alternating shading for the columns in your table

Table Styles Group

The last group on the Table Tools tab enables you to quickly change the style of your table (see figure 16).


Figure 16: Table Styles group on the Table Tools tab of the ribbon

Click the dropdown button to the right of the gallery to see all choices available to you. Hover over a particular style to see what your table would look like when you click it. At the bottom of the gallery there are two extra choices:

1. New Table Style

This option enables you to create your own table style.

2. Clear

Use this to remove the table style from your table entirely. Number formats are retained.

Referencing cells in a table (structured referencing)

Excel 2007 introduces a new syntax to refer to cells inside a table. To see how this works, click in a cell to the immediate right of the table, hit the = sign, type SUM( and then click on any cell with data within the table. You’ll get a formula like this one:

Excel 2007: =SUM(Table3[[#This Row];[Discount]])

This syntax has been simplified in Excel 2010 and 2013:

=SUM(Table3[@Discount])

The new naming convention to refer to the cells in your table works as follows:

Table3: The name of your table

[#This Row] in Excel 2007, @ in Excel 2010-2013 : Denotes the data comes from the same row your formula cell is in

[Discount] : The column inside the table

Some other examples:

Description Excel 2007 Excel 2010, 2013
The entire table =Table1 =Table1
The same row in the table =Table1[[#This Row][Discount]] =Table1[@Discount]
Heading of table =Table1[#Headers] =Table1[#Headers]
Entire table (2) =Table1[#All] =Table1[#All]
Table total row =Table1[#Totals] =Table1[#Totals]

Because of this naming convention, you are not allowed to have more than one column inside a table with a specific heading. As soon as you try to type a new heading that duplicates an existing one, Excel will automatically correct the duplication by appending a number to the new column name.

A nice feature of tables is immediately shown as soon as you hit enter: your table is automatically resized to include your formula (Excel has also made up a column heading for you) and the formula is automatically copied down to fill the entire column alongside your data! Both actions may be undone by using the smart tag that appears.

Referring to a table from another workbook

Even tough I mentioned that a table is also stored as a range name there is a peculiarity. The range name points only to the data rows of the table. The header row is NOT included. This means that if you want to create a pivot table on data that is in a table in another workbook you need to use a syntax that differs from the old days.

Normally you would refer to a range name "TableName" in workbook "WorkbookName.xls" using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the range name syntax as the source. Rather you must use this:

WorkbookName!TableName

This will convince Excel that you are pointing to a table and then includes the header rows.

Conclusion

As you have seen, Tables are a great addition to Excel’s features. Most of these features were already part of Excel 2003's List feature. But Excel 2007-2013’s Tables build upon that feature, significantly improving it. The most important benefits are:

Links

If you're interested in VBA, read about Excel 2007 Tables and VBA here.

Ron de Bruin has written a nice add-in to ease working with tables.


Comments

All comments about this page:


Comment by: Jim Henderson (1/12/2007 7:50:26 AM)

Excellent Post Great job of explaining and using the pictures of the ribbon

 


Comment by: Jan Karel Pieterse (1/12/2007 10:26:07 AM)

Hi Jim,

Thanks!

 


Comment by: Alex J (1/12/2007 11:56:03 AM)

Excellent Post. Despite never having looked into Excel 2007 before, the instructions were clear and well explained. The table functionality looks excellent, as well. Are there any VBA changes which deal with the data table?

 


Comment by: Jan Karel Pieterse (1/12/2007 1:30:56 PM)

Hi Alex,

Thanks.
I plan to follow up on this post with a VBA topic on Tables. Not sure when yet though.

 


Comment by: Dave Johnson (1/12/2007 2:37:16 PM)

I was not aware of this Excel 2007 feature. The automatic expansion of the table (including the copying of formulas) is alone worth the price of admission. This will prove useful in many contexts. Thanks for clearly articulating and sharing your knowledge!

 


Comment by: kanwaljit (1/18/2007 5:07:08 AM)

Great...........

 


Comment by: Zeki Sanal (1/24/2007 5:25:15 AM)

Thanks for your help. Information is very timely. It made a diffrence for me in my project. I hope it will make difference to other users too.

 


Comment by: Adnan (2/21/2007 7:24:29 AM)

Owesome post, just learend this feature. Thank you Jan!

 


Comment by: Alexandre Mironiuc (3/2/2007 7:00:53 AM)

Great post! It’s being very useful to me. I’ve got a problem and I can’t solve it. I’ve got 2 tables which are arranged in parallel. Is it possible to create a filter that works just for one table, with no side effects to the other one? Currently when one is filtered, the other one is being supressed (filtered) as well. I wish I’ve made myself clear and hope you could help me on this. Thanks.

 


Comment by: Jan Karel Pieterse (3/2/2007 7:33:39 AM)

Hi Alexandre,

Unfortunately this is not possible, if you want to filter a table, anything next to the table will be affected.

Note that this has been the case for all Excel versions I know of (which goes back to Excel 5).

 


Comment by: Alexandre Mironiuc (3/2/2007 7:49:47 AM)

Thanks Jan

 


Comment by: Andrea (3/14/2007 9:37:38 PM)

If it wasn't for the new format features i think i would have prefered the old list: why Microsoft removed the special line there was at the bottom of list in excel 2003 to add new rows. if you copy and past data from your list that row was great and helped to make the job VERY FAST

 


Comment by: Jan Karel Pieterse (3/14/2007 10:09:11 PM)

Hi Andrea,

Could you elaborate a bit more? What does Excel 2003 do that 2007 doesn't?

 


Comment by: Andrea (3/15/2007 4:59:57 PM)

Try to copy three rows from your table in excel 2007 and copy them at the end of your table (with total row on). Try to do the same with your list in excel 2003 (total row on): you will see that in excel 2007 you have to overwright the total row or add new rows before, in excel 2003 everithing is smooth and automatic due to the special insert row (the one at the bottom of your list with a star)

 


Comment by: Jan Karel Pieterse (3/16/2007 1:03:19 AM)

Hi Andrea,

Well, I tested this both on 2003 and 2007 and both do the same for me: The pasted rows get inserted and the total row moves down, including the new rows in it's total.
I pasted the rows on top of the total row.

 


Comment by: Andrea (3/16/2007 8:14:51 AM)

i am still trying, but if you past just one row it doesn't work. it erase the total row

 


Comment by: Jan Karel Pieterse (3/17/2007 7:27:10 AM)

Hi Andrea,

I see what you mean. One workaround is to NOT hit control-v to paste but to use control-shift-+, that inserts the copied cells. Or rightclick the total row and hit insert, copied cells.

 


Comment by: Andrea (3/17/2007 3:08:22 PM)

A bit tricky! I tried and it works when you get the black arrow on the total. Alright. Any hints how to past a single raw when you filter the table (without removing the filter and without adding a raw)? This will save me lot of time! Hope Microsoft improves copy and past in the tables in the SR1 or at least matches excel 2003 capabilities.
Thanks Jan
Hope to see soon an article about Pivot Tables/Charts

 


Comment by: Jan Karel Pieterse (3/18/2007 5:37:57 AM)

Hi Andrea,

Sorry, no idea's at the moment.

 


Comment by: Tom (4/5/2007 9:29:06 AM)

>Comment by: Jan Karel Pieterse (1/12/2007 1:30:56 PM)
>I plan to follow up on this post with a VBA topic on Tables.

Still planning to do this topic? I am hoping you are, as I have been stumped on being able to create a table, filtering on a specific column for blanks and removing these selected blank rows. I can do it w/ a normal range, but can't figure out once the range is converted to a table in Excel 2007.

 


Comment by: Jan Karel Pieterse (4/6/2007 2:40:13 AM)

Hi Tom,

Yes I am, but business is crazy, so don't hold your breath.

As for your filtering problem: this shouldn't be too hard. You could always NOT format the range as table, then do the filtering and deleting and after that is done convert to a table.

Record a macro whilst doing so should give you a god idea of the code.

 


Comment by: Tom (4/6/2007 7:23:47 AM)

Thanks for the suggestion of NOT formatting as range as a table first, this was my original workaround. Also,tried recording a macro, still couldn't figure it out.

However, I think I figured it out. It appears that with the new tables in 2007 Range.EntireRow.Delete method fails in the range contains more than one area.
Code to explain:
Sub DeleteTableRowsWithBlanksInColumn11()
    Dim table2 As ListObject
    Dim rng2 As Range
    
    Set table2 = ActiveSheet.ListObjects("Table2")
    table2.Range.AutoFilter Field:=11, Criteria1:="="
    
    With table2.Range
        Set rng2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
        'Excel 2003 Range works
        'Excel 2007 fails with "1004 - Delete method of Range failed"
        'if rng2 countains more than one area.
        'rng2.EntireRow.Delete
        
        'workaround
        CCount = rng2.Areas.Count
        If rng2.Areas.Count = 1 Then
            rng2.EntireRow.Delete
        Else
            Dim rng3 As Range
            For Each rng3 In rng2.Areas
                rng3.EntireRow.Delete
            Next
        End If
    End With
    table2.Range.AutoFilter Field:=11
End Sub

Maybe you will have a better solution in your VBA topic, I still look forward to learning from it! If not, at least you will not waste your time investing this issue!

 


Comment by: Avner (5/9/2007 1:20:23 AM)

Hi,
Nice new Excel feature, GREAT explanation.
No need for dynamic range names and freezed panes.

 


Comment by: Misange (9/5/2007 12:25:48 PM)

Hi Jan Karel,
clearly explained and easy to read. Good job.
I could not manage to format my table as I would like :
In the first column item names
in the first line dates (months)
the table contents expenses that should be done on a given date.
I would like to have the first column in gey and then alternate 3 white colums with 3 blue ones. Could not figure it out but maybe this is simple not possible ?
Thanks again for the tutorial

 


Comment by: Jan Karel Pieterse (9/5/2007 9:20:29 PM)

Hi Misange,

Well, thank you for the compliments.

I could not get that kind of formatting either.

 


Comment by: Jan Karel Pieterse (9/5/2007 9:55:32 PM)

Hi Misange,

Had another go at it. Of course this works, as long as you remember to check the "First column" box on the Table styles tab!

 


Comment by: Misange (9/7/2007 4:49:17 AM)


Hello Jan Karel,
I did select the first column box but then I have lets say 1 blue, 2 white, 3 green 3 white, 3 green 3 white colums ... and not 1 blue, 3 white (instead of 2) 3 green...

Another pb : when formatting the table, I chose first line font color=white on a dark background. In the "thumb" image (not sure this is the adequate wording !), It appears correctly, white on a dark background. However when I apply this format on unformatted data, the background is OK but the font color does not change... Any idea ?

 


Comment by: Peter (11/22/2007 4:33:32 PM)

Smashing page, I'll have to get xl2007 though to use it. The comments below were also very interesting.

 


Comment by: Kerry (12/4/2007 12:09:37 AM)

Please help. I am putting my own training material together and have found that once I apply Format as Table, I can no longer create Custom Views? Any reason why?

 


Comment by: Avner (2/14/2008 3:40:34 AM)

Hi,
i noticed that you can copy-paste, and fill (right or left), a formula that refers to some table columns But you cannot drag it to the right(or left): the columns names orders changes in the formula. Is there a way to make the column absolute ($ ,f4 ) ?

 


Comment by: Jan karel Pieterse (2/14/2008 9:40:16 AM)

Hi,

Excel Help says that if you hold the control key whilst dragging the columns should stay unchanged. Oddly enough I cannot reproduce what it says in help!

 


Comment by: Gabor (3/3/2008 1:48:41 AM)

Hi, any hints about how to autoformat a table if I don't need the fancy filtering and summarizing option? My other issue is that how it is possible to use this "Format as Table" function when the number of columns in the header differs from that of the data? For example in the header I have one column for "Size" but in the table I use two columns for this as the quantity and the unit (10 ft)?

 


Comment by: Jan karel Pieterse (3/3/2008 3:52:44 AM)

Hi Gabor,

I think there is little choice: with formatting as a table you get the filtering.

Indeed does a "table" need unique headings for each column, there is no way around that I'm afraid.

However, you can have just the formatting, by first converting the range to a table (which demands column headings) and subsequently reverting the table to a range.

 


Comment by: Gabor (3/3/2008 4:50:30 AM)

Thanks Jan karel,
your suggestion is a good one for me. I can have the formatting but no need to deal with the filtering and other stuff. The process is to use the "Format as table" option then convert the table back to range. The filtering and the annoying columnnames are gone.

 


Comment by: TomasR (3/31/2008 1:30:43 AM)

One peculiar functionality:
If you have a cell which is validated using a list one would think using a tables name (and column perhaps) might work. But no.

To make it possible to use the table you must create a common name that in turn refers to the table and then use the name as the validation list source. Simpler than per Excel 2007 but still one step to much.

 


Comment by: Andrew (11/10/2008 2:13:53 PM)

Was there ever an answer about the absolute table reference? I love using tables, but I spend so much time correcting formulas because I can't make a single table reference absolute. I want some to be absolute and some relative, but I can't find a way to make it happen. Any suggestions?

 


Comment by: Jan Karel Pieterse (11/10/2008 10:57:15 PM)

Hi Andrew,

I checked what happens if you drag the fill handle to the right. On a normal drag, the named columns in a formula stay put. But if you hold the control key during the drag, the column names adjust accordingly (Excel help is wrong there!!). Unfortunately this goes for all named references in the formula. If you need mixed behaviour (some stay put, others do not), you must use "old-style" referencing.

 


Comment by: heather (11/22/2008 8:18:28 PM)

When I email the document containing tables to someone else, I get it back without the tables formatting, they convert to ranges. The other person uses Mac office 2008 and I use Office 2007 for windows. How can we stop losing the table functionality?

 


Comment by: Jan Karel Pieterse (11/23/2008 7:55:04 AM)

Hi Heather,

If Excel 2008 (MAC) doesn't support this functionality, then loosing it during a save from MAC Excel is something you cannot avoid. The best you can do is record a macro when setting up the table in 2007 and run that macro when you get it back form your MAC user.

 


Comment by: Rob (12/4/2008 2:25:20 PM)

Hi Andrew,

One way is to use the indirect Function, say we have a sumif formula and we want the first column to remain constant (absolute):

=SUMIF(INDIRECT("Table1[[#All],[Headers]]"),$H$1,Table1[[#All],[Headers2]])

Hope that helps.

 


Comment by: Charlie Hall (2/12/2009 3:27:29 PM)

Hi Jan,

I have been avoiding XL07 but finally had to bite the bullet for a new client. I like tables - certainly a nice feature, much improved over lists (which I liked as well).

One question - Vlookup has always had the weakness that the column parameter is hard coded and as the table changes this parameter can easily get out of date. I have many creative solutions to this in XL03. I was hoping this might be solved elegantly with tables, and it is improved, but maybe you have a better suggestion.

I have come up with:
vlookup( lookupvalue, tablename, column(tablename[ column_name]) - column(tablename) + 1, range_lookup)

Is there a shortcut to get the column number based on the column_name - without have to adjust for the first column of the table?

Hope so - thanks in advance

 


Comment by: Jan Karel Pieterse (2/13/2009 3:16:45 AM)

Hi Charles,

I would use a different approach by using a combination of index and match:

=INDEX(tablename[DisplayColumn_name], Match(lookupvalue,tablename[SearchColumn_name],0))


Simpler, with the added bonus that the lookup data does not have to be to the left of the displayed data.

 


Comment by: Neuromancer (2/19/2009 4:25:56 AM)

I don't know if anyone else has seen this issue or even if this is an issue. but I doubt it is the normal behaviour

first off I type the numbers 1-9 in The range A2:C4
Add headers Col1 col 2 col3
convert the whole range into a table

in cell d2 i construct a formula which multiplies (c2*b2)+a1
which converts it to
=Table1[[#This Row],[Col3]]*Table1[[#This Row],[Col2]]+Table1[[#This Row],[Col1]]

now if I put a fiter on col1 to show the value 4
and then copy this entire row to another sheet The formula is
or copy the row directly it still shows
=Table1[[#This Row],[Col3]]*Table1[[#This Row],[Col2]]+Table1[[#This Row],[Col1]]

even though it is outside the table it still references it and also in relation to where you paste the row,

so if you paste it into the first row of a new sheet it errors

Personally I don't think Excel should behave like this as it makes it quite annoying to have to ensure that you have to paste the data as just values

When you do this in Excel 2003 with Lists it operates in the fashion you would expect


 


Comment by: Jan Karel Pieterse (2/19/2009 6:21:55 AM)

Hi Neuromancer,

I agree this can be confusing.

Excel 2007 behaves the same way as if you've used range names in your formulas instead of cell references. If you look at it that way, the behaviour is consistent with earlier Excel versions.

 


Comment by: Curt (2/24/2009 6:25:53 AM)

I have a checkbook ledger application in which I use a VLOOKUP function that keys off the MAX(Date) to get the latest check balance. One problem, if I have multiple entries to my ledger on the MAX(Date) column, the VLOOKUP selects the "first" date that matches MAX(DATE) to lookup the balance. I would like the VLOOKUP in my Table to find the LAST_ MAX(DATE) and return that balance. Any ideas?
I would like to find a formula that performs that lookup without using VBA code. Thanks!

 


Comment by: Luke Brunning (3/4/2009 7:20:39 AM)

I've created a new table style in a spreadsheet, is there any way to share this in all my spreadsheets?

 


Comment by: Jan Karel Pieterse (3/4/2009 8:28:13 AM)

Hi Luke,

The simplest way to have it available in all new files is to create a global template:

- Copy the table to a new empty workbook.
- Remove the table entirely
- Make sure the new file is completely empty and contains the number of worksheets you want
- Do a save-as, filetype Excel template (xltx) and save to this location:
C:\Documents and Settings\UserName\Application Data\Microsoft\Excel\XLSTART

Important! use this filename: book.xltx

To get it in an existing file, copy (part of) the table to the new file.

 


Comment by: Luke Brunning (3/4/2009 10:32:58 AM)

Thanks Jan, thats very helpful; especially the tip about getting it into existing files.

 


Comment by: John Kaye (3/17/2009 4:16:27 AM)

Does anyone know how I add another column to a table?. If I can't do this, how do I remove the table function in order to be able to add another column?

 


Comment by: Jan Karel Pieterse (3/17/2009 7:17:37 AM)

Hi John,

You simply type the heading of the new column next to the last column of your table. Excel should expand your table automatically.

 


Comment by: Drew (3/24/2009 6:49:16 AM)

My total row does not sum the numbers it just says 0, I have selected sum from the drop down but it does not recognize the numbers or something. Any thoughts?

 


Comment by: Jan Karel Pieterse (3/24/2009 10:53:11 AM)

Hi Drew,

Excel thinks these are text, not numbers.
Copy an empty cell
Select numbers, select (From hoome tab) paste, paste special, se;ect the Add option.

 


Comment by: Barbara Bianconi (4/1/2009 8:35:51 PM)

I am attempting to create a custom style using Modify Custom Quick Style. I selected the range A1:A8 and clicked the Format as Table button. I clicked Table Style Medium 2 and when the Format As Table dialog box came up I checked “My table has headers” to select it. I clicked OK. Now for the problem: I want to customize my quick style. I would like a bold font style with a black font. I select A2 to activate the table. I click the Format as Table button and right-click Table Style Medium 2 to display the short-cut menu. I click Duplicate to display the Modify Table Quick Style dialog box. I type TableStyleMedium2 – Custom. I select Whole Table then click Format. I change the font to Bold and color to black. I click OK to close the Format Cells dialog box and Click OK to close the Modify Table Quick Style dialog box. I then enter text and it is not bold! Under the Element Formatting in the Modify Table Quick Style dialog box it shows as bold—but it is not! Help! I have also tried checking “Set as default table quick style for this document” but nothing seems to work to bold using the Modify Custom Quick Style. I know I can highlight my table and click bold, but I would like to use the Modify Custom Quick Style. Can you help?

 


Comment by: Jan Karel Pieterse (4/3/2009 7:27:53 AM)

Hi Barbara,

The point is that after creating the new table style, you have to apply the new style to the table separately, using the quick table styles gallery.

 


Comment by: Barbara Bianconi (4/6/2009 11:41:12 AM)

Hi Jan,
Thank you for your reply, I realized after writing the above that the step I was missing was to "Apply (and Maintain Formatting)." This is a terrific site and I'm going to tell my students about it.

 


Comment by: John (4/21/2009 4:47:01 AM)

Hi Jan,

I've got the following question regarding pivottables in MS Excel 2007. I'm trying for hours to find out how I can use a custom defined pivottable format in other documents and other pivottables. According to some other sites it should be possible in the following way:

<Choosing a Default Style for Future Pivot Tables
You can control which style is the default style to use for all future pivot tables on the computer. The default can either be one of the built-in styles or a new custom style that you modified.
In the PivotTable Styles gallery on the Design ribbon, right-click the style and choose Set as Default.>

I tried these steps, but it doesn't work on my laptop. Could there be an issue regarding the fact that I still have got MS Excel 2003 on my laptop or is this a known bug?

Thank you already in advance,
John

 


Comment by: Ramu (5/26/2009 11:23:03 PM)

In Ms excel 2007 i try to create a pivot table. But I found that it does not display the field list. When I select the show/hidden option form the right click and select from the menu it does not show any result. Can anybody help in this regard.

Thanks in Advance


Ramu.A
ramu2k06@yahoo.co.in

 


Comment by: gabriela (7/19/2009 9:16:01 PM)

hey how do i make a graph from a table?

 


Comment by: Jan Karel Pieterse (7/19/2009 9:21:58 PM)

Hi Gabriela,

Just like any other set of cells, you select the columns you want shown on your chart and you go to the insert tab to pick the chart you need from the Charts group.

 


Comment by: gabrela (7/19/2009 10:55:25 PM)

ive tried that but the graph wont show the info i need it to show. it says some random numbers on the side of the graph that go from 0-450 when they should say temperatures from 40-90. how do i change these numbers

 


Comment by: Jan Karel Pieterse (7/20/2009 12:28:32 AM)

Hi Gabriela,

Do you have the right chart type selected? I expect you need an XY-scatter chart.
Also, are your x values recognised by Excel as being numbers, not text?

Select an empty cell and try this formula:
=ISNUMBER(A2)
Where A2 is one of the cells with the x values
The formula should yield TRUE.

 


Comment by: Dan Ling (7/30/2009 12:13:08 PM)

I have a Excel 2007 worksheet named "Tables" with many tables defined and I would like to reference data in some of these tables in other worksheets and formulas. A One-Row table is named "tlCostTypes" and I want a formula in another worksheet to reference the first row of this table by table name and colum name -- how?

I've see some VBA references but how can I do this in a worksheet formula? Formulas are easy, Excel adds the reference automatically in most cases, when you want a whole row or column but how would I find a specific cell in the table usign a formula and not VBA? What about indexing through the Table usign Row,Col numbers or references.

 


Comment by: Jan Karel Pieterse (7/31/2009 6:11:42 AM)

Hi Dan,

If your table contains a single row, then it is simply =TableName[ColumnName]

Look in Excel help for the subject called "Structured references".

 


Comment by: Abd. Ghani Hashim (8/7/2009 1:58:22 AM)

Hi..

I put some cells protection in excel 2007 table, but then i found the table is converted to the normal table automatically. How do i solve this problem? tq

 


Comment by: clive (8/8/2009 3:10:58 AM)

i am a quantity surveyor and i am trying to learn excel 2007
i can only use the very basic elements of it. i have the in depth excel 2007 by bill jelen but i find it difficult to follow his text.I need a book that will suite me and what i want to do. i need to make expanding tables line graphs sort data by value and make a budget table so when i make a claim for my client the value of the claim is deducted away from the budget total.what book would you suggest i get.i live in ireland.

 


Comment by: Michael (8/11/2009 7:55:02 AM)

Hi Guys,
how do i get the 'Column 1' heading that Excel inserts with the drop down menu to disappear? All I want is a quick way to jazz up the table.

 


Comment by: Melissa Fisher (8/26/2009 9:41:32 AM)

I have my data source in a separate Excel workbook and my pivot tables that feed into a dashboard in another workbook. This is designed so that the file size is reduced and I can share the dashboard without the datasource attached. What do I have to do, so that others can just "refresh" the dashboard when I update the datasource. Is it just a matter of storing both the datasource and dashboard in the same network folder? I have tried to have others access the dashboard and refresh it, but they have not been successful. There has to be a way, I just don't know how. Help please?

 


Comment by: Jan Karel Pieterse (9/7/2009 9:23:32 AM)

Dear mr Hashim,

I just tried to reproduce your problem, but my Excel table does not get converted when I protect the sheet.
Do you have any VBA code that may cause this to happen?

 


Comment by: Jan Karel Pieterse (9/7/2009 9:26:55 AM)

Hi Clive,

I'd suggest any book by John Walkenbach. The Microsoft step-by-step series are also quite good for an Excel beginner.

 


Comment by: Jan Karel Pieterse (9/7/2009 9:34:58 AM)

Hi Micheal,

You can tell Excel to omit the table headings in the table tools tab of the ribbon. In the Table style options, remove the checkbox called "Header row"

 


Comment by: Venus (9/8/2009 1:49:32 PM)

Please tell me I'm doing something wrong. If I have a table with 20-columns and I apply the Table Row setting, do I have to go to each and every column to set it to sum? Copy/paste doesn't work since it copies/pastes the original heading.
Thank you, Venus

 


Comment by: Jan Karel Pieterse (9/9/2009 12:30:51 AM)

Hi Venus,

Not sure what you need, do you want the total beneath the table? That is one of the options on the Table Style Options group on the ribbon (called "Total Row").

 


Comment by: Kjones1997 (9/23/2009 7:46:43 PM)

I have a large amount of data in a table and am trying to optimize calculations. When multiplying two columns, is there a difference in saying =[Column A]*[Column B]
vs an explicit structured reference?
=Table1[[#This Row],[ColumnA]]*Table1[[#This Row],[ColumnB]]
Is one faster caclulating?

 


Comment by: Jan Karel Pieterse (9/24/2009 4:11:12 AM)

Hi Kjones1997,

I would not expect a difference, best way of course is to create a test case and time the calculation.

 


Comment by: kjones1997 (9/25/2009 12:07:37 PM)

You are correct. No difference in time of calculation by using #this row.

BTW, I have never used these before now, but here are some great VBA for testing timing of calculations.

http://msdn.microsoft.com/en-us/library/aa730921.aspx

 


Comment by: Emily (10/7/2009 1:47:22 PM)

I've formatted my data as a table, and now I cannot insert new rows or columns. Is there a way around this?

 


Comment by: Jan Karel Pieterse (10/7/2009 9:22:56 PM)

Hi Emily,

You should be able to however. Is there anything next to or underneath the table that might prevent this? Is the worksheet protected?

 


Comment by: DJ Burgh (10/11/2009 10:55:35 AM)

how do I save a table? I have taken my data, created a table and then tried to 'save as' but when I reopen it, it is back in the original worksheet format. What am I missing?

 


Comment by: Jan Karel Pieterse (10/12/2009 12:10:05 AM)

Hi DJ,

Just save the file in any of the Excel 2007 file formats, NOT in Excel 97-2003 file format (those versions do not recognize the new table styles, which is why that gets zapped on save).

 


Comment by: Hickymanz (11/12/2009 11:00:43 AM)

now for something Really useful

How can you write a formula using SUMPRODUCT that references data in the table

This is driving me up the wall,

 


Comment by: Jan Karel Pieterse (11/13/2009 10:42:49 AM)

Hi Hickymanz,

Can you post an example of what you tried?

 


Comment by: Hickymanz (11/13/2009 11:39:04 AM)

I obviously haven't explored the functions of 2007 fully
SUMIFS provide exactly what I need

....
I was trying to do SUMPRODUCT((data[country]="Germany")*data[USD]*(data[Qualified]="Half"))

This does not work in Excel 2007 with Tables
you need to use SUm IFs

 


Comment by: Misange (11/28/2009 8:29:26 AM)

I was asked how we could combine tables with DcountA and other old database formulas. It would allow to have dynamic databases.
DcountA(mytable,field,criteria) cannot work because mytable does not include the reference to the header row.
Any idea to change that Jan Karel ?
I've been reading your pages on tables several times before writing mine for excelabo.net (in french). Thanks a lot for these excellent pages.
Misange

 


Comment by: Misange (11/28/2009 8:59:18 AM)

I answer to my own question!
If you define a name as
mynewtable=offset(mytable[[#headers],[firstcol]],,,countA(mytable[firstcol])+1,countA(mytable[#headers]))

you can use mynewtable in DcountA and use the header of the column as field.

 


Comment by: Jan Karel Pieterse (11/28/2009 11:05:42 AM)

Hi Misange,

Simpler would have been to use:

=Data[#All]

to refer to the entire table with headings.

 


Comment by: Misange (11/28/2009 11:13:40 AM)

!
in french we say "I tried to find noon at 2PM". So simple. Thanks Jan Karel :-)

 


Comment by: Bob (12/17/2009 4:56:18 AM)

Once I found it, I was able to use the consolidated pivot table wizard to create a consolidated pivot table from 2 tables.

But the table headings are not available in the consolidated pivot, so is there a way to fix it? (I tried using including the worksheet name! and the table name and that didn't work.)

 


Comment by: Jan Karel Pieterse (12/17/2009 12:14:05 PM)

Hi Bob,

I honestly have no idea! Try asking on the Microsoft newsgroups, there is bound to be someone there who knows.

 


Comment by: Shaikh Sajid (12/27/2009 4:09:30 AM)

MY REQUEST IS IF ANY CELL CONTENT FIRST LETTER IN CAP'S OTHER LATTER IN SMALL SO HOW TO CONVERT ALL IN SMALL OR IN CAP'S FOR Eg: IN CELL WRITTEN America?

 


Comment by: Jan Karel Pieterse (12/27/2009 6:43:28 AM)

Hi Shaikh,

You can use the LOWER worksheet function for this.

 


Comment by: RD (12/28/2009 1:57:22 PM)

Hi,
I was wondering if you knew how to auto-resize a table?
I have an Excel sheet with a table (ListObject control)
populated from SQL. If I copy several rows and paste to a new row directly below the last table row, the table will not expand to include the copied rows.
Is there an auto-resize method?

 


Comment by: Jan Karel Pieterse (12/28/2009 10:12:20 PM)

Hi RD,

I expect this is because the table is a query table, normally it would expand automatically. Having the query table expand is (IMO) counter intuitive, since the data now is from mixed sources.

 


Comment by: Minouche (1/5/2010 8:36:54 PM)

Hi, i need to know how to remove the table, i can apply the table but cant remove it without removing all the data. please help

 


Comment by: Jan Karel Pieterse (1/5/2010 11:02:03 PM)

Hi Minouche,

Look for the section called "Table Options on the Ribbon"on this page. You need the button called "Convert table to range"

 


Comment by: YM_Saru (1/29/2010 1:47:50 AM)

Hi,

I am trying to resize the tables in a worksheet(excel 2007) from Access vba. It was running successfully at first time. the problem is: if i rerun the code at the same time, getting error 1004 "Method range failed": following is the Code:

Set E1_TmpWsh = E1_TmpWbk.Worksheets.Item("E1 Vergleich")
    Set E1_colrng = E1_TmpWsh.Columns(1).Find("MDC", LookIn:=xlValues)
    cnt = E1_colrng.Row + 1
    If E1_Accrec.RecordCount <> 0 Then
        Reccnt = E1_Accrec.RecordCount
    For Colsetnr = LBound(ColsetSt) To UBound(ColsetSt)
        SZrng1 = ColsetSt(Colsetnr) & cnt - 1
        SZrng2 = ColsetEn(Colsetnr) & Reccnt + cnt - 1
        Shtbnm = ColsetSt(Colsetnr) & "x"
        Set lstobj = E1_TmpWsh.ListObjects(Shtbnm)
        With lstobj
            Set setrng = Range(SZrng1 & ":" & SZrng2)
                .Resize setrng
        End With
        'lstobj.Resize Range(SZrng1 & ":" & SZrng2)
        'E1_TmpWsh.ListObjects(Shtbnm).Resize Range(SZrng1 & ":" & SZrng2)
        Set setrng = Nothing
        Set lstobj = Nothing
    Next Colsetnr
    End If

E1_TmpWbk.SaveAs (curpath & "\" & mapflname)
E1_TmpWbk.Close savechanges:=False
'E1_TmpWbk.RefreshAll
Set E1_TmpWsh = Nothing
Set E1_TmpWbk = Nothing
Set E1_TmpExl = Nothing


It would be great if you could help me!

 


Comment by: Jan Karel Pieterse (1/29/2010 5:44:03 AM)

Hi YM_Saru,

On which line does the code fail?
On first glance, don't you have to give the Resize command the full (new) range of the listobject, rather than the new part?

 


Comment by: YM_Saru (1/29/2010 10:00:04 AM)

Thanks for the reply!

The error shows on "Range(SZrng1 & ":" & SZrng2)" selecting the range for resizing. it worked out at firstrun. Its not accepting the range selection next rerun process. i tried to make all the varibles declaration as new when it starts the process.

i couldnt really understand the "new part"

Thanks!!!

 


Comment by: Jan Karel Pieterse (1/29/2010 11:21:18 AM)

Hi YM_Saru,

With the new part I mean:
On the second run of your code it appears that you are extending the range of the table, starting from the "cnt" row. You are setting the range to resize starting from that row. I think you need to set the resize range starting from the first row of the table, down to and including the last (new) row of the table.

 


Comment by: YM_Saru (2/1/2010 6:49:18 AM)

I was doing from the first row of the table. I display the entire program. Probably you get an idea. the comment lines are just for random trials (this is only rough example, need to recode, once it works):
curpath = Application.CurrentProject.Path
Set E1_TmpExl = CreateObject("Excel.Application")
Set E1_TmpWbk = E1_TmpExl.Workbooks.Open(curpath & "\Analyse_Muster.xlsx")
mapflname = E1Jrcond & "_Beisp_Analyse.xlsx"

ColsetSt = Array("A", "B", "E", "H", "K", "N", "Q", "S", "U", "W")
ColsetEn = Array("A", "D", "G", "J", "M", "P", "R", "T", "V", "AB")

For mapcnt = 0 To 3
E1_Mapping_Analyse.E1_MappingTabloadfrm_map mapcnt + 1
E1_Accrec.Open "E1_Mapping_analtbl", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

Select Case (mapcnt)

Case 0
    Set E1_TmpWsh = E1_TmpWbk.Worksheets.Item("E1 Vergleich")
    cnt = 4
Case 1
    Set E1_TmpWsh = E1_TmpWbk.Worksheets.Item("E1 Vergleich")
    Set E1_colrng = E1_TmpWsh.Columns(1).Find("MDC", LookIn:=xlValues)
    cnt = E1_colrng.Row + 1
    If E1_Accrec.RecordCount <> 0 Then
        Reccnt = E1_Accrec.RecordCount
    For Colsetnr = LBound(ColsetSt) To UBound(ColsetSt)
        SZrng1 = ColsetSt(Colsetnr) & cnt - 1
        SZrng2 = ColsetEn(Colsetnr) & Reccnt + cnt - 1
        Shtbnm = ColsetSt(Colsetnr) & "x"
        Set lstobj = E1_TmpWsh.ListObjects(Shtbnm)
        With lstobj
            Set setrng = Range(SZrng1 & ":" & SZrng2)
                .Resize setrng
        End With
        'lstobj.Resize Range(SZrng1 & ":" & SZrng2)
        'E1_TmpWsh.ListObjects(Shtbnm).Resize Range(SZrng1 & ":" & SZrng2)
        Set setrng = Nothing
        Set lstobj = Nothing
    Next Colsetnr
    End If
End Select
Next mapcnt
E1_TmpWbk.RefreshAll
E1_TmpWbk.Close savechanges:=True
'E1_TmpWbk.RefreshAll
E1_TmpExl.Application.Quit
'E1_TmpWbk.Close savechanges:=False
Set E1_TmpWsh = Nothing
Set E1_TmpWbk = Nothing
Set E1_TmpExl = Nothing
'E1_Accrec.Close
Exit Sub


Thnx

 


Comment by: Jan Karel Pieterse (2/1/2010 8:37:59 AM)

Hi YM_Saru

Maybe we can solve this if you send me your file?

 


Comment by: Marvin (2/1/2010 10:53:52 AM)

I'm trying to decide if I need to change my spreadsheet into a table or not. Perhaps there is a formula that could do the same. My spreadsheet has 30 columns and about 45 rows of data. Is where a formula that will search a selected area for data in column A, go across to column H to retrieve data in the cell that intersect the two to be enter on a difference worksheet? Like on a x and y axis. Example the search start in column A it locate the first data on row 16 then finds column H and go down to row 16 and retrieves that cell data. If not can this be done on a table worksheet?

 


Comment by: Jan Karel Pieterse (2/2/2010 5:29:17 AM)

Hi Marvin,

Check out Help for the VLOOKUP function.

 


Comment by: Max (2/2/2010 4:45:23 PM)

Hi,

Thank you for the very useful summary! I would have a question regarding the Export Data function.

I have created a Table and a List (EXCEL 07 and 03, respectively) and exported them both to SharePoint 2007 (SP) via a macro.

The one exported from 2003 works perfectly, any changes made in the EXCEL list are reflected on SP and vice versa.

Unfortunately the table exported to the same SP site works only one way and I loose the ability to make changes to EXCEL. Or at least those are not refeleceted on SP anymore.

The code I used to export the data to SP in both cases was this:
ActiveSheet.ListObjects("Table1").Publish Array("http://teamwork.xxx.com/sites/Reporting/", "PIp_2010_report_2"), True


What is the most likely cause for the issue? How come XLS 2003 is able to handle the export and the new and improved 07 is not?

Thank you in advance for any help or pointer!

Regards,

Max

 


Comment by: Jan Karel Pieterse (2/3/2010 12:10:48 AM)

Hi Max,

I'm sorry to say I have no Sharepoint knowledge to help you resolve this issue. what happens if you save the offending workbook in 97/2003 file format?

 


Comment by: Max (2/3/2010 6:59:08 AM)

Hi,

Unfortunately does not help. WHat I did notice (to my surprise) is that XLS03 and XLS07 seem to connecting to SP differently. I exported the connection details and opened them up in notepad.

The connection details in XLS03, which seem to be working perfectly has this line in the odc file
"<odc:ConnectionString>Provider=Microsoft.Office.List.OLEDB.1.0;Data Source=&quot;&quot;;ApplicationName=Excel</odc:ConnectionString>"

The same line in the XLS07 connection, which does not work has this in it:
"<odc:ConnectionString>Provider=Microsoft.Office.List.OLEDB.2.0;Data Source=&quot;&quot;;ApplicationName=Excel;Version=12.0.0.0</odc:ConnectionString>"

So probably the solution would be to force XLS07 to use OLEDB1.0, but I'm not sure how to do that.

Thank you

Max

 


Comment by: Jan Karel Pieterse (2/3/2010 7:06:20 AM)

Well, you might try to change the connection string on a copy of your file like this:

- Select a cell in the list
- Alt-F11 to the VBE
- control-g to open the immediate pane
- Type:

Activecell.ListObject.Connection="PasteYourConnectionStringHere"

If the connection string has any " in it, make sure you replace them with two ", like this: ""

- Then with the cusor on that line hit enter.

 


Comment by: Max (2/3/2010 7:26:17 AM)

I will try it. Does the ListObject have the Connection property?
The only reason I'm asking because in the help (which is far worse than the help in XLS03, might I add) it only lists PivotTables and … well, I can't remember what's the other, but not the ListObject. Also apparently OLEDB has a Connection property, which could be set, but I'm not sure it will let me set it to 1.0. Maybe XLS07 only comes with the 2.0 version.
I'll let you know what will be the result.

Thank you!

Max

 


Comment by: Jan Karel Pieterse (2/3/2010 8:44:57 AM)

Hi Max,

Sorry, I did not check whether a table object has a connection. Probably not.

See if there is a querytable tied to the table:
type this in the immediate window and hit enter:

?Activecell.ListObject.Querytable.Connection

 


Comment by: Max (2/3/2010 1:19:48 PM)

No luck, I get a Run-time error 1004. Let me see if I get anywhere with the OLEDB property.

 


Comment by: Max (2/3/2010 1:39:01 PM)

No luck either. I just can't figure out how to change the connection to use 1.0 instead of the 2.0. I also tried searching for it in the VBA References, but did not get anywhere. It seems that I will just have to switch back to XLS03 for any XLS to SP work.

Thx for the help!

Max

 


Comment by: Mary (2/10/2010 8:58:53 PM)

I accidentally formatted the wrong cells for my table. I am now trying to remove the formatting so that I can format the proper cells, but I can't get rid of the old table formatting. I have tried delete, cut, clear, and even formatting over the table, to no avail. I have already saved it with the incorrect formatting, so I can't go back either. What do I do? Thanks so much - Mary

 


Comment by: Jan Karel Pieterse (2/11/2010 5:39:25 AM)

Hi Mary,

Select the part of your table with the formatting you want to loose.
Then on the Home tab, find the "Editing" group. It has a "Clear" button, click it's arrow and select "Formatting".
Next, select anywhere in your table and use the "Table Tools" tab of the ribbon to change the appearance of your table.

 


Comment by: Kailash.5270@gmail.com (2/20/2010 1:18:02 AM)

Hi Tom,

Yes I am, but business is crazy, so don't hold your breath.

As for your filtering problem: this shouldn't be too hard. You could always NOT format the range as table, then do the filtering and deleting and after that is done convert to a table.

Record a macro whilst doing so should give you a god idea of the code.

 


Comment by: John K (2/24/2010 10:25:39 AM)


I can't figure out how to remove the sort down arrows from the header columns. Is there a way to remove these and still have a header row. Seems odd if you can't do this.

 


Comment by: Jan Karel Pieterse (2/25/2010 2:11:27 AM)

Hi John,

Select any cell in your table, click on the Data tab and in the "Sort & Filter" group, click on the Filter button.

 


Comment by: Frank (3/1/2010 11:50:27 PM)

Hello

I'm using excell 2007, Please assist to know how I can return a value exactly comparing two colunms pick the value on the same array and return it on different case. I know vlookup only return the value by checking on only one colunm of the table array

Regards

 


Comment by: Jan Karel Pieterse (3/2/2010 2:13:25 AM)

You can use a matrix function to do this (enter the function using control+shift+enter):

=INDEX($C$2:$C$4,MATCH(E1&"|"&F1,$A$2:$A$4&"|"&$B$2:$B$4,0))

The values to look up are in cells E1 and E2, you're finding a match in columns A and B and the value you want from the matching row is in column C.
After hitting control+shift+enter, the formula in the formulabar will look like this:

{=INDEX($C$2:$C$4,MATCH(E1&"|"&F1,$A$2:$A$4&"|"&$B$2:$B$4,0))}

 


Comment by: Raj (3/8/2010 8:51:23 PM)

I want to lock some columns in a table while leaving others unlocked for data entry. When I protect the sheet with some columns locked and others unlocked, the table behaviour, viz automatically carrying over formulas to the new row is no longer possible. I love this automated carry over of formulas. Am I missing something or is there a workaround for this? (I am currently using the offset formula to define the dynamic range which I need not do in case I have a Table).

Thanks in advance for the help.

Regards,
Raj

 


Comment by: Jan Karel Pieterse (3/9/2010 3:51:08 AM)

Hi Rai,

You are correct, if part of the table is protected, not all functionality works.

I think al you can do is use VBA to do the inserting/removing of rows after changing the protection of the sheet using the UserInterfaceOnly argument.

 


Comment by: Jeff (4/6/2010 9:19:31 AM)

I like using the tables feature primarily for it's quick formatting capabilities. However, there are many times when the table functionality of auto-filling a formula change in a particular cell to the entire column gets very annoying. I.e.- I JUST want to change the formula in C5, but after I do, the table changes all cells in column C to the same formula, even though I want to leave those unchanged. Hitting Undo once will take care of the issue, but is there a setting to have this turned off completely (either for a particular table, or as an Excel-wide option)?

Thanks!

 


Comment by: Jan Karel Pieterse (4/6/2010 9:23:49 PM)

Hi Jeff,

Unfortunately, I could not find a way to prevent this from happening, apart from keeping an extra empty column between the entered formula and the table.

 


Comment by: NTOLO (4/15/2010 3:59:54 AM)

I am using Excel 2007 so i want to add multiple sheets on one work book.

 


Comment by: Jan Karel Pieterse (4/16/2010 3:03:21 AM)

Hi NTOLO,

I'm not sure I understand your question? You can repeatedly insert a worksheet (rightclick on a sheet tab and select Insert), is that not what you need?

 


Comment by: Jeff Weir (4/21/2010 2:57:07 PM)

Hi Jan. I didn't have any luck when trying to reference a table on another sheet your way. Must be missing something.

However, I found that I could do this if I selected just one cell on my destination sheet, typing = in the formula bar, then selecting the part of the table on the source sheet(i.e. headers, data, or the entire table) that I wanted to suck through to the destination, then pushing Enter.

Note that this returns a #VALUE! error if you are selecting more than one cell, but that's fixed when you copy the formula generated into a range of cells the same size as the original table, then array entering the formula (i.e. selecting all the cells where you want the table to be copied to on your destination sheet, selecting the formula bar, and pressing Ctrl+Shift+Enter).

Note that if you expect your source table to expand, you'll need to select extra rows or columns in your destination sheet when entering this formula.

 


Comment by: Jan Karel Pieterse (4/22/2010 12:01:34 AM)

Hi Jeff,

Thanks for the suggestion!
Not sure why you'd want to duplicate the entire table though?

 


Comment by: Jeff Weir (4/22/2010 12:52:11 AM)

Good question, Jan. Currently I have some graphs that reference another workbook using some dynamic ranges. But these only work when the source workbook is open. So I was considering duplicating the entire table from the source workbook to my destination workbook, and then using the dynamic ranges on the table copy instead. I could also use linked cells instead of referencing a table.

However, today I decided on a different approach...I'm going to suck data from the entire closed source sheet to the destination sheet via some SQL in MS Query, using a SELECT * query. That way, no matter what way the table expands (i.e. new rows or new columns) the query will capture the changes, without me having to guess how big the source table might grow over time.

On this note, perhaps you might be able to help me. Unfortunately in this source spreadsheet the data is arranged by date columns. When new data gets pasted into the spreadsheet, someone adds a new date at the top right of the existing data array and then pastes the correstponding data below. So it's structured like this:

Category 1/6/2009 1/9/2009 1/12/2009
Planes    112     123     52
Trains    52     53     123

That is, it doesn't have a 'date' column header but rather the columns across the top of the spreadsheet are quarters.

If I want to select the categories for a couple of these date columns, then I can use something like:

SELECT Category, `39965`, `40057`, `40148` --note that these numbers are dates
FROM `Worksheet_Name$`

...but what I'd like to be able to do is dynamically select just the most recent column that I want, and I don't want to have to manually update my MS query SQL to pull the latest x columns. (Say the last two for the above example.)

Do you happen to know how I code in a check on column headers so that it only pulls the ones with the largest dates?

Regards

Jeff

 


Comment by: Jim (4/22/2010 6:31:08 AM)

Hi Jan, I want to print multiple tables in excel 2007 with no page breaks after each table and have the appropriate header row repeat at the top of each new page. For example, if table one is 1.5 pages long, the header row for table 1 will be printed at the top of page 1 and 2. If table 2 then starts on page 2 and continues on page 3, the header for table 2 will be displayed at the start of table 2 in the middle of page 2 and on the top of page 3.
Thanks for any suggestions

 


Comment by: Jan Karel Pieterse (4/22/2010 8:06:08 AM)

Hi Jeff,

You'd use:

SELECT * FROM `Worksheet_Name$`

 


Comment by: Jan Karel Pieterse (4/22/2010 8:10:15 AM)

Hi Jim,

You could use something like this:

Sub PrintLists()
    Dim oHeader As Range
    Dim oListObj As ListObject
    For Each oListObj In ActiveSheet.ListObjects
        With ActiveSheet
            .PageSetup.PrintArea = oListObj.Range.Address
            .PageSetup.PrintTitleRows = oListObj.HeaderRowRange.EntireRow.Address
            .PrintOut
        End With
    Next
End Sub

 


Comment by: Jim (4/22/2010 8:24:30 AM)

Thanks Jan! I will give it a try.

 


Comment by: Ketty Israel (5/12/2010 9:06:05 PM)

This article is exactly what I needed when I needed it. My search showed your link as the 5th option, but you were the only one of the 5 to answer the question that I had.

Incidently, the question was to find a way to place column totals for tables.

Ketty

 


Comment by: Phil (5/17/2010 4:48:18 PM)

Hi Jan,

I've been using tables for a while (and Lists prevously) with great success, however, I seem to be getting eratic formula updating when I make a copy of a table in another worksheet, that is, highlight the entire table, copy, and then Paste into a new worksheeet. In some cases the formulae update to refer to the new table name, and in somecases they remain referring to the old table name. I've even had a mix, with some references updated to the copy and some remaining with the original. Can you provide any guidance on how table referencing should work when a table is copied?

 


Comment by: Jan Karel Pieterse (5/18/2010 7:26:46 AM)

Hi Phil,

I have not heard of this issue before. If you ask me, copying a table should never change formulas that point to it. Period.

It is a different matter if you do a cut and paste, then I do expect all formulas to point to the new location.

 


Comment by: Tony Bolton (5/27/2010 8:16:19 AM)

Once set how do you remove the table format.
The bottom row in my table won't sort alphabetically so I thought if I remove the format then i could the use a sort.

 


Comment by: Jan Karel Pieterse (5/28/2010 1:01:30 AM)

Hi Tony,

On the table tools tab of the ribbon there is a button "Convert to range" that does that.

 


Comment by: karen (6/4/2010 3:00:21 PM)

I want to add a row into a table but 'insert cells' is grayed out.

 


Comment by: Jan Karel Pieterse (6/6/2010 11:11:29 PM)

Hi Karen,

Maybe the worksheet is protected?

 


Comment by: Michael Hubbard (6/23/2010 5:17:52 PM)

So I inherited an Excel table. It is actually very good and I like the way it works.

My question is that a couple of the columns are effectively comments or details (i.e. sentences) pertaining to each row and when these are included in the table filtering possibilities the whole Excel file becomes VERY LARGE. Is there anyway to remove these columns from the table filtering and yet they would obviously stay as part of the row when other filtering is applied?

Thanks for any assistance.

 


Comment by: Jan Karel Pieterse (6/24/2010 2:29:10 AM)

Hi Michael,

I am quite surprised that it would make a difference on file size whether the texts are within the table or not.

But you can put the texts outside the table, because Excel hides entire rows when you use the filter anyway.

 


Comment by: lmshow (7/21/2010 10:52:05 AM)

can you please help me with this;

What is the essence of having this special bracket "{" starting and ending an excel formular
e.g.

{=IF($B$2="All",LARGE(Table6[Column47],$J5),LARGE(IF(Table6[Column110]=$B$2,Table6[Column47],FALSE),$J5))}

I'm trying to create such formular and if i manually type in the special bracket "{" excel doesn't recognise it as a formular.

 


Comment by: Gerson (7/22/2010 11:39:55 AM)

Hi Jan,

Very Good write-up. I'm hoping you can help me resolve an issue I'm having with relative table references.

I have two table in two different workbooks. One is an Employee List and the other is Employee Sales Activity. I'm using a vlookup function in the sales activity sheet to look up which employee it correlates to by ID and when I have both sheets open, the function works great. But as soon as I close the Employee List all of the values revert to #REF!.

I can get the values to work if I used defined ranges (when the sheet is closed) like A1:G1000, but is there a way I can make this work using table references instead and not lose the value when I close the sheet?

Thank you

 


Comment by: Mark (8/15/2010 6:34:55 PM)

Is it possible to reference a value in the following way

=Table1[[#ThisRow],[HeaderName]]

Where "HeaderName" is a Name in Excel that holds the same text string as one of the headers in Table 1?

Thanks

Mark

 


Comment by: Jan Karel Pieterse (8/16/2010 2:57:58 AM)

Hi Imshow,

The curly braces indicate that you're dealing with an array formula. The curly braces are added by Excel when you edit the formula and confirm your edit by hitting control+shift+enter instead of just enter.

 


Comment by: Jan Karel Pieterse (8/16/2010 3:06:04 AM)

Hi Gerson,

I'm afraid structured refs to external tables do not work when the external workbook is closed. As you've already found out.

 


Comment by: Jan Karel Pieterse (8/16/2010 4:44:48 AM)

Hi Mark,

If the column name is in a cell (say K3), then this formula does that:

=INDIRECT("Table1[[#ThisRow],[" &K3&"]]")

 


Comment by: graham jones (9/21/2010 7:48:12 PM)

I have a user opening a excel 2007 .xlsm workbook that contains an embedded query table. This table has Autofilter on for a date column. The Autofilter drop down does not show any date grouping by Year. This only affects the one user; all other users opening the same sheet see the dates grouped by year.
Excel Options\Advanced\Display Options for this workbook\Group Dates in the Autofilter Menu is Checked.
I can't find out how a User level property can override this setting.
Our user is opening the workbook from 2007; they are not opening a 2007 workbook from 2003 with compatibility pack.

 


Comment by: Jan Karel Pieterse (9/21/2010 11:43:32 PM)

Hi Graham,

I have no idea why that might be the case. Try if toggling that setting off and back on helps (in-between, make sure to close and re-open Excel).

 


Comment by: graham jones (9/26/2010 3:42:29 PM)

re - my autofilter date grouping post - your advice to toggle the setting on and off worked - thank you - much appreciated.

 


Comment by: Jan Karel Pieterse (9/26/2010 10:58:55 PM)

Hi Graham,

Thanks for letting us know!

 


Comment by: Rich (10/4/2010 1:28:34 PM)

Referring to a table from another workbook

You mention the following in your article:

"Normally you would refer to a range name "TableName" in workbook "WorkbookName.xls" using: [WorkbookName.xls]!TableName
But although a table is represented by a range name, you should not use the range name syntax as the source. Rather you must use this:

WorkbookName!TableName

My question is if I have a table created by the name of Test and it is created a workbook called Sales and I want to create pivot table in a new workbook using the table "Test", what is my syntax when I select Insert Pivot Table? Wouln't I need to specify the drive mapping and folder it is in (e.g. C:\desktop\sales.xlsx!test ? Does anything have to be in brackets?

Thank you.

Rich

 


Comment by: Jan Karel Pieterse (10/5/2010 12:52:02 AM)

Hi Rich,

You can only create the Pivot table with the source workbook open. In that case, Excel will add any path information behind the scenes. As soon as you close the source workbook, the full path will be in the Pivot table's source.

 


Comment by: sreevani (10/13/2010 6:12:45 AM)

Useful

 


Comment by: Leif Holstad (11/10/2010 10:25:48 PM)

Hello,

I have created a table of some 3000 rows containing a volume-column. After selecting the wanted rows, I want a accumulated sum of the volumes shown. This does not work with normal accumulation because the formula is refering to rows which are hidden.

Could this accumulation in a selection be done?
In advance thank you very much!

Kind regards,
Leif HOlstad

 


Comment by: Jan Karel Pieterse (11/10/2010 10:46:07 PM)

Hi Leif,

Check out the SUBTOTAL funtion, it ignores filtered rows.

 


Comment by: Diane Woods (11/16/2010 4:47:56 PM)

Hi,

I have created tables as you have decribed in the Excel 2007 article. The tables are not refreshing when I update the data source. I have located the refresh button on the toolbar but nothing happens.

Thanks,
Diane

 


Comment by: Jan Karel Pieterse (11/17/2010 3:05:06 AM)

Hi Diane,

I don't think I understand your problem. If a range is based on an external data source, it normally already is set to be a table (Excel does that automatically during the definition of the query). Looks like the external data connection is lost?

 


Comment by: Diane Woods (11/17/2010 9:26:38 PM)


Jan,

The data source is in the same workbook. Could I have lost the connection with that. (I am new to this)

Diane

 


Comment by: Jan Karel Pieterse (11/18/2010 3:04:33 AM)

Hi Diane,

How did you "reference" to the "data source", did you use formulas?

 


Comment by: Diane Woods (11/19/2010 4:20:07 PM)


Jan,

I followed the steps to create a table format. As in figure 4, I indicated where the data is for my table.
When I add data to that data source, my table does not update.


Diane

 


Comment by: Jan Karel Pieterse (11/21/2010 10:22:44 PM)

Hi Diane,

You mean if you type anything in a cell below or next to your table, Excel does not expand the table automatically?

 


Comment by: MAUREEN (11/23/2010 4:52:46 PM)

Hi
When I export a query to excel it comes up with field names heading.
There used to be a funtion in the previous excel which you could exclude field names in the "External Data Range Properties" why has this been taken out? and can I get it back. I noticed in spreadsheet created before the 2007 upgrade have still kept this function.

 


Comment by: Jan Karel Pieterse (11/24/2010 12:28:17 AM)

Hi Maureen,

Indeed you can no longer set that up in the query properties. You can however set the Table to hide it's header (see the Table tools tab in the ribbon, Table Style Options group).

 


Comment by: Kami Borisova (11/24/2010 2:59:06 AM)

Hi,

When I type a formula, for ex.=vlookup(Table5[[#This Row];[Material]],PriceList$A:$E,2,0) and I want to drag it into the next columnq but to change only 2 with 3, it happens that the header [Material] changes also with the header of the next column. Can I prevent it?

 


Comment by: Jan Karel Pieterse (11/24/2010 8:40:41 AM)

Hi Kami,

The easiest way I can think if right now is to hit F2, select entire formula, hit control+c and then move to the target cell and press control+v. Then edit the target cell to change the 2 to a 3.
Not very simple, I know.

 


Comment by: Kami Borisova (11/24/2010 11:35:59 PM)

Hi Jan,

Thank you very much for the respond.
I thought it might be a simple way, but I guess I'm wrong :).
Anyway, let's hope Microsoft will make it easier.

And you continue doing this because it helps a lot!

Best regards,
Kami

 


Comment by: Graeme (11/30/2010 3:10:56 AM)

hi, i have a table of records with a total, i have a macro in place so that when new data is entered into the input form it adds it to the top of the table as a new record, however the total does not take into acount the new record, it just drops the formula down a row.
thanks, Graeme.

 


Comment by: Jan Karel Pieterse (12/1/2010 9:40:12 AM)

Hi Graeme,

The simplest solution is to have your total formula refer to a range that includes one extra row. So ifyou're inserting on row 3, make sure row 2 is included in the formulas.

 


Comment by: Leif Holstad (12/14/2010 8:16:00 AM)

Hi,

I have been using tables for many years and have had few problems. Now I have got a problem in Excel 2007 concerning autofiltering. The filter is set to select just one of the optional entities. The result is showing the selected entity plus some others which should not show. From my experience this should not be possible. How can this be solved?
Many thanks in advance!

Regards,
Leif

 


Comment by: Jan Karel Pieterse (12/14/2010 9:25:05 AM)

Hi Leif,

All I can think of is that the autofilter range is improperly specified and does not include all rows of your table.

 


Comment by: Forrest (12/26/2010 2:32:19 PM)

Hello Jan - First - let me thank you for your well written tutorial on Tables for Excel 2007 - great stuff.

I have a particular task I need to complete and am wondering if using Tables is the answer.

I have one range (could become a table) that contains hundreds of financial transactions (one per row) - columns include "date" (of transaction), description, amount, currency, etc.

I have another that is a range of dates and currency conversion rates (one row per day for 5 years) - 1st column is the date, columns B-X are the conversion rates for the date for various currencies (e.g. column B is euros, C is yen, D is Australian dollars, etc.)

What I need to figure out is how, for every transaction in the first data set, to go to the second and extract the appropriate currency conversion rate, and convert to the target rate (US dollars in this case)...

I have a sneaking hunch that using Tables might make this task easier - your thoughts?

 


Comment by: Jan Karel Pieterse (12/28/2010 6:06:35 AM)

Hi Forrest,

Tables may help, especially because you don't have to worry about pointing your formulas to enough rows. What you need is the VLOOKUP function.
Rather than trying to describe the function here, I suggest you look up the function in Excel help, it is rather complete on how it works.
Pay special attention to the last argument of the function.

 


Comment by: Ute Simon (1/10/2011 11:34:51 AM)

Hi Jan,
Thank you for this excellent tutorial!

I have a table (TableA) on the first worksheet of a file. It is constantly growing, because PersonA enters new data daily. I insert a new table (TableB) on the second worksheet of this file and reference to the first three columns of TableA, PersonB has to enter data in the other columns (the data cannot be managed in one table for privacy reasons).
If new rows are added in TableA, is there any way to make them automatically appear in TableB, so that TableB is growing automatically, too? Or do I have to look at the last row in TableA and resize TableB manually by dragging the corner or entering a new range? (I tried entering a formula in the "Change size", but it's converted to a fixed cell address.)

 


Comment by: Jan Karel Pieterse (1/10/2011 11:19:19 PM)

Hi Ute,

You'll need some VBA code that copies the new line to the other table. But you probably also need code to update existing lines in table B when the user edits existing material. I cannot show you example code because I do not know your precise setup.

 


Comment by: Allen (1/20/2011 7:56:47 AM)

Hi, this works in the immediate window but not in a function:

Range("MtgTable1").Sort Range("MtgTable1[Match]"), Header:=xlYes


In a function, it gets this error:

"1004:Method 'Range' of object '_Worksheet' failed."

MtgTable1 is a table and Match is column in the table. I'm using Excel 2007.

 


Comment by: Jan Karel Pieterse (1/20/2011 8:22:10 AM)

Hi Allen,

Maybe in the function there is no range called MtgTable on the active sheet when the code tries to execute the statement?

 


Comment by: Stefan Kemp (2/17/2011 1:04:18 PM)

Hi Jan,

Is there any way that a table can still autoexpand on a protected sheet? I have a table with all it's cells unlocked and the cells immediately below it unlocked, but if I protect the worksheet then the table no longer expands automatically. In fact, even when I unlock ALL the cells in the worksheet, and protect it with ALL the protection options selected, the table won't auto expand. Is there any way around this?

Thanks.

 


Comment by: Jan Karel Pieterse (2/20/2011 11:12:13 AM)

Hi Stefan,

No, tables on protected sheets cannot grow. All you can do is allow the user to insert rows when settin up protection (you'll have to unlock the cels in the tbale too of course). Then if a row is inserted within the table, the table grows. BUt the table does not auto-expand when you add a row below the table or a column to its right or left.

 


Comment by: tony (3/3/2011 11:24:09 AM)

What is the best way to write to a row column intersection within a table using vba code?

 


Comment by: Jan Karel Pieterse (3/4/2011 2:01:55 AM)

Hi Tony,

Depends on what information you know about the column and row: how do you find the row/column you're interested in?

 


Comment by: Kami Borisova (3/25/2011 4:43:32 AM)

Hi Jan,

I have 2 tables in 2 sheets. The first column in the both tables contains numbers - the same numbers. But in the one of the tables the numbers repeat in different rows /not necessary one after another/, in the other - one number=one row. The next columns contain different infos. So, the question is - is there a way to "transpose" the info from any of the columns from the table with the repeated numbers to the table with the unique numbers, but to make the info to go in different columns, depending on the unique number?
Did I manage to explain it clearly? :)
Maybe there is a combination of functions, but I just can't think of some :(

BR,
Kami

 


Comment by: Jan Karel Pieterse (3/25/2011 5:13:42 AM)

Hi Kami,

I advise you to ask this question at www.eileenslounge.com

 


Comment by: Jacinto (4/15/2011 1:22:39 AM)

I need to create a pivot table in excel 2007 using another excel file as the data source.

In excel 2000 this was easy since the pop up menu gave a choice "Microsoft Excel List or Database". This does not seem to be available in 2007. Appreciate your help. thank you.

 


Comment by: Jan Karel Pieterse (4/15/2011 2:59:05 AM)

Hi Jacinto,

Use the old Pivot table wizard. It is not in the ribbon, but you can find it if you right-click the QAT and select customize Quick Access Toolbar. Then the wizard is in the category commands not in the ribbon. STart the wizard and select the external data option in step 1.

 


Comment by: Jacinto (4/16/2011 12:42:19 AM)

Hi Jan, Followed your directions but am unable to find the wizard. I did look into all the sections (Popular, Formulas, Proofing, Save, Advanced, Customize, etc). Please help. Thank you.

 


Comment by: Jacinto (4/16/2011 12:50:07 AM)

Jan, Thanks. Got the tip from this location
http://www.pcreview.co.uk/forums/excel-2007-pivot-table-wizard-t3602763.html

 


Comment by: David (4/18/2011 12:49:20 PM)

Hi Jan, I am trying to create a dynamic chart that will keep changing as we add columns to the data. I converted the data to a table and added some columns as a test. The chart is not expanding when this is done. Also, the new headers show up as a number on the chart instead of a month (text). Thanks for your help!

David

 


Comment by: Jan Karel Pieterse (4/19/2011 12:08:25 AM)

Hi David,

From the top of my head, I don't know whether a chart will reflect additional columns added to a table. It does work with adding rows however, so maybe you can put that to use?.

 


Comment by: J Rott (4/22/2011 8:49:05 AM)

I am pulling data from a database to excel 2007 into a table, the table is named the same name as the database (access 2007). This data is a truck loading schedule and gets updated daily. I have built macros to help format the data a bit, then copy the table to a new worksheet via macro make some additional changes to new table on the new sheet. change to a basic range and use subtotal. I am doing this since subtotal doesn't work with a table. THe problem I have is the name of the table changes, adds a number to the end of the name. I have tried building a macro to change the name to the same name I built the original macros on, but that doesn't work for the same reason. I have searched everywhere for a fix/workaround. I don't change the table I download, because I don't want to mess it up for the next download. Any ideas?

 


Comment by: Jan Karel Pieterse (4/26/2011 12:33:19 AM)

Hi,

Judging from your description I expect your code is recreating the querytable everytime you have it refresh. This causes Excel to add a new named range and a new table each time. Instead, you should be refreshing the existing query. Then the name will no longer change.

 


Comment by: mohammed ageel (5/20/2011 11:38:07 AM)

Hi David,
I have 288 charts in my research and need to change the same things in its some properties did there a maner to make the change in only chart and drag the change to anothers bu one thing.
very thank to you
note i dont have time to make the change one one, therefore i wish and wiat helpful

 


Comment by: Jan Karel Pieterse (5/22/2011 3:25:10 AM)

Hi mohammed,

Maybe this utility helps:
www.jkp-ads.com/officemarketplaceff-en.asp

 


Comment by: Jerry (6/3/2011 10:18:30 AM)

In excel 2003 the list function used a blue border and preempted the next line. In the case of 2007, the table will expand but only with a manual entered number or text on the subsequent line. The validation function does not transfer to the next line until the expansion takes place. Is there a way to activate the blue border with *

 


Comment by: Jan Karel Pieterse (6/5/2011 11:44:40 PM)

Hi Jerry,

Unfortunately they removed that feature, I agree it's a pity!

 


Comment by: Talat usman (6/7/2011 11:36:01 PM)

dear Sir
Thank you very much for teaching me

Talat usman

 


Comment by: Becky (6/10/2011 10:06:08 AM)

I am looking for help on structured referencing- and you have a lot of helpful pages- thank you!

BUT...when I try the example you give above about typing directly to right of a table "=Sum(..." and picking cells in the table...it doesnt give structured references- only D15, A12 etc. What am I missing??? hmmm...

- I have checked setting for formulas to use table names.
- (unrelated) I have checked GetPivotData option
- I have tried saving as .xlsm versus .xls --.xlsx wont save due to macros in my file...fine.
- for both .xls and .xlsm I tried creating a table from scratch and attempting your example- same result - no structured references...

any help appreciated- thanks!

 


Comment by: Jan Karel Pieterse (6/14/2011 12:23:09 AM)

Hi Becky,

Maybe the file was in 97-2003 file format?
In that case, save as 2007 format, close and re-open the file.

 


Comment by: ribka (6/23/2011 7:25:50 PM)

Hi Jan,

Can we customize the color in the table become the same for example every 3 rows instead of every 1 row like the template given?

Thank you.. :)

 


Comment by: Jan Karel Pieterse (6/24/2011 5:30:05 AM)

Hi ribka,

Sure. Rightclick the tablestyle and click duplicate if it is a built-in style, or modify if it is a custom one. Then select "Second row stripe" and change the stripe size.

 


Comment by: ayca (7/6/2011 6:57:53 AM)

Hi,

I would like make up a new table format via "new table style". But I would like to see my new style not only in the current workbook (inwhich I have made the style), but in every excel workbook. how can this be possible?

Thanks in advance,

 


Comment by: Jan Karel Pieterse (7/6/2011 9:48:52 AM)

Hi Ayca,

1. Open an empty workbook
2. Insert a table into it containing some dummy date
3. Create the table style you need
4. Remove the table and all of its contents
5. Save-as your file, choose filetype template (either xltx or xltm, depending on what default file format you want to have)
6. Save the file in your XLSTART folder and call it Book.xlt (x or m)

 


Comment by: Laura (7/15/2011 3:48:13 AM)

Hi,

How do I creat a new table style from an existing table?


Thanks

 


Comment by: Jan Karel Pieterse (7/15/2011 11:35:07 AM)

Hi Laura,

You can right-click any existing table style and select "Duplicate". After that you can edit the duplicated style.

 


Comment by: Joan Cullemore (7/18/2011 7:31:24 AM)

We have "inherited" an excel file with pivot tables created by a previous employee, hardly necessary for this information. Now we need to now how to see rows that appear bewlo the "pivot headings row", We can use either Excel 2003 or 2007. The Unhide rows feature does nothing.

 


Comment by: Jan Karel Pieterse (7/19/2011 1:34:43 AM)

Hi Joan,

Maybe one of the fields of the pivot table has a filter applied? Just click the dropdowns next to each field (in turn) and make sure no filter is applied to any of them.

 


Comment by: leev (7/22/2011 11:23:43 PM)

thankssss~!!!
very nice tricks, with printscreen to help us, thumbs up ! ^^

 


Comment by: Marie (7/24/2011 12:56:33 PM)

What a life saver! Thanks for explaining the new "Table Tools" ribbon. I just spent two hours trying to figure out why that tab appeared on one workbook but not the other. Now I can recreate it effortlessly. (sigh ...)

 


Comment by: Wilson (8/10/2011 12:28:30 PM)

How can I remove an existing table? I tried convert to range but it is taking too long.
is there a vba function to check if there is an existing table on the worksheet.

would it be better if i use resize table instead of removing the table?

 


Comment by: Rajendra Pai (8/12/2011 9:02:27 PM)

Is there a way to protect some parts of a table?
Example:
A table has three columns: Column 1 has Dates, Column 2 has amounts and Column 3 has the cumulative total of amounts.
Is there any way to prevent the user from modifying Column 3 while retaining the Table functionality?

Thanks in advance/

 


Comment by: haari (8/16/2011 1:46:33 PM)

how can i change the row headers to specific names??
i know how to change the A,B,C's in column headers to 1,2,3.I wnat to find out how I can do that for row headers?

thank you

 


Comment by: Jan Karel Pieterse (8/22/2011 2:06:27 AM)

Hi Haari,

I'm afraid that is not possible in Excel.

 


Comment by: Jan Karel Pieterse (8/22/2011 2:13:06 AM)

Hi Rajendra,

You could unlock the cells you want the user to be able to modify and then protect the worksheet, but I seem to recall that does restrict the functionality of the table.

 


Comment by: Jan Karel Pieterse (8/22/2011 2:35:53 AM)

Hi Wilson,

Do you also want to remove the content of the table, or just convert it to a range?

You can check for a table like this:

Public Function HasTable(oWorksheet As Worksheet, Optional sTableName As String) As Boolean
    Dim oLo As ListObject
    If Len(sTableName) = 0 Then
        HasTable = (oWorksheet.ListObjects.Count > 0)
    Else
        For Each oLo In oWorksheet.ListObjects
            If LCase(oLo.Name) = LCase(sTableName) Then
                HasTable = True
            End If
        Next
    End If
End Function

Sub demo()
    If HasTable(ActiveSheet, "Table1") Then
        MsgBox "The activesheet has a table named 'table1'"
    End If
End Sub

 


Comment by: Wilson (8/22/2011 9:30:46 AM)

Hi,

actually I would like to remove the table so i can recreate the table. i tried to do a resize but it says table exists. I would need a function to check if a table exists and if there is a table, how can i resize it with VBA.

the above function works well in checking for an existing table but i'm looking forward to a resize with it.

 


Comment by: Jan Karel Pieterse (8/23/2011 5:54:48 AM)

Hi Wilson,

This clears the cells and removes the table:

ActiveSheet.ListObjects("Table1").Range.Clear

 


Comment by: vinutha s hegde (8/26/2011 10:36:05 AM)

how can i enable the table name in formulas? I could do this in Excel 2003, Here, it is not taking that formula

 


Comment by: Jan Karel Pieterse (8/29/2011 1:33:23 AM)

Hi Vinutha,

Excel 2003 did not have the structured table referencing that Excel 2007 and 2010 have, so I think I don't understand your question?

 


Comment by: Staf Laenen (9/8/2011 4:52:25 AM)

Hi,

I believe Vinutha refers to the label names (or natural language formulas) that in excel 97-2003 allowed to use a column header in a formula.
This option is no longer supported in Excel 2007-2010.

 


Comment by: Jan Karel Pieterse (9/8/2011 6:58:10 AM)

Hi Staf,

I think you are correct.

 


Comment by: chetan patil (10/4/2011 8:34:59 AM)

how to read table from excel 2010 in vba....can you provide code for the same? or how to combine multiple ranges to table?

 


Comment by: Jan Karel Pieterse (10/5/2011 12:10:06 AM)

Hi chetan,

The next page of this article shows VBA code examples...

 


Comment by: Paul Cheadle (11/15/2011 3:31:25 AM)

I have a table that has refreshable sql data coming in. If I insert a column for manual entries and set the external data properties (right click on table - table - external data properties - advanced) to 'insert a whole line' it doesn't shift my added column data in tandem and then everything goes out of sync.

 


Comment by: Jan Karel Pieterse (11/15/2011 5:12:31 AM)

Hi Paul,

Excel does not insert whole rows when refreshing tables, only in the area below the table. If you need to keep comments in synch with the data, you must use an additional table which refers to the data table, using any unique key in the comments table which ties your comment to the appropriate row in the data table.

 


Comment by: shereen (11/17/2011 12:34:00 AM)

When I try to enter a VLOOKUP formula on a column of data in table - I get an error.

 


Comment by: gecs (11/29/2011 4:09:09 AM)

Because the name of the table points dynamically to the data range of the table it is very helpful to use the data stored in a table column to create data validation lists.

Just a tip: using structured references for creating such lists you can't use a formula like:
=Table_Name[Column_Name]
but it works including the structured reference as the argument for the INDIRECT function:
=INDIRECT("Table_Name[Column_Name]")

 


Comment by: ripudaman (12/13/2011 11:10:18 PM)

i am calculating (Total Hours * Rate per Place)*24) its gives me the wrong answer...

pl help

 


Comment by: Jan Karel Pieterse (12/13/2011 11:19:46 PM)

Hi ripudaman,

What answer do you get and what are you expecting to get?
Tip: is the cells numberformat set up properly?

 


Comment by: NISHA (2/21/2012 9:06:10 PM)

WHEN I SELECT A RANGE FOR MY TABLE IT PROMPTS TO REMOVE AUTOFILTER IN MY SHEET. HOW CAN I DO THIS..PLEASE HELP

 


Comment by: Jan Karel Pieterse (2/23/2012 3:02:19 AM)

Hi Nisha,

Please do not write in all caps, it is hard to read an is considered as if you are shouting.

Just turn off autofilter and then convert the range to the table.

 


Comment by: zainul (3/17/2012 3:31:29 AM)

how i write the numeric into words with rupees in excel sheet

 


Comment by: Jay (3/19/2012 3:54:17 AM)

I created some tables and i want to change the name of the tables because it comes up like table5, table2, etc?

 


Comment by: Jan Karel Pieterse (3/19/2012 5:50:20 AM)

Hi Jay,

Have a look at the section called "Table Options on the Ribbon" near the top of this articlt.

 


Comment by: Jan Karel Pieterse (3/19/2012 5:51:56 AM)

Hi Zainul,

You need a special VBA function for this. Microsoft has an example on their website, but it is English:

http://support.microsoft.com/kb/213360

 


Comment by: Nguyen Thi Thanh Nhan (4/12/2012 2:15:29 AM)

I want to merge 2 cells in a table. But I see in ribbon, this button is disabled. Excel 2007 can not merge 2 cells in a table? If I want to do that, what have I do?

 


Comment by: Jan Karel Pieterse (4/12/2012 4:43:17 AM)

Hi Nguyen,

No, cells in a table cannot be merged.

 


Comment by: chris (4/30/2012 7:17:24 AM)

what is used to add another row to the table!!!!!!!!!!!!!

 


Comment by: Jan Karel Pieterse (5/1/2012 4:48:09 AM)

Hi Chris,

You can just type your info on the first empty row beneath the table, Excel will automatically expand the table.

 


Comment by: Chris K (8/16/2012 3:52:16 PM)

Hi,

Background:

I have a very large sheet which performs multiple calculations using a data range, i currently use find and replace to update the cell references when i add to the range.

My issue: I figured using tables would be much more efficient as the cell references within the formulae update automatically but i have found that copying and pasting these new rows (never more than a few hundred) is taking longer than the way i currently do this. Does anyone know why?

 


Comment by: Jan Karel Pieterse (8/16/2012 4:13:58 PM)

Hi Chris,

This is because Excel does extra work like expanding the table's range when you do a paste.
What helps -if you have to paste more than once- is to paste but leave one empty row between the paste and the table. Then once you're done, you manually expand the size of the table.

 


Comment by: fas (9/12/2012 7:30:04 AM)

when i create a table in excel and protect the sheet with password, new row not insert, though i have checked the option while protectin the sheet

 


Comment by: Jan Karel Pieterse (9/12/2012 9:49:20 AM)

Hi Fas,

Unfortunately, this is "by design": you cannot expand tables on protected worksheets.

 


Comment by: Annette Hartz (9/22/2012 11:22:16 PM)

Hi!

I use some rows underneath the table with a different row height. When I add new rows to the table (with a button/macro), the different row height underneath the table is "moving up", changing the layout of the part underneath the table and - as soon as it "reaches" the bottom part of the table, the table itself. Is there a way to stop that?

 


Comment by: Jan Karel Pieterse (9/24/2012 11:44:05 AM)

Hi Anette,

You mean that the rows beneath the table do not shift downwards when a row is added to the table, correct?
One way around that would be to first insert an entire row immediately beneath the table and then add the row.

 


Comment by: Pat (9/26/2012 5:47:01 PM)

Hi Jan,
Thank you for providing the info and also replying to the questions.
In my case I have 2 tables, 1 table has data and the other just sums each column. It is similar to the total row but it is a different table. Both tables can grow. When Table 1 grows by one column I want to add a column to Table 2 also and then copy the formula from the previous column of Table 2. Is this the best way to do it? How can the new column in Table 2 refer to the new column in Table 1? Is there a way to refer to the table column by the column number instead of the column name? Also if I add a new column in a table and if the last column was a calculated column then how can I automatically copy the formula to the new column added.
Thanks for all your help.

 


Comment by: Jan Karel Pieterse (9/26/2012 7:18:19 PM)

Hi Pat,

I'm sorry, can you ask this question here:

www.eileenslounge.com

I'm tied up at the moment.

 


Comment by: Jan Karel Pieterse (9/27/2012 1:07:48 PM)

Hi Pat,

I guess you'd need a bit of VBA code, as there is no automatic way of doing this.
A simple solution however is to NOT use structured referencing in your sum formulas, but rather simple
=SUM(A2:A10).
If you add a row to the table, the formula will auto-adjust.
Also, you can simply copy the formula to the left or right and the column will adjust because it is a normal cell reference.

 


Comment by: Pat (9/27/2012 3:39:29 PM)

Hi Jan,

Thank you for your inputs. I will use this and try to work out something and let you know if there are more issues.

Thanks again.

 


Comment by: rosa (10/2/2012 7:12:36 AM)

Hi--

Above you state that you can turn the auto-expand off, but where do I make those changes? I do want to control certain things that tables assume, but am going mad looking for where I might actually make such a change. Specifically, i have put dates in the column header, 2 weeks apart. I delete one of the dates and it now auto enters the date to its left, plus one year. Argh!

 


Comment by: Jan Karel Pieterse (10/2/2012 9:23:17 AM)

Hi Rosa,

If you enter a value to the immediate right of a table and it expands, a small rectangular object appears near the new column. Hover your mouse over it and you'll see autocorrect options.

Click the box and you can select "Stop automatically expanding tables".

So this behaviour is in the autocorrect options:
File, Options, Proofing tab, autocorrect options, Atuformat as you type tab.

 


Comment by: Adrian (10/3/2012 4:19:28 AM)

Is there a way to insert a vaiable into the second row number of a summation in oder to use anoter cell to dictate how many rows a sum() cell should use?

 


Comment by: Jan Karel Pieterse (10/3/2012 9:25:38 AM)

Hi Adrian,

I am not sure what you are trying to achieve?

 


Comment by: Allie C (10/12/2012 1:13:06 AM)

Hi,
I was wondering if you could help with creating links to a table. The issue is that I am referencing specific cells of a table, and when the table is resorted the links remain on the cell requested. It seems that this is not a capability because excel tracks the cell description and not the value description.
Thanks in advance,
Allie

 


Comment by: Jan Karel Pieterse (10/12/2012 12:28:12 PM)

Hi Allie,

Perhaps you can use the HYPERLINK worksheet function to create a dynamic link. Suppose you want to refer to this cell:

='Sheet A'!$A$1

Then the hyperlink function would look like this:

=HYPERLINK("#'Sheet A'!$A$1","Hyperlink text")

So the trick is, to find the address of the cell you are looking for.

Suppose the value you need from the table is in cell F1 and the table is named Table1 and you are looking in Column1 of the table. This formula gives a direct link to the found cell:

=HYPERLINK("#"&CELL("Address",OFFSET(Table1[[#Headers],[Column1]],MATCH(F1,Table1[Column1],0),0,1,1)),"Link to: " & F1)

 


Comment by: Gerry (10/15/2012 3:55:13 AM)

Hi, is it possible to create a table which is based off information in another table that updates when the original table is updated; for example, if I add a row in one table, is there a way to have the other table add a row, and calculate based on the original table data as the other rows in the second table do? Thanks.

 


Comment by: Jan Karel Pieterse (10/15/2012 1:05:20 PM)

Hi Gerry,

There is no automatic way to do this, apart from either using a bit of VBA code, or by pre-filling extra rows in the second table which do not display anything as long as the associated row in the first table is empty:

=IF(Table1[Column1]="","",Table1[@Column1])

 


Comment by: Jan Karel Pieterse (11/14/2012 11:04:27 AM)

test message

 


Comment by: Hasan (12/15/2012 6:55:14 AM)

Thanks a lot for the very helpful information. I noticed that you could reference a cell in the same row of a table by typing the column name(example : "=[Column1]" rather than typing "=[@Column1]", however, if the formula contains a need to reference the whole column "=[Column1]*[@[Column1]]" and you use the column name. Are there are performance differences?

 


Comment by: Jan Karel Pieterse (12/17/2012 9:39:38 AM)

Hi Hasan,

I don't think it makes a big difference, but I do think it is better to be explicit on what your formula is using. Some functions WILL use the entire column if you use this referencing technique and others will not, depending on the function.

 


Comment by: Scott Shanks (1/16/2013 1:40:20 PM)

Hi Jan,

As soemone thats used Tables including in VBA since 2007, this is still a really useful resource!

Is there a more elegant/concise way to get the lngRow & lngCol values below?

Public Sub GetTableLocation()

    Dim ACell As Range
    Dim tbl As ListObject
    Dim lngRow As Long, lngCol As Long
    Dim strColumn As String
    
    Set ACell = ActiveCell
    Set tbl = ACell.ListObject
    
    With tbl
        lngRow = Selection.Row - .HeaderRowRange.Row
        lngCol = Selection.Column - .ListColumns(1).Range.Column + 1
        strColumn = .ListColumns(lngCol).Name
Debug.Print .ListColumns(strColumn).DataBodyRange(lngRow)
    End With

End Sub

 


Comment by: Gwen (1/16/2013 9:30:03 PM)

I would like to be able to modify a table and just view that same table in multiple worksheets in the same workbook.

I am really using this so that I can use the table as a constant list, whose size and data (strings only) may change from time to time and display that data in all worksheets. If there is a better way to do this, let me know.

Thanks for any help you can provide.

 


Comment by: Jan Karel Pieterse (1/17/2013 8:31:34 AM)

Hi Gwen,

Why do you want to show that same list on all worksheets?

 


Comment by: Gwen (1/17/2013 5:34:21 PM)

I just "inherited" a workbook where each sheet is an employee of the company, with data for their employee reviews. Each worksheet is printed and given to each employee at review time. But at the bottom, we'd like to put a note (same note for everyone) about benefits or benefit changes so that the information is on each employee's sheet when they get it.     It is all text. It could be a text box, or any other object to display text.     I just don't want to copy and paste for each employee (80+) when there are changes.
I an relatively new to Excel, but am a programmer. I have done a bit of looking into Text Box, Table, List, etc.. I would like for who ever "inherits" this from me to not worry about the number of lines in the text (table or list). I would like them to be able to change the text in one place and have it update on all employee worksheets.

 


Comment by: Gwen (1/17/2013 6:00:25 PM)

*add to last note*

If there is a way to put Carriage Returns in a text field, then I can format the cell for wrap, make it big, and it should work to fit my needs. But I couldn't find a way to put CRs in the text

 


Comment by: Jan Karel Pieterse (1/18/2013 4:36:27 PM)

Hi Gwen,

You could put the note in the footer of the sheets.
If you group all sheets (by clicking the first and shift+clicking the last), you can set up that footer for all sheets in one go.
Same to change the footer text later.
Do not forget to ungroup the sheets (right-click the tab and select ungroup sheets) when you're done.

 


Comment by: Ziyauddin Kazi (1/20/2013 3:46:17 PM)

01)Night Duty is counting from 22:00 To Morning 06:00
02) I am working
A) From 18:10 To 23:20 ( Night Duty will be 1:30 )
B) From 20:30 To 03:15 ( Night Duty will be 5:15 )
C) 22:30 To 04:25 ( Night Duty will be 5:55 )
D) 2:10 To 07:30 ( Night Duty will be 3:50 )
What formula i can use to find above Night Duty.
Waiting for reply by mail.
Thank you

 


Comment by: Jan Karel Pieterse (1/20/2013 6:59:54 PM)

Hi Ziyauddin,

Suppose the starttime is in cell A2 and the end time in B2.
In C2 enter this formula to calculate the total hours worked:

=IF(B2<A2,1,0)+B2-A2

In D2, this formula then gives # night shift hrs:

=IF(A2<TIMEVALUE("22:00"),A2-TIMEVALUE("22:00"),0)+C2

 


Comment by: sonu (1/21/2013 8:45:31 AM)

how to link table in another work sheet by using check box

 


Comment by: Jan Karel Pieterse (1/22/2013 8:52:06 AM)

Hi sonu,

Can you please give more information?

 


Comment by: Vicky (2/10/2013 11:21:46 PM)

Hi,
Thanks for the information above. It really helps.

I am trying to get do a cell reference say cell A1 in a sheet to a table (say R2C3).
When i do so it just shows in RC format.

How can i use the =TABLE[ function to do this referencing.

I need a formula so that i can use it and not to reference to the cells in table manually.
Please help.

 


Comment by: Jan Karel Pieterse (2/11/2013 11:39:54 AM)

Hi Vicky,

Are you trying to add a formula using VBA?

 


Comment by: Khurram Ali (2/11/2013 8:20:34 PM)

Your introduction to data tables deserves my thanks.

My question is how can the data table replace the offset function to create a dynamic range and an interactive range formula. I have heard that since the arrival of Excel 2007, it is considered that table feature of excel is superior to offset function for creating a dynamic/interactive range etc.
Could you please explain, with some easy to advanced examples (better if in flv files) so that I can learn and start making good use of the data table.

Thanks

 


Comment by: Jan Karel Pieterse (2/12/2013 8:55:45 AM)

Hi Khurram,

Anywhere where you would normally use a dynamic range name, you can instead use a direct reference to the table. Either by using the structured table referencing shown in this article, or by using direct cell references as you're used to.
Excel will make sure your range expands and contracts with the size of the table. Dynamic range names using OFFSET are no longer necessary.

 


Comment by: ALF (2/19/2013 2:05:23 PM)

HI,

This feature is great but I have issues seeing tables created in Excel with MS Query (Which would made sens...) Is there a fix around this? I have tried to look on several forums but the only answer is Convert your table into a rang (Not Dynamic range becaus MS Query does not see Dynamic ranges either...) Do you know of any fix or work around for this?

Thanks in advance :-)

ALF

 


Comment by: Jan Karel Pieterse (2/19/2013 4:31:14 PM)

Hi Alf,

If you are trying to use MSQUery to pull in data from a table in another file, simply create a range name that spans the entire table. The range name will auto-expand with the table AND MSQuery will be able to find the range name.

 


Comment by: Shripad Lale (3/6/2013 6:49:07 AM)

Thank you, Jan Karel Pieterse! Your tip saved my life! Funny how such an important piece of information is so hard to find! Now, my custom table styles are available to all worksheets. This was important to me, since I don't care too much about the choice of colors the default styles use. Too loud for my taste! Thanks again.

 


Comment by: Praveen (3/30/2013 1:25:02 PM)

Hello,

I have a Master Database with employee details. I want all employees from one department in individual sheets in the same workbook.
Please help

 


Comment by: Jan Karel Pieterse (4/2/2013 2:02:52 PM)

One thing you can do is create a pivot table using the source data and add the employee name as a page field. Then you select The options tab of the pivot table tools and click the Options button on the left. Select "Show Report Filter pages".

 


Comment by: Mamadou (4/26/2013 3:19:50 AM)

I'm going to have my first finance course next May. Glad to be on this site. Tks for this lessons

 


Comment by: Klaas Wijbrans (4/26/2013 9:35:17 AM)

I have a table with in its header numeric data. I need to interpolate data values for a specific row. I tried to do this by doing a MATCH less than on the header information, but I am getting a #N/A as a result. Is there any way around this?

For example:
headers 400, 1380, 1385, 1400, 1600, 1700
item1 10, 1.0, 100, 1, 1, 100

And then I need to interpolate for example for the value 550, which should find 400, and then interpolate between 400 and 1380.

Is there any way around this, or would I need to fall back to VB?

 


Comment by: Jan Karel Pieterse (4/26/2013 2:31:28 PM)

Hi Klaas,

The MATCH function always returns either an exact match (or NA if not found), or the next higher or lower match(depending on the last argument and sort order). It cannot interpolate.

So if 550 is in cell C3 and your first row starts in cell A1, this finds 400:

=INDEX(A1:F1,MATCH(C3,A1:F1,1))

Not sure how you would want to interpolate, linear?

 


Comment by: Jonathan Papillo (5/3/2013 11:46:47 PM)

I am building a data spreadsheet using tables.

In one of my table columns I have a yes/no as to if this particular row should be reported

I have built a macro that will copy the table to a new spreadsheet and will start walking down the table and deleting rows that are not to be reported, giving me exactly what I am looking to report out.

Unfortunately the only way I was able to make my macro work was to hard code the size of the table (which of course is going to be always changing/growing).

How in the macro language can I reference the table and get the number of rows it contains so my code can get away from the limitation of hard coding.

My table is on a sheet called "AI List" and my table is called "AITable"

I am very new to VBA so code samples for me to start from would be the most helpful.

Thanks,

-J

 


Comment by: Jan Karel Pieterse (5/4/2013 10:47:30 AM)

Hi Jonathan,

The data area of a table can be accessed like so:

Worksheets("AI List").ListObjects(AITable").DataBodyRange

 


Comment by: Hamid (6/4/2013 10:26:20 AM)

Hi there,
I am using tables as you have described, and my question is:
I have 5 different table. there is a 6th table that gathers its information from the first 5 tables.
How can I refer to which table to look at using cells in the 6th table?
another way of putting it is, when looking up I need to specify 1)a cell to be looked up, 2)a range 3)the column/row index number.... how can I identify the range (which can be from different tables) using a value in a cell?
If i'm not making much sense it's probably because I am not making sense; but if you email me I can send my workbook and you can see what I mean...
regards
H

 


Comment by: Jan Karel Pieterse (6/4/2013 11:53:28 AM)

Hi Hamid,

You might be able to use the INDIRECT function to do what you want perhaps? To know the exact argument the INDIRECT function needs, it is best to first create a direct cell reference in the formula and study that syntax closely. Then you can try to recreate that in the INDIRECT function.

E.g. suppose the sheetname is in cell A1 and you want to get cell B1 from that sheet:

=INDIRECT("'" & A1 & "'!B1")

The apostrophe's are needed in case the sheetname contains a space character.

 


Comment by: HAMID (6/4/2013 3:30:49 PM)

Worked perfectly,
Thanks a lot :)

 


Comment by: Jacob Miller (6/4/2013 7:45:02 PM)

Hi,


I was ecstatic when i realized that tables autofill formulas into newly inserted rows, until i ran into the following problem -

I have a table where each row contains three formulas referring to a cell to the left on the same row (an ID number), which pulls data from another worksheet.

When i insert a new row, rather than referring sequentially to the cell in the same row, say A15, the formula referrs to the cell immediately below, A16. What is the fix?

 


Comment by: Jan Karel Pieterse (6/4/2013 8:14:12 PM)

Hi Jacob,

Two questions:

1. How many columns does the table have in total and how many contain a formula?

2. Can you share the formula?

 


Comment by: Jacob Miller (6/5/2013 7:58:46 PM)

1. 17 columns, three of which contain a formula, all of which should refer to the same values in column A.

2. The formula is a bit convoluted, because it refers to a separate workbook but here it is (from row 6): =VLOOKUP(A6,CHOOSE({1,2},'[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,'[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000).

The key issue here is, the row 6 VLOOKUP function correctly refers to A6, just all the other formulas in the table refer to the column value in the same row as they are. But when i insert a new row, the formula autofills incorrectly. rather than VLOOKUPing "A7", it pulls from "A8", the row immediately below.

 


Comment by: Annie (6/6/2013 5:21:17 AM)

What is required to be able to resize tables when worksheet protection is applied for locked cells and the columns the table uses are unlocked?

 


Comment by: Jan Karel Pieterse (6/6/2013 8:10:21 AM)

Hi Jacob,

The formula looks incomplete to me, as it does not have the column argument of the VLOOKUP function. Also, it is unclear to me what the CHOOSE function is doing, given it has a fixed array argument between brackets.

If I try a "normal" vlookup formula pointing to a different workbook, all is fine, it keeps pointing to the same row as it did after inserting a row in the table.

I have heard of this situation before, but alas cannot reproduce it.

Perhaps you can send a copy of your file to me by email?

 


Comment by: Jan Karel Pieterse (6/6/2013 8:12:14 AM)

Hi Annie,

The only way I know is to have a bit of VBA code that does the insertion (after first unprotecting and reprotecting afterwards.

 


Comment by: Jacob Miller (6/6/2013 7:16:32 PM)

My fault, I cut off the formula when pasting it over - the actual formula includes the column argument as follows: =VLOOKUP(A8,CHOOSE({1,2},'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY -K12 CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000),2,0)

The Choose formula is a workaround, because VLOOKUP only flows left to right, and the workbook i'm referencing went right to left.


I would be most grateful if you could take a look at the file - i'm emailing it to you.

 


Comment by: Jan Karel Pieterse (6/7/2013 2:06:03 PM)

Hi Jacob,

Ah, that explains things. Never saw that workaround before.
I'd advise to use INDEX and MATCH:

=INDEX(,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY -K12 CACIQUE IDS.xls]Day Center Clients'!$A$2:$A$22000),MATCH(A8,'G-drive:Users:Reception:CACIQUE ID NUMBERS:[DO NOT COPY - CACIQUE IDS.xls]Day Center Clients'!$B$2:$B$18000,0))

 


Comment by: Jacob Miller (6/7/2013 8:06:13 PM)

Switched all the formulas to index and match and the problem cleared right up. Thank you so much!!

 


Comment by: Jan Karel Pieterse (6/7/2013 9:14:00 PM)

Hi Jacob,

Excellent!

 


Comment by: Kevin (6/19/2013 7:13:23 AM)

Hi There,
In table reference, how would you specify a row instead of the current row? For example .. this =Table1[@Discount] uses the current target cell row number to find that item in the Discount Field/Column in the table. But what if I wanted a different row number .. say the the target row is 2 but I want row 4 from the Table?

Thx .. Kevin..

 


Comment by: Jan Karel Pieterse (6/19/2013 7:36:28 AM)

Hi Kevin,

You can't do that using table references, you'd have to use a normal direct cell ref to do that. Simplest is to just click on the cell, if it isn't on the same row Excel will automatically adjust to a normal reference.

 


Comment by: sowmyalillyput@gmail.com (6/19/2013 2:13:05 PM)

in excel how to remove default name column1 ,column2,if like this appears when we remove format table in excel please give me steps to remove default

 


Comment by: Jan Karel Pieterse (6/19/2013 3:48:42 PM)

Hi sowmyalillyput,

Not sure what you need, but can't you just select the header cells and hit the Delete button?

 


Comment by: Asoka (6/24/2013 8:28:56 AM)

Hi
This is a fantastic article. Pls tell me a way or protecting some columns of a table. When I lock the cells & protect the sheet the table feature goes off. Is there any way to protect the columns with formulas in an Excel Table?

 


Comment by: Jan Karel Pieterse (6/24/2013 1:35:13 PM)

Hi Asoka,

You are correct, protecting a worksheet with a table disables quite some functionality of the Table.
If you really need the sheet to be protected, then the only way around this is to write VBA code that does the actions that you do want to allow the user to do.

 


Comment by: kirubakaran (6/25/2013 2:30:59 PM)

Hi Pieterse,

I need a solution for tis if we work n excel we select any column and select the option means its automatically closed. we reinstall tat but it shows tat same error oly.if thr is any solution for tat means pls help us.

 


Comment by: Jan Karel Pieterse (6/25/2013 5:28:18 PM)

Hi kirubakaran,

Check out this page, perhaps it helps:

http://www.jkp-ads.com/articles/corruptfiles.asp

 


Comment by: Daniel Rosenqvist (7/11/2013 8:01:31 PM)

I work with Excel 2010 and the problem I'm having is that I have two tables with a common header.
I want to take out specific values from table1 and make an average of those values in table2.
The order of the names is not the same and with the table1[@col2] just puts the values in the same order as table1.
table1 includes all people and table2 includes a section of people from table1.

If someone knows how I could solve this it I would be greatful.

 


Comment by: Jan Karel Pieterse (7/11/2013 9:17:00 PM)

Hi Daniel,

I kindly request you to ask this question at Eileens lounge:
www.eileenslounge.com

 


Comment by: Ed Grubbs (7/14/2013 10:02:18 PM)

I still have a black and white printer. This will be great when I move up in equipment.

 


Comment by: Mandi Hanson (7/18/2013 6:35:48 PM)

I have formatted cells within my table but the formatting does not follow through when I expand my table. Any thoughts?

Thanks

 


Comment by: Jan Karel Pieterse (8/8/2013 9:13:18 PM)

Hi Mandy,

I think only the table style automatically expands.

 


Comment by: Pritu gupta (8/12/2013 6:45:48 PM)

Hi,
I hope that it is only used in making report cards . It is because the usage of the tables cannot be written.

 


Comment by: Jan Karel Pieterse (8/13/2013 7:35:39 AM)

Hi Pritu,

Can you please explain what you mean?

 


Comment by: Mathias Richard (8/16/2013 4:16:47 AM)

I have 2 tables that refer to the same columns in tables in an external Excel worksheet with tabs for the tables (i.e., an Excel SS of datasource).

I link to that Excel datasource via an ODBC driver (Actual Technologies), and query refers to the tables very nicely in the designer, and SQL.

I can update each on the tables in my workbook, one at a time using refresh data; but, if I try just refresh on the data tab on the ribbon, excel crashes.

Any explanation, or work around?

I'd also like to know exactly which program is compiling the SQL so I can find out it's limitations, and deviations from the standard.

Thanks so much -- I've spent a whole day on this so far.

 


Comment by: Jan Karel Pieterse (8/16/2013 3:41:12 PM)

Hi Mathias,

Perhaps the crash does not occur if you refer to the tables using range names?

 


Comment by: Mathias Richard (8/17/2013 3:29:14 AM)

I checked, and both are simple range names.

The queries are on two woorksheets in the same workbook. The 2nd one is linked to a cell in the 1st via the Parameter setting. The 1st sheet's query has it's parameter set from a field outside the query range that receives input from the user -- a number entered into a cell. In effect two queries are linked, like thru a customer number. If the 1st one is updated, and then the 2nd one, then they stay in sync be the customer number.

This is all a test for a real application. I wrote a macro that updates the queries in order, and it runs just fine.

The big question is all about selecting "Refresh Data" on each works just fine; however, choosing "Refresh All" on the data banner, or the button on the banner crashes. BTW: the main menu only has Refresh in it; but, I think it used to include the All button.

Thanks...Matt

 


Comment by: Jan Karel Pieterse (8/17/2013 8:52:39 PM)

Hi Matthias,

Hmm. I've not seen that one before, but perhaps this is a timing problem because the refreshes are done asynchronously and hence may be executed more or less at the same time.
You could of course add customUI to the file to change the behaviour of the refreshall button.

 


Comment by: Mathias Richard (8/19/2013 3:22:04 AM)

I'd be happy if someone verified my findings. Should be pretty simple; 2 queries on same db on same sheet, press update on each, should work; now press the update all. Let me know it your crashes.

 


Comment by: Jan Karel Pieterse (8/19/2013 1:19:01 PM)

Hi Matthias,

I have had this happen before, but only with Excel 2007 and with code that updates the CommandText of the queries and then refreshes them. The behaviour was so bad that I moved everything away from querytables (for that workbook) and used VBA (ADO) to write the recordsets to Excel.

 


Comment by: Kristine Goris (10/25/2013 4:08:00 PM)

Hi,

How do I change de order of de columns once I've created the table?

Thx,
Kristine

 


Comment by: Jan Karel Pieterse (10/25/2013 4:55:06 PM)

Hi Kristine,

You can simply cut a column and then right-click on the column immediately to the *Right* of where you want to move that column to and select "Insert cut cells".

 


Comment by: Per Inge (11/21/2013 6:04:57 PM)

We are using tables in Excel a lot and we like the functionality. For relatively small tables it works great, but when the number of rows increases the time it takes to populate the table (programatically with C# code) increases quite a lot.

Are there some size limits where tables are not practical to use ?
Are there any calculations or update features that we can switch off during the population to speed thing up ?

PS JKP. Thanks for so many great postings on the net

 


Comment by: Jan Karel Pieterse (11/22/2013 11:20:47 AM)

Hi Per,

One thing you can try is to add the information below the table whilst leaving one blank row. Then after adding the data, delete the row and change the size of the table. Not sure if it will work, but it is worth the try.

 


Comment by: Kris Wright (2/5/2014 3:01:20 PM)

Hi

I am trying to create a new pivot table in a new workbook who's Data Source is a table in the workbook, which contains the VBA code.

I can create a Pivot Table as you describe above, but it will only refresh when the source workbook is open.

Is there any setting that needs to be altered to allow refresh when source is closed?

Thanks

Kris

 


Comment by: Jan Karel Pieterse (2/5/2014 7:25:39 PM)

Hi Kris,

Perhaps if you use MSQuery to tie the pivot table to the data rather than direcly to the Excel table?

 


Comment by: Carsten (2/26/2014 1:33:26 PM)

I'm trying to use the TIME function in a table:

=TIME([@[*Start_hh]];[@[*Start_mm]];[@[*Start_ss]])

It returns zero (0). When I use the function outside the table it works just fine, so the question is, does anyone know if the TIME function works within a table?

Thank you
Carsten

 


Comment by: Carsten (2/26/2014 1:35:27 PM)

Just posted a question about the TIME function. I forgot to mention that I use Excel 2010.

 


Comment by: Jan Karel Pieterse (2/26/2014 4:22:11 PM)

Hi Karsten,

Works fine for me.

 


Comment by: Mitchell (2/28/2014 2:04:13 AM)

Can I protect the data in 2010 Excel tables, but still give users the ability to sort and filter the tables?

 


Comment by: Jan Karel Pieterse (2/28/2014 2:09:37 PM)

Hi Mitchell,

Odd enough, if you protect the sheet with the Sort and Autofilter boxes checked, filtering is allowed, but sorting isn't. (Excel 2010). Must be a bug.

 


Comment by: Zack Barresse (3/11/2014 9:15:49 AM)

Oddly enough, if the table data body range cells have the locked protection property set to false (not locked) you will be able to sort on a protected worksheet. I'm with Jan though, I don't think this should be 'normal' behavior, although I doubt we'll get it fixed.

Please note you MUST ALSO unprotect the header row. No need to worry about the total row, but it won't work if the header cells are locked also.

 


Comment by: Jan Karel Pieterse (3/11/2014 10:38:03 AM)

Hi Zack,

Thanks, that is good to know!

 


Comment by: Zack Barresse (3/11/2014 10:40:36 AM)

Very welcome. :) I should also mention this has the same behavior in 2010 as well as 2013, even after SP1 (haven't tested 2007).

 


Comment by: Todd (4/3/2014 1:20:58 PM)

LOVE THE ARTICLE!

My question is this... If I do the format as table option for my table, I lose data when a column is sorted. Example: In Column C I have my name TODD and in Column D I also have my name TODD. If I select the sort on column C to be TODD, I lose the ability to sort column D with TODD.

 


Comment by: Jan Karel Pieterse (4/3/2014 1:21:52 PM)

Hi Todd,

Thanks!

You cannot have two columns with the same name in a table unfortunately.

 


Comment by: Jim (4/7/2014 7:20:18 PM)

In Excel 2010, am trying to make single & 2-value data tables using what-if.. Follow tutorials, but all values are the same in the tables---either across or down rows. What could I be doing wrong? Thanks!

 


Comment by: Jan Karel Pieterse (4/9/2014 7:24:07 AM)

Hi Jim,

I suggest to go to www.eileenslounge.com to ask your question, more people are there to look at your problem.

 


Comment by: Peggy (4/28/2014 6:52:13 PM)

How do you "un-do" the table? I know how to un-do the table style but I want to restore the data to pre-table status.

 


Comment by: morgan harding (4/29/2014 3:21:22 PM)

Each 'page' in a spreadsheet is called a _____.

what is the answer

 


Comment by: Jan Karel Pieterse (5/8/2014 7:47:51 AM)

Hi Morgan,

A worksheet?

 


Comment by: Jan Karel Pieterse (5/8/2014 7:55:10 AM)

Hi Peggy,

Use the Convert to Range button on the Table tools tab of the ribbon.

 


Comment by: Robert, Plumstead (6/5/2014 12:54:46 PM)

Hi Jan
I have: excel 2013, a worksheet with many rows and cols of data. I have changed the data area into a table = after selecting the entire range (and reverted back to a range then table etc many times - I am ok with this working correctly). I have selected a table style - while the cursor covers the entire table = heading row changes colour as expected. With cursor in the table I selected check box "banded rows (and unselected) But no banded rows appear (ditto banded columns - no banding). Question: what could be going wrong?
Thanks
Robert

 


Comment by: Jan Karel Pieterse (6/5/2014 2:36:43 PM)

Hi Robert,

All I can think of is that your table has a table style applied that has no alternating row coloring.

 


Comment by: ronald omegna (7/21/2014 1:41:34 PM)

jan,
excellent overview.
can i take advantage of tables in the following way?

1. column 1 of the table includes unique key field
2. column 2-10 of the table use lookups to a db and do calculations specific to the unique key field
3. each row is driven from the key field in column 1 of the specific row
so then:
4. suppose i want to refresh the table weekly?
5. and the refresh is simply importing to the table a new list of key fields in column 1 [there may be some new additional key fields and some existing key fields may no longer be in the import

will the table expand/contract based on the import of key fields in column 1? [i think yes? correct?]
since the remaining columns are formula driven from the values in column 1, will the table re-generate itself when the import is done? [i'm not sure on this?]
can table features do this? or do i need some code to walk through each column 1 key field and force the remaining columns to re-calculate?

i get how powerful the table concept is, i'm just not sure if what i am asking is outside its' feature set.

thanks,
ron

 


Comment by: Jan Karel Pieterse (7/21/2014 8:56:09 PM)

Hi Ronald,

Yes you should be able to do that. If your keyfield is -for example- tied to a database connection, refresshing the connection will automatically expand or contract the table including formulas.

 


Comment by: Darren (8/17/2014 12:07:00 AM)

How do I lock the table on my work sheet so no one can change it?

 


Comment by: Jan Karel Pieterse (8/17/2014 11:11:03 AM)

Hi Darren,

By protecting the worksheet itself. Review tab, Protect sheet.

 


Comment by: Ray (9/16/2014 10:09:16 PM)

Hello,

I have a workbook with multiple worksheets(individual salesperson commission by customer ISCC)which calculate off a single worksheet(gross profit by customer GPC). I set up a data range in GPC, and in each individual sheet used a data import from other sources/connection wizard/OBDC DSN/Excel Files/range name. I can then update GPC, including adding rows, and when I select Refresh All, my ISCC sheets are updated perfectly. The problem is I need to calculate commissions monthly, as well as creating/updating quarterly and yearly forecasts. When I rename the file to create one of the new files, all data updates continue to be attached to the original file. I cant seem to figure out how to have the data stay with the renamed file. Thank you.

 


Comment by: Jan Karel Pieterse (9/17/2014 9:23:56 AM)

Hi Ray,

You should be able to change the sourcefile from the Data tab of the ribbon, "Connections" button. Then click the properties button for each connection in turn and look at the Definition tab. Make your changes there.

 


Comment by: Ray (9/17/2014 4:48:00 PM)

Thanks Jan,

That work's but now I realize maybe I should be handling this data differently since I'd be constantly updating the ISCC sheets in multiple workbooks. As mentioned the issue is that I have actual commission data, which believe it or not most times changes right up to the due date, I have quarterly forecasts , which I actualize each month, and yearly forecasts, which are actualized as well. Any suggestions on how to effectively compartmentalize data so it travels with the appropriate workbook? Thanks,

Ray

 


Comment by: Jan Karel Pieterse (9/18/2014 7:16:03 AM)

Hi Ray,

Depends. Do the old copies really need to stay "connected" to their sources, or is it OK for them to become static? In that case, you can keep using the same set of files which shows the most up-to-date information and save copies at times you need a snapshot. In these copies you just remove the connections.

 


Comment by: Ray (9/18/2014 2:53:21 PM)

Hi Jan,

Yes the files need to stay connected, at least until the point where I can make them final, and then remove the connections. The issue is that I'm doing the actuals, quarterly and yearly forecasts simultaneously. I guess I can set up a template for each but would like to avoid duplicity if I could.

Ray

 


Comment by: Jan Karel Pieterse (9/19/2014 12:11:45 PM)

Hi Ray,

I guess I fail to understand how exactly you work with those connections.

 


Comment by: Ray (9/19/2014 1:36:38 PM)

Hi Jan,

No problem, I've decided to make an individual template for each commission type.

Ray

 


Comment by: Robert Galik (10/7/2014 12:51:51 PM)

I am using a table that continually expands, requiring 5-10 additional rows per week. I needed to protect the worksheet to preserve several formulas, and the ability to tab from the last active cell and create a new row disappeared. I believe this is due to the sheet protection. I recorded a macro that first unprotects the sheet, adds 10 rows to the table, sorts the table by date, the turns protection back on. I was pleased it worked as well as it did, but even with the new (blank) rows created, when I tab forward from the last cell in the last column, instead of wrapping, the active cell leaves the table to the right. (There is no data adjacent to the table)
Thanks for any suggestions

 


Comment by: Jan Karel Pieterse (10/8/2014 10:28:18 AM)

Hi Robert,

If you protect the sheet and disallow selecting of locked cells and unlock the appropriate cells in the table, you should stay in the table.

 


Comment by: Robert Galik (10/8/2014 7:03:30 PM)

With the sheet protected and the column adjacent to my table locked, when I tab from within the table, I still exit the table. I cannot enter data in the new active cell because of the protection. I am still stumped, but thank your for your suggestion.

 


Comment by: Jan Karel Pieterse (10/9/2014 9:45:48 AM)

Hi Robert,

When protecting the worksheet, do you ensure locked cells cannot be selected? That is key to make this work.

 


Comment by: Robert Galik (10/10/2014 3:44:58 AM)

Through trial and error, the table is working properly if both 'users can select unprotected cells' and 'users can select protected cells' are checked in the protect worksheet dialog box. It may be off topic, but I had a second issue. After running my macro which unprotects, add rows, sorts by date and reprotects, I am always returned to the top of the table, when I would prefer to be in the first empty cell at the bottom. Can you explain how to accomplish this?

 


Comment by: Jan Karel Pieterse (10/10/2014 5:06:18 PM)

Hi Robert,

Code like this selects the last row of the table:


    Dim oL As ListObject
    Set oL = ActiveSheet.ListObjects(1)
    oL.DataBodyRange.Rows(oL.DataBodyRange.Rows.Count).Select

 


Comment by: Quyen Nguyen (10/21/2014 9:18:48 AM)

Hello,
Could anyone help me? I want to convert a table, like a first table, to other type, like the second one.
Thanks!

Table 1
DG A B C D
1 500 460 450 420
2 450 440 430 410
...

table 2
A1 500
A2 450
B1 460
B2 440
C1 450
C2 430
D1 420
D2 410
...

 


Comment by: Jan Karel Pieterse (10/21/2014 3:38:33 PM)

Hi Quyen,

This is what is called "unpivoting" your data.
- Right-click the quick access toolbar and select "Customize quick access toolbar".
- in the "Choose Commands from" dropdown, select "Commands not in the ribbon".
- Find the entry "PivotTable and PivotChart Wizard
- Click the Add button
- Click OK

- Click the new Pivottable wizard button
- Select "Multiple consolidation ranges"
- Click Next
- Select "I will create the page fields"
- Click Next
- Select your data
- Click Add
- Click Finish
- Double-click the grand total of the newly inserted pivottable.

 


Comment by: Quyen Nguyen (10/23/2014 4:07:15 PM)

It works perfectly :D. Thank you very much Jan Karel Pieterse!

 


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here: www.eileenslounge.com.

Please enter your name (required):

Your e-mail address (optional but if you want me to respond it helps!; will not be shown, nor be used to send you unsolicited information):

Your request or comment:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].