Soluții

How to use the PIVOTBY Function in Excel

Excel’s PIVOTBY function allows you to group your figures without needing to recreate your data in a PivotTable. What’s more, data summaries created via PIVOTBY automatically update to reflect changes in your original data, and you can customize what they show by tweaking the formula.

If you already know how to use the GROUPBY function, learning to use the PIVOTBY function will be straightforward. The difference is that where GROUPBY only groups variables into rows, PIVOTBY has an additional argument that lets you also group certain variables into columns.

The PIVOTBY function lets you input a total of 11 arguments. Although this sounds daunting, the benefit of having so many arguments is that you can tailor the result to align with what you want the data to show.

However, only the first four fields are required.

Required Fields

The required PIVOTBY fields are where you tell Excel which variables you want to be displayed in rows, which variables you want to be displayed in columns, where to find the values, and how you want to aggregate your data:

=PIVOTBY(a,b,c,d)

where

  • a are the cells containing the category or categories you want to appear as row headers down the left-hand side of your result,
  • b are the cells containing the category or categories you want to appear as column headers across the top of your result,
  • c are the values that will appear in the center of your result, according to the rows and columns you selected for arguments a and b, and
  • d is the Excel function (or function you’ve created yourself through LAMBDA) that defines how the data will be aggregated.

Although arguments a and b are required for PIVOTBY to produce the type of result it was created to produce, you can omit them to group data in rows or columns only.

  • First, let me show you how to use the PIVOTBY function in its simplest form with just the four required fields.
  • Let’s say you’ve been handed this table of data named Sports_Viewers. It shows the live viewing figures (column D) for six sports (column B) across four regions (column C) over four years (column A), and you’ve been asked to generate a summary of total viewers by sport and year.
  • You could use the GROUPBY function to do this, though you would need to use optional arguments to produce a result that makes analysis easier. What’s more, since GROUPBY only sorts data into rows, you could end up with a long list of information that is difficult to interpret.
  • Instead, using the PIVOTBY function lets you take one or more of the variables and place them into columns, giving you a clearer picture of how your numbers stack up.
  • Since the PIVOTBY formula produces a dynamic spilled array, you must type its formula in an area of your spreadsheet that is not formatted as an Excel table.

To do this, in cell F1, type

=PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)

where

  • Sports_Viewers[Sport] is the named column in the named table that represents the six different sports. These will be the row headers down the left-hand side of your result.
  • Sports_Viewers[Year] represents the table’s Year column, and these will be the column headers along the top of the result.
  • Sports_Viewers[Viewers] is the column showing the number of people who have watched those sports in those years, so this data will be the focal part of the result.
  • SUM tells Excel you want the total columns to add the figures together. On the other hand, if, for example, you use AVERAGE, the result will show you the mean of those figures instead.

  • Instead of typing the table and column names in your formula (also known as structured references) manually, if you select the cells in the table using your mouse, Excel will input these for you.

  • Because of the way PIVOTBY displays and organizes the data, you can draw insights from the result straightaway. For example, you can see that data is absent for certain sports in certain years. Also, in the absence of the optional fields, Excel automatically adds totals to both the rows and columns of the PIVOTBY result, meaning you can instantly see the total viewing figures per year and per sport.
  • Without the optional arguments, Excel also sorts the result into alphabetical or ascending order by argument a, and doesn’t include column headers for argument a.
  • Now, let’s say you want to include a column that divides each sport into the four different regions. To do this, you’ll need to include both the sport and the region in argument a:
  • =PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM)

To group non-adjacent columns into an argument, use the CHOOSECOLS function.

This result provides more detailed insight into your data by showing each sport’s total viewing figures by region. However, because you’ve added an extra variable to argument a, to make the result clearer, you could enter some optional arguments.

  • PIVOTBY: Using the Optional Arguments
  • Whenever you create an Excel formula, each argument is separated by a comma. This means that if you want to skip over any of the optional arguments, you simply need to type a first comma to open the argument, followed by a second comma to close it. The fact that there’s nothing between these two commas tells Excel that you’ve deliberately omitted this argument, so you want the program to adopt the default setting.
The Beginner’s Guide to Excel’s Formulas and Functions

Everything you need to know about Excel’s engine room.

  • Example 1: Grand Totals and Subtotals
  • One way to make the PIVOTBY result in the previous example clearer would be to add subtotal rows for each sport.
  • To do this, you need to type:
  • =PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2)
  • Notice how, after argument d (SUM), two commas indicate that you want to jump over argument e (headers), but include argument f (in this case, 2 means that you want to include a subtotal at the bottom of each sport, as well as grand totals at the bottom of the overall result).

  • To make the data even clearer, I have used conditional formatting to color any rows in the result where column F is not blank, column G is blank, and column F doesn’t contain the words “Grand Total.” Applying direct formatting to spilled arrays is not advised, as the formatting is attached to the cells, rather than the data. This means that if the data changes, the formatting won’t change with it.

  • Example 2: Sort Order
  • Now, let’s look at another way to manipulate your result: sorting by one of the output columns.
  • Typing:
  • =PIVOTBY(Sports_Viewers[[Sport]:[Region]],Sports_Viewers[Year],Sports_Viewers[Viewers],SUM,,2,3)
  • means that, after skipping over argument e, as well as including grand totals and subtotals (number 2 in argument f), you also want to order your data by overall viewing figures in ascending order (number 3 in argument g).

Notice how sorting by total viewing figures places not only the sports in order according to their overall totals, but also the regional subcategories according to their subtotals.

  • Example 3: Percentages (PERCENTOF)
  • Here’s how to view your result using percentages.
  • Typing:
  • =PIVOTBY(Sports_Viewers[Sport],Sports_Viewers[Year],Sports_Viewers[Viewers],PERCENTOF,,,2,,,,2) tells Excel that you want the sports as the row headers (argument a), the years as the column headers (argument b), and you want to aggregate your data (argument c) in the form of percentages (argument d). The first 2 (argument g) represents the order, which, in this case, is by the values. Finally, the second 2 (argument k) tells Excel that the percentages should be relative to the overall data. The unpopulated commas tell Excel you want it to adopt the default for arguments e, f, h, i, and j.

Use the PERCENTOF Function to Simplify Percentage Calculations in Excel

Handle percentages like a pro!

  • Now, select the cells containing the decimalized results, and click the “Percent Style” icon in the Number group of the Home tab on the ribbon. While you’re there, click the “Increase Decimal” and “Decrease Decimal” buttons to define the number of decimal places.

  • Select extra rows beneath the result in case more data is added to the original table in the future.

  • Finally, take a moment to digest what this result tells you.
  • For example, the number of people watching softball in 2022 constitutes 11.5% of the overall viewing figures for all sports across all years. Also, because the data is organized by the total column in ascending order, you can see that nearly a quarter (23.7%) of the overall viewing figures came from people watching basketball.
  • As well as using SUM, AVERAGE, and PERCENTOF for the function argument in PIVOTBY, you can also use other aggregation functions, like COUNT, MIN, or MAX.
[mai mult...]

How to use Excel’s AGGREGATE Function to Refine calculations

Before we look at some examples of the AGGREGATE function in use, let’s see how it works. The AGGREGATE function has two syntaxes—one for references and one for arrays—though you don’t need to get yourself tied up in knots over which one you’re using, as Excel selects the relevant one depending on the arguments you input. You can see both syntaxes in use when I show you some examples soon.

The Reference Form Syntax

The syntax for the reference form of the AGGREGATE function is:

=AGGREGATE(a,b,c,d)

where

  • a (required) is a number that represents the function you want to use in the calculation,
  • b (required) is a number that defines what you want the calculation to ignore,
  • c (required) is the range of cells on which the function will be applied, and
  • d (optional) is the first of up to 252 additional arguments that specify further ranges.

The Array Form Syntax

On the other hand, if you’re working with arrays, the syntax is:

=AGGREGATE(a,b,c,d)

where

  • a (required) is a number that represents the function you want to use in the calculation,
  • b (required) is a number that defines what you want the calculation to ignore,
  • c (required) is the array of values on which the function will be applied, and
  • d is the second argument required by array functions like LARGE, SMALL, PERCENTILE.INC, and others.

Functions and Exclusions (Arguments a and b)

When entering arguments a and b in either syntax form above, you’ll have various options to choose from.

The table below shows the different functions you can use in the AGGREGATE calculation (argument a). Even though you might be tempted to type the function name, remember that this argument must be a number that represents the function you want to use. Functions 1 to 13 are for use with the reference form syntax, and functions 14 to 19 are for use with the array form syntax.

Number Function What It Calculates
1 AVERAGE The arithmetic mean
2 COUNT The number of cells that contain numeric values
3 COUNTA The number of cells that are not empty
4 MAX The largest value
5 MIN The smallest value
6 PRODUCT A multiplication
7 STDEV.S The simple standard deviation
8 STDEV.P The population-based standard deviation
9 SUM An addition
10 VAR.S The simple variation
11 VAR.P The population-based variance
12 MEDIAN The middle value
13 MODE.SNGL The most frequently occurring number
14 LARGE The nth largest value
15 SMALL The nth smallest value
16 PERCENTILE.INC The nth percentile, with the first and last values included
17 QUARTILE.INC The nth quartile, with the first and last values included
18 PERCENTILE.EXC The nth percentile, with the first and last values excluded
19 QUARTILE.EXC The nth quartile, with the first and last values excluded

This table lists the numbers you can input to exclude certain values when creating your AGGREGATE formula (argument b):

Number What Is Ignored
0 Nested SUBTOTAL and AGGREGATE functions
1 Hidden rows, and nested SUBTOTAL and AGGREGATE functions
2 Errors, and nested SUBTOTAL and AGGREGATE functions
3 Hidden rows, error values, and nested SUBTOTAL and AGGREGATE functions
4 Nothing
5 Hidden rows only
6 Errors only
7 Hidden rows and errors

Now, let’s look at some examples of how you can use the AGGREGATE function in real-world scenarios.

Example 1: Using AGGREGATE to ignore errors

This Excel spreadsheet contains a list of soccer players, the number of games they’ve played, the number of goals they’ve scored, and their game-per-goal ratios. Your aim is to work out the average game-per-goal ratio for all the players combined.

If you were to use the AVERAGE function alone by typing:

=AVERAGE(Player_Goals[Games per goal])

into cell C1, this would return an error, because the referenced range contains #DIV/0! errors.

Instead, using the AGGREGATE function gives you the option to ignore these errors and return the average for the remaining data. To do this, in cell C2, you need to type:

=AGGREGATE(1,6,Player_Goals[Games per goal])

where

  • 1 (argument a) represents the AVERAGE function,
  • 6 (argument b) tells Excel to ignore errors, and
  • Player_Goals[Games per goal] is the reference.

An alternative way to achieve the same outcome would be to use the IFERROR function in column D to replace any errors with a blank value.

Example 2: Using AGGREGATE to Ignore Hidden Rows (Reference)

Using the same spreadsheet, your next target is to calculate the total number of goals the team has scored.

One way to display totals is to check “Total Row” in the Table Design tab on the ribbon, which places the totals at the bottom of the table. However, if you’re working with a large dataset, constantly scrolling down to see the totals could waste time. Instead, consider placing the totals at the top of the spreadsheet outside the formatted table, so that they’re always on display.

Specifically, you want to display two totals. The first is the overall total when you combine the goals scored by all players, but the second is the total of only the players showing in the table after you apply filters.

To calculate the overall total, in cell C1, type:

=SUM(Player_Goals[Goals scored])

Now, even after you apply a filter to one of the columns, such as displaying only the players who have played 15 games or more, the SUM formula you just applied still includes the rows that are filtered out.

This is where the AGGREATE function will save the day, as it allows your calculation to ignore rows that are filtered out. In fact, the AGGREGATE function would also work if you wanted to ignore rows you’ve hidden by right-clicking the row header and clicking “Hide.”

In cell C2, type:

=AGGREGATE(9,5,Player_Goals[Goals scored])

where

  • 9 (argument a) represents the SUM function,
  • 5 (argument b) tells Excel to ignore hidden rows, and
  • Player_Goals[Goals scored] is the reference.

Now, notice that the result of this formula differs from the result of the SUM formula you used in cell C1, because it considers only the rows on display.

​​​​Example 3: Using AGGREGATE to Ignore Hidden Rows (Array)

Next, let’s say you wanted to list the two highest goal tallies for players who have played 20 games or fewer.

You could apply the filter first and then generate your formula, but for the purposes of this demonstration, let’s create the formula first.

In cell C1, type:

=AGGREGATE(14,5,Player_Goals[Goals scored],{1;2})

where

  • 14 (argument a) represents the LARGE function,
  • 5 (argument b) tells Excel to ignore hidden rows,
  • Player_Goals[Goals scored] is the array of values, and
  • {1;2} tells Excel that you want it to return the largest (1) and second-largest values (2) on separate rows (;).

When you press Enter, notice that the result is a spilled array covering cells C1 and C2 because you told Excel to return the top two values.

Now, filter the Games Played column to include only those players who have played 20 games or fewer, and see that the result of the AGGREGATE formula you entered earlier changes to ignore the hidden rows.

[mai mult...]

How to use Windows 11 as a Bluetooth Audio Receiver

To listen to music on your Windows 11 PC from a Bluetooth device, you’ll have to pair that device with your PC first. This is how your PC recognizes your device.

To do that, turn on Bluetooth on your device, like your iPhone or Android phone.

  • On an iPhone, you’ll head into Settings > Bluetooth and turn on the “Bluetooth” toggle.
  • On an Android phone, you’ll pull down twice from the top of the device screen and tap the “Bluetooth” tile.

On Windows 11, launch Settings by pressing Windows+i.

  • From the left sidebar, choose “Bluetooth & Devices”
  • On the right pane, enable “Bluetooth” if it isn’t already enabled
  • Then, next to Devices, click “Add Device”

On the Add a Device window, select “Bluetooth.” Wait for your PC to detect your device. When the device appears on the list, click it to select it. When your PC says Your Device is Ready to Go!, click “Done” to close the window. Both your PC and your device are now paired.

To receive audio via Bluetooth on your Windows 11 PC, you’ll download Bluetooth Audio Receiver from the Microsoft Store. This is because Windows 11 doesn’t have a built-in feature that allows it to act as a Bluetooth speaker.

To get that app, launch Windows Search (press Windows+S), type Microsoft Store, and open the app. Click the search box at the top, type Bluetooth Audio Receiver, press Enter, and select that app on the list. Then, choose “Get” to download and install the app.

Boost your PC’s efficiency with these apps.

Open Windows Search again, type Bluetooth Audio Receiver, and select the app in the search results. On the app screen, select the device you want to receive audio from. This should be the device that you’ve paired with your PC earlier. After selecting the device, click the “Open Connection” button.

At the top of the device list, you’ll see a [Your Device Name] Connected message, which indicates your PC is ready to act as a Bluetooth speaker for your device.

On your device, launch a music app and start playing audio. You’ll find that the audio plays through your PC’s speakers. You can control the volume using your device’s volume control or by clicking the sound icon in your PC’s system tray and adjusting the volume level.

  • When you want to stop listening to the music on your PC, access the Bluetooth Audio Receiver window and click the “Close Connection” button. Note that you don’t have to unpair your device.
  • If you run into issues while trying to connect or play music on your Windows 11 PC, the following troubleshooting tips will help fix the problem.
  • If you don’t see your Bluetooth device on the Add a Device window on your computer, your device is likely undiscoverable. In this case, access the pair screen on your device.

How to Troubleshoot Bluetooth Issues on Windows

Having trouble connecting a Bluetooth device on Windows? These basic troubleshooting steps should get things working again!

  • For example, on an Android phone, you’ll head into Settings > Connected Devices > Pair New Device and keep this screen open while performing the pairing process on your PC.
  • If the connection is established, but your PC doesn’t play the audio, ensure you haven’t muted your PC’s speakers. To verify that, click the sound icon in your PC’s system tray and increase the volume.
  • If you’re using headphones with your PC, right-click the sound icon and choose “Sound Settings.” On the open window, click “Choose Where to Play Sound” and select your headphones. This ensures your PC routes the audio to the correct output device.

If the audio breaks, ensure both your PC and your device are close to each other. This is because Bluetooth has a working range of 30 feet (10 meters). Outside of that, your devices can’t talk to each other. Also, make sure there are no obstacles between your PC and your device.

[mai mult...]