If you notice that Excel tables don’t automatically expand on your computer, to include new data, there might be a problem with the Excel Option settings. See how to change those settings manually, or use a macro to make the change.
When you create an Excel Table, or a Pivot Table, a default style is applied. You can change to a different built-in style, or create custom styles, with your own formatting. There are details below, and an Excel custom styles problem that you might run into.
Do you ever need to compare two Excel Tables? Here’s a simple formula that quickly shows if there are any differences, between tables that have the same number of columns and rows.
With a formatted Excel table, you can turn the Total Row on or off easily, and it shows at the bottom of the table. Someone asked me how to add data to Excel Table with Total Row showing – they were hiding the totals every time they wanted to add data. You don’t need to do that!
This week, while working on a client’s Excel project, I ran into some trouble with Excel tables, while adding new data. Instead of expanding to include the rows, the table just ignored them.
Fortunately, the problem is easy to fix, if you know how, and if you have the patience to do lots of clicking.
Data validation is a great feature in Excel, and I often use it to create a drop down list in a cell. That helps prevent data entry errors, and limits what people can input. You can use data validation rules in other ways too. For example, you can block duplicate entries in Excel table columns, or in a range of cells.
A few years ago, Ron Coderre created his PivotPlay PLUS Add-in that you can download from my Contextures site. This free add-in was designed for Excel 2003, and lets you view and edit the connection strings for pivot tables and query tables that are based on external data queries.
What’s a quick way to combine the items in two table? For example:
Table A has 3 items – Sugar, Coffee and Milk.
Table B has 2 items – Cans and Sticks.
Combine Table Items
How can you create a third table that has all the Table 1 items combined with each of the Table 2 items?
Sugar – Cans, Sugar – Sticks, etc.
Use MS Query
I’ve done this type of item combining with programming before, but this time I used Microsoft Query, to do the work for me.
Add the two tables to the query, with no join line between them, and the results show each item in table 1 connected to each item in table 2.
Read the Details
To see the details for setting this technique up, and refreshing the results table, please visit the Cartesian Join in Excel Using MS Query page on my Contextures website.
The instructions on that show all the steps for creating the MS query, and then sending the query results to Excel, and finally, refreshing the table if the source data changes.