Excel OfficeExcel Data Analysis is a powerful tool that is used to visualize and gain insights of records in a spreadsheet.
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »
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 »