Tables in Excel

Excel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.

How to sum a total in multiple Excel tables

To sum a total in multiple tables, you can use the SUM function and structured references to refer to the columns to sum. See example below: Formula =SUM(Table1[column],Table2[column]) Note: the total row must be enabled. If you disable a total row, the formula will return the #REF error. Explanation In the example shown, the formula in I6 is:… read more »

How to conditionally sum numeric data in an Excel table using SUMIFS

To conditional sum numeric data in an Excel table, you can use SUMIFS with structured references to for both sum and criteria ranges. Formula =SUMIFS(Table[sum_col],Table[crit_col],criteria) Explanation In the example shown, the formula in I5 is: =SUMIFS(Table1[Total],Table1[Item],H5) Where Table1 is an Excel Table with the data range B105:F89. How this formula works This formula uses structured references to… read more »

How to calculate two-way lookup VLOOKUP in Excel Table

To do a two-way lookup in an Excel Table, you can use the MATCH function with a structured reference and VLOOKUP. See example below: Recall that VLOOKUP depends on the lookup value being to the left of the value being retrieved in a table. Generally, this means the lookup value will be the first value in the table…. read more »

How to calculate current stock or inventory in Excel

This tutorial covers basic inventory formula. To calculate current stock, or inventory, you can use Excel Tables with a formula based on the SUMIF function. Formula =SUMIFS(In[Qty],In[Color],A1)-SUMIFS(Out[Qty],Out[Color],A1) Explanation In the example shown, the formula in K7 is: =SUMIFS(In[Qty],In[Color],J7)-SUMIFS(Out[Qty],Out[Color],J7) Where “In” is the Excel Table on the left, “Out” is the table in the middle. How this formula… read more »

How to count table columns in Excel

This tutorial shows how to count columns in an Excel table. To achieve this, you can use the COLUMNS function. See example below: Formula COLUMNS(table) Explanation In the example shown, the formula in I4 is: =COLUMNS(Table1) How this formula works This formula uses structured referencing, a syntax that allows table parts to be referred to by name. When… read more »

How to count table rows in Excel

To count rows in an Excel table, you can use the ROWS function. See example: Note: with just the table name, ROWS will count data rows only. Formula ROWS(table) Explanation In the example shown, the formula in I4 is: =ROWS(Table1) How this formula works This formula uses structured referencing, a syntax that allows table parts to be… read more »

How to create dynamic reference table name in Excel

To build a formula with a dynamic reference to an Excel Table name, you can use the INDIRECT function with concatenation as needed. Formula =SUM(INDIRECT(table&”[column]”)) Note: INDIRECT is a volatile function and can cause performance issues in larger, more complex workbooks. Explanation In the example shown, the formula in L5 is: =SUM(INDIRECT(K5&”[Amount]”)) Which returns the SUM of Amounts… read more »

Calculate Conditional Percentile ‘IF’ in table in Excel

To calculate a conditional percentile, you can use an array formula using the IF function inside the PERCENTILE function. See example below: Formula =PERCENTILE(IF(criteria,values),k) Note: This is an array formula and must be entered with control + shift + enter. Explanation In the example shown, the formula in G5 is: =PERCENTILE(IF(Table[Gender]=G$4,Table[Score]),$F5) Where “Table” is an Excel Table with data… read more »

Example of COUNTIFS with variable table column in Excel

To use COUNTIFS with a variable table column, you can use INDEX and MATCH to find and retrieve the column for COUNTIFS. See example below: Formula =COUNTIFS(INDEX(Table,0,MATCH(name,Table[#Headers],0)),criteria)) Explanation In the example shown, the formula in H5 is: =COUNTIFS(INDEX(Table1,0,MATCH(G5,Table1[#Headers],0)),”x”) How this formula works First, for context, it’s important to note that you can use COUNTIFS with… read more »

How to create running total in an Excel Table

This tutorial shows illustrates a Running total in Excel Table. To create a running total in an Excel Table, you can use the INDEX function set up with a structured reference. Formula =SUM(INDEX([column],1):[@column]) Explanation  In the example shown, the formula in F5 is: =SUM(INDEX([Total],1):[@Total]) When copied down the column, this formula will return a running total… read more »

Sidebar