Excel Table Does Not Expand Automatically to Include New Data

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.

Continue reading “Excel Table Does Not Expand Automatically to Include New Data”

Add Data to Excel Table with Total Row

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!

Continue reading “Add Data to Excel Table with Total Row”

Excel Table Doesn’t Expand For New Data

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.

Continue reading “Excel Table Doesn’t Expand For New Data”

Block Duplicate Entries in Excel Table

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.

Continue reading “Block Duplicate Entries in Excel Table”

Counting Query Tables in Excel

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.

Continue reading “Counting Query Tables in Excel”

Create Table Combinations With MS Query

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.
third table has all item combinations
third table has all item combinations

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.

two tables in MS Query with no join
two tables in MS Query with no join

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.

_____________________