Filter Multiple Tables in Excel with VSTACK and FILTER

This problem appears when songs, inventory items, or other records are split across several Excel tables. The most reliable fix is usually to combine the records into one structured table, then filter the rating column once. A formula-based approach works when the tables must remain separate.

The problem

A MacBook user asked how to filter data from different tables at the same time and show only songs with a specific rating, such as 10. This came up in the r/excel community, where the suggested solutions included redesigning the spreadsheet, combining tables with VSTACK, and using Power Query.

Excel's built-in filter controls work best when related records share one table. When songs are divided into separate blocks, each table has its own filter, so filtering one table does not automatically filter the others. Excel adds filter controls to the headers of a table, but those controls apply to that table's rows, not to every table on the worksheet. Microsoft Support documents this standard table and range filtering behavior.[7]

Before choosing a formula, check three things:

• Are these actual Excel tables, created with Insert > Table?
• Do all tables use the same column structure?
• Are all tables in the same workbook?

These details determine whether a simple consolidation formula will work or whether the workbook needs restructuring.

The solution

The strongest long-term solution is to create one master table. Use one row per song and one column for each field, such as Song, Artist, Album, Genre, Rating, and Source Table. Treat the former table names or section headings as values in a column, rather than as separate areas of the sheet.

For example:

Song	Artist	Genre	Rating	Collection
Song A	Artist A	Pop	10	Favorites
Song B	Artist B	Rock	8	Road trip
Song C	Artist C	Jazz	10	Favorites

This structure lets you use one filter on the Rating column. Select the filter arrow, clear the existing selections, and select 10. Excel will show every song with that rating in the master table.

It also improves sorting, reporting, PivotTables, charts, and later automation. A table with consistent columns is easier to connect to an inventory system, quoting process, or reporting workflow than several visually separate blocks.

If separate tables are necessary, use VSTACK to place their rows one below another, then use FILTER to return only the matching records. The general pattern is:

=FILTER(VSTACK(Table1,Table2,Table3),VSTACK(Table1[Rating],Table2[Rating],Table3[Rating])=10,"No songs found")

VSTACK combines the tables vertically. FILTER returns only rows that meet a condition. The first VSTACK supplies the full records. The second VSTACK supplies the Rating values used for the test. The number 10 is the required rating. The final text appears when no match exists.

This formula assumes the tables have compatible columns and that the rating column has the same meaning in each table. If the tables use different column headings or different column orders, fix the structure first. Power Query can combine and shape multiple sources, but it may require mapping columns when the headings do not match.

Power Query is also a suitable option for recurring consolidation. Microsoft describes it as Excel's Get & Transform technology for importing, combining, shaping, filtering, and loading data. Microsoft also documents Power Query support for Excel on Mac, although available features depend on the Excel version.[5][8]

How to apply it

1. Inspect each table. Confirm that every table has the same fields, such as Song, Artist, Album, and Rating. Check that ratings are stored as numbers, not text.

2. Choose the structure. Use one master table if the tables are simply separate sections of the same list. Keep separate tables only when each one represents a genuinely different dataset.

3. Create a master table. Copy the records into one range. Keep one header row at the top. Do not insert blank rows or merged cells inside the data.

4. Add a source column. If the original table names matter, add a Collection or Source column. Enter values such as Favorites, Road trip, or New releases for each row.

5. Convert the range to an Excel table. Select the data and choose Insert > Table. Confirm that the table has headers. Give it a clear name, such as Songs.

6. Filter the rating. Open the filter arrow in the Rating column. Clear all values, select 10, and apply the filter. Excel will display all rated songs with a 10.

7. Use VSTACK when the tables must stay separate. Replace Table1, Table2, and Table3 in the example with the real table names. Verify that the tables contain matching columns in the same order.

8. Test the formula. Add a known rating of 10 to each source table. Confirm that the result includes every matching row and that the columns remain aligned.

9. Use Power Query for repeatable imports. In Excel for Mac, open the Data tab and use the available Get Data or Power Query commands. Combine the sources, standardize the column headings, filter Rating to 10, and load the result into a new worksheet. Refresh the query when the source data changes.

10. Check for errors. Look for ratings stored as text, blank rows, duplicate records, mismatched headings, and tables with different column orders. These issues commonly produce incomplete or misaligned results.

The master-table approach is preferable for most small businesses. A single clean table makes it easier to filter orders by status, products by stock level, customers by region, or invoices by payment state. The workbook becomes a usable dataset instead of a collection of formatted sections.

Implementation angle

For a larger rated-songs workbook, an automation partner could consolidate each source table into a controlled master dataset, validate the Rating field, and publish filtered views for staff. Make or n8n could move new records from forms or cloud files into the central table, while Power Query could refresh recurring imports on the Mac. Claude could help generate and check the VSTACK and FILTER formulas, then test them against sample rows before the workbook reaches users.

Book an AI implementation engagement with Creator Scripts to turn fragile Excel filtering and consolidation into a reliable business workflow.

Frequently asked questions

Can I filter multiple Excel tables at the same time?

Excel’s built-in filter applies to one table at a time. For one combined result, put the records in a single master table or combine compatible tables with a formula or Power Query.

How do I combine Excel tables and show only rows with a rating of 10?

If the tables have the same columns in the same order, use VSTACK to combine their rows and FILTER to return rows where Rating equals 10. For example: =FILTER(VSTACK(Table1,Table2),VSTACK(Table1[Rating],Table2[Rating])=10,"No songs found").

What is the best way to manage several related lists in Excel?

For lists that contain the same kind of records, use one master table with a column that identifies each list or source. This gives you one place to filter and makes sorting and reporting easier.

Elapsed Time Formatting in Excel Format Cells

A weekly total that appears to reset after 24 hours is usually a time formatting issue, not a problem with the SUM formula. Excel stores time as decimal days, so a value of 1 equals 24 hours. The standard hh:mm format shows clock time and wraps back to zero after 24 hours. Use elapsed-time formatting instead.

The problem

This issue came up in the r/excel community discussion about fixing a 24-hour reset. It affects businesses that use an Excel working hours sheet to total employee shifts, weekly hours, overtime, or billable time.

The formula in the total cell is:

=SUM(J8,J13,J18,J23,J28)

This formula adds the five referenced cells. There is no obvious problem with it if those cells contain valid Excel time values.

The likely problem is the cell formatting. With hh:mm, Excel treats the result as a time of day. Once the total reaches 24 hours, the display starts again at zero. For example, 30 hours may appear as 06:00 because Excel displays only the time remaining after complete 24-hour periods.

The underlying value can still be correct. Excel represents time as a fraction of a day. A result of 1.25 means one day plus one-quarter of a day, which equals 30 hours. The display changes, but the stored calculation does not.

The solution

Change the total cell's custom format from hh:mm to [h]:mm.

The square brackets are the important part. They tell Excel to display elapsed hours instead of clock hours. A result of 30 hours will then appear as 30:00 rather than 06:00.

Microsoft's guidance uses [h]:mm for elapsed time that exceeds 24 hours. The h displays hours, the colon separates hours from minutes, and mm displays minutes. The brackets around h prevent the hours from wrapping after 24.

The formula can remain:

=SUM(J8,J13,J18,J23,J28)

SUM adds the values in the listed cells. The commas separate each cell reference. You could also use a range if the cells were next to each other, such as =SUM(J8:J28), but that would include every cell between J8 and J28. Keep the original formula if the gaps are intentional.

Use hh:mm when you want a time of day, such as 08:30 for a start time. Use [h]:mm when you want a duration, such as 37:45 for total weekly hours.

How to apply it

1. Select the cell that shows the weekly total.
2. Open Format Cells. In desktop Excel, right-click the cell and choose Format Cells, or press Ctrl+1 on Windows.
3. Select the Number tab.
4. Choose Custom.
5. In the Type box, enter [h]:mm without the spaces.
6. Select OK.
7. Confirm that the total now shows accumulated hours, such as 30:00 or 42:15.
8. Check the formula bar to verify that the cell still contains =SUM(J8,J13,J18,J23,J28).
9. Test the sheet with a known total. For example, add entries that equal 25 hours and confirm that the result displays 25:00, not 01:00.
10. Apply the same format to any other cells that show weekly, monthly, overtime, or billable-hour totals.

If the workbook is used in Excel for the web, the calculation can still add more than 24 hours, but custom number-format options may be limited depending on the version and editing experience. When the web version does not allow the format to be applied, open the workbook in desktop Excel, set the cell to [h]:mm, save it, and then reopen it online.

Do not change the source shift cells to [h]:mm automatically. A shift beginning at 08:00 should normally remain a clock time. Apply elapsed-time formatting to calculated durations and totals.

If shifts cross midnight, the calculation needs separate attention. For a start time in A2 and an end time in B2, a basic duration formula is:

=B2-A2

This subtracts the start time from the end time. For an overnight shift where the end time is recorded as an earlier clock time, use:

=MOD(B2-A2,1)

MOD returns the remainder after division. Here, it converts a negative-looking overnight difference into the correct fraction of a day. Format the result as [h]:mm.

For payroll or compliance reporting, consider storing the date alongside each shift. A time-only sheet can become ambiguous when an employee works across midnight or when managers review several weeks together.

Implementation angle

For a small business, this fix is a useful first step toward a more reliable hours process. An automation partner could replace scattered references such as J8, J13, J18, J23, and J28 with a structured table containing employee, date, start time, end time, break, and approved hours. Make or n8n could then send approved totals to payroll, invoicing, or a weekly management report, while Zoho Creator could provide controlled data entry instead of an easily damaged spreadsheet.

Creator Scripts can build and check the Excel formulas, validate overnight shifts, and connect the approved hours workflow to the systems your business already uses. Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

Why does my total hours in Excel reset after 24 hours?

This happens because of the time formatting in Excel. If your total is formatted as hh:mm, it resets after reaching 24 hours, displaying only the time remaining instead of the total hours worked.

How do I change the time format to show hours over 24?

To display hours beyond 24, change the cell format to [h]:mm. This format allows Excel to show accumulated hours correctly without resetting after 24 hours.

Can I use the same formula for an overnight shift in Excel?

Yes, you can use a formula to calculate overnight shifts. Use =MOD(B2-A2,1) to handle the end time that occurs past midnight, then format the cell as [h]:mm to display the correct duration.

Ownership Percentages in Excel with SUMIFS

Ownership percentages become difficult when one company owns part of another company, and an individual owns part of several entities. Small-business operators often start with an organizational chart, then discover that direct ownership and indirect ownership require a structured calculation.

The reliable approach is to record each entity relationship as a separate row, calculate each ownership path, and add the results. A chart can show the business structure, but a clean data table should drive the automated ownership calculation.

The problem

This issue came up in the r/excel community discussion about automatically calculating ownership percentages. The example involves John Smith, who owns 25% directly of Dog Dad LLC and 32% directly of Cat Mom LLC. Those two companies have an ownership relationship with Pet Parents LLC, and the goal is to calculate John's total indirect ownership of Pet Parents LLC.

The missing detail is the relationship between Dog Dad LLC, Cat Mom LLC and Pet Parents LLC. Excel cannot infer that relationship from names or from a visual organizational chart. You must enter the ownership percentage for every connection.

For example, suppose:

• John Smith owns 25% of Dog Dad LLC.
• Dog Dad LLC owns 40% of Pet Parents LLC.
• John Smith owns 32% of Cat Mom LLC.
• Cat Mom LLC owns 20% of Pet Parents LLC.

John's indirect ownership through Dog Dad LLC is 10%. The calculation is 25% multiplied by 40%.

John's indirect ownership through Cat Mom LLC is 6.4%. The calculation is 32% multiplied by 20%.

His total indirect ownership of Pet Parents LLC is 16.4%, before adding any direct ownership John may have in Pet Parents LLC.

This is called cascading ownership. Each link in the ownership chain contributes to the final percentage. For a longer chain, multiply every percentage along that path.

The solution

Do not build the calculation directly into the organizational chart. Store the business structure in a simple ownership table.

Create these columns:

Owner	Owned entity	Ownership percentage	Path result
John Smith	Dog Dad LLC	25%	25%
Dog Dad LLC	Pet Parents LLC	40%	40%
John Smith	Cat Mom LLC	32%	32%
Cat Mom LLC	Pet Parents LLC	20%	20%

For a two-step path, calculate the result in a separate calculation table. Use one row per complete path.

Person	Target entity	Ownership path	Indirect ownership
John Smith	Pet Parents LLC	John Smith → Dog Dad LLC → Pet Parents LLC	10%
John Smith	Pet Parents LLC	John Smith → Cat Mom LLC → Pet Parents LLC	6.4%

The formula for the first path is:

=25%*40%

The first percentage is John Smith's ownership of Dog Dad LLC. The second is Dog Dad LLC's ownership of Pet Parents LLC. Excel multiplies them to produce John's indirect stake.

The formula for the second path is:

=32%*20%

To total all paths for John Smith, use:

=SUM(D2:D3)

SUM adds the indirect ownership results in cells D2 through D3.

If John also owns 5% directly of Pet Parents LLC, add that stake separately:

=5%+SUM(D2:D3)

This returns 21.4% total ownership. Direct ownership should not be multiplied by another percentage because it already applies to the target entity.

For three levels, continue multiplying:

=JohnOwnership*FirstEntityOwnership*SecondEntityOwnership

For example, if John owns 25% of Dog Dad LLC, Dog Dad LLC owns 40% of Holding LLC, and Holding LLC owns 60% of Pet Parents LLC, the result is:

=25%*40%*60%

That produces 6% indirect ownership.

The strongest model treats every path as a separate calculation. It is easier to audit than one complicated formula, especially when legal entities, partners and subsidiaries change over time.

How to apply it

1. Create an Excel table with one row for every ownership relationship.

2. Use clear columns such as Owner, OwnedEntity, OwnershipPercent, EffectiveDate and SourceDocument.

3. Enter percentages as actual percentages, such as 25%, not as text like 25 percent.

4. Confirm that ownership percentages for each entity add up correctly. If Dog Dad LLC has three owners, their stakes should normally total 100%, unless the structure includes unallocated shares or another documented exception.

5. List every complete ownership path from the person to the target company.

6. Multiply the percentages along each path. For example, use =B2*C2 for a two-level path, where B2 is the first ownership percentage and C2 is the second.

7. For a three-level path, use =B2*C2*D2.

8. Add all paths that have the same person and target entity with =SUMIFS(ResultRange, PersonRange, PersonName, TargetRange, TargetEntity).

9. SUMIFS adds only the path results that match the selected person and target entity. This lets one summary report calculate ownership for many people and companies.

10. Add direct ownership separately from indirect ownership.

11. Add validation checks. A useful check is =SUM(OwnershipRange), which confirms the ownership entered for one entity.

12. Format the result as a percentage with an appropriate number of decimal places. Avoid rounding the underlying values before adding them.

A practical summary table might contain Person, TargetEntity, DirectOwnership, IndirectOwnership and TotalOwnership. The total formula can be:

=C2+D2

Here, C2 contains direct ownership and D2 contains indirect ownership.

If an owner can reach the same target through several entities, keep each path separate. Do not assume that adding a person's percentages in Dog Dad LLC and Cat Mom LLC is enough. The ownership percentage of each intermediary company must also be included.

For a larger business hierarchy, a flat table is more reliable than manually editing an organizational chart. The chart can remain as a visual report, while the table becomes the source for formulas, checks and automation.

Implementation angle

Zoho Creator can turn this ownership table into a controlled workflow that stores entities, ownership relationships, effective dates and supporting documents in a structured database. Make or n8n can sync approved changes from intake forms into Excel or a reporting database, while automated checks flag ownership totals that do not reconcile to 100%. An AI assistant such as Claude can help generate and review Excel formulas, but the ownership relationships still need to be confirmed against the company's legal records.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

How do I track ownership percentages in Excel?

To track ownership percentages in Excel, create a simple ownership table with columns for owner, owned entity, and ownership percentage. Enter each ownership relationship in its own row to allow for easy calculations.

Can I automate calculations for indirect ownership in Excel?

Yes, you can automate calculations for indirect ownership by using formulas to multiply the ownership percentages along the paths. Use the SUM function to total the indirect ownership from different paths for accurate results.

What if ownership percentages do not add up to 100%?

If ownership percentages do not add up to 100%, check if there are unallocated shares or other exceptions documented in your records. It’s important to ensure that the ownership structure is clearly defined to avoid discrepancies.

Power Query: Load Newest File from Folder

Power Query can reuse the same transformation steps every day while selecting a new source file. For a daily work-items report, the most reliable setup is a folder connection that keeps the newest file and ignores older files. This avoids rebuilding the query or replacing files manually.

The problem

This came up in the r/excel community from someone downloading a daily report, transforming it in Power Query, and needing the workbook to use the next file without losing previous downloads. The core issue affects businesses that receive recurring inventory lists, sales reports, work queues, or operational exports in the same structure.

The goal is not to create a new query for every download. It is to separate the file import from the transformation steps. The import query identifies the correct file. The transformation query cleans and reshapes its contents. Once those query steps exist, a refresh applies them again to the next report.

The most practical approach is to store every daily report in one folder. The folder connection can select the newest file based on its Date modified value, so older files remain available for history or audit purposes. The original discussion is available in the r/excel question about updating a Power Query source file.

The solution

Use a two-part Power Query design:

1. A source query connects to the folder and identifies the file to import.
2. A transformation query applies the cleaning steps to the selected file.

This is usually better than a macro for the data preparation itself. Power Query records repeatable data transformation steps, such as removing columns, changing data types, filtering rows, splitting text, and combining tables. A macro can provide a file-picker button, but it adds code that must be maintained and secured.

For a daily report, the source query should:

• Connect to the folder where reports are saved.
• Sort files by Date modified in descending order.
• Keep the top row.
• Open the selected file.
• Pass its contents to the transformation steps.

The "keep the top row" step means Power Query retains only the newest file after sorting. Each day, the operator saves the new report in the folder and refreshes the workbook. The older reports remain untouched.

A consistent naming convention is still useful. Names such as WorkItems_2026-10-16.xlsx make the folder easier to audit and allow additional filters by file name. However, renaming is not required when the newest file is always the correct source.

A second option is a workbook-controlled file parameter. The operator can type a full file path into a cell, load that cell into Power Query, and use it as the source. This gives direct file selection without opening the Power Query editor. It is useful when the correct file is not always the newest one.

The folder method is more robust for a standard daily workflow. The cell parameter is better when an operator must choose among several reports or rerun an older date.

How to apply it

1. Create a dedicated folder for the daily reports. Do not mix unrelated Excel or CSV files into it.

2. Save each downloaded report in that folder. Keep the column names, table structure, and worksheet layout consistent.

3. In Excel, open the Data tab and choose Get Data, then From File, then From Folder.

4. Select the report folder. In the file preview, choose Transform Data rather than loading every file immediately.

5. In Power Query, filter the file list if necessary. For example, filter the Name column so that only files containing WorkItems remain.

6. Sort the Date modified column from newest to oldest.

7. Select Keep Rows, then Keep Top Rows, and enter 1. This leaves only the newest matching file.

8. Use the file's binary content to open the workbook or CSV. For an Excel report, select the appropriate worksheet or table.

9. Apply the data transformation steps once. Typical steps include promoting headers, removing blank rows, changing dates and quantities to the correct data types, and filtering out totals or notes.

10. Give the queries clear names, such as Source_LatestWorkItems and Transform_WorkItems. Clear names make the workbook easier to maintain.

11. Load the transformed result into a worksheet or the Data Model, depending on how the report will be used.

12. Test the workflow. Add a second report with a newer modified date, refresh the query, and confirm that the result changes to the new file.

13. Configure refresh behavior from Data, Queries & Connections, and the query properties. The source discussion recommends updating the query when the workbook opens, where that option is available in the Excel setup.

14. Document the folder location and file rules in a small instruction sheet. State that the report must use the same columns and that unrelated files should not be saved there.

The file structure matters more than the file name. If a new report changes a column heading, removes a table, or changes a date format, a previously working query can fail. Keep the report schema stable, or add validation steps that flag missing columns before the output reaches the business report.

If selecting a specific file is more important than selecting the newest file, create a small Excel table with one value containing the full file path. Import that table into Power Query, extract the path value, and use it as a parameter in the source step. The operator can then change the path in the worksheet and refresh the workbook. This alternative is more controlled, but it introduces a manual input that can contain typing errors.

Implementation angle

For a small business, this pattern can become a controlled reporting workflow instead of a personal Excel routine. Creator Scripts can connect the daily folder to a structured process in Zoho Creator, or use Make or n8n to place incoming reports in the correct folder and trigger the refresh workflow. A validation layer can check that required columns such as item ID, status, quantity, and date exist before the report is loaded. For teams looking to move beyond spreadsheet-based reporting entirely, Zoho Analytics offers business intelligence dashboards that can connect directly to data sources without manual file handling. Claude can also help document the query steps, review M Code, and identify why a new report no longer matches the expected structure.

Book an AI implementation engagement with Creator Scripts at creatorscripts.com.

Frequently asked questions

How do I set up Power Query to automatically use the latest file from a folder?

Create a source query in Power Query that connects to the folder where your reports are saved. Sort the files by their date modified, keep the top row, and then apply your transformation steps to the data from that file.

Can I use Power Query without constantly changing the source file?

Yes, you can use a folder connection to identify and pull in the newest file automatically each day. This way, you only need to refresh the queries without manually changing the source file every time.

What if my reports have different formats each day?

If the report structure changes, it can cause your query to fail. It's essential to maintain a consistent schema in your reports or implement validation steps to check for required columns before loading the data.