What is PIVOTBY called in Spanish?

In Spanish Excel it is PIVOTARPOR. The formula =PIVOTBY(A2:A11,B2:B11,C2:C11,SUM) is written =PIVOTARPOR(A2:A11;B2:B11;C2:C11;SUMA) (or with commas in Mexico).

Why does PIVOTBY use semicolons in some Excel versions?

It depends on regional settings. Where the comma is the decimal mark (Spain, much of South America), Excel separates arguments with semicolons. English Excel and Mexican Spanish Excel use commas.

Excel PIVOTBY function

Builds a full pivot table with a formula: rows, columns, values and totals. In Spanish Excel it is called PIVOTARPOR.

Syntax

English Excel

=PIVOTBY(row_fields, col_fields, values, function, [field_headers], [row_total_depth], [row_sort_order], [col_total_depth], [col_sort_order])

Spanish Excel (Mexico)

=PIVOTARPOR(campos_de_fila, campos_de_columna, valores, función, [encabezados_de_campo], [profundidad_total_de_fila], [orden_de_fila], [profundidad_total_de_columna], [orden_de_columna])

Spanish Excel (Spain, Latin America)

=PIVOTARPOR(campos_de_fila; campos_de_columna; valores; función; [encabezados_de_campo]; [profundidad_total_de_fila]; [orden_de_fila]; [profundidad_total_de_columna]; [orden_de_columna])

Argument	What it is
row_fields	What goes in the rows.
col_fields	What goes in the columns.
values	The numbers to summarize.
function	SUM, AVERAGE, COUNT… without parentheses, or a LAMBDA.
field_headersoptional	Whether the ranges include headers.
row_total_depthoptional	0 removes the Total row.
row_sort_orderoptional	-1 sorts the rows descending.
col_total_depthoptional	0 removes the Total column.
col_sort_orderoptional	-1 sorts the columns descending.

Example

PIVOTBY(row_fields, col_fields, values, function) is a full pivot table: one row per value of the first field, one column per value of the second, and totals both ways. It recalculates by itself when the data changes, with no Refresh button.

=PIVOTBY(A2:A11,B2:B11,C2:C11,SUM)

Common mistake

Excel 365 only. Unlike a PivotTable, there is no Refresh button to press: it changes as soon as the data does.

About Ads

This site uses Google AdSense to display ads. These ads help us keep WebHerramientas completely free. Ads are managed by Google and meet strict security and privacy standards.

Practice PIVOTBY

Type the formula in the sheet and check it instantly. Spanish or English function names both work. See all lessons

Your progress0/218

My Excel is inExcel functions in English and Spanish

PIVOTBY: rows and columns

Example=PIVOTBY(A2:A11,B2:B11,C2:C11,SUM)PIVOTARPOR = PIVOTBY

In E1, build a table with branches as rows, quarters as columns and the sum of sales with PIVOTBY.

	A	B	C
1	Branch	Quarter	Sales
2	Downtown	Q1	42,000.00
3	Downtown	Q2	45,500.00
4	Downtown	Q3	39,800.00
5	North	Q1	31,200.00
6	North	Q2	35,800.00
7	North	Q3	38,900.00
8	South	Q1	27,400.00
9	South	Q2	29,900.00
10	South	Q3	33,100.00
11	Downtown	Q3	6,200.00
12

Tip: while typing, click a cell or drag across several to insert their reference. F4 anchors the reference with $.

Frequently asked questions

What is PIVOTBY called in Spanish?: In Spanish Excel it is PIVOTARPOR. The formula =PIVOTBY(A2:A11,B2:B11,C2:C11,SUM) is written =PIVOTARPOR(A2:A11;B2:B11;C2:C11;SUMA) (or with commas in Mexico).
Why does PIVOTBY use semicolons in some Excel versions?: It depends on regional settings. Where the comma is the decimal mark (Spain, much of South America), Excel separates arguments with semicolons. English Excel and Mexican Spanish Excel use commas.
What is the most common PIVOTBY mistake?: Excel 365 only. Unlike a PivotTable, there is no Refresh button to press: it changes as soon as the data does.